AnalyticDB:Data Lakehouse Edition へのデータインポートに外部テーブルを使用する

サンプルデータ

1,james,10,2023-06-15
2,bond,20,2023-06-15
3,jack,30,2023-06-15
4,lucy,40,2023-06-15       

手順

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

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

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

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

   通常のインポート方法または Elastic Import 方法を使用してデータをインポートできます。 デフォルトでは、通常のインポート方法が使用されます。 通常のインポート方法を使用する場合、データは計算ノードから読み取られ、インデックスはストレージノードに作成されます。 通常のインポート方法は、計算リソースとストレージリソースを消費します。 Elastic Import 方法を使用する場合、データは読み取られ、Serverless Spark ジョブ用にインデックスが作成されます。 Elastic Import 方法は、ジョブリソースグループのリソースを消費します。 ジョブリソースグループを持つ V3.1.10.0 以降の AnalyticDB for MySQL Enterprise Edition、Basic Edition、および Data Lakehouse Edition クラスタのみが、Elastic Import 方法をサポートしています。 詳細については、「データインポート方法」をご参照ください。

   通常のインポート

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

      CREATE EXTERNAL DATABASE adb_external_db;

   2. CREATE EXTERNAL TABLE 文を使用して、adb_external_db データベースに OSS 外部テーブルを作成します。 この例では、外部テーブルの名前は adb_external_db.person です。

      説明

      AnalyticDB for MySQL の OSS 外部テーブルは、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. OSS 外部テーブルにパーティションを追加します。 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 外部テーブルにパーティションを追加する方法、または OSS 外部テーブルからパーティションを削除する方法については、「ALTER TABLE (外部テーブル)」をご参照ください。

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

      構文については、「CREATE EXTERNAL TABLE」をご参照ください。

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

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

      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 rows in set (0.35 sec)

   4. AnalyticDB for MySQL クラスタにデータベースとテーブルを作成します。 詳細については、CREATE DATABASE および AnalyticDB for MySQL をご参照ください。 データベースをすでに作成している場合は、この手順をスキップしてください。

      CREATE DATABASE adb_demo; 

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

      説明

      作成される内部テーブルは、手順 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 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 文を実行して、データを非同期的にインポートします。 詳細については、INSERT OVERWRITE SELECT トピックの「非同期書き込み」セクションをご参照ください。

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

   Elastic Import

   1. データベースを作成します。 データベースをすでに作成している場合は、この手順をスキップしてください。

      CREATE DATABASE adb_demo; 

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

      説明

      • AnalyticDB for MySQL の OSS 外部テーブルは、OSS オブジェクトと同じ名前、数、順序、およびデータ型のフィールドを使用する必要があります。

      • Elastic Import 方法では、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://<bucket-name>/adb/oss_import_test_data.csv",
          "accessid":"************",
          "accesskey":"************",
          "delimiter":","
      }';

      重要

      外部テーブルを作成する際に、CSV、Parquet、または ORC 外部テーブルに対して、以下の TABLE_PROPERTIES パラメーターを構成できます。

      • 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 外部テーブル」セクションを含む「Data Warehouse Edition へのデータインポートに外部テーブルを使用する」Topic をご参照ください。

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

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

      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 クラスタにテーブルを作成します。

      説明

      作成される内部テーブルは、手順 b で作成された外部テーブルと同じ名前、数、順序、およびデータ型のフィールドを使用する必要があります。

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

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

      重要

      Elastic Import 方法では、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;

        重要

        Elastic Import ジョブを非同期的に送信する場合、優先度付きキューを構成することはできません。

        サンプル結果:

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

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

      ヒントパラメータ:

      • elastic_load: Elastic Import を使用するかどうかを指定します。 有効な値: true および false。 デフォルト値: false。

      • elastic_load_configs: エラスティックインポート機能の構成パラメーター。パラメーターは角かっこ（[ ]）で囲み、複数のパラメーターは縦棒（｜）で区切る必要があります。次の表でパラメーターについて説明します。

        パラメータ	必須	説明
        adb.load.resource.group.name	はい	Elastic Import ジョブを実行するジョブリソースグループの名前。
        adb.load.job.max.acu	いいえ	Elastic Import ジョブの最大リソース量。 単位: AnalyticDB Compute Unit (ACU)。 最小値: 5 ACU。 デフォルト値: シャード数 + 1。 クラスタ内のシャード数をクエリするには、次の文を実行します。 SELECT count(1) FROM information_schema.kepler_meta_shards;
        spark.driver.resourceSpec	いいえ	Spark ドライバーのリソースタイプです。 デフォルト値: small。「Spark リソース仕様」テーブルの Conf 構成パラメーター Topic の「タイプ」列に有効な値が記載されています。
        spark.executor.resourceSpec	いいえ	Spark エグゼキュータのリソースタイプです。 デフォルト値: large。有効な値については、Conf 構成パラメーター Topic の「Spark リソース仕様」テーブルの「タイプ」列を参照してください。
        spark.adb.executorDiskSize	いいえ	Spark エグゼキューターのディスク容量。 有効な値: (0,100]。 単位: GiB。 デフォルト値: 10 GiB。 詳細については、「Conf 構成パラメータ」トピックの「ドライバーとエグゼキューターのリソースを指定する」セクションをご参照ください。

   6. (オプション) 送信されたジョブが Elastic Import ジョブであるかどうかを確認します。

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

      Elastic Import ジョブが送信された場合、is_elastic_load パラメータには 1 が返されます。 通常のインポートジョブが送信された場合は、0 が返されます。

付録: Parquet、ORC、および AnalyticDB for MySQL 間のデータ型マッピング