Data Lake Analyticsを利用したTable StoreおよびObject Storage Serviceのデータ参照 [ DLA + OTS + OSS ]

f:id:sbc_suzuki10:20200722145331p:plain

こんにちは。エンジニアの鈴木です。

今回はData Lake Analyticsを使用してTable StoreとObject Storage Serviceのデータに対してクエリを実行し、
異なるデータソース間のデータ参照をご紹介したいと思います。

例えば、Table Storeでは単一のテーブルデータの検索が可能ですが、複数テーブルを跨いだ検索ができません。
また、ApsaraDB for RDSやObject Storage Serviceなどの異なるプロダクト間でデータの関連性を調べようとすると、
データ形式が異なるため容易ではありません。

しかし、Data Lake Analyticsを使用することで上記が簡単に実現可能となります。

本ブログでは、実際にData Lake Analyticsを使用し、Table Storeの複数テーブルを跨いだデータ検索や異なるデータソース間の関連データ参照をご紹介します。




はじめに

Data Lake Analyticsについて

Data Lake Analytics(DLA)は、サーバーレスの対話型クエリおよび分析サービスです。

■以下の特徴を持っています。

  • ・サーバーレスアーキテクチャ
    • メンテナンスを不要とし、弾力的なスケーリングと透過的なアップグレードをサポート
  • ・標準SQLインターフェースを提供
    • 標準的なJDBC(Java Database Connectivity)とODBC(Open Database Connectivity)の技術を採用
  • ・複数のデータソースにまたがる関連性を分析
    • Object Storage Service、TableStore、AnalyticDB、RDS(MySQL、PostgresSQL、SQL Server用)、Redis、MongoDB、PolarDBなどの複数のデータソースにまたがる関連性分析をサポート
  • ・高性能な分析エンジン
    • 新世代解析エンジンXIHEを活用し、MPP(Massive Parallel Processing)とDAG(Directed Acyclic Graph)技術を適用して高圧縮率、高スケーラビリティ、高可用性を実現
  • ・BIツール連携
    • Quick BIやDataVなどのBIツールとの連携をサポート
  • ・コストの柔軟性
    • 利用料金はスキャンするデータ量に基づいて課金



本ブログでは以下の機能を実際に使用してご紹介します。
・標準SQLインターフェースを提供
・複数のデータソース(本ブログでは例としてObject Storage ServiceとTable Storeを使用)にまたがる関連性を分析

以下の工程でご紹介していきます。
・Table Storeにテーブルを作成
・Object Storage ServiceにCSVをアップロード
・Data Management ServiceでTable Storeテーブルのスキーマを作成
・Data Management ServiceでTable Storeテーブルの外部テーブルを作成
・Data Lake Analyticsでテーブル結合を確認(Table Storeの異なるテーブルを結合)
・Data Management ServiceでCSVのスキーマを作成
・Data Management ServiceでCSVの外部テーブルを作成
・Data Lake Analyticsでテーブル結合を確認(Table StoreのテーブルおよびObject Storage ServiceのCSVを結合)
・Data Lake Analyticsでクエリ結果をエクスポート

f:id:sbc_suzuki10:20200623175024p:plain

環境

Table Store

f:id:sbc_suzuki10:20200622141255p:plain

Table Storeにテーブルを作成

まずはData Lake Analyticsで参照するデータをTable Storeで用意します。

www.alibabacloud.com

参照用テーブル001の作成

テーブル定義の作成(参照用テーブル001)

以下のテーブル定義を作成します。

f:id:sbc_suzuki10:20200625154005p:plain



テーブルへのデータ挿入(参照用テーブル001)

以下のテストデータをテーブルに挿入します。

timeStamp messageId deviceName rawMessage
2020-06-16 12:00:00 0000000000000000001 dev_001 dev_001@test@001
2020-06-16 12:01:00 0000000000000000002 dev_002 dev_002@test@001
2020-06-16 12:02:00 0000000000000000003 dev_003 dev_003@test@001


f:id:sbc_suzuki10:20200622155101p:plain



参照用テーブル002の作成

テーブル定義の作成(参照用テーブル002)

以下のテーブル定義を作成します。

f:id:sbc_suzuki10:20200625153941p:plain



テーブルへのデータ挿入(参照用テーブル002)

以下のテストデータをテーブルに挿入します。

timeStamp messageId deviceName state message
2020-06-16 12:00:00 0000000000000000001 dev_001 info システムが再起動しました。
2020-06-16 12:01:00 0000000000000000002 dev_002 warn xxxへの接続がタイムアウトしました。
2020-06-16 12:02:00 0000000000000000003 dev_003 error システムが異常終了しました。


f:id:sbc_suzuki10:20200622155135p:plain





Object Storage Service(OSS)

f:id:sbc_suzuki10:20200622141314p:plain

バケットとディレクトリの作成

Data Lake Analyticsで参照するデータをObject Storage Serviceで用意します。
Data Lake Analyticsで参照可能なファイルはCSV、JSON、TSV、Parquet、ORC、RCFile、Avroなど多岐に渡ります。

www.alibabacloud.com


その中で今回はCSVを使用します。

参照するCSVを格納するディレクトリとそのディレクトリを内包するバケットを作成します。
今回は以下の階層としました。

OSSプロダクト
 └バケット名
  └ ディレクトリ名
   └ CSVファイル


f:id:sbc_suzuki10:20200622172830p:plain


CSVファイルのアップロード

以下のテストデータが含まれたCSVをOSSにアップロードします。

deviceName,user,address
dev_001,斎藤,saito@test.com
dev_002,高橋,takahashi@test.com
dev_003,石本,ishimoto@test.com


f:id:sbc_suzuki10:20200622132649p:plain





Data Lake Analytics(DLA)

f:id:sbc_suzuki10:20200622141143p:plain

エンドポイントの作成

Data Lake Analyticsへのエンドポイントを作成します。
これは後のTable StoreやObject Storage Serviceとの連携の際に必要となる作業です。
Data Management ServiceからData Lake Analyticsに接続できるようにします。

以下画像の「Create Endpoint」ボタンを押下し、エンドポイントを設定します。

f:id:sbc_suzuki10:20200622181832p:plain


VPC、VSwitch、Available Zoneを設定します。

f:id:sbc_suzuki10:20200622181816p:plain


エンドポイントが作成されました。
これでData Management ServiceからData Lake Analyticsにログイン可能となります。

f:id:sbc_suzuki10:20200623132218p:plain





プロダクト連携

Data Management Serviceへのログイン

www.alibabacloud.com


先程、作成したエンドポイントから「Log on in DMS」を押下し、Data Management Serviceコンソールに遷移します。

f:id:sbc_suzuki10:20200623132151p:plain


Data Lake Analyticsエンドポイントと、
Data Lake Analyticsのアカウント管理画面のRoot AccountでData Management Serviceにログインします。

f:id:sbc_suzuki10:20200623130305p:plain




Table Store + Data Lake Analytics 連携

f:id:sbc_suzuki10:20200623171509p:plain

www.alibabacloud.com

Table Storeスキーマの作成

Table StoreのテーブルをData Lake Analyticsに連携させるため、
Data Management Serviceでテーブルのスキーマを作成します。

www.alibabacloud.com

記述例

以下は記述例となります。

CREATE SCHEMA <スキーマ名>  WITH DBPROPERTIES (
catalog = 'ots',
location = '<Table StoreインスタンスのVPCエンドポイント>',
instance = '<Table Storeインスタンス名>'
);


Data Management Service

実際に今回入力したスキーマは以下になります。

CREATE SCHEMA ots_dla_test  WITH DBPROPERTIES (
catalog = 'ots',
location = 'https://suzuki10-test.cn-shanghai.vpc.tablestore.aliyuncs.com',
instance = 'suzuki10-test'
);
f:id:sbc_suzuki10:20200623142626p:plain


Data Lake Analytics

スキーマ作成に成功するとData Lake Analyticsコンソールでスキーマが作成されます。

f:id:sbc_suzuki10:20200623132903p:plain



外部テーブルの作成

引き続きData Management Serviceで作業をします。
スキーマの次は外部テーブルを作成します。

www.alibabacloud.com

記述例

以下は記述例になります。
Table Storeで作成したテーブル定義により、以下の記述は変化しますので、Table Storeのテーブルに合わせ調整します。
※フィールド名に「-」や「_」が含まれている場合は、「`」でフィールド名を囲む必要があります。
 例:device_name varchar not NULL ⇒ `device_name` varchar not NULL

CREATE EXTERNAL TABLE <スキーマ名>.<テーブル名> (
  `<フィールド名>` <データ型> <not NULL または NULL> ,
  `<フィールド名>` <データ型> <not NULL または NULL> ,
  PRIMARY KEY (`<主キーのフィールド名>`, `<主キーのフィールド名>`)
);


外部テーブルの作成(参照用テーブル001)

今回作成した外部テーブルは以下になります。

CREATE EXTERNAL TABLE ots_dla_test.tbl_001 (
  timeStamp varchar not NULL ,
  messageId  varchar not NULL ,
  deviceName  varchar not NULL ,
  rawMessage varchar NULL ,
  PRIMARY KEY (`timeStamp`, `messageId`, `deviceName`)
);
f:id:sbc_suzuki10:20200623142915p:plain


外部テーブルの作成(参照用テーブル002)

今回作成した外部テーブルは以下になります。

CREATE EXTERNAL TABLE ots_dla_test.tbl_002 (
  timeStamp varchar not NULL ,
  messageId  varchar not NULL ,
  deviceName  varchar not NULL ,
  state varchar NULL ,
  message varchar NULL ,
  PRIMARY KEY (`timeStamp`, `messageId`, `deviceName`)
);
f:id:sbc_suzuki10:20200619120247p:plain




テーブル結合

Data Management Serviceでスキーマと外部テーブルを作成すると、
Data Lake Analyticsコンソールでクエリの実行が可能となります。

では、実際にData Lake Analyticsコンソールで今回作成した2つのテーブルに対し、クエリを発行してみます。
テーブル結合イメージは以下になります。

f:id:sbc_suzuki10:20200617193752p:plain



以下のSELECT文をData Lake Analyticsコンソールの「Serverless SQL > Execute」画面で入力し、実行します。

SELECT rawMessage, state, message FROM ots_dla_test.tbl_001
    INNER JOIN ots_dla_test.tbl_002
    ON ots_dla_test.tbl_001.messageId = ots_dla_test.tbl_002.messageId;



結果は以下になります。
本ブログで作成したTable Storeの2つのテーブルが結合され結果が表示されました。

f:id:sbc_suzuki10:20200623142322p:plain




Table Store + Object Storage Service + Data Lake Analytics 連携

f:id:sbc_suzuki10:20200623171549p:plain

www.alibabacloud.com

Object Storage Serviceスキーマの作成

Object Storage ServiceのCSVをData Lake Analyticsに連携させるため、
CSVのスキーマを作成します

www.alibabacloud.com

記述例

以下は記述例になります。

CREATE SCHEMA oss_dla_test with DBPROPERTIES(
  catalog='oss',
  location = 'oss://<バケット名>/<あればディレクトリ名>/'
  );


Data Management Service

実際に今回入力したスキーマは以下になります。

CREATE SCHEMA oss_dla_test with DBPROPERTIES(
  catalog='oss',
  location = 'oss://oss-suzuki10n01/dla/'
  );
f:id:sbc_suzuki10:20200623141200p:plain


Data Lake Analytics

スキーマ作成に成功するとData Lake Analyticsコンソールでスキーマが作成されます。

f:id:sbc_suzuki10:20200623143118p:plain



外部テーブルの作成

引き続きスキーマの次はTable Store同様、Data Management Serviceで外部テーブルを作成します。

www.alibabacloud.com

記述例

以下は記述例になります。
今回参照するのはObject Storage ServiceにアップロードしたCSVになりますので、
CSVに合わせた外部テーブルを作成します。

CREATE EXTERNAL TABLE <スキーマ名>.<テーブル名>(
<フィールド名> <データ型>,
<フィールド名> <データ型>
)
ROW FORMAT
SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
WITH SERDEPROPERTIES('serialization.encoding'='<文字コード>', 'field.delim'='<データの区切り文字>')
STORED AS TEXTFILE
LOCATION 'oss://<バケット名>/<ディレクトリ名>/<ファイル名>'
TBLPROPERTIES ('skip.header.line.count' = '<読み込み対象外の行数>')

CSV - Serverless SQL| Alibaba Cloud Documentation Center


外部テーブルの作成(CSV)

実際に作成したテーブルは以下になります。
OSSにアップロードしたCSVに合わせ以下を調整しています。
・CSVの区切り文字にカンマを使用しているため、'field.delim'=','を指定
・CSVに日本語が含まれるため、'serialization.encoding'='SJIS'を指定
・CSVにフィールド名を記入しているので、'skip.header.line.count' = '1'を指定

CREATE EXTERNAL TABLE oss_dla_test.oss_testdata_csv(
deviceName STRING,
user STRING,
address STRING
)
ROW FORMAT
SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
WITH SERDEPROPERTIES('serialization.encoding'='SJIS', 'field.delim'=',')
STORED AS TEXTFILE
LOCATION 'oss://oss-suzuki10n01/dla/oss_testdata001.csv'
TBLPROPERTIES ('skip.header.line.count' = '1')
f:id:sbc_suzuki10:20200619141042p:plain




テーブル結合

Data Management ServiceでObject Storage ServiceのCSVスキーマと外部テーブルを作成すると、
Object Storage Serviceに関してもData Lake Analyticsコンソールでクエリの実行が可能となります。

実際にData Lake Analyticsコンソールで以下の3つのテーブルに対し、クエリを実行してみます。
・Table Store 参照用テーブル001
・Table Store 参照用テーブル002
・Object Storage Service CSVファイル

テーブル結合イメージは以下になります。

f:id:sbc_suzuki10:20200617193819p:plain

以下のSELECT文をData Lake Analyticsコンソールの「Serverless SQL > Execute」画面で入力し、実行します。

SELECT rawMessage, state, message, address FROM ots_dla_test.tbl_001
INNER JOIN ots_dla_test.tbl_002
ON ots_dla_test.tbl_001.messageId = ots_dla_test.tbl_002.messageId
INNER JOIN oss_dla_test.oss_testdata_csv
ON ots_dla_test.tbl_001.devicename = oss_testdata_csv.devicename


結果は以下になります。
本ブログで作成したTable Storeの2つのテーブルとObject Storage ServiceのCSVが結合され異なるデータソースからなる結果が表示されました。

f:id:sbc_suzuki10:20200623120405p:plain



データエクスポート

Data Lake Analyticsはクエリで結合したテーブルをCSV形式でエクスポートが可能です。
Execute画面の「エクスポート結果セット(Export Result Set)」ボタンを押下することでCSVファイルをダウンロード可能です。

f:id:sbc_suzuki10:20200623120511p:plain



ダウンロードされたCSVファイルはExecute画面でのクエリ結果が反映されています。

"rawMessage","state","message","address"
"dev_001@test@001","info","システムが再起動しました。","saito@test.com"
"dev_003@test@001","error","システムが異常終了しました。","ishimoto@test.comm"
"dev_002@test@001","warn","xxxへの接続がタイムアウトしました。","takahashi@test.com"



まとめ

今回はData Lake Analyticsを使用したクエリ実行についてご紹介しました。

Data Lake Analyticsと各プロダクトを連携することでTable Storeのテーブルだけではなく、
Object Storage ServiceのCSVもまとめてデータ分析することができました。
Data Lake Analyticsうまく活用することで異なるデータソースの関連データの解析が容易になると思われます。

今回はTable StoreとObject Storage Serviceとの連携について記載しましたが、
他にもRDSとの連携や、OSSにアップロードしたログの分析などにもData Lake Analyticsは活用できます。

BIサービスと連携することでさらに視覚的に統計データを調べることができますので、ぜひData Lake Analyticsをご活用ください!