clickhouse输⼊输出（导⼊导出）数据格式
  　ClickHouse可以接受和返回各种格式的数据。受⽀持的输⼊格式可⽤于提交给INSERT语句、从⽂件表(File,URL,HDFS或者外部⽬录)执⾏SELECT语句，受⽀持的输出格式可⽤于格式化SELECT语句的返回结果，或者通过INSERT写⼊到⽂件表。
以下是⽀持的格式:
格式输⼊输出TabSeparated✔✔TabSeparatedRaw✔✔TabSeparatedWithNames✔✔TabSeparatedWithNamesAndTypes✔✔
Template✔✔TemplateIgnoreSpaces✔✗
CSV✔✔CSVWithNames✔✔CustomSeparated✔✔
Values✔✔
Vertical✗✔
JSON✗✔JSONAsString✔✗JSONStrings✗✔JSONCompact✗✔JSONCompactStrings✗✔JSONEachRow✔✔JSONEachRowWithProgress✗✔JSONStringsEachRow✔✔JSONStringsEachRowWithProgress✗✔JSONCompactEachRow✔✔JSONCompactEachRowWithNamesAndTypes✔✔JSONCompactStringsEachRow✔✔JSONCompactStringsEachRowWithNamesAndTypes✔✔
TSKV✔✔
Pretty✗✔PrettyCompact✗✔PrettyCompactMonoBlock✗✔PrettyNoEscapes✗✔PrettySpace✗✔
Protobuf✔✔ProtobufSingle✔✔
Avro✔✔AvroConfluent✔✗
Parquet✔✔
Arrow✔✔ArrowStream✔✔
ORC✔✔
RowBinary✔✔RowBinaryWithNamesAndTypes✔✔
Native✔✔
Null✗✔
XML✗✔
CapnProto✔✗LineAsString✔✗
Regexp✔✗
RawBLOB✔✔TabSeparated
  在TabSeparated分隔格式中，数据按⾏写⼊。每⾏包含由制表符分隔的值，每个值后跟⼀个制表符，除了⾏中最后⼀个值，最后的值后⾯是⼀个换⾏符。在任何地⽅都采⽤严格的Unix换⾏(\n)。最后⼀⾏结束后必须再插⼊⼀个换⾏符。值以⽂本格式编写，不包含引号，并使⽤转义的特殊字符。
  这种格式也被称为TSV。
  TabSeparated格式便于其他的程序和脚本处理数据。默认情况下，HTTP接⼝和命令⾏客户端的批处理模式中会使⽤这个格式。这种格式还允许在不同dbms之间传输数据。例如，您可以从MySQL获取转储并将其上传到ClickHouse，反之亦然。
  TabSeparated格式⽀持输出总计的结果(当SQL语句包含WITH TOTALS)和极值(当extremes被设置为1时)。在这种情况下，总计值和极值会在主数据后输出。主要结果、总值和极值之间⽤空⾏分隔。⽰例:
set extremes=1;
SELECT EventDate, count() AS c FROM test.hits_v1 GROUP BY EventDate WITH TOTALS ORDER BY EventDate FORMAT TabSeparated;
2014-03-171406958
2014-03-181383658
2014-03-191405797
2014-03-201353623
2014-03-211245779
2014-03-221031592
2014-03-231046491
0000-00-008873898
2014-03-171031592
2014-03-231406958
数据格式化
  整数是⽤⼗进制形式写的。数字可以在开头包含⼀个额外的+字符(解析时忽略该符号，格式化时不记录该符号)。⾮负数不能包含负号。在读取时，允许将空字符串解析为零，或者(对于有符号类型)将'-'(仅有减号的字符串)解析为零。不符合相应数据类型的数字可能被解析为数值，⽽不会出现错误消息。
  浮点数以⼗进制形式书写。⽤.作为⼩数点的符号。⽀持指数符号，如inf、+inf、-inf和nan。⼩数点前或后可以不出现数字(如123.或.123)。在格式化期间，浮点数精度可能会丢失。在解析期间，并不严格要求读取与机器可以表⽰的最接近的数值。
  ⽇期以YYYY-MM-DD格式编写，并以相同的格式解析，但允许使⽤任何字符作为分隔符。⽇期和时间以YYYY-MM-DD hh:mm:ss的格式书写，并以相同的格式解析，但允许使⽤任何字符作为分隔符。时区采⽤客户端或服务器端时区(取决于谁对数据进⾏格式化)。对于带有时间的⽇期，没有是哦⽤夏时制时间。因此，如果导出的数据采⽤了夏时制，则实际⼊库的时间不⼀定与预期的时间对应，解析将根据解析动作发起⽅选择时间。在读取操作期间，不正确的⽇期和具有时间的⽇期可以⾃然溢出(如2021-01-32)或设置成空⽇期和时间，⽽不会出现错误消息。
有个例外情况，时间解析也⽀持Unix时间戳(如果它恰好由10个⼗进制数字组成)。其结果与时区⽆关。
格式YYYY-MM-DD hh:mm:ss和NNNNNNNNNN这两种格式会⾃动转换。
  字符串输出时，特殊字符会⾃动转义。以下转义序列⽤于输出:\b, \f, \r, \n, \t, \0, \', \\。解析还⽀持\a、\v和\xHH(HH代表⼗六进制编码)和\c，其中c是任何字符(这些序列被转换
为c)。因此，读取数据时，换⾏符可以写成\n或\。例如，如果要表⽰字符串Hello world中'Hello'与'world'中间的空格实际上是个换⾏符，可以写成下⾯的形式:
Hello\nworld
--等同于
Hello\
world
  第⼆种形式也受⽀持，因为MySQL导出tab-separated格式的数据时使⽤这种格式。使⽤TabSeparated格式传递数据时⾄少需要转义以下特殊字符：制表符(\t)、换⾏符(\n)和反斜杠(\)。只有⼀⼩部分符号被转义。您可以很容易地到⼀个能够破坏命令⾏终端输出的特殊字符。
  数组⽤⽅括号包裹、逗号分隔的形式表⽰(例如[11,22,33])。数组中的数字项按上述规则进⾏格式化。⽇期和⽇期时间类型⽤单引号包裹。字符串⽤单引号包裹，遵循上述转义规则。NULL将输出为\N。Nested结构的每个元素都表⽰为数组。
  ⽰例：
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']
TabSeparatedRaw
  与TabSeparated格式的不同之处在于，写⼊的数据不会进⾏转义处理。使⽤这种格式解析时，每个字段中不允许出现制表符或换⾏符。这种格式也被称为TSVRaw。
TabSeparatedWithNames
  不同于TabSeparated，列名会写在第⼀⾏。在解析过程中，第⼀⾏被完全忽略。您不能依赖列名来确定它们的位置或检查它们的正确性。(将来可能会添加对头⾏解析的⽀持。)这种格式也被称为TSVWithNames。
TabSeparatedWithNamesAndTypes
  与TabSeparated格式不同的是列名写在第⼀⾏，⽽列类型写在第⼆⾏。在解析过程中，将完全忽略第⼀⾏和第⼆⾏。这种格式也被称为TSVWithNamesAndTypes。
Template
  此格式允许指定带有占位符的⾃定义格式字符串，这些占位符⽤于指定转义规则。它使⽤format_schema, format_schema_rows, format_schema_rows_between_delimiter以及其他格式的⼀些设置(例如转义JSON时使⽤output_format_json_quote_64bit_integers，具体请向下阅读)
设置format_template_row⽤于指定⾏格式⽂件的路径，该格式⽂件包含⾏格式字符串，语法如下:
delimiter_i${column_i:serializeAs_i}delimiter_i${column_i:serializeAs_i} ... delimiter_i,
其中，delimiter_i是各值之间的分隔符($符号可以转义为$$)，column_i是选择或插⼊值的列的名称或索引(如果为空，则跳过该列)，serializeAs_i是列值的转义规则。⽀持以下转义规则:
CSV, JSON, XML (类似于相同名称的格式)
Escaped (类似于TSV)
Quoted (类似于Values)
Raw (不转义，类似于TSVRaw)
None (不转义，具体请向下阅读)
  如果省略了转义规则，那么将使⽤None。XML和Raw只适⽤于输出。
  对于下⾯的格式字符串:
`Search phrase: ${SearchPhrase:Quoted}, count: ${c:Escaped}, ad price: $$${price:JSON};`
  SearchPhrase、c和price列的值遵循Quoted、Escaped和JSON转义规则，将分别在Search phrase:，， count:，， ad price: $和；分隔符之间打印(⽤于SELECT)或输⼊期望的值(⽤
于INSERT)。例如:
Search phrase: 'bathroom interior design', count: 2166, ad price: $3;format_template_rows_between_delimiter设置指定⾏之间的分隔符，它将打印(或输⼊期望值)在每⼀⾏之后，最后⼀⾏除外(该设置默认值为\n)。
  设置format_template_resultset指定结果集格式⽂件路径，该⽂件包含结果集的格式字符串。结果集
的格式字符串与上述的⾏格式字符串具有相同的语法，并允许指定前缀、后缀，还提供打印⼀些附加信息的⽅法。该⽂件使⽤如下占位符，⽤于取代⾏格式字符串的列名的位置(即column_i):
data代表遵循format_template_row格式的数据⾏，由format_template_rows_between_delimiter设置制定的字符分隔。此占位符必须是格式字符串中的第⼀个占位符。
totals代表遵循format_template_row格式的数据⾏，该⾏⽤于代表结果的总计值(当SQL语句包含了WITH TOTALS)
min代表遵循format_template_row格式的数据⾏，该⾏⽤于代表结果的最⼩值(当extremes设置为1时)
max代表遵循format_template_row格式的数据⾏，该⾏⽤于代表结果的最⼤值(当extremes设置为1时)
rows代表输出⾏的总数
rows_before_limit代表没有LIMIT限制的结果最⼩⾏数。仅当查询包含LIMIT时才输出此值。如果查询包含GROUP BY，那么rows_before_limit_at_least就是没有LIMIT的确切⾏数。
time代表请求执⾏时间(秒)
rows_read代表已读取的⾏数
phpjson格式化输出
bytes_read代表已读取(未压缩)的字节数
  占位符data、totals、min和max不允许指定转义规则(允许显式指定None)。其余占位符可以指定任何转义规则。如果format_template_resultset设置为空，则使⽤${data}作为默认值。对于insert查询，格式允许跳过某些列或某些字段的前缀或后缀(参见⽰例)。
  Select⽰例:
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>yandex</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>
  Insert⽰例：
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 FORMAT Template SETTINGS
format_template_resultset ='/some/path/resultset.format', format_template_row ='/some/path/row.format'
  /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 FORMAT TemplateIgnoreSpaces SETTINGS
format_template_resultset ='/some/path/resultset.format', format_template_row ='/some/path/row.format', format_template_rows_between_delimiter =','
--/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=yandex    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差。这种格式⽀持数据输出和解析。⽤于解析时，可
以任意指定列的顺序，也可以省略某些列，那些列的值为该列的默认值，⼀般情况下为0或空⽩。不⽀持将可在表中可指定的复杂值设为默认值。解析时允许出现后没有=的字
段tskv。此字段会被忽略。
CSV
  按，分隔的数据格式(RFC)。格式化时，每⼀⾏的值会⽤双引号括起，⽇期和时间也会以双引号包括。
数字不⽤双引号括起，字符串中的双引号会以两个双引号输出，除此之
外没有其他规则来做字符转义了。值由分隔符隔开，这个分隔符默认是,。每⼀⾏使⽤Unix换⾏符(LF,\n)分隔。数组序列化成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,\n)，Windows(CR LF\r\n)和Mac OS
Classic(CR LF\t\n)都受⽀持。如果启⽤input_format_defaults_for_omitted_fields，对应列如果存在未输⼊的空⽩，且没有⽤双引号括起，将⽤默认值替换。NULL被格式化
为\N或NULL或⼀个不是引号的其他字符串(详见配置input_format_csv_unquoted_null_literal_as_null或input_format_defaults_for_omitted_fields)。CSV格式⽀持输出总数和极值的
⽅式与TabSeparated相同。
CSVWithNames
  会输出带头部的信息(字段列表)，和TabSeparatedWithNames⼀样。
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的转义规则,⽽不是从格式字符串。也有CustomSeparatedIgnoreSpaces格式，这个类似于TemplateIgnoreSpaces。
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": "'hello'",
"type": "String"
},
{
"name": "multiply(42, number)",
"type": "UInt64"
},
{
"name": "range(5)",
"type": "Array(UInt8)"
}
],
"data":
[
{
"'hello'": "hello",
"multiply(42, number)": "0",
"range(5)": [0,1,2,3,4]
},
{
"'hello'": "hello",
"multiply(42, number)": "42",
"range(5)": [0,1,2,3,4]
},
{
"'hello'": "hello",
"multiply(42, number)": "84",
"range(5)": [0,1,2,3,4]
}
],
"rows": 3,
"rows_before_limit_at_least": 3
}
  JSON与JavaScript兼容。为了确保这⼀点，⼀些字符被另外转义：斜线/被转义为\/; 换⾏符U+2028和U+2029会打断⼀些浏览器的解析，它们会被转义为\uXXXX。 ASCII控制字
符被转义：退格，换页，换⾏，回车和制表符被转义为\b，\f，\n，\r，\t。剩下的0x00-0x1F被转义成相应的\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 – 极值(当extremes设置为1时)。该格式仅适⽤于输出查询结果，但不适⽤于解析输⼊(将数据插
⼊到表中)。
  ClickHouse⽀持NULL, 在JSON输出中显⽰为null。若要在输出中启⽤+nan、-nan、+inf、-inf值，请设置output_format_json_quote_denormals为1。
  参考 
JSONEachRow格式
output_format_json_array_of_rows设置
JSONStrings
  与JSON的不同之处在于数据字段以字符串输出，⽽不是以类型化JSON值输出。
  ⽰例：
{
"meta":
[
{
"name": "'hello'",
"type": "String"
},
{
"name": "multiply(42, number)",
"type": "UInt64"
},
{
"name": "range(5)",
"type": "Array(UInt8)"
}
],
"data":
[
{
"'hello'": "hello",
"multiply(42, number)": "0",
"range(5)": "[0,1,2,3,4]"
},
{
"'hello'": "hello",
"multiply(42, number)": "42",
"range(5)": "[0,1,2,3,4]"
},
{
"'hello'": "hello",
"multiply(42, number)": "84",
"range(5)": "[0,1,2,3,4]"
}
],
"rows": 3,
"rows_before_limit_at_least": 3
}
  注意range(5)的值。
JSONAsString
  在这种格式中，⼀个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 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}          │
└───────────────────────────────────┘
JSONCompact
JSONCompactStrings
  与JSON格式不同的是它以数组的⽅式输出结果，⽽不是以结构体。
  ⽰例：
// JSONCompact
{
"meta":
[
{
"name": "'hello'",
"type": "String"
},
{
"name": "multiply(42, number)",
"type": "UInt64"
},
{
"name": "range(5)",
"type": "Array(UInt8)"
}
],
"data":
[
["hello", "0", [0,1,2,3,4]],
["hello", "42", [0,1,2,3,4]],
["hello", "84", [0,1,2,3,4]]
],
"rows": 3,
"rows_before_limit_at_least": 3
}
// JSONCompactStrings
{

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。