Snowflake is a cloud-based analytic data warehouse system. Use Domo's Snowflake KeyPair Internal Managed Unload connector to unload data from your Snowflake database into internal Amazon S3 easily and securely. The files are then downloaded from internal S3 into Domo. This connector should be preferred over the other Snowflake connectors if you are executing very large queries.
Once your Snowflake data is in Domo, you can combine it with all your other Domo data to get a centralized view of your most mission-critical data.
To connect to a Snowflake database and create a DataSet, you must have the following:
The full name of your account (provided by Snowflake). This is the portion of your Snowflake URL immediately following https://.
Note that your full account name may include additional segments that identify the region and cloud platform where your account is hosted. For US West Region account_name and All other regions account_name.region_id. The following table provides account name examples by cloud platform/region. For each row, it assumes your account name is "xy12345."
The username used to connect to your Snowflake host.
The private key.
The passphrase. If you create an encrypted private key, then OpenSSL prompts for a passphrase used to encrypt the private key file.
- Your role name.
Configuring the public/private key pair
To configure the public/private key pair, follow these steps:
- From the command line in a terminal window, generate a private key.
You can generate either an encrypted version of the private key or an unencrypted version of the private key.
- To generate an unencrypted version, use the following command:
$ openssl genrsa -out rsa_key.pem 2048
- To generate an encrypted version, use the following command:
$ openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8
It is typically safer to generate an encrypted version.
If you use the second command to encrypt the private key, then OpenSSL prompts for a passphrase used to encrypt the private key file. We recommend using a strong passphrase to protect the private key. Record this passphrase in a secure location. You will input it when connecting to Snowflake. Note that the passphrase is only used for protecting the private key and will never be sent to Snowflake.
The following is a sample PEM private key:
-----BEGIN ENCRYPTED PRIVATE KEY-----
-----END ENCRYPTED PRIVATE KEY-----
- To generate an unencrypted version, use the following command:
From the command line, generate the public key by referencing the private key. Assuming the private key is encrypted and contained in the file named “rsa_key.p8”, use the following command:
$ openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub
The following is a sample PEM public key:
-----BEGIN PUBLIC KEY-----
-----END PUBLIC KEY-----
Copy the public and private key files to a local directory for storage. Record the path to the files. Note that the private key is stored using the PKCS#8 (Public Key Cryptography Standards) format and is encrypted using the passphrase you specified in the previous step; however, the file should still be protected from unauthorized access using the file permission mechanism provided by your operating system. It is your responsibility to secure the file when it is not being used.
Assign the public key to the Snowflake user using ALTER USER. For example:
alter user jsmith set rsa_public_key='MIIBIjANBgkqh...';Notes:
- Only security administrators (i.e. users with the SECURITYADMIN role) or higher can alter a user.
- Exclude the public key header and footer in the SQL statement.
- Verify the user’s public key fingerprint using DESCRIBE USER:
Property Value Default Description Name JSMITH null Name RSA_PUBLIC_KEY_FP SHA256:nvnONUsfiuycCLMXIEWG4eTp4FjhVUZQUQbNpbSHXiA= null Fingerprint of user's RSA public key. RSA_PUBLIC_KEY_2_FP null null Fingerprint of user's second RSA public key.Note: For more information about the RSA_PUBLIC_KEY_2_FP property, see Key Rotation.
- Modify and execute the sample code, below. The code decrypts the private key file and passes it to the Snowflake driver to create a connection:
- Update the security parameters:
- <path> specifies the local path to the private key file you created.
- Update the session parameters:
- <user> specifies your Snowflake login name.
- <account> specifies the name of your account (provided by Snowflake).
- The sample code is as follows:
public class TestJdbc
public static void main(String args)
File f = new File("<path>/rsa_key.p8");
FileInputStream fis = new FileInputStream(f);
DataInputStream dis = new DataInputStream(fis);
byte keyBytes = new byte[(int) f.length()];
String encrypted = new String(keyBytes);
String passphrase = System.getenv("PRIVATE_KEY_PASSPHRASE");
encrypted = encrypted.replace("-----BEGIN ENCRYPTED PRIVATE KEY-----", "");
encrypted = encrypted.replace("-----END ENCRYPTED PRIVATE KEY-----", "");
EncryptedPrivateKeyInfo pkInfo = new EncryptedPrivateKeyInfo(Base64.getMimeDecoder().decode(encrypted));
PBEKeySpec keySpec = new PBEKeySpec(passphrase.toCharArray());
SecretKeyFactory pbeKeyFactory = SecretKeyFactory.getInstance(pkInfo.getAlgName());
PKCS8EncodedKeySpec encodedKeySpec = pkInfo.getKeySpec(pbeKeyFactory.generateSecret(keySpec));
KeyFactory keyFactory = KeyFactory.getInstance("RSA");
PrivateKey encryptedPrivateKey = keyFactory.generatePrivate(encodedKeySpec);
String url = "jdbc:snowflake://<account>.snowflakecomputing.com";
Properties prop = new Properties();
Connection conn = DriverManager.getConnection(url, prop);
Statement stat = conn.createStatement();
ResultSet res = stat.executeQuery("select 1");
- Update the security parameters:
Snowflake supports multiple active keys to allow for uninterrupted rotation. Rotate and replace your public and private keys based on the expiration schedule you follow internally.
Currently, you can use the RSA_PUBLIC_KEY and RSA_PUBLIC_KEY_2 parameters for ALTER USER to associate up to 2 public keys with a single user.
To rotate your keys,
- Complete the steps in Using Key Pair Authentication to...
- Generate a new private and public key set.
- Assign the public key to the user. Set the public key value to either RSA_PUBLIC_KEY or RSA_PUBLIC_KEY_2 (whichever key value is not currently in use).
alter user jsmith set rsa_public_key_2='JERUEHtcve...';
- Update the code to connect to Snowflake. Specify the new private key.
Snowflake verifies the correct active public key for authentication based on the private key submitted with your connection information.
- Remove the old public key from the user profile. For example:
alter user jsmith unset rsa_public_key;
Connecting to Your Snowflake Account
This section enumerates the options in the Credentials and Details panes in the Snowflake KeyPair Internal Managed Unload Connector page. The components of the other panes in this page, Scheduling and Name & Describe Your DataSet, are universal across most connector types and are discussed in greater length in Adding a DataSet Using a Data Connector.
This pane contains fields for entering credentials to your Snowflake account. The following table describes what is needed for each field:
|Account Name||Enter the full name of your account (provided by Snowflake.) For US West Region account_name and All other regions account_name.region_id|
|Username||Enter the username used to connect to your Snowflake host.|
|Private Key||Enter the private key.|
|Passphrase||If you create an encrypted private key, then OpenSSL prompts for a passphrase used to encrypt the private key file. Enter that passphrase here.|
|Role||Enter the name for your role.|
Once you have entered valid credentials, you can use the same account any time you want to create a new Snowflake KayPair Internal Managed Unload dataset. You can manage connector accounts in the Accounts tab in the Data Center. For more information about this tab, see Managing User Accounts for Connectors.
This pane contains a primary Reports menu, along with various other menus which may or may not appear depending on the report type you select.
Select the Query Type. The following query types are available:
|Warehouses||Select the warehouse containing the data you want to retrieve. The list populates with all of the warehouses you have access to based on your credentials.|
|Databases||Select the database containing the data you want to retrieve. The list populates with all of the databases you have access to based on your credentials.|
|Schemas||Select the database schema containing the data you want to retrieve. The list populates with all of the schemas you have access to based on your credentials.|
|Database Objects||Select the database object containing the data you want to retrieve.|
|Database Tables||Select the database table you want to retrieve. The list populates with all of the tables you have access to based on your credentials. If you do not select a database table, the connector will retrieve your entire database schema.|
|Table Columns||Select the table columns you want to retrieve. The list populates with all of the columns you have access to based on your credentials. If you do not select a table column, the connector retrieves your entire selected database table.|
|Table Query Helper||A query is generated based on the selected fields. Copy and paste this text into the QUERY field. You may edit this query, if desired.|
|Views||Select the view.|
|View Columns||Select the view columns that you would like to include in your report.|
|View Query Helper||A query is generated based on the selected fields. Copy and paste this text into the QUERY field. You may edit this query, if desired.|
Enter the SQL query to retrieve your data.
|Partition Support||Specify whether you want to partition your data.|
|Partition Support Format||Select the partition format.|
|Partition Column Name||Select the partition column name.|
|Past Years||Enter the number of past years that you want to get data for. Value can be X, where X is a positive integer. For example: 30.|
|Future Years||Enter the number of future years that you want to get data for. Value can be X, where X is a positive integer. For example: 30.|
|Past Months||Enter the number of past months that you want to get data for. Value can be X, where X is a positive integer. For example: 30.|
|Future Months||Enter the number of future months that you want to get data for. Value can be X, where X is a positive integer. For example: 30.|
|Past Days||Enter the number of past days that you want to get data for. Value can be X, where X is a positive integer. For example: 30.|
|Date Format||Select the required date format. By default yyyy-MM-dd will be used.|
|Custom Date Format||Enter the custom date format.|
For information about the remaining sections of the Connector interface, including how to configure scheduling, retry, and update options, see Adding a DataSet Using a Data Connector.
When should I use this connector?
The Snowflake KeyPair Internal Managed Unload connector imports data to Domo from Snowflake by first unloading data to a Snowflake-managed S3 bucket before ingesting the data in Domo. This connector is suitable for datasets of any size and is recommended for very large datasets. To connect, you'll need a local Snowflake username and a private key.
What kind of credentials do I need to power up this connector?
You need the account name, username, and private key associated with your Snowflake account. If you create an encrypted private key, then OpenSSL prompts for a passphrase used to encrypt the private key file.
Are there any API limits I should be aware of?
The limits depend on your server configuration.
How frequently will my data update?
DataSets can run as often as every 15 minutes. However, depending on the runtime of the query, DataSets may need to run less frequently.
What should I be aware of while writing a query?
Make sure that all the words, table names, and field names are correctly spelled.
Why can't I connect to my Snowflake database? Do I need to whitelist any IP addresses?
Before you can connect to a Snowflake database, you must also whitelist a number of IP addresses on your database server on the port you want to connect to. For the full list of IP addresses, see Whitelisting IP Addresses in Domo.
- Make sure your authentication remains valid.
- Review the configuration to make sure that all required items have been selected.
- Review the Connector history for error messages.
- In rare cases, you may be requesting too much information and reaching API limitations or timeouts. If this is the case, you can review the history of the Connector run to see the error message and duration. If this is the case, you can reduce the number of accounts that are being pulled, choose a smaller number of metrics for the report that you are pulling, or reduce the timeframe that you are trying to pull.