formats.md 64.5 KB
Newer Older
I
Ivan Blinkov 已提交
1 2 3 4 5
---
toc_priority: 21
toc_title: Input and Output Formats
---

I
Ivan Blinkov 已提交
6
# Formats for Input and Output Data {#formats}
7

8
ClickHouse can accept and return data in various formats. A format supported for input can be used to parse the data provided to `INSERT`s, to perform `SELECT`s from a file-backed table such as File, URL or HDFS, or to read an external dictionary. A format supported for output can be used to arrange the
9
results of a `SELECT`, and to perform `INSERT`s into a file-backed table.
10

11
The supported formats are:
12

H
hcz 已提交
13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57
| Format                                                                                  | Input | Output |
|-----------------------------------------------------------------------------------------|-------|--------|
| [TabSeparated](#tabseparated)                                                           | ✔     | ✔      |
| [TabSeparatedRaw](#tabseparatedraw)                                                     | ✔     | ✔      |
| [TabSeparatedWithNames](#tabseparatedwithnames)                                         | ✔     | ✔      |
| [TabSeparatedWithNamesAndTypes](#tabseparatedwithnamesandtypes)                         | ✔     | ✔      |
| [Template](#format-template)                                                            | ✔     | ✔      |
| [TemplateIgnoreSpaces](#templateignorespaces)                                           | ✔     | ✗      |
| [CSV](#csv)                                                                             | ✔     | ✔      |
| [CSVWithNames](#csvwithnames)                                                           | ✔     | ✔      |
| [CustomSeparated](#format-customseparated)                                              | ✔     | ✔      |
| [Values](#data-format-values)                                                           | ✔     | ✔      |
| [Vertical](#vertical)                                                                   | ✗     | ✔      |
| [VerticalRaw](#verticalraw)                                                             | ✗     | ✔      |
| [JSON](#json)                                                                           | ✗     | ✔      |
| [JSONString](#jsonstring)                                                               | ✗     | ✔      |
| [JSONCompact](#jsoncompact)                                                             | ✗     | ✔      |
| [JSONCompactString](#jsoncompactstring)                                                 | ✗     | ✔      |
| [JSONEachRow](#jsoneachrow)                                                             | ✔     | ✔      |
| [JSONEachRowWithProgress](#jsoneachrowwithprogress)                                     | ✗     | ✔      |
| [JSONStringEachRow](#jsonstringeachrow)                                                 | ✔     | ✔      |
| [JSONStringEachRowWithProgress](#jsonstringeachrowwithprogress)                         | ✗     | ✔      |
| [JSONCompactEachRow](#jsoncompacteachrow)                                               | ✔     | ✔      |
| [JSONCompactEachRowWithNamesAndTypes](#jsoncompacteachrowwithnamesandtypes)             | ✔     | ✔      |
| [JSONCompactStringEachRow](#jsoncompactstringeachrow)                                   | ✔     | ✔      |
| [JSONCompactStringEachRowWithNamesAndTypes](#jsoncompactstringeachrowwithnamesandtypes) | ✔     | ✔      |
| [TSKV](#tskv)                                                                           | ✔     | ✔      |
| [Pretty](#pretty)                                                                       | ✗     | ✔      |
| [PrettyCompact](#prettycompact)                                                         | ✗     | ✔      |
| [PrettyCompactMonoBlock](#prettycompactmonoblock)                                       | ✗     | ✔      |
| [PrettyNoEscapes](#prettynoescapes)                                                     | ✗     | ✔      |
| [PrettySpace](#prettyspace)                                                             | ✗     | ✔      |
| [Protobuf](#protobuf)                                                                   | ✔     | ✔      |
| [Avro](#data-format-avro)                                                               | ✔     | ✔      |
| [AvroConfluent](#data-format-avro-confluent)                                            | ✔     | ✗      |
| [Parquet](#data-format-parquet)                                                         | ✔     | ✔      |
| [Arrow](#data-format-arrow)                                                             | ✔     | ✔      |
| [ArrowStream](#data-format-arrow-stream)                                                | ✔     | ✔      |
| [ORC](#data-format-orc)                                                                 | ✔     | ✗      |
| [RowBinary](#rowbinary)                                                                 | ✔     | ✔      |
| [RowBinaryWithNamesAndTypes](#rowbinarywithnamesandtypes)                               | ✔     | ✔      |
| [Native](#native)                                                                       | ✔     | ✔      |
| [Null](#null)                                                                           | ✗     | ✔      |
| [XML](#xml)                                                                             | ✗     | ✔      |
| [CapnProto](#capnproto)                                                                 | ✔     | ✗      |
58

59
You can control some format processing parameters with the ClickHouse settings. For more information read the [Settings](../operations/settings/settings.md) section.
60

I
Ivan Blinkov 已提交
61
## TabSeparated {#tabseparated}
62

63
In TabSeparated format, data is written by row. Each row contains values separated by tabs. Each value is followed by a tab, except the last value in the row, which is followed by a line feed. Strictly Unix line feeds are assumed everywhere. The last row also must contain a line feed at the end. Values are written in text format, without enclosing quotation marks, and with special characters escaped.
64

I
Ivan Blinkov 已提交
65
This format is also available under the name `TSV`.
66

67
The `TabSeparated` format is convenient for processing data using custom programs and scripts. It is used by default in the HTTP interface, and in the command-line client’s batch mode. This format also allows transferring data between different DBMSs. For example, you can get a dump from MySQL and upload it to ClickHouse, or vice versa.
I
Ivan Blinkov 已提交
68

69
The `TabSeparated` format supports outputting total values (when using WITH TOTALS) and extreme values (when ‘extremes’ is set to 1). In these cases, the total values and extremes are output after the main data. The main result, total values, and extremes are separated from each other by an empty line. Example:
I
Ivan Blinkov 已提交
70

71
``` sql
I
Ivan Blinkov 已提交
72
SELECT EventDate, count() AS c FROM test.hits GROUP BY EventDate WITH TOTALS ORDER BY EventDate FORMAT TabSeparated``
73 74
```

75
``` text
I
Ivan Blinkov 已提交
76 77 78 79 80 81 82 83
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

A
Alexey Milovidov 已提交
84
1970-01-01      8873898
85

I
Ivan Blinkov 已提交
86 87
2014-03-17      1031592
2014-03-23      1406958
88
```
I
Ivan Blinkov 已提交
89

90
### Data Formatting {#data-formatting}
I
Ivan Blinkov 已提交
91

92
Integer numbers are written in decimal form. Numbers can contain an extra “+” character at the beginning (ignored when parsing, and not recorded when formatting). Non-negative numbers can’t contain the negative sign. When reading, it is allowed to parse an empty string as a zero, or (for signed types) a string consisting of just a minus sign as a zero. Numbers that do not fit into the corresponding data type may be parsed as a different number, without an error message.
I
Ivan Blinkov 已提交
93

94
Floating-point numbers are written in decimal form. The dot is used as the decimal separator. Exponential entries are supported, as are ‘inf’, ‘+inf’, ‘-inf’, and ‘nan’. An entry of floating-point numbers may begin or end with a decimal point.
I
Ivan Blinkov 已提交
95 96 97 98
During formatting, accuracy may be lost on floating-point numbers.
During parsing, it is not strictly required to read the nearest machine-representable number.

Dates are written in YYYY-MM-DD format and parsed in the same format, but with any characters as separators.
99 100
Dates with times are written in the format `YYYY-MM-DD hh:mm:ss` and parsed in the same format, but with any characters as separators.
This all occurs in the system time zone at the time the client or server starts (depending on which of them formats data). For dates with times, daylight saving time is not specified. So if a dump has times during daylight saving time, the dump does not unequivocally match the data, and parsing will select one of the two times.
I
Ivan Blinkov 已提交
101 102 103 104
During a read operation, incorrect dates and dates with times can be parsed with natural overflow or as null dates and times, without an error message.

As an exception, parsing dates with times is also supported in Unix timestamp format, if it consists of exactly 10 decimal digits. The result is not time zone-dependent. The formats YYYY-MM-DD hh:mm:ss and NNNNNNNNNN are differentiated automatically.

105
Strings are output with backslash-escaped special characters. The following escape sequences are used for output: `\b`, `\f`, `\r`, `\n`, `\t`, `\0`, `\'`, `\\`. Parsing also supports the sequences `\a`, `\v`, and `\xHH` (hex escape sequences) and any `\c` sequences, where `c` is any character (these sequences are converted to `c`). Thus, reading data supports formats where a line feed can be written as `\n` or `\`, or as a line feed. For example, the string `Hello world` with a line feed between the words instead of space can be parsed in any of the following variations:
I
Ivan Blinkov 已提交
106

107
``` text
I
Ivan Blinkov 已提交
108
Hello\nworld
109

I
Ivan Blinkov 已提交
110 111 112 113 114 115 116 117 118 119
Hello\
world
```

The second variant is supported because MySQL uses it when writing tab-separated dumps.

The minimum set of characters that you need to escape when passing data in TabSeparated format: tab, line feed (LF) and backslash.

Only a small set of symbols are escaped. You can easily stumble onto a string value that your terminal will ruin in output.

120
Arrays are written as a list of comma-separated values in square brackets. Number items in the array are formatted as normally. `Date` and `DateTime` types are written in single quotes. Strings are written in single quotes with the same escaping rules as above.
I
Ivan Blinkov 已提交
121

122
[NULL](../sql-reference/syntax.md) is formatted as `\N`.
I
Ivan Blinkov 已提交
123

124
Each element of [Nested](../sql-reference/data-types/nested-data-structures/nested.md) structures is represented as array.
125 126 127

For example:

128
``` sql
129 130
CREATE TABLE nestedt
(
131
    `id` UInt8,
132
    `aux` Nested(
133
        a UInt8,
134 135 136 137 138
        b String
    )
)
ENGINE = TinyLog
```
139 140

``` sql
141 142
INSERT INTO nestedt Values ( 1, [1], ['a'])
```
143 144

``` sql
145 146
SELECT * FROM nestedt FORMAT TSV
```
147 148

``` text
149
1  [1]    ['a']
150 151
```

I
Ivan Blinkov 已提交
152
## TabSeparatedRaw {#tabseparatedraw}
I
Ivan Blinkov 已提交
153 154

Differs from `TabSeparated` format in that the rows are written without escaping.
H
hcz 已提交
155
When parsing with this format, tabs or linefeeds are not allowed in each field.
I
Ivan Blinkov 已提交
156 157 158

This format is also available under the name `TSVRaw`.

I
Ivan Blinkov 已提交
159
## TabSeparatedWithNames {#tabseparatedwithnames}
I
Ivan Blinkov 已提交
160 161

Differs from the `TabSeparated` format in that the column names are written in the first row.
162
During parsing, the first row is completely ignored. You can’t use column names to determine their position or to check their correctness.
I
Ivan Blinkov 已提交
163 164 165 166
(Support for parsing the header row may be added in the future.)

This format is also available under the name `TSVWithNames`.

I
Ivan Blinkov 已提交
167
## TabSeparatedWithNamesAndTypes {#tabseparatedwithnamesandtypes}
I
Ivan Blinkov 已提交
168 169 170 171 172 173

Differs from the `TabSeparated` format in that the column names are written to the first row, while the column types are in the second row.
During parsing, the first and second rows are completely ignored.

This format is also available under the name `TSVWithNamesAndTypes`.

I
Ivan Blinkov 已提交
174
## Template {#format-template}
A
Alexander Tokmakov 已提交
175

176
This format allows specifying a custom format string with placeholders for values with a specified escaping rule.
A
Alexander Tokmakov 已提交
177

178
It uses settings `format_template_resultset`, `format_template_row`, `format_template_rows_between_delimiter` and some settings of other formats (e.g. `output_format_json_quote_64bit_integers` when using `JSON` escaping, see further)
A
Alexander Tokmakov 已提交
179

A
Alexander Tokmakov 已提交
180
Setting `format_template_row` specifies path to file, which contains format string for rows with the following syntax:
A
Alexander Tokmakov 已提交
181

182
`delimiter_1${column_1:serializeAs_1}delimiter_2${column_2:serializeAs_2} ... delimiter_N`,
A
Alexander Tokmakov 已提交
183

184 185 186
where `delimiter_i` is a delimiter between values (`$` symbol can be escaped as `$$`),
`column_i` is a name or index of a column whose values are to be selected or inserted (if empty, then column will be skipped),
`serializeAs_i` is an escaping rule for the column values. The following escaping rules are supported:
187

188 189 190 191 192
-   `CSV`, `JSON`, `XML` (similarly to the formats of the same names)
-   `Escaped` (similarly to `TSV`)
-   `Quoted` (similarly to `Values`)
-   `Raw` (without escaping, similarly to `TSVRaw`)
-   `None` (no escaping rule, see further)
193

194
If an escaping rule is omitted, then `None` will be used. `XML` and `Raw` are suitable only for output.
195

196
So, for the following format string:
197

198
      `Search phrase: ${SearchPhrase:Quoted}, count: ${c:Escaped}, ad price: $$${price:JSON};`
199

200
the values of `SearchPhrase`, `c` and `price` columns, which are escaped as `Quoted`, `Escaped` and `JSON` will be printed (for select) or will be expected (for insert) between `Search phrase:`, `, count:`, `, ad price: $` and `;` delimiters respectively. For example:
A
Alexander Tokmakov 已提交
201

202
`Search phrase: 'bathroom interior design', count: 2166, ad price: $3;`
203

204
The `format_template_rows_between_delimiter` setting specifies delimiter between rows, which is printed (or expected) after every row except the last one (`\n` by default)
A
Alexander Tokmakov 已提交
205

206
Setting `format_template_resultset` specifies the path to file, which contains a format string for resultset. Format string for resultset has the same syntax as a format string for row and allows to specify a prefix, a suffix and a way to print some additional information. It contains the following placeholders instead of column names:
A
Alexander Tokmakov 已提交
207

208 209 210 211 212 213 214 215 216
-   `data` is the rows with data in `format_template_row` format, separated by `format_template_rows_between_delimiter`. This placeholder must be the first placeholder in the format string.
-   `totals` is the row with total values in `format_template_row` format (when using WITH TOTALS)
-   `min` is the row with minimum values in `format_template_row` format (when extremes are set to 1)
-   `max` is the row with maximum values in `format_template_row` format (when extremes are set to 1)
-   `rows` is the total number of output rows
-   `rows_before_limit` is the minimal number of rows there would have been without LIMIT. Output only if the query contains LIMIT. If the query contains GROUP BY, rows\_before\_limit\_at\_least is the exact number of rows there would have been without a LIMIT.
-   `time` is the request execution time in seconds
-   `rows_read` is the number of rows has been read
-   `bytes_read` is the number of bytes (uncompressed) has been read
217 218 219 220 221 222 223 224

The placeholders `data`, `totals`, `min` and `max` must not have escaping rule specified (or `None` must be specified explicitly). The remaining placeholders may have any escaping rule specified.
If the `format_template_resultset` setting is an empty string, `${data}` is used as default value.
For insert queries format allows skipping some columns or some fields if prefix or suffix (see example).

Select example:

``` sql
225
SELECT SearchPhrase, count() AS c FROM test.hits GROUP BY SearchPhrase ORDER BY c DESC LIMIT 5 FORMAT Template SETTINGS
A
Alexander Tokmakov 已提交
226 227
format_template_resultset = '/some/path/resultset.format', format_template_row = '/some/path/row.format', format_template_rows_between_delimiter = '\n    '
```
228

A
Alexander Tokmakov 已提交
229
`/some/path/resultset.format`:
230 231

``` text
A
Alexander Tokmakov 已提交
232
<!DOCTYPE HTML>
A
Alexander Tokmakov 已提交
233 234 235 236 237 238 239 240 241 242 243
<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>
A
Alexander Tokmakov 已提交
244
</html>
A
Alexander Tokmakov 已提交
245
```
246

A
Alexander Tokmakov 已提交
247
`/some/path/row.format`:
248 249

``` text
A
Alexander Tokmakov 已提交
250
<tr> <td>${0:XML}</td> <td>${1:XML}</td> </tr>
A
Alexander Tokmakov 已提交
251
```
252

A
Alexander Tokmakov 已提交
253
Result:
254 255

``` html
A
Alexander Tokmakov 已提交
256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274
<!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>
```

A
Alexander Tokmakov 已提交
275
Insert example:
276 277

``` text
A
Alexander Tokmakov 已提交
278 279 280 281
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
A
Alexander Tokmakov 已提交
282
```
283 284

``` sql
285
INSERT INTO UserActivity FORMAT Template SETTINGS
A
Alexander Tokmakov 已提交
286 287
format_template_resultset = '/some/path/resultset.format', format_template_row = '/some/path/row.format'
```
288

A
Alexander Tokmakov 已提交
289
`/some/path/resultset.format`:
290 291

``` text
A
Alexander Tokmakov 已提交
292 293
Some header\n${data}\nTotal rows: ${:CSV}\n
```
294

A
Alexander Tokmakov 已提交
295
`/some/path/row.format`:
296 297

``` text
A
Alexander Tokmakov 已提交
298
Page views: ${PageViews:CSV}, User id: ${UserID:CSV}, Useless field: ${:CSV}, Duration: ${Duration:CSV}, Sign: ${Sign:CSV}
A
Alexander Tokmakov 已提交
299
```
300 301

`PageViews`, `UserID`, `Duration` and `Sign` inside placeholders are names of columns in the table. Values after `Useless field` in rows and after `\nTotal rows:` in suffix will be ignored.
A
Alexander Tokmakov 已提交
302
All delimiters in the input data must be strictly equal to delimiters in specified format strings.
303

I
Ivan Blinkov 已提交
304
## TemplateIgnoreSpaces {#templateignorespaces}
A
Alexander Tokmakov 已提交
305

A
Alexander Tokmakov 已提交
306
This format is suitable only for input.
307 308 309 310
Similar to `Template`, but skips whitespace characters between delimiters and values in the input stream. However, if format strings contain whitespace characters, these characters will be expected in the input stream. Also allows to specify empty placeholders (`${}` or `${:None}`) to split some delimiter into separate parts to ignore spaces between them. Such placeholders are used only for skipping whitespace characters.
It’s possible to read `JSON` using this format, if values of columns have the same order in all rows. For example, the following request can be used for inserting data from output example of format [JSON](#json):

``` sql
A
Alexander Tokmakov 已提交
311
INSERT INTO table_name FORMAT TemplateIgnoreSpaces SETTINGS
A
Alexander Tokmakov 已提交
312 313
format_template_resultset = '/some/path/resultset.format', format_template_row = '/some/path/row.format', format_template_rows_between_delimiter = ','
```
314

A
Alexander Tokmakov 已提交
315
`/some/path/resultset.format`:
316 317

``` text
A
Alexander Tokmakov 已提交
318 319
{${}"meta"${}:${:JSON},${}"data"${}:${}[${data}]${},${}"totals"${}:${:JSON},${}"extremes"${}:${:JSON},${}"rows"${}:${:JSON},${}"rows_before_limit_at_least"${}:${:JSON}${}}
```
320

A
Alexander Tokmakov 已提交
321
`/some/path/row.format`:
322 323

``` text
A
Alexander Tokmakov 已提交
324
{${}"SearchPhrase"${}:${}${phrase:JSON}${},${}"c"${}:${}${cnt:JSON}${}}
A
Alexander Tokmakov 已提交
325
```
A
Alexander Tokmakov 已提交
326

I
Ivan Blinkov 已提交
327
## TSKV {#tskv}
I
Ivan Blinkov 已提交
328 329 330

Similar to TabSeparated, but outputs a value in name=value format. Names are escaped the same way as in TabSeparated format, and the = symbol is also escaped.

331
``` text
I
Ivan Blinkov 已提交
332 333 334 335 336 337 338 339 340 341 342 343
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
```

344
[NULL](../sql-reference/syntax.md) is formatted as `\N`.
I
Ivan Blinkov 已提交
345 346 347 348 349

``` sql
SELECT * FROM t_null FORMAT TSKV
```

350
``` text
351
x=1    y=\N
I
Ivan Blinkov 已提交
352 353
```

A
alexey-milovidov 已提交
354
When there is a large number of small columns, this format is ineffective, and there is generally no reason to use it. Nevertheless, it is no worse than JSONEachRow in terms of efficiency.
I
Ivan Blinkov 已提交
355 356 357 358

Both data output and parsing are supported in this format. For parsing, any order is supported for the values of different columns. It is acceptable for some values to be omitted – they are treated as equal to their default values. In this case, zeros and blank rows are used as default values. Complex values that could be specified in the table are not supported as defaults.

Parsing allows the presence of the additional field `tskv` without the equal sign or a value. This field is ignored.
359

I
Ivan Blinkov 已提交
360
## CSV {#csv}
361 362 363

Comma Separated Values format ([RFC](https://tools.ietf.org/html/rfc4180)).

364
When formatting, rows are enclosed in double-quotes. A double quote inside a string is output as two double quotes in a row. There are no other rules for escaping characters. Date and date-time are enclosed in double-quotes. Numbers are output without quotes. Values are separated by a delimiter character, which is `,` by default. The delimiter character is defined in the setting [format\_csv\_delimiter](../operations/settings/settings.md#settings-format_csv_delimiter). Rows are separated using the Unix line feed (LF). Arrays are serialized in CSV as follows: first, the array is serialized to a string as in TabSeparated format, and then the resulting string is output to CSV in double-quotes. Tuples in CSV format are serialized as separate columns (that is, their nesting in the tuple is lost).
365

366
``` bash
367
$ clickhouse-client --format_csv_delimiter="|" --query="INSERT INTO test.csv FORMAT CSV" < data.csv
C
chertus 已提交
368 369
```

370
\*By default, the delimiter is `,`. See the [format\_csv\_delimiter](../operations/settings/settings.md#settings-format_csv_delimiter) setting for more information.
371

372 373
When parsing, all values can be parsed either with or without quotes. Both double and single quotes are supported. Rows can also be arranged without quotes. In this case, they are parsed up to the delimiter character or line feed (CR or LF). In violation of the RFC, when parsing rows without quotes, the leading and trailing spaces and tabs are ignored. For the line feed, Unix (LF), Windows (CR LF) and Mac OS Classic (CR LF) types are all supported.

374
Empty unquoted input values are replaced with default values for the respective columns, if
375
[input\_format\_defaults\_for\_omitted\_fields](../operations/settings/settings.md#session_settings-input_format_defaults_for_omitted_fields)
376 377
is enabled.

378
`NULL` is formatted as `\N` or `NULL` or an empty unquoted string (see settings [input\_format\_csv\_unquoted\_null\_literal\_as\_null](../operations/settings/settings.md#settings-input_format_csv_unquoted_null_literal_as_null) and [input\_format\_defaults\_for\_omitted\_fields](../operations/settings/settings.md#session_settings-input_format_defaults_for_omitted_fields)).
379 380 381

The CSV format supports the output of totals and extremes the same way as `TabSeparated`.

382
## CSVWithNames {#csvwithnames}
383 384 385

Also prints the header row, similar to `TabSeparatedWithNames`.

I
Ivan Blinkov 已提交
386
## CustomSeparated {#format-customseparated}
A
Alexander Tokmakov 已提交
387 388 389 390

Similar to [Template](#format-template), but it prints or reads all columns and uses escaping rule from setting `format_custom_escaping_rule` and delimiters from settings `format_custom_field_delimiter`, `format_custom_row_before_delimiter`, `format_custom_row_after_delimiter`, `format_custom_row_between_delimiter`, `format_custom_result_before_delimiter` and `format_custom_result_after_delimiter`, not from format strings.
There is also `CustomSeparatedIgnoreSpaces` format, which is similar to `TemplateIgnoreSpaces`.

I
Ivan Blinkov 已提交
391
## JSON {#json}
392

393
Outputs data in JSON format. Besides data tables, it also outputs column names and types, along with some additional information: the total number of output rows, and the number of rows that could have been output if there weren’t a LIMIT. Example:
394

395
``` sql
396 397 398
SELECT SearchPhrase, count() AS c FROM test.hits GROUP BY SearchPhrase WITH TOTALS ORDER BY c DESC LIMIT 5 FORMAT JSON
```

399
``` json
400 401 402 403
{
        "meta":
        [
                {
H
hcz 已提交
404
                        "name": "'hello'",
405 406 407
                        "type": "String"
                },
                {
H
hcz 已提交
408
                        "name": "multiply(42, number)",
409
                        "type": "UInt64"
H
hcz 已提交
410 411 412 413
                },
                {
                        "name": "range(5)",
                        "type": "Array(UInt8)"
414 415 416 417 418 419
                }
        ],

        "data":
        [
                {
H
hcz 已提交
420 421 422
                        "'hello'": "hello",
                        "multiply(42, number)": "0",
                        "range(5)": [0,1,2,3,4]
423 424
                },
                {
H
hcz 已提交
425 426 427
                        "'hello'": "hello",
                        "multiply(42, number)": "42",
                        "range(5)": [0,1,2,3,4]
428 429
                },
                {
H
hcz 已提交
430 431 432
                        "'hello'": "hello",
                        "multiply(42, number)": "84",
                        "range(5)": [0,1,2,3,4]
433 434 435
                }
        ],

H
hcz 已提交
436
        "rows": 3,
437

H
hcz 已提交
438
        "rows_before_limit_at_least": 3
439 440 441
}
```

442
The JSON is compatible with JavaScript. To ensure this, some characters are additionally escaped: the slash `/` is escaped as `\/`; alternative line breaks `U+2028` and `U+2029`, which break some browsers, are escaped as `\uXXXX`. ASCII control characters are escaped: backspace, form feed, line feed, carriage return, and horizontal tab are replaced with `\b`, `\f`, `\n`, `\r`, `\t` , as well as the remaining bytes in the 00-1F range using `\uXXXX` sequences. Invalid UTF-8 sequences are changed to the replacement character � so the output text will consist of valid UTF-8 sequences. For compatibility with JavaScript, Int64 and UInt64 integers are enclosed in double-quotes by default. To remove the quotes, you can set the configuration parameter [output\_format\_json\_quote\_64bit\_integers](../operations/settings/settings.md#session_settings-output_format_json_quote_64bit_integers) to 0.
443 444 445 446

`rows` – The total number of output rows.

`rows_before_limit_at_least` The minimal number of rows there would have been without LIMIT. Output only if the query contains LIMIT.
447
If the query contains GROUP BY, rows\_before\_limit\_at\_least is the exact number of rows there would have been without a LIMIT.
448 449 450

`totals` – Total values (when using WITH TOTALS).

451
`extremes` – Extreme values (when extremes are set to 1).
452 453

This format is only appropriate for outputting a query result, but not for parsing (retrieving data to insert in a table).
454

455
ClickHouse supports [NULL](../sql-reference/syntax.md), which is displayed as `null` in the JSON output. To enable `+nan`, `-nan`, `+inf`, `-inf` values in output, set the [output\_format\_json\_quote\_denormals](../operations/settings/settings.md#settings-output_format_json_quote_denormals) to 1.
456

457
See also the [JSONEachRow](#jsoneachrow) format.
458

H
hcz 已提交
459
## JSONString {#jsonstring}
460

H
hcz 已提交
461
Differs from JSON only in that data fields are output in strings, not in typed json values.
462 463 464

Example:

H
hcz 已提交
465
```json
466 467 468 469
{
        "meta":
        [
                {
H
hcz 已提交
470
                        "name": "'hello'",
471 472 473
                        "type": "String"
                },
                {
H
hcz 已提交
474
                        "name": "multiply(42, number)",
475
                        "type": "UInt64"
H
hcz 已提交
476 477 478 479
                },
                {
                        "name": "range(5)",
                        "type": "Array(UInt8)"
480 481 482 483 484
                }
        ],

        "data":
        [
H
hcz 已提交
485 486 487 488 489 490 491 492 493 494 495 496 497 498 499
                {
                        "'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]"
                }
500 501
        ],

H
hcz 已提交
502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532
        "rows": 3,

        "rows_before_limit_at_least": 3
}
```

## JSONCompact {#jsoncompact}
## JSONCompactString {#jsoncompactstring}

Differs from JSON only in that data rows are output in arrays, not in objects.

Example:

``` json
// JSONCompact
{
        "meta":
        [
                {
                        "name": "'hello'",
                        "type": "String"
                },
                {
                        "name": "multiply(42, number)",
                        "type": "UInt64"
                },
                {
                        "name": "range(5)",
                        "type": "Array(UInt8)"
                }
        ],
533

H
hcz 已提交
534 535 536 537 538 539
        "data":
        [
                ["hello", "0", [0,1,2,3,4]],
                ["hello", "42", [0,1,2,3,4]],
                ["hello", "84", [0,1,2,3,4]]
        ],
540

H
hcz 已提交
541
        "rows": 3,
542

H
hcz 已提交
543
        "rows_before_limit_at_least": 3
544 545 546
}
```

H
hcz 已提交
547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564
```json
// JSONCompactString
{
        "meta":
        [
                {
                        "name": "'hello'",
                        "type": "String"
                },
                {
                        "name": "multiply(42, number)",
                        "type": "UInt64"
                },
                {
                        "name": "range(5)",
                        "type": "Array(UInt8)"
                }
        ],
565

H
hcz 已提交
566 567 568 569 570 571
        "data":
        [
                ["hello", "0", "[0,1,2,3,4]"],
                ["hello", "42", "[0,1,2,3,4]"],
                ["hello", "84", "[0,1,2,3,4]"]
        ],
H
hcz 已提交
572

H
hcz 已提交
573
        "rows": 3,
H
hcz 已提交
574

H
hcz 已提交
575 576 577
        "rows_before_limit_at_least": 3
}
```
H
hcz 已提交
578

I
Ivan Blinkov 已提交
579
## JSONEachRow {#jsoneachrow}
H
hcz 已提交
580
## JSONStringEachRow {#jsonstringeachrow}
H
hcz 已提交
581
## JSONCompactEachRow {#jsoncompacteachrow}
H
hcz 已提交
582
## JSONCompactStringEachRow {#jsoncompactstringeachrow}
583

H
hcz 已提交
584
When using these formats, ClickHouse outputs rows as separated, newline-delimited JSON values, but the data as a whole is not valid JSON.
585

586
``` json
H
hcz 已提交
587 588
{"some_int":42,"some_str":"hello","some_tuple":[1,"a"]} // JSONEachRow
[42,"hello",[1,"a"]] // JSONCompactEachRow
H
hcz 已提交
589
["42","hello","(2,'a')"] // JSONCompactStringsEachRow
590 591
```

H
hcz 已提交
592
When inserting the data, you should provide a separate JSON value for each row.
593

H
hcz 已提交
594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618
## JSONEachRowWithProgress {#jsoneachrowwithprogress}
## JSONStringEachRowWithProgress {#jsonstringeachrowwithprogress}

Differs from JSONEachRow/JSONStringEachRow in that ClickHouse will also yield progress information as JSON objects.

```json
{"row":{"'hello'":"hello","multiply(42, number)":"0","range(5)":[0,1,2,3,4]}}
{"row":{"'hello'":"hello","multiply(42, number)":"42","range(5)":[0,1,2,3,4]}}
{"row":{"'hello'":"hello","multiply(42, number)":"84","range(5)":[0,1,2,3,4]}}
{"progress":{"read_rows":"3","read_bytes":"24","written_rows":"0","written_bytes":"0","total_rows_to_read":"3"}}
```

## JSONCompactEachRowWithNamesAndTypes {#jsoncompacteachrowwithnamesandtypes}
## JSONCompactStringEachRowWithNamesAndTypes {#jsoncompactstringeachrowwithnamesandtypes}

Differs from JSONCompactEachRow/JSONCompactStringEachRow in that the column names and types are written as the first two rows.

```json
["'hello'", "multiply(42, number)", "range(5)"]
["String", "UInt64", "Array(UInt8)"]
["hello", "0", [0,1,2,3,4]]
["hello", "42", [0,1,2,3,4]]
["hello", "84", [0,1,2,3,4]]
```

619
### Inserting Data {#inserting-data}
620

621
``` sql
622 623 624 625 626
INSERT INTO UserActivity FORMAT JSONEachRow {"PageViews":5, "UserID":"4324182021466249494", "Duration":146,"Sign":-1} {"UserID":"4324182021466249494","PageViews":6,"Duration":185,"Sign":1}
```

ClickHouse allows:

627 628
-   Any order of key-value pairs in the object.
-   Omitting some values.
629

630
ClickHouse ignores spaces between elements and commas after the objects. You can pass all the objects in one line. You don’t have to separate them with line breaks.
631

632
**Omitted values processing**
633

634
ClickHouse substitutes omitted values with the default values for the corresponding [data types](../sql-reference/data-types/index.md).
635

636
If `DEFAULT expr` is specified, ClickHouse uses different substitution rules depending on the [input\_format\_defaults\_for\_omitted\_fields](../operations/settings/settings.md#session_settings-input_format_defaults_for_omitted_fields) setting.
637

638
Consider the following table:
639

640
``` sql
641 642 643 644 645 646 647
CREATE TABLE IF NOT EXISTS example_table
(
    x UInt32,
    a DEFAULT x * 2
) ENGINE = Memory;
```

648 649
-   If `input_format_defaults_for_omitted_fields = 0`, then the default value for `x` and `a` equals `0` (as the default value for the `UInt32` data type).
-   If `input_format_defaults_for_omitted_fields = 1`, then the default value for `x` equals `0`, but the default value of `a` equals `x * 2`.
650

651 652
!!! note "Warning"
    When inserting data with `insert_sample_with_metadata = 1`, ClickHouse consumes more computational resources, compared to insertion with `insert_sample_with_metadata = 0`.
653

654
### Selecting Data {#selecting-data}
655

656
Consider the `UserActivity` table as an example:
657

658
``` text
659 660 661 662 663 664 665 666
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         5 │      146 │   -1 │
│ 4324182021466249494 │         6 │      185 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘
```

The query `SELECT * FROM UserActivity FORMAT JSONEachRow` returns:

667
``` text
668 669 670 671 672 673 674
{"UserID":"4324182021466249494","PageViews":5,"Duration":146,"Sign":-1}
{"UserID":"4324182021466249494","PageViews":6,"Duration":185,"Sign":1}
```

Unlike the [JSON](#json) format, there is no substitution of invalid UTF-8 sequences. Values are escaped in the same way as for `JSON`.

!!! note "Note"
675
    Any set of bytes can be output in the strings. Use the `JSONEachRow` format if you are sure that the data in the table can be formatted as JSON without losing any information.
676

I
Ivan Blinkov 已提交
677
### Usage of Nested Structures {#jsoneachrow-nested}
678

679
If you have a table with [Nested](../sql-reference/data-types/nested-data-structures/nested.md) data type columns, you can insert JSON data with the same structure. Enable this feature with the [input\_format\_import\_nested\_json](../operations/settings/settings.md#settings-input_format_import_nested_json) setting.
680 681 682

For example, consider the following table:

683
``` sql
684 685 686
CREATE TABLE json_each_row_nested (n Nested (s String, i Int32) ) ENGINE = Memory
```

687
As you can see in the `Nested` data type description, ClickHouse treats each component of the nested structure as a separate column (`n.s` and `n.i` for our table). You can insert data in the following way:
688

689
``` sql
690 691 692
INSERT INTO json_each_row_nested FORMAT JSONEachRow {"n.s": ["abc", "def"], "n.i": [1, 23]}
```

693
To insert data as a hierarchical JSON object, set [input\_format\_import\_nested\_json=1](../operations/settings/settings.md#settings-input_format_import_nested_json).
694

695
``` json
696 697 698 699 700 701 702 703
{
    "n": {
        "s": ["abc", "def"],
        "i": [1, 23]
    }
}
```

704
Without this setting, ClickHouse throws an exception.
705

706
``` sql
707 708
SELECT name, value FROM system.settings WHERE name = 'input_format_import_nested_json'
```
709 710

``` text
711 712 713 714
┌─name────────────────────────────┬─value─┐
│ input_format_import_nested_json │ 0     │
└─────────────────────────────────┴───────┘
```
715 716

``` sql
717 718
INSERT INTO json_each_row_nested FORMAT JSONEachRow {"n": {"s": ["abc", "def"], "i": [1, 23]}}
```
719 720

``` text
721 722
Code: 117. DB::Exception: Unknown field found while parsing JSONEachRow format: n: (at row 1)
```
723 724

``` sql
725 726 727 728
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
```
729 730

``` text
731 732 733 734 735
┌─n.s───────────┬─n.i────┐
│ ['abc','def'] │ [1,23] │
└───────────────┴────────┘
```

I
Ivan Blinkov 已提交
736
## Native {#native}
737

738
The most efficient format. Data is written and read by blocks in binary format. For each block, the number of rows, number of columns, column names and types, and parts of columns in this block are recorded one after another. In other words, this format is “columnar” – it doesn’t convert columns to rows. This is the format used in the native interface for interaction between servers, for using the command-line client, and for C++ clients.
739

740
You can use this format to quickly generate dumps that can only be read by the ClickHouse DBMS. It doesn’t make sense to work with this format yourself.
741

I
Ivan Blinkov 已提交
742
## Null {#null}
743

744
Nothing is output. However, the query is processed, and when using the command-line client, data is transmitted to the client. This is used for tests, including performance testing.
745 746
Obviously, this format is only appropriate for output, not for parsing.

I
Ivan Blinkov 已提交
747
## Pretty {#pretty}
748

749
Outputs data as Unicode-art tables, also using ANSI-escape sequences for setting colours in the terminal.
750 751
A full grid of the table is drawn, and each row occupies two lines in the terminal.
Each result block is output as a separate table. This is necessary so that blocks can be output without buffering results (buffering would be necessary in order to pre-calculate the visible width of all the values).
752

753
[NULL](../sql-reference/syntax.md) is output as `ᴺᵁᴸᴸ`.
754

755 756
Example (shown for the [PrettyCompact](#prettycompact) format):

757
``` sql
758 759 760
SELECT * FROM t_null
```

761
``` text
762 763 764 765 766
┌─x─┬────y─┐
│ 1 │ ᴺᵁᴸᴸ │
└───┴──────┘
```

767
Rows are not escaped in Pretty\* formats. Example is shown for the [PrettyCompact](#prettycompact) format:
768

769
``` sql
770 771 772
SELECT 'String with \'quotes\' and \t character' AS Escaping_test
```

773
``` text
774
┌─Escaping_test────────────────────────┐
775
│ String with 'quotes' and      character │
776 777 778
└──────────────────────────────────────┘
```

779
To avoid dumping too much data to the terminal, only the first 10,000 rows are printed. If the number of rows is greater than or equal to 10,000, the message “Showed first 10 000” is printed.
780 781
This format is only appropriate for outputting a query result, but not for parsing (retrieving data to insert in a table).

782
The Pretty format supports outputting total values (when using WITH TOTALS) and extremes (when ‘extremes’ is set to 1). In these cases, total values and extreme values are output after the main data, in separate tables. Example (shown for the [PrettyCompact](#prettycompact) format):
783

784
``` sql
785 786 787
SELECT EventDate, count() AS c FROM test.hits GROUP BY EventDate WITH TOTALS ORDER BY EventDate FORMAT PrettyCompact
```

788
``` text
789 790 791 792 793 794 795 796 797 798 799 800
┌──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─┐
A
Alexey Milovidov 已提交
801
│ 1970-01-01 │ 8873898 │
802 803 804 805 806 807 808 809 810
└────────────┴─────────┘

Extremes:
┌──EventDate─┬───────c─┐
│ 2014-03-17 │ 1031592 │
│ 2014-03-23 │ 1406958 │
└────────────┴─────────┘
```

I
Ivan Blinkov 已提交
811
## PrettyCompact {#prettycompact}
812

813
Differs from [Pretty](#pretty) in that the grid is drawn between rows and the result is more compact.
814 815
This format is used by default in the command-line client in interactive mode.

I
Ivan Blinkov 已提交
816
## PrettyCompactMonoBlock {#prettycompactmonoblock}
817

818
Differs from [PrettyCompact](#prettycompact) in that up to 10,000 rows are buffered, then output as a single table, not by blocks.
819

I
Ivan Blinkov 已提交
820
## PrettyNoEscapes {#prettynoescapes}
821

822
Differs from Pretty in that ANSI-escape sequences aren’t used. This is necessary for displaying this format in a browser, as well as for using the ‘watch’ command-line utility.
823 824 825

Example:

826
``` bash
827
$ watch -n1 "clickhouse-client --query='SELECT event, value FROM system.events FORMAT PrettyCompactNoEscapes'"
828 829 830 831
```

You can use the HTTP interface for displaying in the browser.

832
### PrettyCompactNoEscapes {#prettycompactnoescapes}
833 834 835

The same as the previous setting.

836
### PrettySpaceNoEscapes {#prettyspacenoescapes}
837 838 839

The same as the previous setting.

I
Ivan Blinkov 已提交
840
## PrettySpace {#prettyspace}
841

842
Differs from [PrettyCompact](#prettycompact) in that whitespace (space characters) is used instead of the grid.
843

I
Ivan Blinkov 已提交
844
## RowBinary {#rowbinary}
845 846

Formats and parses data by row in binary format. Rows and values are listed consecutively, without separators.
847
This format is less efficient than the Native format since it is row-based.
848

849
Integers use fixed-length little-endian representation. For example, UInt64 uses 8 bytes.
850 851 852 853 854 855 856
DateTime is represented as UInt32 containing the Unix timestamp as the value.
Date is represented as a UInt16 object that contains the number of days since 1970-01-01 as the value.
String is represented as a varint length (unsigned [LEB128](https://en.wikipedia.org/wiki/LEB128)), followed by the bytes of the string.
FixedString is represented simply as a sequence of bytes.

Array is represented as a varint length (unsigned [LEB128](https://en.wikipedia.org/wiki/LEB128)), followed by successive elements of the array.

857
For [NULL](../sql-reference/syntax.md#null-literal) support, an additional byte containing 1 or 0 is added before each [Nullable](../sql-reference/data-types/nullable.md) value. If 1, then the value is `NULL` and this byte is interpreted as a separate value. If 0, the value after the byte is not `NULL`.
858

I
Ivan Blinkov 已提交
859
## RowBinaryWithNamesAndTypes {#rowbinarywithnamesandtypes}
860 861

Similar to [RowBinary](#rowbinary), but with added header:
D
Denis Zhuravlev 已提交
862

863 864 865
-   [LEB128](https://en.wikipedia.org/wiki/LEB128)-encoded number of columns (N)
-   N `String`s specifying column names
-   N `String`s specifying column types
866

I
Ivan Blinkov 已提交
867
## Values {#data-format-values}
868

869
Prints every row in brackets. Rows are separated by commas. There is no comma after the last row. The values inside the brackets are also comma-separated. Numbers are output in a decimal format without quotes. Arrays are output in square brackets. Strings, dates, and dates with times are output in quotes. Escaping rules and parsing are similar to the [TabSeparated](#tabseparated) format. During formatting, extra spaces aren’t inserted, but during parsing, they are allowed and skipped (except for spaces inside array values, which are not allowed). [NULL](../sql-reference/syntax.md) is represented as `NULL`.
870 871 872 873 874

The minimum set of characters that you need to escape when passing data in Values ​​format: single quotes and backslashes.

This is the format that is used in `INSERT INTO t VALUES ...`, but you can also use it for formatting query results.

875
See also: [input\_format\_values\_interpret\_expressions](../operations/settings/settings.md#settings-input_format_values_interpret_expressions) and [input\_format\_values\_deduce\_templates\_of\_expressions](../operations/settings/settings.md#settings-input_format_values_deduce_templates_of_expressions) settings.
A
Alexander Tokmakov 已提交
876

I
Ivan Blinkov 已提交
877
## Vertical {#vertical}
878

879
Prints each value on a separate line with the column name specified. This format is convenient for printing just one or a few rows if each row consists of a large number of columns.
880

881
[NULL](../sql-reference/syntax.md) is output as `ᴺᵁᴸᴸ`.
882 883 884

Example:

885
``` sql
886 887 888
SELECT * FROM t_null FORMAT Vertical
```

889
``` text
890 891 892 893 894
Row 1:
──────
x: 1
y: ᴺᵁᴸᴸ
```
895

896
Rows are not escaped in Vertical format:
897

898
``` sql
899
SELECT 'string with \'quotes\' and \t with some special \n characters' AS test FORMAT Vertical
900 901
```

902
``` text
903 904
Row 1:
──────
905
test: string with 'quotes' and      with some special
906 907 908
 characters
```

909
This format is only appropriate for outputting a query result, but not for parsing (retrieving data to insert in a table).
910

I
Ivan Blinkov 已提交
911
## VerticalRaw {#verticalraw}
912 913 914

Similar to [Vertical](#vertical), but with escaping disabled. This format is only suitable for outputting query results, not for parsing (receiving data and inserting it in the table).

I
Ivan Blinkov 已提交
915
## XML {#xml}
916 917 918

XML format is suitable only for output, not for parsing. Example:

919
``` xml
920 921 922 923 924 925 926 927 928 929 930 931 932 933 934 935 936 937 938 939 940 941 942 943 944 945 946 947
<?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>yandex</SearchPhrase>
                        <field>1655</field>
                </row>
                <row>
948
                        <SearchPhrase>2014 spring fashion</SearchPhrase>
949 950 951 952 953 954 955 956 957 958 959 960 961 962 963 964 965 966 967
                        <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>
968
                        <SearchPhrase>curtain designs</SearchPhrase>
969 970 971 972 973 974 975 976 977 978 979 980
                        <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>
```

981
If the column name does not have an acceptable format, just ‘field’ is used as the element name. In general, the XML structure follows the JSON structure.
982 983 984 985
Just as for JSON, invalid UTF-8 sequences are changed to the replacement character � so the output text will consist of valid UTF-8 sequences.

In string values, the characters `<` and `&` are escaped as `<` and `&`.

986
Arrays are output as `<array><elem>Hello</elem><elem>World</elem>...</array>`,and tuples as `<tuple><elem>Hello</elem><elem>World</elem>...</tuple>`.
I
Ivan Blinkov 已提交
987

I
Ivan Blinkov 已提交
988
## CapnProto {#capnproto}
I
Ivan Blinkov 已提交
989

990
Cap’n Proto is a binary message format similar to Protocol Buffers and Thrift, but not like JSON or MessagePack.
I
Ivan Blinkov 已提交
991

992
Cap’n Proto messages are strictly typed and not self-describing, meaning they need an external schema description. The schema is applied on the fly and cached for each query.
I
Ivan Blinkov 已提交
993

994
``` bash
995
$ cat capnproto_messages.bin | clickhouse-client --query "INSERT INTO test.hits FORMAT CapnProto SETTINGS format_schema='schema:Message'"
I
Ivan Blinkov 已提交
996 997 998 999
```

Where `schema.capnp` looks like this:

1000
``` capnp
I
Ivan Blinkov 已提交
1001 1002 1003 1004 1005 1006
struct Message {
  SearchPhrase @0 :Text;
  c @1 :Uint64;
}
```

1007
Deserialization is effective and usually doesn’t increase the system load.
I
Ivan Blinkov 已提交
1008

1009 1010
See also [Format Schema](#formatschema).

I
Ivan Blinkov 已提交
1011
## Protobuf {#protobuf}
1012 1013 1014 1015

Protobuf - is a [Protocol Buffers](https://developers.google.com/protocol-buffers/) format.

This format requires an external format schema. The schema is cached between queries.
1016 1017
ClickHouse supports both `proto2` and `proto3` syntaxes. Repeated/optional/required fields are supported.

1018 1019
Usage examples:

1020
``` sql
1021 1022 1023
SELECT * FROM test.table FORMAT Protobuf SETTINGS format_schema = 'schemafile:MessageType'
```

1024
``` bash
1025 1026 1027
cat protobuf_messages.bin | clickhouse-client --query "INSERT INTO test.table FORMAT Protobuf SETTINGS format_schema='schemafile:MessageType'"
```

1028
where the file `schemafile.proto` looks like this:
1029

1030
``` capnp
1031 1032 1033 1034 1035 1036 1037 1038 1039 1040
syntax = "proto3";

message MessageType {
  string name = 1;
  string surname = 2;
  uint32 birthDate = 3;
  repeated string phoneNumbers = 4;
};
```

1041
To find the correspondence between table columns and fields of Protocol Buffers’ message type ClickHouse compares their names.
1042
This comparison is case-insensitive and the characters `_` (underscore) and `.` (dot) are considered as equal.
1043
If types of a column and a field of Protocol Buffers’ message are different the necessary conversion is applied.
1044 1045 1046

Nested messages are supported. For example, for the field `z` in the following message type

1047
``` capnp
1048 1049 1050 1051 1052 1053 1054 1055 1056 1057 1058 1059
message MessageType {
  message XType {
    message YType {
      int32 z;
    };
    repeated YType y;
  };
  XType x;
};
```

ClickHouse tries to find a column named `x.y.z` (or `x_y_z` or `X.y_Z` and so on).
1060
Nested messages are suitable to input or output a [nested data structures](../sql-reference/data-types/nested-data-structures/nested.md).
1061

1062
Default values defined in a protobuf schema like this
1063

1064
``` capnp
1065 1066
syntax = "proto2";

1067 1068 1069 1070 1071
message MessageType {
  optional int32 result_per_page = 3 [default = 10];
}
```

1072
are not applied; the [table defaults](../sql-reference/statements/create/table.md#create-default-values) are used instead of them.
1073

1074 1075 1076 1077
ClickHouse inputs and outputs protobuf messages in the `length-delimited` format.
It means before every message should be written its length as a [varint](https://developers.google.com/protocol-buffers/docs/encoding#varints).
See also [how to read/write length-delimited protobuf messages in popular languages](https://cwiki.apache.org/confluence/display/GEODE/Delimiting+Protobuf+Messages).

I
Ivan Blinkov 已提交
1078
## Avro {#data-format-avro}
A
Andrew Onyshchuk 已提交
1079

H
hcz 已提交
1080
[Apache Avro](https://avro.apache.org/) is a row-oriented data serialization framework developed within Apache’s Hadoop project.
A
Andrew Onyshchuk 已提交
1081

H
hcz 已提交
1082
ClickHouse Avro format supports reading and writing [Avro data files](https://avro.apache.org/docs/current/spec.html#Object+Container+Files).
A
Andrew Onyshchuk 已提交
1083

1084
### Data Types Matching {#data_types-matching}
A
Andrew Onyshchuk 已提交
1085

1086
The table below shows supported data types and how they match ClickHouse [data types](../sql-reference/data-types/index.md) in `INSERT` and `SELECT` queries.
A
Andrew Onyshchuk 已提交
1087

1088 1089
| Avro data type `INSERT`                     | ClickHouse data type                                                                                                  | Avro data type `SELECT`      |
|---------------------------------------------|-----------------------------------------------------------------------------------------------------------------------|------------------------------|
1090 1091 1092 1093 1094 1095 1096 1097 1098 1099 1100 1101 1102
| `boolean`, `int`, `long`, `float`, `double` | [Int(8\|16\|32)](../sql-reference/data-types/int-uint.md), [UInt(8\|16\|32)](../sql-reference/data-types/int-uint.md) | `int`                        |
| `boolean`, `int`, `long`, `float`, `double` | [Int64](../sql-reference/data-types/int-uint.md), [UInt64](../sql-reference/data-types/int-uint.md)                   | `long`                       |
| `boolean`, `int`, `long`, `float`, `double` | [Float32](../sql-reference/data-types/float.md)                                                                       | `float`                      |
| `boolean`, `int`, `long`, `float`, `double` | [Float64](../sql-reference/data-types/float.md)                                                                       | `double`                     |
| `bytes`, `string`, `fixed`, `enum`          | [String](../sql-reference/data-types/string.md)                                                                       | `bytes`                      |
| `bytes`, `string`, `fixed`                  | [FixedString(N)](../sql-reference/data-types/fixedstring.md)                                                          | `fixed(N)`                   |
| `enum`                                      | [Enum(8\|16)](../sql-reference/data-types/enum.md)                                                                    | `enum`                       |
| `array(T)`                                  | [Array(T)](../sql-reference/data-types/array.md)                                                                      | `array(T)`                   |
| `union(null, T)`, `union(T, null)`          | [Nullable(T)](../sql-reference/data-types/date.md)                                                                    | `union(null, T)`             |
| `null`                                      | [Nullable(Nothing)](../sql-reference/data-types/special-data-types/nothing.md)                                        | `null`                       |
| `int (date)` \*                             | [Date](../sql-reference/data-types/date.md)                                                                           | `int (date)` \*              |
| `long (timestamp-millis)` \*                | [DateTime64(3)](../sql-reference/data-types/datetime.md)                                                              | `long (timestamp-millis)` \* |
| `long (timestamp-micros)` \*                | [DateTime64(6)](../sql-reference/data-types/datetime.md)                                                              | `long (timestamp-micros)` \* |
A
Andrew Onyshchuk 已提交
1103

H
hcz 已提交
1104
\* [Avro logical types](https://avro.apache.org/docs/current/spec.html#Logical+Types)
A
Andrew Onyshchuk 已提交
1105 1106 1107

Unsupported Avro data types: `record` (non-root), `map`

1108
Unsupported Avro logical data types: `time-millis`, `time-micros`, `duration`
A
Andrew Onyshchuk 已提交
1109

1110
### Inserting Data {#inserting-data-1}
A
Andrew Onyshchuk 已提交
1111 1112 1113

To insert data from an Avro file into ClickHouse table:

1114
``` bash
A
Andrew Onyshchuk 已提交
1115 1116 1117 1118 1119
$ cat file.avro | clickhouse-client --query="INSERT INTO {some_table} FORMAT Avro"
```

The root schema of input Avro file must be of `record` type.

1120
To find the correspondence between table columns and fields of Avro schema ClickHouse compares their names. This comparison is case-sensitive.
A
Andrew Onyshchuk 已提交
1121 1122
Unused fields are skipped.

1123
Data types of ClickHouse table columns can differ from the corresponding fields of the Avro data inserted. When inserting data, ClickHouse interprets data types according to the table above and then [casts](../sql-reference/functions/type-conversion-functions.md#type_conversion_function-cast) the data to corresponding column type.
A
Andrew Onyshchuk 已提交
1124

1125
### Selecting Data {#selecting-data-1}
A
Andrew Onyshchuk 已提交
1126 1127 1128

To select data from ClickHouse table into an Avro file:

1129
``` bash
A
Andrew Onyshchuk 已提交
1130 1131 1132 1133 1134
$ clickhouse-client --query="SELECT * FROM {some_table} FORMAT Avro" > file.avro
```

Column names must:

1135 1136
-   start with `[A-Za-z_]`
-   subsequently contain only `[A-Za-z0-9_]`
A
Andrew Onyshchuk 已提交
1137

1138
Output Avro file compression and sync interval can be configured with [output\_format\_avro\_codec](../operations/settings/settings.md#settings-output_format_avro_codec) and [output\_format\_avro\_sync\_interval](../operations/settings/settings.md#settings-output_format_avro_sync_interval) respectively.
A
Andrew Onyshchuk 已提交
1139

I
Ivan Blinkov 已提交
1140
## AvroConfluent {#data-format-avro-confluent}
A
Andrew Onyshchuk 已提交
1141 1142 1143 1144 1145 1146 1147

AvroConfluent supports decoding single-object Avro messages commonly used with [Kafka](https://kafka.apache.org/) and [Confluent Schema Registry](https://docs.confluent.io/current/schema-registry/index.html).

Each Avro message embeds a schema id that can be resolved to the actual schema with help of the Schema Registry.

Schemas are cached once resolved.

1148
Schema Registry URL is configured with [format\_avro\_schema\_registry\_url](../operations/settings/settings.md#format_avro_schema_registry_url).
A
Andrew Onyshchuk 已提交
1149

1150
### Data Types Matching {#data_types-matching-1}
A
Andrew Onyshchuk 已提交
1151

1152
Same as [Avro](#data-format-avro).
A
Andrew Onyshchuk 已提交
1153

1154
### Usage {#usage}
A
Andrew Onyshchuk 已提交
1155

I
Ivan Blinkov 已提交
1156
To quickly verify schema resolution you can use [kafkacat](https://github.com/edenhill/kafkacat) with [clickhouse-local](../operations/utilities/clickhouse-local.md):
A
Andrew Onyshchuk 已提交
1157

1158
``` bash
A
Andrew Onyshchuk 已提交
1159 1160 1161 1162 1163 1164
$ 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
```

1165
To use `AvroConfluent` with [Kafka](../engines/table-engines/integrations/kafka.md):
1166 1167

``` sql
A
Andrew Onyshchuk 已提交
1168 1169 1170 1171 1172 1173 1174 1175 1176 1177 1178 1179 1180 1181 1182 1183 1184 1185
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';

SET format_avro_schema_registry_url = 'http://schema-registry';

SELECT * FROM topic1_stream;
```

!!! note "Warning"
1186
    Setting `format_avro_schema_registry_url` needs to be configured in `users.xml` to maintain it’s value after a restart. Also you can use the `format_avro_schema_registry_url` setting of the `Kafka` table engine.
A
Andrew Onyshchuk 已提交
1187

I
Ivan Blinkov 已提交
1188
## Parquet {#data-format-parquet}
1189

H
hcz 已提交
1190
[Apache Parquet](https://parquet.apache.org/) is a columnar storage format widespread in the Hadoop ecosystem. ClickHouse supports read and write operations for this format.
1191

1192
### Data Types Matching {#data_types-matching-2}
1193

1194
The table below shows supported data types and how they match ClickHouse [data types](../sql-reference/data-types/index.md) in `INSERT` and `SELECT` queries.
1195

1196 1197
| Parquet data type (`INSERT`) | ClickHouse data type                                      | Parquet data type (`SELECT`) |
|------------------------------|-----------------------------------------------------------|------------------------------|
1198 1199 1200 1201 1202 1203 1204 1205 1206 1207 1208 1209 1210 1211 1212
| `UINT8`, `BOOL`              | [UInt8](../sql-reference/data-types/int-uint.md)          | `UINT8`                      |
| `INT8`                       | [Int8](../sql-reference/data-types/int-uint.md)           | `INT8`                       |
| `UINT16`                     | [UInt16](../sql-reference/data-types/int-uint.md)         | `UINT16`                     |
| `INT16`                      | [Int16](../sql-reference/data-types/int-uint.md)          | `INT16`                      |
| `UINT32`                     | [UInt32](../sql-reference/data-types/int-uint.md)         | `UINT32`                     |
| `INT32`                      | [Int32](../sql-reference/data-types/int-uint.md)          | `INT32`                      |
| `UINT64`                     | [UInt64](../sql-reference/data-types/int-uint.md)         | `UINT64`                     |
| `INT64`                      | [Int64](../sql-reference/data-types/int-uint.md)          | `INT64`                      |
| `FLOAT`, `HALF_FLOAT`        | [Float32](../sql-reference/data-types/float.md)           | `FLOAT`                      |
| `DOUBLE`                     | [Float64](../sql-reference/data-types/float.md)           | `DOUBLE`                     |
| `DATE32`                     | [Date](../sql-reference/data-types/date.md)               | `UINT16`                     |
| `DATE64`, `TIMESTAMP`        | [DateTime](../sql-reference/data-types/datetime.md)       | `UINT32`                     |
| `STRING`, `BINARY`           | [String](../sql-reference/data-types/string.md)           | `STRING`                     |
| —                            | [FixedString](../sql-reference/data-types/fixedstring.md) | `STRING`                     |
| `DECIMAL`                    | [Decimal](../sql-reference/data-types/decimal.md)         | `DECIMAL`                    |
1213

1214
ClickHouse supports configurable precision of `Decimal` type. The `INSERT` query treats the Parquet `DECIMAL` type as the ClickHouse `Decimal128` type.
1215

1216 1217
Unsupported Parquet data types: `DATE32`, `TIME32`, `FIXED_SIZE_BINARY`, `JSON`, `UUID`, `ENUM`.

1218
Data types of ClickHouse table columns can differ from the corresponding fields of the Parquet data inserted. When inserting data, ClickHouse interprets data types according to the table above and then [cast](../query_language/functions/type_conversion_functions/#type_conversion_function-cast) the data to that data type which is set for the ClickHouse table column.
1219

1220
### Inserting and Selecting Data {#inserting-and-selecting-data}
1221

A
alexey-milovidov 已提交
1222
You can insert Parquet data from a file into ClickHouse table by the following command:
1223

1224
``` bash
1225
$ cat {filename} | clickhouse-client --query="INSERT INTO {some_table} FORMAT Parquet"
1226 1227 1228 1229
```

You can select data from a ClickHouse table and save them into some file in the Parquet format by the following command:

1230
``` bash
1231 1232 1233
$ clickhouse-client --query="SELECT * FROM {some_table} FORMAT Parquet" > {some_file.pq}
```

1234
To exchange data with Hadoop, you can use [HDFS table engine](../engines/table-engines/integrations/hdfs.md).
1235

A
Alexander Kuzmenkov 已提交
1236
## Arrow {#data-format-arrow}
H
hcz 已提交
1237 1238 1239

[Apache Arrow](https://arrow.apache.org/) comes with two built-in columnar storage formats. ClickHouse supports read and write operations for these formats.

1240
`Arrow` is Apache Arrow’s “file mode” format. It is designed for in-memory random access.
H
hcz 已提交
1241

A
Alexander Kuzmenkov 已提交
1242
## ArrowStream {#data-format-arrow-stream}
H
hcz 已提交
1243

1244
`ArrowStream` is Apache Arrow’s “stream mode” format. It is designed for in-memory stream processing.
H
hcz 已提交
1245

I
Ivan Blinkov 已提交
1246
## ORC {#data-format-orc}
1247

1248
[Apache ORC](https://orc.apache.org/) is a columnar storage format widespread in the Hadoop ecosystem. You can only insert data in this format to ClickHouse.
1249

1250
### Data Types Matching {#data_types-matching-3}
1251

1252
The table below shows supported data types and how they match ClickHouse [data types](../sql-reference/data-types/index.md) in `INSERT` queries.
1253

1254 1255
| ORC data type (`INSERT`) | ClickHouse data type                                |
|--------------------------|-----------------------------------------------------|
1256 1257 1258 1259 1260 1261 1262 1263 1264 1265 1266 1267 1268 1269
| `UINT8`, `BOOL`          | [UInt8](../sql-reference/data-types/int-uint.md)    |
| `INT8`                   | [Int8](../sql-reference/data-types/int-uint.md)     |
| `UINT16`                 | [UInt16](../sql-reference/data-types/int-uint.md)   |
| `INT16`                  | [Int16](../sql-reference/data-types/int-uint.md)    |
| `UINT32`                 | [UInt32](../sql-reference/data-types/int-uint.md)   |
| `INT32`                  | [Int32](../sql-reference/data-types/int-uint.md)    |
| `UINT64`                 | [UInt64](../sql-reference/data-types/int-uint.md)   |
| `INT64`                  | [Int64](../sql-reference/data-types/int-uint.md)    |
| `FLOAT`, `HALF_FLOAT`    | [Float32](../sql-reference/data-types/float.md)     |
| `DOUBLE`                 | [Float64](../sql-reference/data-types/float.md)     |
| `DATE32`                 | [Date](../sql-reference/data-types/date.md)         |
| `DATE64`, `TIMESTAMP`    | [DateTime](../sql-reference/data-types/datetime.md) |
| `STRING`, `BINARY`       | [String](../sql-reference/data-types/string.md)     |
| `DECIMAL`                | [Decimal](../sql-reference/data-types/decimal.md)   |
1270

1271
ClickHouse supports configurable precision of the `Decimal` type. The `INSERT` query treats the ORC `DECIMAL` type as the ClickHouse `Decimal128` type.
1272 1273 1274

Unsupported ORC data types: `DATE32`, `TIME32`, `FIXED_SIZE_BINARY`, `JSON`, `UUID`, `ENUM`.

1275
The data types of ClickHouse table columns don’t have to match the corresponding ORC data fields. When inserting data, ClickHouse interprets data types according to the table above and then [casts](../sql-reference/functions/type-conversion-functions.md#type_conversion_function-cast) the data to the data type set for the ClickHouse table column.
1276

1277
### Inserting Data {#inserting-data-2}
1278

1279
You can insert ORC data from a file into ClickHouse table by the following command:
1280

1281
``` bash
1282
$ cat filename.orc | clickhouse-client --query="INSERT INTO some_table FORMAT ORC"
1283
```
1284

1285
To exchange data with Hadoop, you can use [HDFS table engine](../engines/table-engines/integrations/hdfs.md).
A
alexey-milovidov 已提交
1286

I
Ivan Blinkov 已提交
1287
## Format Schema {#formatschema}
1288 1289

The file name containing the format schema is set by the setting `format_schema`.
1290
It’s required to set this setting when it is used one of the formats `Cap'n Proto` and `Protobuf`.
1291
The format schema is a combination of a file name and the name of a message type in this file, delimited by a colon,
1292
e.g. `schemafile.proto:MessageType`.
1293
If the file has the standard extension for the format (for example, `.proto` for `Protobuf`),
1294
it can be omitted and in this case, the format schema looks like `schemafile:MessageType`.
1295

1296
If you input or output data via the [client](../interfaces/cli.md) in the [interactive mode](../interfaces/cli.md#cli_usage), the file name specified in the format schema
1297
can contain an absolute path or a path relative to the current directory on the client.
1298 1299 1300
If you use the client in the [batch mode](../interfaces/cli.md#cli_usage), the path to the schema must be relative due to security reasons.

If you input or output data via the [HTTP interface](../interfaces/http.md) the file name specified in the format schema
1301
should be located in the directory specified in [format\_schema\_path](../operations/server-configuration-parameters/settings.md#server_configuration_parameters-format_schema_path)
1302 1303
in the server configuration.

I
Ivan Blinkov 已提交
1304
## Skipping Errors {#skippingerrors}
A
Alexander Tokmakov 已提交
1305

1306 1307
Some formats such as `CSV`, `TabSeparated`, `TSKV`, `JSONEachRow`, `Template`, `CustomSeparated` and `Protobuf` can skip broken row if parsing error occurred and continue parsing from the beginning of next row. See [input\_format\_allow\_errors\_num](../operations/settings/settings.md#settings-input_format_allow_errors_num) and
[input\_format\_allow\_errors\_ratio](../operations/settings/settings.md#settings-input_format_allow_errors_ratio) settings.
A
Alexander Tokmakov 已提交
1308
Limitations:
1309 1310
- In case of parsing error `JSONEachRow` skips all data until the new line (or EOF), so rows must be delimited by `\n` to count errors correctly.
- `Template` and `CustomSeparated` use delimiter after the last column and delimiter between rows to find the beginning of next row, so skipping errors works only if at least one of them is not empty.
1311 1312

[Original article](https://clickhouse.tech/docs/en/interfaces/formats/) <!--hide-->