Hologres:INSERT OVERWRITE

Ikhtisar

• Sintaks native INSERT OVERWRITE tersedia mulai Hologres V3.1.

• Sintaks native INSERT OVERWRITE mendukung jenis tabel berikut:

  • Tabel non-partisi.

  • Tabel anak dari tabel partisi fisik (diperlakukan sebagai tabel non-partisi).

  • Tabel partisi logis, dengan partisi yang ditentukan.

Batasan

• Sintaks native INSERT OVERWRITE secara default mengaktifkan transaksi DML campuran: SET hg_experimental_enable_transaction = on;. Untuk informasi tentang kemampuan transaksi di Hologres, lihat SQL transactions.

  • Pernyataan INSERT OVERWRITE dan DDL tidak dapat digabung dalam satu transaksi yang sama.

  • Semua pernyataan DML dalam suatu transaksi hanya akan dikomit saat perintah COMMIT dieksekusi.

• Sebelum Hologres V4.0, sintaks native INSERT OVERWRITE tidak menghasilkan log biner untuk tabel yang telah mengaktifkan binary logging. Mulai Hologres V4.0, sintaks ini menghasilkan log biner. Pembuatan log biner dapat menambah beban penyimpanan yang besar, sehingga kami menyarankan Anda menonaktifkannya kecuali jika benar-benar diperlukan. Untuk menonaktifkan pembuatan log biner pada tingkat sesi, jalankan perintah berikut: SET hg_experimental_generate_binlog = off;.

• Atomicitas baca dijamin secara default tetapi menimbulkan overhead metadata yang lebih tinggi dan dapat meningkatkan latensi DQL (gudang virtual secondary lebih terpengaruh). Operasi INSERT OVERWRITE yang berjalan lama dapat menyebabkan kegagalan baca seperti Data version is inconsistent atau Insert overwrite version not match.

  • Jika atomicitas baca tidak diperlukan, nonaktifkan GUC untuk tugas DQL: set hg_experimental_enable_check_data_version=off. Tugas DQL kemudian mungkin memindai campuran file data dari sebelum dan sesudah operasi INSERT OVERWRITE.

  • Jika atomicitas baca diperlukan tetapi latensi DQL harus dikurangi, jalankan tugas DQL pada gudang virtual primary.

Sintaks

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

Parameter

Contoh

-- Buat tabel A sebagai tabel target.
CREATE TABLE public.tablea (
    cid INTEGER NOT NULL,
    cname TEXT,
    code INTEGER
    ,PRIMARY KEY (cid)
);

-- Buat tabel B sebagai sumber.
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');

-- Gunakan sintaks native INSERT OVERWRITE untuk memasukkan data dari tabel B ke tabel A.
INSERT OVERWRITE public.tablea SELECT * FROM public.tableb;

-- Buat tabel A sebagai tabel target.
CREATE TABLE public.tablea(
  a TEXT , 
  b INT, 
  c TIMESTAMP, 
  d TEXT,
  ds TEXT,
  PRIMARY KEY(ds,b)
  )
  LOGICAL PARTITION BY LIST(ds);

-- Buat tabel partisi fisik B untuk 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');

-- Gunakan sintaks native INSERT OVERWRITE untuk memasukkan data dari tabel B ke tabel A.
INSERT OVERWRITE public.tablea PARTITION (ds = '20201215') SELECT * FROM public.tableb WHERE ds='20201215';

Riwayat versi

• Mulai V3.1, hg_insert_overwrite mendukung tabel partisi logis (partisi harus ditentukan).

• Mulai V3.0, hg_insert_overwrite mendukung impor langsung ke tabel induk.

• Tabel dengan dependensi view: Untuk V2.0.15+, aktifkan dengan: set hg_experimental_hg_insert_overwrite_enable_view=on;. Tabel dengan dependensi Materialized View tidak didukung.

  Untuk V3.0+, tidak diperlukan konfigurasi tambahan, tetapi dependensi Materialized View tetap tidak didukung.

• Jika hg_insert_overwrite gagal, bersihkan tabel temporary secara manual. Mulai V3.0, gunakan SQL berikut.

  -- Hapus tabel temporary yang dibuat oleh sistem sebelum waktu yang ditentukan oleh before_time.
  CALL hg_clean_insert_overwrite_tmp_tables(before_time::timestamptz); 

Batasan penggunaan

• Impor subset field harus sesuai dengan urutan kolom tabel sumber.

• hg_insert_overwrite memerlukan izin pemilik tabel (hanya superuser atau pemilik tabel).

• Kunci partisi mendukung tipe INT, TEXT, atau VARCHAR.

• Mulai V3.0, hg_insert_overwrite tidak dapat digunakan di dalam transaksi.

  Catatan

  Pada versi sebelumnya, penggunaan hg_insert_overwrite dalam transaksi berisiko menyebabkan deadlock atau hang.

• Mulai V3.0, jumlah kolom dan tipe data dalam sql dari hg_insert_overwrite harus persis sesuai dengan target_table. Ketidaksesuaian akan menghasilkan error seperti "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".

Perubahan perilaku

Jika hanya target_table (tabel induk) dan sql diberikan:

Sebelum V3.0, operasi ini gagal. Mulai V3.0, kemungkinan hasilnya:

• Jika semua tabel anak dalam hasil sql ada, operasi berhasil.

• Jika ada tabel anak yang tidak ada, akan terjadi error.

Sintaks

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

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

Parameter

Perilaku bergantung pada auto_create_partition dan partition_value. Partisi dianggap "relevan" jika muncul dalam hasil SQL.

• Perilaku hg_insert_overwrite untuk tabel partisi fisik

  Nilai parameter		auto_create_partition
  		TRUE	FALSE
  partition_value	Tidak ditentukan	Partisi relevan: Partisi yang ada ditimpa. Partisi yang tidak ada dibuat secara otomatis. Partisi tidak relevan: Diabaikan.	Jika semua partisi relevan sudah ada: Partisi relevan ditimpa. Partisi tidak relevan diabaikan. Jika ada partisi relevan yang tidak ada, error dilempar; tidak ada operasi timpa yang dilakukan pada partisi lain.
  	Ditentukan	Untuk partisi yang ditentukan: Partisi relevan yang tidak ada dibuat secara otomatis. Partisi relevan yang ada ditimpa. Data partisi yang ada tetapi tidak relevan dikosongkan. Untuk partisi yang tidak ditentukan: Tidak berpengaruh.	Untuk partisi yang ditentukan: Jika ada partisi relevan yang tidak ada, error dilempar; tidak ada operasi timpa yang dilakukan pada partisi lain. Partisi relevan yang ada ditimpa. Partisi yang ada tetapi tidak relevan dikosongkan. Untuk partisi yang tidak ditentukan: Tidak berpengaruh.

• Perilaku hg_insert_overwrite untuk tabel partisi logis

  Tabel partisi logis tidak mendukung partisi otomatis; auto_create_partition diabaikan.

  Nilai parameter		Deskripsi
  partition_value	Tidak ditentukan	Tidak didukung.
  	Ditentukan	Untuk partisi yang ditentukan: Data partisi relevan ditimpa. Partisi tidak relevan dikosongkan. Untuk partisi yang tidak ditentukan: Tidak ada yang terpengaruh.

Contoh

-- Buat tabel A sebagai tabel target.
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;

-- Buat tabel B untuk input data.
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');

-- Masukkan data dari tabel B ke tabel A.
CALL hg_insert_overwrite('public.tablea' , 'SELECT * FROM public.tableb');

-- Buat tabel target.
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;

-- Atau, tabel partisi logis.
CREATE TABLE public.tableA_lp(
  a TEXT, 
  b INT, 
  c TIMESTAMP, 
  d TEXT,
  ds TEXT,
  PRIMARY KEY(ds,b)
  )
  LOGICAL PARTITION BY LIST(ds);

-- Buat tabel B untuk 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');

-- Tabel partisi fisik
CALL hg_insert_overwrite('public.tableA' , '{20201215,20201216,20201217}'::text[],$$SELECT * FROM public.tableB$$);
-- Tabel partisi logis
CALL hg_insert_overwrite('public.tableA_lp' , '{20201215,20201216,20201217}'::text[],$$SELECT * FROM public.tableB$$);

-- Buat tabel non-partisi di MaxCompute. Contoh ini menggunakan data dari tabel customer di proyek public_data, yang merupakan dataset publik di MaxCompute. Kode berikut menyediakan pernyataan DDL untuk tabel tersebut.
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);

-- Buat tabel eksternal di Hologres untuk memetakan ke tabel data sumber di 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');

-- Buat tabel internal di Hologres, seperti tabel berorientasi kolom, untuk menerima data dari tabel sumber MaxCompute.
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;

-- Impor data ke Hologres.
IMPORT FOREIGN SCHEMA <project_name> LIMIT TO
(customer) FROM server odps_server INTO PUBLIC options(if_table_exist 'update');-- Perbarui tabel eksternal.
SELECT pg_sleep(30);-- Tunggu beberapa saat sebelum mengimpor data ke Hologres. Ini mencegah kegagalan sinkronisasi yang disebabkan oleh inkonsistensi data akibat pembaruan cache metadata Hologres yang lambat.

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

-- Kueri data dari tabel sumber MaxCompute di Hologres.
SELECT * FROM holo_customer limit 10;

-- Buat tabel partisi di 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
)
;

-- Tambahkan partisi 20210815 ke tabel sumber.
ALTER TABLE odps_sale_detail ADD IF NOT EXISTS PARTITION(sale_date='20210815')
;

-- Tulis data ke partisi.
INSERT OVERWRITE TABLE odps_sale_detail PARTITION(sale_date='20210815') VALUES 
('s1','c1',100.1),
('s2','c2',100.2),
('s3','c3',100.3)
;

-- Buat tabel eksternal di Hologres untuk memetakan ke tabel data sumber di MaxCompute.
DROP FOREIGN TABLE IF EXISTS odps_sale_detail;

-- Buat tabel eksternal.
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');

-- Buat tabel internal di Hologres untuk menerima data dari tabel sumber MaxCompute.
DROP TABLE IF EXISTS holo_sale_detail;

-- Buat tabel partisi (tabel internal) di 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;

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

-- Kueri data dari tabel sumber MaxCompute di Hologres.
SELECT * FROM holo_sale_detail;

-- Buat tabel partisi di 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
)
;

-- Tambahkan partisi 20210815 dan 20210816 ke tabel sumber.
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')
;

-- Tulis data ke partisi.
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)
;

-- Buat tabel eksternal di Hologres untuk memetakan ke tabel data sumber di MaxCompute.
DROP FOREIGN TABLE IF EXISTS odps_sale_detail;

-- Buat tabel eksternal.
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');

-- Buat tabel internal di Hologres untuk menerima data dari tabel sumber MaxCompute.
DROP TABLE IF EXISTS holo_sale_detail;

-- Buat tabel partisi (tabel internal) di 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;

-- Impor data ke Hologres. Jangan tentukan partisi anak dan atur auto_create_partition ke TRUE. Sistem secara otomatis membuat dua partisi anak dan mengimpor data.
CALL hg_insert_overwrite ('holo_sale_detail', $$SELECT * FROM public.odps_sale_detail$$, TRUE);

-- Kueri data di Hologres.
SELECT * FROM holo_sale_detail;

Sintaks

Gunakan SQL berikut untuk mengimplementasikan INSERT OVERWRITE.

BEGIN ;

-- Kosongkan tabel temporary yang mungkin ada.
DROP TABLE IF EXISTS <table_new>;

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

COMMIT ;

-- Masukkan data ke tabel temporary.
INSERT INTO <table_new> [( <column> [, ...] )]
VALUES ( {<expression>}  [, ...] )
[, ...] | <query>}

ANALYZE <table_new>;

BEGIN ;

-- Hapus tabel lama.
DROP TABLE IF EXISTS  <table>;

-- Ubah nama tabel temporary.
ALTER TABLE <table_new> RENAME TO <table>;

COMMIT ;

Parameter

Contoh interaksi dengan MaxCompute

BEGIN ;

-- Kosongkan tabel temporary yang ada.
DROP TABLE IF EXISTS public.region_new;

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

-- Masukkan data ke tabel temporary.
INSERT INTO public.region_new
SELECT *
FROM public.odps_region_10g;

ANALYZE public.region_new;

BEGIN ;

-- Hapus tabel lama.
DROP TABLE IF EXISTS public.region;

-- Ubah nama tabel temporary.
ALTER TABLE IF EXISTS public.region_new RENAME TO region;

COMMIT ;

BEGIN ;

-- Kosongkan tabel temporary yang mungkin ada.
DROP TABLE IF EXISTS public.lineitem_new_20210101;

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

-- Masukkan data ke tabel temporary.
INSERT INTO public.lineitem_new_20210101
SELECT *
FROM public.odps_lineitem_10g
WHERE DS = '20210101'

ANALYZE public.lineitem_new_20210101;

BEGIN ;

-- Hapus partisi lama.
DROP TABLE IF EXISTS public.lineitem_20210101;

-- Ubah nama tabel temporary.
ALTER TABLE public.lineitem_new_20210101 RENAME TO lineitem_20210101;

-- Pasang tabel temporary ke tabel partisi yang ditentukan.
ALTER TABLE public.lineitem ATTACH PARTITION lineitem_20210101 FOR VALUES IN ('20210101');

COMMIT ;

-- Buat tabel temporary untuk tabel sink di MaxCompute.
CREATE  TABLE if not exists mc_holotable_temp(
    age INT,
    job STRING,
    name STRING
);

-- Buat pemetaan untuk tabel temporary di 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');
-- Perbarui tabel sumber di Hologres.
UPDATE holotable SET "job" = 'president' WHERE "name" = 'Lily';
-- Tulis data yang diperbarui ke pemetaan tabel temporary.
INSERT INTO mapping_holotable_temp SELECT * FROM holotable;

-- Hapus tabel sink lama di MaxCompute.
DROP TABLE IF EXISTS mc_holotable;
-- Ubah nama tabel temporary menjadi tabel sink.
ALTER TABLE mc_holotable_temp RENAME TO mc_holotable;