Motor de tabla MySQL

El motor MySQL le permite realizar consultas SELECT e INSERT en datos almacenados en un servidor MySQL remoto.

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 ]
;

Consulta una descripción detallada de la sentencia CREATE TABLE.

La estructura de la tabla puede diferir de la estructura de la tabla MySQL original:

• Los nombres de las columnas deben ser los mismos que en la tabla MySQL original, pero puedes usar solo algunas de ellas y en cualquier orden.
• Los tipos de las columnas pueden diferir de los de la tabla MySQL original. ClickHouse intenta convertir los valores a los tipos de datos de ClickHouse.
• La configuración external_table_functions_use_nulls define cómo manejar las columnas Nullable. Valor predeterminado: 1. Si es 0, la función de tabla no crea columnas Nullable e inserta valores predeterminados en lugar de valores NULL. Esto también se aplica a los valores NULL dentro de arrays.

Parámetros del motor

• host:port — Dirección del servidor MySQL.
• database — Nombre de la base de datos remota.
• table — Nombre de la tabla remota, o una consulta pasada a MySQL tal cual (consulta Pasar una consulta en lugar de un nombre de tabla).
• user — usuario MySQL.
• password — Contraseña del usuario.
• replace_query — Indicador que convierte las consultas INSERT INTO en REPLACE INTO. Si replace_query=1, la consulta se sustituye.
• on_duplicate_clause — La expresión ON DUPLICATE KEY on_duplicate_clause que se añade a la consulta INSERT. Ejemplo: INSERT INTO t (c1,c2) VALUES ('a', 2) ON DUPLICATE KEY UPDATE c2 = c2 + 1, donde on_duplicate_clause es UPDATE c2 = c2 + 1. Consulta la documentación de MySQL para ver qué on_duplicate_clause puedes usar con la cláusula ON DUPLICATE KEY. Para especificar on_duplicate_clause, debes pasar 0 al parámetro replace_query. Si pasas simultáneamente replace_query = 1 y on_duplicate_clause, ClickHouse genera una excepción.

Los argumentos también se pueden pasar mediante colección nombrada. En este caso, host y port deben especificarse por separado. Este enfoque se recomienda para entornos de producción.

Las cláusulas WHERE simples, como =, !=, >, >=, <, <=, se ejecutan en el servidor MySQL.

El resto de las condiciones y la restricción de muestreo LIMIT se ejecutan en ClickHouse solo después de que finaliza la consulta a MySQL.

Las credenciales de una conexión cifrada a MySQL se proporcionan como claves de una colección nombrada (o como argumentos de clave-valor):

Los valores son el contenido de los archivos PEM correspondientes, que se puede copiar en una colección nombrada o en una consulta. Se enmascaran en los logs y en las consultas SHOW, del mismo modo que las contraseñas.

Las mismas credenciales también pueden proporcionarse como rutas a archivos del servidor, mediante ssl_ca, ssl_cert y ssl_key, pero solo en una colección nombrada definida en el archivo de configuración del servidor; dichos valores no pueden sobrescribirse en una consulta. El servidor abre esos archivos con sus propios privilegios, por lo que aceptar una ruta desde SQL permitiría a cualquier usuario capaz de definir una fuente MySQL examinar el sistema de archivos local y autenticarse con un certificado y una clave que no tiene permiso para leer.

<named_collections>
    <mysql_creds>
        <host>mysql-host</host>
        <port>3306</port>
        <user>mysql_user</user>
        <password>****</password>
        <ssl_ca>/etc/clickhouse-server/mysql-ca.crt</ssl_ca>
    </mysql_creds>
</named_collections>

En lugar del nombre de una tabla, el argumento table puede ser una consulta SELECT que se envía a MySQL sin modificar. La estructura de la tabla se infiere a partir del resultado de la consulta. La consulta puede escribirse como una subconsulta o envolverse en la función 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');

Esto es útil para hacer pushdown de joins, agregaciones o cualquier otro procesamiento a MySQL. Esa tabla es de solo lectura: no se permite INSERT en ella. La función de tabla mysql admite la misma sintaxis.

Admite múltiples réplicas, que deben indicarse con |. Por ejemplo:

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

Crear una tabla en 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)

Cree una tabla en ClickHouse con argumentos simples:

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

O bien, usando colecciones nombradas:

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')

Obtención de datos de la tabla de MySQL:

SELECT * FROM mysql_table

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

La configuración predeterminada no es muy eficiente, ya que ni siquiera reutiliza las conexiones. Estos ajustes permiten aumentar el número de consultas que el servidor ejecuta por segundo.

Permite cerrar automáticamente la conexión después de ejecutar la consulta; es decir, desactiva la reutilización de la conexión.

Valores posibles:

• 1 — Se permite el cierre automático de la conexión, por lo que la reutilización de la conexión está desactivada
• 0 — No se permite el cierre automático de la conexión, por lo que la reutilización de la conexión está activada

Valor predeterminado: 1.

Establece el número de reintentos del pool con failover.

Valores posibles:

• Entero positivo.
• 0 — No hay reintentos para el pool con failover.

Valor predeterminado: 3.

Tamaño del pool de conexiones (si todas las conexiones están en uso, la consulta esperará hasta que se libere alguna).

Valores posibles:

• Entero positivo.

Valor predeterminado: 16.

Tiempo de espera (en segundos) para esperar a que haya una conexión libre (en caso de que ya haya connection_pool_size conexiones activas); 0: no esperar.

Valores posibles:

• Entero positivo.

Valor predeterminado: 5.

Tiempo de espera de conexión (en segundos).

Valores posibles:

• Entero positivo.

Valor predeterminado: 10.

Tiempo de espera de lectura y escritura (en segundos).

Posibles valores:

• Entero positivo.

Valor predeterminado: 300.

Habilita la compresión para la conexión mediante el protocolo MySQL.

Valor predeterminado: false.

Esta configuración se aplica a:

• el motor de tabla MySQL;
• el motor de base de datos MySQL;
• la función de tabla mysql;
• las colecciones nombradas utilizadas por las integraciones de MySQL.

Cuando está habilitada, ClickHouse solicita compresión para la conexión.

Ejemplo:

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;

• La función de tabla mysql
• Uso de MySQL como fuente de diccionario