Tutorial

Use Snowflake Data Warehouse Storage

  • 11 January 2023
  • 0 replies
  • 1764 views
Use Snowflake Data Warehouse Storage
Userlevel 3
Badge +3

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.

Released in TimeXtender 6143.1 

Snowflake Storage is available as part of the Standard, Premium or Enterprise Package

The following functionality is currently supported when using Snowflake storage when using the TimeXtender 6429.1 release:

  • ODX using Azure Data Lake Storage with SAS authentication
  • Simple Mode tables.
  • Incremental Load, 
  • Field Transformations
  • Field Validations
  • Conditional Look-ups
  • History tables
  • Supernatural Keys
  • Custom Views

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: <OrganzationID>-<AccountID>
  2. To find the Account Identifier in the snowflake portal click 
    1. Admin >
    2. Accounts >
    3. Next to your account ID, click the link
    4. 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

Configure Data Warehouse Instance Storage

In TimeXtender Portal > Data estate > InstancesAdd Data Warehouse

 

  1. In the Server Storage Type, Select Snowflake
  2. Enter your Account Identifier
  3. Enter your Warehouse Name
  4. Enter your Database Name
  5. Enter your Port (Default is 443)
  6. Enter Username: Created when you signed up for Snowflake
  7. Enter Password: Created when you signed up for Snowflake
  8. Connection Timeout: number of seconds to wait before terminating the attempt to connect to the server. Set it to 0 to wait indefinitely
  9. Command Timeout: number of seconds to wait before terminating a command. Set it to 0 to wait indefinitely

 Configure SAS token for ODX 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 ODX 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 MDW

  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 MDW 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 Desktop. 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

Error: Snowflake Internal Error: Unable to connect

Cause: The account name is incorrect.

Solution: Edit your Data Warehouse instance in TimeXtender Portal to review and correct the account name, as described above.

Failed to authenticate request

Error: Server failed to authenticate the request

 

Cause: Snowflake is unable to access the ODX Storage

Solution: Verify you using Azure Data Lake ODX Storage and the SAS token is configured with access to the folder in Azure Data Lake that you're pulling the data from.

Execution Fails on ODX Transfer

Error: SQL compilation error:
syntax error line 1 at position 7 unexpected 'R'.
syntax error line 3 at position 18 unexpected '('.

Cause: This error occurs if Incremental Load is used. 

Solution: Set Table Settings to Full Load or Disable Incremental Rules on ODX Data source and Synchronize objects on ODX. 

Locked User

Error: ‘User temporarily locked’

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 


0 replies

Be the first to reply!

Reply