处理其他格式

之前加载 JSON 数据的示例假设使用 JSONEachRow (ndjson)。我们在下面提供了加载其他常用格式 JSON 的示例。

JSON 对象数组

JSON 数据最流行的形式之一是在 JSON 数组中包含一个 JSON 对象列表，例如此示例

> cat list.json
[
  {
    "path": "Akiba_Hebrew_Academy",
    "month": "2017-08-01",
    "hits": 241
  },
  {
    "path": "Aegithina_tiphia",
    "month": "2018-02-01",
    "hits": 34
  },
  ...
]

让我们为此类数据创建一个表

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

要导入 JSON 对象列表，我们可以使用 JSONEachRow 格式（从 list.json 文件插入数据）

INSERT INTO sometable
FROM INFILE 'list.json'
FORMAT JSONEachRow

我们使用了 FROM INFILE 子句从本地文件加载数据，我们可以看到导入成功了

SELECT *
FROM sometable

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

处理 NDJSON（行分隔 JSON）

许多应用程序可以以 JSON 格式记录数据，以便每条日志行都是一个独立的 JSON 对象，例如此文件

cat object-per-line.json

{"path":"1-krona","month":"2017-01-01","hits":4}
{"path":"Ahmadabad-e_Kalij-e_Sofla","month":"2017-01-01","hits":3}
{"path":"Bob_Dolman","month":"2016-11-01","hits":245}

相同的 JSONEachRow 格式能够处理此类文件

INSERT INTO sometable FROM INFILE 'object-per-line.json' FORMAT JSONEachRow;
SELECT * FROM sometable;

┌─path──────────────────────┬──────month─┬─hits─┐
│ Bob_Dolman                │ 2016-11-01 │  245 │
│ 1-krona                   │ 2017-01-01 │    4 │
│ Ahmadabad-e_Kalij-e_Sofla │ 2017-01-01 │    3 │
└───────────────────────────┴────────────┴──────┘

JSON 对象键

在某些情况下，JSON 对象列表可以编码为对象属性而不是数组元素（请参阅 objects.json 作为示例）

cat objects.json

{
  "a": {
    "path":"April_25,_2017",
    "month":"2018-01-01",
    "hits":2
  },
  "b": {
    "path":"Akahori_Station",
    "month":"2016-06-01",
    "hits":11
  },
  ...
}

ClickHouse 可以使用 JSONObjectEachRow 格式从此类数据加载数据

INSERT INTO sometable FROM INFILE 'objects.json' FORMAT JSONObjectEachRow;
SELECT * FROM sometable;

┌─path────────────┬──────month─┬─hits─┐
│ Abducens_palsy  │ 2016-05-01 │   28 │
│ Akahori_Station │ 2016-06-01 │   11 │
│ April_25,_2017  │ 2018-01-01 │    2 │
└─────────────────┴────────────┴──────┘

指定父对象键值

假设我们也想要将父对象键中的值保存到表中。在这种情况下，我们可以使用以下选项来定义我们要将键值保存到的列的名称

SET format_json_object_each_row_column_for_object_name = 'id'

现在，我们可以使用 file() 函数检查将从原始 JSON 文件中加载哪些数据

SELECT * FROM file('objects.json', JSONObjectEachRow)

┌─id─┬─path────────────┬──────month─┬─hits─┐
│ a  │ April_25,_2017  │ 2018-01-01 │    2 │
│ b  │ Akahori_Station │ 2016-06-01 │   11 │
│ c  │ Abducens_palsy  │ 2016-05-01 │   28 │
└────┴─────────────────┴────────────┴──────┘

请注意，id 列已正确填充了键值。

JSON 数组

有时，为了节省空间，JSON 文件以数组而不是对象编码。在这种情况下，我们处理一个 JSON 数组列表

cat arrays.json

["Akiba_Hebrew_Academy", "2017-08-01", 241],
["Aegithina_tiphia", "2018-02-01", 34],
["1971-72_Utah_Stars_season", "2016-10-01", 1]

在这种情况下，ClickHouse 将加载此数据并将每个值根据其在数组中的顺序分配给相应的列。为此，我们使用 JSONCompactEachRow 格式

SELECT * FROM sometable

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

从 JSON 数组导入单个列

在某些情况下，数据可以按列而不是按行编码。在这种情况下，父 JSON 对象包含具有值的列。请查看以下文件

cat columns.json

{
  "path": ["2007_Copa_America", "Car_dealerships_in_the_USA", "Dihydromyricetin_reductase"],
  "month": ["2016-07-01", "2015-07-01", "2015-07-01"],
  "hits": [178, 11, 1]
}

ClickHouse 使用 JSONColumns 格式来解析以这种格式格式化的数据

SELECT * FROM file('columns.json', JSONColumns)

┌─path───────────────────────┬──────month─┬─hits─┐
│ 2007_Copa_America          │ 2016-07-01 │  178 │
│ Car_dealerships_in_the_USA │ 2015-07-01 │   11 │
│ Dihydromyricetin_reductase │ 2015-07-01 │    1 │
└────────────────────────────┴────────────┴──────┘

当处理列数组而不是使用 JSONCompactColumns 格式的对象时，也支持更紧凑的格式

SELECT * FROM file('columns-array.json', JSONCompactColumns)

┌─c1──────────────┬─────────c2─┬─c3─┐
│ Heidenrod       │ 2017-01-01 │ 10 │
│ Arthur_Henrique │ 2016-11-01 │ 12 │
│ Alan_Ebnother   │ 2015-11-01 │ 66 │
└─────────────────┴────────────┴────┘

保存 JSON 对象而不是解析

在某些情况下，您可能想要将 JSON 对象保存到单个 String（或 JSON）列中，而不是解析它。这在处理具有不同结构的 JSON 对象列表时很有用。让我们以此文件为例，我们在此文件中父列表中包含多个不同的 JSON 对象

cat custom.json

[
  {"name": "Joe", "age": 99, "type": "person"},
  {"url": "/my.post.MD", "hits": 1263, "type": "post"},
  {"message": "Warning on disk usage", "type": "log"}
]

我们想要将原始 JSON 对象保存到以下表中

CREATE TABLE events
(
    `data` String
)
ENGINE = MergeTree
ORDER BY ()

现在，我们可以使用 JSONAsString 格式将数据从文件加载到此表中，以保留 JSON 对象而不是解析它们

INSERT INTO events (data)
FROM INFILE 'custom.json'
FORMAT JSONAsString

我们可以使用 JSON 函数查询保存的对象

SELECT
    JSONExtractString(data, 'type') AS type,
    data
FROM events

┌─type───┬─data─────────────────────────────────────────────────┐
│ person │ {"name": "Joe", "age": 99, "type": "person"}         │
│ post   │ {"url": "/my.post.MD", "hits": 1263, "type": "post"} │
│ log    │ {"message": "Warning on disk usage", "type": "log"}  │
└────────┴──────────────────────────────────────────────────────┘

请注意，JSONAsString 在我们拥有 JSON 对象每行格式的文件（通常与 JSONEachRow 格式一起使用）的情况下也能正常工作。

嵌套对象的模式

在处理嵌套 JSON 对象的情况下，我们可以额外定义模式并使用复杂类型（Array、Object Data Type 或 Tuple）来加载数据

SELECT *
FROM file('list-nested.json', JSONEachRow, 'page Tuple(path String, title String, owner_id UInt16), month Date, hits UInt32')
LIMIT 1

┌─page───────────────────────────────────────────────┬──────month─┬─hits─┐
│ ('Akiba_Hebrew_Academy','Akiba Hebrew Academy',12) │ 2017-08-01 │  241 │
└────────────────────────────────────────────────────┴────────────┴──────┘

访问嵌套 JSON 对象

我们可以通过启用嵌套 JSON 键以下设置选项来引用它们

SET input_format_import_nested_json = 1

这使我们能够使用点表示法引用嵌套 JSON 对象键（请记住，要使用反引号将它们括起来才能正常工作）

SELECT *
FROM file('list-nested.json', JSONEachRow, '`page.owner_id` UInt32, `page.title` String, month Date, hits UInt32')
LIMIT 1

┌─page.owner_id─┬─page.title───────────┬──────month─┬─hits─┐
│            12 │ Akiba Hebrew Academy │ 2017-08-01 │  241 │
└───────────────┴──────────────────────┴────────────┴──────┘

通过这种方式，我们可以展平嵌套 JSON 对象或使用一些嵌套值将它们保存为单独的列。

跳过未知列

默认情况下，ClickHouse 在导入 JSON 数据时会忽略未知列。让我们尝试将原始文件导入到没有 month 列的表中

CREATE TABLE shorttable
(
    `path` String,
    `hits` UInt32
)
ENGINE = MergeTree
ORDER BY path

我们仍然可以将原始 JSON 数据（包含 3 列）插入到此表中

INSERT INTO shorttable FROM INFILE 'list.json' FORMAT JSONEachRow;
SELECT * FROM shorttable

┌─path──────────────────────┬─hits─┐
│ 1971-72_Utah_Stars_season │    1 │
│ Aegithina_tiphia          │   34 │
│ Akiba_Hebrew_Academy      │  241 │
└───────────────────────────┴──────┘

ClickHouse 在导入时会忽略未知列。可以使用 input_format_skip_unknown_fields 设置选项禁用此功能

SET input_format_skip_unknown_fields = 0;
INSERT INTO shorttable FROM INFILE 'list.json' FORMAT JSONEachRow;

Ok.
Exception on client:
Code: 117. DB::Exception: Unknown field found while parsing JSONEachRow format: month: (in file/uri /data/clickhouse/user_files/list.json): (at row 1)

在 JSON 和表列结构不一致的情况下，ClickHouse 会抛出异常。

BSON

ClickHouse 允许将数据导出到 BSON 编码文件并从这些文件导入数据。此格式由某些 DBMS 使用，例如 MongoDB 数据库。

要导入 BSON 数据，我们使用 BSONEachRow 格式。让我们从此 BSON 文件导入数据

SELECT * FROM file('data.bson', BSONEachRow)

┌─path──────────────────────┬─month─┬─hits─┐
│ Bob_Dolman                │ 17106 │  245 │
│ 1-krona                   │ 17167 │    4 │
│ Ahmadabad-e_Kalij-e_Sofla │ 17167 │    3 │
└───────────────────────────┴───────┴──────┘

我们也可以使用相同的格式导出到 BSON 文件

SELECT *
FROM sometable
INTO OUTFILE 'out.bson'
FORMAT BSONEachRow

之后，我们的数据将导出到 out.bson 文件中。

处理其他格式

JSON 对象数组​

处理 NDJSON（行分隔 JSON）​

JSON 对象键​

指定父对象键值​

JSON 数组​

从 JSON 数组导入单个列​

保存 JSON 对象而不是解析​

嵌套对象的模式​

访问嵌套 JSON 对象​

跳过未知列​

BSON​