Native App from Snowflake's Marketplace

This guide illustrates the steps to install the CARTO Analytics Toolbox for Snowflake as a Native App available from the Snowflake's Marketplace.

1 - Get the Native App

In order to find the CARTO Analytics Toolbox native app, you need to login to your Snowflake account and use the left panel to find Data Products > Marketplace, and search for "CARTO Analytics Toolbox".

Alternatively, you can directly use this link: https://app.snowflake.com/marketplace/listing/GZT0Z4CM1E9NA/carto-carto-analytics-toolbox

Once there, click on 'Get'. At this point, you can choose between two different options:

• Free 30-day trial: This will give you access to the application for 30 days, after which you will loose access to it. If you're going with this option, you can also use a CARTO trial account to get the necessary credentials to setup the application later. You can do so through your Partner Connect portal or signing up at carto.com/signup.

• Request full product: A request will be sent to the CARTO team. Once approved (provided that you're a CARTO customer) you will be able to proceed with the installation. You can also request the full product after installing the trial version, using the button Request full product in the Native App's listing.

After the installation is complete, you will be able to access the Native App's by clicking on Open in the app's listing or via the Apps section under Data Products in your Snowflake account. Either of those will get you to the instructions to finalize the installation by running a few SQL commands in the Snowflake console.

2 - Installation

This section contains the same explanation and code samples that can be found in the Native App's page in your Snowflake account.

The Native App contains the code necessary to install and configure all functions and procedures in the CARTO Analytics Toolbox. In order to be used, they need to be created first in your database.

2.1 - Install the functions and procedures

For the sake of documenting the process, we'll will assume a database named CARTO, as well as a schema named CARTO in that database, also we assume the app to be called CARTO_ANALYTICS_TOOLBOX. The next guidelines and examples will assume that in order to simplify the onboarding process.

All the database, schema and user can have a different name, but remember to adapt the code snippets accordingly.

-- Set admin permissions
USE ROLE ACCOUNTADMIN;

-- Create the carto database
CREATE DATABASE CARTO;

-- Create the carto schema
CREATE SCHEMA CARTO.CARTO;

-- Grant all to sysadmin role
GRANT ALL ON SCHEMA CARTO.CARTO TO ROLE SYSADMIN;

-- Set create function and procedure permissions
GRANT USAGE ON DATABASE CARTO TO APPLICATION CARTO_ANALYTICS_TOOLBOX;
GRANT USAGE, CREATE FUNCTION, CREATE PROCEDURE ON SCHEMA CARTO.CARTO TO APPLICATION CARTO_ANALYTICS_TOOLBOX;

-- Generate the installer procedure in the specified location
CALL CARTO_ANALYTICS_TOOLBOX.CARTO.GENERATE_INSTALLER('CARTO.CARTO');

-- Update ownership of the install procedure
GRANT OWNERSHIP ON PROCEDURE CARTO.CARTO.INSTALL(STRING, STRING) TO ROLE ACCOUNTADMIN REVOKE CURRENT GRANTS;

-- Grant usage to public role
GRANT USAGE ON DATABASE CARTO TO ROLE PUBLIC;
GRANT USAGE ON SCHEMA CARTO.CARTO TO ROLE PUBLIC;
GRANT SELECT ON FUTURE TABLES IN SCHEMA CARTO.CARTO TO ROLE PUBLIC;
GRANT SELECT ON FUTURE VIEWS IN SCHEMA CARTO.CARTO TO ROLE PUBLIC;
GRANT USAGE ON FUTURE FUNCTIONS IN SCHEMA CARTO.CARTO TO ROLE PUBLIC;
GRANT USAGE ON FUTURE PROCEDURES IN SCHEMA CARTO.CARTO TO ROLE PUBLIC;

-- Install the Analytics Toolbox in CARTO.CARTO
CALL CARTO.CARTO.INSTALL('CARTO_ANALYTICS_TOOLBOX', 'CARTO.CARTO');

2.2 - Setup LDS Services

Select a Lambda function

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 proximity to the region of your Snowflake account:

AWS Region	Region name	API Gateway
United States East (N. Virgina)	us-east-1	https://m0qahrqhei.execute-api.us-east-1.amazonaws.com/production/lds
Europe (Ireland)	eu-west-1	https://a3g86ayq28.execute-api.eu-west-1.amazonaws.com/production/lds
Asia Pacific (Sydney)	ap-southeast-2	https://wccsrp480l.execute-api.ap-southeast-2.amazonaws.com/production/lds
Asia Pacific (Tokyo)	ap-northeast-1	https://d0ril9q8b8.execute-api.ap-northeast-1.amazonaws.com/production/lds

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.

Create a Snowflake API integration

To make the connection between Snowflake and the API Gateway, Snowflake uses an 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_API
  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');

Find your CARTO API Base URL and API Access Token

For this step, a CARTO account is needed.

Once you're logged in, go to the 'Developers' section, where you will find your API Base URL and will be able to create an API Access Token. Make sure that LDS API is allowed for your token.

Run the SETUP procedure

Once you have collected the four needed configuration parameters explained above, we can proceed and configure Location Data Services in your Analytics Toolbox instalaltion, by calling the SETUP procedure, like:

CALL CARTO.CARTO.SETUP('{
  "api_integration": "CARTO_API",
  "endpoint": "https://m0qahrqhei.execute-api.us-east-1.amazonaws.com/production/lds",
  "api_base_url": "MY_API_BASE_URL",
  "api_access_token": "MY_API_ACCESS_TOKEN"
}');

2.3 - Configure your CARTO connection

Now, you need to create a CARTO connection to Snowflake. Follow this documentation page to find more information.

Make sure that you open the Advanced options and set the Analytics Toolbox location setting to the database and schema where the functions and procedures of the Native Apps have been installed in the first step above.

Congrats! if everything went well, you should be able to use your CARTO Analytics Toolbox for Snowflake both from

3 - Updates

Your Native App gets updated automatically when CARTO release new versions of the Analytics Toolbox.

You can find detailed release notes here. The version that is currently available can be found in your Snowflake account by navigating to Data Products > Apps

However, as mentioned before, that's the version of the installer for the actuall functions and procedures. In order to recreate them with the latest version, you will need to reinstall them with:

-- Set admin permissions
USE ROLE ACCOUNTADMIN;

-- Generate the installer procedure in the specified location
CALL CARTO_ANALYTICS_TOOLBOX.CARTO.GENERATE_INSTALLER('CARTO.CARTO');

-- Update ownership of the install procedure
GRANT OWNERSHIP ON PROCEDURE CARTO.CARTO.INSTALL(STRING, STRING) TO ROLE ACCOUNTADMIN REVOKE CURRENT GRANTS;

-- Install the Analytics Toolbox in CARTO.CARTO
CALL CARTO.CARTO.INSTALL('CARTO_ANALYTICS_TOOLBOX', 'CARTO.CARTO');