VantageでAzure Blob Storageに接続してJSONファイルのデータを検索してみる

多くのTeradataのお客様は、Teradata VantageとMicrosoft Azure First Party Servicesの統合に関心をお持ちです。この記事ではVantageのNative Object Store機能を使用してTeradata VantageをAzure Blob Storageと接続する方法を説明します。

このアプローチは社内で実装されテストされていますが、そのままの状態で提供されます。Microsoftは、Native Object Store機能を使用したTeradata VantageとAzureサービスとの検証を提供していません。

ご意見・ご感想はコメントとしてお寄せください。

免責事項：このガイドには、MicrosoftとTeradataの両方の製品ドキュメントの内容が含まれています。

# 概要

ここではTeradata VantageのNative Object Store（NOS）機能を使用して、膨大な量の半構造化または非構造化データを保管できるMicrosoft Azure Blob Storage上のJSONファイルにクエリを実行します。

JSONデータを作成および処理する方法は数多くあります。
この記事では
・Raspberry Piオンラインシミュレータを使用してJSON文字列をAzure IoTハブデバイスに送信
・メッセージをAzure Eventハブにルーティング
・Azure Stream Analyticジョブでデータ処理
・JSONファイルとしてAzure Blob Storageコンテナに送付
します。

最後にVantageのNOS機能を使用してJSONファイルに接続しクエリを実行します。
これは、ワークフローの図です。

![画像1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2742714/bdaf328c-aee3-13ca-b8d8-dc75781d1617.png)

# 前提条件

AzureサービスおよびTeradata Vantage with Native Object Store（NOS）に精通していることが前提になります。

以下のアカウント、オブジェクト、システムが必要です。セットアップ手順のリンクが含まれています。

- Microsoft Azureのサブスクリプションです。[無料アカウント](https://azure.microsoft.com/free/)で開始することができます。
- [Azure Blob Storage](https://docs.microsoft.com/en-us/azure/storage/common/storage-quickstart-create-account?tabs=azure-portal) アカウントとコンテナ。
- [Azure Event Hub](Azure Event Hub) ネームスペースとイベントハブ。
- [Azure Stream Analytic](https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-quick-create-portal)ジョブ。
- [Azure IoT Hub](https://docs.microsoft.com/en-us/azure/iot-hub/iot-hub-create-through-portal)instance、デバイスの登録、ルートの定義、およびカスタムエンドポイント。
- IoTハブデバイスのための[Raspberry Piオンラインシミュレータ](https://docs.microsoft.com/en-us/azure/iot-hub/iot-hub-raspberry-pi-web-simulator-get-started)。
- [Teradata VantageとNOS](https://www.teradata.com/Blogs/The-Road-Ahead-Integrating-Amazon-S3-and-Azure-Blob-into-Teradata-Vantage)機能

# Azure Blob Storageのアカウントとコンテナを作成する

Azure Blob Storage アカウントとコンテナの作成JSON ファイルを保存するために、Azure Blob Storage アカウントが必要です。

1. [Azureポータル](https://portal.azure.com/)にログオンし、[ストレージアカウントを作成する](https://docs.microsoft.com/en-us/azure/storage/common/storage-account-create?tabs=azure-portal)。

「Subscription」、「Resource group」、「Storage account name」、「Location」を入力します。残りのフィールドは、デフォルト値のままにしておきます。「Review+create」をクリックして確認し「Create」をクリックします。
作成するすべてのサービスに同じ場所を使用することをお勧めします。

2. 配置が完了したら、「Go to resource」をクリックしJSONファイル用のコンテナを作成します。

3. コンテナアイコンをクリックし「+Container」をクリックして新しいコンテナ名を指定します。この例では、Public access levelを「Private」のままにしておきます。

![画像2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2742714/9ac4ab77-c366-8d1d-a999-690a08d6cb9e.png)

4. アカウントを完全に制御するにはAccess keysをクリックし「Storage account name」と「key1」をコピーします。これは、後で「Connect Teradata Vantage Native Object Store to Azure Blob Storage - Create Authorization Object」のセクションで使用します。

![画像3.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2742714/0100c6c5-c417-94c2-744a-60ab1c291351.png)

より細かい認証を行うにはShared access signatureをクリックして、コンテナまたはコンテナ内のオブジェクトへのアクセスを定義します。適切なSASアクセスプロパティを選択し、「Generate SAS and connection string」ボックスを選択します。

![画像4.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2742714/e995c6a7-dfce-095e-4d99-9eae66bf957b.png)

SASトークン出力とストレージアカウント名を選択し後で「Connect Teradata Vantage Native Object Store to Azure Blob Storage - Create Authorization Object」セクションで使用するドキュメントにコピーしてください。

![画像5.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2742714/06ca4e8d-5307-d05a-775f-8b930b5b1fcd.png)

# Azure Event Hubのネームスペースとハブの作成

IoT Hubデバイスがメッセージを送信するためにEvent HubネームスペースとEvent Hubエンドポイントを作成する必要があります。

1. ポータルを使用して、イベントハブのネームスペースとイベントハブを作成します。Namespace名を指定しStandard tierを選択しResource Groupを新規作成または既存のものを使用しLocationを指定します。残りのフィールドはデフォルト値のままにしておきます。「Create」をクリックします。システムがリソースを完全にプロビジョニングするまで、数分待つ必要があります。

リソースグループを開くと、イベントハブのネームスペースが表示されます。

![画像6.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2742714/72fb40f3-ab25-e954-f448-01a683b7f1d9.png)

本ガイドではThroughput UnitsおよびEnable Auto-Inflateプロパティは扱いません。詳細についてはAzureのドキュメントを参照してください。

次にEvent Hubネームスペースを選択し「+Event Hub」をクリックしてイベントハブを作成します。「Event Hub Name」を入力します。残りのフィールドはデフォルト値のまま「Create」をクリックします。

![画像7.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2742714/27a12dea-0b4f-fe5d-ec81-83bd1bef8086.png)

Event Hubのキャプチャ機能ではAzure Blob StorageへのAvro形式のみサポートしています。このガイドではStream Analyticsを使用してJSON フォーマットに対応するようにデータを移動します。

イベントハブが作成されると、以下のようにイベントハブのリストに表示されるようになります。

![画像8.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2742714/c2f3712a-b7ea-7529-90c8-742bc99485be.png)

# Azure Stream Analyticジョブの作成 

イベントハブのメッセージをAzure Blob Storageに移動させたいので[イベントハブからのデータをStream Analyticsで処理することにします](https://docs.microsoft.com/en-us/azure/event-hubs/process-data-azure-stream-analytics)。

1．作成したイベントハブ（例：eventhub1）をクリックし「Process data」をクリックします。

![画像9.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2742714/b1ff2e6a-69b6-795c-d320-cc3506843b9e.png)

2．「Explore」をクリックするとQueryダイアログが表示されます。イベントハブにメッセージがある場合は「Create」をクリックするとメッセージを処理することができます。

3. 「Deploy query」をクリックしてEvent HubのメッセージをAzure Blob Storageに移動するStream Analyticジョブを作成します。

![画像10.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2742714/62defd11-b944-ea22-dfe7-6b6dfadc9135.png)

4．New Stream Analytic jobペインに「Job Name」を入力し、残りのフィールドはデフォルト値のままにしておきます。

5．「Create」をクリックします。

6．Output UIをクリックし、「+Add」をクリックしてBlob Storageを出力として定義します。

![画像11.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2742714/0101f692-1ed5-39c7-a24c-8f2db9099bb7.png)

7．Blob Storage/Data Lake Storage Gen2ペインに、「Output alias」、「Storage account」、「Container」を入力します。Pathパターン（例えば、<ディレクトリ>/{日付}/{時間}）はオプションです。

8．Event serialization formatがJSONに設定されていることを確認します。残りのフィールドはデフォルト値のまま、「Save」をクリックします。

![画像12.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2742714/5e73110e-9403-bdc2-6a1e-98b42f67d821.png)

Pathパターンのプロパティの詳細については[Azure Stream Analytic custom blob output partitioning](https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-custom-path-patterns-blob-storage-output)を参照してください。

8．右上隅をクリックしてOutputペインを終了します。

9．クエリを編集し新しい出力エイリアス（例：output1）を付けて保存します。

10．右上の角をクリックしてQueryペインを終了します。

11．ジョブの「Start」をクリックします。

![画像13.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2742714/7163f6d0-98ee-cf22-88d9-c78d4db4dff2.png)

# メッセージをルーティングするためのAzure IoT Hubの作成

IoT Hubインスタンスを作成しIoT Hubデバイスを登録しRaspberry PiオンラインシミュレータからAzure IoT Hubに、そして最後にEvent Hubにメッセージを取得するためのルート/カスタムエンドポイントを設定する必要があります。

1．ポータルで[IoT Hub を作成し、新しいデバイスを登録し、メッセージルーティングを定義します](https://docs.microsoft.com/en-us/azure/iot-hub/iot-hub-create-through-portal)。

2．左上の「Create a resource」をクリックし、IoT Hubを検索します。「Create」をクリックします。

3．「Basic」タブで新しいリソースグループを作成するか、既存のリソースグループを使用し、Iot Hub Nameを指定します。「Size」タブで「B1：Basic Layer」を選択します。残りのフィールドはデフォルト値のままにし、「Review+create」をクリックして検証します。「Create」をクリックします。

詳細については、[適切なIoT Hubの階層を選択する](https://docs.microsoft.com/en-us/azure/iot-hub/iot-hub-scaling)を参照してください。Number of IoT Hub unitsプロパティの設定やパフォーマンスに関するより一般的な話はここでは取り上げません。

オンラインシミュレータではハブに接続するためにレジストリでデバイスを識別する必要があります。

4．「Go to resource」をクリックするかIoT Hubペインで、「IoT Devices」を開き、「+New」を選択してIoTハブのデバイスを追加します。「Device ID」の名前を入力し「Save」をクリックします。

![画像14.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2742714/0350dd34-babf-ef1c-ce09-c3866013a5ef.png)

5．IoT デバイスダイアログの「Refresh」をクリックしてデバイスを表示し「Device」をクリックします。後でRaspberry PiオンラインシミュレータとIoT Hubデバイス (例: rasppi) を接続するために使用するPrimary Connection Stringをコピーしてください。

![画像15.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2742714/66d7168f-7bcf-180a-8ec6-b90f81654647.png)

IoT HubデバイスがEvent Hubにメッセージを送信するためのルートとエンドポイントを設定する必要があります。

6．IoT Hubペインで、Messagingの「Message routing」をクリックして、[ルートとカスタムエンドポイントを定義します](https://docs.microsoft.com/en-us/azure/iot-hub/iot-hub-devguide-messages-d2c)。

![画像16.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2742714/ad14b458-7f31-8f8f-fc8a-d2ed18d1fc75.png)

RoutesはMessage Routingペインの最初のタブです。

7．「+Add」をクリックして新しいルートを追加します。次の画面が表示されます。Route Nameを入力しEndpoint選択します。エンドポイントについてはドロップダウンリストから選択するか、新しいものを追加します。この例では「+Add endpoint」をクリックしEvent Hubを選択します。

![画像17.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2742714/dcd90341-1cd9-37a3-3d49-f9e85b50da9e.png)

8．Add an event hub endpointペインでEndpoint name、既存のEvent hub namespace、およびEvent hub instanceを指定します。「Create」をクリックします。

![画像18.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2742714/e1e8d952-8acc-31f4-a050-e3ef66696567.png)

9．「Save」をクリックしてルーティングルールを保存します。新しいルーティングルールが表示されるはずです。

![画像19.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2742714/4fd6a1ba-8a0b-c09e-6d60-b7abedbd1bc7.png)

10．Custom Endpointタブで、「Update」をクリックすると、イベントハブの下にカスタムエンドポイントルールが表示されます。StatusがHealthyであることを確認します。

![画像20.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2742714/f34e4084-29e6-c8d9-b5de-4dc6d304e86f.png)

# Raspberry Piオンラインシミュレータを新しいデバイスに設定する

JSON文字列のソースとして[Raspberry Piオンラインシミュレータ](https://docs.microsoft.com/en-us/azure/iot-hub/iot-hub-raspberry-pi-web-simulator-get-started)を使用します。データは、前項で作成したIoTハブ登録デバイスに送信されます。

これはシミュレーターが生成するJSON文字列の例です。(わかりやすくするために改行しています。）

```JSON:Output.json
{"messageId":2,
"deviceId":"Raspberry Pi Web Client",
"temperature":29.288325949023434,
"humidity":77.5147906}
```

オンラインシミュレータが接続するためにIoTハブデバイスのPrimary Connection Stringを追加する必要があります。

1．「START RASPBERRY PI SIMULATOR」をクリックします。

2．15行目を編集し'[Your IoT hub device connection string]'; を 「Primary Connection String」に置き換えます。

![画像21.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2742714/97034e75-3bf4-95f4-e703-bd40bf7e443d.png)

3．オンラインシミュレーターを実行します。BLOBストレージコンテナにJSONファイルが表示されるはずです。

4．「Stop」をクリックしてシミュレーターを停止します。

必要に応じてBLOBストレージデータを表示することができます。

A. [Azureポータル](https://portal.azure.com/)にログインし「Resource groups」をクリックします。

B. ストレージアカウントを持つリソースグループを検索してクリックします。

C. 「Container Name」をクリックします。

![画像22.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2742714/82262950-acad-af56-b3f6-f39daeb45f44.png)

D. JSONファイルをクリックし「Edit」でデータを表示します。

![画像23.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2742714/cf108619-b236-95a8-d702-61a1f349f830.png)

または、[Azure Storage Explorer](https://azure.microsoft.com/en-us/features/storage-explorer/) を使用することもできますが、Azure Storage Explorer の構成については本記事では説明しません。

# Teradata Vantage Native Object StoreをAzure Blob Storageに接続

Native Object StoreはTeradata Vantage 17.0に搭載された新機能でAzure Blob Storageなどの外部オブジェクトストア上にあるデータセットを標準SQLで簡単に探索できるようになります。

設定するとAzure Blob上のオブジェクトはVantage上のテーブルと同じように表示されます。

詳細な情報は**Native Object Store - Teradata Vantage Advance SQL Engine 17.0 (Orange Book)** に記載されています。

Vantag はAzure Blob Storageアカウントにアクセスするために認可オブジェクトを必要とします。認可オブジェクトはAzure Blob Storageの認可に依存するだけでなくより高いセキュリティを提供します。

CREATE AUTHORIZATION DDL文ではUSERフィールドにストレージアカウント名を指定し、PASSWORDフィールドに先ほど保存したAzureアクセスキーまたはSAS Tokenを指定する必要があります。

認可オブジェクトはそれを参照する外部テーブルと同じデータベースで作成する必要があります。

1．以下のような構文を使用して、認可オブジェクトを作成します。

```sql:create_authorization.sql
CREATE AUTHORIZATION DefAuth_AZ
AS DEFINER TRUSTED
USER 'mystorageacctrs' /* storage account name */
PASSWORD '********';	/* storage account key or SAS Token */
```

同じ名前のストレージアカウントを削除して再作成するとアクセスキーが変更されます。そのため認証オブジェクトを削除して再作成する必要があります。

外部テーブルを使用するとVantage Advanced SQLエンジン内で外部データを簡単に参照することができ構造化されたリレーショナル形式でデータを利用できるようになります。以下は単純な外部テーブルを作成するための構文の例です。

ストレージの LOCATION 情報には、AZ プレフィックス、「blob.core.windows.net」サフィックスを含むストレージアカウント名、コンテナ名の3つの部分がありスラッシュで区切られています。

外部セキュリティ認証を使用する場合は、DDL文のEXTERNAL SECURITY DEFINER TRUSTED DefAuth_AZ構文で指定します。

2．以下のような構文で外部テーブルを作成する。

```sql:create_foreigntabl.sql
CREATE MULTISET FOREIGN TABLE json ,
EXTERNAL SECURITY DEFINER TRUSTED DefAuth_AZ
USING
(
      LOCATION  ('/AZ/mystorageacctrs.blob.core.windows.net/json')
) 
```

CREATE FOREIGN TABLEステートメントを実行した後show table jsonコマンドで完全な外部テーブル定義を表示することができます。以下はその例です。

```sql:result_showtbljson.sql
CREATE MULTISET FOREIGN TABLE json ,FALLBACK ,EXTERNAL SECURITY DEFINER TRUSTED DefAuth_AZ,
     MAP = TD_MAP1
     (
      Location VARCHAR(2048) CHARACTER SET UNICODE CASESPECIFIC,
      Payload JSON(8388096) INLINE LENGTH 32000 CHARACTER SET UNICODE)
USING
(
      LOCATION  ('/AZ/mystorageacctrs.blob.core.windows.net/json')
      MANIFEST  ('FALSE')
      PATHPATTERN  ('$Var1/$Var2/$Var3/$Var4/$Var5/$Var6/$Var7/$Var8/$Var9/$Var10/$Var11/$Var12/$Var13/$Var14/$Var15/$Var16/$Var17/$Var18/$Var19/$Var20')
      ROWFORMAT  ('{"record_delimiter":"\n", "character_set":"UTF8"}')
      STOREDAS  ('TEXTFILE')
)
NO PRIMARY INDEX ;
```

外部テーブルが作成されたのでAzure Blob Storage上のオブジェクトにアクセスすることができます。

JSONは基本的にキーと値のリストです。JSON_KEYSテーブル演算子でキー（属性）のリストを取得するには、次のように記述します。

```sql:select_jsonkey.sql
SELECT DISTINCT * FROM JSON_KEYS (ON (SELECT payload FROM json)) AS j
```

![画像24.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2742714/17012300-b5bb-d52d-1a6c-84220baa3588.png)

フィルタリングを行わず少数の行を選択することで実際の値のリストを得ることができます。

```sql:select_json.sql
SELECT Payload.* FROM json
```

![画像25.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2742714/34bef390-f9f4-7c42-c7ed-4b38f33f4991.png)

Payloadキーワードからアスタリスクを省略するとフィールドと値を含む名前-値ペアを取得できます。

```sql:select_payloadfromjson.sql
SELECT Payload FROM json
```
![画像26.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2742714/e131aea1-fc48-a93e-56b8-a441de484d4a.png)

テーブルの上にビューを作成しユーザーやツールと基礎となるテーブルの間のレイヤーを提供することはよくある手法です。
以下は、JSON外部テーブル上のビューの例です。

```sql:create_view.sql
CREATE VIEW json_perm_cols_v AS ( 
SELECT 
CAST(payload.messageId AS INT) MessageID,
     	CAST(payload.deviceId AS VARCHAR(25)) DeviceID,
	  	 	CAST(payload.temperature AS FLOAT) Temperature,
	   	CAST(payload.humidity AS FLOAT) Humidity
       FROM json
       );
```

この時点で、ビューはユーザーやツールによって使用される可能性があります。

Native Object Storeは外部テーブルを通じて読み取った外部オブジェクトの永続的なコピーを自動的には作成しません。
外部オブジェクトのデータをVantageのテーブルにコピーすることができます。以下はいくつかの簡単なCOPYオプションです。

CREATE TABLE AS...WITH DATAステートメントはデフォルトでターゲットテーブルとしてNo Primary Index (NoPI)リレーショナルテーブルを作成します。それ以上の操作を行わない場合このNoPIテーブルは2つの列だけを持つことになります。このNoPIテーブルにはLocationとPayloadの2つのカラムがあるだけです。

```sql:create_table.sql
CREATE TABLE json_perm AS (select * from json) WITH DATA
```

各ペイロードごとにカラムを作成するのがよいでしょう。以下の例ではこれを実現しています。

```sql:create_tablewithcolumn.sql
CREATE TABLE json_perm_cols AS 
(SELECT 
       CAST(payload.messageId AS INT) MessageID,
       CAST(payload.deviceId AS VARCHAR(25)) DeviceID,
       CAST(payload.temperature AS FLOAT) Temperature,
       CAST(payload.humidity AS FLOAT) Humidity
       FROM json
       )
WITH DATA
NO PRIMARY INDEX
```

もう一つの方法はINSERT...SELECTステートメントを使用することです。この方法では永続的なテーブルが事前に作成されている必要があります。以下の例ではまずテーブルを作成しそれから挿入を実行します。

```sql:createandinsertselect.sql
CREATE SET TABLE json_perm_empty ,FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO,
     MAP = TD_MAP1
     (
      MessageId INTEGER,
      DeviceId VARCHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC,
      Temperature FLOAT,
      Humidity FLOAT)
PRIMARY INDEX ( MessageId );

INSERT into.json_perm_empty 
SELECT
	payload.messageId,
	payload.deviceId,
	payload.temperature,
	payload.humidity 
FROM json
```

# おわりに
Azure BLOBストレージへのNOSアクセスはいかがでしたでしょうか？
ぜひお試しください！

#### Teradata Vantageへのお問合せ
[Teradata Vantage へのお問合せ](https://www.teradata.jp/About-Us/Contact)