SQL Server 2019 ビッグデータクラスターを AKS で使う - サンプルデータを試す

今回は、[前回](https://qiita.com/kenakamu/items/fd7834f07e391b90337a)構築した環境にサンプルデータをインストールします。またサンプルスクリプトの中身みしっかり見ていきます。

[参照: チュートリアル:SQL Server ビッグ データ クラスターにサンプル データを読み込む](https://docs.microsoft.com/ja-jp/sql/big-data-cluster/tutorial-load-sample-data?view=sql-server-ver15)

# 前提条件

- Windows 10 環境。Linux の場合は上記参照から別途手順を確認
- [前回の記事]() の手順がすべて終わっていること
- curl 
- sa アカウントの有効化

## curl

サンプルデータのインストールなどいくつかのスクリプトで利用するため、curl をインストールします。

https://curl.haxx.se/windows/

## sa アカウントの有効化

セキュリティの観点から既定で sa アカウントが無効になっています。サンプルデータの読み込みは sa アカウントの使用を想定しているため、アカウントを有効にしておきます。

1\. Azure Data Studio (ADS) で SQL Server 2019 BDC に接続。

2\.「New Query」より以下クエリを実行。

- パスワードは任意のものに変更

```sql
ALTER LOGIN sa ENABLE ;
GO
ALTER LOGIN sa WITH PASSWORD = '<password>' ;
GO
```

3\. sa アカウントでログインできることを確認。

# サンプルデータの読み込み

1\. コマンドプロンプトを開き、任意のフォルダを作成。

```shell
mkdir bdcsample
cd bdcsample
```

2\. サンプル データ用のブートストラップ スクリプトと Transact-SQL スクリプトをダウンロード。

```shell
curl -o bootstrap-sample-db.cmd "https://raw.githubusercontent.com/Microsoft/sql-server-samples/master/samples/features/sql-big-data-cluster/bootstrap-sample-db.cmd"
curl -o bootstrap-sample-db.sql "https://raw.githubusercontent.com/Microsoft/sql-server-samples/master/samples/features/sql-big-data-cluster/bootstrap-sample-db.sql"
```
3\. SQL マスターインスタンスと Gateway の IP アドレスを確認。

```shell
kubectl get svc master-svc-external gateway-svc-external
```
4\. スクリプトを実行。

- SQL_MASTER_IP および KNOX_IP: 上記で取得したそれぞれの IP
- SQL_MASTER_SA_PASSWORD: 指定した sa のパスワード
- KNOX_PASSWORD: 環境構築時に指定したパスワード

```shell
.\bootstrap-sample-db.cmd mssql-cluster <SQL_MASTER_IP> <SQL_MASTER_SA_PASSWORD> <KNOX_IP> <KNOX_PASSWORD>
```

5\. スクリプトが完了するまで待機。途中でエラーが出る場合は、ログを確認して対応。

```shell
Verifying sqlcmd.exe is in path
Verifying bcp.exe is in path
Verifying kubectl.exe is in path
Verifying curl.exe is in path
A subdirectory or file bootstrap-sample-db.cmd already exists.
Copying sales database backup file to SQL Master instance...
Configuring sample database(s)...
Removing database backup file...
removed '/var/opt/mssql/data/tpcxbb_1gb.bak'
Exporting web_clickstreams data...
Exporting inventory data...
Exporting customer data...
Exporting product_reviews data...
Uploading web_clickstreams data to HDFS...
{"boolean":true}
Uploading product_reviews data to HDFS...
{"boolean":true}.
Bootstrap of the sample database completed successfully.
You can now login using "root" and Knox password to get the unified experience in Azure Data Studio.
Data files for Oracle setup are located at [C:\Users\kenakamu\AppData\Local\Temp\bootstrap-sample-db.cmd].
```

サンプルが読み込まれると、sales データベースと csv ファイルが HDFS 上に作成されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/214116/94188e6c-c7de-ef81-7333-44410c908716.png)

## サンプルで作成された CSV の確認

ADS を使うと HDFS 上の CSV をプレビューできます。

1\. web_clickstreams.csv を右クリックして「Preview」を選択。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/214116/63a0e1b8-d6b8-08dd-1495-7ea40fb6e039.png)

2\. 表示されたデータを確認。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/214116/15d9995a-bb44-1d1c-c8ac-7785bdb9e796.png)

3\. 同様に product_reviews.csv も確認。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/214116/d0885bde-65a7-2184-36fa-69c4934032e9.png)

# データ仮想化のテスト

HDFS に保存された csv ファイルを外部テーブルとして作成して読み取ることができます。
[参照: チュートリアル:SQL Server ビッグ データ クラスター内の HDFS にクエリを実行する](https://docs.microsoft.com/ja-jp/sql/big-data-cluster/tutorial-query-hdfs-storage-pool?view=sql-server-ver15)

1\. ADS で SQL Server 2019 BDC に接続。

2\. New Query より以下のクエリを実行して sales データベースにスイッチ

```sql
USE Sales
GO
```
3\. まず HDFS から読み取る CSV のファイル形式を定義。

- 参照ページのサンプルでは FIRST_ROW が 2 に指定されているが、CSV にヘッダーがないため、1 行目から読み取るように変更
- 参照ページのサンプルでは csv_file という名前がだが、ここではヘッダーを持たない CSV 用の定義としてため、csv_file_without_header に変更

```sql
CREATE EXTERNAL FILE FORMAT csv_file_without_header
WITH (
    FORMAT_TYPE = DELIMITEDTEXT,
    FORMAT_OPTIONS(
        FIELD_TERMINATOR = ',',
        STRING_DELIMITER = '"',
        FIRST_ROW = 1,
        USE_TYPE_DEFAULT = TRUE)
);
```
4\. 記憶域プールに対してデータソースがない場合は新規に作成。

```sql
IF NOT EXISTS(SELECT * FROM sys.external_data_sources WHERE name = 'SqlStoragePool')
BEGIN
  CREATE EXTERNAL DATA SOURCE SqlStoragePool
  WITH (LOCATION = 'sqlhdfs://controller-svc/default');
END
```

5\. 外部テーブルの作成。

- Data_SOURCE: 記憶域プールを指定
- LOCATION: CSV を含む HDFS 上のパス
- FILE_FORMAT: 作成したフォーマット

```sql
CREATE EXTERNAL TABLE [web_clickstreams_hdfs]
("wcs_click_date_sk" BIGINT , "wcs_click_time_sk" BIGINT , "wcs_sales_sk" BIGINT , "wcs_item_sk" BIGINT , "wcs_web_page_sk" BIGINT , "wcs_user_sk" BIGINT)
WITH
(
    DATA_SOURCE = SqlStoragePool,
    LOCATION = '/clickstream_data',
    FILE_FORMAT = csv_file_without_header 
);
GO
```
6\. テストクエリの実行。

```sql
SELECT  
    wcs_user_sk,
    SUM( CASE WHEN i_category = 'Books' THEN 1 ELSE 0 END) AS book_category_clicks,
    SUM( CASE WHEN i_category_id = 1 THEN 1 ELSE 0 END) AS [Home & Kitchen],
    SUM( CASE WHEN i_category_id = 2 THEN 1 ELSE 0 END) AS [Music],
    SUM( CASE WHEN i_category_id = 3 THEN 1 ELSE 0 END) AS [Books],
    SUM( CASE WHEN i_category_id = 4 THEN 1 ELSE 0 END) AS [Clothing & Accessories],
    SUM( CASE WHEN i_category_id = 5 THEN 1 ELSE 0 END) AS [Electronics],
    SUM( CASE WHEN i_category_id = 6 THEN 1 ELSE 0 END) AS [Tools & Home Improvement],
    SUM( CASE WHEN i_category_id = 7 THEN 1 ELSE 0 END) AS [Toys & Games],
    SUM( CASE WHEN i_category_id = 8 THEN 1 ELSE 0 END) AS [Movies & TV],
    SUM( CASE WHEN i_category_id = 9 THEN 1 ELSE 0 END) AS [Sports & Outdoors]
  FROM [dbo].[web_clickstreams_hdfs]
  INNER JOIN item it ON (wcs_item_sk = i_item_sk
                        AND wcs_user_sk IS NOT NULL)
GROUP BY  wcs_user_sk;
GO
```
# データプールへの取り込み

外部テーブルはデータが常に外部にあるため、パフォーマンスに影響があります。データプールへデータを取り込んでしまえばより高速にクエリを実行できます。

## T-SQL を使ったデータプールへの取り込み

[参照: チュートリアル: Transact-SQL を使用して SQL Server のデータ プールにデータを取り込む](https://docs.microsoft.com/ja-jp/sql/big-data-cluster/tutorial-data-pool-ingest-sql?view=sql-server-ver15)

1\. 引き続き ADS よりデータプールへの外部データソースを作成。

```sql
IF NOT EXISTS(SELECT * FROM sys.external_data_sources WHERE name = 'SqlDataPool')
  CREATE EXTERNAL DATA SOURCE SqlDataPool
  WITH (LOCATION = 'sqldatapool://controller-svc/default');
```

2\. データプールに外部テーブルを作成。

- 複数のデータプールがある場合はラウンドロビンでデータを分散格納

```sql
CREATE EXTERNAL TABLE [web_clickstream_clicks_data_pool]
("wcs_user_sk" BIGINT , "i_category_id" BIGINT , "clicks" BIGINT)
WITH
(
    DATA_SOURCE = SqlDataPool,
    DISTRIBUTION = ROUND_ROBIN
);
```

3\. データの仮想化で作成した web_clickstreams_hdfs テーブルと、item テーブルを結合した結果を web_clickstream_clicks_data_pool に読み込み。

- 参照先のサンプルでは HAVING COUNT_BIG(*) > 100; があるが、パフォーマンス比較のため除外

```sql
INSERT INTO web_clickstream_clicks_data_pool
SELECT wcs_user_sk, i_category_id, COUNT_BIG(*) as clicks
  FROM sales.dbo.web_clickstreams_hdfs
INNER JOIN sales.dbo.item it ON (wcs_item_sk = i_item_sk
                        AND wcs_user_sk IS NOT NULL)
GROUP BY wcs_user_sk, i_category_id
```

4\. 挿入されたレコード数と中身を確認。

```sql
SELECT count(*) FROM [dbo].[web_clickstream_clicks_data_pool]
SELECT TOP 10 * FROM [dbo].[web_clickstream_clicks_data_pool]  
```
5\. パフォーマンス比較のため、以下のクエリを実行。

- 参照先のサンプルでは Top 100 があるが、パフォーマンス比較のため除外

```sql
SELECT 
   w.wcs_user_sk,
   SUM( CASE WHEN i.i_category = 'Books' THEN 1 ELSE 0 END) AS book_category_clicks,
   SUM( CASE WHEN w.i_category_id = 1 THEN 1 ELSE 0 END) AS [Home & Kitchen],
   SUM( CASE WHEN w.i_category_id = 2 THEN 1 ELSE 0 END) AS [Music],
   SUM( CASE WHEN w.i_category_id = 3 THEN 1 ELSE 0 END) AS [Books],
   SUM( CASE WHEN w.i_category_id = 4 THEN 1 ELSE 0 END) AS [Clothing & Accessories],
   SUM( CASE WHEN w.i_category_id = 5 THEN 1 ELSE 0 END) AS [Electronics],
   SUM( CASE WHEN w.i_category_id = 6 THEN 1 ELSE 0 END) AS [Tools & Home Improvement],
   SUM( CASE WHEN w.i_category_id = 7 THEN 1 ELSE 0 END) AS [Toys & Games],
   SUM( CASE WHEN w.i_category_id = 8 THEN 1 ELSE 0 END) AS [Movies & TV],
   SUM( CASE WHEN w.i_category_id = 9 THEN 1 ELSE 0 END) AS [Sports & Outdoors]
FROM [dbo].[web_clickstream_clicks_data_pool] as w
INNER JOIN (SELECT DISTINCT i_category_id, i_category FROM item) as i
   ON i.i_category_id = w.i_category_id
GROUP BY w.wcs_user_sk;
```

6\. 結果を比較。

[データ仮想化]
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/214116/6ece0b1b-59bf-c9b5-a181-4cda5aefa236.png)
[データプール]
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/214116/8c803532-3519-0886-b783-0c022729de8e.png)

## Spark job を使ったデータプールへの取り込み

SQL Server 2019 BDC のウリである Spark を使った手法も試してみます。尚、以下手順は一部不足しているため、こちらの記事と内容が異なりますが、そのうち公式も更新されると思います。
[参照: チュートリアル:Spark ジョブを使用して SQL Server のデータ プールにデータを取り込む](https://docs.microsoft.com/ja-jp/sql/big-data-cluster/tutorial-data-pool-ingest-spark?view=sql-server-ver15)

### SQL 側での準備

1\. ADS より外部データソースを作成。

```sql
USE Sales
GO
IF NOT EXISTS(SELECT * FROM sys.external_data_sources WHERE name = 'SqlDataPool')
  CREATE EXTERNAL DATA SOURCE SqlDataPool
  WITH (LOCATION = 'sqldatapool://controller-svc/default');
```

2\. データプールに外部テーブルの作成。

```sql
USE Sales
GO
IF NOT EXISTS(SELECT * FROM sys.external_tables WHERE name = 'web_clickstreams_spark_results')
   CREATE EXTERNAL TABLE [web_clickstreams_spark_results]
   ("wcs_click_date_sk" BIGINT , "wcs_click_time_sk" BIGINT , "wcs_sales_sk" BIGINT , "wcs_item_sk" BIGINT , "wcs_web_page_sk" BIGINT , "wcs_user_sk" BIGINT)
   WITH
   (
      DATA_SOURCE = SqlDataPool,
      DISTRIBUTION = ROUND_ROBIN
   );
```

3\. Spark から SQL につなぐユーザーを sales データベースに作成し、権限を付与。

- 任意のパスワードに変更

```sql
CREATE LOGIN connector_user  WITH PASSWORD ='password123!#' 
CREATE USER connector_user FROM LOGIN connector_user

-- データプールで外部データソースを作る権限
grant alter any external data source to connector_user;

-- テーブル作成権限
grant create table to connector_user;
grant alter any schema to connector_user;

-- データ読み書き権限
ALTER ROLE [db_datareader] ADD MEMBER connector_user
ALTER ROLE [db_datawriter] ADD MEMBER connector_user
```

4\. データプール側にも同じユーザー追加および権限付与。

- 同じパスワードを使用

```sql
-- データプールにユーザーを追加
EXECUTE( 'Use sales; CREATE LOGIN connector_user  WITH PASSWORD = ''password123!#'';' )  AT  DATA_SOURCE SqlDataPool;
EXECUTE( 'Use sales; CREATE USER  connector_user ; ALTER ROLE [db_datareader] ADD MEMBER connector_user;  ALTER ROLE [db_datawriter] ADD MEMBER connector_user ;')  AT  DATA_SOURCE SqlDataPool;
```

### Spark Job の実行

1\. sales データベースを右クリックして、New Notebook をクリック。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/214116/16ba934f-54a3-b978-2f76-9278d1e4e92f.png)

2\.「Kernel」より「Spark | Scala」を選択。また「Attach To」で sales データベースが選択されている事を確認。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/214116/9eead924-a4f7-0bd7-43eb-ce19ff7268f7.png)

3\.「Code」をクリックして以下コードを張り付け。

- Spark から SQL Server に対するコネクタを設定
- データソースとなる HDFS パスを設定
- データ格納先となる外部テーブルとスキーマを設定
- ユーザーとパスワードは上記で作成したものを利用

```scala
import org.apache.spark.sql.types._
import org.apache.spark.sql.{SparkSession, SaveMode, Row, DataFrame}

// Change per your installation
val user= "connector_user"
val password= "password123!#"
val database =  "sales"
val sourceDir = "/clickstream_data"
val datapool_table = "web_clickstreams_spark_results"
val datasource_name = "SqlDataPool"
val schema = StructType(Seq(
StructField("wcs_click_date_sk",LongType,true), StructField("wcs_click_time_sk",LongType,true), 
StructField("wcs_sales_sk",LongType,true), StructField("wcs_item_sk",LongType,true),
StructField("wcs_web_page_sk",LongType,true), StructField("wcs_user_sk",LongType,true)
))

val hostname = "master-0.master-svc"
val port = 1433
val url = s"jdbc:sqlserver://${hostname}:${port};database=${database};user=${user};password=${password};"
```

4\. 再度「Code」をクリックして、以下コードを追加。

```scala
import org.apache.spark.sql.{SparkSession, SaveMode, Row, DataFrame}

val df = spark.readStream.format("csv").schema(schema).option("header", true).load(sourceDir)
val query = df.writeStream.outputMode("append").foreachBatch{ (batchDF: DataFrame, batchId: Long) => 
          batchDF.write
           .format("com.microsoft.sqlserver.jdbc.spark")
           .mode("append")
            .option("url", url)
            .option("dbtable", datapool_table)
            .option("user", user)
            .option("password", password)
            .option("dataPoolDataSource",datasource_name).save()
         }.start()

query.processAllAvailable()
query.awaitTermination(40000)
```

5\.「Run Cells」ボタンをクリックして、すべてのコードを実行。少し時間がかかるため、ADS Notebook のビデオでも見て待つ。
[![Introduction to Azure Data Studio Notebooks | Data Exposed](http://img.youtube.com/vi/Nt4kIHQ0IOc/0.jpg)](http://www.youtube.com/watch?v=Nt4kIHQ0IOc)

6\. 完了後 ADS よりデータを確認。

```sql
SELECT count(*) FROM [web_clickstreams_spark_results];
SELECT TOP 10 * FROM [web_clickstreams_spark_results];
```

# サンプルデータ読み込みスクリプトの詳細

では最後にスクリプトの詳細を見て、どのようにサンプルを SQL Server 2019 BDC に作成したか見ていきます。

## 変数設定と事前準備

スクリプトの 34 行目までは以下の通り

- コマンドの引数から変数を受け取り
- ポートなど既定の変数を設定
- 必要なツールがインストールされているかの確認
- 一時フォルダの作成

```shell
@echo off
REM bootstrap sample database CMD script
setlocal enableextensions
setlocal enabledelayedexpansion
set CLUSTER_NAMESPACE=%1
set SQL_MASTER_IP=%2
set SQL_MASTER_SA_PASSWORD=%3
set KNOX_IP=%4
set KNOX_PASSWORD=%5
set AW_WWI_SAMPLES=%6
set SQL_MASTER_PORT=%7
set KNOX_PORT=%8
set STARTUP_PATH=%~dp0
set TMP_DIR_NAME=%~nx0

if NOT DEFINED CLUSTER_NAMESPACE goto :usage
if NOT DEFINED SQL_MASTER_IP goto :usage
if NOT DEFINED SQL_MASTER_SA_PASSWORD goto :usage
if NOT DEFINED KNOX_IP goto :usage
if NOT DEFINED KNOX_PASSWORD set KNOX_PASSWORD=%SQL_MASTER_SA_PASSWORD%
if NOT DEFINED AW_WWI_SAMPLES set AW_WWI_SAMPLES=no
if NOT DEFINED SQL_MASTER_PORT set SQL_MASTER_PORT=31433
if NOT DEFINED KNOX_PORT set KNOX_PORT=30443

set SQL_MASTER_INSTANCE=%SQL_MASTER_IP%,%SQL_MASTER_PORT%
set KNOX_ENDPOINT=%KNOX_IP%:%KNOX_PORT%

for %%F in (sqlcmd.exe bcp.exe kubectl.exe curl.exe) do (
    echo Verifying %%F is in path & CALL WHERE /Q %%F || GOTO exit
)

pushd "%tmp%"
md %TMP_DIR_NAME% >NUL
cd %TMP_DIR_NAME%
```

## データベースバックアップの取得

36-39 行目では bak ファイルをダウンロードして保存しています。

```shell
if NOT EXIST tpcxbb_1gb.bak (
    echo Downloading sample database backup file...
    %DEBUG% curl -G "https://sqlchoice.blob.core.windows.net/sqlchoice/static/tpcxbb_1gb.bak" -o tpcxbb_1gb.bak
)
```
## SQL マスターポッドへのログイン

41 行目から 51 行目では sqlcmd を使ってマスターポッドの情報を取得し、kubectl cp コマンドでバックアップファイルをポッドにコピーしています。

```shell
set SQLCMDSERVER=%SQL_MASTER_INSTANCE%
set SQLCMDUSER=sa
set SQLCMDPASSWORD=%SQL_MASTER_SA_PASSWORD%
for /F "usebackq tokens=1,2" %%v in (`sqlcmd -I -b -h-1 -W -Q "SET NOCOUNT ON; SELECT @@SERVERNAME, SERVERPROPERTY('IsHadrEnabled');"`) do (
	SET MASTER_POD_NAME=%%v
	SET HADR_ENABLED=%%w
)

REM Copy the backup file, restore the database, create necessary objects and data file
echo Copying sales database backup file to SQL Master instance...
%DEBUG% kubectl cp tpcxbb_1gb.bak %CLUSTER_NAMESPACE%/%MASTER_POD_NAME%:var/opt/mssql/data/ -c mssql-server || goto exit
```
## 追加サンプルのインストール

53 行目から 80 行目はコマンド実行時に --install-extra-samples を第 6 引数で渡すと実行される追加のサンプルデータベースの復元。(AW=Adventure Works, WWI=World Wide Importer)

```shell
REM Download and copy the sample backup files
if /i "%AW_WWI_SAMPLES%" EQU "--install-extra-samples" (
    set FILES=AdventureWorks2016_EXT.bak AdventureWorksDW2016_EXT.bak
    for %%f in (!FILES!) do (
        if NOT EXIST %%f (
                echo Downloading %%f sample database backup file...
                %DEBUG% curl -L -G "https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/%%f" -o %%f
        )
        echo Copying %%f database backup file to SQL Master instance...
        %DEBUG% kubectl cp %%f %CLUSTER_NAMESPACE%/%MASTER_POD_NAME%:var/opt/mssql/data/ -c mssql-server || goto exit

        echo Removing database backup file...
        %DEBUG% kubectl exec %MASTER_POD_NAME% -n %CLUSTER_NAMESPACE% -c mssql-server -i -t -- bash -c "rm -rvf /var/opt/mssql/data/%%f"
    )

    set FILES=WideWorldImporters-Full.bak WideWorldImportersDW-Full.bak
    for %%f in (!FILES!) do (
        if NOT EXIST %%f (
            echo Downloading %%f sample database backup file...
            %DEBUG% curl -L -G "https://github.com/Microsoft/sql-server-samples/releases/download/wide-world-importers-v1.0/%%f" -o %%f
        )
        echo Copying %%f database backup file to SQL Master instance...
        %DEBUG% kubectl cp %%f %CLUSTER_NAMESPACE%/%MASTER_POD_NAME%:var/opt/mssql/data/ -c mssql-server || goto exit

        echo Removing database backup file...
        %DEBUG% kubectl exec %MASTER_POD_NAME% -n %CLUSTER_NAMESPACE% -c mssql-server -i -t -- bash -c "rm -rvf /var/opt/mssql/data/%%f"
    )
)
```

## HADR (High Availability/Disaster Recovery) チェック

82 行目から 87 行目は HA/DR 構成の確認と、その場合の対処。

```shell
REM If HADR is enabled then port-forward 1533 temporarily to connect to the primary directly
REM Default timeout for port-forward is 5 minutes so start command in background & it will terminate automatically
if /i "%HADR_ENABLED%" EQU "1" (
    %DEBUG% start kubectl port-forward pods/%MASTER_POD_NAME% 1533:1533 -n %CLUSTER_NAMESPACE%
    SET SQLCMDSERVER=127.0.0.1,1533
)
```

## サンプルデータベースの構成

89 行目から 107 行目はサンプルデータベースの作成、バックアップの削除およびデータのコピーを実施。スクリプトは bootstrap-sample-db.sql も利用しています。

```shell
echo Configuring sample database(s)...
%DEBUG% sqlcmd -i "%STARTUP_PATH%bootstrap-sample-db.sql" -o "bootstrap.out" -I -b -v SA_PASSWORD="%KNOX_PASSWORD%" || goto exit

REM remove files copied into the pod:
echo Removing database backup file...
%DEBUG% kubectl exec %MASTER_POD_NAME% -n %CLUSTER_NAMESPACE% -c mssql-server -i -t -- bash -c "rm -rvf /var/opt/mssql/data/tpcxbb_1gb.bak"

for %%F in (web_clickstreams inventory customer) do (
    if NOT EXIST %%F.csv (
        echo Exporting %%F data...
        if /i %%F EQU web_clickstreams (set DELIMITER=,) else (SET DELIMITER=^|)
        %DEBUG% bcp sales.dbo.%%F out "%%F.csv" -S %SQLCMDSERVER% -Usa -P%SQL_MASTER_SA_PASSWORD% -c -t"!DELIMITER!" -o "%%F.out" -e "%%F.err" || goto exit
    )
)

if NOT EXIST product_reviews.csv (
    echo Exporting product_reviews data...
    %DEBUG% bcp "select pr_review_sk, replace(replace(pr_review_content, ',', ';'), char(34), '') as pr_review_content from sales.dbo.product_reviews" queryout "product_reviews.csv" -S %SQLCMDSERVER% -Usa -P%SQL_MASTER_SA_PASSWORD% -c -t, -o "product_reviews.out" -e "product_reviews.err" || goto exit
)
```

スクリプトでは以下のことを実施しています。

- root ユーザーに sysadmin 権限を付与
- R/Python/Java などが実行できるよう external scripts enabled 有効化
- 外部テーブルに挿入できるよう allow polybase export を有効化
- 各種データソースの作成
- /var/opt/mssql/data にコピーされた bak ファイルを順次リストア、また tpcxbb_1gb を sales にリネーム
- リストアしたデータベースの互換性レベルを 150 に変更
- ビューや追加のテーブルを作成

## HDFS にファイルをコピー

109 行目から 119 行目は HTTP PUT を利用して Gateway エンドポイント経由でフォルダの作成と CSV をコピーしています。

```shell
REM Copy the data file to HDFS
echo Uploading web_clickstreams data to HDFS...
%DEBUG% curl -L -k -u root:%KNOX_PASSWORD% -X PUT "https://%KNOX_ENDPOINT%/gateway/default/webhdfs/v1/clickstream_data?op=MKDIRS" || goto exit
%DEBUG% curl -L -k -u root:%KNOX_PASSWORD% -X PUT "https://%KNOX_ENDPOINT%/gateway/default/webhdfs/v1/clickstream_data/web_clickstreams.csv?op=create&overwrite=true" -H "Content-Type: application/octet-stream" -T "web_clickstreams.csv" || goto exit
:: del /q web_clickstreams.*

echo.
echo Uploading product_reviews data to HDFS...
%DEBUG% curl -L -k -u root:%KNOX_PASSWORD% -X PUT "https://%KNOX_ENDPOINT%/gateway/default/webhdfs/v1/product_review_data?op=MKDIRS" || goto exit
%DEBUG% curl -L -k -u root:%KNOX_PASSWORD% -X PUT "https://%KNOX_ENDPOINT%/gateway/default/webhdfs/v1/product_review_data/product_reviews.csv?op=create&overwrite=true" -H "Content-Type: application/octet-stream" -T "product_reviews.csv" || goto exit
:: del /q product_reviews.*
```

# クラスターの削除

最後に azdata を使ってクラスタを削除します。

1\. azdata でクラスタにログイン。必要に応じてプロンプトを入力。

```shell
azdata login
```

2\. delete コマンドで削除。

```shell
azdata bdc delete -n mssql-cluster
```
3\. ポッドやサービスを確認。

```shell
>kubectl get all
No resources found.
```

# AKS ノードプールの停止

AKS は VM ベースで課金されるため、使わない場合、VM を停止します。

1\. Azure ポータルより AKS のノードプールが展開されているリソースグループを選択。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/214116/46e53291-51a5-918a-c238-88b47ce5e495.png)

2\. 仮想マシンのスケールセットを選択して、「割り当てを解除する」から VM を停止する。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/214116/f1b77f21-68db-59f1-5467-9da84cd43c57.png)

# まとめ

今回は提供されているサンプルを使って一部の機能を試したほか、サンプル作成スクリプトがどのように SQL サーバーと HDFS にアクセスして操作したかを見ていきました。次回は展開をカスタマイズしてみます。

[次の記事へ](https://qiita.com/kenakamu/items/56d30f66ddf90414e752)
[目次へ戻る](https://qiita.com/kenakamu/items/e155740aa60c85a3d867)