Hologres:INSERT OVERWRITE

Syntax

call hg_insert_overwrite('holo_tablename' regclass, ['partition_value' text|int|varchar|date], 'sql' text);

Parameter description

Examples

Example 1: Use a stored procedure to import data to a Hologres non-partitioned table

-- Create a table named tableA as the destination table in Hologres.

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

-- Use the hg_insert_overwrite function to import data from tableB into tableA.

call hg_insert_overwrite('public.tableA' , 'select * from public.tableB;');

Example 2: Use a stored procedure to import data into a Hologres partitioned table

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

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

-- Use the hg_insert_overwrite function to import data from tableB into tableA.

call hg_insert_overwrite('public.tableA' , '20201215',$$select * from public.tableB where ds='20201215'$$);

Example 3: Use a stored procedure to import data from a MaxCompute table into Hologres

For more information, see Use a stored procedure to import data into a Hologres internal table (Beta).

Syntax

You can execute the following SQL statements to import data by using a temporary table.

BEGIN ;

-- Delete an 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 into the temporary table.
INSERT INTO <table_new> [( <column> [, ...] )]
VALUES ( {<expression>}  [, ...] )
[, ...] | <query>}

ANALYZE <table_new>;

BEGIN ;

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

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

COMMIT ;

Parameter description

Examples

Example 1: Import data from MaxCompute to a non-partitioned table in Hologres

You can use the method described in this topic if you need to import data from a result table of offline processing in MaxCompute to an online service table in Hologres and overwrite all the data of the online service table. In this example, data is imported from the MaxCompute table named odps_region_10g to the region table in Hologres, and the imported data overwrites all the data of the region table in Hologres.

BEGIN ;

-- Delete an 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 ;

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

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

COMMIT ;

Example 2: Import data from MaxCompute to a partitioned table in Hologres

You can use the method described in this topic if you need to import the daily updated data from a partitioned table in MaxCompute to a partitioned table in Hologres and overwrite all the data of the specified partition in the partitioned Hologres table. This way, you can use offline data to correct real-time data. In this example, data is imported from the MaxCompute table named odps_lineitem_10g to the lineitem table in Hologres, and the imported data overwrites all the data of the specified partition in the lineitem table in Hologres. Both tables are partitioned by the day based on the ds field.

BEGIN ;

-- Delete an 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 ;

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

Example 3: Import data from Hologres to a non-partitioned table in MaxCompute

You can create a temporary table if you need to import data from Hologres to a non-partitioned table in MaxCompute. After data is imported, you need to rename the temporary table as the name of a regular table. In this example, data is imported from the Hologres table named holotable to the MaxCompute table named mc_holotable, and the imported data overwrites all the data of the MaxCompute table named mc_holotable.

-- Create a temporary table of 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;

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