Mecanismo de tabela MySQL

Documentação do 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.

​

TLS/SSL

As credenciais de uma conexão criptografada com o MySQL são fornecidas como chaves de uma coleção nomeada (ou como argumentos de chave-valor):

Parâmetro	Descrição
ssl_ca_pem	Conteúdo do certificado da CA usado para verificar o certificado do servidor MySQL.
ssl_cert_pem	Conteúdo do certificado do cliente, para autenticação baseada em certificado.
ssl_key_pem	Conteúdo da chave privada associada a ssl_cert_pem.

Os valores são o conteúdo dos arquivos PEM correspondentes, que pode ser copiado para uma coleção nomeada ou para uma consulta. Eles são mascarados nos logs e nas consultas SHOW, da mesma forma que as senhas.

As mesmas credenciais também podem ser fornecidas como caminhos para arquivos no servidor, em ssl_ca, ssl_cert e ssl_key — mas somente em uma coleção nomeada definida no arquivo de configuração do servidor. Esse valor não pode ser substituído em uma consulta. O servidor abre esses arquivos com seus próprios privilégios; portanto, aceitar um caminho via SQL permitiria que qualquer usuário capaz de definir uma fonte MySQL sondasse o sistema de arquivos local e se autenticasse com um certificado e uma chave que não tem permissão para ler.

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

​

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 sobre as colunas da tabela é rejeitado com uma exceção em vez de ser aplicado localmente, porque não pode ser delegado à consulta fornecida. A verificação abrange o predicado WHERE de nível superior e cada conjunção de um AND de nível superior. Um PREWHERE sobre as colunas desta tabela não se enquadra nesta configuração: este mecanismo de tabela não suporta PREWHERE, e tal consulta é rejeitada com ILLEGAL_PREWHERE independentemente da configuração. A verificação é executada apenas onde um filtro poderia de fato ser delegado: quando esta tabela é a única tabela da consulta, em qualquer lado de um INNER JOIN, ou no lado preservado de uma junção externa (o lado esquerdo de um LEFT JOIN, o lado direito de um RIGHT JOIN). No lado não preservado de um LEFT/RIGHT JOIN e em qualquer lado de um FULL JOIN, nada é delegado e nada é verificado, portanto um filtro sobre as colunas desta tabela é aplicado localmente após a junção, mesmo no modo estrito. Onde a verificação é executada, um predicado que referencia outras tabelas unidas na consulta envolvente não é delegado e fica de fora da verificação, tanto se referenciar apenas o lado unido quanto se o misturar com esta tabela dentro de uma única expressão que não seja AND (por exemplo, um OR); tal predicado mantém seu ponto de avaliação usual no ClickHouse (WHERE após a junção, PREWHERE antes dela) e não é rejeitado.

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;

​

Veja também

• A função de tabela do MySQL
• Usando o MySQL como fonte de dicionário