更新时间:2023-11-21 GMT+08:00

使用COPY命令导出导入数据

COPY是cqlsh中的逻辑导出导入命令,包括COPY TOCOPY FROM两个命令。

COPY TO支持将数据从表中导出到csv、parquet、orc格式的文件中。
  • 导出文件格式为csv时,需要每行都写入目标文件中的一行,其中的字段由定界符分隔。
  • 如果未指定列名,则导出所有字段。
  • 如果需要跳过部分列,需要指定列列表。

COPY FROM支持将数据从csv文件文件导入到现有表中。

  • 源文件中的每一行都作为一行导入。
  • 数据集中的所有行必须包含相同数量的字段,并且在PRIMARY KEY字段中必须具有值,该过程将验证PRIMARY KEY并更新现有记录。
  • 如果HEADER为False并且没有指定列名,则以确定的顺序导入字段。指定列名后,将按该顺序导入字段,缺少和空字段设置为null。
  • 源文件不能具有比目标表更多的字段,但是可以具有更少的字段。
  • 仅使用COPY FROM导入数据时,数据集必须少于200万行。

使用须知

  • 建议您尽量选择在业务低峰期导出导入数据,避免在该过程中对业务造成影响。
  • 需要获取最新的二进制包,具体地址请参见下载二进制包

COPY语法

  • COPY TO命令

    COPY table_name [( column_list )] TO 'file_name' [, 'file2_name', ...] | STDOUT [WITH option = 'value' [ADN ...]]

  • COPY FROM命令

    COPY table_name [( column_list )] FROM 'file_name' [, 'file2_name', ...] | STDIN [WITH option = 'value' [ADN ...]]

COPY支持一个或多个以逗号分隔的文件名或python glob表达式的列表。

上述COPY命令中出现的一些常见语法符号可以参见表1的约定。

表1 符号约定

常用符号

说明

大写字母

表示文字关键字。

小写字母

表示变量,需要替换为用户自定义的真实值。

斜体

可选的。方括号( [] )围绕可选命令参数。请勿输入方括号。

( )

组。括号(( ))表示要选择的组。不要键入括号。

|

或。竖线( | )分隔其他元素。键入任何一个元素。请勿输入竖线。

...

可重复的。省略号(...)表示您可以根据需要多次重复语法元素。

'Literal string'

单引号( ' )必须包含CQL语句中的文字字符串。使用单引号将大写字母保留下来。

{ key : value }

地图集合map。大括号( { } )包含地图集合或键值对。用冒号分隔键和值。

<datatype1,datatype2>

有序列表set,列出,映射或元组。尖括号(< >)将数据类型包含在集合,列表,地图或元组中。用逗号分隔数据类型。

cql_statement;

结束CQL语句。分号( ; )终止所有CQL语句。

[--]

使用两个连字符(--)将命令行选项与命令参数分开 。当参数可能误认为命令行选项时,此语法很有用。

' <schema> ... </schema> '

仅搜索CQL:单引号( ' )包围整个XML模式声明。

@xml_entity='xml_entity_type'

仅搜索CQL:标识实体和文字值以覆盖模式和solrConfig文件中的XML元素。

COPY参数使用建议

表2 参数介绍

命令类型

参数

描述

默认值

使用建议

TO/FROM

DELIMITER

用于分隔字段的单个字符。

英文逗号,

-

TO/FROM

QUOTE

包含字段值的单个字符。

"

-

TO/FROM

ESCAPE

转义使用QUOTE字符的单个字符。

\

-

TO/FROM

HEADER

布尔值(true | false),指示第一行上的列名称。

True将字段名称与导入(FROM)上的列名匹配,并将列名称插入到导出(TO)数据的第一行中。

FALSE

-

TO/FROM

NULL

查询结果为空的字段的填充值,可自由设置。

空字符串()

-

TO/FROM

DATETIMEFORMAT

用于读取或写入CSV时间数据的时间格式。

时间戳使用strftime格式。如果未设置,则默认值将设置为cqlshrc文件中的time_format值。

默认格式:%Y-%m-%d %H:%M:%S%z。

%Y-%m-%d %H:%M:%S%z

-

TO/FROM

MAXATTEMPTS

发生错误时的最大重试次数。

5

-

TO/FROM

REPORTFREQUENCY

状态显示的频率(以秒为单位)。

0.25

-

TO/FROM

DECIMALSEP

decimal(大数)值的分隔字符。

英文句号.

-

TO/FROM

THOUSANDSSEP

千位数组的分隔符。

None

-

TO/FROM

BOOLSTYLE

布尔值指示True和False。该值不区分大小写,例如:yes,no和YES,NO相同。

True,False

-

TO/FROM

NUMPROCESSES

工作进程数。

16

该参数的默认值是计算机上的内核数量减一个,当前未设置上限值。

您可以通过dstat,dstat -lvrn 10观察CPU空闲时间,如果存在CPU空闲时间,请使用默认的工作进程数。您可以增加进程数,增加的同时也需要观察实例的CPU使用情况,建议不超过60%,如果执行机CPU有空闲,而实例CPU已经超过建议值,进一步提升性能则需要扩容。

TO/FROM

CONFIGFILE

指定一个cqlshrc配置文件以设置WITH选项。

说明:

命令行选项始终会覆盖cqlshrc文件。

无,需自行制定

-

TO/FROM

RATEFILE

将输出统计信息打印到此文件。

无,需自行制定

导出数据时建议加上该参数以提升统计效率。

TO/FROM

ORIGIN

需要导入导出的数据库是否为开源Cassandra。

  • 如果是开源Cassandra,则为True。
  • 如果是GeminiDB Cassandra,则为False。

False

-

FROM

CHUNKSIZE

块大小传递给工作进程。

5000

该参数是从Feeder进程(从文件读取数据)发送到worker进程的行数。根据数据集的平均行大小,增加此参数的值可能是有利的。

FROM

INGESTRATE

每秒的近似导入速率。

100000

INGESTRATE是feeder进程发送数据给worker进程的每秒速率(以行数为单位),通常,除非每秒速率太高,需要限制速率,否则无需更改此值。

FROM

MAXBATCHSIZE

导入批处理的最大大小。

20

该值的设置可以尽可能大,但不要超过上限。

  • MAXBATCHSIZE*单行size < batch_size_fail_threshold_in_kb。
  • 批大小太大可能会导致警告并最终被拒绝。
  • 如下两个参数在cassandra.yaml中进行设置:

    batch_size_warn_threshold_in_kb(当前值为5)

    batch_size_fail_threshold_in_kb(当前值为50)

FROM

MINBATCHSIZE

导入批处理的最小大小。

2

对于每一个chunk,worker进程至少会按照最小batchsize进行batch写入,根据块的大小,群集中的节点数以及每个节点的VNODES数,此值可能需要调整:chunksize越大,此值需要调大。

FROM

MAXROWS

最大行数。使用“ -1”意味无最大限制。

-1

-

FROM

SKIPROWS

要跳过的行数。

0

-

FROM

SKIPCOLS

以逗号分隔的要跳过的列名称列表。

无,需自行制定

-

FROM

MAXPARSEERRORS

最大全局解析错误数。使用“ -1”意味无最大限制。

-1

-

FROM

MAXINSERTERRORS

全局最大插入错误数。使用“ -1”意味无最大限制。

-1

-

FROM

ERRFILE

用于存储所有未导入的行的文件。

如果未设置任何值,则信息存储在import_ ks _ table .err中,其中ks是键空间,而table是表名。

import_ ks _ table .err

-

FROM

TTL

生存时间以秒为单位。默认情况下,数据不会过期。

3600

-

TO

ENCODING

输出字符串类型。

UTF-8

-

TO

PAGESIZE

用于获取结果的页面大小。

1000

取值为整数,表示获取结果的页面大小,默认为1000。

页面大小越大,pagetimeout应该越长。单行数据量比较大时需要调小,单行数据量比较小时可以适当增大该值。该值的最佳效果还取决于执行机批量本地写的能力,如果批量本地写能力很强(如使用华为云obsfs),可适当增大。

TO

PAGETIMEOUT

页面超时以获取结果。

10

取值为整数,表示获取每个页面的超时(以秒为单位),默认为10秒。

  • 对于较大的页面大小或较大的分区,建议增加该参数值。
  • 如果发现超时,则应考虑增加该参数值。
  • 如果服务器超时,则会自动启动一个指数退避策略,因此您可能会注意到延迟,但这是为了防止服务器进一步过载。驱动程序还会生成超时,在这种情况下,由于驱动程序不知道服务器稍后是否会丢弃请求或返回结果,因此可能会丢失或重复数据的可能性很小。增大该参数值对于防止驱动程序生成超时非常有帮助。

TO

BEGINTOKEN

用于导出数据的最小token。

无,需自行制定

取值为字符串,表示导出数据时要考虑的最小令牌。

具有较小令牌的记录将不会导出。

默认为空,表示没有最小令牌。

TO

ENDTOKEN

用于导出数据的最大token。

无,需自行制定

取值为字符串,表示导出数据时要考虑的最大令牌。

具有较大令牌的记录将不会导出。

默认为空,表示没有最大令牌。

TO

MAXREQUESTS

每个工作者可以并行处理的最大请求数。

6

该参数取值为整数,表示每个工作进程可以处理的最大运行中请求数。

导出数据时总的并行度=工作进程数*该参数值。

默认值为6。每个请求将导出整个令牌范围的数据。

TO

MAXOUTPUTSIZE

输出文件的最大大小,以行数为单位。

设置后,超过该值时,输出文件将拆分为多个段。使用“ -1”意味无最大限制。

-1

该参数取值为整数,表示以行数为单位的输出文件的最大大小。超过此值,输出文件将被拆分为多个段。它的默认值为-1,表示无限制的最大值,因此是唯一的输出文件。可以和MAXFILESIZE同时使用。

TO

MAXFILESIZE

输出文件的最大大小,以KB为单位。

设置后,超过该值时,输出文件将拆分为多个段。

无,需自行制定

该参数取值为整数,表示以字节为单位的输出文件的最大大小,文件最终大小近似于该值。超过此值,输出文件将被拆分为多个段。它的默认值为-1,表示无限制的最大值,因此是唯一的输出文件。可以和MAXOUTPUTSIZE同时使用。

TO

dataformats

输出文件格式,当前如果设置值只能选json。

无,需自行制定

-

TO

DATATYPE

文件格式可选parquet或者orc。

无,需自行制定

-

TO

RESULTFILE

导出的详细结果文件。

无,需自行制定

导出数据时建议加上该参数以提升统计效率。

TO

wherecondition

导出时指定的导出条件。

无,需自行制定

-

如何使用COPY命令导出、导入数据

下面将以具体的示例介绍从预置数据到导出数据、导入数据的完整过程。

  1. 预置数据

    1. 创建keyspace。
      CREATE KEYSPACE cycling WITH replication = {'class': 'SimpleStrategy', 'replication_factor': 3};
    2. 创建table。
      CREATE TABLE cycling.cyclist_name (
         id UUID PRIMARY KEY,
         lastname text,
         firstname text
      );
    3. 插入数据。
      INSERT INTO cycling.cyclist_name (id, lastname, firstname) VALUES (5b6962dd-3f90-4c93-8f61-eabfa4a803e2, 'VOS','Marianne');
      INSERT INTO cycling.cyclist_name (id, lastname, firstname) VALUES (e7cd5752-bc0d-4157-a80f-7523add8dbcd, 'VAN DER BREGGEN','Anna');
      INSERT INTO cycling.cyclist_name (id, lastname, firstname) VALUES (e7ae5cf3-d358-4d99-b900-85902fda9bb0, 'FRAME','Alex');
      INSERT INTO cycling.cyclist_name (id, lastname, firstname) VALUES (220844bf-4860-49d6-9a4b-6b5d3a79cbfb, 'TIRALONGO','Paolo');
      INSERT INTO cycling.cyclist_name (id, lastname, firstname) VALUES (6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47, 'KRUIKSWIJK','Steven');
      INSERT INTO cycling.cyclist_name (id, lastname, firstname) VALUES (fb372533-eb95-4bb4-8685-6ef61e994caa, 'MATTHEWS', 'Michael');

  2. “cyclist_name”表中导出、导入数据。

    1. “cyclist_name”表中的“id”“lastname”列导出到CSV文件。
      COPY cycling.cyclist_name (id,lastname)  TO '../cyclist_lastname.csv' WITH HEADER = TRUE;
      图1 导出成功

      上述命令执行成功后,会在当前目录的上一层目录中创建“cyclist_lastname.csv”文件。如果该文件已经存在,则会将其覆盖。

    2. “cyclist_name”表中的“id”“first name”列导出到另一个CSV文件。
      COPY cycling.cyclist_name (id,firstname)  TO '../cyclist_firstname.csv' WITH HEADER = TRUE;
      图2 导出成功

      上述命令执行成功后,会在当前目录的上一层目录中创建“cyclist_firstname.csv”文件。如果该文件已经存在,则会将其覆盖。

    3. “cyclist_name”表中的数据删除。为了您的数据安全,暂不支持TRUNCATE命令。
      DELETE FROM cycling.cyclist_name WHERE id = 'fb372533-eb95-4bb4-8685-6ef61e994caa';
    4. 查看此时表中无数据。
      SELECT * FROM cycling.cyclist_name ;
      图3 查询数据
    5. 导入“cyclist_firstname.csv”文件。
      COPY cycling.cyclist_name (id,firstname) FROM '../cyclist_firstname.csv' WITH HEADER = TRUE;
      图4 导入成功
    6. 校验新导入的数据。
      SELECT * FROM cycling.cyclist_name;
      图5 导入成功
    7. 导入“cyclist_lastname.csv”文件。
      COPY cycling.cyclist_name (id,lastname) FROM '../cyclist_lastname.csv' WITH HEADER = TRUE;
      图6 导入数据
    8. 校验数据是否更新。
      SELECT * FROM cycling.cyclist_name;

      查询结果显示,

      图7 导入成功