XIHE SQLとSpark SQLを使用したOSSデータへのアクセス - Object Storage Service

AnalyticDB for MySQL Data Lakehouse Edition (V3.0) では、XIHE SQLまたはSpark SQLを使用してObject Storage Service (OSS) データにアクセスできます。このトピックでは、XIHE SQLとSpark SQLを使用して、OSSに保存されているファンド取引データにアクセスする方法について説明します。

前提条件

OSSが有効化されています。 OSSバケットとディレクトリが作成されます。詳細については、「OSSの有効化」、「バケットの作成」、「ディレクトリの作成」をご参照ください。
AnalyticDB for MySQL Data Lakehouse Edition (V3.0) クラスターが作成されます。詳細については、「クラスターの作成」をご参照ください。
予約済みコンピューティングリソースの少なくとも8つのAnalyticDBコンピューティングユニット (ACU) を持つジョブリソースグループが作成されます。詳細については、「リソースグループの作成」をご参照ください。
AnalyticDB for MySQL Data Lakehouse Edition (V3.0) クラスター用にデータベースアカウントが作成されます。
- Alibaba Cloudアカウントを使用する場合は、特権アカウントを作成する必要があります。詳細については、「データベースアカウントの作成」トピックの「特権アカウントの作成」セクションをご参照ください。
- RAM (Resource Access Management) ユーザーを使用する場合は、特権アカウントと標準アカウントの両方を作成し、標準アカウントをRAMユーザーに関連付ける必要があります。詳細については、「データベースアカウントの作成」および「データベースアカウントの関連付けまたは関連付けの解除」をご参照ください。
AliyunADBSparkProcessingDataRoleロールが作成され、AnalyticDB for MySQLは他のクラウドリソースへのアクセスが許可されています。詳細については、「Alibaba Cloudアカウント内での認証の実行」をご参照ください。

手順

データを準備します。
1. シミュレートされたデータをダウンロードして解凍します。
2. アップロードトランザクション情報テーブルと、ユーザー情報テーブルシミュレートされたデータから<bucketname>/adb/trade /と<bucketname>/adb/user /OSSバケット内のそれぞれのディレクトリ。詳細については、「オブジェクトのアップロード」をご参照ください。
  説明
  - シミュレートされたデータでは、トランザクション情報テーブルの名前はdc_trade_final_ddで始まり、ユーザー情報テーブルの名前はuser_infoで始まります。
  - < bucketname> をOSSバケットの名前に置き換えます。

データにアクセスします。

XIHE SQLを使用したOSSデータへのアクセス

AnalyticDB for MySQLコンソールにログインします。ホームページの左上でリージョンを選択します。左側のナビゲーションウィンドウで、クラスターリスト をクリックします。 Lake Warehouse Edition(3.0) タブで、管理するクラスターを見つけ、クラスターIDをクリックします。
左側のナビゲーションウィンドウで、 [ジョブ開発] > [SQL開発] を選択します。
SQLConsoleタブで、ジョブリソースグループとXIHEエンジンを選択します。
データベースを作成します。データベースを作成した場合は、この手順をスキップしてください。
```
CREATE DATABASE adb_demo;
```

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

トランザクション情報テーブルの作成

外部テーブルの作成tradelist_csv (
    t_useridストリングコメント 'ユーザーID' 、
    t_dealdate STRING COMMENT 'アプリケーションタイム' 、 
    t_businflagストリング「ビジネスコード」、 
    t_cdateストリングコメント '確認日' 、 
    t_date STRING COMMENT「アプリケーション日付」、
    t_serialno STRING COMMENT'Application No.'、 
    t_agencynoSTRING COMMENT'Agency No.'、 
    t_netnoストリング解説「ブランチNo. 」、
    t_fundaccoストリング解説 'ファンドアカウント' 、
    t_tradeccoストリングCOMMENT'Transaction account' 、
    t_fundcodeストリングコメント 'ファンドコード' 、
    t_sharetypeストリング解説「共有タイプ」、
    t_confirmbalance二重コメント '確認されたバランス' 、
    t_tradefareダブルコメント '取引料金' 、
    t_backfareダブルコメント「サービス料」、
    t_otherfare1 DOUBLE COMMENT' その他の料金1 '、
    t_remarkストリングコメント'
    )
    '、' によって終了するROW FORMAT DELIMITED FIELDS 
    TEXTFIlEとして保存
    ロケーション 'oss://<bucketname>/adb/trade/';

構文の詳細については、「CREATE external TABLE」トピックの「OSS外部テーブル」セクションをご参照ください。構文で、LOCATIONはトランザクション情報テーブルのOSSパスを指定します。

ユーザー情報テーブルの作成

外部テーブルの作成userinfo (
    u_useridストリング「ユーザーID」、
    u_accountdate STRING COMMENT 'アカウント作成日' 、 
    u_genderストリングコメント 'Gender' 、 
    u_age INT COMMENT 'Age' 、 
    u_risk_tolerance INT COMMENT 'リスクトレランス、1-10、10は最高レベルを示します' 、
    u_cityストリング解説「シティ」、 
    u_job STRING COMMENT'Jobカテゴリ、A-K '、 
    u_income DOUBLE COMMENT'年収 (10,000米ドル)'
    )
    '、' によって終了するROW FORMAT DELIMITED FIELDS 
    TEXTFIlEとして保存
    ロケーション 'oss://<bucketname>/adb/user/';

構文の詳細については、「CREATE external TABLE」トピックの「OSS外部テーブル」セクションをご参照ください。構文で、LOCATIONはユーザー情報テーブルのOSSパスを指定します。

外部テーブルのデータを照会します。

同期または非同期でデータを照会できます。クエリを同期して実行すると、クエリ結果が直接表示されます。クエリを非同期で実行すると、ジョブIDが返されます。ジョブIDを使用して、非同期ジョブの状態を確認し、ジョブが成功したかどうかを判断できます。

同期クエリ

例1: 2018年6月3日から2018年6月4日までのSXS_0010機関の最初の100トランザクションレコードを照会します。

SELECT * からtradelist_csv
WHERE t_cdate >= '2018-06-03 'およびt_cdate <= '2018-06-04' およびt_agencyno = 'SXS_0010'
制限100;

サンプル結果:

+ --------------------------------------------------------------------------------------------
| t_userid | t_dealdate | t_businflag | t_cdate | t_date | t_serialno | t_agencyno | t_netno |
----------- --------------------- ----------------------------------------------------------------------------------
| 00041972 | 2018-06-03 09:11:57 | 保証ファンドB | 2018-06-03 | 2018-06-03 | 2018-06-03-000846 | SXS_0010S | STORE-0135 |
----------- --------------------- ----------------------------------------------------------------------------------
| 00051912 | 2018-06-03 09:28:20 | 保証ファンドD | 2018-06-03 | 2018-06-03 | 2018-06-03-001046 | SXS_0010S | STORE-0397 |
----------- --------------------- ----------------------------------------------------------------------------------
| 00120370 | 2018-06-03 11:21:39 | 保証ファンドB | 2018-06-03 | 2018-06-03 | 2018-06-03-002393 | SXS_0010S | STORE-0800 |
----------- --------------------- ----------------------------------------------------------------------------------
| 00165308 | 2018-06-03 12:35:35 | 保証ファンドD | 2018-06-03 | 2018-06-03 | 2018-06-03-003281 | SXS_0010S | STORE-0340 |
----------- --------------------- ----------------------------------------------------------------------------------

説明

サンプル結果にはデータの一部のみが含まれます。コンソールにログインして、実際の結果を表示します。

例2: 異なる都市で男性と女性が購入した資金の合計金額を照会します。この例では、複数のテーブルが結合されています。

SELECT u_city, u_gender, SUM(t_confirmbalance) AS sum_balance
からtradelist_csv , userinfo
WHERE u_userid = t_userid
グループBY u_city, u_gender
ORDER BY sum_balance DESC;

サンプル結果:

+ ---------- --------------- ---------------- +
| u_city | u_gender | sum_balance |
+ ---------- -------------- + ---------------- +
| 北京 | 男性 | 2445539161 |
+ ---------- -------------- + ---------------- +
| 広州 | 男性 | 1271999857 |
+ ---------- -------------- + ---------------- +
| 青島 | 男性 | 1266748660 |
+ ---------- -------------- + ---------------- +
| 武漢 | 男性 | 12641688475 |
+ ---------- ---------------

説明

サンプル結果にはデータの一部のみが含まれます。コンソールにログインして、実際の結果を表示します。

非同期クエリAsynchronous query

異なる都市で男性と女性が購入した資金の合計額を照会します。この例では、複数のテーブルが結合されています。

SUBMIT JOB SELECT u_city, u_gender, SUM(t_confirmbalance) AS sum_balance
からtradelist_csv , userinfo
WHERE u_userid = t_userid
グループBY u_city, u_gender
ORDER BY sum_balance DESC;

サンプル結果:

+ --------------------------------------- +
| job_id |
+ --------------------------------------- +
| 2023091410365702101701713803151 ****** |
+ --------------------------------------- +

SUBMIT JOBステートメントを実行して非同期ジョブを送信した後、返されたジョブIDを使用して非同期ジョブを終了したり、非同期ジョブの状態を照会したりできます。詳細については、「インポートジョブの非同期送信」をご参照ください。

Spark SQLを使用したOSSデータへのアクセス

AnalyticDB for MySQLコンソールにログインします。ホームページの左上でリージョンを選択します。左側のナビゲーションウィンドウで、クラスターリスト をクリックします。 Lake Warehouse Edition(3.0) タブで、管理するクラスターを見つけ、クラスターIDをクリックします。
左側のナビゲーションウィンドウで、 [ジョブ開発] > [SQL開発] を選択します。
SQLConsoleタブで、ジョブリソースグループとSparkエンジンを選択します。
データベースを作成します。データベースを作成した場合は、この手順をスキップしてください。
```
CREATE DATABASE adb_test;
```

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

トランザクション情報テーブルの作成

外部テーブルの作成adb_test.tradelist_csv (
    t_useridストリングコメント 'ユーザーID' 、
    t_dealdate STRING COMMENT 'アプリケーションタイム' 、 
    t_businflagストリング「ビジネスコード」、 
    t_cdateストリングコメント '確認日' 、 
    t_date STRING COMMENT「アプリケーション日付」、
    t_serialno STRING COMMENT'Application No.'、 
    t_agencynoSTRING COMMENT'Agency No.'、 
    t_netnoストリング解説「ブランチNo. 」、
    t_fundaccoストリング解説 'ファンドアカウント' 、
    t_tradeccoストリングCOMMENT'Transaction account' 、
    t_fundcodeストリングコメント 'ファンドコード' 、
    t_sharetypeストリング解説「共有タイプ」、
    t_confirmbalance二重コメント '確認されたバランス' 、
    t_tradefareダブルコメント '取引料金' 、
    t_backfareダブルコメント「サービス料」、
    t_otherfare1 DOUBLE COMMENT' その他の料金1 '、
    t_remarkストリングコメント'
    )
    '、' によって終了するROW FORMAT DELIMITED FIELDS 
    TEXTFIlEとして保存
    ロケーション 'oss://<bucketname>/adb/trade/';

ユーザー情報テーブルの作成

外部テーブルの作成adb_test.userinfo (
    u_useridストリング「ユーザーID」、
    u_accountdate STRING COMMENT 'アカウント作成日' 、 
    u_genderストリングコメント 'Gender' 、 
    u_age INT COMMENT 'Age' 、 
    u_risk_tolerance INT COMMENT 'リスクトレランス、1-10、10は最高レベルを示します' 、
    u_cityストリング解説「シティ」、 
    u_job STRING COMMENT'Jobカテゴリ、A-K '、 
    u_income DOUBLE COMMENT'年収 (10,000米ドル)'
    )
    '、' によって終了するROW FORMAT DELIMITED FIELDS 
    TEXTFIlEとして保存
    ロケーション 'oss://<bucketname>/adb/user/';

クエリデータ。
説明
Spark SQLステートメントを実行しても、クエリ結果は返されません。実行が成功したかどうかに関するメッセージのみを受け取ることができます。クエリ結果を表示するには、Spark JAR開発ページの [アプリケーション] タブに移動し、[操作] 列の [ログ] をクリックします。詳細については、Spark開発エディターのトピックの「Sparkアプリケーションに関する情報の表示」をご参照ください。
- 例1: 2018年6月3日から2018年6月4日までのSXS_0010機関の最初の100トランザクションレコードを照会します。
```
SELECT * からadb_test.tradelist_csv
WHERE t_cdate >= '2018-06-03 'およびt_cdate <= '2018-06-04' およびt_agencyno = 'SXS_0010'
LIMIT 100; 
```
- 例2: 異なる都市で男性と女性が購入した資金の合計金額を照会します。この例では、複数のテーブルが結合されています。
```
SELECT u_city, u_gender, SUM(t_confirmbalance) AS sum_balance
adb_test.tradelist_csvから、adb_test.userinfo
WHERE u_userid = t_userid
グループBY u_city, u_gender
ORDER BY sum_balance DESC; 
```

Object Storage Service:XIHE SQLまたはSpark SQLを使用したOSSデータへのアクセス

前提条件

手順

XIHE SQLを使用したOSSデータへのアクセス

トランザクション情報テーブルの作成

ユーザー情報テーブルの作成

同期クエリ

非同期クエリAsynchronous query

Spark SQLを使用したOSSデータへのアクセス

トランザクション情報テーブルの作成

ユーザー情報テーブルの作成

関連ドキュメント