MaxCompute:Hologres 外部テーブル

外部テーブル作成ルール

• Hologres で IP アドレスホワイトリストを有効にしている場合、Hologres 外部テーブルは二重署名モードで作成する必要があります。STS モードで作成すると、IP アドレスホワイトリストにより MaxCompute からの Hologres へのアクセスがブロックされます。

• Hologres の親テーブルおよび子テーブルの場合、テーブル名は Hologres 外部テーブルおよび SQL 文で指定されます。親テーブルおよび子テーブルのいずれも Hologres 外部テーブルに対応できますが、親テーブルは読み取り専用です。

• Hologres 外部テーブルにデータを書き込む際、Hologres の INSERT ON CONFLICT (UPSERT) 機構はサポートされていません。Hologres ソーステーブルにプライマリキーが存在する場合、書き込むデータがソーステーブルのプライマリキー一意性制約に違反しないことを確認してください。

• テーブル名およびフィールド名は大文字と小文字を区別しません。テーブルおよびフィールドの作成時、クエリ時、参照時に強制的に大文字・小文字変換を行うことはできません。

スキーマ互換性

Hologres ソーステーブルのスキーマが外部テーブルのスキーマと異なる場合：

• 列数の不一致: Hologres ソーステーブルの列数が外部テーブルのデータ定義言語 (DDL) 文の列数より少ない場合、Hologres からデータを読み取る際にエラーが発生します。たとえば、エラーメッセージ column "xxx" does not exist が表示されます。Hologres ソーステーブルの列数が DDL 文の列数より多い場合、余分な列は無視されます。

• 列のデータ型の不一致: MaxCompute は Hologres ソーステーブルから STRING データを受信するために INT 型を使用することをサポートしていません。STRING 型を使用して INT データを受信することは可能ですが、推奨されません。

STS モードでの Hologres 外部テーブルの作成

サンプルコードについては、「Hologres 外部テーブルの作成 (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>,...'
);

二重署名モードでの Hologres 外部テーブルの作成

サンプルコードについては、「Hologres 外部テーブルの作成 (二重署名モード)」をご参照ください。

二重署名モードで作成された Hologres 外部テーブルを使用すると、同じ RAM ユーザーが MaxCompute および Hologres の許可されたテーブルに手動での権限付与なしにアクセスできます。このモードは Hologres の IP アドレスホワイトリスト機能もサポートしています。

-- 二重署名モードを有効にします。
SET odps.sql.common.table.planner.ext.hive.bridge=true;
-- 外部テーブルを作成します。
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>,...']
);

Hologres データベースの作成

1. Hologres 管理コンソール にログインし、左上隅でリージョンを選択します。

2. 左側のナビゲーションウィンドウで、[インスタンス]を選択します。

3. Hologres インスタンスがない場合は、まず「Hologres インスタンスの購入」を行ってください。

   「[インスタンス]」ページで、対象のインスタンス名をクリックします。

4. インスタンスの詳細ページで、[インスタンスに接続] をクリックします。

5. 上部の [メタデータ管理] タブをクリックします。

   Create Database をクリックします。表示されるダイアログボックスで、Database Name を入力し、他のパラメーターはデフォルト設定のままにします。

Hologres テーブルの作成

1. インスタンスの詳細ページで、[インスタンスに接続] をクリックします。

2. 上部の [SQL Editor] タブをクリックします。

3. 以下の文を入力します。

   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;

前提条件

1. 「MaxCompute クライアントのインストールおよび構成」を行ってください。

2. Hologres 外部テーブルを作成するための MaxCompute プロジェクトを準備してください。

   MaxCompute プロジェクトの作成方法の詳細については、「MaxCompute プロジェクトの作成」をご参照ください。

3. RAM ロールの作成および権限付与方法については、「Hologres での STS モードによる通常の RAM ロールの権限付与」をご参照ください。

MaxCompute での外部テーブルの作成

1. MaxCompute クライアントにログインし、対象の MaxCompute プロジェクトに入ります。

2. 以下のコマンドを実行して Hologres 外部テーブルを作成します。

   以下の Hologres インスタンス情報は「データの準備」セクションの手順に基づいています。

   • Hologres データベース名: holo_external_test。

   • Hologres データベーススキーマ: public。

   • Hologres テーブル名: holo。

   • 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. 以下のコマンドを実行して、新しい Hologres 外部テーブルを使用して Hologres ソーステーブルをクエリします。

   -- Hologres 外部テーブルにアクセスするには、以下のプロパティを追加する必要があります。
   SET odps.sql.split.hive.bridge=true;
   SET odps.sql.hive.compatible=true;
   SET odps.table.api.enable.holo.table=true; -- JDBC ダイレクトリードモードを有効にします。 
   -- Hologres 外部テーブルからデータをクエリします。
   SELECT * FROM my_table_holo_jdbc limit 10;
   -- 以下の結果が返されます。
   +------------+------------+
   | id         | name       | 
   +------------+------------+
   | 9          | hanmeimei  | 
   | 4          | tom        | 
   | 7          | haward     | 
   | 2          | mary       | 
   | 5          | lulu       | 
   | 8          | lilei      | 
   | 10         | lily       | 
   | 1          | kate       | 
   | 6          | mark       | 
   | 11         | lucy       | 
   +------------+------------+

4. Hologres 外部テーブルを使用して Hologres とのデータ交換および結合分析を行います。

   • Hologres 外部テーブルを使用して、MaxCompute で処理されたデータを Hologres に書き込み、分析の高速化およびオンラインサービスを実現します。以下のサンプルコマンドを提供します。

     -- Hologres 外部テーブルにアクセスするには、以下のプロパティを追加する必要があります。
     SET odps.sql.split.hive.bridge=true;
     SET odps.sql.hive.compatible=true;
     SET odps.table.api.enable.holo.table=true; -- JDBC ダイレクトリードモードを有効にします。 
     -- Hologres 外部テーブルにデータを挿入します。
     INSERT INTO my_table_holo_jdbc VALUES (12,'alice');
     -- Hologres 外部テーブルからデータをクエリします。
     SELECT * FROM my_table_holo_jdbc;
     -- 以下の結果が返されます。
     +------------+------------+
     | 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        | 
     +------------+------------+

   • 頻繁に更新されるディメンションテーブルを Hologres に格納し、リアルタイム動的更新要件を満たします。MaxCompute は外部テーブルを使用してこれらのディメンションテーブルにアクセスし、MaxCompute のファクトテーブルと結合分析を実行します。以下のサンプルコマンドを提供します。

     -- Hologres 外部テーブルにアクセスするには、以下のプロパティを追加する必要があります。
     SET odps.sql.split.hive.bridge=true;
     SET odps.sql.hive.compatible=true;
     -- MaxCompute 内部テーブルを作成します。
     CREATE TABLE holo_test AS SELECT * FROM my_table_holo_jdbc;
     -- MaxCompute 内部テーブルと Hologres 外部テーブルの結合分析を実行します。
     SELECT * FROM my_table_holo_jdbc t1 INNER JOIN holo_test t2 ON t1.id=t2.id;
     -- 以下の結果が返されます。
     +------------+------------+------------+------------+
     | 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        | 
     +------------+------------+------------+------------+

前提条件

1. 「MaxCompute クライアントのインストールおよび構成」を行ってください。

2. Hologres 外部テーブルを作成するための MaxCompute プロジェクトを準備してください。

   MaxCompute プロジェクトの作成方法の詳細については、「MaxCompute プロジェクトの作成」をご参照ください。

3. MaxCompute アカウントと同じ名前のアカウントが Hologres に存在し、このアカウントが Hologres の対応するテーブルに対して読み取りおよび書き込み権限を持っている必要があります。

4. Hologres V1.3 以降でのみ、MaxCompute から二重署名モードで Hologres 外部テーブルを作成できます。現在、このモードは Hologres 外部テーブルからのデータ読み取りのみをサポートしており、Hologres 外部テーブルへのデータ書き込みはサポートされていません。

MaxCompute での外部テーブルの作成

1. MaxCompute クライアントにログインし、対象の MaxCompute プロジェクトに入ります。

2. 以下のコマンドを実行して Hologres 外部テーブルを作成します。

   以下の Hologres インスタンス情報は「データの準備」セクションの手順に基づいています。

   • Hologres データベース名: holo_external_test。

   • Hologres データベーススキーマ: public。

   • Hologres テーブル名: holo。

   • Hologres データベースのクラシックネットワークエンドポイント: hgprecn-cn-oew210ut****-cn-hangzhou-internal.hologres.aliyuncs.com:80。

   -- 外部テーブルを作成します。
   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. 外部テーブルをクエリします。

   -- 外部テーブルをクエリします。このコマンドは二重署名モードを有効にするコマンドと一緒に実行する必要があります。
   SET odps.sql.common.table.planner.ext.hive.bridge=true;
   SELECT * FROM holo_mc_external_dbl;
   
   -- 以下の結果が返されます。
   +------------+------------+
   | 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        | 
   +------------+------------+