AnalyticDB:Enterprise Edition、Basic Edition、Data Lakehouse Edition のクイックスタート

Spark SQL を使用した外部テーブルジョブの開発

以下の手順に従います：

1. AnalyticDB for MySQL クラスターと同じリージョンで OSS バケットおよびディレクトリを作成します。詳細については、「OSS の有効化」、「OSS バケットの作成（コンソール）」、「ディレクトリの作成」をご参照ください。

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

   1. クラスター詳細ページの左側ナビゲーションウィンドウで、ジョブを開発する > Sql開発 をクリックします。

   2. SQLConsole ウィンドウで、Spark エンジン、ジョブリソースグループ、または Spark エンジン付きインタラクティブリソースグループを選択します。

   3. 以下の文を入力し、SQL の実行 (F8) をクリックして、Hudi データを格納する外部データベース spark_external_db を作成します。この例ではパス oss://testBucketName/adb-test-1/ を使用します。

      CREATE DATABASE spark_external_db
      LOCATION 'oss://testBucketName/adb-test-1/';

   4. spark_hudi_table という名前の外部テーブルを spark_external_db データベース内に作成し、Hudi データを格納します。この例ではパス oss://testBucketName/adb-test-1/spark_external_db/ を使用します。

      CREATE TABLE spark_external_db.spark_hudi_table
      (id int,
       name string,
       score int,
       city string
      )
      using hudi
      partitioned by (id)
      tblproperties
      (primaryKey = 'id', 
      preCombineField = 'city')
      LOCATION 'oss://testBucketName/adb-test-1/spark_external_db/';

   5. データを挿入します。

      INSERT OVERWRITE spark_external_db.spark_hudi_table PARTITION(id) VALUES
      (001,'Anna',99,'London'),
      (002,'Bob',67,'USA'),
      (003,'Cindy',78,'Spain'),
      (004,'Dan',100,'China');

3. OSS Hudi 外部テーブルのデータをクエリします。

   1. 以下の文を入力し、SQL の実行 (F8) をクリックして、外部テーブル spark_hudi_table のデータをクエリします。

      SELECT * FROM spark_external_db.spark_hudi_table;

      説明

      Data Lakehouse Edition クラスターに計算予約リソースが設定されている場合、または Enterprise Edition クラスターに予約済みリソースが設定されている場合、Xihe MPP SQL オンラインモード（Xihe エンジンおよびインタラクティブリソースグループを選択）を使用して外部テーブルのデータをクエリできます。

   2. ジョブを開発する > Spark Jar 開発 ページの アプリケーションリスト 一覧で、SQL クエリの横にある 操作 をクリックし、ログ をクリックします。ログでテーブルのデータを確認します。

Xihe BSP SQL を使用した外部テーブルジョブの開発

以下の手順に従います：

1. AnalyticDB for MySQL クラスターと同じリージョンで OSS バケットおよびディレクトリを作成します。詳細については、「OSS の有効化」、「OSS バケットの作成（コンソール）」、「ディレクトリの作成」をご参照ください。

2. サンプルデータをアップロードします。

   この例では、データファイル xihe_oss.txt を OSS パス oss://adb-test-1/test_xihe/ にアップロードします。行は改行で区切られ、列はカンマ（,）で区切られます。サンプルデータは以下のとおりです：

   001,Anna,99,London
   002,Bob,67,USA
   003,Cindy,78,Spain
   004,Dan,100,China

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

   1. クラスター詳細ページの左側ナビゲーションウィンドウで、ジョブを開発する > Sql開発 をクリックします。

   2. SQLConsole ウィンドウで、ジョブリソースグループおよび Xihe エンジンを選択します。

   3. 以下の文を入力し、SQL の実行 (F8) をクリックして、外部データベース xihe_external_db を作成します。

      CREATE EXTERNAL DATABASE xihe_external_db;

   4. 以下の文を入力し、SQL の実行 (F8) をクリックして、外部テーブル xihe_oss_table を作成します。

      CREATE EXTERNAL TABLE xihe_external_db.xihe_oss_table
      (
       id int ,
       name string ,
       score int,
       city string
      )
      ROW FORMAT DELIMITED FIELDS TERMINATED BY  ','
      STORED AS TEXTFILE
      LOCATION 'oss://adb-test-1/test_xihe/xihe_oss.txt';

4. OSS 外部テーブルをクエリします。

   以下の文を入力し、SQL の実行 (F8) をクリックして、外部テーブル xihe_oss_table のデータをクエリします。

   SELECT * FROM xihe_external_db.xihe_oss_table;

   説明

   Data Lakehouse Edition クラスターに計算予約リソースが設定されている場合、または 、Xihe MPP SQL オンラインモード（Xihe エンジンおよびインタラクティブリソースグループを選択）を使用して外部テーブルのデータをクエリできます。

Spark SQL を使用した内部テーブルジョブの開発

1. ODS レイヤーのデータテーブルを作成します。

   1. クラスター詳細ページの左側ナビゲーションウィンドウで、ジョブを開発する > Sql開発 をクリックします。

   2. SQLConsole ウィンドウで、Spark エンジンおよびジョブリソースグループを選択します。

   3. 以下の文を入力し、SQL の実行 (F8) をクリックして、データベース test_spark_db を作成します。

      CREATE DATABASE test_spark_db;

   4. adb_spark_ods という名前のインデックスなしパーティションテーブルを test_spark_db データベース内に ODS レイヤー用に作成し、データを挿入します。

      CREATE TABLE test_spark_db.adb_spark_ods
        (id int,
         name string,
         age int)
      USING adb
      tblproperties (
      'distributeType' = 'HASH',
      'distributeColumns' = 'id',
      'partitionType' = 'VALUE',
      'partitionColumn' = 'age', 
      'partitionCount' = '200', 
      'indexAll' = 'false')

      INSERT OVERWRITE test_spark_db.adb_spark_ods PARTITION(age) VALUES
      (001,'Anna',18),
      (002,'Bob',22),
      (003,'Cindy',12),
      (004,'Dan',25);

2. DWD レイヤーのデータテーブルを作成します。

   本チュートリアルでは、ODS レイヤーのテーブルからデータを読み取り、フィルター処理した後、DWD レイヤーのテーブルに書き込みます。

   1. adb_spark_dwd という名前のインデックスなしパーティションテーブルを test_spark_db データベース内に作成します。

      CREATE TABLE test_spark_db.adb_spark_dwd (
        id int,
        name string,
        age int
      )
      USING adb
      TBLPROPERTIES(
        'distributeType'='HASH', 
        'distributeColumns'='id', 
        'partitionType'='value',
        'partitionColumn'='age',
        'partitionCount'='200',
        'indexAll'='false')

   2. （任意）オフラインホットデータをクエリする前に、SQLConsole ウィンドウで Xihe エンジンおよび（インタラクティブ）user_default リソースグループを選択し、以下の文を実行します：

      SET adb_config CSTORE_HOT_TABLE_ALLOW_SINGLE_REPLICA_BUILD=true;
      SET adb_config ELASTIC_ENABLE_HOT_PARTITION_HAS_HDD_REPLICA=true;
      SET adb_config ELASTIC_PRODUCT_ENABLE_MIXED_STORAGE_POLICY=true;

      重要

      オフラインホットデータをクエリする前にこれらのパラメーターを正しく設定しないと、その後の SQL 文が失敗します。

   3. Spark エンジンに切り替え、adb_spark_ods の ODS レイヤーのテーブルから id 列が 002 でないデータを読み取り、DWD レイヤーのテーブルに書き込みます。

      INSERT OVERWRITE test_spark_db.adb_spark_dwd partition(age) 
      SELECT 
        id,
        name,
        age
      FROM test_spark_db.adb_spark_ods WHERE id != 002;

   4. adb_spark_dwd テーブルのデータをクエリします。

      SELECT * FROM test_spark_db.adb_spark_dwd;

      説明

      Spark SQL を使用して SELECT 文を実行すると、結果としてテーブルのデータは表示されません。テーブルのデータを表示するには、以下の任意の手順に従ってください。

   5. 任意：Spark JAR 開発 ページの アプリケーション 一覧で、SQL クエリの横にある 操作 をクリックし、ログ をクリックします。ログでテーブルのデータを確認します。

3. ADS レイヤーのデータを作成します。

   ADS レイヤーのデータは、DWD レイヤーから精緻化されたフィルター処理を経て派生され、ビジネス分析に直接使用可能な状態です。ADS レイヤーのテーブルは高速なクエリ性能を必要とするため、ADS レイヤーのテーブルを作成する際にインデックスを追加する必要があります。このチュートリアルでは、adb_spark_dwd テーブル（`age` 列の値が 15 より大きい）からデータを読み取り、ADS レイヤーのテーブル adb_spark_ads に書き込みます。

   1. adb_spark_ads という名前のインデックス付きパーティションテーブルを test_spark_db データベース内に作成します。

      CREATE TABLE test_spark_db.adb_spark_ads (
        id int,
        name string,
        age int
      )
      USING adb
      TBLPROPERTIES(
        'distributeType'='HASH', 
        'distributeColumns'='id', 
        'partitionType'='value',
        'partitionColumn'='age',
        'partitionCount'='200',
        'indexAll'='true')

   2. DWD レイヤーのデータテーブル adb_spark_dwd から age が 15 より大きいデータを読み取り、ADS レイヤーのデータテーブル adb_spark_ads に書き込みます。

      INSERT OVERWRITE test_spark_db.adb_spark_ads partition(age) 
      SELECT 
        id,
        name,
        age 
      FROM test_spark_db.adb_spark_dwd WHERE age > 15;

4. ADS レイヤーのテーブルデータをクエリします。

   AnalyticDB for MySQL Enterprise Edition、Basic Edition、および Data Lakehouse Edition クラスターでは、オフラインデータクエリ（Spark SQL または Xihe BSP SQL を使用）およびオンラインリアルタイムクエリ（Xihe MPP SQL を使用）の両方がサポートされています。リアルタイムの結果を保証するため、本チュートリアルでは Xihe MPP SQL オンラインモードを使用して ADS レイヤーのテーブルデータをクエリします。

   1. SQLConsole ウィンドウで、Xihe エンジンおよび（インタラクティブ）user_default リソースグループを選択します。

   2. 以下の文を実行して、ADS レイヤーのテーブルデータをクエリします。

      SELECT * FROM test_spark_db.adb_spark_ads;

      結果：

      +------+-------+------+
      | id   | name  | age  |
      +------+-------+------+
      |    4 | Dan   |   25 |
      |    1 | Anna  |   18 |
      +------+-------+------+

Xihe BSP SQL を使用したジョブの開発

1. ODS レイヤーのデータテーブルを作成します。

   1. クラスター詳細ページの左側ナビゲーションウィンドウで、ジョブを開発する > Sql開発 をクリックします。

   2. SQLConsole ウィンドウで、Xihe エンジンおよびジョブリソースグループを選択します。

   3. 以下の文を入力し、SQL の実行 (F8) をクリックします。test_xihe_db という名前のデータベースを作成します。

      CREATE DATABASE test_xihe_db;

   4. 以下の文を入力し、SQL の実行 (F8) をクリックします。test_xihe_db データベース内に、ODS レイヤー用のインデックスなしパーティションテーブル adb_xihe_ods を作成し、データを挿入します。

      CREATE TABLE test_xihe_db.adb_xihe_ods (id int, name string, age int)DISTRIBUTED BY HASH (id)PARTITION BY VALUE (age)LIFECYCLE 4INDEX_ALL='N';

      INSERT INTO test_xihe_db.adb_xihe_ods(id,name,age) VALUES(001,'Anna',18),(002,'Bob',22),(003,'Cindy',12),(004,'Dan',25);

2. DWD レイヤーのデータテーブルを作成します。

   本チュートリアルでは、ODS レイヤーのテーブルからデータを読み取り、フィルター処理した後、DWD レイヤーのテーブルに書き込みます。

   1. 以下の文を入力し、SQL の実行 (F8) をクリックします。test_xihe_db データベース内に、インデックスなしパーティションテーブル adb_xihe_dwd を作成します。

      CREATE TABLE test_xihe_db.adb_xihe_dwd( id int, name string, age int)DISTRIBUTED BY HASH (id)PARTITION BY VALUE (age)LIFECYCLE 4INDEX_ALL = 'N';

   2. 以下の文を入力し、SQL の実行 (F8) をクリックします。運用データストア（ODS）レイヤーのテーブル adb_xihe_ods から id 列が 002 でないデータを読み取り、DWD レイヤーのテーブル adb_xihe_dwd に書き込みます。

      INSERT INTO test_xihe_db.adb_xihe_dwd SELECT  id, name, age FROM test_xihe_db.adb_xihe_ods where id != 002;

   3. 以下の文を入力し、SQL の実行 (F8) をクリックします。adb_xihe_dwd テーブルのデータをクエリします。

      SELECT * FROM test_xihe_db.adb_xihe_dwd;

      結果：

      +------+-------+------+| id   | name  | age  |+------+-------+------+|    4 | Ban   |   25 ||    1 | Anna  |   18 ||    3 | Cindy |   12 |+------+-------+------+

3. ADS レイヤーのデータテーブルを作成します。

   ADS レイヤーのテーブルデータは、DWD レイヤーからさらに詳細なフィルター処理を経て書き込まれ、ビジネス分析に直接利用可能です。ADS レイヤーのテーブルは、高速なクエリ応答を実現するためにインデックスを備える必要があります。本チュートリアルでは、DWD レイヤーのテーブル adb_xihe_dwd から age 列の値が 15 より大きいデータを読み取り、ADS レイヤーのテーブル adb_xihe_ads に書き込みます。

   1. 以下の文を入力し、SQL の実行 (F8) をクリックします。test_xihe_db データベース内に、インデックス付きパーティションテーブル AnalyticDB for MySQL adb_xihe_ads を作成します。

      CREATE TABLE test_xihe_db.adb_xihe_ads (id int, name string, age int)DISTRIBUTED BY HASH (id)PARTITION BY VALUE (age)LIFECYCLE 4; 

   2. 以下の文を入力し、SQL の実行 (F8) をクリックします。DWD レイヤーのデータテーブル adb_xihe_dwd から age 列の値が 15 より大きいデータを読み取り、ADS レイヤーのデータテーブル adb_xihe_ads に書き込みます。

      INSERT INTO test_xihe_db.adb_xihe_ads SELECT  id, name, age FROM test_xihe_db.adb_xihe_dwd WHERE age > 15;

4. ADS レイヤーのデータをクエリします。

   AnalyticDB for MySQL Data Lakehouse Edition クラスターでは、オフラインデータクエリ（Spark SQL または Xihe BSP SQL を使用）およびオンラインリアルタイムクエリ（Xihe MPP SQL を使用）の両方がサポートされています。リアルタイムの結果を保証するため、本チュートリアルでは Xihe MPP SQL オンラインモードを使用して ADS レイヤーのテーブルデータをクエリします。

   1. SQLConsole ウィンドウで、Xihe エンジンおよび（インタラクティブ）user_default リソースグループを選択します。

   2. 以下の文を入力し、SQL の実行 (F8) をクリックします。ADS レイヤーのテーブルデータをクエリします。

      SELECT * FROM test_xihe_db.adb_xihe_ads;

      結果：

      +------+-------+------+| id   | name  | age  |+------+-------+------+|    4 | Ban   |   25 ||    1 | Anna  |   18 |+------+-------+------+