postgresql

Permite executar consultas SELECT e INSERT em dados armazenados em um servidor PostgreSQL remoto.

postgresql({host:port, database, table, user, password[, schema, [, on_conflict]] | named_collection[, option=value [,..]]} [, SETTINGS name=value, ...])

Os argumentos também podem ser passados usando coleções nomeadas. Nesse caso, host e port devem ser especificados separadamente. Essa abordagem é recomendada para ambientes de produção.

Os parâmetros TLS/SSL são encaminhados ao libpq e podem ser fornecidos como chaves de coleções nomeadas ou como argumentos chave-valor ao final: sslmode (disable, allow, prefer, require, verify-ca ou verify-full; quando não definido, é usado o padrão prefer do libpq) e os certificados e a chave, em uma de duas formas. sslrootcert (certificado de CA ou o valor especial system), sslcert (certificado do cliente) e sslkey (chave privada do cliente) são caminhos para arquivos locais do servidor e só podem ser especificados em uma coleção nomeada definida no arquivo de configuração do servidor. sslrootcert_pem, sslcert_pem e sslkey_pem aceitam, em vez disso, o conteúdo literal do arquivo correspondente — por exemplo, postgresql('host:port', 'database', 'table', 'user', 'password', sslmode = 'verify-full', sslrootcert_pem = '...') — e são mascarados nos logs e em consultas SHOW, como uma senha.

Um objeto de tabela com as mesmas colunas da tabela PostgreSQL original.

O pool de conexões usado pela função de tabela postgresql (e pelo motor de tabela PostgreSQL) pode ser configurado com uma cláusula SETTINGS no final. Quando uma configuração não é especificada, ela assume o valor da configuração postgresql_* correspondente no nível da consulta. Consulte a seção Configurações do motor de tabela para ver a lista completa das configurações postgresql_connection_pool_* e postgresql_connection_attempt_timeout, bem como seus valores padrão.

Exemplo:

SELECT * FROM postgresql('localhost:5432', 'test', 'test', 'postgresql_user', 'password', SETTINGS postgresql_connection_pool_size = 32);

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ção, condições IN [ array ] e a restrição de amostragem LIMIT são executadas no ClickHouse somente após o término da consulta ao PostgreSQL.

Em vez de um nome de tabela, o terceiro argumento pode ser uma consulta SELECT que é passada ao PostgreSQL sem alterações. A estrutura da tabela resultante é inferida com base no resultado da consulta. A consulta pode ser escrita como uma subconsulta ou encapsulada na função query:

SELECT * FROM postgresql('localhost:5432', 'test', (SELECT a, b FROM t1 JOIN t2 USING (id) WHERE a > 0), 'user', 'password');
SELECT * FROM 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 é suportada pelo motor 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.

Tipos Array do PostgreSQL são convertidos em arrays do ClickHouse.

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

SELECT name FROM postgresql(`postgres{1|2|3}:5432`, 'postgres_database', 'postgres_table', 'user', 'password');

ou

SELECT name FROM postgresql(`postgres1:5431|postgres2:5432`, 'postgres_database', 'postgres_table', 'user', 'password');

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

Tabela no PostgreSQL:

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)

Selecionando dados do ClickHouse com argumentos simples:

SELECT * FROM postgresql('localhost:5432', 'test', 'test', 'postgresql_user', 'password') WHERE str IN ('test');

Ou usando coleções nomeadas:

CREATE NAMED COLLECTION mypg AS
        host = 'localhost',
        port = 5432,
        database = 'test',
        user = 'postgresql_user',
        password = 'password';
SELECT * FROM postgresql(mypg, table='test') WHERE str IN ('test');

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

Inserção:

INSERT INTO TABLE FUNCTION postgresql('localhost:5432', 'test', 'test', 'postgrsql_user', 'password') (int_id, float) VALUES (2, 3);
SELECT * FROM postgresql('localhost:5432', 'test', 'test', 'postgresql_user', 'password');

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

Usando um esquema diferente do padrão:

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

• O motor de tabela PostgreSQL
• Usando o PostgreSQL como fonte de dicionário

Além das funções de tabela, você sempre pode usar o PeerDB, da ClickHouse, para configurar um pipeline contínuo de dados do Postgres para o ClickHouse. O PeerDB é uma ferramenta projetada especificamente para replicar dados do Postgres para o ClickHouse usando CDC (captura de alterações de dados).