Use Snowflake for Prepare instance storage¶

Snowflake is a cloud-based data warehouse storage technology with automatic usage-based scaling of compute and storage resources. Additionally, Snowflake offers multi-cloud compatibility, allowing for greater flexibility in data management across different platforms.

Instructions¶

Create a Snowflake Database¶

1. Log into the snowflake portal app.snowflake.com, click your name > Switch Role > select the SYSADMIN role
2. Click Data > Databases
3. Click + Database
4. Give your Database a name and click Create

Get Snowflake Instance Details¶

Log into the snowflake portal app.snowflake.com

Find the Snowflake Account Identifier

1. The Snowflake Account Identifier should look like this: <OrganizationID>-<AccountID>
2. To find the Account Identifier in the snowflake portal click
3. Admin >
4. Accounts >
5. Next to your account ID, click the link
6. Copy the subdomain of the URL, the portion between "https://" and the first "."

Find the Snowflake Warehouse Name

1. Admin > Warehouses
2. Copy the name of the Warehouse you wish to use

Setting up key pair authentication¶

1. You need to generate a private key and a public key using the OpenSSL program. You can download a program that uses it here. https://slproweb.com/products/Win32OpenSSL.html
2. Once this program is installed you open the file location.

3. Then you right-click on the program and choose Run as Administrator.

4. Change the directory, so you point at the folder where you want to store the certificate files.

5. Then you add and run the following query to create the private key.

openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 des3 -inform PEM -out rsa_key.p8

It requests you to add a Password. Add one and remember it as it will be used in the setup.

6. Now we will add and run the public key.

   ```apache
   openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub
   ```
   ![Use Snowflake for Prepare instance storage — screenshot 7](images/use-snowflake-for-prepare-instance-storage-7.png)

Add the password you created earlier to complete it. 7. Now you have the two certificates. You can open both files with a text editor.

Create user account to use for Key Pair authentication.¶

1. Start by going to the snowflake portal. https://app.snowflake.com/. Log in to your account. Make sure you are able to create users, you may have to change the rights. In here go to Users & roles.

2. Add a user

I set mine up like so and called it MACHINE_USER.

Notice that I didn't add a password or checked the Force user to change password on first time login. 3. Now click on Projects. Create a new SQL file and give it a name. I called it query. I added the following query to make it use the public key for access. Do not add all the content, only the part after -----BEGIN PUBLIC KEY----- and before -----END PUBLIC KEY-----.

ALTER USER MACHINE_USER SET RSA_PUBLIC_KEY='content of the rsa_key.pub file';

4. Now you can use the MACHINE_USER account to authenticate your create and read commands towards Snowflake.

Configure Storage for Prepare Instance¶

In Timextender Portal > Data estate > Instances > Add prepare instance

In the Server Storage Type, Select Snowflake

1. Enter your Account Identifier
2. Enter your Warehouse Name
3. Enter your Database Name
4. Enter your Port (Default is 443)
5. Set the Authentication type to Key-pair authentication or Username and Password.
6. Enter the Username you altered to allow the user to access using the public key. In my case the MACHINE_USER account shown in the above section.
7. Enter Password you created when you generated the private key.
8. Add the Private Key content. Add the whole thing from where it begins with -----BEGIN ENCRYPTED PRIVATE KEY----- to the end.
9. DSN will be explained in the Connecting to Tabular, Tableau and Power BI Endpoints from a Snowflake Prepare instance section.
10. Connection Timeout: number of seconds to wait before terminating the attempt to connect to the server. Set it to 0 to wait indefinitely
11. Command Timeout: number of seconds to wait before terminating a command. Set it to 0 to wait indefinitely

Configure SAS token for Ingest ADLS storage¶

1. In the Azure Portal portal.azure.com > go to your ADLS resource, go to Shared access signature
2. Check mark Service, Container and Object boxes
3. Specify an expiry date
4. Generate & Copy the SAS token

1. Go to Timextender portal > Data estate > instances > Edit Ingest instance > Azure Data Lake Storage
2. Enter the full SAS token in the Azure SAS Token box

Connecting to Tabular, Tableau and Power BI Endpoints from a Snowflake Prepare instance¶

1. Download and install the Snowflake ODBC driver https://developers.snowflake.com/odbc/
2. Open the ODBC Data Source Administrator Application and go to the System DSN tab

3. Press Add and select SnowflakeDSIIDriver. Click Finish.

4. In the Snowflake Configuration Dialog, provide a name for the data source, and enter the user and password that were created when you signed up for Snowflake. Specify the database, schema, warehouse and set Tracing to 0. Click Test to ensure the connection to the data source is successful. Press OK.

5. Select the Prepare instance with Snowflake storage in the Timextender Portal in order to edit its details.
6. Type the Data Source name, that was entered into the Snowflake Configuration Dialog, into the DSN field. Press Save to confirm.

7. If using the Power BI endpoint, the ODBC Data source credentials must also be updated. After deploying the Power BI endpoint within Timextender Data Integration. Navigate to the dataset in app.powerbi.com, and click on the three dots, and select Settings.

8. Expand the gateway connection menu and press the action button.

9. Click Add to gateway on the ODBC data source.

10. Provide a name for the gateway and the data source. Select basic authentication method and enter the Snowflake username and password. Press Create.

    

11. Expand the Gateway connection again and map the ODBC connection. Click Apply.

    

Troubleshooting¶

Unable to Connect¶

Cause: The account name is incorrect.

Solution: Edit your Prepare instance in Timextender Portal to review and correct the account name, as described above.

Failed to authenticate request¶

Cause: Snowflake is unable to access the Ingest instance storage

Solution: If using Azure Data Lake for Ingest storage, verify the SAS token is configured with access to the folder in Azure Data Lake that you're pulling the data from. If using Snowflake ingest storage, verify the Ingest instance connection credentials are correct.

Execution Fails on Transfer task in Ingest instance¶

Cause: This error occurs if Incremental Load is used.

Solution: Set Table Settings to Full Load or Disable Incremental Rules on Data source in your Ingest instance and Synchronize your Ingest instance against your Prepare instance.

Locked User¶

Cause: This error may occur if the user is testing the connection 5 or more times with an incorrect password

https://community.snowflake.com/s/article/FAQ-User-and-Password-Management