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>';
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 URI¶
SELECT
DATABASE_NAME AS CATALOG
FROM
SNOWFLAKE.ACCOUNT_USAGE.DATABASES
WHERE
DELETED IS NULL;
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.
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) !='';
SELECT
CATALOG_NAME AS CATALOG,
SCHEMA_NAME AS SCHEMA,
COMMENT AS REMARKS
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,
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) !='';
SELECT
CATALOG_NAME AS CATALOG,
SCHEMA_NAME AS SCHEMA
COMMENT AS REMARKS
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.
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) !='';
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' 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) !='';
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;
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%';
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>'
-- Uncomment the next line to filter out temporary views
-- AND VIEW_DEFINITION not ILIKE '%create or replace temporary view%'
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 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%';
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>')
-- Uncomment the next line if to filter out temporary views
-- AND VIEW_DEFINITION not ILIKE '%create or replace temporary view%'
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.
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) !='';
.. code-block:: sql
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 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) !='';
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 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>';
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¶
-- 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>');
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: 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;
Policies and Policy Links¶
Database Name Is Provided in JDBC URI¶
Policies¶
WITH POLICIES_QUERIES AS
(
SELECT * FROM
(
SELECT
POLICY_CATALOG,
POLICY_SCHEMA,
POLICY_NAME,
POLICY_OWNER,
'ROW_ACCESS' AS POLICY_TYPE,
POLICY_SIGNATURE,
POLICY_BODY,
POLICY_RETURN_TYPE,
CREATED,
POLICY_COMMENT,
POLICY_ID,
DELETED
FROM SNOWFLAKE.ACCOUNT_USAGE.ROW_ACCESS_POLICIES
WHERE DELETED IS NULL
UNION
SELECT
POLICY_CATALOG,
POLICY_SCHEMA,
POLICY_NAME,
POLICY_OWNER,
'DATA_MASK' AS POLICY_TYPE,
POLICY_SIGNATURE,
POLICY_BODY,
POLICY_RETURN_TYPE,
CREATED,
POLICY_COMMENT,
POLICY_ID,
DELETED
FROM SNOWFLAKE.ACCOUNT_USAGE.MASKING_POLICIES
WHERE DELETED IS NULL
) ALL_POLICIES
WHERE POLICY_ID IN
(SELECT POLICY_ID
FROM SNOWFLAKE.ACCOUNT_USAGE.POLICY_REFERENCES
WHERE REF_DATABASE_NAME = '<database_name>')
OR POLICY_CATALOG IN ('<database_name>')
)
SELECT
LISTAGG(POLICY_OWNER, ', ') AS POLICY_OWNER,
POLICY_CATALOG AS CATALOG,
POLICY_SCHEMA AS SCHEMA,
POLICY_NAME,
POLICY_TYPE,
POLICY_SIGNATURE,
POLICY_BODY,
POLICY_RETURN_TYPE,
CREATED,
POLICY_COMMENT
FROM POLICIES_QUERIES
GROUP BY
POLICY_CATALOG,
POLICY_SCHEMA,
POLICY_NAME,
POLICY_TYPE,
POLICY_SIGNATURE,
POLICY_BODY,
POLICY_RETURN_TYPE,
CREATED,
POLICY_COMMENT;
Policy Links¶
SELECT
POLICY_DB AS CATALOG,
POLICY_SCHEMA AS SCHEMA,
POLICY_NAME,
CASE
WHEN POLICY_KIND = 'MASKING_POLICY' THEN 'DATA_MASK'
WHEN POLICY_KIND = 'ROW_ACCESS_POLICY' THEN 'ROW_ACCESS'
END AS POLICY_TYPE,
REF_DATABASE_NAME,
REF_SCHEMA_NAME,
REF_ENTITY_NAME,
REF_ENTITY_DOMAIN,
REF_COLUMN_NAME
FROM SNOWFLAKE.ACCOUNT_USAGE.POLICY_REFERENCES
WHERE POLICY_KIND IN ('MASKING_POLICY', 'ROW_ACCESS_POLICY')
AND REF_ENTITY_DOMAIN IN ('TABLE', 'VIEW', 'COLUMN', 'EXTERNAL TABLE')
AND REF_SCHEMA_NAME IN ('<schema_name_1>' , '<schema_name_2>' , '<schema_name_3>')
AND REF_DATABASE_NAME = '<database_name>';
Database Name Is Not Provided in JDBC URI¶
Policies¶
WITH POLICIES_QUERIES AS
(
SELECT * FROM
(
SELECT
POLICY_CATALOG,
POLICY_SCHEMA,
POLICY_NAME,
POLICY_OWNER,
'ROW_ACCESS' AS POLICY_TYPE,
POLICY_SIGNATURE,
POLICY_BODY,
POLICY_RETURN_TYPE,
CREATED,
POLICY_COMMENT,
POLICY_ID,
DELETED
FROM SNOWFLAKE.ACCOUNT_USAGE.ROW_ACCESS_POLICIES
WHERE DELETED IS NULL
UNION
SELECT
POLICY_CATALOG,
POLICY_SCHEMA,
POLICY_NAME,
POLICY_OWNER,
'DATA_MASK' AS POLICY_TYPE,
POLICY_SIGNATURE,
POLICY_BODY,
POLICY_RETURN_TYPE,
CREATED,
POLICY_COMMENT,
POLICY_ID,
DELETED
FROM SNOWFLAKE.ACCOUNT_USAGE.MASKING_POLICIES
WHERE
DELETED IS NULL
)
ALL_POLICIES
)
SELECT
LISTAGG(POLICY_OWNER, ', ') AS POLICY_OWNER,
POLICY_CATALOG AS CATALOG,
POLICY_SCHEMA AS SCHEMA,
POLICY_NAME,
POLICY_TYPE,
POLICY_SIGNATURE,
POLICY_BODY,
POLICY_RETURN_TYPE,
CREATED,
POLICY_COMMENT
FROM POLICIES_QUERIES
GROUP BY
POLICY_CATALOG,
POLICY_SCHEMA,
POLICY_NAME,
POLICY_TYPE,
POLICY_SIGNATURE,
POLICY_BODY,
POLICY_RETURN_TYPE,
CREATED,
POLICY_COMMENT;
Policy Links¶
SELECT
POLICY_DB AS CATALOG,
POLICY_SCHEMA AS SCHEMA,
POLICY_NAME,
CASE
WHEN POLICY_KIND = 'MASKING_POLICY' THEN 'DATA_MASK'
WHEN POLICY_KIND = 'ROW_ACCESS_POLICY' THEN 'ROW_ACCESS'
END AS POLICY_TYPE,
REF_DATABASE_NAME,
REF_SCHEMA_NAME,
REF_ENTITY_NAME,
REF_ENTITY_DOMAIN,
REF_COLUMN_NAME
FROM SNOWFLAKE.ACCOUNT_USAGE.POLICY_REFERENCES
WHERE
POLICY_KIND IN ('MASKING_POLICY', 'ROW_ACCESS_POLICY')
AND REF_ENTITY_DOMAIN IN ('TABLE', 'VIEW', 'COLUMN', 'EXTERNAL TABLE')
AND REF_SCHEMA_NAME IN ('<schema_name_1>', '<schema_name_2>', '<schema_name_3>')
AND REF_DATABASE_NAME IN ('<database_name_1>', '<database_name_2');