Adding a Snowflake Data Warehouse as a Data Store

You can add a Snowflake data warehouse as a data store to Qubole Data Service (QDS) by using the QDS UI or REST API.

Prerequisites

You must be a system administrator to add a data store.
You must have a Qubole Enterprise edition account.

Before you begin, you should review and understand the parameters required for adding a data store. See Understanding the parameters used for Snowflake

Adding a Snowflake Data Warehouse as a Data Store by using the QDS UI

Note

All the values are case-sensitive except the Host Address.

From the Home menu, click Explore to navigate to the Explore page.
Pull down the drop-down list that defaults to Qubole Hive and choose + Add Data Store.
Enter the name of a data store that is to be created in QDS in the Data Store Name field.
Choose Database Type as Snowflake.
Enter the name of the Snowflake catalog in the Catalog Name field.
Enter the Database Name.
Enter the name of the Snowflake warehouse in the Warehouse Name field.
Enter the base URL of your Snowflake account login URL in the Host Address text field. For example, qubole.snowflakecomputing.com
Enter the username (to be used on the host) in the Username text field.
Enter the password (for the username on the host) in the Password text field.
Click Save to add the data store.
Verify that the data store is added and activated by clicking on the drop-down list at the top left of the Explore page. A green dot indicates that the data store is activated.
Verify that the table explorer widget in the left pane of the Explore page displays all of the tables in the Snowflake data store.

After adding a Snowflake data store, you can edit the properties of the data store. See Edit a DbTap

Note

After adding a Snowflake data store, you should restart the Spark cluster if you want to use an already running Spark cluster. Restarting the Spark cluster installs the Snowflake jars on the Spark cluster.

After editing a Snowflake data store, you should restart the Spark cluster for the changes to take effect.

Adding a Snowflake Data Warehouse as a Data Store by using the REST API

Use the POST /api/v1.2/db_taps/ API to add a Snowflake data warehouse as a data store (DbTap) in QDS.

Note

To invoke this API, you must be a part of either the system-admin group or a group associated with a role that allows creating a DbTap. See Managing Groups and Managing Roles for more information.

Resource URI	db_taps/
Request Type	POST
Supporting Versions	v1.2
Return Value	Json object representing the newly created DbTap.

REST API Parameters

Note

Parameters marked in bold below are mandatory. Others are optional and have default values. All values are case-sensitive except for the db_host parameter.

Parameter	Description
catalog_name	This parameter is mandatory to make the data stores accessible through Spark clusters. The catalog_name can contain lower-case alphabets or numerals.
name	Name of the data store that is created in the QDS.
db_name	Database Name.
db_host	IP address or hostname of the data store. Use the base URL of your Snowflake account login URL. For example, qubole.snowflakecomputing.com. This value is not case-sensitive.
db_user	User name to log in to the data store.
db_passwd	Password to log in to the data store.
extra_configs	Specify the warehouse field in this parameter. Value of warehouse field is name of the virtual warehouse of Snowflake.
db_type	Type of database. Valid value is `snowflake`. The default value is `mysql`.
skip_validation	Set it to `true` to skip the data store validation when you create or edit a data store. It is set to `false` by default. During the data store validation process, QDS tests the connection to the data store.

Example

The following example code shows how to add a Snowflake data warehouse as a data store by using the API.

sample_file
{
   “name”: “snowflake-db”,
   “db_name”: “Example_DB”,
   “db_host”: “example.snowflakecomputing.com”,
   “db_user”: “user1”,
   “db_passwd”: “******“,
   “db_type”: “snowflake”,
   “catalog_name”: “snowflake-db”,
   “extra_configs”: {“warehouse”: “load_wh”}
}

curl -i -X POST -H "Content-Type: application/json" -H "Accept: application/json" -H "X-AUTH-TOKEN: $AUTH_TOKEN" -d @sample_file
https://api.qubole.com/api/${V}/db_taps/

Note

For this release, the above syntax uses https://api.qubole.com as the endpoint. The other Supported Qubole Endpoints on Different Cloud Providers to access QDS will be available in the future releases.

Sample Response

 {
       {
     “id”: 557,
     “db_user”: “user1”,
     “name”: “snowflake-db”,
     “db_host”: “example.snowflakecomputing.com”,
     “db_name”: “Example_DB”,
     “active”: true,
     “created_at”: “2018-01-03T12:06:35Z”,
     “account_id”: 2,
     “port”: null,
     “db_type”: “snowflake”,
     “is_metastore”: false,
     “refresh_token”: null,
     “catalog_name”: “snowflake-db”,
     “skip_validation”: false,
     “extra_configs”: {
         “warehouse”: “load_wh”
     },
     “user_id”: 92,
     “db_passwd”: “********”
}
 }

After adding a Snowflake data store, you can edit the properties of the data store. See Edit a DbTap

Note

After adding a Snowflake data store, you should restart the Spark cluster if you want to use an already running Spark cluster. Restarting the Spark cluster installs the Snowflake jars on the Spark cluster.

After editing a Snowflake data store, you should restart the Spark cluster for the changes to take effect.