# 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](https://console.cloud.google.com/) and select the project to install the toolbox, then use the “>\_” button (top right) to “Activate Cloud Shell”.

<figure><img src="https://lh7-us.googleusercontent.com/cGiDvMqDIAMgkgLmckYbe1J87m23_PJ6GeaM_WSWYpkCqg2x-OZSoYYU1dlgO-gbvYDkF2xICHBlSqsghM2FooRNud1-GlEgit2EXM4eTSaGx2UlpfTKrzhday48_cCd-8cgbgJoHY5cN0Vemm35QA0" alt=""><figcaption></figcaption></figure>

{% hint style="info" %}
All following commands and instructions should be executed from the Cloud Shell in your console or from authenticated `gcloud`and `bq`CLI sessions.
{% endhint %}

## 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](https://cloud.google.com/bigquery/docs/locations) 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):

```sh
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:

```sh
# Check project existence
gcloud projects describe $PROJECT
```

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

```sh
# 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

{% hint style="info" %}
Please [reach out to our Support team](mailto:support@carto.com) in order to get access to the installation package.
{% endhint %}

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](https://cloud.google.com/shell/docs/uploading-and-downloading-files) if you need help uploading the package to your Cloud Shell. Once you're all set with that, run the following commands:

```sh
# 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.

```sh
# 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.

```sh
# 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.

```sh
# 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.

{% hint style="danger" %}
Please be aware that this script will remove all the previous functions and procedures in the **`carto`** schema.
{% endhint %}

## 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**](https://cloud.google.com/bigquery/docs/remote-functions)**.** In order to call the CARTO API, BigQuery needs a connection and a Cloud Run function that works as prox&#x79;**:**

* Creation of isolines, geocoding and routing require making calls to CARTO LDS API. 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.
* Some other functionalities, like [HTTP Request](https://docs.carto.com/carto-user-manual/workflows/components/input-output#http-request) or [Create Builder Map](https://docs.carto.com/carto-user-manual/workflows/components/input-output#create-builder-map) 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](https://docs.carto.com/data-and-analysis/analytics-toolbox-for-bigquery/getting-access/projects-maintained-by-carto-in-different-bigquery-regions), but when installing the Analytics Toolbox manually in your own project, there is some configuration required:

* Create a [BigQuery connection](https://cloud.google.com/bigquery/docs/connections-api-intro) that will allow to call Cloud Run functions from BigQuery.
* Select an AT Gateway endpoint that is in the same region as your project.

{% hint style="info" %}
If you are installing a **CARTO Self Hosted in a Google Cloud VPC**, please refer to [this section](https://docs.carto.com/data-and-analysis/analytics-toolbox-for-bigquery/getting-access/installation-in-a-google-cloud-vpc) to learn how to provision the AT Gateway in your VPC before proceeding with this part of the configuration.
{% endhint %}

### 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`](https://cloud.google.com/bigquery/docs/access-control#bq-permissions) permission will be required:

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

\
Export the `CONNECTION`variable:

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

### Find an AT Gateway endpoint

{% hint style="info" %}
At this point, if you're installing the Analytics Toolbox in a Google Cloud VPC, please refer to [this section](https://docs.carto.com/data-and-analysis/analytics-toolbox-for-bigquery/getting-access/installation-in-a-google-cloud-vpc).
{% endhint %}

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.

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

Export the `GATEWAY_ENDPOINT` variable:

```sh
export ENDPOINT="<gateway_endpoint>"
```

### Find CARTO LDS API connection details

In order to use the functions in the[ lds module](https://docs.carto.com/data-and-analysis/analytics-toolbox-for-bigquery/sql-reference/lds), 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](https://docs.carto.com/carto-user-manual/developers/api-base-url).

To get the LDS Token, go to the “Developers” section and create a new API Access Token. For more information, [check the documentation](https://docs.carto.com/carto-user-manual/developers/api-access-tokens). Make sure your token has LDS API enabled:

<figure><img src="https://lh7-us.googleusercontent.com/OkY0TPwu3CdjMD5P3-rRmI9936KkdVF70zDdH8lSZZxib9uuM1He3j7NDFDjsj55b8VubsTCPMPJ2gpTrUNskLahYE6QaTYPcAqZ6E_TLLOgT6ur1NgCTJ5YozTnOWuvx6J3OrFqCbyeDiSUjjGNJSs" alt=""><figcaption></figcaption></figure>

Let's export those values as variables:

```sh
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:

```sql
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.

```sql
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);
```

:tada:**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**](https://docs.carto.com/carto-user-manual/connections/creating-a-connection#advanced-options-for-bigquery) setting to ensure that all queries generated by CARTO applications use it.

{% hint style="info" %}
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.

<img src="https://3029946802-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FybPdpmLltPkzGFvz7m8A%2Fuploads%2Fgit-blob-acbc5e6073857c9a52e36f50e941c334c4ea9fdc%2FScreenshot%202024-01-10%20at%2016.43.37.png?alt=media" alt="" data-size="original">
{% endhint %}