Snowflake OCF Connector: Extraction Queries - Versions Before 2.0.0

Note

The queries on this page apply to versions of the Snowflake OCF connector that come before version 2.0.0.

The default extraction queries for the Snowflake data source are listed below. You can customize them to better suit your extraction requirements. Custom queries should 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.

Examples

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>'
ORDER BY
  DATABASE_NAME;

Database Name Is Not Provided in JDBC UR

SELECT
  DATABASE_NAME AS CATALOG
FROM
  SNOWFLAKE.ACCOUNT_USAGE.DATABASES
WHERE
  DELETED IS NULL
  AND DATABASE_NAME IN ('<database_name1>', '<database_name2>')
ORDER BY
  DATABASE_NAME;

Schema

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

Examples

Database Name Is Provided in JDBC URI

SELECT
  CATALOG_NAME AS CATALOG,
  SCHEMA_NAME AS SCHEMA
FROM
  SNOWFLAKE.ACCOUNT_USAGE.SCHEMATA
WHERE
 DELETED IS NULL
 AND CONCAT(CATALOG_NAME,'.',SCHEMA_NAME) IN ('<database_name>.<schema_name>')
 AND CATALOG_NAME = '<database_name>'
ORDER BY
  DATABASE_NAME;

Database Name Is Not Provided in JDBC URI

SELECT
  CATALOG_NAME AS CATALOG,
  SCHEMA_NAME AS SCHEMA
FROM
  SNOWFLAKE.ACCOUNT_USAGE.SCHEMATA
WHERE
 DELETED IS NULL
 AND CONCAT(CATALOG_NAME,'.',SCHEMA_NAME) IN ('<database_name>.<schema_name>')
 AND CATALOG_NAME IN ('<database_name1>', '<database_name2>')
ORDER BY
  DATABASE_NAME;

Table

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

Examples

Database Name Is Provided in JDBC URI

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

Database Name Is Not Provided in JDBC URI

SELECT
  TABLE_CATALOG AS CATALOG,
  TABLE_SCHEMA AS SCHEMA,
  TABLE_NAME,
  TABLE_TYPE,
  COMMENT AS REMARKS
FROM
  SNOWFLAKE.ACCOUNT_USAGE.TABLES
WHERE
  DELETED IS NULL
  AND TABLE_TYPE = 'BASE TABLE'
  AND CONCAT(TABLE_CATALOG,'.',TABLE_SCHEMA) IN ('<database_name>.<schema_name>')
  AND TABLE_CATALOG IN ('<database_name1>', '<database_name2>')
  AND TRIM(TABLE_NAME) !=''
ORDER BY
  TABLE_CATALOG;

View

Make sure that your query has columns labeled as DATABASE_NAME, SCHEMA_NAME, NAME, TEXT, IS_MATERIALIZED, and COMMENT in the SELECT statement.

Examples

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 CONCAT(TABLE_CATALOG,'.',TABLE_SCHEMA) IN ('<database_name>.<schema_name>')
  AND TABLE_CATALOG = '<database_name>'
ORDER BY
  TABLE_CATALOG;

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 CONCAT(TABLE_CATALOG,'.',TABLE_SCHEMA) IN ('<database_name>.<schema_name>')
  AND TABLE_CATALOG IN ('<database_name1>', '<database_name2>')
ORDER BY
  TABLE_CATALOG;

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.

Examples

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 CONCAT(TABLE_CATALOG,'.',TABLE_SCHEMA) IN ('<database_name>.<schema_name>')
  AND TABLE_CATALOG = '<database_name>'
  AND TRIM(COLUMN_NAME) NOT IN ('','SYS_MV_SOURCE_PARTITION')
  AND TRIM(TABLE_NAME) !=''
ORDER BY
  TABLE_CATALOG;

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 CONCAT(TABLE_CATALOG,'.',TABLE_SCHEMA) IN ('<database_name>.<schema_name>')
  AND TABLE_CATALOG  IN ('<database_name1>', '<database_name2>')
  AND TRIM(COLUMN_NAME) NOT IN ('','SYS_MV_SOURCE_PARTITION')
  AND TRIM(TABLE_NAME) !=''
ORDER BY
  TABLE_CATALOG;

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 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.

Examples

Database Name Is Provided in JDBC URI

SELECT
  FUNCTION_CATALOG AS CATALOG,
  FUNCTION_SCHEMA AS SCHEMA,
  FUNCTION_NAME,
  COMMENT AS REMARKS,
  '' AS FUNCTION_DEFINITION,
  ARGUMENT_SIGNATURE,
  FUNCTION_ID
FROM SNOWFLAKE.ACCOUNT_USAGE.FUNCTIONS
WHERE DELETED IS NULL
  AND 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

SELECT
  FUNCTION_CATALOG AS CATALOG,
  FUNCTION_SCHEMA AS SCHEMA,
  FUNCTION_NAME,
  COMMENT AS REMARKS,
  '' AS FUNCTION_DEFINITION,
  ARGUMENT_SIGNATURE,
  FUNCTION_ID
FROM SNOWFLAKE.ACCOUNT_USAGE.FUNCTIONS
WHERE DELETED IS NULL
  AND CONCAT(FUNCTION_CATALOG,'.',FUNCTION_SCHEMA) IN ('<database_name>.<schema_name>')
  AND FUNCTION_CATALOG IN ('database_name1', 'database_name2')
ORDER BY
  FUNCTION_CATALOG;

Function Definition

Examples

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;

Tags and Tag Links

Note

Tag and tag link extraction using a custom query is available from connector version 1.2.0.5882.

Tags

Ensure your query has columns labeled as TAG_DATABASE, TAG_SCHEMA, TAG_NAME, TAG_OWNER, CREATED, TAG_COMMENT, TAG_ID, DELETED, TAG_ID, SCHEMA, TAG_OWNER, CATALOG, TAG_NAME, CREATED, TAG_ID, TAG_COMMENT in the SELECT statement.

WITH TAG_QUERIES AS
(
SELECT
  TAG_DATABASE,
  TAG_SCHEMA,
  TAG_NAME,
  TAG_OWNER,
  CREATED,
  TAG_COMMENT,
  TAG_ID,
  DELETED
FROM SNOWFLAKE.ACCOUNT_USAGE.TAGS
WHERE DELETED IS NULL
  AND TRIM(TAG_NAME) != ''
  AND (TAG_ID IN
          (SELECT TAG_ID
          FROM SNOWFLAKE.ACCOUNT_USAGE.TAG_REFERENCES
          WHERE OBJECT_DATABASE = '<database_name>') OR TAG_DATABASE IN ('<database_name>'))
ORDER BY TAG_DATABASE,
  TAG_SCHEMA,
  TAG_NAME
)
SELECT
  LISTAGG(TAG_OWNER, ', ') AS TAG_OWNER,
  TAG_DATABASE AS CATALOG,
  TAG_SCHEMA AS SCHEMA,
  TAG_NAME,
  CREATED,
  TAG_ID,
  TAG_COMMENT
FROM TAG_QUERIES
GROUP BY
  CATALOG,
  SCHEMA,
  TAG_NAME,
  TAG_ID,
  CREATED,
  TAG_COMMENT
ORDER BY
  CATALOG,
  SCHEMA,
  TAG_NAME;

Tag Links

Ensure your query has columns labeled as CATALOG, SCHEMA, TAG_NAME, TAG_VALUE, TAG_ID, OBJECT_DATABASE, OBJECT_SCHEMA, OBJECT_NAME, DOMAIN, COLUMN_NAME in the SELECT statement.

(
SELECT
  TAG_DATABASE AS CATALOG,
  TAG_SCHEMA AS SCHEMA,
  TAG_NAME,
  TAG_VALUE,
  TAG_ID,
  OBJECT_DATABASE,
  OBJECT_SCHEMA,
  OBJECT_NAME,
  DOMAIN,
  COLUMN_NAME
FROM SNOWFLAKE.ACCOUNT_USAGE.TAG_REFERENCES
WHERE OBJECT_DELETED IS NULL
  AND DOMAIN IN ('SCHEMA')
  AND OBJECT_DATABASE = '<database_name>'
UNION
SELECT
  TAG_DATABASE AS CATALOG,
  TAG_SCHEMA AS SCHEMA,
  TAG_NAME,
  TAG_VALUE,
  TAG_ID,
  OBJECT_DATABASE,
  OBJECT_SCHEMA,
  OBJECT_NAME,
  DOMAIN,
  COLUMN_NAME
FROM SNOWFLAKE.ACCOUNT_USAGE.TAG_REFERENCES
WHERE OBJECT_DELETED IS NULL
  AND DOMAIN IN ('TABLE', 'COLUMN')
  AND OBJECT_DATABASE = '<database_name>'
)
ORDER BY
  CATALOG,
  SCHEMA,
  TAG_NAME;

Extracting Tags and Tag Links for Multiple Databases

To extract tags and tag links for multiple databases, use the IN operator in the WHERE filter, similar to the following examples:

• tags:

  WHERE OBJECT_DATABASE IN ('<database_name1>', '<database_name2>') OR TAG_DATABASE IN (<database_name1>', '<database_name2>')

• tag links:

  WHERE OBJECT_DELETED IS NULL AND DOMAIN IN ('SCHEMA') AND OBJECT_DATABASE IN (<database_name1>', '<database_name2>')