输入和输出数据格式
ClickHouse 可以接受和返回各种格式的数据。支持输入的格式可以用于解析提供给 INSERT
的数据,从文件支持的表(例如 File、URL 或 HDFS)执行 SELECT
,或读取字典。支持输出的格式可以用于排列 SELECT
的结果,以及将数据 INSERT
到文件支持的表中。所有格式名称均不区分大小写。
支持的格式如下:
您可以使用 ClickHouse 设置控制一些格式处理参数。有关更多信息,请阅读 设置 部分。
TabSeparated
在 TabSeparated 格式中,数据按行写入。每行包含以制表符分隔的值。每个值后面都跟着一个制表符,除了行中的最后一个值,它后面跟着一个换行符。严格假设在任何地方都使用 Unix 换行符。最后一行也必须在末尾包含一个换行符。值以文本格式写入,不带引号,并对特殊字符进行转义。
此格式也可在名称 TSV
下使用。
TabSeparated
格式便于使用自定义程序和脚本处理数据。它在 HTTP 接口和命令行客户端的批处理模式中默认使用。此格式还允许在不同的 DBMS 之间传输数据。例如,您可以从 MySQL 获取转储并将其上传到 ClickHouse,反之亦然。
TabSeparated
格式支持输出总值(当使用 WITH TOTALS 时)和极值(当“extremes”设置为 1 时)。在这些情况下,总值和极值在主数据之后输出。主结果、总值和极值之间用空行分隔。示例
SELECT EventDate, count() AS c FROM test.hits GROUP BY EventDate WITH TOTALS ORDER BY EventDate FORMAT TabSeparated
2014-03-17 1406958
2014-03-18 1383658
2014-03-19 1405797
2014-03-20 1353623
2014-03-21 1245779
2014-03-22 1031592
2014-03-23 1046491
1970-01-01 8873898
2014-03-17 1031592
2014-03-23 1406958
数据格式化
整数以十进制形式写入。数字可以在开头包含额外的“+”字符(解析时忽略,格式化时不记录)。非负数不能包含负号。在读取时,允许将空字符串解析为零,或者(对于带符号类型)仅由减号组成的字符串解析为零。不适合相应数据类型的数字可能会被解析为不同的数字,而不会出现错误消息。
浮点数以十进制形式写入。点用作小数点分隔符。支持指数项,以及“inf”、“+inf”、“-inf”和“nan”。浮点数项可以在开头或结尾包含一个小数点。在格式化过程中,可能会丢失浮点数的精度。在解析过程中,不一定严格要求读取最接近的机器可表示数字。
日期以 YYYY-MM-DD 格式写入,并以相同的格式解析,但使用任何字符作为分隔符。带时间的日期以 YYYY-MM-DD hh:mm:ss
格式写入,并以相同的格式解析,但使用任何字符作为分隔符。所有这些都在客户端或服务器启动时的系统时区发生(取决于哪个格式化数据)。对于带时间的日期,未指定夏令时。因此,如果转储在夏令时期间有时间,则转储不会明确匹配数据,并且解析将选择两个时间之一。在读取操作期间,不正确的日期和带时间的日期可以自然溢出或作为空日期和时间进行解析,而不会出现错误消息。
作为例外,如果带时间的日期由正好 10 个十进制数字组成,则也支持以 Unix 时间戳格式解析。结果与时区无关。格式 YYYY-MM-DD hh:mm:ss
和 NNNNNNNNNN
会自动区分。
字符串使用反斜杠转义的特殊字符输出。以下转义序列用于输出:\b
、\f
、\r
、\n
、\t
、\0
、\'
、\\
。解析还支持序列 \a
、\v
和 \xHH
(十六进制转义序列)以及任何 \c
序列,其中 c
是任何字符(这些序列转换为 c
)。因此,读取数据支持换行符可以写成 \n
或 \
,或作为换行符的格式。例如,字符串 Hello world
在单词之间使用换行符而不是空格,可以按以下任何一种变体进行解析
Hello\nworld
Hello\
world
支持第二种变体,因为 MySQL 在写入制表符分隔的转储时使用它。
在 TabSeparated 格式中传递数据时,您需要转义的字符集最小:制表符、换行符 (LF) 和反斜杠。
仅转义一小部分符号。您很容易偶然遇到终端将在输出中破坏的字符串值。
数组写成方括号中以逗号分隔的值列表。数组中的数字项按正常方式格式化。Date
和 DateTime
类型用单引号括起来。字符串用单引号括起来,并遵循与上面相同的转义规则。
NULL 根据设置 format_tsv_null_representation(默认值为 \N
)进行格式化。
在输入数据中,ENUM 值可以表示为名称或 ID。首先,我们尝试将输入值与 ENUM 名称匹配。如果失败并且输入值是数字,则我们尝试将此数字与 ENUM ID 匹配。如果输入数据仅包含 ENUM ID,建议启用设置 input_format_tsv_enum_as_number 以优化 ENUM 解析。
每个元素 嵌套 结构表示为数组。
例如
CREATE TABLE nestedt
(
`id` UInt8,
`aux` Nested(
a UInt8,
b String
)
)
ENGINE = TinyLog
INSERT INTO nestedt Values ( 1, [1], ['a'])
SELECT * FROM nestedt FORMAT TSV
1 [1] ['a']
TabSeparated 格式设置
- format_tsv_null_representation - TSV 格式中自定义的 NULL 表示形式。默认值 -
\N
。 - input_format_tsv_empty_as_default - 将 TSV 输入中的空字段视为默认值。默认值 -
false
。对于复杂的默认表达式,input_format_defaults_for_omitted_fields 也必须启用。 - input_format_tsv_enum_as_number - 将 TSV 格式中插入的枚举值视为枚举索引。默认值 -
false
。 - input_format_tsv_use_best_effort_in_schema_inference - 使用一些调整和启发式方法在 TSV 格式中推断模式。如果禁用,所有字段都将推断为字符串。默认值 -
true
。 - output_format_tsv_crlf_end_of_line - 如果设置为 true,则 TSV 输出格式中的行尾将为
\r\n
而不是\n
。默认值 -false
。 - input_format_tsv_crlf_end_of_line - 如果设置为 true,则 TSV 输入格式中的行尾将为
\r\n
而不是\n
。默认值 -false
。 - input_format_tsv_skip_first_lines - 跳过数据开头指定的行数。默认值 -
0
。 - input_format_tsv_detect_header - 自动检测 TSV 格式数据中的列名和类型。默认值 -
true
。 - input_format_tsv_skip_trailing_empty_lines - 跳过数据末尾的空行。默认值 -
false
。 - input_format_tsv_allow_variable_number_of_columns - 允许 TSV 格式数据中列数不固定,忽略多余的列,并在缺少的列上使用默认值。默认值 -
false
。
TabSeparatedRaw
与 TabSeparated
格式不同之处在于,行写入时不进行转义。使用此格式解析时,每个字段中不允许出现制表符或换行符。
此格式也可用以下名称:TSVRaw
、Raw
。
TabSeparatedWithNames
与 TabSeparated
格式不同之处在于,列名写入在第一行。
解析过程中,期望第一行包含列名。您可以使用列名来确定其位置并检查其正确性。
如果设置 input_format_with_names_use_header 为 1,则输入数据中的列将根据其名称映射到表的列,如果设置 input_format_skip_unknown_fields 为 1,则会跳过名称未知的列。否则,将跳过第一行。
此格式也可用以下名称:TSVWithNames
。
TabSeparatedWithNamesAndTypes
与 TabSeparated
格式不同之处在于,列名写入在第一行,而列类型写入在第二行。
如果设置 input_format_with_names_use_header 为 1,则输入数据中的列将根据其名称映射到表的列,如果设置 input_format_skip_unknown_fields 为 1,则会跳过名称未知的列。否则,将跳过第一行。如果设置 input_format_with_types_use_header 为 1,则会将输入数据中的类型与表中对应列的类型进行比较。否则,将跳过第二行。
此格式也可用以下名称:TSVWithNamesAndTypes
。
TabSeparatedRawWithNames
与 TabSeparatedWithNames
格式不同之处在于,行写入时不进行转义。使用此格式解析时,每个字段中不允许出现制表符或换行符。
此格式也可用以下名称:TSVRawWithNames
、RawWithNames
。
TabSeparatedRawWithNamesAndTypes
与 TabSeparatedWithNamesAndTypes
格式不同之处在于,行写入时不进行转义。使用此格式解析时,每个字段中不允许出现制表符或换行符。
此格式也可用以下名称:TSVRawWithNamesAndNames
、RawWithNamesAndNames
。
Template
此格式允许使用自定义格式字符串,其中包含带有指定转义规则的值的占位符。
它使用设置 format_template_resultset
、format_template_row
(format_template_row_format
)、format_template_rows_between_delimiter
和其他格式的一些设置(例如,当使用 JSON
转义时使用 output_format_json_quote_64bit_integers
,请参阅下文)
设置 format_template_row
指定包含以下语法行的格式字符串的文件路径
delimiter_1${column_1:serializeAs_1}delimiter_2${column_2:serializeAs_2} ... delimiter_N
,
其中 delimiter_i
是值之间的分隔符($
符号可以转义为 $$
),column_i
是要选择或插入的值的列的名称或索引(如果为空,则跳过该列),serializeAs_i
是列值的转义规则。支持以下转义规则
CSV
、JSON
、XML
(类似于同名格式)Escaped
(类似于TSV
)Quoted
(类似于Values
)Raw
(不转义,类似于TSVRaw
)None
(无转义规则,请参阅下文)
如果省略转义规则,则将使用 None
。XML
仅适用于输出。
因此,对于以下格式字符串
`Search phrase: ${SearchPhrase:Quoted}, count: ${c:Escaped}, ad price: $$${price:JSON};`
将打印(用于选择)或期望(用于插入)SearchPhrase
、c
和 price
列的值,这些值分别以 Quoted
、Escaped
和 JSON
进行转义,它们位于 Search phrase:
、, count:
、, ad price: $
和 ;
分隔符之间。例如
Search phrase: 'bathroom interior design', count: 2166, ad price: $3;
在难以或无法将模板格式的格式输出配置部署到集群中所有节点上的目录,或者格式很简单的情况下,可以使用 format_template_row_format
直接在查询中设置模板字符串,而不是设置包含它的文件路径。
format_template_rows_between_delimiter
设置指定行之间的分隔符,该分隔符在除最后一行之外的每一行之后打印(或期望)(默认值为 \n
)
设置 format_template_resultset
指定包含结果集格式字符串的文件路径。设置 format_template_resultset_format
可用于直接在查询本身中设置结果集的模板字符串。结果集的格式字符串与行的格式字符串具有相同的语法,并允许指定前缀、后缀以及打印一些其他信息的方式。它包含以下占位符而不是列名
data
是以format_template_row
格式表示的数据行,由format_template_rows_between_delimiter
分隔。此占位符必须是格式字符串中的第一个占位符。totals
是以format_template_row
格式表示的包含总值的行(当使用 WITH TOTALS 时)min
是以format_template_row
格式表示的包含最小值的行(当极值设置为 1 时)max
是以format_template_row
格式表示的包含最大值的行(当极值设置为 1 时)rows
是输出行的总数rows_before_limit
是在没有 LIMIT 的情况下将具有的最小行数。仅在查询包含 LIMIT 时输出。如果查询包含 GROUP BY,则 rows_before_limit_at_least 是在没有 LIMIT 的情况下将具有的确切行数。time
是请求执行时间(以秒为单位)rows_read
是已读取的行数bytes_read
是已读取的字节数(未压缩)
占位符 data
、totals
、min
和 max
必须没有指定的转义规则(或必须显式指定 None
)。其余占位符可以指定任何转义规则。如果 format_template_resultset
设置为空字符串,则 ${data}
将用作默认值。对于插入查询,如果前缀或后缀(请参阅示例),则格式允许跳过某些列或字段。
选择示例
SELECT SearchPhrase, count() AS c FROM test.hits GROUP BY SearchPhrase ORDER BY c DESC LIMIT 5 FORMAT Template SETTINGS
format_template_resultset = '/some/path/resultset.format', format_template_row = '/some/path/row.format', format_template_rows_between_delimiter = '\n '
/some/path/resultset.format
:
<!DOCTYPE HTML>
<html> <head> <title>Search phrases</title> </head>
<body>
<table border="1"> <caption>Search phrases</caption>
<tr> <th>Search phrase</th> <th>Count</th> </tr>
${data}
</table>
<table border="1"> <caption>Max</caption>
${max}
</table>
<b>Processed ${rows_read:XML} rows in ${time:XML} sec</b>
</body>
</html>
/some/path/row.format
:
<tr> <td>${0:XML}</td> <td>${1:XML}</td> </tr>
结果
<!DOCTYPE HTML>
<html> <head> <title>Search phrases</title> </head>
<body>
<table border="1"> <caption>Search phrases</caption>
<tr> <th>Search phrase</th> <th>Count</th> </tr>
<tr> <td></td> <td>8267016</td> </tr>
<tr> <td>bathroom interior design</td> <td>2166</td> </tr>
<tr> <td>clickhouse</td> <td>1655</td> </tr>
<tr> <td>spring 2014 fashion</td> <td>1549</td> </tr>
<tr> <td>freeform photos</td> <td>1480</td> </tr>
</table>
<table border="1"> <caption>Max</caption>
<tr> <td></td> <td>8873898</td> </tr>
</table>
<b>Processed 3095973 rows in 0.1569913 sec</b>
</body>
</html>
插入示例
Some header
Page views: 5, User id: 4324182021466249494, Useless field: hello, Duration: 146, Sign: -1
Page views: 6, User id: 4324182021466249494, Useless field: world, Duration: 185, Sign: 1
Total rows: 2
INSERT INTO UserActivity SETTINGS
format_template_resultset = '/some/path/resultset.format', format_template_row = '/some/path/row.format'
FORMAT Template
/some/path/resultset.format
:
Some header\n${data}\nTotal rows: ${:CSV}\n
/some/path/row.format
:
Page views: ${PageViews:CSV}, User id: ${UserID:CSV}, Useless field: ${:CSV}, Duration: ${Duration:CSV}, Sign: ${Sign:CSV}
PageViews
、UserID
、Duration
和 Sign
在占位符内是表中列的名称。行中 Useless field
之后的值以及后缀中 \nTotal rows:
之后的值将被忽略。输入数据中的所有分隔符必须严格等于指定格式字符串中的分隔符。
TemplateIgnoreSpaces
此格式仅适用于输入。类似于 Template
,但在输入流中跳过分隔符和值之间的空格字符。但是,如果格式字符串包含空格字符,则将在输入流中期望这些字符。还允许指定空占位符(${}
或 ${:None}
)以将某些分隔符拆分为单独的部分以忽略它们之间的空格。此类占位符仅用于跳过空格字符。如果所有行中列的值顺序相同,则可以使用此格式读取 JSON
。例如,以下请求可用于从格式 JSON 的输出示例中插入数据
INSERT INTO table_name SETTINGS
format_template_resultset = '/some/path/resultset.format', format_template_row = '/some/path/row.format', format_template_rows_between_delimiter = ','
FORMAT TemplateIgnoreSpaces
/some/path/resultset.format
:
{${}"meta"${}:${:JSON},${}"data"${}:${}[${data}]${},${}"totals"${}:${:JSON},${}"extremes"${}:${:JSON},${}"rows"${}:${:JSON},${}"rows_before_limit_at_least"${}:${:JSON}${}}
/some/path/row.format
:
{${}"SearchPhrase"${}:${}${phrase:JSON}${},${}"c"${}:${}${cnt:JSON}${}}
TSKV
类似于 TabSeparated,但以 name=value 格式输出值。名称的转义方式与 TabSeparated 格式相同,并且 = 符号也进行了转义。
SearchPhrase= count()=8267016
SearchPhrase=bathroom interior design count()=2166
SearchPhrase=clickhouse count()=1655
SearchPhrase=2014 spring fashion count()=1549
SearchPhrase=freeform photos count()=1480
SearchPhrase=angelina jolie count()=1245
SearchPhrase=omsk count()=1112
SearchPhrase=photos of dog breeds count()=1091
SearchPhrase=curtain designs count()=1064
SearchPhrase=baku count()=1000
NULL 格式化为 \N
。
SELECT * FROM t_null FORMAT TSKV
x=1 y=\N
当存在大量的小列时,此格式效率低下,通常没有理由使用它。然而,就效率而言,它并不比 JSONEachRow 差。
此格式同时支持数据输出和解析。对于解析,支持不同列的值的任何顺序。可以省略某些值 - 它们被视为等于其默认值。在这种情况下,零和空行用作默认值。表中可以指定的复杂值不支持作为默认值。
解析允许存在不带等号或值的附加字段 tskv
。此字段将被忽略。
导入期间,如果设置 input_format_skip_unknown_fields 为 1,则会跳过名称未知的列。
CSV
逗号分隔值格式(RFC)。
格式化时,行用双引号括起来。字符串内部的双引号输出为连续的两个双引号。没有其他字符转义规则。日期和日期时间用双引号括起来。数字不带引号输出。值由分隔符字符分隔,默认值为 ,
。分隔符字符在设置 format_csv_delimiter 中定义。行使用 Unix 换行符 (LF) 分隔。数组在 CSV 中序列化如下:首先,数组按照 TabSeparated 格式序列化为字符串,然后将生成的字符串输出到 CSV 中的双引号内。CSV 格式中的元组被序列化为单独的列(也就是说,它们在元组中的嵌套关系会丢失)。
$ clickhouse-client --format_csv_delimiter="|" --query="INSERT INTO test.csv FORMAT CSV" < data.csv
*默认情况下,分隔符为 ,
。有关更多信息,请参阅 format_csv_delimiter 设置。
在解析时,所有值都可以使用或不使用引号进行解析。双引号和单引号都支持。行也可以不带引号排列。在这种情况下,它们将解析到分隔符字符或换行符 (CR 或 LF) 处。违反 RFC 规定,在不带引号解析行时,会忽略开头和结尾的空格和制表符。对于换行符,支持 Unix (LF)、Windows (CR LF) 和 Mac OS Classic (CR LF) 类型。
NULL
的格式根据设置 format_csv_null_representation(默认值为 \N
)进行格式化。
在输入数据中,ENUM 值可以表示为名称或 ID。首先,我们尝试将输入值与 ENUM 名称匹配。如果失败且输入值是数字,则尝试将此数字与 ENUM ID 匹配。如果输入数据仅包含 ENUM ID,建议启用设置 input_format_csv_enum_as_number 以优化 ENUM 解析。
CSV 格式支持以与 TabSeparated
相同的方式输出总计和极值。
CSV 格式设置
- format_csv_delimiter - CSV 数据中被视为分隔符的字符。默认值 -
,
。 - format_csv_allow_single_quotes - 允许使用单引号括起来的字符串。默认值 -
true
。 - format_csv_allow_double_quotes - 允许使用双引号括起来的字符串。默认值 -
true
。 - format_csv_null_representation - CSV 格式中自定义的 NULL 表示。默认值 -
\N
。 - input_format_csv_empty_as_default - 将 CSV 输入中的空字段视为默认值。默认值 -
true
。对于复杂的默认表达式,还需要启用 input_format_defaults_for_omitted_fields。 - input_format_csv_enum_as_number - 将 CSV 格式中插入的枚举值视为枚举索引。默认值 -
false
。 - input_format_csv_use_best_effort_in_schema_inference - 使用一些调整和启发式方法来推断 CSV 格式的模式。如果禁用,所有字段都将被推断为字符串。默认值 -
true
。 - input_format_csv_arrays_as_nested_csv - 从 CSV 读取数组时,预期其元素已序列化为嵌套 CSV,然后放入字符串中。默认值 -
false
。 - output_format_csv_crlf_end_of_line - 如果设置为 true,则 CSV 输出格式中的行尾将为
\r\n
而不是\n
。默认值 -false
。 - input_format_csv_skip_first_lines - 跳过数据开头的指定行数。默认值 -
0
。 - input_format_csv_detect_header - 自动检测 CSV 格式中带有名称和类型的标题。默认值 -
true
。 - input_format_csv_skip_trailing_empty_lines - 跳过数据末尾的尾随空行。默认值 -
false
。 - input_format_csv_trim_whitespaces - 修剪未加引号的 CSV 字符串中的空格和制表符。默认值 -
true
。 - input_format_csv_allow_whitespace_or_tab_as_delimiter - 允许在 CSV 字符串中使用空格或制表符作为字段分隔符。默认值 -
false
。 - input_format_csv_allow_variable_number_of_columns - 允许 CSV 格式中列数可变,忽略多余的列并在缺少的列上使用默认值。默认值 -
false
。 - input_format_csv_use_default_on_bad_values - 允许在 CSV 字段反序列化在错误值上失败时为列设置默认值。默认值 -
false
。 - input_format_csv_try_infer_numbers_from_strings - 在模式推断期间尝试从字符串字段推断数字。默认值 -
false
。
CSVWithNames
还会打印包含列名称的标题行,类似于 TabSeparatedWithNames。
如果设置 input_format_with_names_use_header 设置为 1,则输入数据中的列将按其名称映射到表中的列,如果设置 input_format_skip_unknown_fields 设置为 1,则将跳过名称未知的列。否则,将跳过第一行。
CSVWithNamesAndTypes
还会打印包含列名称和类型的两行标题,类似于 TabSeparatedWithNamesAndTypes。
如果设置 input_format_with_names_use_header 设置为 1,则输入数据中的列将按其名称映射到表中的列,如果设置 input_format_skip_unknown_fields 设置为 1,则将跳过名称未知的列。否则,将跳过第一行。如果设置 input_format_with_types_use_header 设置为 1,则将输入数据中的类型与表中相应列的类型进行比较。否则,将跳过第二行。
CustomSeparated
类似于 Template,但它打印或读取所有列的名称和类型,并使用来自 format_custom_escaping_rule 设置的转义规则以及来自 format_custom_field_delimiter、format_custom_row_before_delimiter、format_custom_row_after_delimiter、format_custom_row_between_delimiter、format_custom_result_before_delimiter 和 format_custom_result_after_delimiter 设置的分隔符,而不是来自格式字符串。
其他设置
- input_format_custom_detect_header - 如果有,则启用标题名称和类型的自动检测。默认值 -
true
。 - input_format_custom_skip_trailing_empty_lines - 跳过文件末尾的尾随空行。默认值 -
false
。 - input_format_custom_allow_variable_number_of_columns - 允许 CustomSeparated 格式中列数可变,忽略多余的列并在缺少的列上使用默认值。默认值 -
false
。
还有一个 CustomSeparatedIgnoreSpaces
格式,它类似于 TemplateIgnoreSpaces。
CustomSeparatedWithNames
还会打印包含列名称的标题行,类似于 TabSeparatedWithNames。
如果设置 input_format_with_names_use_header 设置为 1,则输入数据中的列将按其名称映射到表中的列,如果设置 input_format_skip_unknown_fields 设置为 1,则将跳过名称未知的列。否则,将跳过第一行。
CustomSeparatedWithNamesAndTypes
还会打印包含列名称和类型的两行标题,类似于 TabSeparatedWithNamesAndTypes。
如果设置 input_format_with_names_use_header 设置为 1,则输入数据中的列将按其名称映射到表中的列,如果设置 input_format_skip_unknown_fields 设置为 1,则将跳过名称未知的列。否则,将跳过第一行。如果设置 input_format_with_types_use_header 设置为 1,则将输入数据中的类型与表中相应列的类型进行比较。否则,将跳过第二行。
SQLInsert
将数据输出为一系列 INSERT INTO table (columns...) VALUES (...), (...) ...;
语句。
示例
SELECT number AS x, number + 1 AS y, 'Hello' AS z FROM numbers(10) FORMAT SQLInsert SETTINGS output_format_sql_insert_max_batch_size = 2
INSERT INTO table (x, y, z) VALUES (0, 1, 'Hello'), (1, 2, 'Hello');
INSERT INTO table (x, y, z) VALUES (2, 3, 'Hello'), (3, 4, 'Hello');
INSERT INTO table (x, y, z) VALUES (4, 5, 'Hello'), (5, 6, 'Hello');
INSERT INTO table (x, y, z) VALUES (6, 7, 'Hello'), (7, 8, 'Hello');
INSERT INTO table (x, y, z) VALUES (8, 9, 'Hello'), (9, 10, 'Hello');
要读取此格式输出的数据,可以使用 MySQLDump 输入格式。
SQLInsert 格式设置
- output_format_sql_insert_max_batch_size - 一个 INSERT 语句中的最大行数。默认值 -
65505
。 - output_format_sql_insert_table_name - 输出 INSERT 查询中表的名称。默认值 -
'table'
。 - output_format_sql_insert_include_column_names - 在 INSERT 查询中包含列名称。默认值 -
true
。 - output_format_sql_insert_use_replace - 使用 REPLACE 语句代替 INSERT。默认值 -
false
。 - output_format_sql_insert_quote_names - 使用 "`" 字符引用列名称。默认值 -
true
。
JSON
以 JSON 格式输出数据。除了数据表之外,它还输出列名称和类型,以及一些其他信息:输出行的总数以及如果没有 LIMIT 则可以输出的行数。示例
SELECT SearchPhrase, count() AS c FROM test.hits GROUP BY SearchPhrase WITH TOTALS ORDER BY c DESC LIMIT 5 FORMAT JSON
{
"meta":
[
{
"name": "num",
"type": "Int32"
},
{
"name": "str",
"type": "String"
},
{
"name": "arr",
"type": "Array(UInt8)"
}
],
"data":
[
{
"num": 42,
"str": "hello",
"arr": [0,1]
},
{
"num": 43,
"str": "hello",
"arr": [0,1,2]
},
{
"num": 44,
"str": "hello",
"arr": [0,1,2,3]
}
],
"rows": 3,
"rows_before_limit_at_least": 3,
"statistics":
{
"elapsed": 0.001137687,
"rows_read": 3,
"bytes_read": 24
}
}
JSON 与 JavaScript 兼容。为了确保这一点,一些字符会被额外转义:斜杠 /
会转义为 \/
;一些浏览器会破坏的备用换行符 U+2028
和 U+2029
会转义为 \uXXXX
。ASCII 控制字符将被转义:退格键、换页符、换行符、回车符和水平制表符将分别替换为 \b
、\f
、\n
、\r
、\t
,以及 00-1F 范围内其余的字节,使用 \uXXXX
序列。无效的 UTF-8 序列将更改为替换字符 �,以便输出文本将由有效的 UTF-8 序列组成。为了与 JavaScript 兼容,Int64 和 UInt64 整数默认情况下用双引号括起来。要删除引号,可以将配置参数 output_format_json_quote_64bit_integers 设置为 0。
rows
– 输出行的总数。
rows_before_limit_at_least
如果没有 LIMIT,则行数的最小值。仅当查询包含 LIMIT 时输出。如果查询包含 GROUP BY,则 rows_before_limit_at_least 是如果没有 LIMIT 则行数的确切值。
totals
– 总计值(使用 WITH TOTALS 时)。
extremes
– 极值(当极值设置为 1 时)。
ClickHouse 支持 NULL,在 JSON 输出中显示为 null
。要在输出中启用 +nan
、-nan
、+inf
、-inf
值,请将 output_format_json_quote_denormals 设置为 1。
另请参阅
对于 JSON 输入格式,如果设置 input_format_json_validate_types_from_metadata 设置为 1,则输入数据中元数据中的类型将与表中相应列的类型进行比较。
JSONStrings
仅在数据字段以字符串形式输出而不是以类型化 JSON 值输出方面与 JSON 不同。
示例
{
"meta":
[
{
"name": "num",
"type": "Int32"
},
{
"name": "str",
"type": "String"
},
{
"name": "arr",
"type": "Array(UInt8)"
}
],
"data":
[
{
"num": "42",
"str": "hello",
"arr": "[0,1]"
},
{
"num": "43",
"str": "hello",
"arr": "[0,1,2]"
},
{
"num": "44",
"str": "hello",
"arr": "[0,1,2,3]"
}
],
"rows": 3,
"rows_before_limit_at_least": 3,
"statistics":
{
"elapsed": 0.001403233,
"rows_read": 3,
"bytes_read": 24
}
}
JSONColumns
JSONColumns* 格式的输出提供了 ClickHouse 字段名称,然后是该字段表中每一行的内容;从视觉上看,数据向左旋转了 90 度。
在此格式中,所有数据都表示为单个 JSON 对象。请注意,JSONColumns 输出格式将所有数据缓冲到内存中以将其输出为单个块,这可能导致高内存消耗。
示例
{
"num": [42, 43, 44],
"str": ["hello", "hello", "hello"],
"arr": [[0,1], [0,1,2], [0,1,2,3]]
}
在导入期间,如果设置 input_format_skip_unknown_fields 为 1,则将跳过名称未知的列。块中不存在的列将填充默认值(您可以在此处使用 input_format_defaults_for_omitted_fields 设置)。
JSONColumnsWithMetadata
与 JSONColumns 格式的不同之处在于它还包含一些元数据和统计信息(类似于 JSON 格式)。输出格式将所有数据缓冲到内存中,然后将其输出为单个块,因此,这可能导致高内存消耗。
示例
{
"meta":
[
{
"name": "num",
"type": "Int32"
},
{
"name": "str",
"type": "String"
},
{
"name": "arr",
"type": "Array(UInt8)"
}
],
"data":
{
"num": [42, 43, 44],
"str": ["hello", "hello", "hello"],
"arr": [[0,1], [0,1,2], [0,1,2,3]]
},
"rows": 3,
"rows_before_limit_at_least": 3,
"statistics":
{
"elapsed": 0.000272376,
"rows_read": 3,
"bytes_read": 24
}
}
对于 JSONColumnsWithMetadata 输入格式,如果设置 input_format_json_validate_types_from_metadata 为 1,则将输入数据中元数据中的类型与表中相应列的类型进行比较。
JSONAsString
在此格式中,单个 JSON 对象被解释为单个值。如果输入有多个 JSON 对象(逗号分隔),则将其解释为单独的行。如果输入数据用方括号括起来,则将其解释为 JSON 数组。
此格式只能解析具有单个类型为 String 的字段的表。其余列必须设置为 DEFAULT 或 MATERIALIZED,或省略。收集整个 JSON 对象到字符串后,您可以使用 JSON 函数 来处理它。
示例
查询
DROP TABLE IF EXISTS json_as_string;
CREATE TABLE json_as_string (json String) ENGINE = Memory;
INSERT INTO json_as_string (json) FORMAT JSONAsString {"foo":{"bar":{"x":"y"},"baz":1}},{},{"any json stucture":1}
SELECT * FROM json_as_string;
结果
┌─json──────────────────────────────┐
│ {"foo":{"bar":{"x":"y"},"baz":1}} │
│ {} │
│ {"any json stucture":1} │
└───────────────────────────────────┘
JSON 对象数组
查询
CREATE TABLE json_square_brackets (field String) ENGINE = Memory;
INSERT INTO json_square_brackets FORMAT JSONAsString [{"id": 1, "name": "name1"}, {"id": 2, "name": "name2"}];
SELECT * FROM json_square_brackets;
结果
┌─field──────────────────────┐
│ {"id": 1, "name": "name1"} │
│ {"id": 2, "name": "name2"} │
└────────────────────────────┘
JSONAsObject
在此格式中,单个 JSON 对象被解释为单个 JSON 值。如果输入有多个 JSON 对象(逗号分隔),则将其解释为单独的行。如果输入数据用方括号括起来,则将其解释为 JSON 数组。
此格式只能解析具有单个类型为 JSON 的字段的表。其余列必须设置为 DEFAULT 或 MATERIALIZED。
示例
查询
SET allow_experimental_json_type = 1;
CREATE TABLE json_as_object (json JSON) ENGINE = Memory;
INSERT INTO json_as_object (json) FORMAT JSONAsObject {"foo":{"bar":{"x":"y"},"baz":1}},{},{"any json stucture":1}
SELECT * FROM json_as_object FORMAT JSONEachRow;
结果
{"json":{"foo":{"bar":{"x":"y"},"baz":"1"}}}
{"json":{}}
{"json":{"any json stucture":"1"}}
JSON 对象数组
查询
SET allow_experimental_json_type = 1;
CREATE TABLE json_square_brackets (field JSON) ENGINE = Memory;
INSERT INTO json_square_brackets FORMAT JSONAsObject [{"id": 1, "name": "name1"}, {"id": 2, "name": "name2"}];
SELECT * FROM json_square_brackets FORMAT JSONEachRow;
结果
{"field":{"id":"1","name":"name1"}}
{"field":{"id":"2","name":"name2"}}
具有默认值的列
SET allow_experimental_json_type = 1;
CREATE TABLE json_as_object (json JSON, time DateTime MATERIALIZED now()) ENGINE = Memory;
INSERT INTO json_as_object (json) FORMAT JSONAsObject {"foo":{"bar":{"x":"y"},"baz":1}};
INSERT INTO json_as_object (json) FORMAT JSONAsObject {};
INSERT INTO json_as_object (json) FORMAT JSONAsObject {"any json stucture":1}
SELECT time, json FROM json_as_object FORMAT JSONEachRow
{"time":"2024-09-16 12:18:10","json":{}}
{"time":"2024-09-16 12:18:13","json":{"any json stucture":"1"}}
{"time":"2024-09-16 12:18:08","json":{"foo":{"bar":{"x":"y"},"baz":"1"}}}
JSONCompact
与 JSON 的区别仅在于数据行输出在数组中,而不是在对象中。
示例
{
"meta":
[
{
"name": "num",
"type": "Int32"
},
{
"name": "str",
"type": "String"
},
{
"name": "arr",
"type": "Array(UInt8)"
}
],
"data":
[
[42, "hello", [0,1]],
[43, "hello", [0,1,2]],
[44, "hello", [0,1,2,3]]
],
"rows": 3,
"rows_before_limit_at_least": 3,
"statistics":
{
"elapsed": 0.001222069,
"rows_read": 3,
"bytes_read": 24
}
}
JSONCompactStrings
与 JSONStrings 的区别仅在于数据行输出在数组中,而不是在对象中。
示例:f
{
"meta":
[
{
"name": "num",
"type": "Int32"
},
{
"name": "str",
"type": "String"
},
{
"name": "arr",
"type": "Array(UInt8)"
}
],
"data":
[
["42", "hello", "[0,1]"],
["43", "hello", "[0,1,2]"],
["44", "hello", "[0,1,2,3]"]
],
"rows": 3,
"rows_before_limit_at_least": 3,
"statistics":
{
"elapsed": 0.001572097,
"rows_read": 3,
"bytes_read": 24
}
}
JSONCompactColumns
在此格式中,所有数据都表示为单个 JSON 数组。请注意,JSONCompactColumns 输出格式将所有数据缓冲到内存中以将其输出为单个块,这可能导致高内存消耗。
示例
[
[42, 43, 44],
["hello", "hello", "hello"],
[[0,1], [0,1,2], [0,1,2,3]]
]
块中不存在的列将填充默认值(您可以在此处使用 input_format_defaults_for_omitted_fields 设置)。
JSONEachRow
在此格式中,ClickHouse 将每一行输出为一个分隔的、换行符分隔的 JSON 对象。
示例
{"num":42,"str":"hello","arr":[0,1]}
{"num":43,"str":"hello","arr":[0,1,2]}
{"num":44,"str":"hello","arr":[0,1,2,3]}
在导入数据时,如果设置 input_format_skip_unknown_fields 为 1,则将跳过名称未知的列。
PrettyJSONEachRow
与 JSONEachRow 的区别仅在于 JSON 使用换行符和 4 个空格缩进进行格式化。仅适用于输出。
示例
{
"num": "42",
"str": "hello",
"arr": [
"0",
"1"
],
"tuple": {
"num": 42,
"str": "world"
}
}
{
"num": "43",
"str": "hello",
"arr": [
"0",
"1",
"2"
],
"tuple": {
"num": 43,
"str": "world"
}
}
JSONStringsEachRow
与 JSONEachRow 的区别仅在于数据字段输出为字符串,而不是类型化的 JSON 值。
示例
{"num":"42","str":"hello","arr":"[0,1]"}
{"num":"43","str":"hello","arr":"[0,1,2]"}
{"num":"44","str":"hello","arr":"[0,1,2,3]"}
JSONCompactEachRow
与 JSONEachRow 的区别仅在于数据行输出在数组中,而不是在对象中。
示例
[42, "hello", [0,1]]
[43, "hello", [0,1,2]]
[44, "hello", [0,1,2,3]]
JSONCompactStringsEachRow
与 JSONCompactEachRow 的区别仅在于数据字段输出为字符串,而不是类型化的 JSON 值。
示例
["42", "hello", "[0,1]"]
["43", "hello", "[0,1,2]"]
["44", "hello", "[0,1,2,3]"]
JSONEachRowWithProgress
JSONStringsEachRowWithProgress
与 JSONEachRow
/JSONStringsEachRow
的区别在于 ClickHouse 还将生成进度信息作为 JSON 值。
{"row":{"num":42,"str":"hello","arr":[0,1]}}
{"row":{"num":43,"str":"hello","arr":[0,1,2]}}
{"row":{"num":44,"str":"hello","arr":[0,1,2,3]}}
{"progress":{"read_rows":"3","read_bytes":"24","written_rows":"0","written_bytes":"0","total_rows_to_read":"3"}}
JSONCompactEachRowWithNames
与 JSONCompactEachRow
格式的不同之处在于它还打印带有列名的标题行,类似于 TabSeparatedWithNames。
如果设置 input_format_with_names_use_header 设置为 1,则输入数据中的列将按其名称映射到表中的列,如果设置 input_format_skip_unknown_fields 设置为 1,则将跳过名称未知的列。否则,将跳过第一行。
JSONCompactEachRowWithNamesAndTypes
与 JSONCompactEachRow
格式的不同之处在于它还打印两行标题,其中包含列名和类型,类似于 TabSeparatedWithNamesAndTypes。
如果设置 input_format_with_names_use_header 设置为 1,则输入数据中的列将按其名称映射到表中的列,如果设置 input_format_skip_unknown_fields 设置为 1,则将跳过名称未知的列。否则,将跳过第一行。如果设置 input_format_with_types_use_header 设置为 1,则将输入数据中的类型与表中相应列的类型进行比较。否则,将跳过第二行。
JSONCompactStringsEachRowWithNames
与 JSONCompactStringsEachRow
的不同之处在于它还打印带有列名的标题行,类似于 TabSeparatedWithNames。
如果设置 input_format_with_names_use_header 设置为 1,则输入数据中的列将按其名称映射到表中的列,如果设置 input_format_skip_unknown_fields 设置为 1,则将跳过名称未知的列。否则,将跳过第一行。
JSONCompactStringsEachRowWithNamesAndTypes
与 JSONCompactStringsEachRow
的不同之处在于它还打印两行标题,其中包含列名和类型,类似于 TabSeparatedWithNamesAndTypes。
如果设置 input_format_with_names_use_header 设置为 1,则输入数据中的列将按其名称映射到表中的列,如果设置 input_format_skip_unknown_fields 设置为 1,则将跳过名称未知的列。否则,将跳过第一行。如果设置 input_format_with_types_use_header 设置为 1,则将输入数据中的类型与表中相应列的类型进行比较。否则,将跳过第二行。
["num", "str", "arr"]
["Int32", "String", "Array(UInt8)"]
[42, "hello", [0,1]]
[43, "hello", [0,1,2]]
[44, "hello", [0,1,2,3]]
JSONObjectEachRow
在此格式中,所有数据都表示为单个 JSON 对象,每一行都表示为该对象的单独字段,类似于 JSONEachRow 格式。
示例
{
"row_1": {"num": 42, "str": "hello", "arr": [0,1]},
"row_2": {"num": 43, "str": "hello", "arr": [0,1,2]},
"row_3": {"num": 44, "str": "hello", "arr": [0,1,2,3]}
}
要使用对象名称作为列值,您可以使用特殊设置 format_json_object_each_row_column_for_object_name。此设置的值设置为列的名称,该列用作结果对象中行的 JSON 键。示例
用于输出
假设我们有一个名为 test
的表,其中包含两列
┌─object_name─┬─number─┐
│ first_obj │ 1 │
│ second_obj │ 2 │
│ third_obj │ 3 │
└─────────────┴────────┘
让我们以 JSONObjectEachRow
格式输出它,并使用 format_json_object_each_row_column_for_object_name
设置
select * from test settings format_json_object_each_row_column_for_object_name='object_name'
输出
{
"first_obj": {"number": 1},
"second_obj": {"number": 2},
"third_obj": {"number": 3}
}
用于输入
假设我们将上一个示例的输出存储在一个名为 data.json
的文件中
select * from file('data.json', JSONObjectEachRow, 'object_name String, number UInt64') settings format_json_object_each_row_column_for_object_name='object_name'
┌─object_name─┬─number─┐
│ first_obj │ 1 │
│ second_obj │ 2 │
│ third_obj │ 3 │
└─────────────┴────────┘
它也适用于模式推断
desc file('data.json', JSONObjectEachRow) settings format_json_object_each_row_column_for_object_name='object_name'
┌─name────────┬─type────────────┐
│ object_name │ String │
│ number │ Nullable(Int64) │
└─────────────┴─────────────────┘
插入数据
INSERT INTO UserActivity FORMAT JSONEachRow {"PageViews":5, "UserID":"4324182021466249494", "Duration":146,"Sign":-1} {"UserID":"4324182021466249494","PageViews":6,"Duration":185,"Sign":1}
ClickHouse 允许
- 键值对在对象中的任何顺序。
- 省略某些值。
ClickHouse 忽略元素之间和对象后面的逗号之间的空格。您可以将所有对象放在一行中。您不必用换行符分隔它们。
省略值处理
ClickHouse 将省略的值替换为相应 数据类型 的默认值。
如果指定了 DEFAULT expr
,则 ClickHouse 会根据 input_format_defaults_for_omitted_fields 设置使用不同的替换规则。
考虑以下表格
CREATE TABLE IF NOT EXISTS example_table
(
x UInt32,
a DEFAULT x * 2
) ENGINE = Memory;
- 如果
input_format_defaults_for_omitted_fields = 0
,则x
和a
的默认值等于0
(作为UInt32
数据类型的默认值)。 - 如果
input_format_defaults_for_omitted_fields = 1
,则x
的默认值等于0
,但a
的默认值等于x * 2
。
在使用 input_format_defaults_for_omitted_fields = 1
插入数据时,与使用 input_format_defaults_for_omitted_fields = 0
插入数据相比,ClickHouse 消耗更多的计算资源。
选择数据
以 UserActivity
表为例
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │ 5 │ 146 │ -1 │
│ 4324182021466249494 │ 6 │ 185 │ 1 │
└─────────────────────┴───────────┴──────────┴──────┘
查询 SELECT * FROM UserActivity FORMAT JSONEachRow
返回
{"UserID":"4324182021466249494","PageViews":5,"Duration":146,"Sign":-1}
{"UserID":"4324182021466249494","PageViews":6,"Duration":185,"Sign":1}
与 JSON 格式不同,没有无效 UTF-8 序列的替换。值以与 JSON
相同的方式进行转义。
任何字节集都可以输出到字符串中。如果您确定表中的数据可以格式化为 JSON 而不丢失任何信息,请使用 JSONEachRow
格式。
嵌套结构的使用
如果您有一个带有 嵌套 数据类型列的表,则可以使用相同结构的 JSON 数据插入数据。使用 input_format_import_nested_json 设置启用此功能。
例如,考虑以下表格
CREATE TABLE json_each_row_nested (n Nested (s String, i Int32) ) ENGINE = Memory
如您在 Nested
数据类型描述中看到的,ClickHouse 将嵌套结构的每个组件视为单独的列(对于我们的表,为 n.s
和 n.i
)。您可以通过以下方式插入数据
INSERT INTO json_each_row_nested FORMAT JSONEachRow {"n.s": ["abc", "def"], "n.i": [1, 23]}
要将数据插入为分层 JSON 对象,请设置 input_format_import_nested_json=1。
{
"n": {
"s": ["abc", "def"],
"i": [1, 23]
}
}
没有此设置,ClickHouse 将抛出异常。
SELECT name, value FROM system.settings WHERE name = 'input_format_import_nested_json'
┌─name────────────────────────────┬─value─┐
│ input_format_import_nested_json │ 0 │
└─────────────────────────────────┴───────┘
INSERT INTO json_each_row_nested FORMAT JSONEachRow {"n": {"s": ["abc", "def"], "i": [1, 23]}}
Code: 117. DB::Exception: Unknown field found while parsing JSONEachRow format: n: (at row 1)
SET input_format_import_nested_json=1
INSERT INTO json_each_row_nested FORMAT JSONEachRow {"n": {"s": ["abc", "def"], "i": [1, 23]}}
SELECT * FROM json_each_row_nested
┌─n.s───────────┬─n.i────┐
│ ['abc','def'] │ [1,23] │
└───────────────┴────────┘
JSON 格式设置
- input_format_import_nested_json - 将嵌套 JSON 数据映射到嵌套表(适用于 JSONEachRow 格式)。默认值 -
false
。 - input_format_json_read_bools_as_numbers - 允许在 JSON 输入格式中将布尔值解析为数字。默认值 -
true
。 - input_format_json_read_bools_as_strings - 允许在 JSON 输入格式中将布尔值解析为字符串。默认值 -
true
。 - input_format_json_read_numbers_as_strings - 允许在 JSON 输入格式中将数字解析为字符串。默认值 -
true
。 - input_format_json_read_arrays_as_strings - 允许将 JSON 数组解析为 JSON 输入格式中的字符串。默认值 -
true
。 - input_format_json_read_objects_as_strings - 允许将 JSON 对象解析为 JSON 输入格式中的字符串。默认值 -
true
。 - input_format_json_named_tuples_as_objects - 将命名元组列解析为 JSON 对象。默认值 -
true
。 - input_format_json_try_infer_numbers_from_strings - 在模式推断期间尝试从字符串字段推断数字。默认值 -
false
。 - input_format_json_try_infer_named_tuples_from_objects - 在模式推断期间尝试从 JSON 对象推断命名元组。默认值 -
true
。 - input_format_json_infer_incomplete_types_as_strings - 在 JSON 输入格式中,对于在模式推断期间仅包含 Null 或空对象/数组的键,使用 String 类型。默认值 -
true
。 - input_format_json_defaults_for_missing_elements_in_named_tuple - 在解析命名元组时,为 JSON 对象中缺少的元素插入默认值。默认值 -
true
。 - input_format_json_ignore_unknown_keys_in_named_tuple - 忽略命名元组的 json 对象中的未知键。默认值 -
false
。 - input_format_json_compact_allow_variable_number_of_columns - 允许 JSONCompact/JSONCompactEachRow 格式中列数可变,忽略额外的列,并在缺少列时使用默认值。默认值 -
false
。 - input_format_json_throw_on_bad_escape_sequence - 如果 JSON 字符串包含错误的转义序列,则抛出异常。如果禁用,错误的转义序列将保留在数据中。默认值 -
true
。 - input_format_json_empty_as_default - 将 JSON 输入中的空字段视为默认值。默认值 -
false
。对于复杂的默认表达式,还需要启用 input_format_defaults_for_omitted_fields。 - output_format_json_quote_64bit_integers - 控制 JSON 输出格式中 64 位整数的引用。默认值 -
true
。 - output_format_json_quote_64bit_floats - 控制 JSON 输出格式中 64 位浮点数的引用。默认值 -
false
。 - output_format_json_quote_denormals - 在 JSON 输出格式中启用 '+nan'、'-nan'、'+inf'、'-inf' 输出。默认值 -
false
。 - output_format_json_quote_decimals - 控制 JSON 输出格式中小数的引用。默认值 -
false
。 - output_format_json_escape_forward_slashes - 控制 JSON 输出格式中字符串输出的正斜杠转义。默认值 -
true
。 - output_format_json_named_tuples_as_objects - 将命名元组列序列化为 JSON 对象。默认值 -
true
。 - output_format_json_array_of_rows - 在 JSONEachRow(Compact) 格式中输出所有行的 JSON 数组。默认值 -
false
。 - output_format_json_validate_utf8 - 启用 JSON 输出格式中 UTF-8 序列的验证(请注意,它不会影响 JSON/JSONCompact/JSONColumnsWithMetadata 格式,它们始终验证 utf8)。默认值 -
false
。
BSONEachRow
在此格式中,ClickHouse 格式化/解析数据为一系列 BSON 文档,文档之间没有任何分隔符。每一行都格式化为单个文档,每一列都格式化为带有列名作为键的单个 BSON 文档字段。
对于输出,它使用 ClickHouse 类型和 BSON 类型之间的以下对应关系
ClickHouse 类型 | BSON 类型 |
---|---|
Bool | \x08 boolean |
Int8/UInt8/Enum8 | \x10 int32 |
Int16/UInt16/Enum16 | \x10 int32 |
Int32 | \x10 int32 |
UInt32 | \x12 int64 |
Int64/UInt64 | \x12 int64 |
Float32/Float64 | \x01 double |
Date/Date32 | \x10 int32 |
DateTime | \x12 int64 |
DateTime64 | \x09 datetime |
Decimal32 | \x10 int32 |
Decimal64 | \x12 int64 |
Decimal128 | \x05 binary, \x00 binary subtype, size = 16 |
Decimal256 | \x05 binary, \x00 binary subtype, size = 32 |
Int128/UInt128 | \x05 binary, \x00 binary subtype, size = 16 |
Int256/UInt256 | \x05 binary, \x00 binary subtype, size = 32 |
String/FixedString | \x05 binary, \x00 binary subtype 或 \x02 string 如果启用设置 output_format_bson_string_as_string |
UUID | \x05 binary, \x04 uuid subtype, size = 16 |
Array | \x04 array |
Tuple | \x04 array |
Named Tuple | \x03 document |
Map | \x03 document |
IPv4 | \x10 int32 |
IPv6 | \x05 binary, \x00 binary subtype |
对于输入,它使用 BSON 类型和 ClickHouse 类型之间的以下对应关系
BSON 类型 | ClickHouse 类型 |
---|---|
\x01 double | Float32/Float64 |
\x02 string | String/FixedString |
\x03 document | Map/Named Tuple |
\x04 array | Array/Tuple |
\x05 binary, \x00 binary subtype | String/FixedString/IPv6 |
\x05 binary, \x02 旧二进制子类型 | String/FixedString |
\x05 binary, \x03 旧 uuid 子类型 | UUID |
\x05 binary, \x04 uuid 子类型 | UUID |
\x07 ObjectId | String/FixedString |
\x08 boolean | Bool |
\x09 datetime | DateTime64 |
\x0A null 值 | NULL |
\x0D JavaScript 代码 | String/FixedString |
\x0E 符号 | String/FixedString |
\x10 int32 | Int32/UInt32/Decimal32/IPv4/Enum8/Enum16 |
\x12 int64 | Int64/UInt64/Decimal64/DateTime64 |
不支持其他 BSON 类型。此外,它执行不同整数类型之间的转换(例如,您可以将 BSON int32 值插入 ClickHouse UInt8)。大整数和小数(Int128/UInt128/Int256/UInt256/Decimal128/Decimal256)可以从带有 \x00
二进制子类型的 BSON Binary 值解析。在这种情况下,此格式将验证二进制数据的大小是否等于预期值的大小。
注意:此格式在大端平台上无法正常工作。
BSON 格式设置
- output_format_bson_string_as_string - 对 String 列使用 BSON String 类型而不是 Binary。默认值 -
false
。 - input_format_bson_skip_fields_with_unsupported_types_in_schema_inference - 允许在对 BSONEachRow 格式进行模式推断时跳过具有不受支持类型的列。默认值 -
false
。
Native
最高效的格式。数据以二进制格式分块写入和读取。对于每个块,依次记录行数、列数、列名和类型,以及此块中列的部分内容。换句话说,此格式是“列式”的——它不将列转换为行。这是服务器之间交互、使用命令行客户端和 C++ 客户端的本机接口中使用的格式。
您可以使用此格式快速生成只能由 ClickHouse DBMS 读取的转储。自己处理此格式没有意义。
Null
不输出任何内容。但是,查询会被处理,并且当使用命令行客户端时,数据会被传输到客户端。这用于测试,包括性能测试。显然,此格式仅适用于输出,不适用于解析。
Pretty
将数据输出为 Unicode 艺术表格,还使用 ANSI 转义序列在终端中设置颜色。绘制表格的完整网格,每行在终端中占用两行。每个结果块都输出为单独的表格。这是必要的,以便可以无缓冲地输出块(为了预先计算所有值的可见宽度,需要缓冲)。
NULL 输出为 ᴺᵁᴸᴸ
。
示例(针对 PrettyCompact 格式显示)
SELECT * FROM t_null
┌─x─┬────y─┐
│ 1 │ ᴺᵁᴸᴸ │
└───┴──────┘
Pretty 中的行不会转义*格式。示例针对 PrettyCompact 格式显示
SELECT 'String with \'quotes\' and \t character' AS Escaping_test
┌─Escaping_test────────────────────────┐
│ String with 'quotes' and character │
└──────────────────────────────────────┘
为了避免将过多的数据转储到终端,只打印前 10,000 行。如果行数大于或等于 10,000,则会打印消息“显示了前 10 000”。此格式仅适用于输出查询结果,而不适用于解析(检索要插入表中的数据)。
Pretty 格式支持输出总值(使用 WITH TOTALS 时)和极值(当“extremes”设置为 1 时)。在这些情况下,总值和极值在主数据之后输出,在单独的表格中。示例(针对 PrettyCompact 格式显示)
SELECT EventDate, count() AS c FROM test.hits GROUP BY EventDate WITH TOTALS ORDER BY EventDate FORMAT PrettyCompact
┌──EventDate─┬───────c─┐
│ 2014-03-17 │ 1406958 │
│ 2014-03-18 │ 1383658 │
│ 2014-03-19 │ 1405797 │
│ 2014-03-20 │ 1353623 │
│ 2014-03-21 │ 1245779 │
│ 2014-03-22 │ 1031592 │
│ 2014-03-23 │ 1046491 │
└────────────┴─────────┘
Totals:
┌──EventDate─┬───────c─┐
│ 1970-01-01 │ 8873898 │
└────────────┴─────────┘
Extremes:
┌──EventDate─┬───────c─┐
│ 2014-03-17 │ 1031592 │
│ 2014-03-23 │ 1406958 │
└────────────┴─────────┘
PrettyNoEscapes
与 Pretty 不同之处在于不使用 ANSI 转义序列。这对于在浏览器中显示此格式以及使用“watch”命令行实用程序是必要的。
示例
$ watch -n1 "clickhouse-client --query='SELECT event, value FROM system.events FORMAT PrettyCompactNoEscapes'"
您可以使用 HTTP 接口在浏览器中显示。
PrettyMonoBlock
与 Pretty 不同之处在于最多缓冲 10,000 行,然后输出为单个表,而不是按块输出。
PrettyNoEscapesMonoBlock
与 PrettyNoEscapes 不同之处在于最多缓冲 10,000 行,然后输出为单个表,而不是按块输出。
PrettyCompact
与 Pretty 不同之处在于网格绘制在行之间,结果更紧凑。此格式在命令行客户端的交互模式下默认使用。
PrettyCompactNoEscapes
与 PrettyCompact 不同之处在于不使用 ANSI 转义序列。这对于在浏览器中显示此格式以及使用“watch”命令行实用程序是必要的。
PrettyCompactMonoBlock
与PrettyCompact的不同之处在于,它会缓冲最多 10,000 行,然后作为一个整体输出表格,而不是按块输出。
PrettyCompactNoEscapesMonoBlock
与PrettyCompactNoEscapes的不同之处在于,它会缓冲最多 10,000 行,然后作为一个整体输出表格,而不是按块输出。
PrettySpace
与PrettyCompact的不同之处在于,它使用空格字符而不是网格。
PrettySpaceNoEscapes
与PrettySpace的不同之处在于,它不使用 ANSI 转义序列。这对于在浏览器中显示此格式以及使用“watch”命令行实用程序是必要的。
PrettySpaceMonoBlock
与PrettySpace的不同之处在于,它会缓冲最多 10,000 行,然后作为一个整体输出表格,而不是按块输出。
PrettySpaceNoEscapesMonoBlock
与PrettySpaceNoEscapes的不同之处在于,它会缓冲最多 10,000 行,然后作为一个整体输出表格,而不是按块输出。
Pretty 格式设置
- output_format_pretty_max_rows - Pretty 格式的行数限制。默认值 -
10000
。 - output_format_pretty_max_column_pad_width - Pretty 格式中每列所有值的填充最大宽度。默认值 -
250
。 - output_format_pretty_max_value_width - Pretty 格式中显示的值的最大宽度。如果超过此值,则会被截断。默认值 -
10000
。 - output_format_pretty_color - 在 Pretty 格式中使用 ANSI 转义序列绘制颜色。默认值 -
true
。 - output_format_pretty_grid_charset - 用于打印网格边框的字符集。可用的字符集:ASCII、UTF-8。默认值 -
UTF-8
。 - output_format_pretty_row_numbers - 在 pretty 输出格式的每一行前添加行号。默认值 -
true
。 - output_format_pretty_display_footer_column_names - 如果表格包含多行,则在页脚中显示列名。默认值 -
true
。 - output_format_pretty_display_footer_column_names_min_rows - 设置如果启用了 output_format_pretty_display_footer_column_names 则显示页脚的最小行数。默认值 - 50。
RowBinary
以二进制格式逐行格式化和解析数据。行和值连续列出,没有分隔符。由于数据采用二进制格式,因此 FORMAT RowBinary
之后的定界符严格指定如下:任意数量的空格(' '
- 空格,代码 0x20
;'\t'
- 制表符,代码 0x09
;'\f'
- 换页符,代码 0x0C
),后跟一个换行序列(Windows 风格 "\r\n"
或 Unix 风格 '\n'
),紧跟着二进制数据。此格式效率低于 Native 格式,因为它基于行。
整数使用固定长度的小端表示法。例如,UInt64 使用 8 个字节。DateTime 表示为包含 Unix 时间戳值的 UInt32。Date 表示为包含自 1970-01-01 以来天数的 UInt16 对象。String 表示为可变长度整数(无符号 LEB128),后跟字符串的字节。FixedString 简单地表示为字节序列。
Array 表示为可变长度整数(无符号 LEB128),后跟数组的连续元素。
对于 NULL 支持,在每个 Nullable 值之前添加一个包含 1 或 0 的额外字节。如果为 1,则值为 NULL
,并且此字节被解释为一个单独的值。如果为 0,则字节后的值不为 NULL
。
RowBinaryWithNames
类似于 RowBinary,但添加了头部
- LEB128 编码的列数 (N)
- N 个指定列名的
String
如果设置 input_format_with_names_use_header 设置为 1,则输入数据中的列将按其名称映射到表中的列,如果设置 input_format_skip_unknown_fields 设置为 1,则将跳过名称未知的列。否则,将跳过第一行。
RowBinaryWithNamesAndTypes
类似于 RowBinary,但添加了头部
- LEB128 编码的列数 (N)
- N 个指定列名的
String
- N 个指定列类型的
String
如果设置 input_format_with_names_use_header 设置为 1,则输入数据中的列将按其名称映射到表中的列,如果设置 input_format_skip_unknown_fields 设置为 1,则将跳过名称未知的列。否则,将跳过第一行。如果设置 input_format_with_types_use_header 设置为 1,则将输入数据中的类型与表中相应列的类型进行比较。否则,将跳过第二行。
RowBinaryWithDefaults
类似于 RowBinary,但在每一列之前都添加了一个额外的字节,指示是否应使用默认值。
示例
:) select * from format('RowBinaryWithDefaults', 'x UInt32 default 42, y UInt32', x'010001000000')
┌──x─┬─y─┐
│ 42 │ 1 │
└────┴───┘
对于列 x
,只有一个字节 01
指示应使用默认值,并且在此字节之后没有提供其他数据。对于列 y
,数据以字节 00
开头,指示该列具有应从后续数据 01000000
中读取的实际值。
RowBinary 格式设置
- format_binary_max_string_size - RowBinary 格式中允许的最大 String 大小。默认值 -
1GiB
。 - output_format_binary_encode_types_in_binary_format - 允许使用 二进制编码 在头部写入类型,而不是在 RowBinaryWithNamesAndTypes 输出格式中使用类型名称的字符串。默认值 -
false
。 - input_format_binary_encode_types_in_binary_format - 允许使用 二进制编码 在头部读取类型,而不是在 RowBinaryWithNamesAndTypes 输入格式中使用类型名称的字符串。默认值 -
false
。 - output_format_binary_write_json_as_string - 允许在 RowBinary 输出格式中将 JSON 数据类型的值作为 JSON String 值写入。默认值 -
false
。 - input_format_binary_read_json_as_string - 允许在 RowBinary 输入格式中将 JSON 数据类型的值作为 JSON String 值读取。默认值 -
false
。
Values
将每一行打印在括号中。行之间用逗号分隔。最后一行后面没有逗号。括号内的值也用逗号分隔。数字以十进制格式输出,不带引号。数组以方括号输出。字符串、日期和日期时间以引号输出。转义规则和解析类似于 TabSeparated 格式。在格式化过程中,不会插入额外的空格,但在解析过程中,允许并跳过空格(数组值内部的空格除外,不允许)。NULL 表示为 NULL
。
在 Values 格式中传递数据时,您需要转义的最小字符集:单引号和反斜杠。
这是 INSERT INTO t VALUES ...
中使用的格式,但您也可以将其用于格式化查询结果。
Values 格式设置
- input_format_values_interpret_expressions - 如果流式解析器无法解析该字段,则运行 SQL 解析器并尝试将其解释为 SQL 表达式。默认值 -
true
。 - input_format_values_deduce_templates_of_expressions - 如果流式解析器无法解析该字段,则运行 SQL 解析器,推导出 SQL 表达式的模板,尝试使用模板解析所有行,然后解释所有行的表达式。默认值 -
true
。 - input_format_values_accurate_types_of_literals - 当使用模板解析和解释表达式时,检查字面量的实际类型,以避免可能出现的溢出和精度问题。默认值 -
true
。
Vertical
将每个值打印在单独的行上,并指定列名。如果每一行包含大量列,则此格式便于仅打印一行或几行。
NULL 输出为 ᴺᵁᴸᴸ
。
示例
SELECT * FROM t_null FORMAT Vertical
Row 1:
──────
x: 1
y: ᴺᵁᴸᴸ
Vertical 格式中不会转义行
SELECT 'string with \'quotes\' and \t with some special \n characters' AS test FORMAT Vertical
Row 1:
──────
test: string with 'quotes' and with some special
characters
此格式仅适用于输出查询结果,而不适用于解析(检索要插入表格中的数据)。
XML
XML 格式仅适用于输出,不适用于解析。示例
<?xml version='1.0' encoding='UTF-8' ?>
<result>
<meta>
<columns>
<column>
<name>SearchPhrase</name>
<type>String</type>
</column>
<column>
<name>count()</name>
<type>UInt64</type>
</column>
</columns>
</meta>
<data>
<row>
<SearchPhrase></SearchPhrase>
<field>8267016</field>
</row>
<row>
<SearchPhrase>bathroom interior design</SearchPhrase>
<field>2166</field>
</row>
<row>
<SearchPhrase>clickhouse</SearchPhrase>
<field>1655</field>
</row>
<row>
<SearchPhrase>2014 spring fashion</SearchPhrase>
<field>1549</field>
</row>
<row>
<SearchPhrase>freeform photos</SearchPhrase>
<field>1480</field>
</row>
<row>
<SearchPhrase>angelina jolie</SearchPhrase>
<field>1245</field>
</row>
<row>
<SearchPhrase>omsk</SearchPhrase>
<field>1112</field>
</row>
<row>
<SearchPhrase>photos of dog breeds</SearchPhrase>
<field>1091</field>
</row>
<row>
<SearchPhrase>curtain designs</SearchPhrase>
<field>1064</field>
</row>
<row>
<SearchPhrase>baku</SearchPhrase>
<field>1000</field>
</row>
</data>
<rows>10</rows>
<rows_before_limit_at_least>141137</rows_before_limit_at_least>
</result>
如果列名格式不正确,则仅使用“field”作为元素名称。一般来说,XML 结构遵循 JSON 结构。与 JSON 一样,无效的 UTF-8 序列将更改为替换字符 �,以便输出文本将包含有效的 UTF-8 序列。
在字符串值中,字符 <
和 &
分别转义为 <
和 &
。
数组输出为 <array><elem>Hello</elem><elem>World</elem>...</array>
,元组输出为 <tuple><elem>Hello</elem><elem>World</elem>...</tuple>
。
CapnProto
CapnProto 是一种二进制消息格式,类似于 Protocol Buffers 和 Thrift,但不类似于 JSON 或 MessagePack。
CapnProto 消息是严格类型的,并且不是自描述的,这意味着它们需要外部模式描述。模式是在运行时应用并为每个查询缓存的。
另请参阅 格式模式。
数据类型匹配
下表显示了支持的数据类型以及它们如何在 INSERT
和 SELECT
查询中与 ClickHouse 数据类型 匹配。
CapnProto 数据类型 (INSERT ) | ClickHouse 数据类型 | CapnProto 数据类型 (SELECT ) |
---|---|---|
UINT8 , BOOL | UInt8 | UINT8 |
INT8 | Int8 | INT8 |
UINT16 | UInt16, Date | UINT16 |
INT16 | Int16 | INT16 |
UINT32 | UInt32,DateTime | UINT32 |
INT32 | Int32,Decimal32 | INT32 |
UINT64 | UInt64 | UINT64 |
INT64 | Int64,DateTime64,Decimal64 | INT64 |
FLOAT32 | Float32 | FLOAT32 |
FLOAT64 | Float64 | FLOAT64 |
TEXT,DATA | String,FixedString | TEXT,DATA |
union(T, Void),union(Void, T) | Nullable(T) | union(T, Void),union(Void, T) |
ENUM | Enum(8/16) | ENUM |
LIST | Array | LIST |
STRUCT | Tuple | STRUCT |
UINT32 | IPv4 | UINT32 |
DATA | IPv6 | DATA |
DATA | Int128/UInt128/Int256/UInt256 | DATA |
DATA | Decimal128/Decimal256 | DATA |
STRUCT(entries LIST(STRUCT(key Key, value Value))) | Map | STRUCT(entries LIST(STRUCT(key Key, value Value))) |
整数类型可以在输入/输出期间相互转换。
要使用 CapnProto 格式的 Enum
,请使用 format_capn_proto_enum_comparising_mode 设置。
数组可以嵌套,并且可以将 Nullable
类型的值作为参数。Tuple
和 Map
类型也可以嵌套。
插入和选择数据
您可以使用以下命令将 CapnProto 数据从文件插入到 ClickHouse 表中
$ cat capnproto_messages.bin | clickhouse-client --query "INSERT INTO test.hits SETTINGS format_schema = 'schema:Message' FORMAT CapnProto"
其中 schema.capnp
如下所示
struct Message {
SearchPhrase @0 :Text;
c @1 :Uint64;
}
您可以从 ClickHouse 表中选择数据并将其保存到 CapnProto 格式的某个文件中,使用以下命令:
$ clickhouse-client --query = "SELECT * FROM test.hits FORMAT CapnProto SETTINGS format_schema = 'schema:Message'"
使用自动生成的模式
如果您没有数据的外部 CapnProto 模式,您仍然可以使用自动生成的模式以 CapnProto 格式输出/输入数据。例如
SELECT * FROM test.hits format CapnProto SETTINGS format_capn_proto_use_autogenerated_schema=1
在这种情况下,ClickHouse 将根据表结构使用函数 structureToCapnProtoSchema 自动生成 CapnProto 模式,并使用此模式以 CapnProto 格式序列化数据。
您还可以使用自动生成的模式读取 CapnProto 文件(在这种情况下,文件必须使用相同的模式创建)
$ cat hits.bin | clickhouse-client --query "INSERT INTO test.hits SETTINGS format_capn_proto_use_autogenerated_schema=1 FORMAT CapnProto"
设置 format_capn_proto_use_autogenerated_schema 默认启用,如果未设置 format_schema,则适用。
您还可以使用设置 output_format_schema 在输入/输出期间将自动生成的模式保存在文件中。例如
SELECT * FROM test.hits format CapnProto SETTINGS format_capn_proto_use_autogenerated_schema=1, output_format_schema='path/to/schema/schema.capnp'
在这种情况下,自动生成的 CapnProto 模式将保存在文件 path/to/schema/schema.capnp
中。
Prometheus
以 Prometheus 基于文本的展现格式 公开指标。
输出表应具有正确的结构。列 name
(String)和 value
(数字)是必需的。行可以可选地包含 help
(String)和 timestamp
(数字)。列 type
(String)可以是 counter
、gauge
、histogram
、summary
、untyped
或空。每个指标值还可以具有一些 labels
(Map(String, String))。几个连续的行可以引用具有不同标签的一个指标。表应按指标名称排序(例如,使用 ORDER BY name
)。
对于 histogram
和 summary
的标签,有一些特殊要求,有关详细信息,请参阅 Prometheus 文档。应用于具有标签 {'count':''}
和 {'sum':''}
的行的特殊规则,它们将分别转换为 <metric_name>_count
和 <metric_name>_sum
。
示例
┌─name────────────────────────────────┬─type──────┬─help──────────────────────────────────────┬─labels─────────────────────────┬────value─┬─────timestamp─┐
│ http_request_duration_seconds │ histogram │ A histogram of the request duration. │ {'le':'0.05'} │ 24054 │ 0 │
│ http_request_duration_seconds │ histogram │ │ {'le':'0.1'} │ 33444 │ 0 │
│ http_request_duration_seconds │ histogram │ │ {'le':'0.2'} │ 100392 │ 0 │
│ http_request_duration_seconds │ histogram │ │ {'le':'0.5'} │ 129389 │ 0 │
│ http_request_duration_seconds │ histogram │ │ {'le':'1'} │ 133988 │ 0 │
│ http_request_duration_seconds │ histogram │ │ {'le':'+Inf'} │ 144320 │ 0 │
│ http_request_duration_seconds │ histogram │ │ {'sum':''} │ 53423 │ 0 │
│ http_requests_total │ counter │ Total number of HTTP requests │ {'method':'post','code':'200'} │ 1027 │ 1395066363000 │
│ http_requests_total │ counter │ │ {'method':'post','code':'400'} │ 3 │ 1395066363000 │
│ metric_without_timestamp_and_labels │ │ │ {} │ 12.47 │ 0 │
│ rpc_duration_seconds │ summary │ A summary of the RPC duration in seconds. │ {'quantile':'0.01'} │ 3102 │ 0 │
│ rpc_duration_seconds │ summary │ │ {'quantile':'0.05'} │ 3272 │ 0 │
│ rpc_duration_seconds │ summary │ │ {'quantile':'0.5'} │ 4773 │ 0 │
│ rpc_duration_seconds │ summary │ │ {'quantile':'0.9'} │ 9001 │ 0 │
│ rpc_duration_seconds │ summary │ │ {'quantile':'0.99'} │ 76656 │ 0 │
│ rpc_duration_seconds │ summary │ │ {'count':''} │ 2693 │ 0 │
│ rpc_duration_seconds │ summary │ │ {'sum':''} │ 17560473 │ 0 │
│ something_weird │ │ │ {'problem':'division by zero'} │ inf │ -3982045 │
└─────────────────────────────────────┴───────────┴───────────────────────────────────────────┴────────────────────────────────┴──────────┴───────────────┘
将格式化为
# HELP http_request_duration_seconds A histogram of the request duration.
# TYPE http_request_duration_seconds histogram
http_request_duration_seconds_bucket{le="0.05"} 24054
http_request_duration_seconds_bucket{le="0.1"} 33444
http_request_duration_seconds_bucket{le="0.5"} 129389
http_request_duration_seconds_bucket{le="1"} 133988
http_request_duration_seconds_bucket{le="+Inf"} 144320
http_request_duration_seconds_sum 53423
http_request_duration_seconds_count 144320
# HELP http_requests_total Total number of HTTP requests
# TYPE http_requests_total counter
http_requests_total{code="200",method="post"} 1027 1395066363000
http_requests_total{code="400",method="post"} 3 1395066363000
metric_without_timestamp_and_labels 12.47
# HELP rpc_duration_seconds A summary of the RPC duration in seconds.
# TYPE rpc_duration_seconds summary
rpc_duration_seconds{quantile="0.01"} 3102
rpc_duration_seconds{quantile="0.05"} 3272
rpc_duration_seconds{quantile="0.5"} 4773
rpc_duration_seconds{quantile="0.9"} 9001
rpc_duration_seconds{quantile="0.99"} 76656
rpc_duration_seconds_sum 17560473
rpc_duration_seconds_count 2693
something_weird{problem="division by zero"} +Inf -3982045
Protobuf
Protobuf - 是 Protocol Buffers 格式。
此格式需要一个外部格式模式。模式在查询之间缓存。ClickHouse 支持 proto2
和 proto3
语法。支持重复/可选/必需字段。
用法示例
SELECT * FROM test.table FORMAT Protobuf SETTINGS format_schema = 'schemafile:MessageType'
cat protobuf_messages.bin | clickhouse-client --query "INSERT INTO test.table SETTINGS format_schema='schemafile:MessageType' FORMAT Protobuf"
其中文件 schemafile.proto
如下所示
syntax = "proto3";
message MessageType {
string name = 1;
string surname = 2;
uint32 birthDate = 3;
repeated string phoneNumbers = 4;
};
为了找到表列和 Protocol Buffers 消息类型的字段之间的对应关系,ClickHouse 会比较它们的名称。此比较不区分大小写,并且字符 _
(下划线)和 .
(点)被视为相等。如果列和 Protocol Buffers 消息的字段类型不同,则会应用必要的转换。
支持嵌套消息。例如,对于以下消息类型中的字段 z
message MessageType {
message XType {
message YType {
int32 z;
};
repeated YType y;
};
XType x;
};
ClickHouse 尝试查找名为 x.y.z
(或 x_y_z
或 X.y_Z
等)的列。嵌套消息适用于输入或输出 嵌套数据结构。
在 protobuf 模式中定义的默认值,如下所示
syntax = "proto2";
message MessageType {
optional int32 result_per_page = 3 [default = 10];
}
不适用;将使用 表默认值 而不是它们。
ClickHouse 以 length-delimited
格式输入和输出 protobuf 消息。这意味着在每个消息之前都应写入其长度作为 varint。另请参阅 如何在流行语言中读取/写入长度分隔的 protobuf 消息。
使用自动生成的模式
如果您没有数据的外部 Protobuf 模式,您仍然可以使用自动生成的模式以 Protobuf 格式输出/输入数据。例如
SELECT * FROM test.hits format Protobuf SETTINGS format_protobuf_use_autogenerated_schema=1
在这种情况下,ClickHouse 将根据表结构使用函数 structureToProtobufSchema 自动生成 Protobuf 模式,并使用此模式以 Protobuf 格式序列化数据。
您还可以使用自动生成的模式读取 Protobuf 文件(在这种情况下,文件必须使用相同的模式创建)
$ cat hits.bin | clickhouse-client --query "INSERT INTO test.hits SETTINGS format_protobuf_use_autogenerated_schema=1 FORMAT Protobuf"
设置 format_protobuf_use_autogenerated_schema 默认启用,如果未设置 format_schema,则适用。
您还可以使用设置 output_format_schema 在输入/输出期间将自动生成的模式保存在文件中。例如
SELECT * FROM test.hits format Protobuf SETTINGS format_protobuf_use_autogenerated_schema=1, output_format_schema='path/to/schema/schema.proto'
在这种情况下,自动生成的 Protobuf 模式将保存在文件 path/to/schema/schema.capnp
中。
删除 Protobuf 缓存
要重新加载从 format_schema_path 加载的 Protobuf 模式,请使用 SYSTEM DROP ... FORMAT CACHE 语句。
SYSTEM DROP FORMAT SCHEMA CACHE FOR Protobuf
ProtobufSingle
与 Protobuf 相同,但用于存储/解析单个 Protobuf 消息,没有长度分隔符。
ProtobufList
类似于 Protobuf,但行表示为包含在名为“Envelope”的消息中的子消息序列。
用法示例
SELECT * FROM test.table FORMAT ProtobufList SETTINGS format_schema = 'schemafile:MessageType'
cat protobuflist_messages.bin | clickhouse-client --query "INSERT INTO test.table FORMAT ProtobufList SETTINGS format_schema='schemafile:MessageType'"
其中文件 schemafile.proto
如下所示
syntax = "proto3";
message Envelope {
message MessageType {
string name = 1;
string surname = 2;
uint32 birthDate = 3;
repeated string phoneNumbers = 4;
};
MessageType row = 1;
};
Avro
Apache Avro 是 Apache 的 Hadoop 项目中开发的面向行的、数据序列化框架。
ClickHouse Avro 格式支持读取和写入 Avro 数据文件。
数据类型匹配
下表显示了支持的数据类型以及它们如何在 INSERT
和 SELECT
查询中与 ClickHouse 数据类型 匹配。
Avro 数据类型 INSERT | ClickHouse 数据类型 | Avro 数据类型 SELECT |
---|---|---|
boolean 、int 、long 、float 、double | Int(8\16\32)、UInt(8\16\32) | int |
boolean 、int 、long 、float 、double | Int64、UInt64 | long |
boolean 、int 、long 、float 、double | Float32 | float |
boolean 、int 、long 、float 、double | Float64 | double |
bytes 、string 、fixed 、enum | String | bytes 或 string * |
bytes 、string 、fixed | FixedString(N) | fixed(N) |
enum | Enum(8\16) | enum |
array(T) | Array(T) | array(T) |
map(V, K) | Map(V, K) | map(string, K) |
union(null, T) 、union(T, null) | Nullable(T) | union(null, T) |
null | Nullable(Nothing) | null |
int (date) ** | Date、Date32 | int (date) ** |
long (timestamp-millis) ** | DateTime64(3) | long (timestamp-millis) ** |
long (timestamp-micros) ** | DateTime64(6) | long (timestamp-micros) ** |
bytes (decimal) ** | DateTime64(N) | bytes (decimal) ** |
int | IPv4 | int |
fixed(16) | IPv6 | fixed(16) |
bytes (decimal) ** | Decimal(P, S) | bytes (decimal) ** |
string (uuid) ** | UUID | string (uuid) ** |
fixed(16) | Int128/UInt128 | fixed(16) |
fixed(32) | Int256/UInt256 | fixed(32) |
record | Tuple | record |
* bytes
为默认值,由 output_format_avro_string_column_pattern 控制** Avro 逻辑类型
不支持的 Avro 逻辑数据类型:time-millis
、time-micros
、duration
插入数据
要将数据从 Avro 文件插入到 ClickHouse 表中
$ cat file.avro | clickhouse-client --query="INSERT INTO {some_table} FORMAT Avro"
输入 Avro 文件的根模式必须为 record
类型。
为了找到表列和 Avro 模式的字段之间的对应关系,ClickHouse 会比较它们的名称。此比较区分大小写。跳过未使用的字段。
ClickHouse 表列的数据类型可以与插入的 Avro 数据的相应字段不同。插入数据时,ClickHouse 会根据上表解释数据类型,然后将数据 强制转换 为相应的列类型。
导入数据时,如果模式中未找到字段并且启用了设置 input_format_avro_allow_missing_fields,则将使用默认值而不是错误。
选择数据
要将数据从 ClickHouse 表中选择到 Avro 文件中
$ clickhouse-client --query="SELECT * FROM {some_table} FORMAT Avro" > file.avro
列名必须
- 以
[A-Za-z_]
开头 - 随后仅包含
[A-Za-z0-9_]
可以使用 output_format_avro_codec 和 output_format_avro_sync_interval 分别配置输出 Avro 文件的压缩和同步间隔。
示例数据
使用 ClickHouse 的 DESCRIBE 函数,您可以快速查看 Avro 文件的推断格式,例如以下示例。此示例包含 ClickHouse S3 公共存储桶中公开可访问的 Avro 文件的 URL
DESCRIBE url('https://clickhouse-public-datasets.s3.eu-central-1.amazonaws.com/hits.avro','Avro);
┌─name───────────────────────┬─type────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ WatchID │ Int64 │ │ │ │ │ │
│ JavaEnable │ Int32 │ │ │ │ │ │
│ Title │ String │ │ │ │ │ │
│ GoodEvent │ Int32 │ │ │ │ │ │
│ EventTime │ Int32 │ │ │ │ │ │
│ EventDate │ Date32 │ │ │ │ │ │
│ CounterID │ Int32 │ │ │ │ │ │
│ ClientIP │ Int32 │ │ │ │ │ │
│ ClientIP6 │ FixedString(16) │ │ │ │ │ │
│ RegionID │ Int32 │ │ │ │ │ │
...
│ IslandID │ FixedString(16) │ │ │ │ │ │
│ RequestNum │ Int32 │ │ │ │ │ │
│ RequestTry │ Int32 │ │ │ │ │ │
└────────────────────────────┴─────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
AvroConfluent
AvroConfluent 支持解码通常与 Kafka 和 Confluent Schema Registry 一起使用的单个对象 Avro 消息。
每个 Avro 消息都嵌入一个模式 ID,该 ID 可以借助模式注册表解析为实际模式。
解析模式后会缓存。
模式注册表 URL 使用 format_avro_schema_registry_url 配置。
数据类型匹配
与 Avro 相同。
使用
要快速验证模式解析,可以使用带有clickhouse-local的kafkacat
$ kafkacat -b kafka-broker -C -t topic1 -o beginning -f '%s' -c 3 | clickhouse-local --input-format AvroConfluent --format_avro_schema_registry_url 'http://schema-registry' -S "field1 Int64, field2 String" -q 'select * from table'
1 a
2 b
3 c
要将AvroConfluent
与Kafka一起使用
CREATE TABLE topic1_stream
(
field1 String,
field2 String
)
ENGINE = Kafka()
SETTINGS
kafka_broker_list = 'kafka-broker',
kafka_topic_list = 'topic1',
kafka_group_name = 'group1',
kafka_format = 'AvroConfluent';
-- for debug purposes you can set format_avro_schema_registry_url in a session.
-- this way cannot be used in production
SET format_avro_schema_registry_url = 'http://schema-registry';
SELECT * FROM topic1_stream;
需要在users.xml
中配置format_avro_schema_registry_url
设置以在重新启动后保持其值。您也可以使用Kafka
表引擎的format_avro_schema_registry_url
设置。
Parquet
Apache Parquet是一种在Hadoop生态系统中广泛使用的列式存储格式。ClickHouse支持此格式的读写操作。
数据类型匹配
下表显示了支持的数据类型以及它们如何在 INSERT
和 SELECT
查询中与 ClickHouse 数据类型 匹配。
Parquet数据类型(INSERT ) | ClickHouse 数据类型 | Parquet数据类型(SELECT ) |
---|---|---|
BOOL | Bool | BOOL |
UINT8 , BOOL | UInt8 | UINT8 |
INT8 | Int8/Enum8 | INT8 |
UINT16 | UInt16 | UINT16 |
INT16 | Int16/Enum16 | INT16 |
UINT32 | UInt32 | UINT32 |
INT32 | Int32 | INT32 |
UINT64 | UInt64 | UINT64 |
INT64 | Int64 | INT64 |
FLOAT | Float32 | FLOAT |
DOUBLE | Float64 | DOUBLE |
DATE | Date32 | DATE |
TIME(毫秒) | DateTime | UINT32 |
TIMESTAMP 、TIME(微秒、纳秒) | DateTime64 | TIMESTAMP |
STRING 、BINARY | String | BINARY |
STRING 、BINARY 、FIXED_LENGTH_BYTE_ARRAY | FixedString | FIXED_LENGTH_BYTE_ARRAY |
DECIMAL | Decimal | DECIMAL |
LIST | Array | LIST |
STRUCT | Tuple | STRUCT |
MAP | Map | MAP |
UINT32 | IPv4 | UINT32 |
FIXED_LENGTH_BYTE_ARRAY 、BINARY | IPv6 | FIXED_LENGTH_BYTE_ARRAY |
FIXED_LENGTH_BYTE_ARRAY 、BINARY | Int128/UInt128/Int256/UInt256 | FIXED_LENGTH_BYTE_ARRAY |
数组可以嵌套,并且可以将 Nullable
类型的值作为参数。Tuple
和 Map
类型也可以嵌套。
不支持的Parquet数据类型:FIXED_SIZE_BINARY
、JSON
、UUID
、ENUM
。
ClickHouse表列的数据类型可能与插入的Parquet数据的相应字段不同。插入数据时,ClickHouse会根据上表解释数据类型,然后将数据转换为ClickHouse表列设置的数据类型。
插入和选择数据
您可以通过以下命令将Parquet数据从文件插入到ClickHouse表中
$ cat {filename} | clickhouse-client --query="INSERT INTO {some_table} FORMAT Parquet"
您可以从ClickHouse表中选择数据并将其保存到Parquet格式的文件中,方法是使用以下命令
$ clickhouse-client --query="SELECT * FROM {some_table} FORMAT Parquet" > {some_file.pq}
要与Hadoop交换数据,您可以使用HDFS表引擎。
Parquet格式设置
- output_format_parquet_row_group_size - 数据输出时每组行的行数。默认值 -
1000000
。 - output_format_parquet_string_as_string - 对字符串列使用Parquet字符串类型而不是二进制类型。默认值 -
false
。 - input_format_parquet_import_nested - 允许将结构体数组插入Parquet输入格式中的嵌套表。默认值 -
false
。 - input_format_parquet_case_insensitive_column_matching - 在将Parquet列与ClickHouse列匹配时忽略大小写。默认值 -
false
。 - input_format_parquet_allow_missing_columns - 读取Parquet数据时允许缺少列。默认值 -
false
。 - input_format_parquet_skip_columns_with_unsupported_types_in_schema_inference - 允许在对Parquet格式进行模式推断时跳过具有不支持类型的列。默认值 -
false
。 - input_format_parquet_local_file_min_bytes_for_seek - 在Parquet输入格式中,本地读取(文件)进行查找所需的最小字节数,而不是忽略读取。默认值 -
8192
。 - output_format_parquet_fixed_string_as_fixed_byte_array - 对FixedString列使用Parquet FIXED_LENGTH_BYTE_ARRAY类型而不是Binary/String类型。默认值 -
true
。 - output_format_parquet_version - 输出格式中使用的Parquet格式版本。默认值 -
2.latest
。 - output_format_parquet_compression_method - 输出Parquet格式中使用的压缩方法。默认值 -
lz4
。 - input_format_parquet_max_block_size - parquet读取器的最大块行大小。默认值 -
65409
。 - input_format_parquet_prefer_block_bytes - parquet读取器输出的平均块字节数。默认值 -
16744704
。 - output_format_parquet_write_page_index - 添加写入parquet文件页面索引的可能性。目前需要禁用
output_format_parquet_use_custom_encoder
。默认值 -true
。
ParquetMetadata {data-format-parquet-metadata}
用于读取Parquet文件元数据(https://parquet.apache.org/docs/file-format/metadata/)的特殊格式。它始终输出具有以下结构/内容的一行
- num_columns - 列数
- num_rows - 行总数
- num_row_groups - 行组总数
- format_version - parquet格式版本,始终为1.0或2.6
- total_uncompressed_size - 数据的总未压缩字节大小,计算为所有行组的total_byte_size之和
- total_compressed_size - 数据的总压缩字节大小,计算为所有行组的total_compressed_size之和
- columns - 列元数据列表,具有以下结构
- name - 列名
- path - 列路径(对于嵌套列,与名称不同)
- max_definition_level - 最大定义级别
- max_repetition_level - 最大重复级别
- physical_type - 列物理类型
- logical_type - 列逻辑类型
- compression - 用于此列的压缩
- total_uncompressed_size - 列的总未压缩字节大小,计算为所有行组中该列的total_uncompressed_size之和
- total_compressed_size - 列的总压缩字节大小,计算为所有行组中该列的total_compressed_size之和
- space_saved - 压缩节省的空间百分比,计算为(1 - total_compressed_size/total_uncompressed_size)。
- encodings - 用于此列的编码列表
- row_groups - 行组元数据列表,具有以下结构
- num_columns - 行组中的列数
- num_rows - 行组中的行数
- total_uncompressed_size - 行组的总未压缩字节大小
- total_compressed_size - 行组的总压缩字节大小
- columns - 列块元数据列表,具有以下结构
- name - 列名
- path - 列路径
- total_compressed_size - 列的总压缩字节大小
- total_uncompressed_size - 行组的总未压缩字节大小
- have_statistics - 布尔标志,指示列块元数据是否包含列统计信息
- statistics - 列块统计信息(如果have_statistics = false,则所有字段均为NULL),具有以下结构
- num_values - 列块中非空值的个数
- null_count - 列块中NULL值的个数
- distinct_count - 列块中不同值的个数
- min - 列块的最小值
- max - 列块的最大值
示例
SELECT * FROM file(data.parquet, ParquetMetadata) format PrettyJSONEachRow
{
"num_columns": "2",
"num_rows": "100000",
"num_row_groups": "2",
"format_version": "2.6",
"metadata_size": "577",
"total_uncompressed_size": "282436",
"total_compressed_size": "26633",
"columns": [
{
"name": "number",
"path": "number",
"max_definition_level": "0",
"max_repetition_level": "0",
"physical_type": "INT32",
"logical_type": "Int(bitWidth=16, isSigned=false)",
"compression": "LZ4",
"total_uncompressed_size": "133321",
"total_compressed_size": "13293",
"space_saved": "90.03%",
"encodings": [
"RLE_DICTIONARY",
"PLAIN",
"RLE"
]
},
{
"name": "concat('Hello', toString(modulo(number, 1000)))",
"path": "concat('Hello', toString(modulo(number, 1000)))",
"max_definition_level": "0",
"max_repetition_level": "0",
"physical_type": "BYTE_ARRAY",
"logical_type": "None",
"compression": "LZ4",
"total_uncompressed_size": "149115",
"total_compressed_size": "13340",
"space_saved": "91.05%",
"encodings": [
"RLE_DICTIONARY",
"PLAIN",
"RLE"
]
}
],
"row_groups": [
{
"num_columns": "2",
"num_rows": "65409",
"total_uncompressed_size": "179809",
"total_compressed_size": "14163",
"columns": [
{
"name": "number",
"path": "number",
"total_compressed_size": "7070",
"total_uncompressed_size": "85956",
"have_statistics": true,
"statistics": {
"num_values": "65409",
"null_count": "0",
"distinct_count": null,
"min": "0",
"max": "999"
}
},
{
"name": "concat('Hello', toString(modulo(number, 1000)))",
"path": "concat('Hello', toString(modulo(number, 1000)))",
"total_compressed_size": "7093",
"total_uncompressed_size": "93853",
"have_statistics": true,
"statistics": {
"num_values": "65409",
"null_count": "0",
"distinct_count": null,
"min": "Hello0",
"max": "Hello999"
}
}
]
},
...
]
}
Arrow
Apache Arrow带有两种内置的列式存储格式。ClickHouse支持这些格式的读写操作。
Arrow
是Apache Arrow的“文件模式”格式。它设计用于内存中的随机访问。
数据类型匹配
下表显示了支持的数据类型以及它们如何在 INSERT
和 SELECT
查询中与 ClickHouse 数据类型 匹配。
Arrow数据类型(INSERT ) | ClickHouse 数据类型 | Arrow数据类型(SELECT ) |
---|---|---|
BOOL | Bool | BOOL |
UINT8 , BOOL | UInt8 | UINT8 |
INT8 | Int8/Enum8 | INT8 |
UINT16 | UInt16 | UINT16 |
INT16 | Int16/Enum16 | INT16 |
UINT32 | UInt32 | UINT32 |
INT32 | Int32 | INT32 |
UINT64 | UInt64 | UINT64 |
INT64 | Int64 | INT64 |
FLOAT 、HALF_FLOAT | Float32 | FLOAT32 |
DOUBLE | Float64 | FLOAT64 |
DATE32 | Date32 | UINT16 |
DATE64 | DateTime | UINT32 |
TIMESTAMP 、TIME32 、TIME64 | DateTime64 | UINT32 |
STRING 、BINARY | String | BINARY |
STRING 、BINARY 、FIXED_SIZE_BINARY | FixedString | FIXED_SIZE_BINARY |
DECIMAL | Decimal | DECIMAL |
DECIMAL256 | Decimal256 | DECIMAL256 |
LIST | Array | LIST |
STRUCT | Tuple | STRUCT |
MAP | Map | MAP |
UINT32 | IPv4 | UINT32 |
FIXED_SIZE_BINARY 、BINARY | IPv6 | FIXED_SIZE_BINARY |
FIXED_SIZE_BINARY 、BINARY | Int128/UInt128/Int256/UInt256 | FIXED_SIZE_BINARY |
数组可以嵌套,并且可以将 Nullable
类型的值作为参数。Tuple
和 Map
类型也可以嵌套。
DICTIONARY
类型支持INSERT
查询,对于SELECT
查询,有一个output_format_arrow_low_cardinality_as_dictionary设置允许将LowCardinality类型输出为DICTIONARY
类型。
不支持的Arrow数据类型:FIXED_SIZE_BINARY
、JSON
、UUID
、ENUM
。
ClickHouse表列的数据类型不必与相应的Arrow数据字段匹配。插入数据时,ClickHouse会根据上表解释数据类型,然后将数据转换为ClickHouse表列设置的数据类型。
插入数据
您可以通过以下命令将Arrow数据从文件插入到ClickHouse表中
$ cat filename.arrow | clickhouse-client --query="INSERT INTO some_table FORMAT Arrow"
选择数据
您可以从ClickHouse表中选择数据并将其保存到Arrow格式的文件中,方法是使用以下命令
$ clickhouse-client --query="SELECT * FROM {some_table} FORMAT Arrow" > {filename.arrow}
Arrow格式设置
- output_format_arrow_low_cardinality_as_dictionary - 启用将ClickHouse LowCardinality类型输出为Dictionary Arrow类型。默认值 -
false
。 - output_format_arrow_use_64_bit_indexes_for_dictionary - 对Dictionary索引使用64位整数类型。默认值 -
false
。 - output_format_arrow_use_signed_indexes_for_dictionary - 对Dictionary索引使用带符号的整数类型。默认值 -
true
。 - output_format_arrow_string_as_string - 对字符串列使用Arrow字符串类型而不是二进制类型。默认值 -
false
。 - input_format_arrow_case_insensitive_column_matching - 在将Arrow列与ClickHouse列匹配时忽略大小写。默认值 -
false
。 - input_format_arrow_allow_missing_columns - 读取Arrow数据时允许缺少列。默认值 -
false
。 - input_format_arrow_skip_columns_with_unsupported_types_in_schema_inference - 允许在对Arrow格式进行模式推断时跳过具有不支持类型的列。默认值 -
false
。 - output_format_arrow_fixed_string_as_fixed_byte_array - 对FixedString列使用Arrow FIXED_SIZE_BINARY类型而不是Binary/String类型。默认值 -
true
。 - output_format_arrow_compression_method - 输出Arrow格式中使用的压缩方法。默认值 -
lz4_frame
。
ArrowStream
ArrowStream
是Apache Arrow的“流模式”格式。它设计用于内存中的流处理。
ORC
Apache ORC是一种在Hadoop生态系统中广泛使用的列式存储格式。
数据类型匹配
下表显示了支持的数据类型以及它们如何在 INSERT
和 SELECT
查询中与 ClickHouse 数据类型 匹配。
ORC 数据类型 (INSERT ) | ClickHouse 数据类型 | ORC 数据类型 (SELECT ) |
---|---|---|
布尔值 | UInt8 | 布尔值 |
Tinyint | Int8/UInt8/Enum8 | Tinyint |
Smallint | Int16/UInt16/Enum16 | Smallint |
Int | Int32/UInt32 | Int |
Bigint | Int64/UInt32 | Bigint |
Float | Float32 | Float |
Double | Float64 | Double |
Decimal | Decimal | Decimal |
日期 | Date32 | 日期 |
时间戳 | DateTime64 | 时间戳 |
String , Char , Varchar , Binary | String | Binary |
列表 | Array | 列表 |
结构体 | Tuple | 结构体 |
Map | Map | Map |
Int | IPv4 | Int |
Binary | IPv6 | Binary |
Binary | Int128/UInt128/Int256/UInt256 | Binary |
Binary | Decimal256 | Binary |
其他类型不支持。
数组可以嵌套,并且可以将 Nullable
类型的值作为参数。Tuple
和 Map
类型也可以嵌套。
ClickHouse 表列的数据类型不必与对应的 ORC 数据字段匹配。插入数据时,ClickHouse 会根据上表解释数据类型,然后将数据转换为 ClickHouse 表列设置的数据类型。
插入数据
您可以使用以下命令将 ORC 文件中的数据插入 ClickHouse 表中
$ cat filename.orc | clickhouse-client --query="INSERT INTO some_table FORMAT ORC"
选择数据
您可以选择 ClickHouse 表中的数据并使用以下命令将其保存到 ORC 格式的文件中
$ clickhouse-client --query="SELECT * FROM {some_table} FORMAT ORC" > {filename.orc}
Arrow 格式设置
- output_format_arrow_string_as_string - 对字符串列使用Arrow字符串类型而不是二进制类型。默认值 -
false
。 - output_format_orc_compression_method - 输出 ORC 格式中使用的压缩方法。默认值 -
none
。 - input_format_arrow_case_insensitive_column_matching - 在将Arrow列与ClickHouse列匹配时忽略大小写。默认值 -
false
。 - input_format_arrow_allow_missing_columns - 读取Arrow数据时允许缺少列。默认值 -
false
。 - input_format_arrow_skip_columns_with_unsupported_types_in_schema_inference - 允许在对Arrow格式进行模式推断时跳过具有不支持类型的列。默认值 -
false
。
要与Hadoop交换数据,您可以使用HDFS表引擎。
One
一种特殊的输入格式,不读取文件中的任何数据,只返回一行包含类型为 UInt8
、名称为 dummy
且值为 0
的列(类似于 system.one
表)。可以与虚拟列 _file/_path
一起使用来列出所有文件,而无需读取实际数据。
示例
查询
SELECT _file FROM file('path/to/files/data*', One);
结果
┌─_file────┐
│ data.csv │
└──────────┘
┌─_file──────┐
│ data.jsonl │
└────────────┘
┌─_file────┐
│ data.tsv │
└──────────┘
┌─_file────────┐
│ data.parquet │
└──────────────┘
Npy
此函数旨在将 NumPy 数组从 .npy 文件加载到 ClickHouse 中。NumPy 文件格式是一种二进制格式,用于高效存储数值数据的数组。导入期间,ClickHouse 将顶层维度视为包含单个列的行数组。支持的 Npy 数据类型及其在 ClickHouse 中的对应类型
Npy 数据类型 (INSERT ) | ClickHouse 数据类型 | Npy 数据类型 (SELECT ) |
---|---|---|
i1 | Int8 | i1 |
i2 | Int16 | i2 |
i4 | Int32 | i4 |
i8 | Int64 | i8 |
u1 , b1 | UInt8 | u1 |
u2 | UInt16 | u2 |
u4 | UInt32 | u4 |
u8 | UInt64 | u8 |
f2 , f4 | Float32 | f4 |
f8 | Float64 | f8 |
S , U | String | S |
FixedString | S |
使用 Python 将数组保存为 .npy 格式的示例
import numpy as np
arr = np.array([[[1],[2],[3]],[[4],[5],[6]]])
np.save('example_array.npy', arr)
在 ClickHouse 中读取 NumPy 文件的示例
查询
SELECT *
FROM file('example_array.npy', Npy)
结果
┌─array─────────┐
│ [[1],[2],[3]] │
│ [[4],[5],[6]] │
└───────────────┘
选择数据
您可以选择 ClickHouse 表中的数据并使用以下命令将其保存到 Npy 格式的文件中
$ clickhouse-client --query="SELECT {column} FROM {some_table} FORMAT Npy" > {filename.npy}
LineAsString
在此格式中,输入数据的每一行都被解释为单个字符串值。此格式只能解析具有单个类型为 String 的字段的表。其余列必须设置为 DEFAULT 或 MATERIALIZED,或省略。
示例
查询
DROP TABLE IF EXISTS line_as_string;
CREATE TABLE line_as_string (field String) ENGINE = Memory;
INSERT INTO line_as_string FORMAT LineAsString "I love apple", "I love banana", "I love orange";
SELECT * FROM line_as_string;
结果
┌─field─────────────────────────────────────────────┐
│ "I love apple", "I love banana", "I love orange"; │
└───────────────────────────────────────────────────┘
Regexp
根据正则表达式解析导入数据的每一行。
使用 Regexp
格式时,可以使用以下设置
format_regexp_escaping_rule
— String。支持以下转义规则- CSV(类似于 CSV)
- JSON(类似于 JSONEachRow)
- 转义(类似于 TSV)
- 带引号(类似于 Values)
- 原始(将子模式提取为整体,没有转义规则,类似于 TSVRaw)
format_regexp_skip_unmatched
— UInt8。定义在format_regexp
表达式与导入数据不匹配的情况下是否需要抛出异常。可以设置为0
或1
。
用法
来自 format_regexp 设置的正则表达式应用于导入数据的每一行。正则表达式中的子模式数量必须等于导入数据集中的列数。
导入数据的行必须以换行符 '\n'
或 DOS 样式换行符 "\r\n"
分隔。
根据 format_regexp_escaping_rule 设置,使用对应数据类型的方法解析每个匹配的子模式的内容。
如果正则表达式与行不匹配并且 format_regexp_skip_unmatched 设置为 1,则会静默跳过该行。否则,将抛出异常。
示例
考虑文件 data.tsv
id: 1 array: [1,2,3] string: str1 date: 2020-01-01
id: 2 array: [1,2,3] string: str2 date: 2020-01-02
id: 3 array: [1,2,3] string: str3 date: 2020-01-03
以及表
CREATE TABLE imp_regex_table (id UInt32, array Array(UInt32), string String, date Date) ENGINE = Memory;
导入命令
$ cat data.tsv | clickhouse-client --query "INSERT INTO imp_regex_table SETTINGS format_regexp='id: (.+?) array: (.+?) string: (.+?) date: (.+?)', format_regexp_escaping_rule='Escaped', format_regexp_skip_unmatched=0 FORMAT Regexp;"
查询
SELECT * FROM imp_regex_table;
结果
┌─id─┬─array───┬─string─┬───────date─┐
│ 1 │ [1,2,3] │ str1 │ 2020-01-01 │
│ 2 │ [1,2,3] │ str2 │ 2020-01-02 │
│ 3 │ [1,2,3] │ str3 │ 2020-01-03 │
└────┴─────────┴────────┴────────────┘
格式模式
包含格式模式的文件名由设置 format_schema
设置。当使用 Cap'n Proto
和 Protobuf
格式之一时,需要设置此设置。格式模式是文件名和此文件中消息类型的名称的组合,用冒号分隔,例如 schemafile.proto:MessageType
。如果文件具有该格式的标准扩展名(例如,Protobuf
的 .proto
),则可以省略它,在这种情况下,格式模式看起来像 schemafile:MessageType
。
如果您通过 客户端 在 交互模式 下输入或输出数据,则格式模式中指定的文件名可以包含客户端当前目录的绝对路径或相对路径。如果您在 批处理模式 下使用客户端,则出于安全原因,模式的路径必须是相对路径。
如果您通过 HTTP 接口 输入或输出数据,则格式模式中指定的文件名应位于服务器配置中 format_schema_path 中指定的目录中。
跳过错误
某些格式(例如 CSV
、TabSeparated
、TSKV
、JSONEachRow
、Template
、CustomSeparated
和 Protobuf
)如果发生解析错误,可以跳过损坏的行并从下一行的开头继续解析。请参阅 input_format_allow_errors_num 和 input_format_allow_errors_ratio 设置。限制
- 如果发生解析错误,
JSONEachRow
会跳过所有数据直到新行(或 EOF),因此行必须以\n
分隔才能正确计算错误。 Template
和CustomSeparated
使用最后一列后的分隔符和行之间的分隔符来查找下一行的开头,因此跳过错误仅在其中至少一个不为空时才有效。
RawBLOB
在此格式中,所有输入数据都读取到单个值中。只能解析具有单个类型为 String 或类似类型的字段的表。结果以二进制格式输出,没有分隔符和转义。如果输出多个值,则格式不明确,并且无法读取回数据。
以下是 RawBLOB
和 TabSeparatedRaw 格式的比较。
RawBLOB
:
- 数据以二进制格式输出,不进行转义;
- 值之间没有分隔符;
- 每个值的末尾没有换行符。
TabSeparatedRaw
:
- 数据输出不进行转义;
- 行包含以制表符分隔的值;
- 每行最后一个值之后都有换行符。
以下是 RawBLOB
和 RowBinary 格式的比较。
RawBLOB
:
- 字符串字段输出时不带长度前缀。
RowBinary
:
- 字符串字段表示为可变整数格式(无符号[LEB128](https://en.wikipedia.org/wiki/LEB128)) 中的长度,后跟字符串的字节。
当将空数据传递给 RawBLOB
输入时,ClickHouse 会抛出异常
Code: 108. DB::Exception: No data to insert
示例
$ clickhouse-client --query "CREATE TABLE {some_table} (a String) ENGINE = Memory;"
$ cat {filename} | clickhouse-client --query="INSERT INTO {some_table} FORMAT RawBLOB"
$ clickhouse-client --query "SELECT * FROM {some_table} FORMAT RawBLOB" | md5sum
结果
f9725a22f9191e064120d718e26862a9 -
MsgPack
ClickHouse 支持读取和写入 MessagePack 数据文件。
数据类型匹配
MessagePack 数据类型 (INSERT ) | ClickHouse 数据类型 | MessagePack 数据类型 (SELECT ) |
---|---|---|
uint N , positive fixint | UIntN | uint N |
int N , negative fixint | IntN | int N |
布尔值 | UInt8 | uint 8 |
fixstr , str 8 , str 16 , str 32 , bin 8 , bin 16 , bin 32 | String | bin 8 , bin 16 , bin 32 |
fixstr , str 8 , str 16 , str 32 , bin 8 , bin 16 , bin 32 | FixedString | bin 8 , bin 16 , bin 32 |
float 32 | Float32 | float 32 |
float 64 | Float64 | float 64 |
uint 16 | 日期 | uint 16 |
int 32 | Date32 | int 32 |
uint 32 | DateTime | uint 32 |
uint 64 | DateTime64 | uint 64 |
fixarray , array 16 , array 32 | Array/Tuple | fixarray , array 16 , array 32 |
fixmap , map 16 , map 32 | Map | fixmap , map 16 , map 32 |
uint 32 | IPv4 | uint 32 |
bin 8 | String | bin 8 |
int 8 | Enum8 | int 8 |
bin 8 | (U)Int128/(U)Int256 | bin 8 |
int 32 | Decimal32 | int 32 |
int 64 | Decimal64 | int 64 |
bin 8 | Decimal128/Decimal256 | bin 8 |
示例
写入“.msgpk”文件
$ clickhouse-client --query="CREATE TABLE msgpack (array Array(UInt8)) ENGINE = Memory;"
$ clickhouse-client --query="INSERT INTO msgpack VALUES ([0, 1, 2, 3, 42, 253, 254, 255]), ([255, 254, 253, 42, 3, 2, 1, 0])";
$ clickhouse-client --query="SELECT * FROM msgpack FORMAT MsgPack" > tmp_msgpack.msgpk;
MsgPack 格式设置
- input_format_msgpack_number_of_columns - 插入的 MsgPack 数据中的列数。用于从数据自动推断模式。默认值 -
0
。 - output_format_msgpack_uuid_representation - 在 MsgPack 格式中输出 UUID 的方式。默认值 -
EXT
。
MySQLDump
ClickHouse 支持读取 MySQL 转储。它读取转储中属于一个表的 INSERT 查询中的所有数据。如果有多个表,则默认情况下它会读取第一个表的数据。您可以使用 input_format_mysql_dump_table_name 设置指定要从中读取数据的表的名称。如果设置 input_format_mysql_dump_map_columns 为 1 并且转储包含指定表的 CREATE 查询或 INSERT 查询中的列名,则输入数据中的列将根据其名称映射到表中的列,如果设置 input_format_skip_unknown_fields 为 1,则未知名称的列将被跳过。此格式支持模式推断:如果转储包含指定表的 CREATE 查询,则从中提取结构,否则从 INSERT 查询的数据推断模式。
示例
文件 dump.sql
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `test` (
`x` int DEFAULT NULL,
`y` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `test` VALUES (1,NULL),(2,NULL),(3,NULL),(3,NULL),(4,NULL),(5,NULL),(6,7);
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `test 3` (
`y` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `test 3` VALUES (1);
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `test2` (
`x` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `test2` VALUES (1),(2),(3);
查询
DESCRIBE TABLE file(dump.sql, MySQLDump) SETTINGS input_format_mysql_dump_table_name = 'test2'
┌─name─┬─type────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ x │ Nullable(Int32) │ │ │ │ │ │
└──────┴─────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
SELECT *
FROM file(dump.sql, MySQLDump)
SETTINGS input_format_mysql_dump_table_name = 'test2'
┌─x─┐
│ 1 │
│ 2 │
│ 3 │
└───┘
DWARF
从 ELF 文件(可执行文件、库或目标文件)中解析 DWARF 调试符号。类似于 dwarfdump
,但速度更快(数百 MB/s)并支持 SQL。为 .debug_info
部分中的每个调试信息条目 (DIE) 生成一行。包括 DWARF 编码用于终止树中子列表的“空”条目。
快速背景:.debug_info
由单元组成,对应于编译单元。每个单元都是DIE的树,以compile_unit
DIE 作为其根。每个 DIE 都有一个标签和一个属性列表。每个属性都有一个名称和一个值(以及一个形式,指定值是如何编码的)。DIE 代表源代码中的内容,其标签指示它是什么类型的。例如,有函数(标签 = subprogram
)、类/结构体/枚举(class_type
/structure_type
/enumeration_type
)、变量(variable
)、函数参数(formal_parameter
)。树结构反映了相应的源代码。例如,class_type
DIE 可以包含表示类方法的subprogram
DIE。
输出以下列
offset
- DIE 在.debug_info
部分中的位置size
- 编码的 DIE 中的字节数(包括属性)tag
- DIE 的类型;省略了传统的“DW_TAG”前缀unit_name
- 包含此 DIE 的编译单元的名称unit_offset
- 包含此 DIE 的编译单元在.debug_info
部分中的位置ancestor_tags
- 树中当前 DIE 的祖先标签数组,按从内到外的顺序排列ancestor_offsets
- 祖先的偏移量,与ancestor_tags
平行- 为方便起见,复制了属性数组中的一些常用属性
name
linkage_name
- 经过修饰的完全限定名称;通常只有函数才有(但并非所有函数都有)decl_file
- 声明此实体的源代码文件的名称decl_line
- 声明此实体的源代码的行号
- 描述属性的平行数组
attr_name
- 属性的名称;省略了传统的“DW_AT”前缀attr_form
- 属性如何编码和解释;省略了传统的 DW_FORM 前缀attr_int
- 属性的整数值;如果属性没有数值,则为 0attr_str
- 属性的字符串值;如果属性没有字符串值,则为空
示例:查找定义函数最多的编译单元(包括模板实例化和包含的头文件中的函数)
SELECT
unit_name,
count() AS c
FROM file('programs/clickhouse', DWARF)
WHERE tag = 'subprogram' AND NOT has(attr_name, 'declaration')
GROUP BY unit_name
ORDER BY c DESC
LIMIT 3
┌─unit_name──────────────────────────────────────────────────┬─────c─┐
│ ./src/Core/Settings.cpp │ 28939 │
│ ./src/AggregateFunctions/AggregateFunctionSumMap.cpp │ 23327 │
│ ./src/AggregateFunctions/AggregateFunctionUniqCombined.cpp │ 22649 │
└────────────────────────────────────────────────────────────┴───────┘
3 rows in set. Elapsed: 1.487 sec. Processed 139.76 million rows, 1.12 GB (93.97 million rows/s., 752.77 MB/s.)
Peak memory usage: 271.92 MiB.
Markdown
您可以使用Markdown格式导出结果,以生成准备好粘贴到您的.md
文件中的输出。
SELECT
number,
number * 2
FROM numbers(5)
FORMAT Markdown
| number | multiply(number, 2) |
|-:|-:|
| 0 | 0 |
| 1 | 2 |
| 2 | 4 |
| 3 | 6 |
| 4 | 8 |
Markdown 表格将自动生成,并可在支持 Markdown 的平台(如 Github)上使用。此格式仅用于输出。
表单
表单格式可用于以 application/x-www-form-urlencoded 格式读取或写入单个记录,其中数据格式为key1=value1&key2=value2
示例
给定一个名为data.tmp
的文件,放置在user_files
路径中,其中包含一些 URL 编码的数据
t_page=116&c.e=ls7xfkpm&c.tti.m=raf&rt.start=navigation&rt.bmr=390%2C11%2C10
SELECT * FROM file(data.tmp, Form) FORMAT vertical;
结果
Row 1:
──────
t_page: 116
c.e: ls7xfkpm
c.tti.m: raf
rt.start: navigation
rt.bmr: 390,11,10