3

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?

benesch
  • 5,239
  • 1
  • 22
  • 36

3 Answers3

2

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;"
benesch
  • 5,239
  • 1
  • 22
  • 36
  • the postgres copy command load to cockroach didnt work for me. This approach is not mentioned in documentation as well, so I am assuming its not really supported. pgdump restores are supported though. – RK Kuppala May 31 '17 at 10:50
  • This doesn't work as of 2.1 because CockroachDB does not support `DELIMITER` in the syntax and thus cannot accept a CSV file with comma delimiters. – Mani Gandham Nov 20 '18 at 11:14
  • @RKKuppala @ManiGandham you must use `psql` (i.e., the Postgres CLI), not `cockroach sql`, in order to use the COPY commands. – benesch Nov 22 '18 at 03:08
2

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

DamnWidget
  • 1,397
  • 2
  • 10
  • 16
0

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.

Roland Crosby
  • 63
  • 5