Motor de tabela PostgreSQL

O motor PostgreSQL permite consultas SELECT e INSERT em dados armazenados em um servidor PostgreSQL remoto.

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 type1 [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 type2 [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = PostgreSQL({host:port, database, table, user, password[, schema, [, on_conflict]] | named_collection[, option=value [,..]]})
SETTINGS
    [ postgresql_connection_pool_size=16, ]
    [ postgresql_connection_pool_wait_timeout=5000, ]
    [ postgresql_connection_pool_retries=2, ]
    [ postgresql_connection_pool_auto_close_connection=false, ]
    [ postgresql_connection_attempt_timeout=2 ]
;

Veja uma descrição detalhada da consulta CREATE TABLE.

A estrutura da tabela pode ser diferente da estrutura da tabela PostgreSQL original:

• Os nomes das colunas devem ser os mesmos da tabela PostgreSQL original, mas você pode usar apenas algumas delas e em qualquer ordem.
• Os tipos das colunas podem ser diferentes dos da tabela PostgreSQL original. O ClickHouse tenta converter os valores para os tipos de dados do ClickHouse.
• A configuração external_table_functions_use_nulls define como tratar 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 valores nulos. Isso também se aplica a valores NULL dentro de arrays.

Parâmetros do motor

• host:port — Endereço do servidor PostgreSQL.
• database — Nome do banco de dados remoto.
• table — Nome da tabela remota ou uma consulta passada ao PostgreSQL como está (veja Passando uma consulta em vez de um nome de tabela).
• user — Usuário do PostgreSQL.
• password — Senha do usuário.
• schema — Esquema de tabela não padrão. Opcional.
• on_conflict — Estratégia de resolução de conflitos. Exemplo: ON CONFLICT DO NOTHING. Opcional. Observação: adicionar esta opção tornará a inserção menos eficiente.

Coleções nomeadas (disponíveis desde a versão 21.11) são recomendadas para ambientes de produção. Aqui está um exemplo:

<named_collections>
    <postgres_creds>
        <host>localhost</host>
        <port>5432</port>
        <user>postgres</user>
        <password>****</password>
        <schema>schema1</schema>
    </postgres_creds>
</named_collections>

Alguns parâmetros podem ser substituídos por argumentos de chave-valor:

SELECT * FROM postgresql(postgres_creds, table='table1');

Os parâmetros TLS/SSL são encaminhados ao libpq e podem ser definidos como chaves de uma coleção nomeada ou como argumentos chave-valor finais: sslmode (disable, allow, prefer, require, verify-ca ou verify-full), além dos certificados e da chave, em uma de duas formas. Quando não são definidos, aplicam-se os padrões do libpq (sslmode=prefer).

• sslrootcert (certificado da CA ou o valor especial system), sslcert (certificado do cliente) e sslkey (chave privada do cliente) são caminhos para arquivos locais do servidor. Eles só podem ser especificados em uma coleção nomeada definida no arquivo de configuração do servidor e não podem ser substituídos em uma consulta: o servidor abre os arquivos com seus próprios privilégios.
• sslrootcert_pem, sslcert_pem e sslkey_pem aceitam o conteúdo literal do arquivo correspondente em vez de um caminho. Podem ser especificados em qualquer lugar — em uma consulta, em uma coleção nomeada criada com SQL ou como substituição de uma coleção nomeada — e são mascarados nos logs e em consultas SHOW, como uma senha.

Por exemplo, para exigir uma conexão criptografada e verificar o certificado do servidor:

<named_collections>
    <postgres_creds>
        <host>localhost</host>
        <port>5432</port>
        <user>postgres</user>
        <password>****</password>
        <sslmode>verify-full</sslmode>
        <sslrootcert>/etc/clickhouse-server/postgresql-ca.crt</sslrootcert>
    </postgres_creds>
</named_collections>

O mesmo, sem um arquivo de configuração, passando o conteúdo do certificado na consulta:

CREATE TABLE postgres_table (id UInt64, value String)
ENGINE = PostgreSQL('localhost:5432', 'database', 'table', 'user', 'password',
                    sslmode = 'verify-full', sslrootcert_pem = '-----BEGIN CERTIFICATE-----
...
-----END CERTIFICATE-----');

O pool de conexões usado pelo motor de tabela PostgreSQL (e pela função de tabela postgresql) pode ser configurado para cada tabela com uma cláusula SETTINGS. Quando uma configuração não é especificada, o valor padrão é o da configuração postgresql_* correspondente no nível da consulta.

Tamanho do pool de conexões (se todas as conexões estiverem em uso, a consulta aguardará até que alguma delas seja liberada). Deve ser maior que zero.

Valor padrão: 16.

Tempo limite, em milissegundos, para push/pop no pool de conexões quando o pool está vazio. 0 significa que a operação bloqueia quando o pool está vazio.

Valor padrão: 5000.

Número de tentativas de nova tentativa ao obter/devolver conexões do pool.

Valor padrão: 2.

Feche a conexão antes de devolvê-la ao pool.

Valor padrão: false.

Tempo limite de conexão, em segundos, para uma única tentativa de conexão ao endpoint do PostgreSQL. O valor é passado como parâmetro connect_timeout na URL de conexão.

Valor padrão: 2.

Exemplo:

CREATE TABLE pg_table
(
    `float_nullable` Nullable(Float32),
    `str` String,
    `int_id` Int32
)
ENGINE = PostgreSQL('localhost:5432', 'public', 'test', 'postgres_user', 'postgres_password')
SETTINGS postgresql_connection_pool_size = 32, postgresql_connection_pool_auto_close_connection = 1;

As consultas SELECT no PostgreSQL são executadas como COPY (SELECT ...) TO STDOUT dentro de uma transação PostgreSQL somente leitura, com commit após cada consulta SELECT.

Cláusulas WHERE simples, como =, !=, >, >=, <, <= e IN, são executadas no servidor PostgreSQL.

Todas as junções, agregações, ordenações, condições IN [ array ] e a restrição de amostragem LIMIT são executadas no ClickHouse somente após a conclusão da consulta ao PostgreSQL.

Em vez de um nome de tabela, o argumento table pode ser uma consulta SELECT passada ao PostgreSQL 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 pg_table ENGINE = PostgreSQL('localhost:5432', 'test', (SELECT a, b FROM t1 JOIN t2 USING (id) WHERE a > 0), 'user', 'password');
CREATE TABLE pg_table ENGINE = PostgreSQL('localhost:5432', '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 PostgreSQL. Essa tabela é somente leitura: INSERT nela não é permitido. A mesma sintaxe também é compatível com a função de tabela postgresql.

As consultas INSERT no PostgreSQL são executadas como COPY "table_name" (field1, field2, ... fieldN) FROM STDIN dentro de uma transação PostgreSQL, com commit automático após cada instrução INSERT.

Os tipos Array do PostgreSQL são convertidos em arrays do ClickHouse.

Há suporte para várias réplicas, que devem ser listadas com |. Por exemplo:

CREATE TABLE test_replicas (id UInt32, name String) ENGINE = PostgreSQL(`postgres{2|3|4}:5432`, 'clickhouse', 'test_replicas', 'postgres', 'mysecretpassword');

Há suporte a prioridade de réplicas para a fonte de dicionário do PostgreSQL. Quanto maior o número no map, menor a prioridade. A prioridade mais alta é 0.

No exemplo abaixo, a réplica example01-1 tem a maior prioridade:

<postgresql>
    <port>5432</port>
    <user>clickhouse</user>
    <password>qwerty</password>
    <replica>
        <host>example01-1</host>
        <priority>1</priority>
    </replica>
    <replica>
        <host>example01-2</host>
        <priority>2</priority>
    </replica>
    <db>db_name</db>
    <table>table_name</table>
    <where>id=10</where>
    <invalidate_query>SQL_QUERY</invalidate_query>
</postgresql>
</source>

postgres=# CREATE TABLE "public"."test" (
"int_id" SERIAL,
"int_nullable" INT NULL DEFAULT NULL,
"float" FLOAT NOT NULL,
"str" VARCHAR(100) NOT NULL DEFAULT '',
"float_nullable" FLOAT NULL DEFAULT NULL,
PRIMARY KEY (int_id));

CREATE TABLE

postgres=# INSERT INTO test (int_id, str, "float") VALUES (1,'test',2);
INSERT 0 1

postgresql> SELECT * FROM test;
int_id | int_nullable | float | str  | float_nullable
--------+--------------+-------+------+----------------
       1 |              |     2 | test |
(1 row)

Este exemplo usa o PostgreSQL motor de tabela para conectar a tabela do ClickHouse à tabela do PostgreSQL e usar instruções SELECT e INSERT no banco de dados PostgreSQL:

CREATE TABLE default.postgresql_table
(
    `float_nullable` Nullable(Float32),
    `str` String,
    `int_id` Int32
)
ENGINE = PostgreSQL('localhost:5432', 'public', 'test', 'postgres_user', 'postgres_password');

A função de tabela postgresql copia os dados do PostgreSQL para o ClickHouse. Isso costuma ser usado para melhorar o desempenho das consultas sobre esses dados, executando consultas ou análises no ClickHouse em vez de no PostgreSQL, e também pode ser usado para migrar dados do PostgreSQL para o ClickHouse. Como vamos copiar os dados do PostgreSQL para o ClickHouse, usaremos um motor de tabela MergeTree no ClickHouse e o chamaremos de postgresql_copy:

CREATE TABLE default.postgresql_copy
(
    `float_nullable` Nullable(Float32),
    `str` String,
    `int_id` Int32
)
ENGINE = MergeTree
ORDER BY (int_id);

INSERT INTO default.postgresql_copy
SELECT * FROM postgresql('localhost:5432', 'public', 'test', 'postgres_user', 'postgres_password');

Se depois do insert inicial você quiser realizar uma sincronização contínua entre a tabela PostgreSQL e a tabela do ClickHouse, poderá usar uma cláusula WHERE no ClickHouse para inserir apenas os dados adicionados ao PostgreSQL com base em um timestamp ou em um ID de sequência exclusivo.

Isso exigirá acompanhar o ID máximo ou o timestamp adicionado anteriormente, como no exemplo a seguir:

SELECT max(`int_id`) AS maxIntID FROM default.postgresql_copy;

Em seguida, inserindo valores da tabela do PostgreSQL maiores que o máximo

INSERT INTO default.postgresql_copy
SELECT * FROM postgresql('localhost:5432', 'public', 'test', 'postgres_user', 'postgres_password')
WHERE int_id > (SELECT max(int_id) FROM default.postgresql_copy);

SELECT * FROM postgresql_copy WHERE str IN ('test');

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

postgres=# CREATE SCHEMA "nice.schema";

postgres=# CREATE TABLE "nice.schema"."nice.table" (a integer);

postgres=# INSERT INTO "nice.schema"."nice.table" SELECT i FROM generate_series(0, 99) as t(i)

CREATE TABLE pg_table_schema_with_dots (a UInt32)
        ENGINE PostgreSQL('localhost:5432', 'clickhouse', 'nice.table', 'postgrsql_user', 'password', 'nice.schema');

Veja também

• A função de tabela postgresql
• Usando PostgreSQL como fonte de um dicionário

• Blog: ClickHouse and PostgreSQL - uma combinação perfeita para o universo dos dados - parte 1
• Blog: ClickHouse and PostgreSQL - uma combinação perfeita para o universo dos dados - parte 2