# Importing and exporting data in CrateDB

**URL:** <https://community.cratedb.com/t/importing-and-exporting-data-in-cratedb/1189>\
**Category:** Tutorials\
**Tags:** sql, getting-started\
**Created:** [August 8, 2022, 12:38pm UTC](https://community.cratedb.com/t/importing-and-exporting-data-in-cratedb/1189 "2022-08-08T12:38:43Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![rafaelasantana](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/rafaelasantana/32/358_2.png) [@rafaelasantana](https://community.cratedb.com/u/rafaelasantana)\
**Post date:** [August 8, 2022, 12:38pm UTC](https://community.cratedb.com/t/importing-and-exporting-data-in-cratedb/1189/1 "2022-08-08T12:38:43Z")

</div>

This tutorial is also available in video at [CrateDB Video | Fundamentals: Importing and Exporting Data in CrateDB](https://crate.io/resources/videos/copy-from-statement)

This tutorial presents the basics of `COPY FROM` and `COPY TO` in CrateDB. For in-depth details of CrateDB `COPY FROM`, refer to our [Fundamentals of the COPY FROM statement](https://community.cratedb.com/t/copy-from-statement-things-you-need-to-know/1178) post.

Before anything else, I demonstrate how to link my local file system to the Docker container running CrateDB. This step is only necessary if you are running CrateDB with Docker. Then, I will show you how to import `JSON` and `CSV` data to CrateDB using the `COPY FROM` statement. Finally, I show you how to export data from CrateDB to a local file system using the `COPY TO` statement.

## Importing Data - JSON

Importing Data in CrateDB is done with the `COPY FROM` statement.

Reading the [COPY FROM documentation](https://crate.io/docs/crate/reference/en/latest/sql/statements/copy-from.html), I see that `COPY FROM` accepts both `JSON` and `CSV` inputs, and in this tutorial, I will show you how to do it with both, starting with `JSON`. You can refer to the documentation at the [crate.io website](http://crate.io/) to get more details on `COPY FROM`.

But before getting to the dataset, let me quickly explain how to link local files to CrateDB, in case you are running CrateDB with Docker - as I am.

#### Linking local files to Docker container

To put it simply: the Docker container is the running environment for CrateDB. This means that CrateDB does not have direct access to my local filesystem, but rather to the ones given in the container.

So when I want to import files from my local files to CrateDB, I add these files to CrateDB’s Docker container with the `--volumes` flag, which has the following structure:

```auto
--volume=/path/on/your/machine/to/file.json:/path/in/docker

```

I create a folder in my machine called `my_datasets`, where I will store the JSON file and link this folder to a `docker_datasets` folder in the Docker container.

To apply these changes to my CrateDB setup, I stop Docker with CrateDB and start it again with the following command:

```auto
docker run --publish=4200:4200 --publish=5433:5432 --volume=/Users/rafaelasantana/my_datasets:/docker_datasets --env CRATE_HEAP_SIZE=1g crate:latest

```

From now on, I can add my datasets to my local `my_datasets` folder, which will be accessible from CrateDB!

#### Dataset Overview

The first dataset I’m using today is a small collection of five quotes formatted in `JSON`, each having the `Quote`, `Author`, `Tags`, `Popularity`, and `Category` keys.

If you want to import your dataset, ensure it’s a single `JSON OBJECT` per line and no commas separate different objects. You can find more information on the formatting in our [COPY FROM documentation](https://crate.io/docs/crate/reference/en/4.8/sql/statements/copy-from.html).

 ![single_lined_json](https://us1.discourse-cdn.com/flex020/uploads/crate/original/1X/1a8aa73a3bd1aaa4aada689229919f880e8d9c70.png)

#### Creating a table to store data

I set up a table in CrateDB to store the `single_lined.json` data. I take the object’s keys as the table columns, so it looks like this:

```auto
CREATE TABLE dataset_quotes (
  "Quote" TEXT,
  "Author" TEXT, 
  "Tags" ARRAY(TEXT),
  "Popularity" DOUBLE,
  "Category" TEXT
);

```

And then, I run the following `COPY FROM` statement, which imports `single_lined.json` into the `dataset_quotes` table. It’s worth saying that the folder path is from Docker, for the `docker_datasets` folder, and not my local file path.

Also, adding `RETURN SUMMARY` to the end of my queries gives me detailed error reporting in case something would not work as expected.

```auto
COPY dataset_quotes 
FROM '/docker_datasets/single_lined.json' 
RETURN SUMMARY;

```

Now that I successfully imported `JSON` data into CrateDB let’s quickly check out how it works with `CSV`.

## Importing Data - CSV

I use a [dataset from Kaggle](https://www.kaggle.com/datasets/manann/quotes-500k) with around 500k quote records for the `CSV` data. It consists of three columns: the quote, the author of the quote, and the category tags for that quote.

I download the dataset, name it `quote_dataset.csv` and save it in that same `my_datasets` folder, which is mounted in Docker and accessible from CrateDB.

Then, I open the dataset to check the column names and data types.

 ![dataset_overview](https://us1.discourse-cdn.com/flex020/uploads/crate/original/1X/f3c5b3f75eca02d0f67150f972ab385540f52562.jpeg)

I see there are the `quote`, `author`, and `category` columns, all having `TEXT` data.

So I copy these headers and create a table in CrateDB with the same columns.

```auto
CREATE TABLE csv_quotes (
  quote TEXT,
  author TEXT,
  category TEXT
);

```

Now, all it is left is to run the `COPY FROM` statement to import the data into CrateDB.

```auto
COPY csv_quotes 
FROM '/docker_datasets/quote_dataset.csv' 
RETURN SUMMARY;

```

 ![copy_from_csv_quotes](https://us1.discourse-cdn.com/flex020/uploads/crate/original/1X/060544e7213476764a7953306c63c7c2d5b31148.png)

Here, CrateDB reports five errors in this immense import, probably due to formatting issues within the dataset.

Most importantly, I see that CrateDB ingested close to 500k rows in seconds!

I query the `csv_quotes` in the Table Browser and get a glimpse of the imported data.

 ![imported_data](https://us1.discourse-cdn.com/flex020/uploads/crate/original/1X/bcf5fafa03e1bff19f9fc02ed74a240c13f3833f.png)

## Exporting Data

Finally, I can easily export data from CrateDB using the `COPY TO` statement.

In the `COPY TO` documentation, I read:

_"The_ `COPY TO` _command exports the contents of a table to one or more files into a given directory with unique filenames. Each node with at least one table shard will export its contents onto its local disk._  
_The created files are JSON formatted and contain one table row per line and, due to the distributed nature of CrateDB, will remain on the same nodes where the shards are."_

I head to the Shards Tab in Admin UI and see that CrateDB distributed my tables into shards **0** , **1** , **2** , and **3**.

So, I expect CrateDB to export each of these shards’ data into an individual file.

 ![shards](https://us1.discourse-cdn.com/flex020/uploads/crate/original/1X/f0aecf864e05cf4a8de6c1c91e859a02b500d96a.png)

I copy the `csv_quotes` table content to the `docker_dataset` folder. Since the Docker folder is linked to my local `my_datasets` folder, CrateDB will successfully export the values to `my_datasets`.

```auto
COPY csv_quotes TO DIRECTORY '/docker_datasets/';

```

Now I see four files (one for each CrateDB shard) in my local `my_datasets` folder, containing the exported data in JSON format.

 ![four_files](https://us1.discourse-cdn.com/flex020/uploads/crate/original/1X/b2712779275ea95c4bc628256732b91eaa8e26b4.png)

For example, I open `csv_quoes_1_.json` and see that each row was formatted as a JSON object with `quote`, `author`, and `category` keys.

 ![exported_data](https://us1.discourse-cdn.com/flex020/uploads/crate/original/1X/9ddb86d43ce3bd216c9ed2e98812f1f29942f1a6.jpeg)

With that, we come to the end of this tutorial on importing and exporting data in CrateDB.

## Reference

- COPY FROM (documentation) [COPY FROM - CrateDB: Reference](https://crate.io/docs/crate/reference/en/4.8/sql/statements/copy-from.html)
- COPY TO (documentation) [https://crate.io/docs/crate/reference/en/4.8/sql/statements/copy-to.html](https://crate.io/docs/crate/reference/en/4.8/sql/statements/copy-to.html)
- Quotes Dataset [Quotes- 500k | Kaggle](https://www.kaggle.com/datasets/manann/quotes-500k)