Hologres:INSERT OVERWRITE

Visão geral

• O INSERT OVERWRITE nativo está disponível a partir do Hologres V3.1.

• O INSERT OVERWRITE nativo oferece suporte aos seguintes tipos de tabela:

  • Tabelas não particionadas.

  • Tabelas filhas de tabelas particionadas fisicamente (tratadas como tabelas não particionadas).

  • Tabelas particionadas logicamente, com uma partição especificada.

Restrições

• Por padrão, o INSERT OVERWRITE nativo ativa transações DML mistas: SET hg_experimental_enable_transaction = on;. Para obter informações sobre os recursos de transação no Hologres, consulte SQL transactions.

  • Não é possível misturar instruções INSERT OVERWRITE e DDL na mesma transação.

  • Todas as instruções DML em uma transação são confirmadas somente quando o COMMIT é executado.

• Antes do Hologres V4.0, o INSERT OVERWRITE nativo não gera binary logs para tabelas com binary logging ativado. A partir do Hologres V4.0, essa sintaxe passa a gerar binary logs. A geração de binary logs pode adicionar uma grande sobrecarga de armazenamento, portanto, recomendamos desativá-la, a menos que seja necessária. Para desativar a geração de binary logs no nível da sessão, execute o seguinte comando: SET hg_experimental_generate_binlog = off;.

• A atomicidade de leitura é garantida por padrão, mas incorre em maior sobrecarga de metadados e pode aumentar a latência do DQL (os virtual warehouses secundários são mais afetados). Operações INSERT OVERWRITE de longa duração podem causar falhas de leitura, como Data version is inconsistent ou Insert overwrite version not match.

  • Se a atomicidade de leitura não for necessária, desative o GUC para tarefas DQL: set hg_experimental_enable_check_data_version=off. Dessa forma, uma tarefa DQL poderá ler uma mistura de arquivos de dados de antes e depois da operação INSERT OVERWRITE.

  • Se a atomicidade de leitura for necessária, mas a latência do DQL precisar ser reduzida, execute as tarefas DQL no virtual warehouse primário.

Sintaxe

INSERT OVERWRITE <target_table_name> 
  [ PARTITION (<partition_key> = '<partition_value>') [, ...]]
  VALUES ( <expression>  [, ...] ) [, ...] | <query>;

Parâmetros

Exemplos

-- Create table A as the target table.
CREATE TABLE public.tablea (
    cid INTEGER NOT NULL,
    cname TEXT,
    code INTEGER
    ,PRIMARY KEY (cid)
);

-- Create table B as the source.
CREATE TABLE public.tableb (
    cid INTEGER NOT NULL,
    cname TEXT,
    code INTEGER
    ,PRIMARY KEY (cid)
);

INSERT INTO public.tableb VALUES(1,'aaa',10001),(2,'bbb','10002');

-- Use the native INSERT OVERWRITE syntax to insert data from table B into table A.
INSERT OVERWRITE public.tablea SELECT * FROM public.tableb;

-- Create table A as the target table.
CREATE TABLE public.tablea(
  a TEXT , 
  b INT, 
  c TIMESTAMP, 
  d TEXT,
  ds TEXT,
  PRIMARY KEY(ds,b)
  )
  LOGICAL PARTITION BY LIST(ds);

-- Create physical partitioned table B for the input.
BEGIN;
CREATE TABLE public.tableb(
  a TEXT, 
  b INT, 
  c TIMESTAMP, 
  d TEXT,
  ds TEXT,
  PRIMARY KEY(ds,b)
  )
  PARTITION BY LIST(ds);
CREATE TABLE public.holo_child_3a PARTITION OF public.tableb FOR VALUES IN('20201215');
CREATE TABLE public.holo_child_3b PARTITION OF public.tableb FOR VALUES IN('20201216');
CREATE TABLE public.holo_child_3c PARTITION OF public.tableb FOR VALUES IN('20201217');
COMMIT;

INSERT INTO public.holo_child_3a VALUES('a',1,'2034-10-19','a','20201215');
INSERT INTO public.holo_child_3b VALUES('b',2,'2034-10-20','b','20201216');
INSERT INTO public.holo_child_3c VALUES('c',3,'2034-10-21','c','20201217');

-- Use the native INSERT OVERWRITE syntax to insert data from table B into table A.
INSERT OVERWRITE public.tablea PARTITION (ds = '20201215') SELECT * FROM public.tableb WHERE ds='20201215';

Histórico de versões

• A partir da V3.1, o hg_insert_overwrite oferece suporte a tabelas particionadas logicamente (a partição deve ser especificada).

• A partir da V3.0, o hg_insert_overwrite oferece suporte à importação direta para tabelas pai.

• Tabelas com visualizações (views) dependentes: para V2.0.15+, ative com: set hg_experimental_hg_insert_overwrite_enable_view=on;. Tabelas com dependências de Materialized View não são suportadas.

  Para V3.0+, nenhuma configuração é necessária, mas as dependências de Materialized View continuam sem suporte.

• Se o hg_insert_overwrite falhar, limpe as tabelas temporárias manualmente. A partir da V3.0, use o seguinte SQL.

  -- Delete the temporary tables that were created by the system before the time specified by before_time.
  CALL hg_clean_insert_overwrite_tmp_tables(before_time::timestamptz); 

Restrições de uso

• As importações de um subconjunto de campos devem corresponder à ordem das colunas da tabela de origem.

• O hg_insert_overwrite requer permissão de proprietário da tabela (apenas superusuários ou proprietários da tabela).

• A chave de partição oferece suporte aos tipos INT, TEXT ou VARCHAR.

• A partir da V3.0, o hg_insert_overwrite não pode ser usado dentro de transações.

  Nota

  Em versões anteriores, o uso do hg_insert_overwrite em uma transação apresentava risco de deadlocks ou travamentos.

• A partir da V3.0, a contagem de colunas e os tipos de dados no sql do hg_insert_overwrite devem corresponder exatamente ao target_table. Incompatibilidades geram erros como "error: table "hg_alias" has x columns available but x columns specified" or "error: column xx is of type xxx but expression is of type xxx".

Mudanças de comportamento

Se apenas o target_table (uma tabela pai) e o sql forem fornecidos:

Antes da V3.0, essa operação falhava. A partir da V3.0, os resultados possíveis são:

• Se todas as tabelas filhas nos resultados do sql existirem, a operação será bem-sucedida.

• Se alguma tabela filha não existir, ocorrerá um erro.

Sintaxe

-- Before V3.0
CALL hg_insert_overwrite('<target_table>' regclass, ['<partition_value>' TEXT], '<sql>' TEXT);

-- For Hologres V3.0 and later
CALL hg_insert_overwrite('<target_table>' regclass, ['<partition_value>' ARRAY], '<sql>' TEXT, ['<auto_create_partition>' BOOLEAN]);

Parâmetros

O comportamento depende de auto_create_partition e partition_value. Uma partição é considerada "relevante" se aparecer nos resultados do SQL.

• Comportamento do hg_insert_overwrite para tabelas particionadas fisicamente

  Valor do parâmetro		auto_create_partition
  		TRUE	FALSE
  partition_value	Não especificado	Partições relevantes: as partições existentes são sobrescritas. Partições inexistentes são criadas automaticamente. Partições irrelevantes: ignoradas.	Se todas as partições relevantes já existirem: Partições relevantes são sobrescritas. Partições irrelevantes são ignoradas. Se alguma partição relevante não existir, um erro será gerado; nenhuma operação de sobrescrita ocorrerá em outras partições.
  	Especificado	Para partições especificadas: Partições relevantes inexistentes são criadas automaticamente. Partições relevantes existentes são sobrescritas. Os dados de partições irrelevantes e existentes são limpos. Para partições não especificadas: Nenhum efeito.	Para partições especificadas: Se alguma partição relevante não existir, um erro será gerado; nenhuma operação de sobrescrita ocorrerá em outras partições. Partições relevantes existentes são sobrescritas. Partições existentes e irrelevantes são limpas. Para partições não especificadas: Nenhum efeito.

• Comportamento do hg_insert_overwrite para tabelas particionadas logicamente

  Tabelas particionadas logicamente não oferecem suporte ao particionamento automático; o auto_create_partition é ignorado.

  Valor do parâmetro		Descrição
  partition_value	Não especificado	Não suportado.
  	Especificado	Para partições especificadas: Os dados da partição relevante são sobrescritos. Partições irrelevantes são limpas. Para partições não especificadas: Nenhuma é afetada.

Exemplos

-- Create table A as the target table.
BEGIN;

CREATE TABLE public.tablea (
    cid INTEGER NOT NULL,
    cname TEXT,
    code INTEGER
    ,PRIMARY KEY (cid)
);

CALL set_table_property('public.tablea', 'orientation', 'column');
CALL set_table_property('public.tablea', 'storage_format', 'orc');
CALL set_table_property('public.tablea', 'bitmap_columns', 'cname');
CALL set_table_property('public.tablea', 'dictionary_encoding_columns', 'cname:auto');
CALL set_table_property('public.tablea', 'distribution_key', 'cid');
CALL set_table_property('public.tablea', 'time_to_live_in_seconds', '3153600000');
COMMIT;

-- Create table B for data input.
CREATE TABLE public.tableb (
    cid INTEGER NOT NULL,
    cname TEXT,
    code INTEGER
    ,PRIMARY KEY (cid)
);

INSERT INTO public.tableb VALUES(1,'aaa',10001),(2,'bbb','10002');

-- Insert data from table B into table A.
CALL hg_insert_overwrite('public.tablea' , 'SELECT * FROM public.tableb');

-- Create the target table.
BEGIN;
CREATE TABLE public.tableA(
  a TEXT, 
  b INT, 
  c TIMESTAMP, 
  d TEXT,
  ds TEXT,
  PRIMARY KEY(ds,b)
  )
  PARTITION BY LIST(ds);
CALL set_table_property('public.tableA', 'orientation', 'column');
CREATE TABLE public.holo_child_1 PARTITION OF public.tableA FOR VALUES IN('20201215');
CREATE TABLE public.holo_child_2 PARTITION OF public.tableA FOR VALUES IN('20201216');
CREATE TABLE public.holo_child_3 PARTITION OF public.tableA FOR VALUES IN('20201217');
COMMIT;

-- Or, a logical partitioned table.
CREATE TABLE public.tableA_lp(
  a TEXT, 
  b INT, 
  c TIMESTAMP, 
  d TEXT,
  ds TEXT,
  PRIMARY KEY(ds,b)
  )
  LOGICAL PARTITION BY LIST(ds);

-- Create table B for input.
BEGIN;
CREATE TABLE public.tableB(
  a TEXT, 
  b INT, 
  c TIMESTAMP, 
  d TEXT,
  ds TEXT,
  PRIMARY KEY(ds,b)
  )
  PARTITION BY LIST(ds);
CALL set_table_property('public.tableB', 'orientation', 'column');
CREATE TABLE public.holo_child_3a PARTITION OF public.tableB FOR VALUES IN('20201215');
CREATE TABLE public.holo_child_3b PARTITION OF public.tableB FOR VALUES IN('20201216');
CREATE TABLE public.holo_child_3c PARTITION OF public.tableB FOR VALUES IN('20201217');
COMMIT;

INSERT INTO public.holo_child_3a VALUES('a',1,'2034-10-19','a','20201215');
INSERT INTO public.holo_child_3b VALUES('b',2,'2034-10-20','b','20201216');
INSERT INTO public.holo_child_3c VALUES('c',3,'2034-10-21','c','20201217');

-- Physical partitioned table
CALL hg_insert_overwrite('public.tableA' , '{20201215,20201216,20201217}'::text[],$$SELECT * FROM public.tableB$$);
-- Logical partitioned table
CALL hg_insert_overwrite('public.tableA_lp' , '{20201215,20201216,20201217}'::text[],$$SELECT * FROM public.tableB$$);

-- Create a non-partitioned table in MaxCompute. This example uses data from the customer table in the public_data project, which is a public dataset in MaxCompute. The following code provides the DDL statement for the table.
CREATE TABLE IF NOT EXISTS public_data.customer(
  c_customer_sk BIGINT,
  c_customer_id STRING,
  c_current_cdemo_sk BIGINT,
  c_current_hdemo_sk BIGINT,
  c_current_addr_sk BIGINT,
  c_first_shipto_date_sk BIGINT,
  c_first_sales_date_sk BIGINT,
  c_salutation STRING,
  c_first_name STRING,
  c_last_name STRING,
  c_preferred_cust_flag STRING,
  c_birth_day BIGINT,
  c_birth_month BIGINT,
  c_birth_year BIGINT,
  c_birth_country STRING,
  c_login STRING,
  c_email_address STRING,
  c_last_review_date STRING,
  useless STRING);

-- Create a foreign table in Hologres to map to the source data table in MaxCompute.
CREATE FOREIGN TABLE customer (
    "c_customer_sk" INT8,
    "c_customer_id" TEXT,
    "c_current_cdemo_sk" INT8,
    "c_current_hdemo_sk" INT8,
    "c_current_addr_sk" INT8,
    "c_first_shipto_date_sk" INT8,
    "c_first_sales_date_sk" INT8,
    "c_salutation" TEXT,
    "c_first_name" TEXT,
    "c_last_name" TEXT,
    "c_preferred_cust_flag" TEXT,
    "c_birth_day" INT8,
    "c_birth_month" INT8,
    "c_birth_year" INT8,
    "c_birth_country" TEXT,
    "c_login" TEXT,
    "c_email_address" TEXT,
    "c_last_review_date" TEXT,
    "useless" TEXT
)
SERVER odps_server
OPTIONS (project_name 'public_data', table_name 'customer');

-- Create an internal table in Hologres, such as a column-oriented table, to receive data from the MaxCompute source table.
BEGIN;
CREATE TABLE public.holo_customer (
 "c_customer_sk" INT8,
 "c_customer_id" TEXT,
 "c_current_cdemo_sk" INT8,
 "c_current_hdemo_sk" INT8,
 "c_current_addr_sk" INT8,
 "c_first_shipto_date_sk" INT8,
 "c_first_sales_date_sk" INT8,
 "c_salutation" TEXT,
 "c_first_name" TEXT,
 "c_last_name" TEXT,
 "c_preferred_cust_flag" TEXT,
 "c_birth_day" INT8,
 "c_birth_month" INT8,
 "c_birth_year" INT8,
 "c_birth_country" TEXT,
 "c_login" TEXT,
 "c_email_address" TEXT,
 "c_last_review_date" TEXT,
 "useless" TEXT
);
COMMIT;

-- Import data into Hologres.
IMPORT FOREIGN SCHEMA <project_name> LIMIT TO
(customer) FROM server odps_server INTO PUBLIC options(if_table_exist 'update');-- Update the foreign table.
SELECT pg_sleep(30);-- Wait for some time before you import data to Hologres. This prevents synchronization failures that are caused by data inconsistency due to slow updates of the metadata cache in Hologres.

CALL  hg_insert_overwrite('holo_customer', 'SELECT * FROM customer where c_birth_year > 1980');

-- Query data from the MaxCompute source table in Hologres.
SELECT * FROM holo_customer limit 10;

-- Create a partitioned table in MaxCompute.
DROP TABLE IF EXISTS odps_sale_detail;

CREATE TABLE IF NOT EXISTS odps_sale_detail 
(
    shop_name STRING
    ,customer_id STRING
    ,total_price DOUBLE
)
PARTITIONED BY 
(
    sale_date STRING
)
;

-- Add the 20210815 partition to the source table.
ALTER TABLE odps_sale_detail ADD IF NOT EXISTS PARTITION(sale_date='20210815')
;

-- Write data to the partition.
INSERT OVERWRITE TABLE odps_sale_detail PARTITION(sale_date='20210815') VALUES 
('s1','c1',100.1),
('s2','c2',100.2),
('s3','c3',100.3)
;

-- Create a foreign table in Hologres to map to the source data table in MaxCompute.
DROP FOREIGN TABLE IF EXISTS odps_sale_detail;

-- Create a foreign table.
IMPORT FOREIGN SCHEMA <maxcompute_project> LIMIT TO
(
    odps_sale_detail
) 
FROM SERVER odps_server INTO public 
OPTIONS(if_table_exist 'error',if_unsupported_type 'error');

-- Create an internal table in Hologres to receive data from the MaxCompute source table.
DROP TABLE IF EXISTS holo_sale_detail;

-- Create a partitioned table (internal table) in Hologres.
BEGIN ;
CREATE TABLE IF NOT EXISTS holo_sale_detail
(
    shop_name TEXT
    ,customer_id TEXT 
    ,total_price FLOAT8
    ,sale_date TEXT
)
PARTITION BY LIST(sale_date);
COMMIT;

-- Import data into Hologres.
CALL hg_insert_overwrite('holo_sale_detail', '20210815', $$SELECT * FROM public.odps_sale_detail WHERE sale_date='20210815'$$);

-- Query data from the MaxCompute source table in Hologres.
SELECT * FROM holo_sale_detail;

-- Create a partitioned table in MaxCompute.
DROP TABLE IF EXISTS odps_sale_detail;

CREATE TABLE IF NOT EXISTS odps_sale_detail 
(
    shop_name STRING
    ,customer_id STRING
    ,total_price DOUBLE
)
PARTITIONED BY 
(
    sale_date STRING
)
;

-- Add the 20210815 and 20210816 partitions to the source table.
ALTER TABLE odps_sale_detail ADD IF NOT EXISTS PARTITION(sale_date='20210815')
;
ALTER TABLE odps_sale_detail ADD IF NOT EXISTS PARTITION(sale_date='20210816')
;

-- Write data to the partitions.
INSERT OVERWRITE TABLE odps_sale_detail PARTITION(sale_date='20210815') VALUES 
('s1','c1',100.1),
('s2','c2',100.2),
('s3','c3',100.3)
;
INSERT OVERWRITE TABLE odps_sale_detail PARTITION(sale_date='20210816') VALUES 
('s1','c1',100.1),
('s2','c2',100.2),
('s3','c3',100.3)
;

-- Create a foreign table in Hologres to map to the source data table in MaxCompute.
DROP FOREIGN TABLE IF EXISTS odps_sale_detail;

-- Create a foreign table.
IMPORT FOREIGN SCHEMA <maxcompute_project> LIMIT TO
(
    odps_sale_detail
) 
FROM SERVER odps_server INTO public 
OPTIONS(if_table_exist 'error',if_unsupported_type 'error');

-- Create an internal table in Hologres to receive data from the MaxCompute source table.
DROP TABLE IF EXISTS holo_sale_detail;

-- Create a partitioned table (internal table) in Hologres.
BEGIN ;
CREATE TABLE IF NOT EXISTS holo_sale_detail
(
    shop_name TEXT
    ,customer_id TEXT 
    ,total_price FLOAT8
    ,sale_date TEXT
)
PARTITION BY LIST(sale_date);
COMMIT;

-- Import data into Hologres. Do not specify child partitions and set auto_create_partition to TRUE. The system automatically creates two child partitions and imports data.
CALL hg_insert_overwrite ('holo_sale_detail', $$SELECT * FROM public.odps_sale_detail$$, TRUE);

-- Query data in Hologres.
SELECT * FROM holo_sale_detail;

Sintaxe

Use o seguinte SQL para implementar o INSERT OVERWRITE.

BEGIN ;

-- Clear potential temporary tables.
DROP TABLE IF EXISTS <table_new>;

-- Create a temporary table.
SET hg_experimental_enable_create_table_like_properties=on;
CALL HG_CREATE_TABLE_LIKE ('<table_new>', 'select * from <table>');

COMMIT ;

-- Insert data into the temporary table.
INSERT INTO <table_new> [( <column> [, ...] )]
VALUES ( {<expression>}  [, ...] )
[, ...] | <query>}

ANALYZE <table_new>;

BEGIN ;

-- Delete the old table.
DROP TABLE IF EXISTS  <table>;

-- Rename the temporary table.
ALTER TABLE <table_new> RENAME TO <table>;

COMMIT ;

Parâmetros

Exemplos de interação com o MaxCompute

BEGIN ;

-- Clear the existing temporary table.
DROP TABLE IF EXISTS public.region_new;

-- Create a temporary table.
SET hg_experimental_enable_create_table_like_properties=on;
CALL HG_CREATE_TABLE_LIKE ('public.region_new', 'select * from public.region');
COMMIT ;

-- Insert data into the temporary table.
INSERT INTO public.region_new
SELECT *
FROM public.odps_region_10g;

ANALYZE public.region_new;

BEGIN ;

-- Drop the old table.
DROP TABLE IF EXISTS public.region;

-- Rename the temporary table.
ALTER TABLE IF EXISTS public.region_new RENAME TO region;

COMMIT ;

BEGIN ;

-- Clear potential temporary tables.
DROP TABLE IF EXISTS public.lineitem_new_20210101;

-- Create a temporary table.
SET hg_experimental_enable_create_table_like_properties=on;
CALL HG_CREATE_TABLE_LIKE ('public.lineitem_new_20210101', 'select * from public.lineitem');
COMMIT ;

-- Insert data into the temporary table.
INSERT INTO public.lineitem_new_20210101
SELECT *
FROM public.odps_lineitem_10g
WHERE DS = '20210101'

ANALYZE public.lineitem_new_20210101;

BEGIN ;

-- Delete the old partition.
DROP TABLE IF EXISTS public.lineitem_20210101;

-- Rename the temporary table.
ALTER TABLE public.lineitem_new_20210101 RENAME TO lineitem_20210101;

-- Attach the temporary table to the specified partitioned table.
ALTER TABLE public.lineitem ATTACH PARTITION lineitem_20210101 FOR VALUES IN ('20210101');

COMMIT ;

-- Create a temporary table for the sink table in MaxCompute.
CREATE  TABLE if not exists mc_holotable_temp(
    age INT,
    job STRING,
    name STRING
);

-- Create a mapping for the temporary table in Hologres.
CREATE FOREIGN TABLE "public"."mapping_holotable_temp" (
 "age" INT,
 "job" TEXT,
 "name" TEXT
)
SERVER odps_server
OPTIONS (project_name 'DLF_test',table_name 'mc_holotable_temp');
-- Update the source table in Hologres.
UPDATE holotable SET "job" = 'president' WHERE "name" = 'Lily';
-- Write the updated data to the mapping of the temporary table.
INSERT INTO mapping_holotable_temp SELECT * FROM holotable;

-- Delete the old sink table in MaxCompute.
DROP TABLE IF EXISTS mc_holotable;
-- Rename the temporary table to the sink table.
ALTER TABLE mc_holotable_temp RENAME TO mc_holotable;