CODEWITHSUNDEEP
cockroachdb
benesch
benesch

Reputation: 5269

How do I import a CSV file into CockroachDB?

I have a CSV file I exported from a MySQL database and I want to move it over to CockroachDB. How do I read in the data from the command line?

Upvotes: 3

Views: 3182

Answers (3)

benesch
benesch

Reputation: 5269

CockroachDB v2.0 or later

CSV import is natively supported in CockroachDB 2.0 and later using the IMPORT statement. You can specify a custom delimiter using the WITH delimiter option, as shown in these examples and this migration guide.

For example:

IMPORT TABLE customers
    CREATE USING 'azure://acme-co/customer-create-table.sql'
    CSV DATA ('azure://acme-co/customer-import-data.csv')

In v2.1, you can also import directly from a MySQL or PostgreSQL file using IMPORT MYSQLDUMP or IMPORT PGDUMP. See this section of the docs, as well as the MySQL/Postgres migration guides, for more details.

CockroachDB v1.1 or earlier

The easiest way to get a CSV file into CockroachDB is to use a CSV to SQL converter that turns each row in your CSV into a SQL INSERT statement. You can find any number of websites that will do this for you, or you can build a script to do the processing in something like awk, Perl or Python. Once you've got your SQL INSERT statements in a file, you can execute them like so:

cockroach sql --database=[your db] < [file].sql

Alternatively, if you have Postgres installed, you can connect to CockroachDB through psql and use its COPY command to import the CSV.

psql postgres://USER@HOST:PORT/DATABASE -c "COPY [table] FROM '[path to].csv' DELIMITER ',' CSV;"

If you're running CockroachDB on the default port on your local machine, here's the same command with the defaults filled in:

psql postgres://root@localhost:26257/DATABASE -c "COPY [table] FROM '[path to].csv' DELIMITER ',' CSV;"

Upvotes: 2

Roland Crosby
Roland Crosby

Reputation: 63

The above instructions are now outdated; CSV import is natively supported in CockroachDB 2.0 and later using the IMPORT statement. You can specify a custom delimiter using the WITH delimiter option, as shown in these examples and this migration guide.

If you're using CockroachDB 2.1 and migrating from MySQL or Postgres, you can also import directly from a mysqldump or pg_dump SQL file using IMPORT MYSQLDUMP or IMPORT PGDUMP. See this section of the docs, as well as the MySQL/Postgres migration guides, for more details.

Upvotes: 0

DamnWidget
DamnWidget

Reputation: 1457

Cockroach doesn't support the DELIMITER statement it also does not accepts the CSV format explicit so the right command in order to use COPY FROM would be:

psql postgres://user@host:port/DATABASE -c "COPY [table] FROM '[path to].csv';"

The delimiter character must be the special character \t so if your CSV file is using , as delimiter, you can convert it easily using sed:

sed -i 's/,/\t/g' [your file].csv

Why they hardcoded the special character \t as delimiter instead of , is beyond my knowledge.

There is already an open issue about this in https://github.com/cockroachdb/cockroach/issues/16407

Upvotes: 2

Related Questions