CockroachDB数据的导入导出

本期文章将为大家介绍CockroachDB数据的导入导出。CockroachDB数据可以通过cockroach sql语句和IMPORT语句,以及PostgreSQL的psql语句导入;通过cockroach dump语句导出。

1 CockroachDB数据的导入

在CockroachDB中,可以导入两种类型的数据:一种是SQL文件,一种是CSV/TSV文件。

1.1 SQL文件导入

对于SQL文件的导入分为两种:

  • 普通SQL文件(包含批量的INSERT语句),通过cockroach sql语句进行导入
  • 在PostgreSQL中,使用pg_dump导出的SQL文件,通过psql语句进行数据导入

对于普通的SQL文件(包括CockroachDB中使用cockroach dump出的SQL文件),使用cockroach sql导入数据时,具体的cockroach sql命令如下(非安全模式下):

cockroach sql –insecure –user=maxroach –host=12.345.67.89 \

–port=26257 –database=critterdb < statements.sql 

在PostgreSQL中,使用pg_dump导出的SQL文件中包含的是COPY语句,而不是批量的INSERT语句,所以能更快的把导出数据导入到CockroachDB中。

对于pg_dump命令的使用如下:

  • 导出整个数据库:pg_dump [database] > [filename].sql
  • 导出具体表:pg_dump -t [table] [table’s schema] > [filename].sql

PostgreSQL使用pg_dump导出具体表数据的例子如下,图中除了使用pg_dump导出数据,还显示了导出的SQL文件具体内容:

https://mmbiz.qpic.cn/mmbiz_png/xoB0BH4icmV7miamL24aNUksZnIZZ2O1uDYG8JThhZib2GWQXwpibDFjXtDCjViazOjSlFoft5eMQg17hp0xKfickiajQ/640?wx_fmt=png

https://mmbiz.qpic.cn/mmbiz_png/xoB0BH4icmV7miamL24aNUksZnIZZ2O1uD0ib55Vj8nL2e5bkkIiafsteichrECHKQwMYjqyv2tAkCFTy33DhFSy3lw/640?wx_fmt=png

在使用pg_dump命令导出SQL文件后,还需要对这个文件进行修改才能导入到CockroachDB中。需要修改的地方如下:

  • 除了COPY语句与CREATE TABLE语句外,删除其他所有语句(例如环境变量设置语句、变更表主键约束语句等);
  • 在CREATE TABLE语句中添加主键约束,这是因为PostgreSQL一般是创建表之后添加主键索引,而CockroachDB则是必须在创建表时添加主键索引。

在修改pg_dump命令导出的SQL文件后,使用psql命令导入数据到CockroachDB中,命令如下:

psql -p [port] -h [node host] -d [database] -U [user] < [file name].sql

将上面例子中PostgreSQL导出的文件修改后,数据能成功导入到CockroachDB中。

表格数据(CSV)导入

1.2 表格数据(CSV)导入

在CockroachDB中,对于以表格格式导出的数据(如CSV),可以使用IMPORT语句来导入数据。

对于IMPORT语句的使用,必须注意下面六个约束条件:

  • 需要知道导入到CockroachDB数据的表结构
  • 要导入的表格数据(如CSV)不能放在CockroachDB集群的节点本地存储;需要放在云端存储,而且集群中的所有节点可以通过相同的地址访问;如果没有云端资源,可以使用文件服务器
  • 要导入数据的表在CockroachDB集群中不能存在,而且必须由IMPORT语句创建,如果在集群中有这个表必须提前删除
  •  IMPORT语句每次只能导入一张表
  • 在IMPORT中必须包含CREATE TABLE语句,可以将CREATE TABLE语句放在文件中获取,也可以直接在IMPORT语句中声明
  • 使用IMPORT导入一张表的数据到数据库时,在这个数据库中必须存在该导入表所依赖的表

下面图表介绍一下IMPORT语句的具体使用方法:

图中参数的解释如下:

参数 详细描述
table_name 要导入表的名字
create_table_file 一个只包含建表语句的纯文本文件的URL
table_elem_list 建表语句
file_to_import 要导入数据文件的URL
kv_option 控制数据导入方式的选项

同时,对于要导入文件的URL的格式如下:

[scheme]://[host]/[path]?[parameters]

导入文件所在的位置不同,URL是不一样的,URL具体的定义如下:

Location scheme host parameters
Amazon S3 s3 Bucket name AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY
Azure azure Container name AZURE_ACCOUNT_KEY, AZURE_ACCOUNT_NAME
Google Cloud gs Bucket name None
HTTP http Remote host N/A
NFS/Local nodelocal File system location N/A
S3-compatible services s3 Bucket name AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY, AWS_REGION, AWS_ENDPOINT

在IMPORT中,使用kv_option来控制IMPORT的导入行为,kv_option值是常见的key-value格式,具体的key-value定义如下:

Key Value Example Description
delimiter 在行中分割列的Unicode字符 delimiter = e’\t’ 定义一个列分隔符,使用这个分隔符分割一行数据
comment 一个字符 comment = ‘#’ 当一行数据以Value定义字符开头,则不导入此行数据
nullif 一个字符串 nullif = ‘ ‘ 当导入中的Value字符串替换成NULL

下面举几个例子来说明IMPORT的具体使用:

A 、把要导入数据的表结构定义到一个文件中,在这种情况下,导入数据到CockroachDB中:

B 、IMPORT语句中直接包含建表语句的情况下,导入数据到CockroachDB中:

C 、在导入数据中的每行数据都以字符’\t’来分割数据的情况下,导入数据到CockroachDB中

D 、使用kv_option的comment字段控制数据的导入,当某行数据以’#’开头时,不导入该行数据:

2 CockroachDB数据的导出

在CockroachDB中,使用cockroach dump命令可以导出集群中数据库的表结构、视图结构以及表数据。但是在使用cockroach dump命令之前,需要注意下面三个约束条件:

  • 表结构、视图结构及表中数据的导出数据,都是cockroach dump开始执行时刻的数据;任何在cockroach dump执行时刻后的变化都不会导出
  • cockroach dump导出的表结构与视图结构可以用于重新创建表,而且支持导出包含环形外键约束的表;但是,在重新导入这些导出数据时,必须手动修改导出文件的CREATE TABLE语句,去掉CREATE TABLE语句的外键定义,然后在导出文件的后面添加ALTER TABLE … ADD CONSTRAINT语句
  • 如果cockroach dump执行的时间大于ttlseconds(默认值是25小时),cockroach dump操作将会失败

cockroach dump具体的使用方法如下图:

上面cockroach dump命令使用的flags如下:

标志 具体意义
–as-of 导出具体某个历史时刻的表结构、表数据
–dump-mode 控制导出表或视图的结构,或者导出表数据,还是两者都导出
–echo-sql 显示隐藏的已执行的具体语句

下面举几个例子来说明cockroach dump的使用。

A 、同时导出一张表结构与数据:

B 、仅使用cockroach dump导出表结构:

C 、 仅导出表数据,不导出表结构:

D 、导出某个历史时刻的表数据:

希望通过本篇对CockroachDB导入导出数据的介绍,大家可以了解如何使用cockroach sql语句与PostgreSQL的psql语句导入数据,并清楚IMPORT导入表格数据时需要注意的约束条件及了解如何使用IMPORT语句;同时,帮助大家了解如何使用cockroach dump语句导出数据。