AnalyticDB:Enterprise Edition・Basic Edition・Data Lakehouse Edition

AnalyticDB for MySQL クラスターと同じリージョンに OSS バケットとディレクトリを作成します。詳細については、OSS のアクティブ化、バケットの作成、ディレクトリの管理をご参照ください。

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

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

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

3. 次のステートメントを実行して、Hudi データを格納するための外部データベース spark_external_db を作成します。この例では、oss://testBucketName/adb-test-1/ パスを使用します。ステートメントを実行するには、SQL の実行 (F8) をクリックします。

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

4. spark_external_db データベースに、Hudi データを格納するための spark_hudi_table という名前の外部テーブルを作成します。 この例では、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');

OSS Hudi 外部テーブルからデータをクエリします。

1. 次のステートメントを実行して、spark_hudi_table 外部テーブルからデータをクエリします。ステートメントを実行するには、SQL の実行 (F8) をクリックします。

   SELECT * FROM spark_external_db.spark_hudi_table;

   説明

   Data Lakehouse Edition クラスターに予約済みコンピューティングリソースがある場合、または Enterprise Edition クラスターに予約済みリソースがある場合、オンラインモードで XIHE MPP SQL を使用して外部テーブルからデータをクエリできます。これを行うには、XIHE エンジンとインタラクティブリソースグループを選択します。

2. ジョブを開発する > Spark Jar 開発 > ジョブを開発する ページで、アプリケーションリスト で目的の SQL クエリを見つけ、操作 列の ログ をクリックして、ログ内のテーブルデータを表示します。

AnalyticDB for MySQL クラスターと同じリージョンに OSS バケットとディレクトリを作成します。詳細については、OSS のアクティブ化、バケットの作成、ディレクトリの管理をご参照ください。

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

この例では、xihe_oss.txt データファイルを oss://adb-test-1/test_xihe/ OSS パスにアップロードします。これは、列をコンマ (,)、行を改行で区切ったテキストファイルです：

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

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

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

2. SQL Console ウィンドウで、ジョブベースのリソースグループと XIHE エンジンを選択します。

3. 次のステートメントを実行して、外部データベース xihe_external_db を作成します。ステートメントを実行するには、SQL の実行 (F8) をクリックします。

   CREATE EXTERNAL DATABASE xihe_external_db;

4. 次のステートメントを実行して、xihe_oss_table という名前の外部テーブルを作成します。ステートメントを実行するには、SQL の実行 (F8) をクリックします。

   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';

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

次のステートメントを実行して、xihe_oss_table 外部テーブルからデータをクエリします。ステートメントを実行するには、SQL の実行 (F8) をクリックします。

SELECT * FROM xihe_external_db.xihe_oss_table;

ODS レイヤーテーブルを作成します。

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

2. SQL Console ウィンドウで、Spark エンジンとジョブベースのリソースグループを選択します。

3. 次のステートメントを実行して、test_spark_db という名前のデータベースを作成します。 ステートメントを実行するには、SQL の実行 (F8) をクリックします。

   CREATE DATABASE test_spark_db;

4. test_spark_db データベースで、インデックスなしでパーティション化された ODS レイヤーテーブル adb_spark_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);

DWD レイヤーテーブルを作成します。

このチュートリアルでは、ODS レイヤーテーブルからデータを読み取り、データをフィルタリングし、フィルタリングされたデータを DWD レイヤーテーブルに書き込む方法を説明します。

1. test_spark_db データベースに、パーティションテーブル adb_spark_dwd をインデックスなしで作成します。

   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. (任意) オフラインのホットデータをクエリする前に、SQL Console ウィンドウで XIHE エンジンと user_default (Interactive) リソースグループを選択し、次のステートメントを実行します。

   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 エンジンに切り替え、ODS レイヤーテーブル adb_spark_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 を使用してクエリを実行する場合、結果にはテーブルデータが表示されません。テーブルデータを表示するには、次のステップを実行してください。

5. 任意: Spark Jar 開発 ページで、アプリケーションリスト で対象の SQL クエリを見つけ、操作 列にある ログ をクリックします。ログでテーブルデータを確認できます。

ADS レイヤーテーブルを作成します。

ADS レイヤーには、ビジネス分析のために DWD レイヤーから加工されたデータが含まれています。クエリパフォーマンスを確保するには、ADS テーブルにインデックスを作成する必要があります。このチュートリアルでは、DWD レイヤーのテーブル adb_spark_dwd から age 列の値が 15 より大きいデータを読み取り、ADS レイヤーのテーブル adb_spark_ads に書き込む方法を説明します。

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

   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;

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

AnalyticDB for MySQL の Enterprise Edition、Basic Edition、Data Lakehouse Edition クラスターは、Spark SQL または XIHE BSP SQL を使用したオフラインデータクエリと、XIHE MPP SQL を使用したオンラインリアルタイムデータクエリをサポートします。データの適時性を確保するために、このチュートリアルではオンラインモードで XIHE MPP SQL を使用して ADS レイヤーテーブルデータをクエリします。

1. SQL Console ウィンドウで、XIHE エンジンと user_default (インタラクティブ) リソースグループを選択します。

2. 次のステートメントを実行して、ADS レイヤーテーブルからデータをクエリします。

   SELECT * FROM test_spark_db.adb_spark_ads;

   次の結果が返されます：

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

ODS レイヤーテーブルを作成します。

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

2. SQL Console ウィンドウで、XIHE エンジンとジョブベースのリソースグループを選択します。

3. 次のステートメントを入力し、SQL の実行 (F8) をクリックして、test_xihe_db という名前のデータベースを作成します。

   CREATE DATABASE test_xihe_db;

4. 次のステートメントを入力し、SQL の実行 (F8) をクリックします。このコマンドは、パーティションテーブル adb_xihe_ods を、test_xihe_db データベースの 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);

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) をクリックします。このステートメントは、id 列が 002 ではない ODS レイヤーテーブル adb_xihe_ods からデータを読み取り、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 | Dan | 25 || 1 | Anna | 18 || 3 | Cindy | 12 |+------+-------+------+

ADS レイヤーテーブルを作成します。

ADS レイヤーのデータは、DWD レイヤーのデータからより細かくフィルター処理され、ビジネス分析に直接使用できます。これには一定のクエリ速度が必要なため、ADS レイヤーのテーブルを作成するときにインデックスを追加する必要があります。このチュートリアルでは、DWD レイヤーのテーブル adb_xihe_dwd から age 列の値が 15 より大きいデータを読み取り、ADS レイヤーのテーブル adb_xihe_ads に書き込む方法を説明します。

1. 次のステートメントを入力し、SQL の実行 (F8) をクリックして、インデックス付きでパーティション化された AnalyticDB for MySQL テーブル adb_xihe_ads を test_xihe_db データベースに作成します。

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

2. 次のステートメントを入力し、SQL の実行 (F8) をクリックします。このステートメントは、age 列の値が 15 より大きい DWD レイヤーのデータテーブル adb_xihe_dwd からデータを読み取り、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;

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

AnalyticDB for MySQL の Data Lakehouse Edition クラスターは、Spark SQL または XIHE BSP SQL を使用したオフラインデータクエリと、XIHE MPP SQL を使用したオンラインリアルタイムデータクエリをサポートします。データの適時性を確保するために、このチュートリアルではオンラインモードで XIHE MPP SQL を使用して ADS レイヤーテーブルデータをクエリします。

1. SQL Console ウィンドウで、XIHE エンジンと user_default (インタラクティブ) リソースグループを選択します。

2. 次のステートメントを入力し、SQL の実行 (F8) をクリックして ADS レイヤーテーブルのデータをクエリします。

   SELECT * FROM test_xihe_db.adb_xihe_ads;

   次の結果が返されます：

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