Hologres:INSERT OVERWRITE

Feature description

• Hologres V3.1 and later support the native INSERT OVERWRITE syntax.

• The native INSERT OVERWRITE syntax applies to different types of tables:

  • Supports non-partitioned tables.

  • Supports child tables (processed as non-partitioned tables) of physical partitioned tables , but does not support parent tables of physical partitioned tables.

  • Supports logical partitioned tables, but partitions must be specified.

Limits

• The native INSERT OVERWRITE syntax enables mixed DML transactions by default: SET hg_experimental_enable_transaction = on;. For more information about the transaction capabilities of Hologres, see SQL transaction capabilities.

  • In the same transaction, INSERT OVERWRITE and DDL cannot be mixed.

  • In the same transaction, all DML statements are committed when the transaction is completed, that is, when COMMIT is executed.

• The native INSERT OVERWRITE syntax does not support generating binary logs for tables with binary logging enabled. You need to disable binary logging at the session level by using the SET hg_experimental_generate_binlog = off; command.

Command syntax

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

Parameters

Examples

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

-- Create a table named tableb as the source table.
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 import data from tableb to tablea.
INSERT OVERWRITE public.tablea SELECT * FROM public.tableb;

-- Create a table named tablea as the destination logical partitioned 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 a physical partitioned table named tableb as the source table.
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 import data from tableb to tablea.
INSERT OVERWRITE public.tablea PARTITION (ds = '20201215') SELECT * FROM public.tableb WHERE ds='20201215';

Feature description

• Starting from Hologres V3.1, Hologres supports logical partitioned tables. The stored procedure hg_insert_overwrite also supports logical partitioned tables, but partitions must be explicitly specified when using it.

• In Hologres V3.0, the capability of the hg_insert_overwrite stored procedure is enhanced. You can execute the INSERT OVERWRITE statement to import data to parent partitioned tables.

• Starting from Hologres V2.0.15, you can run the set hg_experimental_hg_insert_overwrite_enable_view=on; command to import data to tables with view dependencies. Importing data to tables with materialized view dependencies is not supported.

  Starting from Hologres V3.0, you can import data to tables with view dependencies without setting the preceding GUC parameter. Importing data to tables with materialized view dependencies is not supported.

• When data import by using the stored procedure hg_insert_overwrite fails, temporary tables may remain. Before Hologres V3.0, you need to manually clean up temporary tables. Starting from Hologres V3.0, you can clean up temporary tables by using the following SQL statement:

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

Limits

• If you want to import only specific fields, the fields that you want to import must be in the same sequence as and correspond to the fields in the source table.

• The hg_insert_overwrite operation requires the table owner permission to create a temporary table. Therefore, only the superuser and the table owner have the permissions to perform the hg_insert_overwrite operation.

• Partition key columns of destination tables can be of the INT, TEXT, or VARCHAR type.

• In Hologres V3.0 and later, you cannot use the hg_insert_overwrite stored procedure in transactions. Otherwise, an error message is reported.

  Note

  In earlier versions, using the stored procedure hg_insert_overwrite in transactions may cause potential issues such as deadlocks or hanging in specific situations. The later versions take stricter measures for this.

• Starting from Hologres V3.1, the number and data types of columns specified in the select_query of the stored procedure hg_insert_overwrite must strictly correspond to the columns of the target table. Otherwise, the following error message is reported: "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".

Behavior changes

Starting from Hologres V3.0, the stored procedure hg_insert_overwrite has the following behavior changes:

In versions earlier than Hologres V3.0, when there are only two input parameters, target_table and select_query, and the destination table is a parent partitioned table, an error is directly reported. Starting from Hologres V3.0, the following situations may occur:

• When all the child partitioned tables corresponding to the execution result of select_query exist, the write operation is successful.

• When a child partitioned table corresponding to the execution result of select_query does not exist, an error may be reported.

Command syntax

-- Syntax of the hg_insert_overwrite stored procedure before V3.0
CALL hg_insert_overwrite('<target_table>' regclass, ['<partition_value>' TEXT], '<sql>' TEXT);

-- Syntax of the hg_insert_overwrite stored procedure in Hologres V3.0 and later
CALL hg_insert_overwrite('<target_table>' regclass, ['<partition_value>' ARRAY], '<sql>' TEXT, ['<auto_create_partition>' BOOLEAN]);

Parameters

Starting from V3.0, for INSERT OVERWRITE operations on parent partitioned tables, that is, when target_table is a parent partitioned table, the behaviors of different parameter settings are as follows:

• Physical partitioned table

  Parameter value		auto_create_partition
  		TRUE	FALSE
  partition_value	Not specified	All target_table partitions corresponding to execution results of sql are overwritten with data. If a partition does not exist, the system automatically creates the partition. target_table partitions that are not related to sql execution results are ignored.	In the sql execution results, if all the corresponding target_table partitions exist: All target_table partitions corresponding to the execution results are overwritten with data. target_table partitions that are not related to the execution results are ignored. In the sql execution results, if some corresponding target_table partitions do not exist, the system returns an error message and does not overwrite data in any partition.
  	Specified	For target_table partitions specified by partition_value: If a partition does not exist, the system automatically creates the partition. For partitions corresponding to the sql execution results, data is overwritten. For partitions that are not related to the sql execution results, the partitions are cleared. For target_table partitions not specified by partition_value: If the sql execution results contain unspecified partitions, no processing is performed. For partitions that are not related to the sql execution results, no processing is performed.	For target_table partitions specified by partition_value: If a partition does not exist, the system returns an error message and does not overwrite data in any partition. For partitions corresponding to the sql execution results, data is overwritten. For partitions that are not related to the sql execution results, the partitions are cleared. For target_table partitions not specified by partition_value: If the sql execution results contain unspecified partitions, no processing is performed. For partitions that are not related to the sql execution results, no processing is performed.

• Logical partitioned table:

  Logical partitioned tables do not involve automatic partition creation (auto_create_partition). This parameter is ignored.

  Parameter value		Description
  partition_value	Not specified	Not supported.
  	Specified	For target_table partitions specified by partition_value: For partitions corresponding to the sql execution results, data is overwritten. For partitions that are not related to the sql execution results, the partitions are cleared. For target_table partitions not specified by partition_value: If the sql execution results contain unspecified partitions, no processing is performed. For partitions that are not related to the sql execution results, no processing is performed.

Examples

-- Create a table named tablea as the destination 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 a table named tableb as the source table.
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');

-- Call the hg_insert_overwrite stored procedure to import data from tableb to tablea.
CALL hg_insert_overwrite('public.tablea' , 'SELECT * FROM public.tableb');

-- Create a table named tableA as the destination 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 create 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 a table named tableB as the source table.
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. In this example, the customer table in the MaxCompute public dataset named public_data is used. The following sample code shows the DDL statement that is used to create the customer 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 for mapping to the source 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 to receive data from the MaxCompute source table. In this example, a column-oriented table is created.
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 to 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 a period of time before you import data to Hologres. Otherwise, the import operation may fail due to the latency that is caused by the update of metadata.

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

-- Query the data of 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 partitioned table.
ALTER TABLE odps_sale_detail ADD IF NOT EXISTS PARTITION(sale_date='20210815')
;

-- Import data to the 20210815 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 for mapping to the 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 table.
DROP TABLE IF EXISTS holo_sale_detail;

-- Create a partitioned table (an 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 to Hologres.
CALL hg_insert_overwrite('holo_sale_detail', '20210815', $$SELECT * FROM public.odps_sale_detail WHERE sale_date='20210815'$$);

-- Query the data of 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 partitioned 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')
;

-- Insert data into 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 for mapping to the source 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 table.
DROP TABLE IF EXISTS holo_sale_detail;

-- Create a partitioned table (an 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 to Hologres. No partition is specified, and the auto_create_partition parameter is set to TRUE. In this case, the system automatically creates two partitions and imports data to the partitions.
CALL hg_insert_overwrite ('holo_sale_detail', $$SELECT * FROM public.odps_sale_detail$$, TRUE);

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

Command syntax

You can execute the following SQL statements to implement INSERT OVERWRITE.

BEGIN ;

-- Drop the existing temporary table.
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 ;

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

ANALYZE <table_new>;

BEGIN ;

-- Drop the original table.
DROP TABLE IF EXISTS  <table>;

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

COMMIT ;

Parameters

Examples

BEGIN ;

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

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

ANALYZE public.region_new;

BEGIN ;

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

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

COMMIT ;

BEGIN ;

-- Drop the existing temporary table.
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 ;

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

ANALYZE public.lineitem_new_20210101;

BEGIN ;

-- Drop the original 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 partitioned Hologres table.
ALTER TABLE public.lineitem ATTACH PARTITION lineitem_20210101 FOR VALUES IN ('20210101');

COMMIT ;

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

-- Create the mapping to 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 original table in Hologres.
UPDATE holotable SET "job" = 'president' WHERE "name" = 'Lily';
-- Write the updated data to the mapping table for the temporary table.
INSERT INTO mapping_holotable_temp SELECT * FROM holotable;

-- Drop the original destination table in MaxCompute.
DROP TABLE IF EXISTS mc_holotable;
-- Rename the temporary table as the name of the destination table.
ALTER TABLE mc_holotable_temp RENAME TO mc_holotable;