MongoDB 表引擎

MongoDB 引擎是只读表引擎，可用于从远程集合中读取数据。

MongoDB 引擎是只读表引擎，可用于从远程 MongoDB 集合中读取数据。

仅支持 MongoDB v3.6+ 服务器。 尚不支持 Seed list(mongodb+srv)。

​

创建表

CREATE TABLE [IF NOT EXISTS] [db.]table_name
(
    name1 [type1],
    name2 [type2],
    ...
) ENGINE = MongoDB(host:port, database, collection, user, password[, options[, oid_columns]]);

引擎参数

Parameter	Description
host:port	MongoDB 服务器地址。
database	远程数据库名称。
collection	远程集合名称。
user	MongoDB 用户。
password	用户密码。
options	可选。MongoDB 连接字符串 options，采用 URL 格式字符串。例如：'authSource=admin&ssl=true'
oid_columns	在 WHERE 子句中应视为 oid 的列的逗号分隔列表。默认为 _id。

如果你使用的是 MongoDB Atlas 云服务，可从 ‘Atlas SQL’ 选项获取连接 URL。 目前尚不支持 Seed list (mongodb**+srv**) ，但将在未来的发行版中加入。

或者，你也可以传入一个 URI：

ENGINE = MongoDB(uri, collection[, oid_columns]);

引擎参数

参数	描述
uri	MongoDB 服务器的连接 URI。
collection	远程集合名称。
oid_columns	在 WHERE 子句中应视为 oid 的列列表，以逗号分隔。默认为 _id。

​

类型映射

MongoDB	ClickHouse
bool, int32, int64	除 Decimal 外的任意数值类型、Boolean、String
double	Float64、String
date	Date、Date32、DateTime、DateTime64、String
string	String、如果格式正确，也可以是任意数值类型 (Decimal 除外)
document	String (作为 JSON)
array	Array、String (作为 JSON)
oid	String
binary	在列中为 String，在数组或文档中为 base64 编码字符串
uuid (binary subtype 4)	UUID
任何其他类型	String

如果在 MongoDB 文档中找不到该键 (例如列名不匹配) ，则会插入默认值或 NULL (如果该列为 Nullable) 。

​

OID

如果你希望在 WHERE 子句中将 String 按 oid 处理，只需将该列的名称放在表引擎的最后一个参数中。 当按 _id 列查询记录时，可能需要这样做，因为在 MongoDB 中，该列默认是 oid 类型。 如果表中的 _id 字段是其他类型，例如 uuid，则需要将 oid_columns 指定为空；否则会使用该参数的默认值 _id。

db.sample_oid.insertMany([
    {"another_oid_column": ObjectId()},
]);

db.sample_oid.find();
[
    {
        "_id": {"$oid": "67bf6cc44ebc466d33d42fb2"},
        "another_oid_column": {"$oid": "67bf6cc40000000000ea41b1"}
    }
]

默认情况下，只有 _id 会被视为 oid 列。

CREATE TABLE sample_oid
(
    _id String,
    another_oid_column String
) ENGINE = MongoDB('mongodb://user:pass@host/db', 'sample_oid');

SELECT count() FROM sample_oid WHERE _id = '67bf6cc44ebc466d33d42fb2'; --将输出 1。
SELECT count() FROM sample_oid WHERE another_oid_column = '67bf6cc40000000000ea41b1'; --将输出 0

在这种情况下，输出将为 0，因为 ClickHouse 还不知道 another_oid_column 的类型是 oid，所以我们来修正一下：

CREATE TABLE sample_oid
(
    _id String,
    another_oid_column String
) ENGINE = MongoDB('mongodb://user:pass@host/db', 'sample_oid', '_id,another_oid_column');

-- 或

CREATE TABLE sample_oid
(
    _id String,
    another_oid_column String
) ENGINE = MongoDB('host', 'db', 'sample_oid', 'user', 'pass', '', '_id,another_oid_column');

SELECT count() FROM sample_oid WHERE another_oid_column = '67bf6cc40000000000ea41b1'; -- 现在将输出 1

​

支持的子句

仅支持包含简单表达式的查询 (例如，WHERE field = <constant> ORDER BY field2 LIMIT <constant>) 。 这类表达式会被转换为 MongoDB 查询语言，并在服务器端执行。 你可以使用 mongodb_throw_on_unsupported_query 关闭这些限制。 在这种情况下，ClickHouse 会尽力转换查询，但这可能导致全表扫描，并在 ClickHouse 端处理。

最好始终显式指定字面量的类型，因为 Mongo 对过滤器的类型要求非常严格。 例如，如果你想按 Date 过滤：

SELECT * FROM mongo_table WHERE date = '2024-01-01'

这将不起作用，因为 Mongo 不会将字符串转换为 Date，因此你需要手动进行类型转换：

SELECT * FROM mongo_table WHERE date = '2024-01-01'::Date OR date = toDate('2024-01-01')

这适用于 Date、Date32、DateTime、Bool、UUID。

​

使用示例

假设 MongoDB 中已加载 sample_mflix 示例数据集

在 ClickHouse 中创建一个表，以从 MongoDB 集合读取数据：

Query

CREATE TABLE sample_mflix_table
(
    _id String,
    title String,
    plot String,
    genres Array(String),
    directors Array(String),
    writers Array(String),
    released Date,
    imdb String,
    year String
) ENGINE = MongoDB('mongodb://<USERNAME>:<PASSWORD>@atlas-sql-6634be87cefd3876070caf96-98lxs.a.query.mongodb.net/sample_mflix?ssl=true&authSource=admin', 'movies');

Query

SELECT count() FROM sample_mflix_table

Response

   ┌─count()─┐
1. │   21349 │
   └─────────┘

Query

-- JSONExtractString 无法下推到 MongoDB
SET mongodb_throw_on_unsupported_query = 0;

-- 查找所有评分 > 7.5 的《回到未来》续集
SELECT title, plot, genres, directors, released FROM sample_mflix_table
WHERE title IN ('Back to the Future', 'Back to the Future Part II', 'Back to the Future Part III')
    AND toFloat32(JSONExtractString(imdb, 'rating')) > 7.5
ORDER BY year
FORMAT Vertical;

Response

Row 1:
──────
title:     Back to the Future
plot:      A young man is accidentally sent 30 years into the past in a time-traveling DeLorean invented by his friend, Dr. Emmett Brown, and must make sure his high-school-age parents unite in order to save his own existence.
genres:    ['Adventure','Comedy','Sci-Fi']
directors: ['Robert Zemeckis']
released:  1985-07-03

Row 2:
──────
title:     Back to the Future Part II
plot:      After visiting 2015, Marty McFly must repeat his visit to 1955 to prevent disastrous changes to 1985... without interfering with his first trip.
genres:    ['Action','Adventure','Comedy']
directors: ['Robert Zemeckis']
released:  1989-11-22

Query

-- 查找改编自 Cormac McCarthy 著作的评分前 3 部电影
SELECT title, toFloat32(JSONExtractString(imdb, 'rating')) AS rating
FROM sample_mflix_table
WHERE arrayExists(x -> x LIKE 'Cormac McCarthy%', writers)
ORDER BY rating DESC
LIMIT 3;

Response

   ┌─title──────────────────┬─rating─┐
1. │ No Country for Old Men │    8.1 │
2. │ The Sunset Limited     │    7.4 │
3. │ The Road               │    7.3 │
   └────────────────────────┴────────┘

​

故障排查

你可以在 DEBUG 级别的日志中查看生成的 MongoDB 查询。

具体实现细节可参见 mongocxx 和 mongoc 的文档。