Motor de tabla MySQL

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

Crear una tabla

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.

TLS/SSL

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

Parámetro	Descripción
`ssl_ca_pem`	Contenido del certificado de CA con el que se verifica el certificado del servidor MySQL.
`ssl_cert_pem`	Contenido del certificado de cliente, para la autenticación basada en certificados.
`ssl_key_pem`	Contenido de la clave privada asociada a `ssl_cert_pem`.

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>

Pasar una consulta en lugar del nombre de una tabla

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.

La forma de subconsulta (SELECT ...) la analiza ClickHouse y la vuelve a serializar en el dialecto de MySQL (identificadores entre acentos graves) antes de enviarla al servidor. Por lo tanto, debe ser válida en ClickHouse SQL. Para pasar sintaxis específica de MySQL que ClickHouse no analiza, use la forma query('...'), cuyo texto se envía a MySQL literalmente.Cualquier WHERE, LIMIT, agregación, etc. de la consulta externa de ClickHouse no se hace pushdown en la consulta pasada; se aplica en ClickHouse después de recuperar el resultado completo de la consulta. Para restringir los datos leídos desde MySQL, coloque el filtro dentro de la consulta pasada. Con external_table_strict_query = 1, un filtro externo sobre las columnas de la tabla se rechaza con una excepción en lugar de aplicarse localmente, porque no puede hacerse pushdown en la consulta pasada. La comprobación cubre el predicado WHERE de nivel superior y cada condición de un AND de nivel superior. Un PREWHERE sobre las columnas de esta tabla no es un caso para esta configuración: este motor de tabla no admite PREWHERE, y dicha consulta se rechaza con ILLEGAL_PREWHERE independientemente de la configuración. La comprobación se ejecuta solo donde un filtro podría hacerse pushdown: cuando esta tabla es la única tabla de la consulta, en cualquiera de los lados de un INNER JOIN o en el lado preservado de un outer join (el lado izquierdo de un LEFT JOIN, el lado derecho de un RIGHT JOIN). En el lado no preservado de un LEFT/RIGHT JOIN y en cualquiera de los lados de un FULL JOIN, no se hace pushdown ni se realiza ninguna comprobación, por lo que un filtro sobre las columnas de esta tabla se aplica localmente después del join incluso en modo estricto. Donde se ejecuta la comprobación, un predicado que hace referencia a otras tablas unidas en la consulta externa no se hace pushdown y se excluye de la comprobación, tanto si hace referencia solo al lado unido como si lo mezcla con esta tabla dentro de una expresión que no es AND (por ejemplo, un OR); dicho predicado conserva su punto habitual de evaluación en ClickHouse (WHERE después del join, PREWHERE antes de él) y no se rechaza.

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

Ejemplo de uso

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 │
└────────────────┴────────┘

Configuración

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.

`connection_auto_close`

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.

`connection_max_tries`

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.

`connection_pool_size`

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.

`connection_wait_timeout`

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.

`connect_timeout`

Tiempo de espera de conexión (en segundos). Valores posibles:

Entero positivo.

Valor predeterminado: 10.

`read_write_timeout`

Tiempo de espera de lectura y escritura (en segundos). Posibles valores:

Entero positivo.

Valor predeterminado: 300.

`enable_compression`

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;

Referencia de SQL

Tipos de datos

Motores

Funciones

Formatos

Configuración

Tablas del sistema

Lagos de datos

Crear una tabla

TLS/SSL

Pasar una consulta en lugar del nombre de una tabla

Ejemplo de uso

Configuración

`connection_auto_close`

`connection_max_tries`

`connection_pool_size`

`connection_wait_timeout`

`connect_timeout`

`read_write_timeout`

`enable_compression`

Véase también

​Crear una tabla

​TLS/SSL

​Pasar una consulta en lugar del nombre de una tabla

​Ejemplo de uso

​Configuración

​connection_auto_close

​connection_max_tries

​connection_pool_size

​connection_wait_timeout

​connect_timeout

​read_write_timeout

​enable_compression

​Véase también

Crear una tabla

TLS/SSL

Pasar una consulta en lugar del nombre de una tabla

Ejemplo de uso

Configuración

`connection_auto_close`

`connection_max_tries`

`connection_pool_size`

`connection_wait_timeout`

`connect_timeout`

`read_write_timeout`

`enable_compression`

Véase también