Analytics Toolbox for Snowflake

Analytics Toolbox for Snowflake

Getting access

Manual installation (for CARTO customers)

This guide explains all the steps to install the SQL functions and procedures of the Analytics Toolbox in your Snowflake database.

The CARTO Analytics Toolbox contains two packages:

  • core: this is the public and open-source package. It contains all the core GIS functions that complement the GIS native functions available in Snowflake.
  • advanced: this is a premium package. It contains advanced GIS functions to power high-level GIS analytics in Snowflake.

The process consists of two steps: setup and installation. The first one is required only the first time you install the toolbox, while the second one must be done every time you want to install a new version of the packages.

Setup

This step consists of setting up the Snowflake database where you want to install the toolbox. A Snowflake account is required.

We’ll create a schema named carto in the database where you want the CARTO Analytics Toolbox installed. We also recommend creating a dedicated Snowflake user called carto to manage the CARTO Analytics Toolbox.

The following script will create the user, schema and role to be used for the installation in your database. Please note that this script must be executed by an account administrator.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
-- Set admin permissions
USE ROLE accountadmin;

-- Create a role for the carto user
CREATE ROLE carto_role;

-- Ensure the sysadmin role inherits any privileges the carto role is granted.
-- Note that this does not grant sysadmin privileges to the carto role
GRANT ROLE carto_role TO ROLE sysadmin;

-- Create the carto user
CREATE USER carto WITH DEFAULT_ROLE=carto_role DEFAULT_WAREHOUSE=COMPUTE_WH PASSWORD='<strong, unique password>';

-- Grant the carto role to the carto user
GRANT ROLE carto_role TO USER carto;

-- Let the carto user see this database
GRANT USAGE ON DATABASE "<my database>" TO ROLE carto_role;

-- Create the carto schema
CREATE SCHEMA "<my database>".carto;

-- Give the carto user full access to the carto schema
GRANT ALL PRIVILEGES ON SCHEMA "<my database>".carto TO ROLE carto_role;

-- Grant usage to public role
-- Repeat this for any other role that needs to use the toolbox
GRANT USAGE ON DATABASE "<my database>" TO ROLE public;
GRANT USAGE ON SCHEMA "<my database>".carto TO ROLE public;
GRANT SELECT ON ALL TABLES IN SCHEMA "<my database>".carto TO ROLE public;
GRANT SELECT ON FUTURE TABLES IN SCHEMA "<my database>".carto TO ROLE public;
GRANT SELECT ON ALL VIEWS IN SCHEMA "<my database>".carto TO ROLE public;
GRANT SELECT ON FUTURE VIEWS IN SCHEMA "<my database>".carto TO ROLE public;
GRANT USAGE ON ALL FUNCTIONS IN SCHEMA "<my database>".carto TO ROLE public;
GRANT USAGE ON FUTURE FUNCTIONS IN SCHEMA "<my database>".carto TO ROLE public;
GRANT USAGE ON ALL PROCEDURES IN SCHEMA "<my database>".carto TO ROLE public;
GRANT USAGE ON FUTURE PROCEDURES IN SCHEMA "<my database>".carto TO ROLE public;

Setup on Snowflake Classic Web Interface

You can check out the Snowflake getting started documentation for further information.

Installation

Once the setup is completed, we can proceed with the installation of the toolbox. This step will be performed the first time and every time we want to install an updated version.

During this installation step you are going to need the following information from the setup step:

  • database name where the Analytics Toolbox will be installed
  • password for the carto user
1. Connect to the database

This step is required to run the installation SQL script.

First, connect to your Snowflake account using the carto user and password. Then, please set the role and database that will be used to install the toolbox:

1
2
USE ROLE carto_role;
USE DATABASE "<my database>";

Setup on Snowflake Classic Web Interface

2. Create the functions and procedures

Download the package file and unzip it in your local storage.

To install the functions and procedures of the Analytics Toolbox in the carto schema of your database, execute the downloaded file modules.sql. Please note that you must execute this file’s commands in the same Worksheet where you executed all the previous statements of this installation guide.

You can load the script into a Worksheet by using the dropdown menu on the top right and choosing “Load Script”. The “All Queries” check seems to work unreliably with large scripts, so we advice to select all the text instead (just press Control-A or Command-A if using a Mac), then press the “Run” button and confirm you want to execute all the lines.

Setup on Snowflake Classic Web Interface

3. Check installation

You can check the installed functions and procedures by running the following statements:

1
2
SHOW USER FUNCTIONS IN SCHEMA carto;
SHOW USER PROCEDURES IN SCHEMA carto;
4. Import sample data (optional)

In order to be able to reproduce the queries included in the Guides and Examples sections, you can optionally import into the carto schema a sample table containing the Starbucks locations in the US.

To do so, please run the following script in the same Worksheet you have used for the installation of the Analytics Toolbox:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
-- Set geom format as wkt
ALTER SESSION SET GEOGRAPHY_OUTPUT_FORMAT = wkt;

-- Load sample table
CREATE OR REPLACE TABLE carto.starbucks_locations_usa(
  id BIGINT,
  brand STRING,
  store_name STRING,
  ownership_type STRING,
  address STRING,
  city STRING,
  state_province STRING,
  country STRING,
  postcode STRING,
  geog GEOGRAPHY);
COPY INTO carto.starbucks_locations_usa
  FROM 'gcs://carto-analytics-toolbox-core/samples/starbucks_locations_usa.csv'
  FILE_FORMAT = (type = 'csv', FIELD_OPTIONALLY_ENCLOSED_BY = '"');

-- Set geom format as geojson
ALTER SESSION SET GEOGRAPHY_OUTPUT_FORMAT = geojson;

Congratulations! you have successfully installed the CARTO Analytics Toolbox in your Snowflake database. Now you can start using the functions. Please refer to Step 3 above to check the installed version and functions.

Marketplace installation (for non-CARTO customers)

You can get access to the core modules of the Analytics Toolbox for Snowflake through the Snowflake’s Data Marketplace. If you are unsure of how to access the Data Marketplace, you can find detailed instructions in this article of Snowflake’s documentation center.

Once in the Data Marketplace, search for carto analytics toolbox to find the listing:

Analytics Toolbox for Snowflake listing

Once you are in the details page of the listing, you will find that you can GET the Analytics Toolbox directly following these instructions:

  1. Click on the GET DATA button on the top right corner of the Data Marketplace listing.

Analytics Toolbox for Snowflake get data

  1. Rename the database to the name of your choice. Next, click on More options to choose all the roles to which you wish to give access to this database, accept the Terms of Use and finally click on “Create Database”.

Analytics Toolbox for Snowflake get data form

By clicking on “View Database” you will be redirected to the database you just created, where you will be able to browse all the modules (schemas) and functions and procedures available within the Analytics Toolbox.

Analytics Toolbox for Snowflake get data form