AnalyticDB:外部テーブルを使用した OSS データのインポート

エンタープライズ、ベーシック、およびデータレイクハウス エディション

1. SQL 開発エディターに移動します。

   1. AnalyticDB for MySQL コンソール にログインします。コンソールの左上隅でリージョンを選択し、左側のナビゲーションウィンドウで クラスターリスト をクリックします。管理対象のクラスターを見つけ、クラスター ID をクリックします。

   2. 左側のナビゲーションウィンドウで、ジョブを開発する > Sql開発 を選択します。

2. データをインポートします。

   通常のインポート（デフォルト）またはエラスティック インポートのいずれかを使用してデータをインポートできます。通常のインポートモードでは、システムがコンピュートノードからソースデータを読み取り、ストレージノードでインデックスを作成するため、コンピュートリソースとストレージリソースの両方を消費します。 エラスティック インポート方式は、カーネルバージョン 3.1.10.0 以降を実行し、ジョブタイプのリソースグループを持つ Enterprise Edition、Basic Edition、および Data Lakehouse Edition クラスターでのみサポートされています。詳細については、「データインポート方式」をご参照ください。

   通常のインポート

   1. 外部データベースを作成します。

      CREATE EXTERNAL DATABASE adb_external_db;

   2. 外部テーブルを作成します。adb_external_db 外部データベース内に OSS 外部テーブルを作成するために、CREATE EXTERNAL TABLE 文を使用します。このトピックでは、adb_external_db.person を例として使用します。

      説明

      AnalyticDB for MySQL の外部テーブルは、ソース OSS ファイルと同じフィールド名、フィールド数、フィールド順序、およびデータ型を持つ必要があります。

      パーティションなしの OSS 外部テーブルの作成

      CREATE EXTERNAL TABLE adb_external_db.person
      (
       id INT,
       name VARCHAR(1023),
       age INT,
       dt VARCHAR(1023)
      )
      ROW FORMAT DELIMITED FIELDS TERMINATED BY  ','
      STORED AS TEXTFILE
      LOCATION  'oss://testBucketName/adb/dt=2023-06-15/';

      パーティション付き OSS 外部テーブルの作成

      パーティション付き OSS 外部テーブルをクエリするには、事前にテーブルを作成し、必要なパーティションを追加する必要があります。

      1. パーティション付き OSS 外部テーブルを作成します。

         CREATE EXTERNAL TABLE adb_external_db.person
         (
          id INT,
          name VARCHAR(1023) ,
          age INT
         )
         PARTITIONED BY (dt STRING)
         ROW FORMAT DELIMITED FIELDS TERMINATED BY  ','
         STORED AS TEXTFILE
         LOCATION  'oss://testBucketName/adb/';

      2. ALTER TABLE ADD PARTITION 文を使用してパーティションを手動で追加するか、MSCK REPAIR TABLE 文を使用してパーティションを自動的に検出して追加できます。

         ALTER TABLE adb_external_db.person ADD PARTITION (dt='2023-06-15') LOCATION 'oss://testBucketName/adb/dt=2023-06-15/';

         説明

         • OSS 外部テーブルへのパーティションの追加または削除については、「ALTER TABLE (外部テーブル)」をご参照ください。

         • OSS 外部テーブルのパーティションを自動同期する方法については、「MSCK REPAIR TABLE」をご参照ください。

      OSS 外部テーブルの作成構文の詳細については、「CREATE EXTERNAL TABLE」をご参照ください。

   3. データをクエリします。

      外部テーブルを作成後、AnalyticDB for MySQL で SELECT 文を実行して OSS からデータをクエリできます。

      SELECT * FROM adb_external_db.person;

      以下の結果が返されます。

      +------+-------+------+-----------+
      | id   | name  | age  | dt        |
      +------+-------+------+-----------+
      |    1 | james |   10 |2023-06-15 |
      |    2 | bond  |   20 |2023-06-15 |
      |    3 | jack  |   30 |2023-06-15 |
      |    4 | lucy  |   40 |2023-06-15 |
      +------+-------+------+-----------+

   4. AnalyticDB for MySQL にデータベースを作成します。既にデータベースが存在する場合は、この手順をスキップできます。以下の文が例です。

      CREATE DATABASE adb_demo; 

   5. AnalyticDB for MySQL に、インポートされた OSS データを格納するためのテーブルを作成します。以下の文が例です。

      説明

      内部テーブルは、ステップ b の外部テーブルとフィールド名、フィールド数、フィールド順序、およびデータ型が一致している必要があります。

      CREATE TABLE IF NOT EXISTS adb_demo.adb_import_test(
          id INT,
          name VARCHAR(1023),
          age INT,
          dt VARCHAR(1023)
      )
      DISTRIBUTED BY HASH(id);

   6. テーブルにデータをインポートします。

      • 方法 1：INSERT INTO 文を使用します。プライマリキーが重複する場合、新しいデータは無視されます。これは INSERT IGNORE INTO と同等です。詳細については、「INSERT INTO」をご参照ください。例：

        INSERT INTO adb_demo.adb_import_test SELECT * FROM adb_external_db.person;

      • 方法 2：INSERT OVERWRITE INTO 文を使用してデータを同期的にインポートします。これにより、テーブル内の既存データが上書きされます。例：

        INSERT OVERWRITE INTO adb_demo.adb_import_test SELECT * FROM adb_external_db.person;

      • 方法 3：INSERT OVERWRITE INTO 文を使用してデータを非同期的にインポートします。詳細については、「非同期書き込み」をご参照ください。例：

        SUBMIT JOB INSERT OVERWRITE adb_demo.adb_import_test SELECT * FROM adb_external_db.person;

   エラスティック インポート

   1. データベースを作成します。既にデータベースが存在する場合は、この手順をスキップできます。以下の文が例です。

      CREATE DATABASE adb_demo; 

   2. 外部テーブルを作成します。

      説明

      • AnalyticDB for MySQL の外部テーブルは、ソース OSS ファイルと同じフィールド名、フィールド数、フィールド順序、およびデータ型を持つ必要があります。

      • エラスティック インポートでは、CREATE TABLE 文を使用した外部テーブルの作成のみがサポートされています。

      CREATE TABLE oss_import_test_external_table
      (
        id INT(1023),
        name VARCHAR(1023),
        age INT,
        dt VARCHAR(1023)
      )
      ENGINE='OSS'
      TABLE_PROPERTIES='{
          "endpoint":"oss-cn-hangzhou-internal.aliyuncs.com",
          "url":"oss://testBucketName/adb/dt=2023-06-15/person.csv",
          "accessid":"accesskey_id",
          "accesskey":"accesskey_secret",
          "delimiter":","
      }';

      重要

      外部テーブル作成時にサポートされる TABLE_PROPERTIES パラメーターは、ファイル形式 (CSV、Parquet、ORC) によって異なります。

      • CSV 形式：endpoint、url、accessid、accesskey、format、delimiter、null_value、maxlinelength、および partition_column パラメーターのみがサポートされます。

      • Parquet 形式：endpoint、url、accessid、accesskey、format、maxlinelength、および partition_column パラメーターのみがサポートされます。

      • ORC 形式：endpoint、url、accessid、accesskey、format、maxlinelength、および partition_column パラメーターのみがサポートされます。

      外部テーブルで設定可能なパラメーターとその説明の詳細については、「OSS パーティションなし外部テーブル」および「OSS パーティション付き外部テーブル」をご参照ください。

   3. データをクエリします。

      外部テーブルを作成後、AnalyticDB for MySQL で SELECT 文を実行して OSS からデータをクエリできます。

      SELECT * FROM oss_import_test_external_table;

      以下の結果が返されます。

      +------+-------+------+-----------+
      | id   | name  | age  | dt        |
      +------+-------+------+-----------+
      |    1 | james |   10 |2023-06-15 |
      |    2 | bond  |   20 |2023-06-15 |
      |    3 | jack  |   30 |2023-06-15 |
      |    4 | lucy  |   40 |2023-06-15 |
      +------+-------+------+-----------+
      4 rows in set (0.35 sec)

   4. AnalyticDB for MySQL に、インポートされた OSS データを格納するためのテーブルを作成します。以下の文が例です。

      説明

      内部テーブルは、ステップ b の外部テーブルとフィールド名、フィールド数、フィールド順序、およびデータ型が一致している必要があります。

      CREATE TABLE adb_import_test
      (
        id INT,
        name VARCHAR(1023),
        age INT,
        dt VARCHAR(1023)
      )
      DISTRIBUTED BY HASH(id);

   5. データをインポートします。

      重要

      エラスティック インポートでは、INSERT OVERWRITE INTO 文を使用したデータのインポートのみがサポートされています。

      • 方法 1：INSERT OVERWRITE INTO 文を実行してデータをエラスティックにインポートし、テーブル内の既存データを上書きします。以下の文が例です。

        /*+elastic_load=true, elastic_load_configs=[adb.load.resource.group.name=resource_group]*/
        INSERT OVERWRITE INTO adb_demo.adb_import_test SELECT * FROM adb_demo.oss_import_test_external_table;

      • 方法 2：INSERT OVERWRITE INTO 文を非同期で実行してデータをエラスティックにインポートします。SUBMIT JOB 文を使用して、バックグラウンドでスケジュールされる非同期タスクを送信できます。

        /*+elastic_load=true, elastic_load_configs=[adb.load.resource.group.name=resource_group]*/
        SUBMIT JOB INSERT OVERWRITE INTO adb_demo.adb_import_test SELECT * FROM adb_demo.oss_import_test_external_table;

        重要

        エラスティック インポートタスクを非同期で送信する際に、優先度付きキューを設定することはできません。

        以下の結果が返されます。

        +---------------------------------------+
        | job_id                                |
        +---------------------------------------+
        | 202308151719510210170190**********    |

      SUBMIT JOB を使用して非同期タスクを送信した後、返された結果はタスクが正常に送信されたことのみを示します。ジョブ ID を使用して非同期タスクを終了させたり、ステータスをクエリしてタスクが正常に実行されたかどうかを確認したりできます。詳細については、「非同期インポートジョブの送信」をご参照ください。

      ヒントパラメーター：

      • elastic_load：エラスティック インポートを使用するかどうかを指定します。有効値：true および false。デフォルト値：false。

      • elastic_load_configs：エラスティック インポート機能の構成パラメーターです。パラメーターは角括弧 ([ ]) で囲み、複数のパラメーターは縦棒 (|) で区切る必要があります。以下の表にパラメーターを示します。

        パラメーター	必須	説明
        adb.load.resource.group.name	はい	エラスティック インポートジョブを実行するジョブリソースグループの名前。
        adb.load.job.max.acu	いいえ	エラスティック インポートジョブの最大リソース量。単位：AnalyticDB 計算ユニット (ACU)。最小値：5 ACU。デフォルト値：シャード数 + 1。 クラスターのシャード数をクエリするには、以下の文を実行します。 SELECT count(1) FROM information_schema.kepler_meta_shards;
        spark.driver.resourceSpec	いいえ	Spark ドライバーのリソースタイプ。 デフォルト値：small。有効値については、「Conf 構成パラメーター」トピックの「Spark アプリケーション構成パラメーター」表の「Type」列をご参照ください。
        spark.executor.resourceSpec	いいえ	Spark エグゼキューターのリソースタイプ。 デフォルト値：large。有効値については、「Conf 構成パラメーター」トピックの「Spark アプリケーション構成パラメーター」表の「Type」列をご参照ください。
        spark.adb.executorDiskSize	いいえ	Spark エグゼキューターのディスク容量。有効値：(0,100]。単位：GiB。デフォルト値：10 GiB。詳細については、「Conf 構成パラメーター」トピックの「ドライバーおよびエグゼキューターのリソースの指定」セクションをご参照ください。

   6. （オプション）送信されたインポートタスクがエラスティック インポートタスクかどうかを確認します。

      SELECT job_name, (job_type = 3) AS is_elastic_load FROM INFORMATION_SCHEMA.kepler_meta_async_jobs where job_name = "2023081818010602101701907303151******";

      以下の結果が返されます。

      +---------------------------------------+------------------+
      | job_name                              | is_elastic_load  |
      +---------------------------------------+------------------+
      | 20230815171951021017019072*********** |       1          |
      +---------------------------------------+------------------+

      is_elastic_load の値が 1 の場合、送信されたインポートタスクはエラスティック インポートタスクです。値が 0 の場合、タスクは通常のインポートタスクです。

データウェアハウス エディション

1. クラスターへの接続 およびデータベースの作成を行います。

   CREATE DATABASE adb_demo;

2. 外部テーブルを作成します。CREATE TABLE 構文を使用して、CSV、Parquet、または ORC 形式の OSS 外部テーブルを作成します。構文の詳細については、「OSS 外部テーブル構文」をご参照ください。

   このトピックでは、CSV 形式のパーティションなし外部テーブルを例として使用します。

   CREATE TABLE IF NOT EXISTS oss_import_test_external_table
   (
       id INT,
       name VARCHAR(1023),
       age INT,
       dt VARCHAR(1023) 
   )
   ENGINE='OSS'
   TABLE_PROPERTIES='{
       "endpoint":"oss-cn-hangzhou-internal.aliyuncs.com",
       "url":"oss://testBucketname/adb/dt=2023-06-15/person.csv",
       "accessid":"accesskey_id",
       "accesskey":"accesskey_secret",
       "delimiter":",",
       "skip_header_line_count":0,
       "charset":"utf-8"
   }'; 

3. oss_import_test_external_table 外部テーブルからデータをクエリします。

   説明

   CSV、Parquet、または ORC データファイルの場合、大規模な外部テーブルをクエリすると、大きなパフォーマンスオーバーヘッドが発生する可能性があります。クエリ効率を向上させるため、ステップ 4 および 5 で説明されているように、OSS 外部テーブルから AnalyticDB for MySQL にデータをインポートしてからクエリを実行することを推奨します。

   SELECT * FROM oss_import_test_external_table;

4. AnalyticDB for MySQL に、OSS 外部テーブルからインポートされたデータを格納するためのテーブルを作成します。

   CREATE TABLE IF NOT EXISTS adb_oss_import_test
   (
      id INT,
      name VARCHAR(1023),
      age INT,
      dt VARCHAR(1023) 
   )
   DISTRIBUTED BY HASH(id);

5. INSERT 文を実行して、OSS 外部テーブルから AnalyticDB for MySQL にデータをインポートします。

   重要

   INSERT INTO または INSERT OVERWRITE SELECT を使用したデータインポート操作は、デフォルトで同期的に実行されます。数百ギガバイトなどの大規模なデータセットの場合、クライアントは長時間にわたり AnalyticDB for MySQL サーバーとの持続的接続を維持する必要があります。この間、ネットワークの問題により接続が中断され、インポートが失敗する可能性があります。そのため、大規模なデータ量の場合は、SUBMIT JOB INSERT OVERWRITE SELECT を使用してインポートを非同期で実行することを推奨します。

   • 方法 1：INSERT INTO 文を実行してデータをインポートします。プライマリキーが重複する場合、現在の書き込み操作は無視され、データは更新されません。この動作は INSERT IGNORE INTO と同等です。詳細については、「INSERT INTO」をご参照ください。以下の文が例です。

     INSERT INTO adb_oss_import_test
     SELECT * FROM oss_import_test_external_table;

   • 方法 2：INSERT OVERWRITE 文を実行してデータをインポートし、テーブル内の既存データを上書きします。以下の文が例です。

     INSERT OVERWRITE adb_oss_import_test
     SELECT * FROM oss_import_test_external_table;

   • 方法 3：INSERT OVERWRITE 文を非同期で実行してデータをインポートします。SUBMIT JOB を使用して、バックグラウンドでスケジュールされる非同期タスクを送信できます。書き込みタスクの前にヒント (/*+ direct_batch_load=true*/) を追加して、タスクを高速化できます。詳細については、「非同期書き込み」をご参照ください。以下の文が例です。

     SUBMIT JOB INSERT OVERWRITE adb_oss_import_test
     SELECT * FROM oss_import_test_external_table;

     以下の結果が返されます。

     +---------------------------------------+
     | job_id                                |
     +---------------------------------------+
     | 2020112122202917203100908203303****** |

     非同期タスクの送信方法の詳細については、「非同期インポートジョブの送信」をご参照ください。

エンタープライズ、ベーシック、およびデータレイクハウス

Enterprise Edition、Basic Edition、および Data Lakehouse Edition における OSS 外部テーブルの作成構文については、「OSS 外部テーブル」をご参照ください。

データウェアハウス

CREATE TABLE [IF NOT EXISTS] table_name
(column_name column_type[, …])
ENGINE='OSS'
TABLE_PROPERTIES='{
    "endpoint":"endpoint",
    "url":"OSS_LOCATION",
    "accessid":"accesskey_id",
    "accesskey":"accesskey_secret",
    "format":"csv|orc|parquet|text
    "delimiter|field_delimiter":";",
    "skip_header_line_count":1,
    "charset":"utf-8"
}';

CREATE TABLE adb_csv_hive_format_oss (
  a tinyint,
  b smallint,
  c int,
  d bigint,
  e boolean,
  f float,
  g double,
  h varchar,
  i varchar, -- binary
  j timestamp,
  k DECIMAL(10, 4),
  l varchar, -- char(10)
  m varchar, -- varchar(100)
  n date
) ENGINE = 'OSS' TABLE_PROPERTIES='{
    "format": "csv",
    "endpoint":"oss-cn-hangzhou-internal.aliyuncs.com",
    "accessid":"accesskey_id",
    "accesskey":"accesskey_secret",
    "url":"oss://testBucketname/adb_data/",
    "delimiter": "\\1",
    "null_value": "\\\\N",
    "oss_ignore_quote_and_escape": "true",
    "ossnull": 2
}';

Parquet ファイルと AnalyticDB for MySQL のデータ型マッピング

ORC ファイルと AnalyticDB for MySQL のデータ型マッピング

Paimon ファイルと AnalyticDB for MySQL のデータ型マッピング