MaxCompute：Tabel eksternal Hologres

Spesifikasi pembuatan tabel eksternal

• Jika Anda mengaktifkan fitur daftar putih alamat IP untuk Hologres, buat tabel eksternal Hologres dalam mode tanda tangan ganda. Jika dibuat dalam mode STS, akses dari MaxCompute ke Hologres akan diblokir oleh daftar putih alamat IP.

• Anda dapat memetakan tabel induk maupun tabel anak di Hologres ke tabel eksternal MaxCompute dengan menentukan nama tabel dalam pernyataan CREATE EXTERNAL TABLE. Namun, 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 kunci primer, pastikan data yang ditulis tidak bertentangan dengan kunci primer tersebut.

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

Kompatibilitas skema

Jika skema tabel sumber Hologres tidak konsisten dengan skema tabel eksternal:

• Jumlah kolom tidak konsisten: Jika tabel sumber Hologres memiliki lebih sedikit kolom daripada DDL tabel eksternal, kesalahan akan dilaporkan saat membaca data dari Hologres, misalnya column "xxx" does not exist. Jika tabel sumber memiliki lebih banyak kolom, kolom tambahan tersebut akan diabaikan.

• Tipe kolom tidak konsisten: MaxCompute tidak mendukung pemetaan tipe INT ke kolom STRING di tabel sumber Hologres. Anda dapat menggunakan tipe STRING untuk memetakan ke kolom 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 tanda tangan ganda

Untuk contoh kode, lihat Buat tabel eksternal Hologres (mode tanda tangan ganda).

Tabel eksternal Hologres dengan tanda tangan ganda memungkinkan pengguna RAM yang sama mengakses tabel yang diotorisasi di MaxCompute dan Hologres tanpa otorisasi manual. Mode ini juga mendukung fitur daftar putih alamat IP Hologres, yang menyederhanakan konfigurasi keamanan.

-- Aktifkan sakelar tanda tangan ganda.
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. Login ke Konsol Manajemen Hologres, lalu pilih wilayah di pojok kiri atas.

2. Di panel navigasi sebelah kiri, pilih Instances.

3. Jika belum memiliki instans, 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. Klien MaxCompute telah diinstal dan dikonfigurasi.

2. Proyek MaxCompute tujuan tersedia untuk membuat tabel eksternal Hologres.

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

Buat dan berikan izin ke peran RAM

1. Buat peran RAM

   Buat peran RAM untuk mendapatkan ARN-nya. ARN digunakan untuk autentikasi STS saat membuat tabel eksternal. Pilih jenis entitas tepercaya untuk peran RAM sesuai kebutuhan. Dalam contoh ini, entitas tepercayanya adalah akun Alibaba Cloud.

   • Akun Alibaba Cloud: Pengguna RAM di bawah akun Alibaba Cloud dapat mengasumsikan peran RAM untuk mengakses sumber daya cloud. Untuk informasi selengkapnya, lihat Buat peran RAM untuk akun Alibaba Cloud tepercaya.

   • Penyedia identitas: Anda dapat mengatur single sign-on (SSO) untuk login ke Konsol Manajemen Alibaba Cloud dari sistem identitas lokal Anda. Ini berguna bagi perusahaan yang memerlukan autentikasi login pengguna terpadu. Untuk informasi selengkapnya, lihat Buat peran RAM untuk IdP tepercaya.

   Lakukan langkah-langkah berikut:

   1. Login ke Konsol Resource Access Management (RAM).

   2. Di panel navigasi sebelah kiri, pilih Identities > Roles.

   3. Di halaman Roles, klik Create Role.

   4. Di halaman Create Role, atur Principal Type ke Cloud Account.

2. Ubah kebijakan kepercayaan

   1. Di halaman Roles, klik Role Name yang dituju untuk membuka halaman detailnya.

   2. Di tab Trust Policy, klik Edit Trust Policy. Di halaman Edit Trust Policy, klik tab JSON.

   3. Perbarui kebijakan kepercayaan dengan konten berikut.

      Ubah kebijakan kepercayaan berdasarkan konten berikut. Konfigurasi kebijakan kepercayaan bervariasi tergantung pada jenis entitas tepercaya yang Anda pilih. <UID> dalam kebijakan adalah ID akun Alibaba Cloud, yang dapat Anda temukan di halaman Informasi Pengguna.

      Jenis entitas tepercaya adalah akun Alibaba Cloud

      {
        "Statement": [
          {
            "Action": "sts:AssumeRole",
            "Effect": "Allow",
            "Principal": {
              "RAM": [
                "acs:ram::<UID>:root"
              ]
            }
          },
          {
            "Action": "sts:AssumeRole",
            "Effect": "Allow",
            "Principal": {
              "Service": [
                "<UID>@odps.aliyuncs.com"
              ]
            }
          }
        ],
        "Version": "1"
      }

      Jenis entitas tepercaya adalah penyedia identitas

      {
        "Statement": [
              {
                  "Action": "sts:AssumeRole",
                  "Condition": {
                      "StringEquals": {
                          "saml:recipient": "https://signin.aliyun.com/saml-role/sso"
                      }
                  },
                  "Effect": "Allow",
                  "Principal": {
                      "Federated": [
                          "acs:ram::<UID>:saml-provider/IDP"
                      ]
                  }
              },
              {
                  "Action": "sts:AssumeRole",
                  "Effect": "Allow",
                  "Principal": {
                      "Service": [
                          "<UID>@odps.aliyuncs.com"
                      ]
                  }
              }
          ],
          "Version": "1"
      }

3. Tambahkan peran RAM ke instans Hologres dan berikan izin

   Peran RAM memerlukan izin developer pada instans Hologres. Secara default, peran RAM tidak dapat melihat atau mengoperasikan instans di konsol Hologres. Oleh karena itu, akun Alibaba Cloud harus memberikan izin yang diperlukan kepada peran RAM. Anda dapat menambahkan peran RAM ke instans Hologres dan memberikan izin menggunakan salah satu metode berikut:

   Berikan izin di konsol Hologres

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

   2. Di panel navigasi sebelah kiri, pilih Instances.

   3. Di halaman Instances, klik nama instans yang dituju.

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

   5. Klik tab Security Center di atas.

      Di panel navigasi sebelah kiri, pilih User Management.

   7. Di tab Database Authorization, berikan izin developer kepada peran RAM untuk instans tersebut.

      Jika kebijakan akses database diatur ke Expert Mode, Anda perlu mengubah tipe peran menjadi SuperUser di halaman User Management. Setelah perubahan ini, Anda tidak perlu memberikan izin database secara terpisah.

   Berikan izin di konsol RAM

   Jika pengguna RAM mengasumsikan peran RAM, pengguna RAM tersebut tidak memiliki izin pada konsol Hologres secara default. Akun Alibaba Cloud harus memberikan izin AliyunRAMReadOnlyAccess kepada pengguna RAM di konsol RAM. Jika tidak, pengguna RAM tidak dapat melakukan operasi apa pun di konsol Hologres. Untuk informasi selengkapnya, lihat Berikan izin kepada pengguna RAM.

   1. Login ke Konsol Resource Access Management (RAM).

   2. Di panel navigasi sebelah kiri, pilih Identities > Roles.

   3. Di halaman Roles, di kolom Actions untuk Role Name yang dituju, klik Grant Permission.

   4. Pilih kebijakan AliyunRAMReadOnlyAccess untuk memberikan izin kepada peran saat ini.

   Berikan izin menggunakan pernyataan SQL

   Untuk memberikan izin menggunakan pernyataan SQL, lihat model izin Hologres.

Buat tabel eksternal di MaxCompute

1. Login ke klien MaxCompute dan beralih ke proyek MaxCompute tujuan.

2. Jalankan perintah berikut untuk membuat tabel eksternal Hologres.

   Informasi instans Hologres yang disiapkan dalam langkah Persiapkan data adalah sebagai berikut:

   • 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 informasi tabel sumber Hologres menggunakan tabel eksternal Hologres yang baru dibuat.

   -- Tambahkan properti berikut 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. Lakukan pertukaran data dan analisis gabungan dengan Hologres menggunakan tabel eksternal Hologres.

   • Anda dapat menulis data yang diproses oleh MaxCompute ke Hologres menggunakan tabel eksternal untuk mempercepat analisis dan layanan online. Perintah contoh berikut menunjukkan contohnya.

     -- Tambahkan properti berikut 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        | 
     +------------+------------+

   • Anda dapat menyimpan tabel dimensi yang sering diperbarui di Hologres untuk memenuhi persyaratan pembaruan real-time. MaxCompute kemudian dapat mengakses tabel dimensi ini menggunakan tabel eksternal dan melakukan analisis asosiasi dengan tabel fakta yang disimpan di MaxCompute. Perintah contoh berikut menunjukkan contohnya.

     -- Tambahkan properti berikut 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 asosiasi 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. Klien MaxCompute telah diinstal dan dikonfigurasi.

2. Proyek MaxCompute tujuan tersedia untuk membuat tabel eksternal Hologres.

   Untuk informasi 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 yang lebih baru yang mendukung pembuatan tabel eksternal Hologres dalam mode tanda tangan ganda di MaxCompute. Saat ini, mode tanda tangan ganda hanya mendukung pembacaan data dari tabel eksternal Hologres, bukan penulisan data ke dalamnya.

Buat tabel eksternal di MaxCompute

1. Login ke klien MaxCompute dan beralih ke proyek MaxCompute tujuan.

2. Jalankan perintah berikut untuk membuat tabel eksternal Hologres.

   Informasi instans Hologres yang disiapkan dalam langkah Persiapkan data adalah sebagai berikut:

   • 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 (harus dijalankan bersama perintah untuk mengaktifkan sakelar tanda tangan ganda).
   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        | 
   +------------+------------+