Instrução INSERT INTO

Documentação sobre a instrução INSERT INTO

Insere dados em uma tabela.

Sintaxe

INSERT INTO [TABLE] [db.]table [(c1, c2, c3)] [SETTINGS ...] VALUES (v11, v12, v13), (v21, v22, v23), ...

Você pode especificar uma lista de colunas para inserir usando (c1, c2, c3). Também é possível usar uma expressão com matcher de colunas, como *, e/ou modificadores, como APPLY, EXCEPT, REPLACE.

Por exemplo, considere a tabela:

SHOW CREATE insert_select_testtable;

CREATE TABLE insert_select_testtable
(
    `a` Int8,
    `b` String,
    `c` Int8
)
ENGINE = MergeTree()
ORDER BY a

INSERT INTO insert_select_testtable (*) VALUES (1, 'a', 1) ;

Se você quiser inserir dados em todas as colunas, exceto a coluna b, pode fazer isso usando a palavra-chave EXCEPT. Com base na sintaxe acima, será necessário garantir que você insira tantos valores (VALUES (v11, v13)) quantas colunas especificar ((c1, c3)) :

INSERT INTO insert_select_testtable (* EXCEPT(b)) Values (2, 2);

SELECT * FROM insert_select_testtable;

┌─a─┬─b─┬─c─┐
│ 2 │   │ 2 │
└───┴───┴───┘
┌─a─┬─b─┬─c─┐
│ 1 │ a │ 1 │
└───┴───┴───┘

Neste exemplo, vemos que a segunda linha inserida tem as colunas a e c preenchidas com os valores informados, e b preenchida com o valor padrão. Também é possível usar a palavra-chave DEFAULT para inserir valores padrão:

INSERT INTO insert_select_testtable VALUES (1, DEFAULT, 1) ;

Se uma lista de colunas não incluir todas as colunas existentes, as demais colunas serão preenchidas com:

• Os valores calculados a partir das expressões DEFAULT especificadas na definição da tabela.
• Zeros e strings vazias, se as expressões DEFAULT não estiverem definidas.

Os dados podem ser fornecidos ao INSERT em qualquer formato compatível com o ClickHouse. O formato deve ser especificado explicitamente na consulta:

INSERT INTO [db.]table [(c1, c2, c3)] FORMAT format_name data_set

Por exemplo, o seguinte formato de consulta é idêntico à versão básica de INSERT ... VALUES:

INSERT INTO [db.]table [(c1, c2, c3)] FORMAT Values (v11, v12, v13), (v21, v22, v23), ...

O ClickHouse remove todos os espaços e uma quebra de linha (se houver) antes dos dados. Ao montar uma consulta, recomendamos colocar os dados em uma nova linha após os operadores da consulta, o que é importante se os dados começarem com espaços.

Exemplo:

INSERT INTO t FORMAT TabSeparated
11  Hello, world!
22  Qwerty

Você pode inserir dados separadamente da consulta usando o cliente de linha de comando ou a interface HTTP.

Se quiser especificar SETTINGS para a consulta INSERT, faça isso antes da cláusula FORMAT, pois tudo após FORMAT format_name é tratado como dados. Por exemplo:

INSERT INTO table SETTINGS ... FORMAT format_name data_set

​

Restrições

Se uma tabela tiver restrições, suas expressões serão verificadas para cada linha dos dados inseridos. Se alguma dessas restrições não for atendida — o servidor lançará uma exceção contendo o nome e a expressão da restrição, e a consulta será interrompida.

​

Validação de tipos de dados

O ClickHouse valida os tipos de dados permitidos (controlados por configurações como enable_time_time64_type, allow_suspicious_low_cardinality_types, allow_suspicious_fixed_string_types etc.) apenas durante a criação da tabela (CREATE TABLE) e a modificação do esquema (ALTER TABLE), não durante o INSERT.

Isso significa que, se já existir uma tabela com um tipo de dado não permitido, ainda será possível inserir dados nela mesmo quando a configuração correspondente estiver desabilitada no servidor. Isso é intencional — depois que uma tabela é criada, inserções não devem ser bloqueadas por configurações que controlam a criação de tipos.

Por exemplo:

SET enable_time_time64_type = 1;

CREATE TABLE events
(
    `id` UInt64,
    `event_time` Time
)
ENGINE = MergeTree()
ORDER BY id;

SET enable_time_time64_type = 0;

-- Isso funciona mesmo com a configuração desabilitada.
-- A tabela já existe, portanto as inserções não são bloqueadas.
INSERT INTO events VALUES (1, '14:30:25');

-- Mas criar uma nova tabela com o tipo Time irá falhar.
CREATE TABLE events_new
(
    `id` UInt64,
    `event_time` Time
)
ENGINE = MergeTree()
ORDER BY id; -- ERR: TYPE_TIME_TIME64_IS_NOT_ENABLED

Como consequência, um cliente com uma versão mais recente (na qual uma configuração vem habilitada por padrão) pode inserir dados com tipos de dados não permitidos em um servidor com uma versão mais antiga (na qual a configuração está desabilitada), desde que a tabela de destino já tenha os tipos de coluna correspondentes. A validação é imposta no nível de DDL, não no nível de DML.

​

Inserindo os resultados de SELECT

Sintaxe

INSERT INTO [TABLE] [db.]table [(c1, c2, c3)] SELECT ...

As colunas são mapeadas de acordo com sua posição na cláusula SELECT. No entanto, os nomes delas na expressão SELECT e na tabela de INSERT podem ser diferentes. Se necessário, é feita a conversão de tipo.

Nenhum dos formatos de dados, exceto o formato Values, permite atribuir valores a expressões como now(), 1 + 2 e assim por diante. O formato Values permite o uso limitado de expressões, mas isso não é recomendado, porque, nesse caso, é usado um código ineficiente para executá-las.

Outras consultas para modificar partes de dados não são compatíveis: UPDATE, DELETE, REPLACE, MERGE, UPSERT, INSERT UPDATE. No entanto, você pode excluir dados antigos usando ALTER TABLE ... DROP PARTITION.

A cláusula FORMAT deve ser especificada no final da consulta se a cláusula SELECT contiver a função de tabela input().

Para inserir um valor padrão em vez de NULL em uma coluna com um tipo de dado não anulável, habilite a configuração insert_null_as_default.

INSERT também oferece suporte a CTE (expressão de tabela comum). Por exemplo, as duas instruções a seguir são equivalentes:

INSERT INTO x WITH y AS (SELECT * FROM numbers(10)) SELECT * FROM y;
WITH y AS (SELECT * FROM numbers(10)) INSERT INTO x SELECT * FROM y;

​

Inserção de dados a partir de um arquivo

Sintaxe

INSERT INTO [TABLE] [db.]table [(c1, c2, c3)] FROM INFILE file_name [COMPRESSION type] [SETTINGS ...] [FORMAT format_name]

Use a sintaxe acima para inserir dados de um arquivo, ou de vários arquivos, armazenados no cliente. file_name e type são literais de string. O formato do arquivo de entrada deve ser definido na cláusula FORMAT.

Há suporte a arquivos comprimidos. O tipo de compressão é detectado pela extensão do nome do arquivo. Também pode ser especificado explicitamente em uma cláusula COMPRESSION. Os tipos com suporte são: 'none', 'gzip', 'deflate', 'br', 'xz', 'zstd', 'lz4', 'bz2'.

Essa funcionalidade está disponível no cliente de linha de comando e no clickhouse-local.

Exemplos

​

Um único arquivo com FROM INFILE

Execute as consultas a seguir usando o cliente de linha de comando:

Query

echo 1,A > input.csv ; echo 2,B >> input.csv
clickhouse-client --query="CREATE TABLE table_from_file (id UInt32, text String) ENGINE=MergeTree() ORDER BY id;"
clickhouse-client --query="INSERT INTO table_from_file FROM INFILE 'input.csv' FORMAT CSV;"
clickhouse-client --query="SELECT * FROM table_from_file FORMAT PrettyCompact;"

Response

┌─id─┬─text─┐
│  1 │ A    │
│  2 │ B    │
└────┴──────┘

​

Vários arquivos com FROM INFILE usando globs

Este exemplo é muito semelhante ao anterior, mas as inserções são feitas a partir de vários arquivos usando FROM INFILE 'input_*.csv.

echo 1,A > input_1.csv ; echo 2,B > input_2.csv
clickhouse-client --query="CREATE TABLE infile_globs (id UInt32, text String) ENGINE=MergeTree() ORDER BY id;"
clickhouse-client --query="INSERT INTO infile_globs FROM INFILE 'input_*.csv' FORMAT CSV;"
clickhouse-client --query="SELECT * FROM infile_globs FORMAT PrettyCompact;"

Além de selecionar vários arquivos com *, você pode usar intervalos ({1,2} ou {1..9}) e outras substituições de glob. Estas três opções funcionariam no exemplo acima:

INSERT INTO infile_globs FROM INFILE 'input_*.csv' FORMAT CSV;
INSERT INTO infile_globs FROM INFILE 'input_{1,2}.csv' FORMAT CSV;
INSERT INTO infile_globs FROM INFILE 'input_?.csv' FORMAT CSV;

​

Inserindo usando uma função de tabela

Os dados podem ser inseridos em tabelas referenciadas por funções de tabela.

Sintaxe

INSERT INTO [TABLE] FUNCTION table_func ...

Exemplo

A função de tabela remote é usada nas consultas a seguir:

Query

CREATE TABLE simple_table (id UInt32, text String) ENGINE=MergeTree() ORDER BY id;
INSERT INTO TABLE FUNCTION remote('localhost', default.simple_table)
    VALUES (100, 'inserted via remote()');
SELECT * FROM simple_table;

Response

┌──id─┬─text──────────────────┐
│ 100 │ inserted via remote() │
└─────┴───────────────────────┘

​

Inserção no ClickHouse Cloud

Por padrão, os serviços no ClickHouse Cloud oferecem várias réplicas para alta disponibilidade. Quando você se conecta a um serviço, a conexão é estabelecida com uma dessas réplicas.

Após um INSERT ser concluído com sucesso, os dados são gravados no armazenamento subjacente. No entanto, as réplicas podem levar algum tempo para receber essas atualizações. Portanto, se você usar outra conexão para executar uma consulta SELECT em uma dessas outras réplicas, os dados atualizados talvez ainda não apareçam.

É possível usar select_sequential_consistency para forçar a réplica a receber as atualizações mais recentes. Veja um exemplo de consulta SELECT usando essa configuração:

SELECT .... SETTINGS select_sequential_consistency = 1;

Note que o uso de select_sequential_consistency aumentará a carga no ClickHouse Keeper (usado internamente pelo ClickHouse Cloud) e poderá resultar em um desempenho mais lento, dependendo da carga no serviço. Recomendamos não ativar essa configuração, a menos que seja necessário. A abordagem recomendada é executar leituras/gravações na mesma sessão ou usar um driver de cliente que utilize o protocolo nativo (e, assim, ofereça suporte a sticky connections).

​

Inserção em uma configuração replicada

Em uma configuração replicada, os dados ficam visíveis em outras réplicas depois de serem replicados. Os dados começam a ser replicados (baixados para outras réplicas) imediatamente após um INSERT. Isso difere do ClickHouse Cloud, em que os dados são gravados imediatamente no armazenamento compartilhado e as réplicas acompanham as alterações de metadados.

Observe que, em configurações replicadas, inserções às vezes podem levar um tempo considerável (na ordem de um segundo), pois isso exige um commit no ClickHouse Keeper para o consenso distribuído. O uso de S3 para armazenamento também acrescenta latência.

​

Considerações de desempenho

INSERT ordena os dados de entrada pela chave primária e os divide em partições com base na chave de partição. Se você inserir dados em várias partições de uma só vez, isso pode reduzir significativamente o desempenho da consulta INSERT. Para evitar isso:

• Adicione os dados em lotes relativamente grandes, como 100.000 linhas por vez.
• Agrupe os dados pela chave de partição antes de enviá-los ao ClickHouse.

O desempenho não será afetado se:

• Os dados forem adicionados em tempo real.
• Você enviar dados que normalmente já estejam ordenados por tempo.

​

Inserções assíncronas

É possível inserir dados de forma assíncrona por meio de inserções pequenas, mas frequentes. Os dados dessas inserções são combinados em lotes e depois inseridos com segurança em uma tabela. Para usar inserções assíncronas, ative a configuração async_insert.

Usar async_insert ou o motor de tabela Buffer resulta em bufferização adicional.

​

Inserções grandes ou demoradas

Ao inserir grandes volumes de dados, o ClickHouse otimiza o desempenho de gravação por meio de um processo chamado “squashing”. Pequenos blocos de dados inseridos na memória são mesclados e compactados em blocos maiores antes de serem gravados em disco. O squashing reduz a sobrecarga associada a cada operação de gravação. Nesse processo, os dados inseridos ficam disponíveis para consulta depois que o ClickHouse conclui a gravação de cada max_insert_block_size linhas.

Veja também

• async_insert
• wait_for_async_insert
• wait_for_async_insert_timeout
• async_insert_max_data_size
• async_insert_busy_timeout_ms
• async_insert_stale_timeout_ms