Движок таблицы 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]]);

Параметры движка

Параметр	Описание
host:port	Адрес сервера MongoDB.
database	Имя удалённой базы данных.
collection	Имя удалённой коллекции.
user	Пользователь MongoDB.
password	Пароль пользователя.
options	Необязательно. Параметры строки подключения MongoDB в виде строки в формате URL. Например: 'authSource=admin&ssl=true'
oid_columns	Список столбцов, разделённых запятыми, которые следует обрабатывать как oid в предложении WHERE. По умолчанию — _id.

Если вы используете облачный сервис MongoDB Atlas, URL подключения можно получить из опции ‘Atlas SQL’. Seed list(mongodb**+srv**) пока не поддерживается, но будет добавлен в будущих релизах.

Либо можно передать URI:

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

Параметры движка

Параметр	Описание
uri	URI подключения к серверу MongoDB.
collection	Имя удалённой коллекции.
oid_columns	Список столбцов, разделённых запятыми, которые следует обрабатывать как oid в предложении WHERE. По умолчанию — _id.

​

Сопоставление типов

MongoDB	ClickHouse
bool, int32, int64	любой числовой тип, кроме Decimals, Boolean, String
double	Float64, String
date	Date, Date32, DateTime, DateTime64, String
string	String, любой числовой тип (кроме Decimals) при корректном формате
document	String (как JSON)
array	Array, String (как JSON)
oid	String
binary	String, если в столбце; строка в кодировке base64, если в массиве или документе
uuid (binary subtype 4)	UUID
любой другой	String

Если ключ не найден в документе MongoDB (например, имя столбца не совпадает), будет вставлено значение по умолчанию или NULL (если столбец допускает NULL).

​

OID

Если вы хотите, чтобы String интерпретировался как oid в предложении WHERE, просто укажите имя столбца в последнем аргументе движка таблицы. Это может понадобиться при запросе записи по столбцу _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.

Всегда лучше явно указывать тип литерала, поскольку MongoDB требует строго типизированных фильтров. Например, вы хотите фильтровать по Date:

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

Это не сработает, потому что MongoDB не приведет строку к типу 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;

-- Найти все сиквелы 'Back to the Future' с рейтингом > 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

-- Найти топ-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 │
   └────────────────────────┴────────┘

​

Устранение неполадок

Сгенерированный запрос MongoDB можно увидеть в журналах уровня DEBUG.

Подробности реализации можно найти в документации mongocxx и mongoc.