postgresql

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

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

​

Sintaxe

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

​

Argumentos

Argument	Descrição
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á (consulte Passando uma consulta em vez de um nome de tabela).
user	Usuário do PostgreSQL.
password	Senha do usuário.
schema	Esquema diferente do padrão da tabela. Opcional.
on_conflict	Estratégia de resolução de conflitos. Exemplo: ON CONFLICT DO NOTHING. Opcional.

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.

​

Valor retornado

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

Na consulta INSERT, para diferenciar a função de tabela postgresql(...) do nome da tabela com uma lista de nomes de colunas, você deve usar as palavras-chave FUNCTION ou TABLE FUNCTION. Veja os exemplos abaixo.

​

Configurações

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

​

Detalhes de implementação

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.

​

Passando uma consulta em vez de um nome de tabela

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.

A forma de subconsulta (SELECT ...) é analisada pelo ClickHouse e reserializada no dialeto PostgreSQL (uso de aspas em identificadores do PostgreSQL e escape de literais de string) antes de ser enviada ao servidor. Portanto, ela deve ser um ClickHouse SQL válido. Para passar sintaxe específica do PostgreSQL que o ClickHouse não analisa, use a forma query('...'), cujo texto é enviado ao PostgreSQL literalmente.

Qualquer WHERE, LIMIT, agregação etc. externo da consulta ClickHouse ao redor não é transferido para a consulta enviada — ele é aplicado no ClickHouse após a obtenção do resultado completo da consulta. Para restringir os dados lidos do PostgreSQL, coloque o filtro dentro da consulta enviada. Com external_table_strict_query = 1, um filtro externo nas colunas da função de tabela é rejeitado com uma exceção em vez de ser aplicado localmente, porque não pode ser transferido para a consulta enviada. A verificação abrange o predicado WHERE de nível superior e cada conjunção de um AND de nível superior. Um PREWHERE nas colunas dessa tabela não é um caso para essa configuração: esse motor de tabela não oferece suporte a PREWHERE, e essa consulta é rejeitada com ILLEGAL_PREWHERE independentemente da configuração. A verificação é executada apenas onde um filtro poderia ser transferido: quando essa 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 é transferido e nada é verificado; portanto, um filtro nas colunas dessa 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 ao redor não é transferido e é excluído da verificação, seja ele referente apenas ao lado unido ou misture-o com esta tabela em uma expressão não AND (por exemplo, um OR); esse predicado mantém seu ponto de avaliação usual no ClickHouse (WHERE após a junção, PREWHERE antes dela) e não é rejeitado.

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.

Atenção: no PostgreSQL, uma coluna do tipo array, como Integer[], pode conter arrays com dimensões diferentes em linhas distintas, mas no ClickHouse só é permitido ter arrays multidimensionais com a mesma dimensão em todas as linhas.

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.

​

Exemplos

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

​

Veja também

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

​

Replicando ou migrando dados do Postgres com o PeerDB

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