How to export data in CARTO
In CARTO there are different options for downloading your CARTO datasets, or the result of SQL queries applied to your CARTO datasets.
In this tutorial we’ll show the export options available within the CARTO Dataset interface and the CARTO SQL API.
When a CARTO dataset is opened, CARTO shows an interface with different elements such as the title of the dataset, the data itself, a SQL panel button and some other options.
To export the CARTO dataset’s data, we need to click on the
export option located at the top right side of the dataset:
export, a menu will appear with several options to download the dataset. Select the one you need and click on
The CARTO SQL API is being used behind-the-scenes when this data is being exported. Therefore we’re constrained by our account’s CARTO SQL API limits.
It’s important to note that clicking the
export option does not necessarily download all of the dataset’s data. Only the data resulting from the dataset’s SQL panel query is exported.
For example, if you apply a custom query like
SELECT * FROM tableName WHERE column < X instead of the dataset’s default
SELECT * FROM tableName query, CARTO will download the custom query’s result instead of the entire dataset’s rows and columns.
CARTO SQL API
The CARTO SQL API offers two ways to download our CARTO dataset data.
1. CARTO SQL API request with
We can use the CARTO SQL API to retrieve the result of a SQL query applied to our CARTO dataset.The result is contained in the request’s response. For example:
https://username.carto.com/api/v2/sql?q=SELECT * FROM table_name
If we want to download the query result as a file, we need to add the
format parameter to our CARTO SQL API request. For example, in the following request we’re downloading data from a table named
table_name as a GeoJSON file:
https://username.carto.com/api/v2/sql?format=GeoJSON&q=SELECT * FROM table_name
In this section of our Developer Center you can find other formats available for downloading data files.
2. COPY TO
The CARTO SQL API is very useful, but it might return a timeout error if you’re trying to export big datasets.
That’s why we recommend using the CARTO SQL API’s COPY TO command to export big datasets as CSV files. In this section of our Developer Center you can find detailed information about using the CARTO SQL API COPY TO command to export data.
The COPY command also has limits. However, its limits are different than the default limits for the CARTO SQL API. You can find detailed information about COPY command limits in this section of the CARTO Developer Center.
These two libraries provide methods that use the SQL API (and other CARTO APIs behind-the-scenes). You can use these methods to define your own workflow for exporting data from your CARTO account.
- We don’t recommend downloading datasets with line or polygon geometries in the CSV format.
- If you need to download big CARTO datasets in a format other than CSV, it might be useful to export them “in parts” to avoid SQL API timeout limits.
For example, we could download our CARTO dataset data in two different queries as geopackage files:
https://username.carto.com/api/v2/sql?format=GPKG&q=SELECT * FROM table_name WHERE cartodb_id < 100000
https://username.carto.com/api/v2/sql?format=GPKG&q=SELECT * FROM table_name WHERE cartodb_id >= 100000