Manual installation in your own project

The manual installation of the Analytics Toolbox is recommended to support other regions required for your project or make the toolbox available inside a Virtual Private Cloud (VPC).

This guide will use Google Cloud Shell to set up and install the toolbox. Please open the GCP console and select the project to install the toolbox, then use the “>_” button (top right) to “Activate Cloud Shell”.

All following commands and instructions should be executed from the Cloud Shell in your console or from authenticated gcloudand bqCLI sessions.

Setup

This step consists of setting up the BigQuery project where you want to install the toolbox. A Google account is required.

Prepare the resources

You will need a GCP project to install the toolbox, as well as a storage bucket in the same project to store the JavasScript libraries needed. Users of the toolbox will need permission to read both the BigQuery dataset (where the functions and procedures will be installed) and the bucket in order to run the CARTO Analytics Toolbox.

We will set the project and bucket names as well as the location where the toolbox will be created (which should be the same as the bucket) as Cloud Shell environment variables:

• PROJECT: Project id where the toolbox dataset will be created

• REGION: Location of the BigQuery dataset that will be created to install the Analytics Toolbox

• BUCKET: Name of the bucket to store the JavasScript libraries needed by the toolbox (please omit any protocol prefix like gs://)

Set these variables by executing the following in Cloud Shell (after replacing the appropriate values):

export PROJECT="<my-project>"
export REGION="<my-region>"
export BUCKET="<my-bucket>"

Creating the dataset

This step is only required before the first installation. Activate the Cloud Shell in the target project and ensure the environment variables from the preparation step above are set.

Before starting the process make sure the target GCP project exists and that it is the correct one by executing the following:

# Check project existence
gcloud projects describe $PROJECT

Then, create a BigQuery dataset named carto, where the Analytics Toolbox will be installed:

# Create dataset "carto"
bq mk --location=$REGION --description="CARTO dataset" -d $PROJECT:carto

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.

Prepare the package

Please reach out to our Support team in order to get access to the installation package.

The CARTO team will provide a zip that contains the scripts to install the analytics toolbox:

• License file

• modules.sql file

• Libs directory with the JS files

Take a look at this documentation if you need help uploading the package to your Cloud Shell. Once you're all set with that, run the following commands:

# Make sure you use the correct file name
AT_PACKAGE=carto-analytics-toolbox-bigquery-latest.zip

# Uncompress package
unzip $AT_PACKAGE

# Enter the directory
cd $(unzip -Z -1 $AT_PACKAGE | head -1)

# Read the license
cat LICENSE

Create catalog tables and variables

The data module function uses the spatial catalog tables. Running the following commands, those tables are created in the carto dataset. When subscriptions are provisioned, the catalog tables will be updated.

# Create spatial catalog datasets
bq query --use_legacy_sql=false --project_id=$PROJECT 'CREATE TABLE IF NOT EXISTS carto.spatial_catalog_datasets(dataset_id string,dataset_slug string,dataset_name string,dataset_country string,dataset_category string,dataset_provider string,dataset_version string,dataset_geom_type string,dataset_is_public boolean,dataset_is_product boolean,associated_geography_id string);'

# Create spatial catalog variables
bq query --use_legacy_sql=false --project_id=$PROJECT 'CREATE TABLE IF NOT EXISTS carto.spatial_catalog_variables(variable_slug string,variable_name string,variable_description string,variable_type string,variable_aggregation string,dataset_slug string);'

Copy the libraries

This command installs the JavaScript libraries for the Analytics Toolbox in the selected bucket. These libraries will be used in runtime by the functions.

# Copy libs to bucket
gsutil -m cp -r libs/ gs://$BUCKET/carto/

Create the functions and procedures

This command installs the functions and procedures of the Analytics Toolbox in the selected project. This operation takes a few minutes to complete.

# Prepare SQL code
sed -e 's!@@BUCKET@@!'"$BUCKET"'!g' modules.sql > modules_rep.sql

# Create the functions and procedures
bq --location=$REGION --project_id=$PROJECT query --use_legacy_sql=false \
--max_statement_results=10000 --format=prettyjson < modules_rep.sql

The installation process might take up to 5 minutes.

Please be aware that this script will remove all the previous functions and procedures in the carto schema.

AT Gateway

Some functionalities of the CARTO Analytics Toolbox for BigQuery require making external calls from BigQuery to CARTO services. These calls are implemented via BigQuery Remote Functions:

• Creation of isolines, geocoding and routing require making calls to CARTO LDS API. In order to call this API, BigQuery needs a connection and a Cloud Run function that works as proxy.

• Some other functions of the Analytics Toolbox require making a request to the CARTO Platform backend. For this purpose, CARTO provides Cloud Run functions on different regions.

These services are readily available when using the Analytics Toolbox from a CARTO maintained project, but when installing the Analytics Toolbox manually in your own project, there is some configuration required:

• Create a BigQuery connection that will allow to call Cloud Run functions from BigQuery.

• Select an AT Gateway endpoint that is in the same region as your project.

If you are installing a CARTO Self Hosted in a Google Cloud VPC, please refer to this section to learn how to provision the AT Gateway in your VPC before proceeding with this part of the configuration.

Create a BigQuery connection

As mentioned above, BigQuery connections are used for running remote functions from BigQuery.

Create a connection with the command below. Please note that bigquery.connections.delegate permission will be required:

# Create the connection
bq mk --connection --display_name='carto-conn' \
--connection_type=CLOUD_RESOURCE --project_id=$PROJECT \
--location=$REGION carto-conn

Export the CONNECTIONvariable:

export CONNECTION="$PROJECT.$REGION.carto-conn"

Find an AT Gateway endpoint

At this point, if you're installing the Analytics Toolbox in a Google Cloud VPC, please refer to this section.

From this table, find the AT Gateway endpoint that correspond with your region. For US and EU multi-regions, choose any US or EU endpoint.

carto-analytics-toolbox	atgwfunc-as-ea1-7604	asia-east1	https://atgwfunc-as-ea1-7604-cmc6qlhtda-de.a.run.app
carto-analytics-toolbox	atgwfunc-as-ea2-9555	asia-east2	https://atgwfunc-as-ea2-9555-cmc6qlhtda-df.a.run.app
carto-analytics-toolbox	atgwfunc-as-ne1-7871	asia-northeast1	https://atgwfunc-as-ne1-7871-cmc6qlhtda-an.a.run.app
carto-analytics-toolbox	atgwfunc-as-ne2-3699	asia-northeast2	https://atgwfunc-as-ne2-3699-cmc6qlhtda-dt.a.run.app
carto-analytics-toolbox	atgwfunc-as-ne3-9803	asia-northeast3	https://atgwfunc-as-ne3-9803-cmc6qlhtda-du.a.run.app
carto-analytics-toolbox	atgwfunc-as-se1-9008	asia-southeast1	https://atgwfunc-as-se1-9008-cmc6qlhtda-as.a.run.app
carto-analytics-toolbox	atgwfunc-as-se2-9937	asia-southeast2	https://atgwfunc-as-se2-9937-cmc6qlhtda-et.a.run.app
carto-analytics-toolbox	atgwfunc-as-so1-7788	asia-south1	https://atgwfunc-as-so1-7788-cmc6qlhtda-el.a.run.app
carto-analytics-toolbox	atgwfunc-as-so2-6443	asia-south2	https://atgwfunc-as-so2-6443-cmc6qlhtda-em.a.run.app
carto-analytics-toolbox	atgwfunc-au-se1-2301	australia-southeast1	https://atgwfunc-au-se1-2301-cmc6qlhtda-ts.a.run.app
carto-analytics-toolbox	atgwfunc-au-se2-9274	australia-southeast2	https://atgwfunc-au-se2-9274-cmc6qlhtda-km.a.run.app
carto-analytics-toolbox	atgwfunc-eu-ce2-6914	europe-central2	https://atgwfunc-eu-ce2-6914-cmc6qlhtda-lm.a.run.app
carto-analytics-toolbox	atgwfunc-eu-no1-6484	europe-north1	https://atgwfunc-eu-no1-6484-cmc6qlhtda-lz.a.run.app
carto-analytics-toolbox	atgwfunc-eu-sw1-1259	europe-southwest1	https://atgwfunc-eu-sw1-1259-cmc6qlhtda-no.a.run.app
carto-analytics-toolbox	atgwfunc-eu-we1-1809	europe-west1	https://atgwfunc-eu-we1-1809-cmc6qlhtda-ew.a.run.app
carto-analytics-toolbox	atgwfunc-eu-we12-743	europe-west12	https://atgwfunc-eu-we12-743-cmc6qlhtda-og.a.run.app
carto-analytics-toolbox	atgwfunc-eu-we2-9846	europe-west2	https://atgwfunc-eu-we2-9846-cmc6qlhtda-nw.a.run.app
carto-analytics-toolbox	atgwfunc-eu-we3-9705	europe-west3	https://atgwfunc-eu-we3-9705-cmc6qlhtda-ey.a.run.app
carto-analytics-toolbox	atgwfunc-eu-we4-4558	europe-west4	https://atgwfunc-eu-we4-4558-cmc6qlhtda-ez.a.run.app
carto-analytics-toolbox	atgwfunc-eu-we6-5628	europe-west6	https://atgwfunc-eu-we6-5628-cmc6qlhtda-oa.a.run.app
carto-analytics-toolbox	atgwfunc-eu-we9-8712	europe-west9	https://atgwfunc-eu-we9-8712-cmc6qlhtda-od.a.run.app
carto-analytics-toolbox	atgwfunc-me-ce1-6804	me-central1	https://atgwfunc-me-ce1-6804-cmc6qlhtda-ww.a.run.app
carto-analytics-toolbox	atgwfunc-me-we1-8177	me-west1	https://atgwfunc-me-we1-8177-cmc6qlhtda-zf.a.run.app
carto-analytics-toolbox	atgwfunc-na-ne1-9070	northamerica-northeast1	https://atgwfunc-na-ne1-9070-cmc6qlhtda-nn.a.run.app
carto-analytics-toolbox	atgwfunc-na-ne2-1664	northamerica-northeast2	https://atgwfunc-na-ne2-1664-cmc6qlhtda-pd.a.run.app
carto-analytics-toolbox	atgwfunc-sa-ea1-5299	southamerica-east1	https://atgwfunc-sa-ea1-5299-cmc6qlhtda-rj.a.run.app
carto-analytics-toolbox	atgwfunc-sa-we1-1940	southamerica-west1	https://atgwfunc-sa-we1-1940-cmc6qlhtda-tl.a.run.app
carto-analytics-toolbox	atgwfunc-us-ce1-4566	us-central1	https://atgwfunc-us-ce1-4566-cmc6qlhtda-uc.a.run.app
carto-analytics-toolbox	atgwfunc-us-ea1-8111	us-east1	https://atgwfunc-us-ea1-8111-cmc6qlhtda-ue.a.run.app
carto-analytics-toolbox	atgwfunc-us-ea4-8568	us-east4	https://atgwfunc-us-ea4-8568-cmc6qlhtda-uk.a.run.app
carto-analytics-toolbox	atgwfunc-us-we1-4218	us-west1	https://atgwfunc-us-we1-4218-cmc6qlhtda-uw.a.run.app
carto-analytics-toolbox	atgwfunc-us-we2-3390	us-west2	https://atgwfunc-us-we2-3390-cmc6qlhtda-wl.a.run.app
carto-analytics-toolbox	atgwfunc-us-we3-1834	us-west3	https://atgwfunc-us-we3-1834-cmc6qlhtda-wm.a.run.app
carto-analytics-toolbox	atgwfunc-us-we4-1057	us-west4	https://atgwfunc-us-we4-1057-cmc6qlhtda-wn.a.run.app

Export the GATEWAY_ENDPOINT variable:

export ENDPOINT="<gateway_endpoint>"

Find CARTO LDS API connection details

In order to use the functions in the lds module, you need to get the API Base URL and LDS Token from your CARTO account.

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:

Let's export those values as variables:

export API_BASE_URL="<api-base-url>"
export API_ACCESS_TOKEN="<api-access-token>"

Run the SETUP procedure

Now that we have collected all the necessary parameters, we can call the SETUP procedure to have our own installation of the Analytics Toolbox ready and available:

CALL carto.SETUP("""{
  "connection": "{CONNECTION}",
  "endpoint": "{ENDPOINT}",
  "api_base_url": "{API_BASE_URL}",
  "api_access_token": "{API_ACCESS_TOKEN}"
}'""");

After running the previous query, the CARTO Analytics Toolbox should be ready to work in your BigQuery project. In order to check if the installation process has worked as expected, you can execute the following queries in the BigQuery console. It will create a table called geocode_test_table containing a gecoded address.

CREATE TABLE {DATASET}.geocode_test_table AS (
  SELECT "Madrid" AS address
)

CALL carto.GEOCODE_TABLE(NULL,NULL,'{PROJECT}.{DATASET}.geocode_test_table','address',NULL, NULL, NULL);

🎉Congratulations! If the previous query execution finishes and you can obtain the geocoded address querying the table that has been created your installation is complete. Now, remember to setup your connections to BigQuery with the correct Analytics Toolbox location setting to ensure that all queries generated by CARTO applications use it.

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.