MaxCompute:Hologres 外部テーブル

外部テーブル作成の仕様

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

• `CREATE EXTERNAL TABLE` ステートメントでテーブル名を指定することで、Hologres の親テーブルと子テーブルの両方を MaxCompute 外部テーブルにマッピングできます。ただし、親テーブルは読み取り専用です。

• Hologres 外部テーブルにデータを書き込む際、Hologres の INSERT ON CONFLICT (UPSERT) メカニズムはサポートされていません。Hologres ソーステーブルにプライマリキーがある場合は、書き込むデータがソーステーブルのプライマリキーと競合しないようにしてください。

• テーブル名とフィールド名では大文字と小文字が区別されません。テーブルやフィールドを作成またはクエリする際に、大文字と小文字の変換を強制することはできません。

スキーマの互換性

Hologres ソーステーブルのスキーマが外部テーブルのスキーマと一致しない場合:

• 列数が一致しない場合：Hologres ソーステーブルの列数が外部テーブル DDL より少ない場合、Hologres テーブルからデータを読み取る際にエラーが報告されます。例：column "xxx" does not exist。Hologres ソーステーブルの列数が外部テーブル DDL より多い場合、余分な列は無視されます。

• 列の型が一致しない場合：MaxCompute は、Hologres ソーステーブルの STRING 列に INT 型をマッピングすることをサポートしていません。STRING 型を使用して INT 列にマッピングすることは可能ですが、推奨されません。

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

サンプルコードについては、「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 インスタンスを購入する必要があります。

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

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

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

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

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 プロジェクトの作成」をご参照ください。

RAM ロールの作成と権限付与

1. RAM ロールの作成

   RAM ロールを作成して、その ARN を取得します。ARN は、外部テーブルを作成する際の STS 認証に使用されます。必要に応じて、RAM ロールの信頼できるエンティティタイプを選択します。この例では、信頼できるエンティティは Alibaba Cloud アカウントです。

   • Alibaba Cloud アカウント：Alibaba Cloud アカウント配下の RAM ユーザーは、RAM ロールを偽装してクラウドリソースにアクセスできます。詳細については、「信頼できる Alibaba Cloud アカウントの RAM ロールを作成する」をご参照ください。

   • ID プロバイダー：オンプレミスの ID システムから Alibaba Cloud 管理コンソールにログインするためのシングルサインオン (SSO) を設定できます。これは、統一されたユーザーログイン認証を必要とする企業に役立ちます。詳細については、「信頼できる IdP の RAM ロールを作成する」をご参照ください。

   次のステップを実行します：

   1. Resource Access Management (RAM) コンソールにログインします。

   2. 左側のナビゲーションウィンドウで、[アイデンティティ] > [ロール] を選択します。

   3. [ロール] ページで、[ロールの作成] をクリックします。

   4. [ロールの作成] ページで、[プリンシパルタイプ] を [クラウドアカウント] に設定します。

2. 信頼ポリシーの変更

   1. [ロール] ページで、対象の [ロール名] をクリックすると、その詳細ページが開きます。

   2. [信頼ポリシー] タブで、[信頼ポリシーの編集] をクリックします。[信頼ポリシーの編集] ページで、[JSON] タブをクリックします。

   3. 次の内容で信頼ポリシーを変更します。

      次の内容に基づいて信頼ポリシーを変更します。信頼ポリシーの設定は、選択した信頼できるエンティティのタイプによって異なります。ポリシー内の <UID> は Alibaba Cloud アカウント ID であり、ユーザー情報ページで確認できます。

      信頼できるエンティティタイプが 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"
      }

      信頼できるエンティティタイプが ID プロバイダーの場合

      {
        "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. RAM ロールを Hologres インスタンスに追加し、権限を付与する

   RAM ロールには、Hologres インスタンスに対する開発者権限が必要です。デフォルトでは、RAM ロールは Hologres コンソールでインスタンスを表示または操作できません。そのため、Alibaba Cloud アカウントが RAM ロールに必要な権限を付与する必要があります。次のいずれかの方法で、RAM ロールを Hologres インスタンスに追加し、権限を付与できます：

   Hologres コンソールで権限を付与する

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

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

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

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

   5. 上の [セキュリティセンター] タブをクリックします。

      左側のナビゲーションウィンドウで、[ユーザー管理]を選択します。

   7. [データベース権限付与] タブで、インスタンスの RAM ロールに開発者権限を付与します。

      データベースのアクセスポリシーがエキスパートモードに設定されている場合は、ユーザー管理ページでロールタイプを SuperUser に変更する必要があります。この変更後、データベース権限を個別に付与する必要はありません。

   RAM コンソールで権限を付与する

   RAM ユーザーが RAM ロールを偽装する場合、デフォルトでは RAM ユーザーは Hologres コンソールに対する権限を持ちません。Alibaba Cloud アカウントは、RAM コンソールで RAM ユーザーに AliyunRAMReadOnlyAccess 権限を付与する必要があります。そうしないと、RAM ユーザーは Hologres コンソールでいかなる操作も実行できません。詳細については、「RAM ユーザーに権限を付与する」をご参照ください。

   1. Resource Access Management (RAM) コンソールにログインします。

   2. 左側のナビゲーションウィンドウで、[アイデンティティ] > [ロール]を選択します。

   3. [ロール] ページで、対象の [ロール名] の [アクション] 列にある [権限の付与] をクリックします。

   4. AliyunRAMReadOnlyAccess ポリシーを選択して、現在のロールに権限を付与します。

   SQL ステートメントを使用して権限を付与する

   SQL ステートメントを使用して権限を付与する方法については、「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 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 とのデータ交換および共同分析を実行します。

   • 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 外部テーブルからのデータ読み取りのみをサポートしており、データ書き込みはサポートしていません。

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