Execute a instrução CREATE TABLE AS para criar uma tabela copiando dados da tabela de origem - Hologres: Central de Documentação do Alibaba Cloud

A instrução CREATE TABLE AS cria uma nova tabela a partir de uma tabela existente ou do resultado de uma consulta SELECT. Opcionalmente, ela também copia os dados no mesmo momento.

Duas variantes de sintaxe são suportadas:

AS TABLE <src_table_name> — copia o esquema e, opcionalmente, os dados de uma tabela ou visualize existente
AS <select_query> — cria uma tabela a partir do conjunto de resultados de uma consulta SELECT

A instrução CREATE TABLE AS copia esquemas de colunas e tipos de dados, mas não propriedades da tabela, como chaves primárias, restrições de nulidade, índices ou valores padrão.

Pré-requisitos

Antes de começar, verifique se você possui:

Uma instância do Hologres executando a versão V1.3.21 ou posterior
Uma tabela ou visualize de origem existente para servir como base da cópia

Sintaxe

-- Variant 1: Copy from a source table or view
CREATE TABLE [ IF NOT EXISTS ] <new_table_name> AS TABLE <src_table_name> [ WITH [ NO ] DATA ]

-- Variant 2: Create from a SELECT query result
CREATE TABLE [ IF NOT EXISTS ] <new_table_name> AS <select_query> [ WITH [ NO ] DATA ]

Parâmetros

Parâmetro	Descrição
`new_table_name`	Nome da nova tabela. Deve ser uma string fixa, não podendo ser uma variável ou chamada de função. Tabelas externas não podem ser criadas com esta instrução.
`IF NOT EXISTS`	Caso já exista uma tabela com o mesmo nome, ignora a criação silenciosamente.
`src_table_name`	Nome da tabela ou visualize de origem. Visualize são suportadas no Hologres V2.1.21 e versões posteriores.
`select_query`	Uma instrução SELECT. Para detalhes sobre a sintaxe, consulte SELECT.
`WITH [ NO ] DATA`	Controla se os dados de origem serão copiados. `WITH DATA` (padrão) copia os dados. `WITH NO DATA` cria uma tabela vazia mantendo o mesmo esquema.

O que é e o que não é copiado

O comando CREATE TABLE AS replica nomes de colunas e tipos de dados, mas exclui as propriedades da tabela.

	CREATE TABLE AS	CREATE TABLE LIKE
Esquemas de colunas e tipos de dados	Copiados	Copiados
Propriedades da tabela (nulidade, valores padrão, índices, chaves primárias, comentários)	Não copiados	Parcialmente copiados
Dados da tabela de origem	Copiados (com `WITH DATA`)	Não copiados
Configuração manual de propriedades (índices, chaves de distribuição)	Suporte limitado. Chaves primárias não podem ser configuradas manualmente.	Suporte limitado. Chaves primárias não podem ser configuradas manualmente.
Tabela não particionada a partir de uma tabela particionada	Suportado	Suportado
Criação de tabela particionada	Não suportado	Parcialmente suportado (via `partition_clause`)

Para obter detalhes sobre CREATE TABLE LIKE, consulte CREATE TABLE LIKE.

Limitações

Somente o Hologres V1.3.21 e versões posteriores suportam CREATE TABLE AS. Para atualizar uma instância mais antiga, faça o upgrade manual pelo console do Hologres ou entre em um grupo do DingTalk do Hologres para solicitar a atualização. Para mais informações, veja Instance upgrades ou How to get online support?.
A atomicidade da importação de dados não é garantida ao usar WITH DATA.
Para colunas com tipos que exigem precisão — VARCHAR, BPCHAR, NUMERIC (DECIMAL), BIT ou VARBIT — especifique explicitamente a precisão na instrução CREATE TABLE AS. A omissão da precisão resulta em erro.
Em colunas dos tipos INTERVAL, TIME, TIMETZ, TIMESTAMP ou TIMESTAMPTZ, não defina precisão. Definir precisão para esses tipos causa erro.
O comando CREATE TABLE AS não cria tabelas particionadas, gerando sempre uma tabela não particionada. Ao copiar de uma tabela pai particionada, a instrução inclui automaticamente os dados de todas as tabelas filhas.
No Hologres V3.0.9 e versões posteriores, é possível utilizar recursos de computação serverless para executar CREATE TABLE AS. Para detalhes, consulte Work with serverless computing.

Comportamento do log de consultas

A partir do Hologres V3.0.9, a instrução CREATE TABLE AS gera dois registros em hologres.hg_query_log: um referente à própria instrução CREATE TABLE AS e outro para a instrução INSERT interna usada na cópia dos dados. Ambos os registros são vinculados por um ID de transação.

Para recuperar ambos os registros de uma execução específica:

SELECT
    query_id,
    query,
    extended_info
FROM
    hologres.hg_query_log
WHERE
    extended_info ->> 'source_trx' = '<transaction_id>'
ORDER BY
    query_start;

Substitua <transaction_id> pelo valor do campo trans_id presente no registro de log do CREATE TABLE AS.

Nas versões anteriores à V3.0.9, apenas um registro é gerado (referente à instrução CREATE TABLE AS).

Exemplos

Copiar uma tabela não particionada

Os exemplos abaixo utilizam esta tabela de origem:

BEGIN;
CREATE TABLE public.src_table (
    "a" int8 NOT NULL,
    "b" text NOT NULL,
    PRIMARY KEY (a)
);
CALL SET_TABLE_PROPERTY('public.src_table', 'orientation', 'column');
CALL SET_TABLE_PROPERTY('public.src_table', 'bitmap_columns', 'b');
CALL SET_TABLE_PROPERTY('public.src_table', 'dictionary_encoding_columns', 'b:auto');
CALL SET_TABLE_PROPERTY('public.src_table', 'time_to_live_in_seconds', '3153600000');
CALL SET_TABLE_PROPERTY('public.src_table', 'distribution_key', 'a');
CALL SET_TABLE_PROPERTY('public.src_table', 'storage_format', 'segment');
COMMIT;

INSERT INTO public.src_table VALUES (1, 'qaz'), (2, 'wsx');

Cópia com dados (padrão)

CREATE TABLE public.new_table AS TABLE public.src_table;

Consulte a nova tabela:

SELECT * FROM public.new_table;

 a |  b
---+-----
 1 | qaz
 2 | wsx

A nova tabela contém os mesmos dados, porém não herda a chave primária nem a restrição NOT NULL da tabela de origem:

SELECT hg_dump_script('public.new_table');

BEGIN;
CREATE TABLE public.new_table (
    a int,   -- int8 NOT NULL in source; inherited as int (nullable) here
    b text   -- text NOT NULL in source; inherited as text (nullable) here
);
CALL set_table_property('public.new_table', 'orientation', 'column');
CALL set_table_property('public.new_table', 'storage_format', 'orc');
CALL set_table_property('public.new_table', 'bitmap_columns', 'b');
CALL set_table_property('public.new_table', 'dictionary_encoding_columns', 'b:auto');
CALL set_table_property('public.new_table', 'time_to_live_in_seconds', '3153600000');
COMMENT ON TABLE public.new_table IS NULL;
END;

Copiar apenas o esquema (sem dados)

CREATE TABLE public.new_table AS TABLE public.src_table WITH NO DATA;

Ignorar se a tabela já existir

CREATE TABLE IF NOT EXISTS public.new_table AS TABLE public.src_table;

Se new_table já existir, a instrução não executa nenhuma ação e retorna:

NOTICE: relation "new_table" already exists, skipping

Criar a partir do resultado de uma consulta SELECT

CREATE TABLE public.new_table_2 AS SELECT * FROM public.src_table WHERE a = 1;

Copiar uma tabela particionada

Os exemplos a seguir usam esta tabela particionada de origem:

BEGIN;
CREATE TABLE public.src_table_partitioned (
    "a" int NOT NULL,
    "b" text,
    PRIMARY KEY (a)
) PARTITION BY LIST(a);

CREATE TABLE public.src_table_child1 PARTITION OF public.src_table_partitioned FOR VALUES IN (1);
CREATE TABLE public.src_table_child2 PARTITION OF public.src_table_partitioned FOR VALUES IN (2);
CREATE TABLE public.src_table_child3 PARTITION OF public.src_table_partitioned FOR VALUES IN (3);
COMMIT;

INSERT INTO src_table_child1 VALUES (1, 'aaa');
INSERT INTO src_table_child2 VALUES (2, 'bbb');
INSERT INTO src_table_child3 VALUES (3, 'ccc');

O comando CREATE TABLE AS sempre gera uma tabela não particionada. Ele não consegue replicar restrições de chave de particionamento ou relacionamentos de herança.

Copiar da tabela pai (inclui dados de todas as partições)

CREATE TABLE public.new_table_2 AS TABLE public.src_table_partitioned;

SELECT * FROM public.new_table_2;

 a |  b
---+-----
 2 | bbb
 1 | aaa
 3 | ccc

Copiar de uma única partição

CREATE TABLE public.new_table_3 AS TABLE public.src_table_child1;

Apenas os dados de src_table_child1 são copiados.

Criar a partir de uma consulta SELECT e definir propriedades da tabela

Crie uma tabela baseada no resultado de uma consulta SELECT e configure suas propriedades na mesma transação:

-- Create the source table
BEGIN;
CREATE TABLE public.src_table (
    "a" int8 NOT NULL,
    "b" text NOT NULL,
    PRIMARY KEY (a)
);
CALL SET_TABLE_PROPERTY('public.src_table', 'orientation', 'column');
COMMIT;

-- Create a new table from the SELECT result and configure properties
BEGIN;
CREATE TABLE public.new_table AS SELECT * FROM public.src_table;
CALL SET_TABLE_PROPERTY('public.new_table', 'bitmap_columns', 'b');
CALL SET_TABLE_PROPERTY('public.new_table', 'dictionary_encoding_columns', 'b:auto');
CALL SET_TABLE_PROPERTY('public.new_table', 'time_to_live_in_seconds', '3153600');
CALL SET_TABLE_PROPERTY('public.new_table', 'distribution_key', 'a');
COMMIT;

Hologres:CREATE TABLE AS