Snowflake OCF Connector Extraction Queries

Alation Cloud Service Applies to Alation Cloud Service instances of Alation

Customer Managed Applies to customer-managed instances of Alation

The default extraction queries for Snowflake OCF data sources are listed below. You can customize them to better suit your extraction requirements. Custom queries can be provided on the Metadata Extraction tab of the data source Settings page under Metadata Extraction Queries.

You can customize all or some of the queries.

If specified, custom extraction queries will take precedence over the default MDE queries.

Important

The database name may or may not be present in the JDBC URI. In this case the default queries will differ in the operator of the WHERE filter:

  • If the database name is present, the WHERE filter will equal CATALOG_NAME to the database name.

  • If the database name is not present, the WHERE filter will in most cases use the IN operator and a list of database names that the service account has access to.

Catalog

Make sure that your query has a column labeled as CATALOG in the SELECT statement.

Database Name Is Provided in JDBC URI

SELECT
    DATABASE_NAME AS CATALOG
FROM
    SNOWFLAKE.ACCOUNT_USAGE.DATABASES
WHERE
    DELETED IS NULL
AND DATABASE_NAME = '<database_name>';

Database Name Is Not Provided in JDBC URI

SELECT
    DATABASE_NAME AS CATALOG
FROM
    SNOWFLAKE.ACCOUNT_USAGE.DATABASES
WHERE
    DELETED IS NULL;

Schema

Make sure that your query has a column labeled as CATALOG, SCHEMA in the SELECT statement.

Database Name Is Provided in JDBC URI

SELECT
    CATALOG_NAME AS CATALOG,
    SCHEMA_NAME AS SCHEMA,
    COMMENT AS REMARKS
FROM
    SNOWFLAKE.ACCOUNT_USAGE.SCHEMATA
WHERE
    DELETED IS NULL
    AND SCHEMA_NAME IN ('<schema_name_1>' , '<schema_name_2>' , '<schema_name_3>')
    AND CATALOG_NAME = '<database_name>'
    AND TRIM(SCHEMA_NAME) !='';

Database Name Is Not Provided in JDBC URI

SELECT
    CATALOG_NAME AS CATALOG,
    SCHEMA_NAME AS SCHEMA,
    COMMENT AS REMARKS
FROM
    SNOWFLAKE.ACCOUNT_USAGE.SCHEMATA
WHERE
    DELETED IS NULL
    AND SCHEMA_NAME IN ('<schema_name_1>' , '<schema_name_2>' , '<schema_name_3>')
    AND CATALOG_NAME IN ('<database_name_1>, <database_name_2>')
    AND TRIM(SCHEMA_NAME) !='';

Table

Make sure that your query has columns labeled as CATALOG, SCHEMA, TABLE_NAME, TABLE_TYPE, and REMARKS in the SELECT statement.

Database Name Is Provided in JDBC URI

SELECT
    TABLE_CATALOG AS CATALOG,
    TABLE_SCHEMA AS SCHEMA,
    TABLE_NAME,
    'TABLE' AS TABLE_TYPE,
    COMMENT AS  REMARKS
FROM
    SNOWFLAKE.ACCOUNT_USAGE.TABLES
WHERE
    DELETED IS NULL
    AND TABLE_TYPE = 'BASE TABLE'
    AND TABLE_SCHEMA IN ('<schema_name_1>', '<schema_name_2>', '<schema_name_3>')
    AND TABLE_CATALOG = '<database_name>'
    AND TRIM(TABLE_NAME) !='';

Database Name Is Not Provided in JDBC URI

SELECT
    TABLE_CATALOG AS CATALOG,
    TABLE_SCHEMA AS SCHEMA,
    TABLE_NAME,
    'TABLE' AS TABLE_TYPE,
    COMMENT AS REMARKS
FROM
    SNOWFLAKE.ACCOUNT_USAGE.TABLES
WHERE
    DELETED IS NULL
    AND TABLE_TYPE = 'BASE TABLE'
    AND TABLE_SCHEMA IN ('<schema_name_1>', '<schema_name_2>', '<schema_name_3>')
    AND TABLE_CATALOG IN ('<database_name_1>, <database_name_2>')
    AND TRIM(TABLE_NAME) !='';

External Table

Database Name Is Provided in JDBC URI

-- get stages from database using INFORMATION_SCHEMA
SELECT
    STAGE_URL,
    CONCAT('@',TRIM(STAGE_CATALOG),'.',TRIM(STAGE_SCHEMA),'.',TRIM(STAGE_NAME),'/') AS LOCATION
FROM "<database_name>".INFORMATION_SCHEMA.STAGES

-- get external tables from database using INFORMATION_SCHEMA
SELECT
    ET.TABLE_CATALOG AS CATALOG,
    ET.TABLE_SCHEMA AS SCHEMA,
    ET.TABLE_NAME,
    'TABLE' AS TABLE_TYPE,
    ET.COMMENT AS REMARKS,
    ET.LOCATION AS DATA_LOCATION
FROM "<database_name>".INFORMATION_SCHEMA.EXTERNAL_TABLES ET
WHERE
    ET.TABLE_SCHEMA
    IN ('<schema_name_1>', '<schema_name_2>', '<schema_name_3>');

Database Name Is Not Provided in JDBC URI

-- get stages from all databases using INFORMATION_SCHEMA
SELECT
    STAGE_URL,
    CONCAT('@',TRIM(STAGE_CATALOG),'.',TRIM(STAGE_SCHEMA),'.',TRIM(STAGE_NAME),'/') AS LOCATION
FROM "<database_name>".INFORMATION_SCHEMA.STAGES

-- get external tables from all databases using INFORMATION_SCHEMA
SELECT
    ET.TABLE_CATALOG AS CATALOG,
    ET.TABLE_SCHEMA AS SCHEMA,
    ET.TABLE_NAME,
    'TABLE' AS TABLE_TYPE,
    ET.COMMENT AS REMARKS,
    ET.LOCATION AS DATA_LOCATION
FROM "<database_name>".INFORMATION_SCHEMA.EXTERNAL_TABLES ET
WHERE ET.TABLE_SCHEMA IN ('<schema_name_1>', '<schema_name_2>', '<schema_name_3>');

View

Make sure that your query has columns labeled as CATALOG, SCHEMA, VIEW_NAME, VIEW_CREATE_STATEMENT, VIEW_TYPE, and REMARKS in the SELECT statement.

Note

The default view extraction query does not filter out temporary views. If you don’t want the temporary views to appear in the catalog, uncomment the condition AND VIEW_DEFINITION not ILIKE '%create or replace temporary view%' in the WHERE filter.

Database Name Is Provided in JDBC URI

SELECT
    TABLE_CATALOG AS CATALOG,
    TABLE_SCHEMA AS SCHEMA,
    TABLE_NAME AS VIEW_NAME,
    VIEW_DEFINITION AS VIEW_CREATE_STATEMENT,
    'VIEW' AS VIEW_TYPE,
    COMMENT AS REMARKS
FROM
    SNOWFLAKE.ACCOUNT_USAGE.VIEWS
WHERE
    DELETED IS NULL
    AND TABLE_SCHEMA IN ('<schema_name_1>', '<schema_name_2>', '<schema_name_3>')
    AND TABLE_CATALOG = '<database_name>'
    AND TRIM(TABLE_NAME) != ''
    -- Uncomment the next line to filter out temporary views
    -- AND VIEW_DEFINITION not ILIKE '%create or replace temporary view%';

Database Name Is Not Provided in JDBC URI

SELECT
    TABLE_CATALOG AS CATALOG,
    TABLE_SCHEMA AS SCHEMA,
    TABLE_NAME AS VIEW_NAME,
    VIEW_DEFINITION AS VIEW_CREATE_STATEMENT,
    'VIEW' AS VIEW_TYPE,
    COMMENT AS REMARKS
FROM
    SNOWFLAKE.ACCOUNT_USAGE.VIEWS
WHERE
    DELETED IS NULL
    AND TABLE_SCHEMA IN ('<schema_name_1>', '<schema_name_2>', '<schema_name_3>')
    AND TABLE_CATALOG IN ('<database_name_1>, <database_name_2>')
    AND TRIM(TABLE_NAME) != ''
    -- Uncomment the next line to filter out temporary views
    -- AND VIEW_DEFINITION not ILIKE '%create or replace temporary view%';

Column

Make sure that your query has columns labeled as CATALOG, SCHEMA, TABLE_NAME, TYPE_NAME, DATA_TYPE, COLUMN_NAME, ORDINAL_POSITION, IS_NULLABLE, REMARKS, and COLUMN_DEFAULT in the SELECT statement.

Database Name Is Provided in JDBC URI

SELECT
    TABLE_CATALOG AS CATALOG,
    TABLE_SCHEMA AS SCHEMA,
    TABLE_NAME,
    DECODE(DATA_TYPE,'TEXT','VARCHAR' || '(' || CHARACTER_MAXIMUM_LENGTH || ')','NUMBER',DATA_TYPE || '(' || NUMERIC_PRECISION ||','|| NUMERIC_SCALE || ')', DATA_TYPE) AS TYPE_NAME,
    DATA_TYPE,
    COLUMN_NAME,
    ORDINAL_POSITION,
    IS_NULLABLE,
    COMMENT AS REMARKS,
    COLUMN_DEFAULT
FROM
    SNOWFLAKE.ACCOUNT_USAGE.COLUMNS
WHERE
    DELETED IS NULL
    AND TABLE_SCHEMA IN ('<schema_name_1>' , '<schema_name_2>' , '<schema_name_3>')
    AND TABLE_CATALOG = '<database_name>'
    AND TRIM(COLUMN_NAME) NOT IN ('','SYS_MV_SOURCE_PARTITION','METADATA$ROW_VERSION')
    AND TRIM(TABLE_NAME) !='';

Database Name Is Not Provided in JDBC URI

SELECT
    TABLE_CATALOG AS CATALOG,
    TABLE_SCHEMA AS SCHEMA,
    TABLE_NAME,
    DECODE(DATA_TYPE,'TEXT','VARCHAR' || '(' || CHARACTER_MAXIMUM_LENGTH || ')',
        'NUMBER',DATA_TYPE || '(' || NUMERIC_PRECISION ||','|| NUMERIC_SCALE || ')', DATA_TYPE) AS TYPE_NAME,
    DATA_TYPE,
    COLUMN_NAME,
    ORDINAL_POSITION,
    IS_NULLABLE,
    COMMENT AS REMARKS,
    COLUMN_DEFAULT
FROM
    SNOWFLAKE.ACCOUNT_USAGE.COLUMNS
WHERE
    DELETED IS NULL
    AND TABLE_SCHEMA IN ('<schema_name_1>', '<schema_name_2>', '<schema_name_3>')
    AND TABLE_CATALOG IN ('<database_name_1>, <database_name_2>')
    AND TRIM(COLUMN_NAME) NOT IN ('','SYS_MV_SOURCE_PARTITION','METADATA$ROW_VERSION')
    AND TRIM(TABLE_NAME) !='';

Primary and Foreign Keys

If the database name is specified in the JDBC URI, then we use queries SHOW PRIMARY KEYS IN DATABASE <database_name> and SHOW EXPORTED KEYS IN DATABASE <database_name>.

If the database name is not specified, then we use queries SHOW PRIMARY KEYS IN ACCOUNT and SHOW EXPORTED KEYS IN ACCOUNT.

Function

Make sure that your query has columns labeled as CATALOG, SCHEMA, FUNCTION_NAME, and REMARKS in the SELECT statement.

Database Name Is Provided in JDBC URI

-- get function arguments
WITH FUNCTION_COLUMNS AS (
SELECT
    FUNCTION_CATALOG,
    FUNCTION_ID,
    FUNCTION_SCHEMA,
    FUNCTION_NAME,
    DATA_TYPE AS ARG_TYPE,
    TRIM(REGEXP_REPLACE(ARGUMENT_SIGNATURE,'(\\()|(\\))')) AS ARGS,
    ARGUMENT_SIGNATURE
FROM
    SNOWFLAKE.ACCOUNT_USAGE.FUNCTIONS WHERE DELETED IS NULL)
SELECT
    FUNCTION_CATALOG AS CATALOG,
    FUNCTION_SCHEMA AS SCHEMA,
    FUNCTION_NAME,
    ARG_TYPE,
    SPLIT_PART(TRIM(FUNCTION_COLUMNS_FLATTENED.VALUE::STRING),' ', 0) AS ARG_NAME,
    SPLIT_PART(TRIM(FUNCTION_COLUMNS_FLATTENED.VALUE::STRING),' ', 2) AS TYPE_NAME,
    FUNCTION_ID
FROM
    FUNCTION_COLUMNS, LATERAL FLATTEN(INPUT=>SPLIT(ARGS, ',')) FUNCTION_COLUMNS_FLATTENED
WHERE
    FUNCTION_SCHEMA IN ('<schema_name_1>', '<schema_name_2>', '<schema_name_3>')
    AND FUNCTION_CATALOG = '<database_name>'

-- get function
SELECT FUNCTION_CATALOG AS CATALOG,
    FUNCTION_SCHEMA AS SCHEMA,
    FUNCTION_NAME,
    F.COMMENT AS REMARKS,
    '' AS FUNCTION_DEFINITION,
    F.ARGUMENT_SIGNATURE,
    FUNCTION_ID,
    CASE
        WHEN (P.PROCEDURE_NAME IS NULL) THEN 'FUNCTION'
        ELSE 'PROCEDURE'
    END AS TYPE
FROM
    SNOWFLAKE.ACCOUNT_USAGE.FUNCTIONS F
LEFT JOIN
    SNOWFLAKE.ACCOUNT_USAGE.PROCEDURES P
ON
    F.FUNCTION_CATALOG = P.PROCEDURE_CATALOG
    AND F.FUNCTION_SCHEMA = P.PROCEDURE_SCHEMA
    AND F.FUNCTION_NAME = P.PROCEDURE_NAME
    AND F.ARGUMENT_SIGNATURE = P.ARGUMENT_SIGNATURE
    AND F.DATA_TYPE = P.DATA_TYPE
WHERE
    DELETED IS NULL
    AND FUNCTION_SCHEMA IN ('<schema_name_1>', '<schema_name_2>', '<schema_name_3>')
    AND FUNCTION_CATALOG = '<database_name>';

Database Name Is Not Provided in JDBC URI

-- get function arguments
WITH FUNCTION_COLUMNS AS (
SELECT
    FUNCTION_CATALOG,
    FUNCTION_ID,
    FUNCTION_SCHEMA,
    FUNCTION_NAME,
    DATA_TYPE AS ARG_TYPE,
    TRIM(REGEXP_REPLACE(ARGUMENT_SIGNATURE,'(\\()|(\\))')) AS ARGS,
    ARGUMENT_SIGNATURE
FROM
    SNOWFLAKE.ACCOUNT_USAGE.FUNCTIONS WHERE DELETED IS NULL)
SELECT
    FUNCTION_CATALOG AS CATALOG,
    FUNCTION_SCHEMA AS SCHEMA,
    FUNCTION_NAME,
    ARG_TYPE,
    SPLIT_PART(TRIM(FUNCTION_COLUMNS_FLATTENED.VALUE::STRING),' ', 0) AS ARG_NAME,
    SPLIT_PART(TRIM(FUNCTION_COLUMNS_FLATTENED.VALUE::STRING),' ', 2) AS TYPE_NAME,
    FUNCTION_ID
FROM
    FUNCTION_COLUMNS, LATERAL FLATTEN(INPUT=>SPLIT(ARGS, ',')) FUNCTION_COLUMNS_FLATTENED
WHERE
    FUNCTION_SCHEMA IN ('<schema_name_1>', '<schema_name_2>', '<schema_name_3>')
    AND FUNCTION_CATALOG IN ('<database_name_1>, <database_name_2>');

-- get functions
SELECT
    FUNCTION_CATALOG AS CATALOG,
    FUNCTION_SCHEMA AS SCHEMA,
    FUNCTION_NAME,
    F.COMMENT AS REMARKS,
    '' AS FUNCTION_DEFINITION,
    F.ARGUMENT_SIGNATURE,
    FUNCTION_ID,
    CASE
        WHEN (P.PROCEDURE_NAME IS NULL) THEN 'FUNCTION'
        ELSE 'PROCEDURE'
    END AS TYPE
FROM
    SNOWFLAKE.ACCOUNT_USAGE.FUNCTIONS F
LEFT JOIN
    SNOWFLAKE.ACCOUNT_USAGE.PROCEDURES P
ON
    F.FUNCTION_CATALOG = P.PROCEDURE_CATALOG
    AND F.FUNCTION_SCHEMA = P.PROCEDURE_SCHEMA
    AND F.FUNCTION_NAME = P.PROCEDURE_NAME
    AND F.ARGUMENT_SIGNATURE = P.ARGUMENT_SIGNATURE
    AND F.DATA_TYPE = P.DATA_TYPE
WHERE
    DELETED IS NULL
    AND FUNCTION_SCHEMA IN ('<schema_name_1>', '<schema_name_2>', '<schema_name_3>')
    AND FUNCTION_CATALOG IN ('<database_name_1>, <database_name_2>');

Function Definition: Version 2.0.0 and Newer

SELECT get_ddl('FUNCTION','"<database_name>"."<schema_name>"."<function_name>"(FLOAT,FLOAT)') AS SQL_VIEW_TEXT;

Function Definition: Versions Before 2.0.0

Database Name Is Provided in JDBC URI

WITH FUNCTION_COLUMNS AS
(
SELECT
  FUNCTION_CATALOG,
  FUNCTION_SCHEMA,
  FUNCTION_NAME,
  DATA_TYPE AS TYPE_NAME,
  TRIM(REGEXP_REPLACE(ARGUMENT_SIGNATURE,'(\\\\()|(\\\\))')) AS ARGS,
  ARGUMENT_SIGNATURE
FROM SNOWFLAKE.ACCOUNT_USAGE.FUNCTIONS
WHERE DELETED IS NULL)
SELECT
  FUNCTION_CATALOG AS CATALOG,
  FUNCTION_SCHEMA AS SCHEMA,
  FUNCTION_NAME,
  TYPE_NAME,
  SPLIT_PART(TRIM(FUNCTION_COLUMNS_FLATTENED.VALUE::STRING),' ', 0) AS ARG_NAME,
  SPLIT_PART(TRIM(FUNCTION_COLUMNS_FLATTENED.VALUE::STRING),' ', 2) AS ARG_TYPE
FROM
  FUNCTION_COLUMNS,
  LATERAL FLATTEN(INPUT=>SPLIT(ARGS, ',')) FUNCTION_COLUMNS_FLATTENED
WHERE
  CONCAT(FUNCTION_CATALOG,'.',FUNCTION_SCHEMA) IN ('<database_name>.<schema_name>')
  AND FUNCTION_CATALOG = '<database_name>'
ORDER BY FUNCTION_CATALOG;

Database Name Is Not Provided in JDBC URI

WITH FUNCTION_COLUMNS AS
(
SELECT
  FUNCTION_CATALOG,
  FUNCTION_SCHEMA,
  FUNCTION_NAME,
  DATA_TYPE AS TYPE_NAME,
  TRIM(REGEXP_REPLACE(ARGUMENT_SIGNATURE,'(\\\\()|(\\\\))')) AS ARGS,
  ARGUMENT_SIGNATURE
FROM SNOWFLAKE.ACCOUNT_USAGE.FUNCTIONS
WHERE DELETED IS NULL)
SELECT
  FUNCTION_CATALOG AS CATALOG,
  FUNCTION_SCHEMA AS SCHEMA,
  FUNCTION_NAME,
  TYPE_NAME,
  SPLIT_PART(TRIM(FUNCTION_COLUMNS_FLATTENED.VALUE::STRING),' ', 0) AS ARG_NAME,
  SPLIT_PART(TRIM(FUNCTION_COLUMNS_FLATTENED.VALUE::STRING),' ', 2) AS ARG_TYPE
FROM
  FUNCTION_COLUMNS,
  LATERAL FLATTEN(INPUT=>SPLIT(ARGS, ',')) FUNCTION_COLUMNS_FLATTENED
WHERE
  CONCAT(FUNCTION_CATALOG,'.',FUNCTION_SCHEMA) IN ('<database_name>.<schema_name>')
  AND FUNCTION_CATALOG IN ('database_name1', 'database_name2')
  ORDER BY FUNCTION_CATALOG;