Connect Snowflake Data with Zoho Analytics

Sync Snowflake data with Zoho Analytics seamlessly. Transform your data into actionable insights with intuitive visualizations and AI powered analytics at each stage of analysis using Zoho Analytics. Automate complex data flows with robust data pipelines and schedule imports for near real-time updates.

• Flexible Connection modes: Pull data from Snowflake using the appropriate connection mode depending on your needs. Zoho Analytics provides two types of connection modes, namely Data Import and Live Connect.
• Multiple Sync Schedules: Enable real-time analysis by scheduling data import to perform analysis on the most recent data.
• Seamless Data Preparation: Use advanced data transformation functions and automate data pipelines to improve data quality and make it ready for analysis.

On this Page

• Prerequisites
  • Generating Private Key for key-pair authentication
  • Generating Client ID and Client Secret for OAuth authentication
• Import Snowflake Data
  • Configure Connection Details
  • Choose Data to Import
  • Schedule Data Import
  • Data Sync Frequency
• Import Rollback
• Live Connect
• Manage Data Source
• Manage Table Synchronization Settings
• Resolving Sync Failures
• Data Blending

Troubleshooting Tips

1. I get an error message "Sorry, there is a problem in connecting to your cloud data source. Check your connection details and try again." What should I do?
2. I am unable to find the Live Connect option while importing data into Zoho Analytics. What could be the possible reasons?

Prerequisites 

The following are the required details to connect the Snowflake data with Zoho Analytics.

Generating Private Key for Key - Pair Authentication

Access the terminal window of the Snowflake database. Run the following command to generate a Private Key.

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

A private key is generated in PEM format as shown below.

-----BEGIN PUBLIC KEY-----
MIIE...
-----END PUBLIC KEY-----

Copy the private key and save it in a secure folder. Provide the key while using the Private Key authentication. Refer to key-pair authentication article of Snowflake to learn more,

Generating Client ID and Client Secret for OAuth authentication

Generating a Client ID and Client Secret in Snowflake comprises the following steps:

• Creating an OAuth Integration
• Viewing Integration Details
• Viewing Client ID and Client Secret

Step 1:  Creating an OAuth Integration

Access the terminal window and run the following command to create an OAuth integration.

Query to create OAUTH Integration:

  CREATE SECURITY INTEGRATION <integration_name>
 TYPE = OAUTH
 ENABLED = TRUE
 OAUTH_CLIENT = 'CUSTOM'
 OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
 OAUTH_REDIRECT_URI = 'https://analytics.zoho.com/import/CloudDBOAuth2ResponseRedirect.jsp'
 OAUTH_ISSUE_REFRESH_TOKENS = TRUE
 OAUTH_REFRESH_TOKEN_VALIDITY = 7776000

• Replace <integration_name> with a descriptive name for your integration.

Step 2:  Viewing Integration Details

The below query displays the properties and configuration details of an integration, such as the authorization and token URL.

  DESC INTEGRATION ANALYTICS_OAUTH

Step 3: Viewing the Client ID and Client Secret

The below query displays the Client ID and Client Secret. Access the terminal window and run the following command to view the details.

  SELECT parse_json(system$show_oauth_client_secrets('<INTEGRATION_NAME>')) AS SECRETS
)
SELECT
   SECRETS:"OAUTH_CLIENT_ID"::STRING AS CLIENT_ID,
   SECRETS:"OAUTH_CLIENT_SECRET"::STRING AS CLIENT_SECRET
FROM
INTEGRATION_SECRETS;

Copy the Client ID and Client Secret and save it in a secure folder. Provide these details while using the OAuth authentication type. Refer to the OAuth article of Snowflake to learn more.

Import Data from Snowflake

Configure Connection Details

1. Click Import your Data on the Zoho Analytics home page.
2. Click Databases & Datalakes > Snowflake.
   
3. Choose the Authentication Type. Zoho Analytics supports two methods to authenticate your Snowflake account.
   • Private Key: Provide the Username, Private Key, and Private Key Passkey Phrase. Refer to the Prerequisite section to learn more
   • OAuth: Provide the Client ID, Client Secret, Authorization URL, Token URL, and Scope. Refer to the Prerequisite section to learn more
     • Click Authenticate Snowflake.
     • You will be redirected to the Snowflake authentication page. Provide your Username and Password.
       
     • On successful authentication, an authentication success message will be displayed.
4. Provide the Account Name, Warehouse Name, User Role, and the Database Name.
   
5. Choose the connection type, Data Import or Live Connect.
   • Data Import: This method imports and stores the data in Zoho Analytics
   • Live Connect: This method enables direct, real-time connection to the data source, ensuring that up-to-the-minute data is used for analysis. In this method, the data is not imported or stored within the Zoho Analytics application. Refer to the Live Connect article to learn more.
6. Click Next.

Choose Data to Import

Once the connection details are configured, you need to decide which data you want to import from Snowflake. Zoho Analytics offers a range of import options, including Single Table, Multiple Tables, and Custom Query to support various data structures and analysis needs.

Single Table

1. Select Single Table from the Import From section.
2. Choose the table to import from the Select Table drop-down menu.
3. Click Next to continue.
   
4. The Import Settings and Preview wizard will open.
   • You can edit the Workspace Name, Table Name, and add a description if needed.
   • The Data Preview section displays the first few records of your data; you can verify the column name and the data type.
   • Unselect the checkbox adjacent to the column header to remove column from importing.
     
5. On Import Errors section allows you to specify how to handle error conditions in case they occur while importing data. The following are the possible options:
   • Set Empty Value for the Column (default): Sets an empty value to the corresponding column value which had problems while importing.
   • Skip Corresponding Rows: Skips the corresponding rows in which an error occurs while importing.
   • Don't Import the Data: Aborts the import process if any error occurs during importing.
6. Click Create. Data import will be initiated, and you can also choose to schedule the import.

Multiple Tables

1. Select Multiple Tables from the Import From section.
2. Choose the tables you wish to import and then click Next.
   
3. In the Import Settings page that opens, specify the Workspace Name that is to be created in Zoho Analytics. You can also provide a Description about the workspace.
4. Click the Edit icon that appears on mouse hover to edit the Table Name.
5. Specify how to handle errors that occur during import in the On Import Errors section.
6. Click Create. Data import will be initiated, and you can also schedule the import.
   

Custom Query

1. Select the Custom Query option and provide the SQL SELECT query in the given text area.
2. Click Next to continue. The successive steps are similar to importing data from a single table.
   

Schedule Data Import

Data Refresh

Data freshness is important for effective analysis. Zoho Analytics allows you to configure what data should be fetched in each schedule.

• Import all records: This option imports all the records in each schedule. Refer to the Schedule import article to learn more.
• Incremental Fetch: This option imports only the new and updated or modified records into Zoho Analytics. This method is beneficial when the database contains transactional records that get updated frequently. Refer to the Incremental Fetch article to learn more.

Follow the below steps to configure the Import synchronization settings (data refresh settings)

1. Once you have initiated the data import, Click Schedule this Import.
   
2. Choose What data to fetch in each schedule drop-down menu. You can choose to import all the records or incrementally fetch only the newly added or modified records.
   
3. Choose How do you want to import the data?
   
4. Select Include new columns added in your cloud database automatically into Zoho Analytics table(s) to import data from newly created columns in the subsequent imports.
   
5. Click Next.

Data Sync Frequency

Zoho Analytics offers flexible sync schedules to ensure that the data is always up-to-date for effective analysis.

1. In the Schedule Setting pane, choose the interval in which you would like to synchronize the data. You can choose to synchronize your data at one of the intervals mentioned below.
   • Every 'N' hour
   • Everyday
   • Weekly Once (Standard plan and above)
   • Monthly Once (Standard plan and above)
2. Selecting Not Scheduled will import the data only once, i.e., at the time of import.
3. In the case of continuous import errors, you can choose to be notified via email. To get notifications, select the interval at which you need to be notified from the Notify me after every drop-down menu.
4. The Trigger when at least ‘n’ tables fail in an import option lets you specify the minimum number of table failures that must occur before a sync failure notification is sent.
5. You can also choose to notify the Account Admin, All Workspace Admins, All Organization Admins, or any Custom User.
   
6. Click Save.

Refer to the Multiple Sync Schedule interval section to learn how to sync different tables at different sync intervals.

Import Rollback

Import rollback reverts the most recent data import to the previous version. Users with administrator privileges and custom role users with permission to create tables can perform this action. Rollback facilitates the correction of errors in data without disrupting operations and also minimizes data loss. It is useful in collaborative environments where multiple users access the data and make changes.

Follow the below steps to revert the data import to the previous version,

1. Access the data table, which needs to be reverted.
2. Click Import Data > Last Import Details.
   
3. Click Rollback to revert to the previous instance.
   
4. Import rollback will be initiated, displaying the status along with the details of the admin who performed the action and the time of execution.
   

Live Connect

Live Connect enables direct, real-time connection to the data source, ensuring that up-to-the-minute data is used for analysis. In this method, the data is not imported or stored within the Zoho Analytics application. Refer to the Live Connect article to learn more.

Manage Data Source

The Data Sources tab is the central hub that helps manage all the connection settings of the data sources in the workspace. It also helps keep tabs on the data sync status and notifies in the case of data sync failures. User with the Administrator Privilege can access the Data Sources tab and make necessary changes when needed.

• Edit Connection: Enables admins to re-authenticate and update the connection details such as credentials or configuration settings without causing any interruption to the existing data flow. Learn more
  
• Sync Settings: Set up how often your data syncs and manage who gets notified when a sync fails. Learn more
• Audit History: Track user and system actions with detailed audit logs for each data source. Learn more
• Sync History: View the status of past syncs and troubleshoot any failures. Learn more

Manage Table Synchronization Settings

Zoho Analytics provides flexible data fetch options like full data and incremental fetch, which imports only the new records. Each table can have different data fetch configurations. All the tables imported from the database will be listed with the Table and Source Table Name, What data is fetched in each sync, Last Fetch Time, and its Sync status. Learn more

Resolving Sync Failures

Data synchronization can fail if the credential provided is invalid or expired, or if the data type mismatches. Zoho Analytics sends an email to the workspace admin and also sends an in-app notification with the reason for failure along with the necessary action to be taken to resolve the issue. Access the Data Sources tab to resolve sync failure. Refer to the Edit Connection section for more details.

Data Blending

Combine data from various data sources, such as Files, Feeds, Cloud storage Databases, and Business applications, for in-depth analysis. While importing data in an existing workspace, Zoho Analytics auto identifies columns of the same data type and provides suggestions for lookup. With Query Tables, you can combine data from different tables in the workspace.

To import data into an existing workspace, Click the Create icon on the side navigation panel and choose New Table/Import Data from the drop-down menu. Successive steps are similar to the steps mentioned in this section.

Import Data into an existing table

1. Click Import > Import data into this Table.
   
2. Select the table you want to import and click Next.
   
3. Choose how you want to import the data into the existing table and select how to handle import errors.
4. Click Import.

Troubleshooting Tips

1. I get an error message "Sorry, there is a problem in connecting to your cloud data source. Check your connection details and try again." What should I do?

This error occurs in the following scenarios:

2. I am unable to find the Live Connect option while importing data into Zoho Analytics. What could be the possible reasons?

You will be able to connect live in Zoho Analytics only if,

• You have a paid or trial plan.
• You are importing data into a new Workspace in Zoho Analytics.