Moteur de table MySQL

Le moteur de table MySQL vous permet d’exécuter des requêtes SELECT et INSERT sur des données stockées sur un serveur MySQL distant.

Créer une table

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = MySQL({host:port, database, table, user, password[, replace_query, on_duplicate_clause] | named_collection[, option=value [,..]]})
SETTINGS
    [ connection_pool_size=16, ]
    [ connection_max_tries=3, ]
    [ connection_wait_timeout=5, ]
    [ connection_auto_close=true, ]
    [ connect_timeout=10, ]
    [ read_write_timeout=300, ]
    [ enable_compression=false ]
;

Consultez une description détaillée de la requête CREATE TABLE. La structure de la table peut différer de celle de la table MySQL d’origine :

Les noms des colonnes doivent être les mêmes que dans la table MySQL d’origine, mais vous pouvez n’utiliser que certaines de ces colonnes, dans n’importe quel ordre.
Les types de colonnes peuvent différer de ceux de la table MySQL d’origine. ClickHouse essaie de convertir les valeurs dans les types de données ClickHouse.
Le paramètre external_table_functions_use_nulls définit comment gérer les colonnes Nullable. Valeur par défaut : 1. Si sa valeur est 0, la fonction de table ne crée pas de colonnes Nullable et insère des valeurs par défaut à la place des valeurs NULL. Cela s’applique également aux valeurs NULL à l’intérieur des arrays.

Paramètres du moteur

host:port — Adresse du serveur MySQL.
database — Nom de la base de données distante.
table — Nom de la table distante, ou requête transmise telle quelle à MySQL (voir Passer une requête au lieu d’un nom de table).
user — Utilisateur MySQL.
password — Mot de passe de l’utilisateur.
replace_query — Option qui convertit les requêtes INSERT INTO en REPLACE INTO. Si replace_query=1, la requête est remplacée.
on_duplicate_clause — Expression ON DUPLICATE KEY on_duplicate_clause ajoutée à la requête INSERT. Exemple : INSERT INTO t (c1,c2) VALUES ('a', 2) ON DUPLICATE KEY UPDATE c2 = c2 + 1, où on_duplicate_clause vaut UPDATE c2 = c2 + 1. Consultez la documentation MySQL pour savoir quelles valeurs de on_duplicate_clause vous pouvez utiliser avec la clause ON DUPLICATE KEY. Pour spécifier on_duplicate_clause, vous devez passer 0 au paramètre replace_query. Si vous passez simultanément replace_query = 1 et on_duplicate_clause, ClickHouse génère une exception.

Les arguments peuvent également être transmis à l’aide de collections nommées. Dans ce cas, host et port doivent être spécifiés séparément. Cette approche est recommandée en production. Les clauses WHERE simples telles que =, !=, >, >=, <, <= sont exécutées sur le serveur MySQL. Le reste des conditions et la contrainte d’échantillonnage LIMIT ne sont exécutés dans ClickHouse qu’une fois la requête vers MySQL terminée.

Passer une requête au lieu d’un nom de table

Au lieu d’un nom de table, l’argument table peut être une requête SELECT transmise telle quelle à MySQL. La structure de la table est déduite du résultat de la requête. La requête peut être écrite soit sous forme de sous-requête, soit encapsulée dans la fonction query :

CREATE TABLE mysql_table ENGINE = MySQL('localhost:3306', 'test', (SELECT a, b FROM t1 JOIN t2 USING (id) WHERE a > 0), 'user', 'password');
CREATE TABLE mysql_table ENGINE = MySQL('localhost:3306', 'test', query('SELECT a, b FROM t1 JOIN t2 USING (id) WHERE a > 0'), 'user', 'password');

Cela est utile pour déléguer à MySQL les jointures, les agrégations ou tout autre traitement. Une telle table est en lecture seule : INSERT n’y est pas autorisé. La même syntaxe est prise en charge par la fonction de table mysql.

La forme de sous-requête (SELECT ...) est analysée par ClickHouse, puis re-sérialisée dans le dialecte MySQL (quotage des identifiants avec des accents graves) avant d’être envoyée au serveur. Elle doit donc être valide en ClickHouse SQL. Pour transmettre une syntaxe propre à MySQL que ClickHouse n’analyse pas, utilisez la forme query('...'), dont le texte est envoyé tel quel à MySQL.Tout WHERE, LIMIT, toute agrégation, etc. externe de la requête ClickHouse englobante n’est pas poussé dans la requête transmise — il est appliqué dans ClickHouse après récupération du résultat complet de la requête. Pour limiter les données lues depuis MySQL, placez le filtre dans la requête transmise. Avec external_table_strict_query = 1, un filtre externe qui ne peut pas être poussé est rejeté avec une exception au lieu d’être appliqué localement.

Prend en charge plusieurs répliques, qui doivent être listées avec |. Par exemple :

CREATE TABLE test_replicas (id UInt32, name String, age UInt32, money UInt32) ENGINE = MySQL(`mysql{2|3|4}:3306`, 'clickhouse', 'test_replicas', 'root', 'clickhouse');

Exemple d’utilisation

Créer une table dans MySQL :

mysql> CREATE TABLE `test`.`test` (
    ->   `int_id` INT NOT NULL AUTO_INCREMENT,
    ->   `int_nullable` INT NULL DEFAULT NULL,
    ->   `float` FLOAT NOT NULL,
    ->   `float_nullable` FLOAT NULL DEFAULT NULL,
    ->   PRIMARY KEY (`int_id`));
Query OK, 0 rows affected (0,09 sec)

mysql> insert into test (`int_id`, `float`) VALUES (1,2);
Query OK, 1 row affected (0,00 sec)

mysql> select * from test;
+------+----------+-----+----------+
| int_id | int_nullable | float | float_nullable |
+------+----------+-----+----------+
|      1 |         NULL |     2 |           NULL |
+------+----------+-----+----------+
1 row in set (0,00 sec)

Créer une table dans ClickHouse avec des arguments simples :

CREATE TABLE mysql_table
(
    `float_nullable` Nullable(Float32),
    `int_id` Int32
)
ENGINE = MySQL('localhost:3306', 'test', 'test', 'bayonet', '123')

Ou en utilisant les collections nommées :

CREATE NAMED COLLECTION creds AS
        host = 'localhost',
        port = 3306,
        database = 'test',
        user = 'bayonet',
        password = '123';
CREATE TABLE mysql_table
(
    `float_nullable` Nullable(Float32),
    `int_id` Int32
)
ENGINE = MySQL(creds, table='test')

Récupération de données depuis une table MySQL :

SELECT * FROM mysql_table

┌─float_nullable─┬─int_id─┐
│           ᴺᵁᴸᴸ │      1 │
└────────────────┴────────┘

Paramètres

Les paramètres par défaut ne sont pas très efficaces, car ils ne réutilisent même pas les connexions. Ces paramètres vous permettent d’augmenter le nombre de requêtes que le serveur peut exécuter par seconde.

`connection_auto_close`

Permet de fermer automatiquement la connexion après l’exécution de la requête, c’est-à-dire de désactiver sa réutilisation. Valeurs possibles :

1 — La fermeture automatique de la connexion est autorisée, donc sa réutilisation est désactivée
0 — La fermeture automatique de la connexion n’est pas autorisée, donc sa réutilisation est activée

Valeur par défaut : 1.

`connection_max_tries`

Définit le nombre de tentatives pour le pool avec basculement. Valeurs possibles :

Entier positif.
0 — Aucune tentative pour le pool avec basculement.

Valeur par défaut : 3.

`connection_pool_size`

Taille du pool de connexions (si toutes les connexions sont utilisées, la requête attendra qu’une connexion se libère). Valeurs possibles :

Entier positif.

Valeur par défaut : 16.

`connection_wait_timeout`

Délai d’expiration (en secondes) d’attente d’une connexion libre (s’il y a déjà connection_pool_size connexions actives), 0 - ne pas attendre. Valeurs possibles :

Entier positif.

Valeur par défaut : 5.

`connect_timeout`

Délai d’expiration de la connexion (en secondes). Valeurs possibles :

Entier positif.

Valeur par défaut : 10.

`read_write_timeout`

Délai d’expiration de lecture/écriture (en secondes). Valeurs possibles :

Entier positif.

Valeur par défaut : 300.

`enable_compression`

Active la compression pour les connexions via le protocole MySQL. Valeur par défaut : false. Ce paramètre s’applique à :

le moteur de table MySQL ;
le moteur de base de données MySQL ;
la fonction de table mysql ;
les collections nommées utilisées par les intégrations MySQL.

Lorsqu’il est activé, ClickHouse demande l’utilisation de la compression pour la connexion. Exemple :

CREATE TABLE mysql_engine_compression
(
    id UInt32,
    name String,
    age UInt32,
    money UInt32
)
ENGINE = MySQL('mysql80:3306', 'clickhouse', 'test_table', 'root', 'password')
SETTINGS enable_compression = 1;

Référence SQL

Créer une table

Passer une requête au lieu d’un nom de table

Exemple d’utilisation

Paramètres

`connection_auto_close`

`connection_max_tries`

`connection_pool_size`

`connection_wait_timeout`

`connect_timeout`

`read_write_timeout`

`enable_compression`

Voir aussi

​Créer une table

​Passer une requête au lieu d’un nom de table

​Exemple d’utilisation

​Paramètres

​connection_auto_close

​connection_max_tries

​connection_pool_size

​connection_wait_timeout

​connect_timeout

​read_write_timeout

​enable_compression

​Voir aussi

Créer une table

Passer une requête au lieu d’un nom de table

Exemple d’utilisation

Paramètres

`connection_auto_close`

`connection_max_tries`

`connection_pool_size`

`connection_wait_timeout`

`connect_timeout`

`read_write_timeout`

`enable_compression`

Voir aussi