Snowflake Assessment

Overview

This section explains how to extract the necessary information for running Assessment with Snowflake.

DWH Dumper

The extraction is done using the DWH Dumper tool which is a part of this project. To obtain the tool, download the ZIP from Releases.

Lite Extraction

This section covers the simplest case of using DWH Dumper for Snowflake.

Data will be collected using the snowflake-lite connector. This is a standalone connector, meaning that no other connector needs to be used with it.

Guide steps

1. Start with the following command template (copy it into an editor):

EXECUTABLE \
    --assessment \
    --connector snowflake-lite \
    --host HOST_NAME \
    --user USER_NAME \
    --role ROLE_NAME \
    --warehouse WAREHOUSE \
    --private-key-file PRIVATE_KEY_PATH \
    --private-key-password PRIVATE_KEY_PASSWORD

2. Provide the values:

• EXECUTABLE - the path to your dwh-migration-dumper-starter executable.

• HOST_NAME - the host name of your Snowflake instance. You can find this information in your Snowflake Account dashboard. It should be of the form “<account_name>.<region_id>.snowflakecomputing.com”.

• USER_NAME - username used for database connection. The specified user must have the “IMPORTED PRIVILEGES” access to the SNOWFLAKE database. You can refer to Snowflake Documentation for how to obtain access.

• ROLE_NAME - (Optional) the user role when running the dwh-migration-dumper tool—for example, ACCOUNTADMIN.

• WAREHOUSE - The warehouse used to run extraction. If you have multiple virtual warehouses, you can specify any of them. As long as you run the tool as ACCOUNTADMIN or a role with IMPORTED PRIVILEGES, you will get information from all the warehouses.

• PRIVATE_KEY_PATH - The path to the RSA private key used for authentication.

• PRIVATE_KEY_PASSWORD - The password that was used when creating the RSA private key. It is required only if the private key is encrypted.

3. Run the command. When it completes, this scenario is done and you will have successfully extracted data using the snowflake-lite connector.

Metadata

This is an alternative to the basic extraction of snowflake-lite. The connector used is snowflake. Because the snowflake connector is not standalone, it needs to be combined with query logs to obtain all necessary data.

An example command to extract metadata (without query logs) looks like this:

EXECUTABLE \
    --assessment \
    --connector snowflake \
    --host HOST_NAME \
    --user USER_NAME \
    --role ROLE_NAME \
    --warehouse WAREHOUSE \
    --private-key-file PRIVATE_KEY_PATH \
    --private-key-password PRIVATE_KEY_PASSWORD

Query Logs

Since we used the snowflake connector, time series data was not extracted. We need to run DWH Dumper again, which will extract the time series data together with query logs.

The connector to use for query logs and time series data is snowflake-logs. The command looks like this:

EXECUTABLE \
    --assessment \
    --connector snowflake-logs \
    --host HOST_NAME \
    --user USER_NAME \
    --role ROLE_NAME \
    --warehouse WAREHOUSE \
    --query-log-start STARTING_DATE \
    --query-log-end ENDING_DATE \
    --private-key-file PRIVATE_KEY_PATH \
    --private-key-password PRIVATE_KEY_PASSWORD

Appendix: connector parameter details

This is a list of help texts for connectors that officially support the --assessment flag.

Note that not all parameters are necessary and you should generally avoid using a parameter unless it is recommended by a guide or you have a very good understanding of how a given parameter is handled by the implementation.

The same items (together with other connectors) will be printed if you download the Dumper and run it with no arguments.

snowflake-lite - Extracts data for the lite version of Snowflake assessment.

        --driver     The vendor-supplied driver JARs to use for this connector.
        --host       (default: localhost) The hostname of the database server. (Required if --url is not specified.)
        --user       The username for the database connection. (Required on most systems.)
        --password   The password for the database connection. (Required If not specified as an argument, will use a secure prompt..)
        --private-key-password Private Key file password. (Required If the private key file is encrypted..)
        --private-key-file Path to the Private Key file used for authentication. (Required If the database user uses keypair authentication..)
        --role       The Snowflake role to use for authorization.
        --warehouse  The Snowflake warehouse to use for processing metadata queries.
        --url        Overrides the generated JDBC URI for the database connection. (Required only for complex installations.)

snowflake-logs - Dumps logs from Snowflake.

        --driver     The vendor-supplied driver JARs to use for this connector.
        --host       (default: localhost) The hostname of the database server. (Required if --url is not specified.)
        --user       The username for the database connection. (Required on most systems.)
        --password   The password for the database connection. (Required If not specified as an argument, will use a secure prompt..)
        --private-key-password Private Key file password. (Required If the private key file is encrypted..)
        --private-key-file Path to the Private Key file used for authentication. (Required If the database user uses keypair authentication..)
        --role       The Snowflake role to use for authorization.
        --warehouse  The Snowflake warehouse to use for processing metadata queries.
        --database   The name of the database to connect to for making queries.
        --url        Overrides the generated JDBC URI for the database connection. (Required only for complex installations.)
        --query-log-start Start date for query history to dump
        --query-log-days The number of days of query history to dump.
        --query-log-end End date for query history to dump
        -Dsnowflake.logs.query=value            Custom query for log dump.
        -Dsnowflake.logs.where=value            Custom where condition to append to query for log dump.
        -Dsnowflake.warehouse_events_history.query=value                Custom query for warehouse events history dump
        -Dsnowflake.automatic_clustering_history.query=value            Custom query for automatic clustering history dump
        -Dsnowflake.copy_history.query=value            Custom query for copy history dump
        -Dsnowflake.database_replication_usage_history.query=value              Custom query for database replication usage history dump
        -Dsnowflake.login_history.query=value           Custom query for login history dump
        -Dsnowflake.metering_daily_history.query=value          Custom query for metering daily history dump
        -Dsnowflake.pipe_usage_history.query=value              Custom query for pipe usage history dump
        -Dsnowflake.query_acceleration_history.query=value              Custom query for query acceleration history dump
        -Dsnowflake.replication_group_usage_history.query=value         Custom query for replication group usage history dump
        -Dsnowflake.serverless_task_history.query=value         Custom query for serverless task history dump
        -Dsnowflake.task_history.query=value            Custom query for task history dump
        -Dsnowflake.warehouse_load_history.query=value          Custom query for warehouse load history dump
        -Dsnowflake.warehouse_metering_history.query=value              Custom query for warehouse metering history dump

snowflake - Dumps metadata from Snowflake.

        --driver     The vendor-supplied driver JARs to use for this connector.
        --host       (default: localhost) The hostname of the database server. (Required if --url is not specified.)
        --user       The username for the database connection. (Required on most systems.)
        --password   The password for the database connection. (Required If not specified as an argument, will use a secure prompt..)
        --private-key-password Private Key file password. (Required If the private key file is encrypted..)
        --private-key-file Path to the Private Key file used for authentication. (Required If the database user uses keypair authentication..)
        --role       The Snowflake role to use for authorization.
        --warehouse  The Snowflake warehouse to use for processing metadata queries.
        --database   The name of the database to connect to for making queries.
        --url        Overrides the generated JDBC URI for the database connection. (Required only for complex installations.)
        -Dsnowflake.metadata.databases.query=value              Custom query for databases dump.
        -Dsnowflake.metadata.databases.where=value              Custom where condition to append to query for databases dump.
        -Dsnowflake.metadata.schemata.query=value               Custom query for schemata dump.
        -Dsnowflake.metadata.schemata.where=value               Custom where condition to append to query for schemata dump.
        -Dsnowflake.metadata.tables.query=value         Custom query for tables dump.
        -Dsnowflake.metadata.tables.where=value         Custom where condition to append to query for tables dump.
        -Dsnowflake.metadata.externalTables.query=value         Custom query for externalTables dump.
        -Dsnowflake.metadata.externalTables.where=value         Custom where condition to append to query for externalTables dump.
        -Dsnowflake.metadata.columns.query=value                Custom query for columns dump.
        -Dsnowflake.metadata.columns.where=value                Custom where condition to append to query for columns dump.
        -Dsnowflake.metadata.views.query=value          Custom query for views dump.
        -Dsnowflake.metadata.views.where=value          Custom where condition to append to query for views dump.
        -Dsnowflake.metadata.functions.query=value              Custom query for functions dump.
        -Dsnowflake.metadata.functions.where=value              Custom where condition to append to query for functions dump.
        -Dsnowflake.metadata.storagemetrics.query=value         Custom query for table storage metrics dump.
        -Dsnowflake.metadata.storagemetrics.where=value         Custom where condition to append to query for table storage metrics dump.