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:
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:
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:
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:
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:
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
filemodules.sql
file
The Analytics Toolbox for Redshift is available for CARTO customers. Please get in touch with support@carto.com 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:
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:
Last updated