CockroachDB export for importing into MySQL and Postgres

[robert-s-lee]

Exalate commented:

A bit more content for https://www.cockroachlabs.com/docs/v2.1/export.html#main-content

Not all databases honor properly formatted CSV file CockroachDB produces. Most common issues are delimiter that is embedded inside a quote string. For example, it is common to have , and tab characters as a part of string. MySQL and Postgres also expect NULL to be represented as \N. For the delimiter, a common alternate character is HEX x01. Below examples exports from CockroachDB using the HEX x01 as delimiter and \N as NULL:

EXPORT INTO CSV 'nodelocal:////dirname' WITH delimiter = e'\x01', nullas = '\N' from table tablename;

• above uses Hex x01 (also known as control-A) as the delimiter.
• \N is the default NULL for MySQL and Postgres
• multiple CSV files are generated from the export. Examples below show n1.0.csv.
  n1 = node 1 0 = file name node 1. there can be more than 1 file. each file has up to 100,000 rows
• The files are located at cockroach-data/extern/dirname
• Replace tablename with table name
• Replace database_name with database name
• Assume tablename has been pre-created before running the copy and load data commands

Import into Postgres:

echo "copy tablename from stdin with DELIMITER e'\x01';" | cat - n1.0.csv | psql

Import into MySQL:

load data local infile 'n1.0.csv' into table tablename FIELDS terminated by X'01' | mysql --local-infile database_name

Jira Issue: DOC-183

[jseldess]

Thanks, @robert-s-lee. I'm not clear on what exactly you'd like us to add, though. Can you summarize?

[robert-s-lee]

cockroachDB import and export commands work with Common Format and MIME Type for Comma-Separated Values (CSV) Files. MySQL and Postgres do not. In order for CockroachDB export CSV to be useable by MySQL and Postgres, special delimiter and specific NULL convention have to be followed. The examples provide the instructions.

[jseldess]

@robert-s-lee, just revisiting this. Why would we want to document how to migrate data from cockroach to mysql/postrgres?

[MattFellows]

@jseldess I guess it might just be a nice thing to do? :) Alternatively people might want to interchange technologies for many reasons. If Cockroach becomes a black hole (importing data, but not exporting in a useful format) people might refrain from using it.

[jseldess]

We have closed this issue because it is more than 3 years old. If this issue is still relevant, please add a comment and reopen the issue. Thank you for your contribution to CockroachDB docs!

[Inigo4562]

This was actually very useful. I took some time to find so I think it could really help including it in the main docs

[exalate-issue-sync[bot]]

Kathryn Hancox (kathancox) commented: Ryan Kuo This is a really old issue. I think it would fall under your area now? Do you know if this can actually be closed?

[exalate-issue-sync[bot]]

Ryan Kuo (taroface) commented: Kathryn Hancox I’ll assign it to myself! Thanks.

Placing it in Migrations but I’m not sure how much this applies to the area.

[drnachio]

We need to be able to do it or we will need to move to other DB

[awoods187]

@drnachio can you say more about what functionality/goal you need to solve?