Getting access
Recommended
The assisted installation from the CARTO Workspace is the recommended installation method for Analytics Toolbox and the compatible one to leverage it from the CARTO platform.
This guide explains all the steps to install the SQL functions and procedures of the Analytics Toolbox in your Snowflake account. 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. This module is also available from the Snowflake Data Marketplace for free.
- advanced: this is a premium package. It contains advanced GIS functions to power high-level GIS analytics in Snowflake.
Note that in order to be able to automatically install the Analytics Toolbox in your Snowflake account, CARTO requires you to run a specific setup so we can proceed with the installation and with the future updates. Please follow the instructions detailed below in order to complete the installation of the Analytics Toolbox for Snowflake.
Note
Note that this feature is only available for the Admin users in your CARTO organization account. Please contact CARTO’s support team at [email protected] in case of needing assistance.
CARTO requires a specific setup in your Snowflake account to be able to securely install the Analytics Toolbox and then access it from our different platform interfaces. This process must be completed by a user with ACCOUNTADMIN permissions in the Snowflake account. In this process you will need to create the following resources:
CARTOdatabase within your Snowflake account.CARTOschema in theCARTOdatabase.CARTO_ROLEuser role with full USAGE privileges in theCARTOdatabase and ALL PRIVILEGES on theCARTOschema.CARTOuser granted with theCARTO_ROLEthat CARTO will use to perform the installation and maintain future updates.
Create resources
In order to create the aforementioned resources please copy & paste the following queries in your Snowflake console.
-- Set admin permissions
USE ROLE ACCOUNTADMIN;
​
-- Create the carto database where the toolbox will be installed
CREATE DATABASE "CARTO";
​
-- Create the carto schema in the carto database
CREATE SCHEMA "CARTO"."CARTO";
​
-- Create a carto 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 "CARTO" TO ROLE CARTO_ROLE;
​
-- Give the carto user full access to the carto schema
GRANT ALL PRIVILEGES ON SCHEMA "CARTO"."CARTO" TO ROLE CARTO_ROLE;
​
-- Give the carto user permission to use the warehouse
GRANT OPERATE ON WAREHOUSE COMPUTE_WH TO ROLE CARTO_ROLE;
GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE CARTO_ROLE;
Grant usage
In order to make the Analytics Toolbox available to use via the resources you have just created, some permissions should be granted. Please copy & paste the queries below if you want to grant usage of the Analytics Toolbox to all the users in your Snowflake account or amend the query to fulfill your specific user access requirements:
-- Set admin permissions
USE ROLE ACCOUNTADMIN;
​
-- Grant usage to public role
-- Apply this for any other role that needs to use the toolbox
GRANT USAGE ON DATABASE "CARTO" TO ROLE PUBLIC;
GRANT USAGE ON SCHEMA "CARTO"."CARTO" TO ROLE PUBLIC;
GRANT SELECT ON ALL TABLES IN SCHEMA "CARTO"."CARTO" TO ROLE PUBLIC;
GRANT SELECT ON FUTURE TABLES IN SCHEMA "CARTO"."CARTO" TO ROLE PUBLIC;
GRANT SELECT ON ALL VIEWS IN SCHEMA "CARTO"."CARTO" TO ROLE PUBLIC;
GRANT SELECT ON FUTURE VIEWS IN SCHEMA "CARTO"."CARTO" TO ROLE PUBLIC;
GRANT USAGE ON ALL FUNCTIONS IN SCHEMA "CARTO"."CARTO" TO ROLE PUBLIC;
GRANT USAGE ON FUTURE FUNCTIONS IN SCHEMA "CARTO"."CARTO" TO ROLE PUBLIC;
GRANT USAGE ON ALL PROCEDURES IN SCHEMA "CARTO"."CARTO" TO ROLE PUBLIC;
GRANT USAGE ON FUTURE PROCEDURES IN SCHEMA "CARTO"."CARTO" TO ROLE PUBLIC;
API integration
CARTO’s Analytics Toolbox for Snowflake uses external lambda functions in Amazon Web Services (AWS) in order to perform requests to our external Location Data Services (LDS) providers for the geocoding and isolines functions in the LDS module. CARTO provides a specific role to make use of the lambda functions: arn:aws:iam::000955892807:role/CartoFunctionsRole.
Snowflake external functions are required to call the lambda function via an API Gateway. These are the endpoints currently available for CARTO LDS, select the best for your case, depending on the region of your Snowflake account:
AWS region | Region name | API Gateway endpoint |
|---|---|---|
Asia Pacific (Tokyo) | ap-northeast-1 | |
Asia Pacific (Sydney) | ap-southeast-2 | |
Europe (Ireland) | eu-west-1 | |
United States East (N. Virgina) | us-east-1 |
Pay attention to this step and remember that you should choose the best API Gateway endpoint for your case based on the region of your Snowflake account. If you make the configuration using an endpoint and later you want to modify it, you must repeat the whole setup and installation process.
For illustration purposes let’s pick, for example, region “us-east-1â€; then, the API Gateway end-point would be: “https://m0qahrqhei.execute-api.us-east-1.amazonaws.com/production/ldsâ€.
To make the connection between Snowflake and the API Gateway, a component called API integration is required. Follow the steps below to create the API integration to be able to use CARTO’s Location Data Services (replacing the API Gateway endpoint with the most suitable one for your Snowflake account):
-- Set admin permissions
USE ROLE ACCOUNTADMIN;
​
-- Create the api integration
CREATE OR REPLACE API INTEGRATION CARTO_LDS
API_PROVIDER = AWS_API_GATEWAY
API_AWS_ROLE_ARN = 'arn:aws:iam::000955892807:role/CartoFunctionsRole'
ENABLED = TRUE
API_ALLOWED_PREFIXES = ('https://m0qahrqhei.execute-api.us-east-1.amazonaws.com/production/lds');
​
-- Grant usage on the api integration
GRANT USAGE ON INTEGRATION CARTO_LDS TO ROLE PUBLIC;
These instructions are well tested to install the Analytics Toolbox in a Snowflake account. If you’re running into issues, please confirm you’ve properly run all the Setup steps detailed in the previous sections. For assistance please contact CARTO’s support team at [email protected].
Admin users can install the Analytics Toolbox in Snowflake accounts from the Settings section available in the CARTO Workspace. In Settings, Admins will find a sub-section called “Analytics Toolbox†in which they can see and manage all different installations of the Analytics Toolbox associated with the CARTO account.
In order to start the installation process of the Analytics Toolbox in your Snowflake account, click on “+ New installation†on the aforementioned area of the Settings section.
From the list of the supported cloud data warehouses, select “Snowflake†and click on “Continueâ€.
The first thing that the installation wizard will ask you is to confirm that you have followed all the necessary steps detailed on the “Setup†section of this documentation. Mainly, the creation of the
CARTO database with the CARTO schema, plus a user and a role with the necessary privileges for CARTO to securely connect with your account and perform the installation and future updates of the Analytics Toolbox.Once you have ensured that your Snowflake account has been configured appropriately, confirm by ticking the box next to the message “I have already completed the setup of my Snowflake accountâ€.
In the next step, you should input your Snowflake account name and the password you have associated with the
CARTO user. Then, click on “Validate setup†so CARTO can check that we have access to all necessary resources to securely proceed with the Analytics Toolbox installation in your Snowflake account.
During this process CARTO will check that we have access to the
CARTO database and schema with the necessary privileges to perform the installation. We also check that we can see the API integration necessary for the Location Data Services procedures (e.g. geocoding, islones, etc.). If you encounter any issues in this step please do not hesitate to contact CARTO’s support team at [email protected] to receive technical assistance.Once we validate that the Snowflake account has been set up successfully, you can click on “Install†in order to start the actual installation of the Analytics Toolbox.
Once you click on Install, the process of installing all functions and procedures in the relevant database will start. Please bear in mind that this process may take several minutes to complete.
Once the process completes successfully, click on “Done†to go back to the Analytics Toolbox section in Settings.
Now, you should be able to see your new Analytics Toolbox installation listed with its relevant details and “Available†as its Status. You are now all set to start getting the most out of advanced spatial analytics natively in your Snowflake account!
You can check the version by running
SELECT CARTO.CARTO.VERSION_ADVANCED().Once you have installed the Analytics Toolbox in your Snowflake account, all connections to Snowflake databases within that account and which have access to the
CARTO database and CARTO schema should be able to automatically detect the Analytics Toolbox and make use of it. In that case, you should see in the relevant connection cards in the “Connections†section of the Workspace that the Analytics Toolbox is available.
We are currently actively working on further expanding and improving the Analytics Toolbox, therefore we tend to release a new update of this component approximately every month. When updates become available you will be informed in the Settings section plus in each connection card that is associated with a specific Analytics Toolbox installation.
Admin users can manage the updates of the Analytics Toolbox from the Settings section, in the same area used to perform the installation. Once new updates become available, click on the “Update available†option in the Status column associated with the relevant installation.
Please confirm that you would like to proceed with the Analytics Toolbox update in the dialog box that will appear on your screen by clicking on the “Yes, update†button.
CARTO will use the same credentials provided for that Analytics Toolbox installation and perform the update process on the same resources used during the installation.
Once the update has completed successfully please click on “Doneâ€. You should then see the Analytics Toolbox installation again with “Available†on the Status column.
Change credentials
If for any reason you need to change the password of the
CARTO user that was used for installing the Analytics Toolbox in your account and that will be used also for updating the package, you can do so by selecting “Change credentials†in the relevant Analytics Toolbox installation from the list.
As all other parameters are fixed, you can only modify the password. Please introduce and confirm the new password associated with the
CARTO user and click on “Validate credentialsâ€.
Next, CARTO will validate that with the new credentials we can check the setup that will be necessary to update the Analytics Toolbox when new versions become available.
Once CARTO has validated that everything works well, click on “Save changesâ€.
Admin users can uninstall the Analytics Toolbox from Snowflake accounts at any time. In order to do that, you should click on Uninstall from the relevant Analytics Toolbox installation detailed in the Settings section.
Next, as this process will be irreversible, we ask you to confirm that you would like to uninstall that specific Analytics Toolbox installation. Click on “Yes, uninstall†to proceed with uninstalling the Analytics Toolbox from your Snowflake account.
Once the process is complete, you will be taken back to the Analytics Toolbox section of Settings in which the specific installation that you have just removed should not appear anymore.
Note that any active Snowflake connection will have stopped having access to the Analytics Toolbox. Therefore, in their connection cards you will be informed that the Analytics Toolbox is not available and that the Admin user should install it.
This guide explains all the steps to manually install the SQL functions and procedures of the Analytics Toolbox in your Snowflake account.
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.
This guide explains how to install the core package. In order to access the advanced features, please contact [email protected].
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.
This step consists of setting up a new database in Snowflake named
CARTO, where you will install the Analytics Toolbox. A Snowflake account is required.We’ll also need to create a schema named
CARTO in the CARTO database. We also recommend creating a dedicated Snowflake user called CARTO to manage the CARTO Analytics Toolbox with a CARTO_ROLE defined with the necessary permissions.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.
Before executing the script make sure to replace the placeholder
'<strong, unique password>'by your password.-- 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 CARTO TO ROLE CARTO_ROLE;
​
-- Create the carto schema
CREATE SCHEMA CARTO.CARTO;
​
-- Give the carto user full access to the carto schema
GRANT ALL PRIVILEGES ON SCHEMA CARTO.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 CARTO TO ROLE public;
GRANT USAGE ON SCHEMA CARTO.CARTO TO ROLE public;
GRANT SELECT ON ALL TABLES IN SCHEMA CARTO.CARTO TO ROLE public;
GRANT SELECT ON FUTURE TABLES IN SCHEMA CARTO.CARTO TO ROLE public;
GRANT SELECT ON ALL VIEWS IN SCHEMA CARTO.CARTO TO ROLE public;
GRANT SELECT ON FUTURE VIEWS IN SCHEMA CARTO.CARTO TO ROLE public;
GRANT USAGE ON ALL FUNCTIONS IN SCHEMA CARTO.CARTO TO ROLE public;
GRANT USAGE ON FUTURE FUNCTIONS IN SCHEMA CARTO.CARTO TO ROLE public;
GRANT USAGE ON ALL PROCEDURES IN SCHEMA CARTO.CARTO TO ROLE public;
GRANT USAGE ON FUTURE PROCEDURES IN SCHEMA CARTO.CARTO TO ROLE public;
Mark the “All Queries†check on your worksheet or select all the lines manually to execute the whole script you pasted in the SQL editor.
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 password for the
CARTO user from the setup step.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, set the role that will be used to install the Analytics Toolbox:USE ROLE CARTO_ROLE;
USE DATABASE CARTO;
2. Create the functions and procedures
To install the functions and procedures 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.
This script will remove all the previously installed functions and procedures of the Analytics Toolbox in the
CARTO schema.3. Check installation
You can show the installed functions and procedures by running the following statements:
SHOW USER FUNCTIONS IN SCHEMA CARTO;
SHOW USER PROCEDURES IN SCHEMA CARTO;
You can check the version by running
SELECT CARTO.CARTO.VERSION_CORE().4. Import sample data (optional)
To do so, please run the following script in the same Worksheet you have used for the installation of the Analytics Toolbox:
-- 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.
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:
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.
- 2.Rename the database to the name
CARTO. 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â€.
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.
You can check the version by running
SELECT CARTO.CARTO.VERSION_CORE().Last modified 15d ago