ClickHouse通过文件导入数据(字段中带有引号、回车)

小霸王 发表于 2021/09/29 19:57:12 2021/09/29
【摘要】 将其他关系型数据库中的数据导出成CSV文件,再导入Clickhouse,由于原数据库中的数据许多字段包含双引号以及换行符,为了区分数据内容中的双引号和换行符,需要从原数据库中导出CSV文件时,指定分隔符为特殊字符'ESC',在导入到Clickhouse时,我们需要指定format_csv_delimiter为该字符。原始的CSV文件中一条数据的格式如图所示:1、首先我们需要先根据所给的数据字...

将其他关系型数据库中的数据导出成CSV文件,再导入Clickhouse,由于原数据库中的数据许多字段包含双引号以及换行符,为了区分数据内容中的双引号和换行符,需要从原数据库中导出CSV文件时,指定分隔符为特殊字符'ESC',在导入到Clickhouse时,我们需要指定format_csv_delimiter为该字符。

原始的CSV文件中一条数据的格式如图所示:

1.png

1、首先我们需要先根据所给的数据字段及类型创建本地表及分布式表:

ReplicatedMergeTree表:

CREATE TABLE audit.test_audit_log_local ON CLUSTER default_cluster
(
    datid Int32,
    dbname String,
    schemaname String,
    start_time DateTime64,
    end_time DateTime64,
    query String,
    query_plan String,
    uuid String
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/audit.test_audit_log_local/{shard}','{replica}')
ORDER BY (datid);
分布式表:
2、通过脚本导入CSV文件到Clickhouse,由于无法在Shell命令窗口打出'ESC'特殊字符,所以需要将命令写在脚本中,如图所示:
CREATE TABLE audit.test_audit_log_all ON CLUSTER default_cluster AS audit.test_audit_log_local
ENGINE = Distributed(default_cluster, audit, test_audit_log_local, rand());

注意:数据中的start_time、end_time字段格式为2021-04-04 00:05:33.652789+08,加了时区,我们需要添加参数--date_time_input_format=best_effort才能识别该日期字段。(https://clickhouse.tech/docs/en/operations/settings/settings/#settings-date_time_input_format

3、执行脚本导入数据:

3.png

由于第三个字段schemaname的值为"$user",public,其中带有双引号,所以导致整行数据解析失败,如果去掉该双引号,该字段解析没问题,但是后面的字段query以及query_plan的值中带有回车(换行符),也导致整行解析失败:

4.png



那么如何导入该文件数据呢?我们不希望改变原有数据,即原有的双引号和回车均保留。

1、原数据库导出CSV文件可以指定分隔符以及包围符,我们还是指定分隔符为特殊字符'ESC',包围符也使用特殊字符'BS'(区分开字段中的双引号),得到的CSV文件中的一条数据如图所示:

5.png


可以看到,每个字段用特殊字符'BS'包围,字段之间使用特殊字符'ESC'分隔。

2、Clickhouse从CSV文件导入数据时,可以识别双引号或单引号包围的字段,但不能识别特殊字符作为包围符,所以这里我们需要做如下处理:
(1)将文本中原有的双引号转义,Clickhouse中对双引号的转义使用的是",并不是\,即在原有的双引号前再添加一个双引号,使用sed命令将"替换为""。
(2)此时字段中的双引号已转义,我们可以将文本中原有的包围符'BS'替换为Clickhouse能够识别的包围符双引号,使用sed命令将'BS'替换为"

具体脚本如图所示:

6.png

3、执行脚本导入CSV文件数据:

7.png

4、导入数据成功,查看导入的数据,原数据中的双引号以及回车(换行符)均保留:

8.png

原始的CSV数据文件见附件:data.txt
插入数据脚本见附件:insert_audit.sh
【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区),文章链接,文章作者等基本信息,否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件至:cloudbbs@huaweicloud.com进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容。
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。