Guide: Using Snowflake with Databox

IN THIS ARTICLE

    Snowflake connections are available in Plus and Business Accounts. Agency Basic Accounts have the ability to use Snowflaake in the Agency Account only.

How to prepare your Snowflake database

1
 First, we will visit our unique Snowflake URL to login. Be sure to note the Account name, which is the first part of the URL before ".snowflakecomputing.com". We'll need this later
2
 Next, we'll click on the Databases icon and will select Create to create a new database

3

 We'll name this new database "DATABOX_TEST"

4

 Click Finish to save the new database. This database will now appear in our list, and the current role, SYSADMIN in our case, will be the database owner. 

Roles can be changed in the upper right corner of Snowflake. Learn more about Snowflake security best praactices here

5

 Next, we'll create a schema for our database. To do this, we'll click on the database name and will select Schemas > Create

6

 We'll name this new schema "TEST_SCHEMA"

7

 We'll create a simple table with 3 columns for our example, and will name it "SAMPLE_DATA"

8

 Click on the table name 

9

 Next, we'll click on the Warehouses icon and will select Create to create a new warehouse

10

 We'll name this new warehouse "WAREHOUSE_X_SMALL." We've selected X-Small as the Size, 5 minutes as the Auto Suspend time, and we checked the Auto Resume checkbox. 

11

 Click Finish to save the new warehouse. This warehouse will now appear in our list. 

12

 Now it's time to load a sample dataset. We've prepared a CSV file with the following content: 

2017-11-03,John Waters,3849.23

2017-11-10,John Waters,105.27

2017-10-05,Jennifer Tracy,124.02

2017-03-06,Milla Marx,932.99

13

 Click Load Table and choose the warehouse we created earlier

14

 Click Next and choose the appropriate CSV file. Click + and define a file format

15

 Select the CSV 

16

 Click Load to load the data in the table

The database is now set up, data is loaded and the warehouse is ready to go. 

17

 Finally, we need to add the Databox IP to the Snowflake Account so queries can be run from Databox. Click the drop-down arrow in the upper right corner of the Account and Switch Role to "ACCOUNTADMIN"

18

 Click on Account > Policies and select Create to create a new Network Policy

19

 Enter "DATABOX" as the Name and Databox's public IP (52.4.198.118) in the Allowed IP Addresses field

This new Network Policy will now appear in the main window, and remote access from Databox is set up.

How to connect Snowflake to Databox

Once our Snowflake Account is properly prepared, we'll connect it to Databox.

To connect Snowflake to Databox, go to Data Manager > + New connection. Type Snowflake in the search bar and click on the green Connect button. 

This will open the  Activation window, which is where you will be prompted to enter your connection data and authorize the Databox app. 

Account is the first part in the URL, before the .snowflakecomputing.com, so enter just that, without the .snowflakecomputing.com part.

If the default Role will be used, leave the Role field empty. If a specific Role is needed to access your Snowflake data warehouse and/ or your table schema, enter it in the Role field to ensure the connection is set up correctly. 

Click the green Activate button to complete the connection process. If Snowflake was successfully connected, the Activation window will close shortly and you'll see a Connected notice. 

If you’re running into connection issues, test your remote access to Snowflake using their command line client called SnowSQL. Learn more here.

How to use the Query Builder for Snowflake

The Query Builder for Snowflake allows you to create Custom Metrics using data from your Database to use in Databoards, Alerts, Goals, etc. Learn more about the Query Builder for Snowflake here

Additional Information

  • Query results are limited to 1000 rows. If you expect your query to return over 1000 rows or if you're not sure how many rows will be returned, be sure to LIMIT the query
  • The query must contain a column named "Date." If you need to, use AS in your SELECT statement to satisfy this requirement
  • The Query won't recognize null values. In those cases please use a coalesce function
Did this answer your question? Thanks for the feedback There was a problem submitting your feedback. Please try again later.

Still need help? Contact Us Contact Us