postgresql - ClickHouse Documentation

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 não 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.

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 que não pode ser transferido é rejeitado com uma exceção em vez de ser aplicado localmente.

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

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

Suporta prioridade de réplicas para a fonte do 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');

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

​Sintaxe

​Argumentos

​Valor retornado

​Configurações

​Detalhes de implementação

​Passando uma consulta em vez de um nome de tabela

​Exemplos

​Veja também

​Replicando ou migrando dados do Postgres com o PeerDB

Sintaxe

Argumentos

Valor retornado

Configurações

Detalhes de implementação

Passando uma consulta em vez de um nome de tabela

Exemplos

Veja também

Replicando ou migrando dados do Postgres com o PeerDB