「S3 + Athena」 で実行していたことを 「BLOB + Azure Data Explorer」 で実現してみました ー ３


# STEP-3 : Azure Data Explorer の Python ライブラリを使用してデータベースへのクエリを実行する

## 概要
AWSの「S3 + Athena」 で実行できること、つまり、S3に保存されているJSON形式等のデータ（以下、JSONデータ）を Athenaで提供されているSQLライクな構文を用いてクエリ実行することによりデータ抽出が可能となっています。同様なことをAzure上の「BLOB + Azure Data Explorer」構成においても可能であることを確認しました。なお、「Web UI」と「Pythonプログラム」の両方で実現しております。

この実現を以下の３つのステップで順次説明します。
STEP-1．Azure Data Explorer クラスターとデータベースを作成する
STEP-2．Azure Data Explorer の Web UI でデータベースへのクエリを実行する
STEP-3．Azure Data Explorer の Python ライブラリを使用してデータベースへのクエリを実行する

[STEP-1についてはこちら](https://qiita.com/turupon/items/ce4be135cb5d1f7f86b4)、[STEP-2についてはこちら](https://qiita.com/turupon/items/b2886387832d4826afbf) を参照ください。 今回は STEP-3 について説明します。

Azure Data Explorer では、Python 用のデータ クライアント ライブラリが提供されています。 このライブラリを使用すると、コードからデータをクエリできます。 そのクラスターでテーブルのクエリを実行し、結果を返すことができます。


## ローカル環境
macOS Big Sur 11.1 
python 3.8.3
Microsoft Edge 89.0.774.63（Safariでは Azure Data Explorer がうまく動きませんでした）

## ローカル上での事前準備
データライブラリをインストールしておきます。

```
pip install azure-kusto-data
```

## Azure上での事前準備
[STEP-1を完了していること](https://qiita.com/turupon/items/ce4be135cb5d1f7f86b4)。
今回のクエリー対象となるデータは、[この記事](https://qiita.com/turupon/items/e16789a959fc92068c68) にある「IoTSample-write.py」で 2222件生成したものとなります。
なお、Azure Data Exploere の作成内容は以下となっています。

|名称|値|
|-|-|
|クラスター名|ituruadxcluster|
|データベース名|IoTDummyDatabase|


## Azure Data Exploere データライブラリ使用 のための事前準備
Azure Data Exploere データライブラリを利用するために必要な手順は以下となります。
１．アプリの新規登録
２．登録アプリのAPIアクセス許可設定
３．登録アプリのシークレット情報の取得
４．Azure Data Exploere クラスターへのアクセス制御設定
５．Azure Data Exploere クラスターへのアクセス許可設定
６．ローカル環境へのクレデンシャル情報の定義

### アプリの新規登録
１．Azureポータルにサインインします。
２．リソースから [Azure Active Directory] を選択します。
３．画面左側から [アプリの登録] を選択します。
４．画面上部タブから [新規登録] を選択します。
５．[アプリケーションの登録] 画面にある [名前] 欄に [iTuruDataExplorerKustoAccess] と入力し、画面の下部にある [登録] を選択します。
６．登録されたアプリの概要が表示されます。この画面の [アプリケーション（クライアント）ID] と [ディレクトリ（テナント）ID] の値をコピーしておきます。

|![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/544022/a289b617-17a4-4af9-63f0-b738cc832c0c.png)|
|:-:|

### 登録アプリのAPIアクセス許可設定
１．画面の左側から [APIのアクセス許可] を選択します。
２．画面中央の [アクセス許可の追加] を選択します。
３．[APIアクセス許可の要求] 画面から [Azure Data Explorer] を選択します。

|![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/544022/49c4f920-65b9-d376-4afb-3570095cf9c4.png)|
|:-:|

４．表示された [Azure Data Explorer] のアクセス許可にチェックを入れ、画面下部にある [アクセス許可の追加] を選択します。

|![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/544022/0bed9ea2-5c82-8c46-1263-b11c4dc39da7.png)|
|:-:|

５．表示されている画面中央にある [xxxxxxxに管理者の同意を与えます] を押します。
６．アクセス許可に管理者の同意がえられた結果、以下の画面となります。

|![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/544022/92b97f01-dc85-9ef0-c782-b58adb4eda09.png)|
|:-:|

### 登録アプリのシークレット情報の取得
１．画面の左側から [証明書とシークレット] を選択します。
２．画面中央下の [新しいクライアントシークレット] を選択します。
３．[クライアントシークレットの追加] 画面で、[説明] と [有効期限] を設定し、 [追加] を選択します

|![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/544022/9f940815-79ef-f1ad-cc84-dd414c3bae43.png)|
|:-:|

４．元の画面に先程追加したクライアントシークレットが表示されており、その[値] を [コピー] しておきます。

|![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/544022/a04a3ce1-fd0b-c0a3-2de6-59b8825c893f.png)|
|:-:|


### Azure Data Exploere クラスターへのアクセス制御設定
１．Azure Portal において、リソースから Azure Data Explorer の [ituruadxcluster] を選択します。
２．画面左側から [アクセス制御（IAM）] を選択します。
３．画面上部タブから [追加] - [ロールの割り当ての追加] を選択します。
４．[ロールの割り当ての追加] 画面にて、 [役割] 欄で [所有者] を選択し、[選択] 欄で [iTuruDataExplorerKustoAccess] を選択し画面の下部にある [保存] を選択します。

|![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/544022/78019ce9-66e5-09a6-1303-69395abdcc84.png)|
|:-:|

５．登録されたアクセス制御を確認します

|![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/544022/f33d429d-d85e-53cc-53ce-e3ad62010fea.png)|
|:-:|

### Azure Data Exploere クラスターへのアクセス許可設定
１．画面左側から [アクセス許可] を選択します。
２．画面上部タブから [追加] を選択します。
３．[アクセス許可の追加] 画面にて、 [役割の選択] 欄で [AllDatabaseAdmin] を選択し、[プリンシパルを選択する] 欄で [iTuruDataExplorerKustoAccess] を選択します。

|![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/544022/59bedae0-a3c1-c726-64e3-9ad96f1980d3.png)|
|:-:|

４．追加されたアクセス許可を確認します

|![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/544022/d0a74c44-337e-26a4-a12f-eb58e5c6d02a.png)|
|:-:|

### ローカル環境へのクレデンシャル情報の定義
クレデンシャル情報を .zshenv に定義してプログラムを実行しています。環境に合わせて定義ください。

```.zshenv
# Azure Data Exploere の データライブラリ使用によるクエリ実行のための定義（Kusto）
export KUSTO_CLUSTER_NAME="ituruadxcluster"
export KUSTO_DATABASE_NAME="IoTDummyDatabase"
export LOCATION_SMALL_CASE="japaneast"
export TENANT_ID=xxxxxxxxxxxxxxxxxxxxxxxxx   #「アプリの新規登録」でコピーした [ディレクトリ（テナント）ID] 
export CLIENT_ID=yyyyyyyyyyyyyyyyyyyyyyyyy   #「アプリの新規登録」でコピーした [アプリケーション（クライアント）ID]
export CLIENT_KEY=zzzzzzzzzzzzzzzzzzzzzzzz   #「登録アプリのシークレット情報の取得」でコピーした [値]
```

## 実行プログラム
実行するPythonプログラムは以下となります。
実行クエリーは [STEP-2 の「Azure Data Explorer Web UI でデータベースへのクエリを実行」の「1」](https://qiita.com/turupon/items/b2886387832d4826afbf) のものとします。

```KustoSample.py
from azure.kusto.data import KustoClient, KustoConnectionStringBuilder
from azure.kusto.data.exceptions import KustoServiceError
from azure.kusto.data.helpers import dataframe_from_result_table
import pandas as pd
import pprint
import time
import os

# Azure Data Explorer Info
TENANT_ID = os.environ['TENANT_ID']
CLIENT_ID = os.environ['CLIENT_ID']
CLIENT_KEY = os.environ['CLIENT_KEY']
LOCATION_SMALL_CASE = os.environ['LOCATION_SMALL_CASE']
KUSTO_CLUSTER_NAME = os.environ['KUSTO_CLUSTER_NAME']
KUSTO_DATABASE_NAME = os.environ['KUSTO_DATABASE_NAME']
KUSTO_URI = "https://{}.{}.kusto.windows.net".format(KUSTO_CLUSTER_NAME, LOCATION_SMALL_CASE)


# Blobコンテナーのデータの取得
def blobContainerData_kusto() :

    # Kusto接続のための定義
    kusto_connection_string_builder = KustoConnectionStringBuilder.with_aad_application_key_authentication(connection_string=KUSTO_URI, aad_app_id=CLIENT_ID, app_key=CLIENT_KEY, authority_id=TENANT_ID)
    print(kusto_connection_string_builder)

    # Azure Data Explorer に接続しクエリの実行
    kusto_client = KustoClient(kusto_connection_string_builder)
    query = "iot_tbl | sort by ['id'] desc | take 10"             # 実行クエリー
    print(kusto_client)
    print(query)

    # データフレームでダータの取得
    response = kusto_client.execute_query(KUSTO_DATABASE_NAME, query)
    df = dataframe_from_result_table(response.primary_results[0])
    return df

if __name__ == '__main__':
    start = time.time()
    df = blobContainerData_kusto()
    convert_time = time.time() - start

    print("")
    pprint.pprint(df)
    print("\n\rデータ取得時間:{0}".format(convert_time) + " [sec]")
    print("")
```


## 実行結果
Pythonプログラムを実行した結果は以下となります。

```
$ python KustoSample.py
Data Source=https://ituruadxcluster.japaneast.kusto.windows.net;AAD Federated Security=True;Application Client Id=7979797-ecce-c3c3-d1d1-33ffeeee6688;Application Key=****;Authority Id=3a3a3a3a-aacc-4545-7f7f-3030ccc63636
<azure.kusto.data.client.KustoClient object at 0x7fcfb0199610>
iot_tbl | sort by ['id'] desc | take 10

     id                             time     proc section   iot_num iot_state       vol_1      vol_2
0  2221 2021-02-26 17:36:02.518630+00:00  ADX2222       Q  133-1478       青森県  167.753668  55.555238
1  2220 2021-02-26 17:36:02.518618+00:00  ADX2222       W  052-1579       富山県  119.868415  67.729233
2  2219 2021-02-26 17:36:02.518608+00:00  ADX2222       F  635-5427       静岡県  168.603883  88.599550
3  2218 2021-02-26 17:36:02.518599+00:00  ADX2222       D  293-1684       高知県  114.518138  55.669590
4  2217 2021-02-26 17:36:02.518589+00:00  ADX2222       Q  254-0578       愛媛県  126.710203  83.226436
5  2216 2021-02-26 17:36:02.518578+00:00  ADX2222       D  376-9573       福井県  138.938321  80.591457
6  2215 2021-02-26 17:36:02.518568+00:00  ADX2222       H  149-6424       山梨県  132.941341  79.180356
7  2214 2021-02-26 17:36:02.518559+00:00  ADX2222       I  050-4286       大分県  155.100844  87.882249
8  2213 2021-02-26 17:36:02.518549+00:00  ADX2222       C  081-6939       長崎県  149.415190  61.082220
9  2212 2021-02-26 17:36:02.518540+00:00  ADX2222       M  284-7501       岡山県  115.914588  80.191101

データ取得時間:0.5570240020751953 [sec]
```

３つのステップにわたり、「S3 + Athena」 で実行していたことを 「BLOB + Azure Data Explorer」 で実現してみました。


## 本課題のシリーズ情報
３つのステップ
[STEP-1．Azure Data Explorer クラスターとデータベースを作成する](https://qiita.com/turupon/items/ce4be135cb5d1f7f86b4)
[STEP-2．Azure Data Explorer の Web UI でデータベースへのクエリを実行する](https://qiita.com/turupon/items/b2886387832d4826afbf)
[STEP-3．Azure Data Explorer の Python ライブラリを使用してデータベースへのクエリを実行する](https://qiita.com/turupon/items/6739b03ee80d9d5e2ae0)