clickhouse 数据导入导出操作

这篇具有很好参考价值的文章主要介绍了clickhouse 数据导入导出操作。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

在ClickHouse中处理CSV和TSV数据
ClickHouse支持从CSV导入和导出数据。由于 CSV 文件可以具有不同的格式细节,包括标题行、自定义分隔符和转义符号,因此 ClickHouse 提供了格式和设置来有效地解决每种情况。

从 CSV 文件导入数据
在导入数据之前,让我们创建一个具有相关结构的表:

CREATE TABLE sometable
(
    `path` String,
    `month` Date,
    `hits` UInt32
)
ENGINE = MergeTree
ORDER BY tuple(month, path)

要将数据从 CSV 文件导入到表中,我们可以将文件直接通过管道传输到 clickhouse-client:sometable

clickhouse-client -q "INSERT INTO sometable FORMAT CSV" < data_small.csv

请注意,我们使用 FORMAT CSV 让 ClickHouse 知道我们正在摄取 CSV 格式的数据。或者,我们可以使用 FROM INFILE 子句从本地文件加载数据:

INSERT INTO sometable
FROM INFILE 'data_small.csv'
FORMAT CSV

在这里,我们使用子句,以便ClickHouse理解文件格式。我们还可以使用 url() 函数直接从 URL 加载数据,或使用 s3() 函数从 S3 文件加载数据。FORMAT CSV

提示
我们可以跳过 和 / 的显式格式设置。 在这种情况下,ClickHouse将根据文件扩展名自动检测格式。file()INFILEOUTFILE

带标题的 CSV 文件
假设我们的 CSV 文件中有标题:

head data-small-headers.csv

"path","month","hits"
"Akiba_Hebrew_Academy","2017-08-01",241
"Aegithina_tiphia","2018-02-01",34

要从此文件导入数据,我们可以使用 CSVWithNames 格式:

clickhouse-client -q "INSERT INTO sometable FORMAT CSVWithNames" < data_small_headers.csv

在这种情况下,ClickHouse在从文件导入数据时会跳过第一行。

提示
从 23.1 版本开始,ClickHouse 会在使用 type 时自动检测 CSV 文件中的标头,因此无需使用 or .CSVCSVWithNamesCSVWithNamesAndTypes

具有自定义分隔符的 CSV 文件
如果 CSV 文件使用逗号分隔符以外的其他字符,我们可以使用 format_csv_delimiter 选项来设置相关符号:

SET format_csv_delimiter = ';'

现在,当我们从 CSV 文件导入时,符号将用作分隔符而不是逗号。;

跳过 CSV 文件中的行
有时,在从 CSV 文件导入数据时,我们可能会跳过一定数量的行。这可以使用input_format_csv_skip_first_lines选项来完成:

SET input_format_csv_skip_first_lines = 10

在本例中,我们将跳过 CSV 文件中的前十行:

SELECT count(*) FROM file('data-small.csv', CSV)

┌─count()─┐
│     990 │
└─────────┘

该文件有 1k 行,但 ClickHouse 只加载了 990 行,因为我们要求跳过前 10 行。

提示
使用该功能时,使用ClickHouse Cloud时,您需要在文件所在的计算机上运行命令。另一种选择是使用 clickhouse-local 在本地浏览文件。file()clickhouse client

处理 CSV 文件中的 NULL 值
Null 值的编码方式可能不同,具体取决于生成文件的应用程序。默认情况下,ClickHouse 在 CSV 中用作 Null 值。但是我们可以使用 format_csv_null_representation 选项来更改它。\N

假设我们有以下 CSV 文件:

> cat nulls.csv
Donald,90
Joe,Nothing
Nothing,70

如果我们从这个文件加载数据,ClickHouse 会被视为一个 String(这是正确的):Nothing

SELECT * FROM file('nulls.csv')

┌─c1──────┬─c2──────┐
│ Donald  │ 90      │
│ Joe     │ Nothing │
│ Nothing │ 70      │
└─────────┴─────────┘

如果我们希望 ClickHouse 将其视为 ,我们可以使用以下选项来定义它:NothingNULL

SET format_csv_null_representation = 'Nothing'

现在我们有了我们期望的位置:NULL

SELECT * FROM file('nulls.csv')

┌─c1─────┬─c2───┐
│ Donald │ 90   │
│ Joe    │ ᴺᵁᴸᴸ │
│ ᴺᵁᴸᴸ   │ 70   │
└────────┴──────┘

TSV(制表符分隔)文件
制表符分隔的数据格式被广泛用作数据交换格式。要将数据从 TSV 文件加载到 ClickHouse,请使用 TabSeparated 格式:

clickhouse-client -q "INSERT INTO sometable FORMAT TabSeparated" < data_small.tsv

还有一种 TabSeparatedWithNames 格式,允许处理具有标头的 TSV 文件。而且,与 CSV 一样,我们可以使用 input_format_tsv_skip_first_lines 选项跳过前 X 行。

原始 TSV
有时,TSV 文件保存时不会转义制表符和换行符。我们应该使用 TabSeparatedRaw 来处理此类文件。

导出到 CSV
前面示例中的任何格式也可用于导出数据。要将数据从表(或查询)导出为 CSV 格式,我们使用相同的子句:FORMAT

SELECT *
FROM sometable
LIMIT 5
FORMAT CSV

"Akiba_Hebrew_Academy","2017-08-01",241
"Aegithina_tiphia","2018-02-01",34
"1971-72_Utah_Stars_season","2016-10-01",1
"2015_UEFA_European_Under-21_Championship_qualification_Group_8","2015-12-01",73
"2016_Greater_Western_Sydney_Giants_season","2017-05-01",86

若要向 CSV 文件添加标头,请使用 CSVWithNames 格式:

SELECT *
FROM sometable
LIMIT 5
FORMAT CSVWithNames

"path","month","hits"
"Akiba_Hebrew_Academy","2017-08-01",241
"Aegithina_tiphia","2018-02-01",34
"1971-72_Utah_Stars_season","2016-10-01",1
"2015_UEFA_European_Under-21_Championship_qualification_Group_8","2015-12-01",73
"2016_Greater_Western_Sydney_Giants_season","2017-05-01",86

将导出的数据保存到 CSV 文件
要将导出的数据保存到文件中,我们可以使用 INTO...OUTFILE 子句:

SELECT *
FROM sometable
INTO OUTFILE 'out.csv'
FORMAT CSVWithNames

36838935 rows in set. Elapsed: 1.304 sec. Processed 36.84 million rows, 1.42 GB (28.24 million rows/s., 1.09 GB/s.)


请注意 ClickHouse ~1 秒将 36m 行保存到 CSV 文件所花费的时间。

使用自定义分隔符导出 CSV
如果我们想要逗号分隔符以外的其他字符,我们可以为此使用format_csv_delimiter设置选项:

SET format_csv_delimiter = '|'

现在ClickHouse将用作CSV格式的分隔符:|

SELECT *
FROM sometable
LIMIT 5
FORMAT CSV

"Akiba_Hebrew_Academy"|"2017-08-01"|241
"Aegithina_tiphia"|"2018-02-01"|34
"1971-72_Utah_Stars_season"|"2016-10-01"|1
"2015_UEFA_European_Under-21_Championship_qualification_Group_8"|"2015-12-01"|73
"2016_Greater_Western_Sydney_Giants_season"|"2017-05-01"|86

导出适用于 Windows 的 CSV
如果我们希望 CSV 文件在 Windows 环境中正常工作,我们应该考虑启用output_format_csv_crlf_end_of_line选项。这将用作换行符,而不是:\r\n\n

SET output_format_csv_crlf_end_of_line = 1;

CSV 文件的架构推理
在许多情况下,我们可能会使用未知的 CSV 文件,因此我们必须探索用于列的类型。默认情况下,Clickhouse 将尝试根据对给定 CSV 文件的分析来猜测数据格式。这称为“架构推理”。可以使用与 file() 函数配对的语句来探索检测到的数据类型:DESCRIBE

DESCRIBE file('data-small.csv', CSV)

┌─name─┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Nullable(String) │              │                    │         │                  │                │
│ c2   │ Nullable(Date)   │              │                    │         │                  │                │
│ c3   │ Nullable(Int64)  │              │                    │         │                  │                │
└──────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘


在这里,ClickHouse可以有效地猜测CSV文件的列类型。如果我们不想让 ClickHouse 猜测,我们可以使用以下选项禁用它:

SET input_format_csv_use_best_effort_in_schema_inference = 0

在这种情况下,所有列类型都将被视为 a。String

导出和导入具有显式列类型的 CSV
ClickHouse还允许在使用CSVWithNamesAndTypes(以及其他*WithNames格式系列)导出数据时显式设置列类型:

SELECT *
FROM sometable
LIMIT 5
FORMAT CSVWithNamesAndTypes

"path","month","hits"
"String","Date","UInt32"
"Akiba_Hebrew_Academy","2017-08-01",241
"Aegithina_tiphia","2018-02-01",34
"1971-72_Utah_Stars_season","2016-10-01",1
"2015_UEFA_European_Under-21_Championship_qualification_Group_8","2015-12-01",73
"2016_Greater_Western_Sydney_Giants_season","2017-05-01",86

此格式将包括两个标题行 - 一个包含列名称,另一个包含列类型。这将允许ClickHouse(和其他应用程序)在从此类文件加载数据时识别列类型:

DESCRIBE file('data_csv_types.csv', CSVWithNamesAndTypes)

┌─name──┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ path  │ String │              │                    │         │                  │                │
│ month │ Date   │              │                    │         │                  │                │
│ hits  │ UInt32 │              │                    │         │                  │                │
└───────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

现在,ClickHouse根据(第二个)标题行而不是猜测来识别列类型。

自定义分隔符、分隔符和转义规则
在复杂的情况下,文本数据可以以高度自定义的方式进行格式化,但仍具有结构。ClickHouse 针对这种情况有一种特殊的 CustomSeparated 格式,它允许设置自定义转义规则、分隔符、行分隔符和开始/结束符号。

假设文件中有以下数据:

row('Akiba_Hebrew_Academy';'2017-08-01';241),row('Aegithina_tiphia';'2018-02-01';34),...

我们可以看到,各个行被包裹起来,行被分隔,单个值用 分隔。在这种情况下,我们可以使用以下设置从此文件中读取数据:row(),;

SET format_custom_row_before_delimiter = 'row(';
SET format_custom_row_after_delimiter = ')';
SET format_custom_field_delimiter = ';';
SET format_custom_row_between_delimiter = ',';
SET format_custom_escaping_rule = 'Quoted';

现在我们可以从自定义格式的文件中加载数据:

SELECT *
FROM file('data_small_custom.txt', CustomSeparated)
LIMIT 3

┌─c1────────────────────────┬─────────c2─┬──c3─┐
│ Akiba_Hebrew_Academy      │ 2017-08-01 │ 241 │
│ Aegithina_tiphia          │ 2018-02-01 │  34 │
│ 1971-72_Utah_Stars_season │ 2016-10-01 │   1 │
└───────────────────────────┴────────────┴─────┘

我们还可以使用 CustomSeparatedWithNames 来正确导出和导入标头。探索正则表达式和模板格式,以处理更复杂的情况。

使用大型 CSV 文件
CSV 文件可能很大,ClickHouse 可以有效地处理任何大小的文件。大文件通常是压缩的,ClickHouse涵盖了这一点,在处理之前不需要解压缩。我们可以在插入期间使用子句:COMPRESSION

INSERT INTO sometable
FROM INFILE 'data_csv.csv.gz'
COMPRESSION 'gzip' FORMAT CSV

如果省略了子句,ClickHouse仍将尝试根据其扩展名来猜测文件压缩。可以使用相同的方法将文件直接导出为压缩格式:COMPRESSION

SELECT *
FROM for_csv
INTO OUTFILE 'data_csv.csv.gz'
COMPRESSION 'gzip' FORMAT CSV

这将创建一个压缩文件。data_csv.csv.gz

其他格式
ClickHouse引入了对文本和二进制多种格式的支持,以覆盖各种场景和平台。在以下文章中探索更多格式和使用它们的方法:

CSV 和 TSV 格式
木条镶花之地板
JSON 格式
正则表达式和模板
本机和二进制格式
SQL 格式
还要检查 clickhouse-local - 一个便携式的全功能工具,无需 Clickhouse 服务器即可处理本地/远程文件。

编辑本页文章来源地址https://www.toymoban.com/news/detail-809703.html

到了这里,关于clickhouse 数据导入导出操作的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 探索ClickHouse——同时支持导入导出功能的文件格式

    在 《探索ClickHouse——安装和测试》中,我们使用clickhouse直接从文件中读取数据。clickhouse支持多种格式文件的导入导出,本节我们对此进行分类介绍。 原始的JSON格式只支持导入,不支持导入。同时支持导入和导出的是其他几种类型: JSONEachRow JSONStringsEachRow JSONCompactEachRow

    2024年02月07日
    浏览(47)
  • 关于java操作excel导入导出三种方式

    在平时的业务系统开发中,少不了需要用到导出、导入excel功能,今天我们就一起来总结一下,如果你正为此需求感到困惑,那么阅读完本文,你一定会有所收获! 大概在很久很久以前,微软的电子表格软件 Excel 以操作简单、存储数据直观方便,还支持打印报表,在诞生之初

    2024年02月05日
    浏览(45)
  • k8s中clickhouse导出导入遇到的问题记录

    近期,在做k8s相关的项目时,涉及到了ck数据库的导入导出的问题。 一开始就想到采用导出数据到csv文件,再从csv文件导入的做法。所以,导出和导入的语句分别为: 导出: kubectl exec -it  ck_pod_name -n namespace --clickhouse-client --host=127.0.0.1 --port=9000 --user=username --password=password -

    2024年02月16日
    浏览(48)
  • DataGrip的MySQL数据导出和导入操作指南

    将开发环境的数据,复制一份到本地,进行本地连接开发工作,避免组内其他开发人员的干扰。假若你的电脑上只安装了DataGrip和MySQL环境,本篇指南就是你所需要的。 将开发环境的数据和结构导出一份到本地。 1.1 选择你要导出的数据架构,右键,选择用mysqldump导出,如下图

    2024年04月08日
    浏览(46)
  • java 实现数据导入导出

    官方文档(完整功能展示):地址 依赖 导出 1.随便一个实体类(默认不管加不加@ExcelProperty的注解的所有字段都会参与读写,若某字段不想参与,可在字段上添加@ExcelIgnore。若只想加@ExcelProperty才参与,则类上加@ExcelIgnoreUnannotated)更多注解使用 2.编一个方法用于返回要导出

    2024年02月13日
    浏览(53)
  • SQL Server 基础操作(五)导入和导出数据表

    1.选择需要导数据的数据库右击----任务----导入数据 2.选择数据源,数据源代表数据表从哪里导入到当前的数据库中。填写数据源服务器名称(本地导入:.,1433;远程导入:IP,1433) 3.选择导入的目标数据库,选择导入到那个数据库中 4.选择复制一个或多个表或视图的数据 5.选择从

    2024年02月07日
    浏览(49)
  • Java导出Excel模板,导出数据到指定模板,通过模板导入数据(一)

    本文章主要是介绍阿里巴巴的easyexcel的使用 1. 首先需要我们导入easyexcel的依赖包 2. 前期工作准备 编写相关导出模板和导入模板。在项目的resources下创建文件夹,命名为excel 导出模板(此处仅做示例,字段根据自己项目来):  导入模板(导入时需要哪些字段根据自己项目业

    2024年02月03日
    浏览(46)
  • Java POI 百万规模数据的导入和导出

    使用POI基于事件模式解析案例提供的Excel文件 **用户模式:**加载并读取Excel时,是通过一次性的将所有数据加载到内存中再去解析每个单元格内容。当Excel数据量较大时,由于不同的运行环境可能会造成内存不足甚至OOM异常。 例如读取我们刚刚导出的百万数据: 会直接报内存

    2024年02月08日
    浏览(46)
  • Javascript 模块导入导出(import export)

    笔者开始学习 Javascript 的时候,对模块不太懂,不知道怎么导入模块,导出模块,就胡乱一通试 比如 import xx from \\\'test.js\\\' 不起作用,就加个括号 import {xx} from \\\'test.js\\\' 反正总是靠蒙,总有一种写法是对的,其实还是没有理解,还是不懂 尤其是在当初写 www.helloworld.net 网站的时候

    2024年02月05日
    浏览(37)
  • 大数据:hive数据库的操作语法,数据表,内部表,external外部表,数据导入导出load,insert

    2022找工作是学历、能力和运气的超强结合体,遇到寒冬,大厂不招人,可能很多算法学生都得去找开发,测开 测开的话,你就得学数据库,sql,oracle,尤其sql要学,当然,像很多金融企业、安全机构啥的,他们必须要用oracle数据库 这oracle比sql安全,强大多了,所以你需要学

    2024年02月08日
    浏览(76)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包