Moteur de table MySQL

Documentation du 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.
• 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.

Prend en charge plusieurs répliques, qui doivent être séparées par |. 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;

​

Voir aussi

• La fonction de table MySQL
• Utiliser MySQL comme source de dictionnaire