Clickhouse: Cláusula SELECT

Clausula SELECT

As consultas SELECT são usadas para recuperar dados de um banco de dados. Por padrão, os dados solicitados são retornados ao cliente. No entanto, quando combinadas com o comando INSERT INTO, as consultas SELECT podem direcionar os dados recuperados para uma tabela diferente, permitindo a inserção dos dados em vez de apenas exibi-los.

Esta é a quantidade de clausulas que podem acompanhar o comando SELECT:

Algumas as cláusulas são opcionais, enquanto outras são consideradas como obrigatórias. As cláusulas a seguir são listadas na mesma ordem em que são executadas:

• WITH
• SELECT
• DISTINCT
• FROM
• SAMPLE
• JOIN
• PREWHERE
• WHERE
• GROUP BY
• LIMIT BY
• HAVING
• QUALIFY
• LIMIT
• OFFSET
• UNION
• INTERSECT
• EXCEPT
• INTO OUTFILE
• FORMAT

Expressões especificadas na cláusula SELECT são calculadas após todas as operações nas cláusulas descritas acima serem concluídas. Essas expressões funcionam como se fossem aplicadas a linhas separadas no resultado. Se as expressões na cláusula SELECT contiverem funções de agregação, o ClickHouse processa funções de agregação e expressões usadas como seus argumentos durante a agregação GROUP BY .

Se você quiser incluir todas as colunas no resultado, use o *símbolo asterisco ( ). Por exemplo, SELECT * FROM ...

Seleção dinâmica de colunas

A seleção dinâmica de colunas (também conhecida como COLUMNS expression) permite que você corresponda algumas colunas em um resultado com uma expressão regular re2 .

COLUMNS('regexp')

Por exemplo, considere a tabela:

CREATE TABLE default.col_names (aa Int8, ab Int8, bc Int8) ENGINE = TinyLog

A consulta a seguir seleciona dados de todas as colunas que contêm o asímbolo em seus nomes.

SELECT COLUMNS('a') FROM col_names

As colunas selecionadas não são retornadas em ordem alfabética.

Você pode usar várias COLUMNSexpressões em uma consulta e aplicar funções a elas.

Por exemplo:

SELECT COLUMNS('a'), COLUMNS('c'), toTypeName(COLUMNS('c')) FROM col_names

Cada coluna retornada pela expressão COLUMNS é passada para a função como um argumento separado. Você também pode passar outros argumentos para a função se ela os suportar. Tenha cuidado ao usar funções, pois se uma função não suportar o número de argumentos que você passou para ela, o ClickHouse lança uma exceção.

Por exemplo:

SELECT COLUMNS('a') + COLUMNS('c') FROM col_names

Received exception from server (version 19.14.1):
Code: 42. DB::Exception: Received from localhost:9000. DB::Exception: Number of arguments for function plus does not match: passed 3, should be 2.

Neste exemplo, COLUMNS('a') retorna duas colunas: aa e ab. COLUMNS('c') retorna a coluna bc . O operador + não pode ser aplicado a 3 argumentos, então o ClickHouse lança uma exceção com a mensagem relevante.

Colunas que correspondem à expressão COLUMNS podem ter diferentes tipos de dados. Se COLUMNS não corresponder a nenhuma coluna e for a única expressão em SELECT, o ClickHouse lança uma exceção.

Você pode colocar um asterisco em qualquer parte de uma consulta em vez de uma expressão. Quando a consulta é analisada, o asterisco é expandido para uma lista de todas as colunas da tabela (excluindo as colunas MATERIALIZED e ALIAS). Existem apenas alguns casos em que o uso de um asterisco é justificado:

• Ao criar um despejo de tabela.
• Para tabelas contendo apenas algumas colunas, como tabelas de sistema.
• Para obter informações sobre quais colunas estão em uma tabela. Neste caso, defina LIMIT 1. Mas é melhor usar a DESC TABLEquery.
• Quando há forte filtragem em um pequeno número de colunas usando PREWHERE.
• Em subconsultas (já que colunas que não são necessárias para a consulta externa são excluídas das subconsultas).

Em todos os outros casos, não é recomendado usar o asterisco, pois ele só lhe dá as desvantagens de um DBMS colunar em vez das vantagens. Em outras palavras, usar o asterisco não é recomendado.

Extremos

Além dos resultados, você também pode obter valores mínimos e máximos para as colunas de resultados. Para fazer isso, defina a configuração extremes como 1. Mínimos e máximos são calculados para tipos numéricos, datas e datas com horas. Para outras colunas, os valores padrão são emitidos.

Duas linhas extras são calculadas – os mínimos e máximos, respectivamente, e essas duas linhas extras são geradas nos formatos XML , JSON*, TabSeparated*, CSV*, Vertical e Template, separadamente das outras linhas. Elas não são geradas para outros formatos. Pretty*

Nos formatos JSON* e XML, os valores extremos são emitidos em um campo 'extremes' separado. Nos formatos TabSeparated*, CSV* e Vertical, a linha vem depois do resultado principal e depois de 'totals', se presente. Ela é precedida por uma linha vazia (depois dos outros dados). Nos formatos Pretty*, a linha é emitida como uma tabela separada depois do resultado principal e depois totals, se presente. No formato Template os valores extremos são emitidos de acordo com o modelo especificado.

Valores extremos são calculados para linhas antes de LIMIT, mas depois de LIMIT BY. No entanto, ao usar LIMIT offset, size, as linhas antes offset são incluídas em extremes. Em solicitações de fluxo, o resultado também pode incluir um pequeno número de linhas que passaram por LIMIT.

Você pode usar sinônimos (AS aliases) em qualquer parte de uma consulta.

As cláusulas GROUP BY, ORDER BY e LIMIT BY podem suportar argumentos posicionais. Para habilitar isso, ative a configuração enable_positional_arguments. Então, por exemplo, ORDER BY 1,2 as linhas da tabela serão classificadas na primeira e depois na segunda coluna.

Detalhes da implementação
Se a consulta omitir as cláusulas DISTINCT, and e as subconsultas and , a consulta será completamente processada em fluxo, usando uma quantidade de RAM O(1). Caso contrário, a consulta pode consumir muita RAM se as restrições apropriadas não forem especificadas: GROUP BY, ORDER BY, IN e JOIN.

• max_memory_usage
• max_rows_to_group_by
• max_rows_to_sort
• max_rows_in_distinct
• max_bytes_in_distinct
• max_rows_in_set
• max_bytes_in_set
• max_rows_in_join
• max_bytes_in_join
• max_bytes_before_external_sort
• max_bytes_before_external_group_by

Para mais informações, veja a seção “Configurações”. É possível usar ordenação externa (salvando tabelas temporárias em um disco) e agregação externa.

SELECT
Você pode usar modificadores em consultas SELECT, que permitem que você invoque alguma função para cada linha retornada por uma expressão de tabela externa de uma consulta.

Sintaxe:

SELECT < expr> APPLY( ) FROM [db.]table_name

Exemplo:

CREATE TABLE columns_transformers (i Int64, j Int16, k Int64) ENGINE = MergeTree ORDER by (i);
INSERT INTO columns_transformers VALUES (100, 10, 324), (120, 8, 23);
SELECT * APPLY(sum) FROM columns_transformers;

Especificando os nomes de uma ou mais colunas a serem excluídas do resultado. Todos os nomes de colunas correspondentes são omitidos da saída.

Sintaxe:

SELECT < expr> EXCEPT ( col_name1 [, col_name2, col_name3, ...] ) FROM [db.]table_name

Exemplo:

SELECT * EXCEPT (i) from columns_transformers;

Ao Especificar um ou mais aliases de expressão, cada alias deve corresponder a um nome de coluna da SELECT * instrução. Na lista de colunas de saída, a coluna que corresponde ao alias é substituída pela expressão naquele REPLACE.

Este modificador não altera os nomes ou a ordem das colunas. No entanto, ele pode alterar o valor e o tipo de valor.

Sintaxe:

SELECT < expr> REPLACE( < expr> AS col_name) from [db.]table_name

Exemplo:

SELECT * REPLACE(i + 1 AS i) from columns_transformers;

Combinações de modificadores

Você pode usar cada modificador separadamente ou combiná-los.

Exemplos:

Usar o mesmo modificador várias vezes.

SELECT COLUMNS('[jk]') APPLY(toString) APPLY(length) APPLY(max) from columns_transformers;

Usando vários modificadores em uma única consulta.

SELECT * REPLACE(i + 1 AS i) EXCEPT (j) APPLY(sum) from columns_transformers;

Configurações na Consulta

Você pode especificar as configurações necessárias diretamente na consulta SELECT, onde o valor da configuração é aplicado somente a esta consulta e é redefinido para o padrão ou valor anterior após a consulta ser executada.

Exemplo

SELECT * FROM some_table SETTINGS optimize_read_in_order=1, cast_keep_nullable=1;