O motor PostgreSQL permite consultas SELECT e INSERT em dados armazenados em um servidor PostgreSQL remoto.
Atualmente, apenas as versões 12 ou superiores do PostgreSQL são compatíveis com o motor de tabela.
Conheça nosso serviço Managed Postgres. Com armazenamento NVMe fisicamente no mesmo local que a computação, ele oferece desempenho até 10x mais rápido para cargas de trabalho limitadas por disco em comparação com alternativas que usam armazenamento conectado à rede, como o EBS, e permite replicar seus dados do Postgres para o ClickHouse usando o conector Postgres CDC no ClickPipes. CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
name1 type1 [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
name2 type2 [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
...
) ENGINE = PostgreSQL({host:port, database, table, user, password[, schema, [, on_conflict]] | named_collection[, option=value [,..]]})
- 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.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>
SELECT * FROM postgresql(postgres_creds, table='table1');
Detalhes de implementação
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.
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.
Atenção: no PostgreSQL, um dado de array, criado como type_name[], pode conter arrays multidimensionais com números diferentes de dimensões em diferentes linhas da mesma coluna da tabela. Já no ClickHouse, só é permitido ter arrays multidimensionais com a mesma quantidade de dimensões em todas as linhas da mesma coluna da tabela.
|. Por exemplo:
CREATE TABLE test_replicas (id UInt32, name String) ENGINE = PostgreSQL(`postgres{2|3|4}:5432`, 'clickhouse', 'test_replicas', 'postgres', 'mysecretpassword');
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)
Criando uma tabela no ClickHouse e conectando-a à tabela do PostgreSQL criada acima
CREATE TABLE default.postgresql_table
(
`float_nullable` Nullable(Float32),
`str` String,
`int_id` Int32
)
ENGINE = PostgreSQL('localhost:5432', 'public', 'test', 'postgres_user', 'postgres_password');
Inserindo dados iniciais de uma tabela do PostgreSQL em uma tabela do ClickHouse usando uma consulta SELECT
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');
Inserindo dados incrementais da tabela PostgreSQL na tabela do ClickHouse
SELECT max(`int_id`) AS maxIntID FROM default.postgresql_copy;
INSERT INTO default.postgresql_copy
SELECT * FROM postgresql('localhost:5432', 'public', 'test', 'postges_user', 'postgres_password');
WHERE int_id > maxIntID;
Selecionando dados da tabela resultante no ClickHouse
SELECT * FROM postgresql_copy WHERE str IN ('test');
┌─float_nullable─┬─str──┬─int_id─┐
│ ᴺᵁᴸᴸ │ test │ 1 │
└────────────────┴──────┴────────┘
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');
