How to manage connections to datasources

Working with Connections

This tutorial shows how to work with connections to datasources in CARTO.

Introduction

The CARTO dashboard includes functionality for registering and managing connections to datasources. You can register connections to cloud storage services, databases and data warehouses. Once you have registered a connection, you can perform different operations with the datasource, like executing the functions provided by the CARTO spatial extension (if available for the datasource) or importing datasets.

Creating a connection

If you go to the Data page in the dashboard, you will see a list of your current datasets. If you select the “Your connections” tab, you will see a list of your current connections. If you haven’t registered a connection yet, you will see the following page:

The workflow for adding a connection is as follows:

1. Click on the “Add Connection” button
2. Select the connector for your datasource
3. Enter the connection parameters and credentials

When you click on the “Add Connection” button, you will see a new modal dialog for selecting the connector to use:

You can create connections using two different types of connectors:

• Database connectors. This group includes connectors for cloud data warehouses and relational databases. In this case, we need to enter the connection parameters such as the server, username or password or provide a service account, depending on the connector. You need to ensure that CARTO can connect to your database: if you have a firewall, you need to open it to the CARTO public IP addresses. In the connection parameters form, you will see the list of IP addresses on the right side.

• Cloud Files connectors. This group includes connectors for cloud storage services. The authentication with these services is configured using OAuth. When you select one of these connectors, we will show the consent dialog from the service provider.

After you have provided the connection parameters or have given your consent to the CARTO platform, you will see the following screen:

In this dialog, you can go back to the “Your Connections” page or you can directly import a dataset. If the connector provides access to the spatial extension visualization module, you will see an additional option to navigate to the page that lists your tilesets.

If you go back to the “Your connections” page, you will see your newly registered connection:

If you click on the connection, you will be redirected to the page for importing a dataset using the connection. If you move the mouse over the connection, you will be able to access the quick actions menu by clicking on the three point icon in the top right corner.

For cloud storage services connections through OAuth, the only action available will be “Delete”, which will revoke consent. For database connections, in addition to the “Delete” action, you will see an “Edit” action that can be used to modify the connection parameters. You can find more information about the actions for editing and deleting a connection below.

In addition to the quick actions, if you move the mouse over the “Parameters” text at the bottom of the connection, you will see a tooltip with the connection parameters.

In the next sections we show how to configure connections to the supported datasources.

Connection to BigQuery

CARTO uses access credentials to connect to BigQuery in order to run queries on your behalf.

You can use parts of CARTO directly by using the BigQuery console through the CARTO spatial extension, or you can use the CARTO dashboard to launch different operations, like tileset generation.

We will use the email associated with your CARTO account to grant access to the extension from the BigQuery Console. If you signed up using your Google Account, there is nothing else to do. Otherwise, please update your email address in your account settings to match your Google Account before registering the connection.

When you access BigQuery, the billing account associated with the selected billing project will be charged. Unless you have a flat-rate pricing agreement, we recommend you configure certain limits in BigQuery to avoid any unexpected charges.

When you select the BigQuery connector in the “Add new connection” dialog, you will see the following page:

If you click the “Continue” button, you will be redirected to the page where you need to provide your service account key file in JSON format. Please read the following instructions for creating a service account and a service account key file.

After you have uploaded your file, you can click on the “Continue” button. CARTO will try to connect with your BigQuery data warehouse using the provided service account.

If the connection is successful, you will be redirected to the billing project selection page. The selector will show the projects accessible from the provided service account. The billing account associated with the selected project will be charged when you access BigQuery from CARTO. CARTO will access BigQuery for importing datasets, creating and updating tilesets, and executing functions from the spatial extension.

Finally you can click on the “Connect” button and the connection will be registered.

Connection to Snowflake

If you want to create a connection to your Snowflake account, you need to select the Snowflake connector in the “Add new connection” dialog. After you select the connector, you will presented with the following dialog for entering the connection parameters:

These are the parameters you need to provide:

Parameter	Description
Name	You can register different connections with the Snowflake connector. You can use the name to identify the connections.
Server	Hostname for your account in the following format: <account_name>.snowflakecomputing.com
Database	Default database your connection will use.
Username	Login name for the Snowflake user.
Password	Password for the Snowflake user.
Warehouse	Default warehouse that will run your queries. This parameter is optional.

Currently we only support username/password authentication using the internal Snowflake authenticator. We don’t support federated authentication/SSO, OAuth or SAML 2.0 compliant identity providers.

If you have any kind of IP filtering to restrict access to your Snowflake account, you need to allow access to the CARTO servers public IP addresses that are listed in the connection parameters dialog.

Once you have entered the parameters, you can click the “Connect” button. CARTO will try to connect to your Snowflake account. If everything is OK, your new connection will be registered.

Connection to Amazon Redshift

If you want to create a connection to your Redshift cluster, you need to select the Redshift connector in the “Add new connection” dialog. After you select the connector, you will presented with the following dialog for entering the connection parameters:

These are the parameters you need to provide:

Parameter	Description
Name	You can register different connections with the Redshift connector. You can use the name to identify the connections.
Cluster	DNS name or IP address for your Redshift cluster.
Port	TCP port where your cluster is listening for connections.
Database	Database your connection will use.
Username	Name of the user account.
Password	Password for the user account.

If you have any kind of IP filtering / VPC security group to restrict access to your Redshift cluster, you need to allow access to the CARTO servers public IP addresses that are listed in the connection parameters dialog.

Once you have entered the parameters, you can click the “Connect” button. CARTO will try to connect to your Redshift cluster. If everything is OK, your new connection will be registered.

Connection to PostgreSQL

If you want to create a connection to your PostgreSQL server, you need to select the PostgreSQL connector in the “Add new connection” dialog. After you select the connector, you will be presented with the following dialog for entering the connection parameters:

These are the parameters you need to provide:

Parameter	Description
Name	You can register different connections with the PostgreSQL connector. You can use the name to identify the connections.
Server	DNS name or IP address for your PostgreSQL server.
Port	TCP port where your server is listening for connections.
Database	Database your connection will use.
Username	Name of the user account.
Password	Password for the user account.

You can use this connector to register connections to PostgreSQL-compatible databases including Amazon Aurora, Amazon RDS for PostgreSQL, Google Cloud for PostgreSQL and Microsoft Azure Database for PostgreSQL.

If you have any kind of IP filtering to restrict access to your PostgreSQL server, you need to allow access to the CARTO servers public IP addresses that are listed in the connection parameters dialog.

Once you have entered the parameters, you can click the “Connect” button. CARTO will try to connect to your PostgreSQL server. If everything is OK, your new connection will be registered.

Connection to MySQL

If you want to create a connection to your MySQL server, you need to select the MySQL connector in the “Add new connection” dialog. After you select the connector, you will presented with the following dialog for entering the connection parameters:

These are the parameters you need to provide:

Parameter	Description
Name	You can register different connections with the MySQL connector. You can use the name to identify the connections.
Server	DNS name or IP address for your MySQL server.
Port	TCP port where your server is listening for connections.
Database	Database your connection will use.
Username	Name of the user account.
Password	Password for the user account.

You can use this connector to register connections to MySQL-compatible databases including MariaDB, Amazon Aurora, Amazon RDS for MySQL, Google Cloud for MySQL and Microsoft Azure Database for MySQL.

If you have any kind of IP filtering to restrict access to your MySQL server, you need to allow access to the CARTO servers public IP addresses that are listed in the connection parameters dialog.

Once you have entered the parameters, you can click the “Connect” button. CARTO will try to connect to your MySQL server. If everything is OK, your new connection will be registered.

Connection to Microsoft SQL Server

If you want to create a connection to your Microsoft SQL Server instance, you need to select the SQL Server connector in the “Add new connection” dialog. After you select the connector, you will presented with the following dialog for entering the connection parameters:

These are the parameters you need to provide:

Parameter	Description
Name	You can register different connections with the SQL Server connector. You can use the name to identify the connections.
Server	DNS name or IP address for your SQL Server.
Port	TCP port where your server is listening for connections.
Database	Database your connection will use.
Username	Name of the user account.
Password	Password for the user account.

You can use this connector to register connections to SQL Server compatible databases including Amazon RDS for SQL Server, Google Cloud for SQL Server and Azure SQL Database.

If you have any kind of IP filtering to restrict access to your SQL Server instance, you need to allow access to the CARTO servers public IP addresses that are listed in the connection parameters dialog.

Once you have entered the parameters, you can click the “Connect” button. CARTO will try to connect to your SQL Server instance. If everything is OK, your new connection will be registered.

Connection to Cloud Storage Services

All the connectors for cloud storage services (Google Drive, Box and Dropbox) use the OAuth protocol for accessing your cloud storage files from your CARTO account. In all cases the workflow is the same: when you click on the connector, you are redirected to an external page from the cloud storage provider where you need to give consent for CARTO to access your account.

After you have given consent, when you click on a cloud storage service connection the dashboard will show a list of files from your account. Currently folder navigation is not supported. That means all files, including those shared with you, are presented in a single list. If you want to find a specific file, you can use your browser’s find functionality to search for the file name.

Editing a connection

If you click on the “Edit” quick action, you will be redirected to a dialog for editing connection parameters. This dialog contains the same form that you filled out when adding the connection, showing the current values.

If you edit the connection parameters and you have datasets that are syncing at periodic intervals, these datasets will no longer sync if they are not available using the modified connection parameters. You will see a warning informing you of that at the top of the form.

After you have modified the parameters, click the “Save & Connect” button. The CARTO platform will try to connect to the datasource. If the connection is successful, the new connection parameters will be saved.

Deleting a connection

If you click on the “Delete” quick action, you will be redirected to a dialog where you need to confirm the deletion. If you delete the connection and you had datasets that were syncing at periodic intervals, they will no longer be updated but they will not be deleted from your account. As we have already mentioned above, deleting an OAuth connection will revoke the consent to access your account from the CARTO platform.