Docs: CockroachDB export for importing into MySQL and Postgres

[robert-s-lee]

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

[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.