Mecanismo de tabela MySQL

O mecanismo MySQL permite executar consultas SELECT e INSERT em dados armazenados em um servidor MySQL remoto.

Criando uma tabela

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

Veja uma descrição detalhada da consulta CREATE TABLE. A estrutura da tabela pode ser diferente da estrutura da tabela MySQL original:

Os nomes das colunas devem ser os mesmos da tabela MySQL original, mas você pode usar apenas algumas delas e em qualquer ordem.
Os tipos das colunas podem ser diferentes dos da tabela MySQL original. O ClickHouse tenta converter os valores para os tipos de dados do ClickHouse.
A configuração external_table_functions_use_nulls define como lidar com colunas Nullable. Valor padrão: 1. Se for 0, a função de tabela não cria colunas Nullable e insere valores padrão em vez de nulos. Isso também se aplica a valores NULL dentro de arrays.

Parâmetros do mecanismo

host:port — Endereço do servidor MySQL.
database — nome do banco de dados remoto.
table — Nome da tabela remota ou uma consulta passada ao MySQL como está (consulte Passando uma consulta em vez de um nome de tabela).
user — usuário MySQL.
password — Senha do usuário.
replace_query — Sinalizador que converte consultas INSERT INTO em REPLACE INTO. Se replace_query=1, a consulta é substituída.
on_duplicate_clause — A expressão ON DUPLICATE KEY on_duplicate_clause adicionada à consulta INSERT. Exemplo: INSERT INTO t (c1,c2) VALUES ('a', 2) ON DUPLICATE KEY UPDATE c2 = c2 + 1, em que on_duplicate_clause é UPDATE c2 = c2 + 1. Consulte a documentação do MySQL para ver qual on_duplicate_clause você pode usar com a cláusula ON DUPLICATE KEY. Para especificar on_duplicate_clause, você precisa passar 0 para o parâmetro replace_query. Se passar replace_query = 1 e on_duplicate_clause ao mesmo tempo, o ClickHouse gera uma exceção.

Os argumentos também podem ser passados usando coleções nomeadas. Nesse caso, host e port devem ser especificados separadamente. Essa abordagem é recomendada para o ambiente de produção. Cláusulas WHERE simples, como =, !=, >, >=, <, <=, são executadas no servidor MySQL. O restante das condições e a restrição de amostragem LIMIT são executados no ClickHouse somente após o término da consulta ao MySQL.

Passando uma consulta em vez do nome de uma tabela

Em vez do nome de uma tabela, o argumento table pode ser uma consulta SELECT passada ao MySQL como está. A estrutura da tabela é inferida a partir do resultado da consulta. A consulta pode ser escrita como uma subconsulta ou encapsulada na função 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');

Isso é útil para delegar junções, agregações ou qualquer outro processamento ao MySQL. Essa tabela é somente leitura: INSERT nela não é permitido. A mesma sintaxe também é compatível com a função de tabela mysql.

A forma de subconsulta (SELECT ...) é analisada pelo ClickHouse e serializada novamente no dialeto do MySQL (com identificadores entre crases) antes de ser enviada ao servidor. Portanto, ela deve ser um SQL do ClickHouse válido. Para passar uma sintaxe específica do MySQL que o ClickHouse não analisa, use a forma query('...'), cujo texto é enviado ao MySQL literalmente.Qualquer WHERE, LIMIT, agregação etc. externo da consulta ClickHouse envolvente não é delegado à consulta fornecida — ele é aplicado no ClickHouse depois que o resultado completo da consulta é obtido. Para restringir os dados lidos do MySQL, coloque o filtro dentro da consulta fornecida. Com external_table_strict_query = 1, um filtro externo que não pode ser delegado é rejeitado com uma exceção em vez de ser aplicado localmente.

Compatível com múltiplas réplicas, que devem ser listadas com |. Por exemplo:

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

Exemplo de uso

Criar tabela no 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)

Crie uma tabela no ClickHouse usando argumentos simples:

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

Ou use coleções nomeadas:

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

Recuperando dados da tabela do MySQL:

SELECT * FROM mysql_table

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

Configurações

As configurações padrão não são muito eficientes, pois nem sequer reutilizam as conexões. Essas configurações permitem aumentar o número de consultas que o servidor executa por segundo.

`connection_auto_close`

Permite fechar automaticamente a conexão após a execução da consulta, ou seja, desativa a reutilização da conexão. Valores possíveis:

1 — O fechamento automático da conexão é permitido, portanto a reutilização da conexão fica desativada
0 — O fechamento automático da conexão não é permitido, portanto a reutilização da conexão fica ativada

Valor padrão: 1.

`connection_max_tries`

Define o número de novas tentativas para o pool com failover. Valores possíveis:

Inteiro positivo.
0 — Não há novas tentativas para o pool com failover.

Valor padrão: 3.

`connection_pool_size`

Tamanho do pool de conexões (se todas as conexões estiverem em uso, a consulta aguardará até que alguma seja liberada). Valores possíveis:

Inteiro positivo.

Valor padrão: 16.

`connection_wait_timeout`

Tempo limite (em segundos) para aguardar uma conexão livre (caso já haja connection_pool_size conexões ativas); 0 - não aguardar. Valores possíveis:

Inteiro positivo.

Valor padrão: 5.

`connect_timeout`

Tempo limite de conexão (em segundos). Valores possíveis:

Inteiro positivo.

Valor padrão: 10.

`read_write_timeout`

Tempo limite de leitura e gravação (em segundos). Valores possíveis:

Inteiro positivo.

Valor padrão: 300.

`enable_compression`

Habilita a compressão para a conexão do protocolo MySQL. Valor padrão: false. Esta configuração se aplica a:

o mecanismo de tabela MySQL;
o mecanismo de banco de dados MySQL;
a função de tabela mysql;
coleções nomeadas usadas por integrações do MySQL.

Quando habilitada, o ClickHouse solicita compressão para a conexão. Exemplo:

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;

Referência SQL

Criando uma tabela

Passando uma consulta em vez do nome de uma tabela

Exemplo de uso

Configurações

`connection_auto_close`

`connection_max_tries`

`connection_pool_size`

`connection_wait_timeout`

`connect_timeout`

`read_write_timeout`

`enable_compression`

Veja também

​Criando uma tabela

​Passando uma consulta em vez do nome de uma tabela

​Exemplo de uso

​Configurações

​connection_auto_close

​connection_max_tries

​connection_pool_size

​connection_wait_timeout

​connect_timeout

​read_write_timeout

​enable_compression

​Veja também

Criando uma tabela

Passando uma consulta em vez do nome de uma tabela

Exemplo de uso

Configurações

`connection_auto_close`

`connection_max_tries`

`connection_pool_size`

`connection_wait_timeout`

`connect_timeout`

`read_write_timeout`

`enable_compression`

Veja também