MaxCompute：Tabel eksternal Hologres

Aturan pembuatan tabel eksternal

• Jika Anda mengaktifkan daftar putih alamat IP untuk Hologres, Anda harus membuat tabel eksternal Hologres dalam mode double-signature. Jika dibuat dalam mode STS, akses MaxCompute ke Hologres akan diblokir oleh daftar putih alamat IP.

• Untuk tabel induk dan anak di Hologres, nama tabel ditentukan dalam tabel eksternal Hologres dan pernyataan SQL dieksekusi. Baik tabel induk maupun anak dapat dipetakan ke tabel eksternal Hologres, tetapi tabel induk bersifat read-only.

• Saat menulis data ke tabel eksternal Hologres, mekanisme INSERT ON CONFLICT (UPSERT) Hologres tidak didukung. Jika tabel sumber Hologres memiliki primary key, pastikan data yang Anda tulis tidak melanggar batasan keunikan primary key di tabel sumber.

• Nama tabel dan bidang tidak peka huruf besar/kecil. Anda tidak dapat memaksa konversi huruf besar/kecil saat membuat, mengkueri, atau mereferensikan tabel dan bidang.

Kompatibilitas skema

Jika skema tabel sumber Hologres berbeda dengan skema tabel eksternal:

• Jumlah kolom tidak konsisten: Jika tabel sumber Hologres memiliki lebih sedikit kolom daripada pernyataan Data Definition Language (DDL) tabel eksternal, terjadi kesalahan saat membaca data dari Hologres, misalnya pesan kesalahan column "xxx" does not exist. Jika tabel sumber Hologres memiliki lebih banyak kolom daripada pernyataan DDL, kolom tambahan tersebut diabaikan.

• Tipe kolom tidak konsisten: MaxCompute tidak mendukung penggunaan tipe INT untuk menerima data STRING dari tabel sumber Hologres. Anda dapat menggunakan tipe STRING untuk menerima data INT, tetapi hal ini tidak disarankan.

Buat tabel eksternal Hologres dalam mode STS

Untuk contoh kode, lihat Buat tabel eksternal Hologres (mode STS).

CREATE EXTERNAL TABLE [IF NOT EXISTS] <table_name>(
  <col1_name> <data_type>,
  <col2_name> <data_type>,
  ......
)
stored BY 'com.aliyun.odps.jdbc.JdbcStorageHandler'
WITH serdeproperties (
  'odps.properties.rolearn'='<ram_arn>')
location '<jdbc:postgresql://<endpoint>:<port>/<database>?ApplicationName=MaxCompute&[currentSchema=<schema>&][useSSL={true|false}&]table=<holo_table_name>/>' 
tblproperties (
  'mcfed.mapreduce.jdbc.driver.class'='org.postgresql.Driver', 
  'odps.federation.jdbc.target.db.type'='holo',
  'odps.federation.jdbc.colmapping'='<table_column1>:<source_column1>, <table_column2>:<source_column2>,...'
);

Buat tabel eksternal Hologres dalam mode double-signature

Untuk contoh kode, lihat Buat tabel eksternal Hologres (mode double-signature).

Tabel eksternal Hologres yang dibuat dalam mode double-signature memungkinkan RAM user yang sama mengakses tabel yang diotorisasi di MaxCompute dan Hologres tanpa otorisasi manual. Mode ini juga mendukung fitur daftar putih alamat IP Hologres.

-- Aktifkan mode double-signature.
SET odps.sql.common.table.planner.ext.hive.bridge=true;
-- Buat tabel eksternal.
CREATE EXTERNAL TABLE [IF NOT EXISTS]  <table_name>(
  <col1_name> <data_type>,
  <col2_name> <data_type>,
  ......
)
STORED BY '<com.aliyun.odps.jdbc.JdbcStorageHandler>'
LOCATION '<jdbc:postgresql://<endpoint>:<port>/<database>?ApplicationName=MaxCompute&[currentSchema=<schema>&][useSSL={true|false}&]table=<holo_table_name>/>' 
tblproperties (
  'mcfed.mapreduce.jdbc.driver.class'='org.postgresql.Driver', 
  'odps.federation.jdbc.target.db.type'='holo',
  ['odps.federation.jdbc.colmapping'='<table_column1>:<source_column1>, <table_column2>:<source_column2>,...']
);

Buat database Hologres

1. Masuk ke Konsol Manajemen Hologres, lalu di pojok kiri atas, pilih wilayah.

2. Di panel navigasi sebelah kiri, pilih Instances.

3. Jika Anda belum memiliki instans Hologres, Anda harus terlebih dahulu membeli instans Hologres.

   Di halaman Instances, klik nama instans yang dituju.

4. Di halaman detail instans, klik Connect To Instance.

5. Klik tab Metadata Management di atas.

   Klik Create Database. Pada kotak dialog yang muncul, masukkan Database Name dan biarkan pengaturan parameter lainnya tetap pada nilai default.

Buat tabel Hologres

1. Di halaman detail instans, klik Connect To Instance.

2. Klik tab SQL Editor di atas.

3. Masukkan pernyataan berikut:

   CREATE TABLE IF NOT EXISTS holo (
       id   INT PRIMARY KEY,
       name TEXT
   );
   
   INSERT INTO holo (id, name) VALUES
       (1, 'kate'),
       (2, 'mary'),
       (3, 'bob'),
       (4, 'tom'),
       (5, 'lulu'),
       (6, 'mark'),
       (7, 'haward'),
       (8, 'lilei'),
       (9, 'hanmeimei'),
       (10, 'lily'),
       (11, 'lucy');
   
   SELECT * FROM holo ORDER BY id;

Prasyarat

1. Instal dan konfigurasikan klien MaxCompute.

2. Persiapkan proyek MaxCompute untuk membuat tabel eksternal Hologres.

   Untuk informasi selengkapnya tentang cara membuat proyek MaxCompute, lihat Buat proyek MaxCompute.

3. Untuk informasi tentang cara membuat dan memberikan izin kepada peran RAM, lihat Otorisasi peran RAM reguler dalam mode STS untuk Hologres.

Buat tabel eksternal di MaxCompute

1. Masuk ke klien MaxCompute dan masukkan proyek MaxCompute tujuan.

2. Jalankan perintah berikut untuk membuat tabel eksternal Hologres.

   Informasi instans Hologres berikut didasarkan pada langkah-langkah di bagian Persiapkan data:

   • Nama database Hologres: holo_external_test.

   • Skema database Hologres: public.

   • Nama tabel Hologres: holo.

   • Titik akhir jaringan klasik database Hologres: hgprecn-cn-oew210ut****-cn-hangzhou-internal.hologres.aliyuncs.com:80.

   CREATE EXTERNAL TABLE IF NOT EXISTS my_table_holo_jdbc
   (
    id bigint,
    name string
   )
   stored BY 'com.aliyun.odps.jdbc.JdbcStorageHandler' 
   WITH serdeproperties (
     'odps.properties.rolearn'='acs:ram::139699392458****:role/<role name>')
   location 'jdbc:postgresql://hgprecn-cn-oew210ut****-cn-hangzhou-internal.hologres.aliyuncs.com:80/<holo database name>?ApplicationName=MaxCompute&currentSchema=public&useSSL=true&table=<table name>/'
   tblproperties (
     'mcfed.mapreduce.jdbc.driver.class'='org.postgresql.Driver',
     'odps.federation.jdbc.target.db.type'='holo',
     'odps.federation.jdbc.colmapping'='id:id,name:name'
   );

3. Jalankan perintah berikut untuk mengkueri tabel sumber Hologres menggunakan tabel eksternal Hologres baru.

   -- Properti berikut harus ditambahkan untuk mengakses tabel eksternal Hologres.
   SET odps.sql.split.hive.bridge=true;
   SET odps.sql.hive.compatible=true;
   SET odps.table.api.enable.holo.table=true; -- Aktifkan mode baca langsung JDBC. 
   -- Kueri data dari tabel eksternal Hologres.
   SELECT * FROM my_table_holo_jdbc limit 10;
   -- Hasil berikut dikembalikan.
   +------------+------------+
   | id         | name       | 
   +------------+------------+
   | 9          | hanmeimei  | 
   | 4          | tom        | 
   | 7          | haward     | 
   | 2          | mary       | 
   | 5          | lulu       | 
   | 8          | lilei      | 
   | 10         | lily       | 
   | 1          | kate       | 
   | 6          | mark       | 
   | 11         | lucy       | 
   +------------+------------+

4. Tukar data dan lakukan analisis join dengan Hologres menggunakan tabel eksternal Hologres.

   • Gunakan tabel eksternal Hologres untuk menulis data yang diproses MaxCompute ke Hologres guna percepatan analisis dan layanan online. Perintah contoh berikut disediakan.

     -- Properti berikut harus ditambahkan untuk mengakses tabel eksternal Hologres.
     SET odps.sql.split.hive.bridge=true;
     SET odps.sql.hive.compatible=true;
     SET odps.table.api.enable.holo.table=true; -- Aktifkan mode baca langsung JDBC. 
     -- Masukkan data ke tabel eksternal Hologres.
     INSERT INTO my_table_holo_jdbc VALUES (12,'alice');
     -- Kueri data dari tabel eksternal Hologres.
     SELECT * FROM my_table_holo_jdbc;
     -- Hasil berikut dikembalikan.
     +------------+------------+
     | id         | name       | 
     +------------+------------+
     | 9          | hanmeimei  | 
     | 4          | tom        | 
     | 7          | haward     | 
     | 2          | mary       | 
     | 5          | lulu       | 
     | 12         | alice      | 
     | 8          | lilei      | 
     | 10         | lily       | 
     | 1          | kate       | 
     | 11         | lucy       | 
     | 6          | mark       | 
     | 3          | bob        | 
     +------------+------------+

   • Simpan tabel dimensi yang sering diperbarui di Hologres untuk memenuhi persyaratan pembaruan dinamis real-time. MaxCompute mengakses tabel dimensi ini menggunakan tabel eksternal dan melakukan analisis join dengan tabel fakta di MaxCompute. Perintah contoh berikut disediakan.

     -- Properti berikut harus ditambahkan untuk mengakses tabel eksternal Hologres.
     SET odps.sql.split.hive.bridge=true;
     SET odps.sql.hive.compatible=true;
     -- Buat tabel internal MaxCompute.
     CREATE TABLE holo_test AS SELECT * FROM my_table_holo_jdbc;
     -- Lakukan analisis join pada tabel internal MaxCompute dan tabel eksternal Hologres.
     SELECT * FROM my_table_holo_jdbc t1 INNER JOIN holo_test t2 ON t1.id=t2.id;
     -- Hasil berikut dikembalikan.
     +------------+------------+------------+------------+
     | id         | name       | id2        | name2      | 
     +------------+------------+------------+------------+
     | 9          | hanmeimei  | 9          | hanmeimei  | 
     | 4          | tom        | 4          | tom        | 
     | 7          | haward     | 7          | haward     | 
     | 2          | mary       | 2          | mary       | 
     | 5          | lulu       | 5          | lulu       | 
     | 12         | alice      | 12         | alice      | 
     | 8          | lilei      | 8          | lilei      | 
     | 10         | lily       | 10         | lily       | 
     | 1          | kate       | 1          | kate       | 
     | 11         | lucy       | 11         | lucy       | 
     | 6          | mark       | 6          | mark       | 
     | 3          | bob        | 3          | bob        | 
     +------------+------------+------------+------------+

Prasyarat

1. Instal dan konfigurasikan klien MaxCompute.

2. Persiapkan proyek MaxCompute untuk membuat tabel eksternal Hologres.

   Untuk informasi selengkapnya tentang cara membuat proyek MaxCompute, lihat Buat proyek MaxCompute.

3. Akun dengan nama yang sama dengan akun MaxCompute ada di Hologres, dan akun ini memiliki izin baca dan tulis pada tabel yang sesuai di Hologres.

4. Hanya Hologres V1.3 dan versi setelahnya yang mendukung pembuatan tabel eksternal Hologres dalam mode double-signature dari MaxCompute. Saat ini, mode ini hanya mendukung pembacaan data dari tabel eksternal Hologres. Penulisan data ke tabel eksternal Hologres tidak didukung.

Buat tabel eksternal di MaxCompute

1. Masuk ke klien MaxCompute dan masukkan proyek MaxCompute tujuan.

2. Jalankan perintah berikut untuk membuat tabel eksternal Hologres.

   Informasi instans Hologres berikut didasarkan pada langkah-langkah di bagian Persiapkan data:

   • Nama database Hologres: holo_external_test.

   • Skema database Hologres: public.

   • Nama tabel Hologres: holo.

   • Titik akhir jaringan klasik database Hologres: hgprecn-cn-oew210ut****-cn-hangzhou-internal.hologres.aliyuncs.com:80.

   -- Buat tabel eksternal.
   CREATE EXTERNAL TABLE IF NOT EXISTS holo_mc_external_dbl
   (
     id int,
     name string
   )
   STORED BY 'com.aliyun.odps.jdbc.JdbcStorageHandler'
   location 'jdbc:postgresql://hgpostcn-cn-****-cn-hangzhou-internal.hologres.aliyuncs.com:80/<holo database name>?ApplicationName=MaxCompute&currentSchema=public&preferQueryMode=simple&useSSL=false&table=<table name>/'
   TBLPROPERTIES (
     'mcfed.mapreduce.jdbc.driver.class'='org.postgresql.Driver',
     'odps.federation.jdbc.target.db.type'='holo',
     'odps.federation.jdbc.colmapping'='id:id,name:name'
   );

3. Kueri tabel eksternal.

   -- Kueri tabel eksternal. Perintah ini harus dijalankan bersamaan dengan perintah yang mengaktifkan mode double-signature.
   SET odps.sql.common.table.planner.ext.hive.bridge=true;
   SELECT * FROM holo_mc_external_dbl;
   
   -- Hasil berikut dikembalikan.
   +------------+------------+
   | id         | name       | 
   +------------+------------+
   | 9          | hanmeimei  | 
   | 4          | tom        | 
   | 7          | haward     | 
   | 2          | mary       | 
   | 5          | lulu       | 
   | 12         | alice      | 
   | 8          | lilei      | 
   | 10         | lily       | 
   | 1          | kate       | 
   | 11         | lucy       | 
   | 6          | mark       | 
   | 3          | bob        | 
   +------------+------------+