Skip to main content

Snowflake Documentation

Snapshot connection

With the Snapshot connection, you can manually download the metadata or telemetry data from your Snowflake account and upload this metadata to the Unravel Snowflake account. The Snapshot connection allows you to unlock Unravel features with one-time data access without any serverless cost or the need for account admin access from the Unravel Snowflake account. However, real-time monitoring is not available with this connection.

The download and upload data tasks can be executed using scripts. After uploading the data, you access the Unravel Snowflake account, detect the metadata, and start the data ingestion. The data for the last 14 days get ingested into the Unravel Snowflake account.

Also, see Choosing connection to Upload Snowflake dataChoosing a connection to upload Snowflake data

Prerequisites for Snapshot connection (Snowflake)

Ensure to complete the following prerequisites for metadata collection.

  • Grant SELECT privilege to the unravel user for schema (metadata uploaded schema).

  • Provide the Unravel user with the necessary permissions to use the warehouse (XS) for executing Unravel queries.

The following steps are involved in setting up an Unravel account.

1. Signing up for the Unravel Snowflake SaaS account

  1. Sign up for Unravel (Snowflake SaaS account).

    1. From the Unravel website, click Get Started for Free and then select Snowflake.

    2. In the Create a Free Account box, specify your email ID, and click Create Account.

      snowflake-saas-register.png

    You will receive a welcome email in your inbox.

  2. After your account details are set, you will receive another email with the following details. Keep these handy to access your Unravel UI and upload Snowflake metadata to Unravel Snowflake account.

    • Login credentials to access Unravel UI

    • Private account URL to access Unravel UI

    • Trial Snowflake account details which include the following:

      • Account login credentials

      • Snowflake Account

      • Role

      • Database

      • Schema

      • Warehouse

2. Uploading Snowflake metadata to Unravel Snowflake account

The Snowflake metadata can be uploaded using one of the following methods:

  • Download/upload scripts

    You can use the download scripts to download metadata from your Snowflake account and then run the upload scripts to upload the metadata to the Unravel Snowflake account.

    Note

    This is the recommended method to upload Snowflake metadata.

  • Snowflake-data-loader tool

    This tool can be used in Linux, MAC, and Windows. You can execute this tool and retrieve account usage information from your Snowflake account and upload it to the Unravel Snowflake account.

Uploading Snowflake metadata using download/upload scripts

The following scripts let you retrieve account usage information from your Snowflake account to the Unravel Snowflake account.

  • snowsql_download_data.sql:

  • snowsql_show_wareshouses.sql

  • snowflake_query.py

  • snowsql_upload_data.sql

The Snowflake metadata or telemetry data, warehouse data, and warehouse parameters data are downloaded/uploaded with these scripts.

Prerequisites for uploading data using down/upload scripts

  • Ensure to install the following before you start to download Snowflake metadata:

  • An account with access to creating state and file format.

  • Account user with permission to access the account_usage schema and all warehouses.

Uploading Snowflake metadata using download/upload scripts

Unravel provides the following download scripts that you can use to download Snowflake metadata, warehouse data, and warehouse parameters data.

  • snowsql_download_data.sql: Downloads Snowflake metadata or telemetry data.

  • snowsql_show_wareshouses.sql: Downloads Snowflake warehouse's data.

  • snowflake_query.py: Downloads Snowflake warehouse parameters

Unravel provides the following upload scripts to upload the downloaded Snowflake metadata to the Unravel Snowflake account:

  • snowsql_upload_data.sql

Do the following to download Snowflake metadata:

  1. Download the download/upload scripts from this location.

  2. Using SnowSQL, connect to a Snowflake account from where you want to download the Snowflake metadata. This account must have access to the creating stage and file format.

  3. Execute the snowsql_download_data.sql script with the required arguments to download the metadata from Snowflake #account_usage views. Refer to the following list of arguments for more details:

    snowsql -f /opt/script/snowsql_download_data.sql -d ${db} -s ${schema} -r ${role} -a ${account} -u ${user} -o variable_substitution=true -o log_file=${script output file path/filename} --variable path=$(local/path/to store/downloaded metadata) --variable stage_name=unravel_stage_name --variable file_format=unravel_file_format

    For example:

    snowsql -f /opt/script/snowsql_download_data.sql -d sf_source_database -s sf_schema -r sysadmin -a sf_account -u sf_user -o variable_substitution=true -o log_file=/opt/script/snowsql_download_data.log --variable path=/opt/download_path/ --variable stage_name=unravel_stage --variable file_format=unravel_file_format

    Parameter

    Description

    -o variable_substitution

    Enable the variable substitution switch in the script. Some variables are used in the script for which the values must be passed from the CLI. Set this to true.

    --variable path

    Specify the local path to store the downloaded Snowflake system metadata.

    --variable stage_name

    Specify the stage name, which is used to keep the temporary files for download and upload.

    --variable file_format

    Specify the file format name, which is used by upload/download scripts.

    -f

    Specify the file name of the script that is executed.

    -d

    Specify the database used for the script execution.

    -s

    Specify the name of the schema name used for the script execution.

    -r

    Specify the role of the user who executes the script.

    -a

    Specify the Snowflake account, which will be used for the script execution.

    -u

    Specify the username of the Snowflake user who executes the script.

    -w

    Specify the Snowflake warehouse.

    -o log_file

    Specify the path to the log file that will be generated when you execute the script.

    -o

    Provide the output-related arguments to get the logs in the specified path and format.

    After the script is executed, the Snowflake metadata gets downloaded to the specified location.

  4. Execute the snowsql_show_wareshouses.sql script with the required arguments to download the warehouse data. Refer to the following list of arguments for more details:

    snowsql -f /opt/script/snowsql_show_wareshouses.sql -d ${db} -s ${schema} -r ${role} -a ${account} -u ${user} -w ${warehouse}  -o output_format=csv -o output_file=${path}/warehouses.csv -o variable_substitution=true
    snowsql -f /opt/script/snowsql_show_wareshouses.sql -d database -s database-schema -r sysadmin -a useraccount -u user1 -w warehouse1  -o output_format=csv -o output_file=/opt/unravel/warehouses.csv -o variable_substitution=true

    Parameter

    Description

    -o variable_substitution

    Enable the variable substitution switch in the script. Some variables are used in the script for which the values must be passed from the CLI. Set this to true.

    -f

    Specify the file name of the script that is executed.

    -d

    Specify the database used for the script execution.

    -u

    Specify the username of the Snowflake user who executes the script.

    -s

    Specify the name of the schema name used for the script execution.

    -r

    Specify the role of the user who executes the script.

    -a

    Specify the Snowflake account, which will be used for the script execution.

    -o

    Provide the output-related arguments to get the logs in the specified path and format.

    -o output_file

    Specify the path to the output file generated on script execution.

    Note

    The output_file path should be the same as the path mentioned in the snowsql_download_data.sql command

    -o output_format

    Specify the output file format on script execution. This is in CSV format.

    After the script is executed, the warehouse data gets downloaded in CSV format at the specified output location.

  5. Execute the snowflake_query.py script with the required arguments to download the warehouse parameters data. Refer to the following list of arguments for more details:

    python3 /opt/script/snowflake_query.py --user '${user}' --password '${password}' --account '${account}' --warehouse '${warehouse}' --database '${db}' --schema '${schema}' --out '/opt/unravel' --role ${role}

    For example: 

    python3 /opt/script/snowflake_query.py --user 'sf_user' --password 'sf_password' --account 'sf_account' --warehouse 'sf_warehouse' --database 'sf_source_database' --schema 'sf_schema' --out '/opt/download_path' --role sysadmin

    Parameter

    Description

    --user

    Specify the name of the Snowflake user for the script execution.

    --password

    Specify the Snowflake user account passcode.

    --schema

    Specify the name of the schema that must be used for the script execution.

    --role

    Specify the role of the user.

    --account

    Specify the Snowflake account, which will be used for the script execution.

    --out

    Specify the path to the output folder.

    --database

    Specify the database used for the script execution.

    After the script is executed, the warehouse parameters data gets downloaded in CSV format at the specified output location.

Uploading metadata from the Snowflake account

Unravel provides download scripts that you can use to upload Snowflake metadata, warehouse data, and warehouse parameters data to the Unravel Snowflake account. The download scripts should be executed in SnowSQL with an account that has access to the following:

  • Creating a stage in the specified Snowflake database and schema

  • Creating file format in the specified snowflake database and schema.

Do the following to upload the Snowflake metadata:

  1. Download the snowsql_upload_data.sql from this location:

    https://github.com/unraveldata-org/snowflake-data-loader/tree/main/script

  2. Execute the snowsql_upload_data.sql script to upload the metadata to Unravel with the required arguments. Refer to the following list of arguments for more details:

    snowsql -f /opt/script/snowsql_upload_data.sql -d ${db} -s ${schema} -r ${role} -a ${account} -u ${user} -w ${warehouse} -o variable_substitution=true -o log_file=/opt/script/snowsql_upload_data.log --variable path=${path} --variable stage_name=unravel_stage_upload --variable file_format=unravel_file_format_upload
    snowsql -f /opt/script/snowsql_upload_data.sql -d unravel_sf_database -s unravel_sf_schema -r unravel_sf_role -a unravel_sf_account -u unravel_sf_user -w unravel_sf_warehouse -o variable_substitution=true -o log_file=/opt/script/snowsql_upload_data.log --variable path=/opt/download_path/ --variable stage_name=unravel_stage_upload --variable file_format=unravel_file_format_upload

    Parameter

    Description

    -o variable_substitution

    Enable the variable substitution switch in the script. Some variables are used in the script for which the values must be passed from the CLI. Set this to true.

    -o log_file

    Specify the name of the log file that must be generated on command execution.

    --variable path

    Specify the local path to where the snowflake system metadata is downloaded.

    --variable stage_name

    Specify the stage name, which is used to keep the temporary files for upload.

    --variable file_format

    Specify the file format name, which is used by the upload scripts.

    -f

    Specify the script file that you want to execute.

    -d

    Specify the Unravel provided Trial Snowflake Account database name.

    -u

    Specify the Unravel provided Trial Snowflake Account User name

    -s

    Specify the Unravel provided Trial Snowflake Account Schema name.

    -r

    Unravel provided Trial Snowflake Account Role

    -a

    Specify the Unravel provided Trial Snowflake Account name

    -o

    Provide the output-related arguments to get the logs in the specified path and format.

    After the script is executed, the Snowflake metadata gets uploaded in the specified format to the selected location.

Uploading Snowflake metadata using the snowflake-data-loader tool

This tool enables you to retrieve account usage information from one Snowflake account and upload it to a second account.

This tool does the following:

  • Creates a stage in the source Snowflake account.

  • Saves source Snowflake account usage to stage.

  • Downloads account usage information from the source Snowflake account to local.

  • Creates a stage in the target Snowflake account.

  • Uploads the account usage information to the target Snowflake account.

Prerequisites for using the snowflake-data-loader tool

The source account user must have the following permissions:

  • Access SNOWFLAKE.ACCOUNT_USAGE and SNOWFLAKE.INFORMATION_SCHEMA schema

  • Create Stage permission

  • Create File Format permission

Uploading Snowflake metadata using the snowflake-data-loader tool

Use a command line to run the snowflake-data-loader tool. You must pass the following arguments from the command line. Some of these are mandatory and some are optional arguments. You are prompted to address the missing mandatory arguments.

Mandatory arguments

Optional arguments

--source_user

Your source Snowflake account username.

--source_login_method

The login method for the source account. Possible options are password (default), oauth, sso, okta, or keypair.

--source_password

Your source Snowflake account password. If source_login_method is password, this argument is required.

--target_login_method

The login method for the target account. Possible options are password (default), oauth, sso, okta, or keypair.

--private_key_path

The path to your private key file. If source_login_method or target_login_method is keypair, this argument is required.

The key will be used for both source and target accounts.

--source_private_link

The private link for the source account for example: testaccount.us-east-1.privatelink.snowflakecomputing.com.

--source_account

Your source Snowflake account ID.

--target_private_link

The private link for the target account for example: testaccount.us-east-1.privatelink.snowflakecomputing.com.

--source_warehouse

The name of the source warehouse from where you want to retrieve details.

--source_okta_url

The okta URL for the source account for example: https://testaccount.okta.com.

--source_database

The name of the source account database where the stage is created.

--target_okta_url

The okta URL for the target account for example: https://testaccount.okta.com.

--source_schema

The name of the source account schema where the stage is created.

--source_passcode

Your source Snowflake account MFA password.

--source_role

The name of the source account role.

--target_passcode

Your target Snowflake account MFA password.

--target_user

Your target Snowflake account username.

--stage

The name of the stage. The default is unravel_stage.

--target_password

Your target Snowflake account password.

--out

The directory to save output files. The default is the current directory.

--target_account

Your target Snowflake account ID.

--file_format

The name of the file format. The default is unravel_file_format.

target_warehouse

The name of the target warehouse from where you want to retrieve details.

--debug

Prints debug messages when set.

target_database

The name of the target database.

--save-sql

This flag saves all queries as SQL files instead of running them.

target_schema

The name of the target schema.

--disable-cleanup

This will skip the local temporary file cleanup process.

--target_role

The name of the target role.

--look-back-days

The number of days to look back for account usage information. The default is 15 days.

If any of the required arguments are missing, you will be prompted to enter them.

The script will also replace - with _  for the value of --stage argument.

You can run the snowflake-data-loader tool on any of the following operating systems:

Linux

  1. Download the latest release of the snowflake-data-loader tool from the following location:

    https://github.com/unraveldata-org/snowflake-data-loader/releases

  2. Using a command line, execute the snowflake-data-loader script with the required arguments as follows: 

    ./snowflake-data-loader \
--source_login_method keypair \
--target_login_method password \
--source_user <source_user> \
--private_key_path <private_key_path> \
--source_account <source_account> \
--source_warehouse <source_warehouse> \
--source_database <source_database> \
--source_schema <source_schema> \
--source_role <source_role> \
--target_user <target_user> \
--target_password <target_password> \
--target_account <target_account> \
--target_warehouse <target_warehouse> \
--target_database <target_database> \
--target_schema <target_schema> \
--target_role <target_role>

    For example:

    ./snowflake-data-loader --source_user sf-user --source_account xyz12345.us-east-1 --source_warehouse unraveldata --source_database unraveldb --source_schema unravelschema --source_role SYSADMIN --target_user 6087e389979f870926a1dbf8d41d52b8 --target_password Cl9ATHlRf6mh --target_account ytb00868 --target_warehouse USER_WH --target_database TRIAL_DB --target_schema unravel_qayt00yvh1gru1p_sf --target_role 47572BE3A1393DDCE452900795978313
MAC

  1. Download the latest release of the snowflake-data-loader tool from the following location:

    https://github.com/unraveldata-org/snowflake-data-loader/releases

    Note

    In MAC, you may be prompted to trust the binary. Run the following command to trust the binary:

    xattr -d com.apple.quarantine  <path_to_the_binary_directory>/snowflake-data-loader

  2. Using a command line, execute the snowflake-data-loader script with the required arguments as follows: 

    ./snowflake-data-loader \
--source_login_method keypair \
--target_login_method password \
--source_user <source_user> \
--private_key_path <private_key_path> \
--source_account <source_account> \
--source_warehouse <source_warehouse> \
--source_database <source_database> \
--source_schema <source_schema> \
--source_role <source_role> \
--target_user <target_user> \
--target_password <target_password> \
--target_account <target_account> \
--target_warehouse <target_warehouse> \
--target_database <target_database> \
--target_schema <target_schema> \
--target_role <target_role>

    For example:

    ./snowflake-data-loader --source_user sf-user --source_account xyz12345.us-east-1 --source_warehouse unraveldata --source_database unraveldb --source_schema unravelschema --source_role SYSADMIN --target_user 6087e389979f870926a1dbf8d41d52b8 --target_password Cl9ATHlRf6mh --target_account ytb00868 --target_warehouse USER_WH --target_database TRIAL_DB --target_schema unravel_qayt00yvh1gru1p_sf --target_role 47572BE3A1393DDCE452900795978313
Windows

  1. Download the latest release of the snowflake-data-loader tool from the following location:

    https://github.com/unraveldata-org/snowflake-data-loader/releases

  2. Sign in to Windows with a password. Using a command line, execute the snowflake-data-loader script, with the required arguments from the list, as follows: 

    snowflake-data-loader.exe \
--source_user <source_user> \
--source_password <source_password> \
--source_account <source_account> \
--source_warehouse <source_warehouse> \
--source_database <source_database> \
--source_schema <source_schema> \
--source_role <source_role> \
--target_user <target_user> \
--target_password <target_password> \
--target_account <target_account> \
--target_warehouse <target_warehouse> \
--target_database <target_database> \
--target_schema <target_schema>

    For example:

    snowflake-data-loader.exe --source_user sf-user --source_account xyz1234.us-east-1 --source_warehouse UNRAVELDATA --source_database SEMI_STRUCTURED_DB --source_schema SEMI_STRUCTURED_SCHEMA --source_role sysadmin --target_user 3e5968c0661f5c37b51aca02bcf827e1 --target_password BNcc5LIIISjc --target_account ytb00868 --target_warehouse USER_WH --target_database TRIAL_DB --target_schema unravel_foqt3sagsf1k1q8_sf --target_role 47572BE3A1393DDCE452900795978313
3. Configuring Snowflake settings in Unravel

The following configurations must be set in the Unravel Snowflake account. For this, you must sign in to Unravel using the login credentials you have received in the email.

  1. Access the Unravel UI using the login credentials you received in the email when you created the Unravel Snowflake free account. The data fields are empty when you access Unravel UI for the first time.

  2. Click manage1.png on the upper right side and select Snowflake configuration. The Snapshot option is selected by default, and the details of the Unravel Snowflake account are pre-filled from the customer's Snowflake account.

  3. Click the Detect Metadata button to check the connection and table metadata access. After a successful connection and metadata access, you can start the data ingestion.

  4. Click Start data ingestion. The data ingestion process begins. The data for up to 14 days gets ingested into the Unravel Snowflake account.

  5. Click the Monitor data ingestion link. You can monitor the data ingestion status from the Data ingestion dashboard. All the dashboards will be visible under the Dashboard tab.

In this section: