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

Enterprise Edition、Basic Edition、および Data Lakehouse Edition

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

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

   2. 左側のナビゲーションウィンドウで、[ジョブ開発] > Sql開発 をクリックします。

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

   通常のインポート (デフォルト) メソッドまたはエラスティックインポートメソッドを使用してデータをインポートできます。通常のインポートメソッドは、計算ノードでソースデータを読み取り、ストレージノードにインデックスを構築します。このメソッドは、コンピューティングリソースとストレージリソースを消費します。エラスティックインポートメソッドは、サーバーレス Spark ジョブでソースデータを読み取り、インデックスを構築します。このメソッドは、ジョブタイプリソースグループのリソースを消費します。エラスティックインポートメソッドは、Enterprise Edition、Basic Edition、および Data Lakehouse Edition の Milvus バージョン 3.1.10.0 以降を実行し、ジョブタイプリソースグループを持つクラスターでのみサポートされます。詳細については、「データインポートメソッド」をご参照ください。

   通常のインポート

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

      CREATE EXTERNAL DATABASE adb_external_db;

   2. 外部テーブルを作成します。CREATE EXTERNAL TABLE 文を使用して、adb_external_db データベースに OSS 外部テーブルを作成します。この例では、外部テーブルの名前は 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. OSS からインポートされたデータを格納するために AnalyticDB for MySQL にテーブルを作成します。次の文に例を示します。

      説明

      作成する内部テーブルには、外部テーブルと同じフィールド名、フィールド数、フィールド順序、およびフィールドタイプが必要です。

      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、および partition_column パラメーターのみがサポートされます。

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

      • ORC 形式: endpoint、url、accessid、accesskey、format、および 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. OSS からインポートされたデータを格納するために AnalyticDB for MySQL にテーブルを作成します。次の文に例を示します。

      説明

      作成する内部テーブルには、外部テーブルと同じフィールド名、フィールド数、フィールド順序、およびフィールドタイプが必要です。

      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 を使用して非同期ジョブを送信した後、返される結果はジョブが正常に送信されたことのみを示します。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 の場合、送信されたインポートタスクは通常のインポートタスクです。

Data Lakehouse Edition

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 形式で大量のデータを含む外部テーブルをクエリすると、高いパフォーマンスオーバーヘッドが発生する可能性があります。クエリ効率を向上させるには、次の手順で説明するように、OSS 外部テーブルから AnalyticDB for MySQL にデータをインポートします。

   SELECT * FROM oss_import_test_external_table;

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

   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

Enterprise Edition、Basic Edition、および Data Lakehouse Edition の OSS 外部テーブルの構文と説明の詳細については、「OSS 外部テーブル」をご参照ください。

Data Lakehouse Edition

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 のデータ型マッピング