Moteur de table MongoDB

Le moteur de table MongoDB est un moteur de table en lecture seule qui permet de lire des données à partir d’une collection distante.

Le moteur de table MongoDB est un moteur de table en lecture seule qui permet de lire des données à partir d’une collection distante dans MongoDB.

Seuls les serveurs MongoDB v3.6+ sont pris en charge. La Seed list(mongodb+srv) n’est pas encore prise en charge.

​

Création d’une table

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

Paramètres du moteur

Paramètre	Description
host:port	Adresse du serveur MongoDB.
database	Nom de la base de données distante.
collection	Nom de la collection distante.
user	Utilisateur MongoDB.
password	Mot de passe de l’utilisateur.
options	Facultatif. Options de la chaîne de connexion MongoDB, sous la forme d’une chaîne au format URL. Par ex. 'authSource=admin&ssl=true'
oid_columns	Liste de colonnes séparées par des virgules devant être traitées comme oid dans la clause WHERE. _id par défaut.

Si vous utilisez l’offre cloud MongoDB Atlas, l’URL de connexion peut être obtenue via l’option ‘Atlas SQL’. La Seed list(mongodb**+srv**) n’est pas encore prise en charge, mais le sera dans de futures versions.

Sinon, vous pouvez transmettre un URI :

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

Paramètres du moteur

Paramètre	Description
uri	URI de connexion au serveur MongoDB.
collection	Nom de la collection distante.
oid_columns	Liste de colonnes séparées par des virgules à traiter comme oid dans la clause WHERE. _id par défaut.

​

Correspondance des types

MongoDB	ClickHouse
bool, int32, int64	tout type numérique sauf Decimals, Boolean, String
double	Float64, String
date	Date, Date32, DateTime, DateTime64, String
string	String, tout type numérique (sauf Decimals) s’il est correctement formaté
document	String (au format JSON)
array	Array, String (au format JSON)
oid	String
binary	String si dans une colonne, chaîne encodée en base64 si dans un tableau ou un document
uuid (binary subtype 4)	UUID
tout autre	String

Si la clé est introuvable dans le document MongoDB (par exemple, si le nom de la colonne ne correspond pas), la valeur par défaut ou NULL (si la colonne est de type Nullable) sera insérée.

​

OID

Si vous voulez qu’une String soit traitée comme un oid dans la clause WHERE, il suffit d’indiquer le nom de la colonne dans le dernier argument du moteur de table. Cela peut être nécessaire lors d’une requête sur un enregistrement via la colonne _id, qui a par défaut le type oid dans MongoDB. Si le champ _id de la table est d’un autre type, par exemple uuid, vous devez spécifier un oid_columns vide ; sinon, la valeur par défaut de ce paramètre, _id, sera utilisée.

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

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

Par défaut, seul _id est considéré comme une colonne 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'; --will output 1.
SELECT count() FROM sample_oid WHERE another_oid_column = '67bf6cc40000000000ea41b1'; --will output 0

Dans ce cas, le résultat sera 0, car ClickHouse ne sait pas que another_oid_column est de type oid, alors corrigeons cela :

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

-- or

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'; -- will output 1 now

​

Clauses prises en charge

Seules les requêtes contenant des expressions simples sont prises en charge (par exemple, WHERE field = <constant> ORDER BY field2 LIMIT <constant>). Ces expressions sont traduites dans le langage de requête de MongoDB et exécutées côté serveur. Vous pouvez désactiver toutes ces restrictions avec mongodb_throw_on_unsupported_query. Dans ce cas, ClickHouse essaie de convertir la requête dans la mesure du possible, mais cela peut entraîner un scan complet de la table et un traitement côté ClickHouse.

Il est toujours préférable de définir explicitement le type d’un littéral, car Mongo exige des filtres strictement typés. Par exemple, si vous souhaitez filtrer sur Date :

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

Cela ne fonctionnera pas, car Mongo ne convertira pas la chaîne en Date ; vous devez donc la convertir manuellement :

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

Ceci s’applique à Date, Date32, DateTime, Bool, UUID.

​

Exemple d’utilisation

En supposant que le jeu de données sample_mflix soit chargé dans MongoDB

Créez une table dans ClickHouse permettant de lire des données à partir d’une collection 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 cannot be pushed down to MongoDB
SET mongodb_throw_on_unsupported_query = 0;

-- Find all 'Back to the Future' sequels with rating > 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

-- Find top 3 movies based on Cormac McCarthy's books
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 │
   └────────────────────────┴────────┘

​

Dépannage

Vous pouvez voir la requête MongoDB générée dans les logs de niveau DEBUG.

Les détails de l’implémentation sont disponibles dans la documentation de mongocxx et de mongoc.