Manual installation in your database

Introduction

This guide contains the steps to install the CARTO Analytics Toolbox for Redshift. This package includes access to both “Core” and “Advanced” modules, which include the LDS functions.

Setup

This step consists of setting up the Redshift cluster and database where we want to install the toolbox.

• An AWS account is required.

• You can check your clusters at the link https://console.aws.amazon.com/redshiftv2.

• Amazon Redshift clusters are installed in a specific region (e.g. us-east-1). Identify the region of your cluster to be used in future steps.

AWS configuration

CARTO Analytics Toolbox uses external lambda functions to perform the requests from Redshift to the external LDS provider. CARTO provides a role to make use of the lambda functions: arn:aws:iam::000955892807:role/CartoFunctionsRole.

In order to obtain invoke permissions over these lambdas, you will need to create a role in your account to assume CARTO’s role. Follow the next steps in your AWS account:

• Go to IAM > Policies > Create Policy, and create a JSON policy with the name “CartoFunctionsRolePolicy” and the following content:

{
    "Version": "2012-10-17",
    "Statement": [
      {
          "Effect": "Allow",
          "Action": "sts:AssumeRole",
          "Resource": "arn:aws:iam::000955892807:role/CartoFunctionsRole",
          "Sid": "AssumeCartoFunctionsRole"
      }
    ]
}

• Go to IAM > Roles > Create Role, and create a role for the use case Redshift - Customizable with the permissions policy “CartoFunctionsRolePolicy” and the name “CartoFunctionsRedshiftRole”.

• Copy the ARN of the role (e.g. “arn:aws:iam::XXXXXXXXXXXX:role/CartoFunctionsRedshiftRole”).

• Ensure that the trust relationship of the new role is compatible with Redshift:

{
    "Version": "2012-10-17",
    "Statement": [
      {
          "Effect": "Allow",
          "Principal": {
              "Service": "redshift.amazonaws.com"
          },
          "Action": "sts:AssumeRole"
      }
    ]
}

• Go to Amazon Redshift > your cluster > Properties > Cluster permissions > Associated IAM roles, and associate the new IAM role to your Redshift cluster. This will allow the role to be used by Redshift in the external function.

Creating the schema

Once the account and the cluster are created, connect the database to create the carto schema. The CARTO Analytics Toolbox will be installed in this schema.

To do this, connect to your Redshift database and run the following script:

-- Create the carto schema
CREATE SCHEMA carto;
-- Grant usage to public role
GRANT USAGE ON SCHEMA carto TO public;
GRANT USAGE ON LANGUAGE plpgsql TO public;
GRANT USAGE ON LANGUAGE plpythonu TO public;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA carto TO public;
GRANT EXECUTE ON ALL PROCEDURES IN SCHEMA carto TO public;

AT Gateway configuration

The functions and procedures in the lds module provide access to geocoding and isolines services from different providers directly in your database via SQL. The service provider for each LDS function/procedure will depend on your contractual arrangements with CARTO.

Lambda function

Redshift external functions require calling a lambda function deployed in the same region of the cluster. These are the regions currently available for CARTO LDS:

ap-northeast-1	arn:aws:lambda:us-east-1:000955892807:function:at-gateway-asia-northeast1
ap-southeast-2	arn:aws:lambda:us-east-1:000955892807:function:at-gateway-australia-southeast1
eu-west-1	arn:aws:lambda:us-east-1:000955892807:function:at-gateway-europe-west1
us-east-1	arn:aws:lambda:us-east-1:000955892807:function:at-gateway-us-east1

Let’s pick, for example, the region “us-east-1”; the lambda name will be “lds-function-us-east1”. The IAM roles should look like this:

"arn:aws:iam::XXXXXXXXXXXX:role/CartoFunctionsRedshiftRole,arn:aws:iam::000955892807:role/CartoFunctionsRole"

Keep the Lambda name (LAMBDA) and the IAM roles (ROLE) to be used in the Installation section.

API Base URL and LDS Token

The Analytics Toolbox LDS functions and procedures make requests to the CARTO LDS API in your account. You need to store the API Base URL and LDS Token in Redshift.

To get the API Base URL, go to the “Developers” section in the CARTO Platform and copy the value. For more information, check the documentation.

To get the LDS Token, go to the “Developers” section and create a new API Access Token. For more information, check the documentation. Make sure your token has LDS API enabled:

Then, keep the API Base URL (API_BASE_URL) and LDS Token (API_ACCESS_TOKEN) to be used in the Installation section:

Installation

Download the Analytics Toolbox package

The installation pacakge comes in zip file that contains the scripts to install the Analytics Toolbox, in this case with the LDS module. Unzip the content in your local storage. This package will contain:

• LICENSE file

• libraries.sql file

• modules.sql file

The Analytics Toolbox for Redshift is available for CARTO customers. Please get in touch with [e-mail] in order to get the installation package.

Create the libraries

Run the libraries.sql file to create the Python libraries used by the toolbox in the cluster.

Troubleshooting If the following error arises “Must be superuser or the owner of library X”, it means that the library is already created by a superuser, so your user can not replace it. If the superuser drops the library (DROP LIBRARY X), then your user will be able to create it.

Create the functions and procedures

Run the modules.sql file to create the SQL functions and procedures in the carto schema.

WARNING

This script will remove all the previous functions and procedures in the carto schema.

Setup LDS variables

To configure the LDS functions, we need to run the SETUP procedure with the LDS variables:

CALL carto.SETUP('{
  "lambda": "{LAMBDA}",
  "roles": "{ROLES}",
  "api_base_url": "{API_BASE_URL}",
  "api_access_token": "{API_ACCESS_TOKEN}"
}');

NOTE: if the SETUP procedure is not executed, the LDS functions will return an informative message, but the rest of the toolbox will be completely functional.

Congratulations!

You have successfully installed the CARTO Analytics Toolbox in your Redshift database.

After an installation or update of the Analytics Toolbox is performed, the CARTO connection needs to refreshed by the owner of the connection by clicking on the refresh button on the connection's card.

Now you can start using the functions in the SQL reference 🎉

Upgrade

In order to upgrade your Analytics Toolbox installation, you just need to follow the same steps from the Installation section. The new functions and procedures will replace their previous versions.

After the steps from the installation are completed, grant permission again with:

GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA carto TO public;
GRANT EXECUTE ON ALL PROCEDURES IN SCHEMA carto TO public;