Snowflake OCF Connector: Install and Configure¶
Alation Cloud Service Applies to Alation Cloud Service instances of Alation
Customer Managed Applies to customer-managed instances of Alation
Firewall Configuration¶
Open the inbound TCP port 443 to the Snowflake server.
Driver¶
The driver is included in the connector package and does not need to be installed separately from the connector. The driver information is available at the top of the Compose tab in your data source settings. You can view it after creating the data source when settings become available.
Authentication¶
For metadata extraction (MDE), profiling and sampling, and query log ingestion (QLI), the connector supports:
Basic authentication (username and password)
Key pair authentication
Both types of authentication require a service account.
In Compose, users can authenticate with:
Basic authentication (username and password)
OAuth 2.0 authentication flow.
You can find more information about Compose authentication in User Authentication For Data Sources.
Workflow¶
To install and configure the Snowflake OCF connector, use this workflow:
If you chose to authenticate with a key pair, Configure Key Pair Authentication
Collect the information for the JDBC URI
Create a Service Account¶
Under your Snowflake account, set up a user account for Alation. Alation uses the service account for metadata extraction (MDE), profiling and sampling, and query log ingestion (QLI). It must be assigned a role that has enough privileges for these extraction jobs.
Security and account administrators for Snowflake (users with the SECURITYADMIN or ACCOUNTADMIN roles) can create users.
Note
It is also possible to use an existing account.
Snowflake connection information requires a warehouse and a role. Snowflake users may have multiple roles which give them different access rights. As a best practice, we recommend setting a default role and a default warehouse for the service account user. The information on creating an account and required permissions is given below:
Create Role for Alation Service Account¶
USE ROLE ACCOUNTADMIN;
CREATE ROLE <alation_role>;
Create Alation Service Account User¶
USE ROLE ACCOUNTADMIN;
CREATE USER <alation_user> PASSWORD='*****' DEFAULT_ROLE = <alation_role> MUST_CHANGE_PASSWORD = TRUE;
GRANT ROLE <alation_role> TO USER <alation_user>;
ALTER USER <alation_user> SET ROWS_PER_RESULTSET=0
Note
We recommend setting the ROWS_PER_RESULTSET
parameter to 0
on the service account level to allow the connector to fetch all accessible metadata. This setting is relevant if a strict limit is set on the account level or if you’re not sure if such a limit exists on the account. With a strict limit applied via the ROWS_PER_RESULTSET
parameter, the connector may skip metadata during extraction, resulting in an incomplete extraction result.
Set Default Warehouse¶
USE ROLE ACCOUNTADMIN;
ALTER USER <alation_user> SET DEFAULT_WAREHOUSE=<warehouse_name>;
Grant Permissions for Metadata Extraction¶
1. Grant Access to Warehouse¶
Grant access to the warehouse from which you want to extract. Access to the warehouse is required even if it is the default warehouse of the service account.
USE ROLE ACCOUNTADMIN; GRANT USAGE ON WAREHOUSE <warehouse_name> TO ROLE <alation_role>;
2. Grant Access to ACCOUNT_USAGE¶
You can either grant access to all views in the ACCOUNT_USAGE schema or, if full access is not allowed, grant limited access using Snowflake database roles. Access to the ACCOUNT_USAGE schema enables metadata extraction (MDE) and query log ingestion (QLI).
Full Access to ACCOUNT_USAGE¶
Grant access to all views in the ACCOUNT_USAGE schema:
USE ROLE ACCOUNTADMIN; GRANT IMPORTED PRIVILEGES ON DATABASE "SNOWFLAKE" to <alation_role>;
Limited Access to ACCOUNT_USAGE¶
If you can only provide limited access to the ACCOUNT_USAGE schema, then grant the service account the SNOWFLAKE database roles of OBJECT_VIEWER and GOVERNANCE_VIEWER:
USE ROLE ACCOUNTADMIN; USE SNOWFLAKE; GRANT DATABASE ROLE OBJECT_VIEWER TO ROLE <alation_role>; GRANT DATABASE ROLE GOVERNANCE_VIEWER TO ROLE <alation_role>;Note
The database roles of OBJECT_VIEWER and GOVERNANCE_VIEWER grant access to these ACCOUNT_USAGE views:
- MDE:
OBJECT_VIEWER
SNOWFLAKE.ACCOUNT_USAGE.DATABASES
SNOWFLAKE.ACCOUNT_USAGE.SCHEMATA
SNOWFLAKE.ACCOUNT_USAGE.TABLES
SNOWFLAKE.ACCOUNT_USAGE.COLUMNS
SNOWFLAKE.ACCOUNT_USAGE.VIEWS
SNOWFLAKE.ACCOUNT_USAGE.FUNCTIONS
GOVERNANCE_VIEWER
SNOWFLAKE.ACCOUNT_USAGE.POLICY_REFERENCES
SNOWFLAKE.ACCOUNT_USAGE.ROW_ACCESS_POLICIES
SNOWFLAKE.ACCOUNT_USAGE.MASKING_POLICIES
SNOWFLAKE.ACCOUNT_USAGE.TAGS
SNOWFLAKE.ACCOUNT_USAGE.TAG_REFERENCES
- QLI:
GOVERNANCE_VIEWER
SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
3. Grant Access to Databases, Schemas, and Tables¶
Grant the service account access to the database(s), schema(s), and table(s) that you want to be cataloged in Alation after metadata extraction:
Grant USAGE on Specific Database(s)¶
GRANT USAGE ON DATABASE <database> TO <alation_role>;
Grant USAGE on Specific Schema(s)¶
Grants limited by database
GRANT USAGE ON ALL SCHEMAS IN DATABASE <database> TO ROLE <alation_role>;Grants limited by a named schema
GRANT USAGE ON SCHEMA <database.schema> TO ROLE <alation_role>;Optional: grant access to future schemas in database
GRANT USAGE ON FUTURE SCHEMAS IN DATABASE <database> TO ROLE <alation_role>;
Grant REFERENCES on Specific Table(s)¶
Grants limited by database
GRANT REFERENCES ON ALL TABLES IN DATABASE <database> TO ROLE <alation_role>;Grants limited by a named schema
GRANT REFERENCES ON ALL TABLES IN SCHEMA <database.schema> TO ROLE <alation_role>;Optional: grant access to future tables in database
GRANT REFERENCES ON FUTURE TABLES IN DATABASE <database> TO ROLE <alation_role>;Optional: grant access to future tables in a specific schema
GRANT SELECT ON FUTURE TABLES IN SCHEMA <database.schema> TO ROLE <alation_role>;
Grant REFERENCES on Specific View(s)¶
Grants limited by database
GRANT REFERENCES ON ALL VIEWS IN DATABASE <database> TO ROLE <alation_role>;Grants limited by a named schema
GRANT REFERENCES ON ALL VIEWS IN SCHEMA <database.schema> TO ROLE <alation_role>;Optional: grant access to future views in database
GRANT REFERENCES ON FUTURE VIEWS IN DATABASE <database> TO ROLE <alation_role>;Optional: grant access to future views in a specific schema
GRANT SELECT ON FUTURE VIEWS IN SCHEMA <database.schema> TO ROLE <alation_role>;
Grant Access to External Tables¶
Alation supports extraction of external tables on Amazon S3 and Azure Storage.
Note
Granting USAGE on databases and schemas is a prerequisite to extracting external tables.
Grant access to external tables, limited by database
GRANT REFERENCES ON ALL EXTERNAL TABLES in DATABASE <database> TO ROLE <alation_role>;Grant access to external tables, limited by a named schema
GRANT REFERENCES ON ALL EXTERNAL TABLES IN SCHEMA <database.schema> TO ROLE <alation_role>;Grant USAGE on stages used in external tables, limited by database
GRANT USAGE ON ALL STAGES IN DATABASE <database> TO ROLE <alation_role>;Grant USAGE on stages used in external tables, limited by a named schema
GRANT USAGE ON ALL STAGES IN SCHEMA <database.schema> TO ROLE <alation_role>;Note
An external table can be created from a stage belonging to a different database. Make sure you provide access to all required stages of all required databases.
Optional: grant access to future external tables in a database
GRANT REFERENCES ON FUTURE EXTERNAL TABLES in DATABASE <database> TO ROLE <alation_role>;Optional: grants access to future external tables in a schema
GRANT REFERENCES ON FUTURE EXTERNAL TABLES IN SCHEMA <database.schema> TO ROLE <alation_role>;
4. Policy and Tag Extraction¶
Permissions for Policy and Tag Extraction¶
Permissions you have granted on the ACCOUNT_USAGE views are enough for policy and tag extraction.
Permissions for Policy and Tag Synchronization¶
Grant access for policy synchronization
GRANT APPLY MASKING POLICY ON ACCOUNT TO <alation_role>;Grant access for tag synchronization
GRANT APPLY TAG ON ACCOUNT TO <alation_role>;
Enable Policy Extraction¶
Policy extraction requires additional configuration on the Alation server. You can enable it using alation_conf.
On customer-managed (on-premise) Alation instances, enable data policy extraction by setting these alation_conf flags to True
:
alation.ocf.mde.policy.enable_extraction
alation.ocf.mde.policy.enable_ingestion
alation.ocf.mde.policy.enable_search
After changing the parameters, restart Alation Supervisor with the command alation_supervisor restart all
.
On how to use alation_conf, see Using alation_conf.
On Alation Cloud Service instances, Alation Support will adjust the alation_conf flag values to activate specific features. Submit a Support ticket to Alation to make these changes to your instance.
For more on working with policies extracted into the catalog, see Policy Center.
Note
Only policies attached to tables, columns, and views are extracted and shown in Alation. Policies linked to tags are not extracted. On connector versions before 1.1.5, metadata extraction exits with an error when it encounters a policy linked to a tag during ingestion. Connector versions 1.1.5 or newer will skip policies linked to tags and successfully extract policies attached to tables, columns, and views.
Enable Tag Extraction¶
Tag extraction requires additional configuration on the Alation server. You can enable it using alation_conf.
On customer-managed (on-premise) Alation instances, enable Snowflake tag extraction by setting these alation_conf flags to True
:
alation.feature_flags.enable_snowflake_tags
alation.ocf.mde.custom_field.enable_extraction
alation.ocf.mde.custom_field.enable_ingestion
After changing the parameters, restart Alation Supervisor with the command alation_supervisor restart all
.
On how to use alation_conf, see Using alation_conf.
On Alation Cloud Service instances, Alation Support will adjust the alation_conf flag values to activate specific features. Submit a Support ticket to Alation to make these changes to your instance.
By default, Alation will extract:
All tags if the database name is not specified in the JDBC URI.
All the tags which have references in the database if the database name is specified in the JDBC URI.
For more on the format of the Snowflake JDBC URI, refer to JDBC URI.
If you want to extract more specific tags, use a custom query for tag extraction. See Customizing MDE below.
For information about working with Snowflake tags in Alation, refer to Snowflake Tags in Alation.
5. Function Extraction¶
Grants limited by database
GRANT USAGE ON ALL FUNCTIONS IN DATABASE <database> TO ROLE <alation_role>;Grants limited by a schema
GRANT USAGE ON ALL FUNCTIONS IN SCHEMA <database.schema> TO ROLE <alation_role>;
Grant Permissions for Profiling¶
Profiling requires SELECT permissions for the tables you are going to profile:
GRANT SELECT ON schema_name.table_name TO <alation_role>;
Grant Permissions for Query Log Ingestion¶
Alation supports query log ingestion (QLI) based on the ACCOUNT_USAGE.QUERY_HISTORY view of the SNOWFLAKE database.
QLI can either use a dedicated view (table-based QLI) or a query (default or query-based QLI).
For table-based QLI, you’ll need to create a view on top of the SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY view and grant the Alation service account these permissions:
USAGE on the database and schema where the view is created.
SELECT on the created view.
For default and query-based QLI, the permissions on the ACCOUNT_USAGE.QUERY_HISTORY view that you’ve granted the service account are enough.
See Query Log Ingestion for more information on the types of QLI, including the information on how to create a QLI view for table-based QLI.
Configure Key Pair Authentication¶
Alation supports the key pair authentication method for metadata extraction, profiling and sampling, and query log ingestion.
Note
Key pair authentication for individual user account connections from Compose is currently not supported.
Key pair authentication for Snowflake OAuth in Compose is currently not supported. Snowflake OAuth uses authentication based on a client ID and secret.
Prerequisite¶
You have created a service account and granted it the required permissions.
Configuration in Snowflake¶
To provide a key pair:
Generate a private key and a public key. Refer to Key Pair Authentication & Key Pair Rotation in Snowflake documentation for details. Optionally, you can generate a private key with a passphrase.
Assign the public key to the service account you created for Alation.
Store the private key and its passphrase securely on your local machine. They will need to be entered in Alation when configuring key pair authentication in the settings of your Snowflake data source.
JDBC URI¶
URI Format¶
snowflake://<hostname>:<port>/?warehouse=<warehouse_name>&db=<db_name>&role=<role_name>
Example¶
snowflake://my_account.us-east-1.snowflakecomputing.com:443/?warehouse=TEST&db=TEST_DB&role=alation-role
URI Parameters¶
When building the URI, include the following components:
Hostname¶
Mandatory.
For instances on AWS:
<account_name>.<region_id>.snowflakecomputing.com
Example:
my_account.us-east-1.snowflakecomputing.com
For instances on Azure:
<account_name>.<region_id>.azure.snowflakecomputing.com
Example:
lj17605.east-us-2.azure.snowflakecomputing.com
For instances on Google Cloud:
<account_name>.<region_id>.gcp.snowflakecomputing.com
Example:
mj27441.europe-west2.gcp.snowflakecomputing.com
Port¶
Mandatory.
Specify port number 443.
Warehouse Name¶
warehouse
The currently active warehouse in your account for which the service account has access privileges.
Optional if a default warehouse has been assigned to the role of the service account. If not, mandatory.
Role¶
role
Optional.
Provide the role of the service account user.
Database Name¶
db
Optional but recommended.
If you don’t include this parameter, Alation will extract metadata from all databases that the service account can access. The same applies to policies. If you are going to extract policies, this parameter is recommended as it limits policy extraction to a specific database.
When users run DDL queries in Compose, Alation can incrementally ingest the corresponding metadata into the catalog. Both the service account connection JDBC URI and the Compose connection URI must include the database name (db
) parameter to enable Alation to ingest metadata from objects created through Compose.
Important
The value of the parameter
db
is case sensitive, as Snowflake supports case sensitive database names. Ensure that you use the right case when adding this parameter.For example, you can have a database with the name test and a database with the name TEST in Snowflake.
For the database with the name in the lower case, the URI will look like this:
snowflake://alation_partner.us-east-1.snowflakecomputing.com:443/?warehouse=compute_wh&db=test
.For the database with the name in the upper case, the URI will look like this:
snowflake://alation_partner.us-east-1.snowflakecomputing.com:443/?warehouse=compute_wh&db=TEST
.To determine the correct casing of the database name, you can use this query:
SELECT DATABASE_NAME AS CATALOG FROM SNOWFLAKE.ACCOUNT_USAGE.DATABASES WHERE DELETED IS NULL ORDER BY DATABASE_NAME;
Installation and Configuration¶
STEP 1: Install the Connector¶
Alation On-Premise¶
Important
Installation of OCF connectors requires Alation Connector Manager to be installed as a prerequisite.
To install an OCF connector:
If this has not been done on your instance, install the Alation Connector Manager: Install Alation Connector Manager.
Ensure that the OCF connector Zip file is available on your local machine.
Install the connector on the Connectors Dashboard page using the steps in Manage Connectors.
Alation Cloud Service¶
Note
On Alation Cloud Service instances, Alation Connector Manager is available by default.
Depending on your network configuration, you may need to use Alation Agent to connect to databases.
Connection via Alation Agent¶
Ensure that Alation Agent is enabled on your Alation instance. If necessary, create a Support ticket with Alation for an Alation representative to enable the Alation Agent feature on your instance and to receive the Alation Agent installer.
Connection Without Agent¶
To install an OCF connector:
Ensure that the OCF connector Zip file is available on your local machine.
Install the connector on the Connectors Dashboard page using the steps in Manage Connectors.
STEP 2: Create and Configure a New Snowflake Data Source¶
In Alation, add a new data source:
Log in to Alation as a Server Admin.
Expand the Apps menu on the right of the main toolbar and select Sources.
On the Sources page, click +Add on the top right of the page and in the list that opens, click Data Source. This will open the Add a Data Source wizard.
On the first screen of the wizard, specify a name for your data source, assign additional Data Source Admins, if necessary, and click the Continue Setup button on the bottom. The Add a Data Source screen will open.
On the Add a Data Source screen, the only field you should populate is Database Type. From the Database Type dropdown, select the connector name. After that you will be navigated to the Settings page of your new data source.
The name of this connector is Snowflake OCF connector.
Access¶
On the Access tab, set the data source visibility using these options:
Public Data Source—The data source will be visible to all users of the catalog.
Private Data Source—The data source will be visible to the users allowed access to the data source by Data Source Admins.
You can add new Data Source Admin users in the Data Source Admins section.
General Settings¶
Note
This section describes configuring settings for credentials and connection information stored in the Alation database. If your organization has configured Azure KeyVault or AWS Secrets Manager to hold such information, the user interface for the General Settings page will change to include the following icons to the right of most options:
By default, the database icon is selected, as shown. In the vault case, instead of the actual credential information, you enter the ID of the secret. See Configure Secrets for OCF Connector Settings for details.
Application Settings¶
Specify Application Settings if applicable. Click Save to save the changes after providing the information.
Parameter |
Description |
---|---|
BI Connection Info |
This parameter is used to generate lineage between the current data source and another source in the catalog, for example a BI source that retrieves data from the underlying database. The parameter accepts host and port information of the corresponding BI data source connection. Use the following format: You can provide multiple values as a comma-separated list:
Find more details in BI Connection Info. |
Disable Automatic Lineage Generation |
Select this checkbox to disable automatic lineage generation from QLI, MDE, and Compose queries. By default, automatic lineage generation is enabled. |
Connector Settings¶
Populate the data source connection information and save the values by clicking Save in each section you fill out.
Data Source Connection¶
Parameter
Description
JDBC URI
Specify the JDBC URI.
Username
Provide the service account username.
Password
Provide the service account password.
Snowflake Key Pair Authentication¶
Parameter
Description
Enable Snowflake Key Pair Auth
Select this checkbox to enable the key pair authentication.
Username
Provide the username of the service account that has the public key assigned to it.
Upload Private Key File
Click the Upload button to upload the private key file.
Private Key Passphrase
Provide the passphrase of the private key if you created it.
Logging Information¶
Select a logging level for the connector logs and save the values by clicking Save in this section. The available log levels are based on the Log4j framework.
You can view the connector logs in Admin Settings > Server Admin > Manage Connectors > Snowflake OCF connector.
Obfuscate Literals¶
Enable this toggle to hide the literal values from queries on the Queries tab on schema and table catalog pages. Disabled by default.
Test Connection¶
After specifying the connector settings, under Test Connection, click Test to validate network connectivity.
Troubleshoot Connection¶
You may see the errors below while connecting to Snowflake using key pair authentication. The errors are specific to OpenSSL version 1.1.1g on Windows or Ubuntu.
Error messages:
Private key provided is invalid or not supported: rsa_key.p8: ObjectIdentifier() – data isn’t an object ID (tag = 48) null
Exception in thread “main” java.security.NoSuchAlgorithmException: 1.2.840.113549.1.5.13 SecretKeyFactory not availableWhen run from JDBC application.
To resolve this issue, use the following command to generate the private key:
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -v1 PBE-SHA1-RC4-128 -out rsa_key.p8
Add-On OCF Connector for dbt¶
The dbt connector can be used as an add-on with another OCF connector to extract and catalog descriptions and lineage from dbt models, sources, and columns in dbt Core or dbt Cloud. See Add-On OCF Connector for dbt on how to use this connector.
Metadata Extraction¶
You can configure metadata extraction (MDE) for an OCF data source on the Metadata Extraction tab of the Settings page. Refer to Configure Metadata Extraction for OCF Data Sources for information about the available configuration options.
If the database name is included into the JDBC URI, then metadata will be extracted only from that database. If the database name is not included in the JDBC URI, then all metadata that the service account has access to will be extracted, which may increase extraction time.
There is 45 minutes to 3 hour latency time to update tables, columns, views, primary keys, foreign keys, functions, and procedures in ACCOUNT_USAGE views. The new items created in databases can be extracted into the catalog after this latency time. Refer to Account Usage — Snowflake Documentation for information about the latency time for each item.
Customizing MDE¶
You can perform metadata extraction (MDE) based on default queries or configure it to use custom queries.
Note
From Snowflake OCF connector version 1.2.0.5882, you can customize tag extraction:
Query examples for tag extraction—Connector versions 2.0.0 or newer.
Query examples for tag extraction—Connector versions before 2.0.0.
Compose¶
For details about configuring the Compose tab of the Settings, refer to Configure Compose for OCF Data Sources.
On how to configure OAuth authentication for Compose, refer to Snowflake OAuth For Compose.
Incremental Ingestion of Compose Queries¶
When users run DDL queries in Compose, Alation incrementally ingests the corresponding metadata into the catalog. There are some requirements for the JDBC URI and the use of object names that ensure that Alation can incrementally ingest queries from Compose:
Both the service account connection JDBC URI and the Compose connection URI must include the database name parameter (
db
).To ensure that tables and views created via Compose are ingested into the catalog, use fully qualified table names, for example:
Creating a table:
CREATE TABLE <DATABASE>.<SCHEMA>.<TABLE> ({column properties});Creating a view:
USE <DATABASE>; CREATE VIEW <SCHEMA>.<VIEW> AS {view condition};
Data Sampling¶
Sampling and profiling is supported. For details, see Configure Sampling and Profiling for OCF Data Sources.
Note
From Snowflake OCF connector version 1.0.1, dynamic sampling and profiling uses the Compose URI. This applies for both basic and OAuth authentication methods.
Query Log Ingestion¶
You configure query log ingestion (QLI) on the Query Log Ingestion tab of the Settings page. The OCF Snowflake connector supports three configurations for QLI:
Default QLI¶
The default QLI feature is available from Snowflake OCF connector version 1.2.1. Default QLI does not require specifying a QLI view name or a QLI query. Just specify a date range for QLI and run or schedule the QLI job. Alation will run a default QLI query to retrieve query history.
For default QLI to succeed, ensure that the service account has enough permissions to select from the system view. See Grant Permissions for Query Log Ingestion for details.
On how to run or schedule QLI, see Perform QLI.
Default QLI Query¶
Note
In the query below, the placeholder parameters
STARTTIME
andENDTIME
will be substituted by Alation with the start and end dates of the QLI range selected in the user interface when QLI is run manually or on schedule.
SELECT
user_name AS "userName",
CASE
WHEN SCHEMA_NAME IS NULL
THEN DATABASE_NAME || '.' || ''
ELSE DATABASE_NAME || '.' || SCHEMA_NAME
END AS "defaultDatabases",
TRIM(QUERY_TEXT) AS "queryString",
TRIM(SESSION_ID) AS "sessionID",
ROUND(TOTAL_ELAPSED_TIME / 1000, 0) AS "seconds",
FALSE AS "cancelled",
TO_CHAR(start_time, 'YYYY-MM-DD HH:MI:SS.US') AS "startTime",
TO_CHAR(start_time, 'YYYY-MM-DD HH:MI:SS.US') AS "sessionStartTime"
FROM
SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE
START_TIME BETWEEN STARTTIME AND ENDTIME
AND execution_status = 'SUCCESS' ;
Table-Based QLI¶
Prerequisite¶
In Snowflake, create a view on top of the SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY view. Create it in any suitable schema. You can give the view any name of your choice. After creating the view, grant the Alation service account the SELECT access to this view (see Grant Permissions for Query Log Ingestion).
CREATE VIEW alation_QLI_view AS
SELECT
user_name as "userName",
CASE
WHEN SCHEMA_NAME IS NULL
THEN DATABASE_NAME ||'.'|| ''
ELSE DATABASE_NAME ||'.'|| SCHEMA_NAME
END AS "defaultDatabases",
TRIM(QUERY_TEXT) AS "queryString",
TRIM(SESSION_ID) AS "sessionID",
ROUND(TOTAL_ELAPSED_TIME/1000,0) AS "seconds",
false AS "cancelled",
TO_CHAR(start_time,'YYYY-MM-DD HH:MI:SS.US') AS "startTime",
TO_CHAR(start_time,'YYYY-MM-DD HH:MI:SS.US') AS "sessionStartTime"
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE execution_status = 'SUCCESS';
Configure Table-Based QLI¶
To configure table-based QLI:
Open the Query Log Ingestion tab of the Settings page of your OCF Snowflake data source.
Under Connector Settings > Query Extraction, in the Table Name field, specify the name of the QLI view.
Important
Use the format
schema_name.view_name
.Click Save.
Custom Query-Based QLI¶
If you cannot create the view for QLI, for example, due to access restrictions, you can use a custom QLI query to extract query history into Alation. If you opt for custom query-based QLI, Alation will query the system view storing query history directly every time you manually run QLI or when the QLI job runs on schedule.
For custom query-based QLI to succeed, ensure that the service account has enough permissions to select from the system view. See Grant Permissions for Query Log Ingestion for details.
Find an example of a custom query for QLI below. You can customize it by adding, removing, or changing the filter, but the columns and their aliases must remain as is since the connector expects this query structure.
Note
When using the QLI query example, do not substitute the
STARTTIME
andENDTIME
parameters in the WHERE filter. These parameters are not actual column names and should stay as is. They are expected by the connector and will be substituted with the start and end date of the QLI range selected in the user interface when QLI is run manually or on schedule.Substitute the placeholder
<database_name>
with a database name.SELECT user_name AS "userName", CASE WHEN SCHEMA_NAME IS NULL THEN DATABASE_NAME || '.' || '' ELSE DATABASE_NAME || '.' || SCHEMA_NAME END AS "defaultDatabases", TRIM(QUERY_TEXT) AS "queryString", TRIM(SESSION_ID || '/' || USER_NAME) AS "sessionID", ROUND(TOTAL_ELAPSED_TIME / 1000, 0) AS "seconds", FALSE AS "cancelled", to_char( start_time, 'YYYY-MM-DD HH:MI:SS.US' ) AS "startTime", to_char( start_time, 'YYYY-MM-DD HH:MI:SS.US' ) AS "sessionStartTime" FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY WHERE START_TIME BETWEEN STARTTIME AND ENDTIME AND EXECUTION_STATUS = 'SUCCESS' AND NOT ( QUERY_TEXT ILIKE 'SHOW%' OR QUERY_TEXT ILIKE 'CREATE%SCHEMA%' OR QUERY_TEXT ILIKE 'CREATE%DATABASE%' OR QUERY_TEXT ILIKE 'GRANT%' OR QUERY_TEXT ILIKE 'GET%' OR QUERY_TEXT ILIKE 'DROP%DATABASE%' OR QUERY_TEXT ILIKE 'REVOKE%' OR QUERY_TEXT ILIKE 'DESC%' OR QUERY_TEXT ILIKE 'CREATE%PROCEDURE%' OR QUERY_TEXT ILIKE 'LIST%' OR QUERY_TEXT ILIKE 'CALL%' OR QUERY_TEXT ILIKE 'PUT_FILES%' OR QUERY_TEXT ILIKE 'REMOVE_FILES%' OR QUERY_TEXT ILIKE 'EXPLAIN%' OR QUERY_TEXT ILIKE 'TRUNCATE%' OR QUERY_TEXT ILIKE 'COMMIT%' ) AND DATABASE_NAME not in ('SNOWFLAKE') AND DATABASE_NAME = '<database_name>';
Configure Custom Query-Based QLI¶
To configure query-based QLI:
Go to the Query Log Ingestion tab of the Settings page of your OCF data source.
Under Connector Settings > Query Extraction, in the Custom QLI Query field, provide the QLI query.
Click Save.
Perform QLI¶
You can either run QLI manually on demand or configure it to run automatically on a schedule.
Run QLI Manually¶
To perform QLI manually on demand:
On the Query Log Ingestion tab of the Settings page of your OCF Snowflake data source, under Automated and Manual Query Log Ingestion ensure that the Enable Automated Query Log Ingestion toggle is disabled.
Specify the desired date range using the Date Range calendar widgets. You will need to specify the start date and the end date separately.
Click the Preview button to preview the queries that will be ingested. This will run a preview job that will fetch a subset of the queries based on your QLI configuration. The result will be displayed in a table.
Click the Import button to run QLI manually. This will run a query log ingestion job.
View Job Status¶
The status of preview or QLI jobs is logged in the Query Log Ingestion Job Status table at the bottom of the page. In this table, click the status link or the View Details link for a job to view the details on the progress of this job.
Schedule QLI¶
To schedule QLI :
On the Query Log Ingestion tab, under Automated and Manual Query Log Ingestion, enable the Enable Automated Query Log Ingestion toggle.
In the Automated Query Log Ingestion Time panel that will appear, specify values for the job recurrence and time. The values are set in your local time.
The next QLI will run on the schedule you set.
Troubleshooting Query Log Ingestion¶
Problem¶
During custom query-based QLI, the preview data does not display any results and the log message displays error Missing result set column: Username.
Solution¶
Use the following steps:
Sign in to Snowflake with an admin account.
If
ALTER USER SET QUOTED_IDENTIFIERS_IGNORE_CASE is ``true
, set it tofalse
.In Alation, click Preview for QLI one more time and see if it runs successfully. If the issue persists, contact Alation support.
Lineage¶
The Snowflake data source supports table-level and column-level lineage. Table-level lineage is available by default. Column-level lineage is an additional paid feature that requires the Snowflake column-level lineage parser add-on. Contact Alation about purchasing this add-on.
Enable Column-Level Lineage¶
To enable column-level lineage:
Go to Settings > Feature Configuration.
Activate the toggle Automatically extracts Column Level Lineage for Snowflake data sources.
Click the Save changes button to save the changes.
In the Verify Feature Configuration Change dialog, click the Save Configuration button.
View Column-Level Lineage¶
When you enable the column-level lineage add-on, Alation will automatically generate column-level lineage data based on the existing metadata that was previously extracted with MDE, QLI, or ingested from Compose.
Note
Once the feature flag is enabled, the column-level lineage will be automatically generated only for the view definitions that are complete and already added to table-level lineage.
Complete view definition:
CREATE VIEW adbc_database_01.query_execution_main.table_view AS SELECT src.* FROM (SELECT col1, col2, col3 FROM tpch.sf1.customer LIMIT 10) src;Incomplete view definition:
SELECT src.* FROM (SELECT col1, col2, col3 FROM tpch.sf1.customer LIMIT 10) src;
More column-level lineage data will be created after you perform metadata extraction (MDE), query log ingestion (QLI), and (or) use Compose to run queries that create or update column objects.
To view column-level lineage data:
Go to the Lineage tab on the catalog page of a table object that should have column-level lineage.
Expand the table or view lineage node to see the columns of the table or view and links to the dataflow object.
When you expand a table or view lineage node and click one of the child columns, the column-level lineage link for that particular column becomes highlighted.
Note
If the column-level lineage feature flag is disabled, the column-level lineage links that were generated previously are not removed. Only the future queries and query history will not be processed.
Troubleshooting¶
Refer to Troubleshooting.