【備忘録】Azure Data Lake Storage Gen2からPolyBaseでDataWarehouseへデータ取込みしてみる

# PolyBaseを使用してみる
備忘録として残しています。
PolyBaseは端的にいうと、構造化データと非構造化データを結びつけることができる技術です。

ということで、Data Lake Storage Gen2（以降、Gen2）からAzure Synapse AnalyticsサービスのDataWarehouse（以降、DWH）への取り込みをやってみました。

前提として、Googole検索結果をスクレイピングしたデータ（CSV）がローカル端末にあることとします。

## リソースの作成とデータ準備
### Data Lake Storage Gen2
上記のとおり、まずはCSVファイルアップロード先のGen2をデプロイします。
「ホーム」→「+リソースの作成」→「分析」の中にあります。
ex) アカウント名：mysamplegen2

デプロイはこの辺を参考にしてください。
　[→https://docs.microsoft.com/ja-jp/learn/modules/introduction-to-azure-data-lake-storage/](https://docs.microsoft.com/ja-jp/learn/modules/introduction-to-azure-data-lake-storage/)

#### ①コンテナの作成
デプロイされたGen2の概要タブの中央ら辺に「コンテナ」があるので、そこから作成します。
ex) コンテナ名：sample-output

#### ②アクセスキーの保持
作成したGen2ストレージを開き、アクセスキータブを選択すると「key1」のところに「キー」があるので控えておきます。

#### ③URLの保持
作成したコンテナを開き、プロパティタブを選択すると「URL」があるので控えておきます。
ex) URL：https\://mysamplegen2.blob.core.windows.net/sample-output

#### ④CSVファイルをアップロード
カンマ区切りのデータが格納されているCSVファイルをGen2のコンテナにアップロードします。
コンテナ選択後、上部に「↑ アップロード」があるので、そこからアップロードします。
※ちなみに私は、ローカル（自身のパソコン）にあるSQL Serverから見出しなしでCSVエクスポートしました。


### Azure Synapse Analytics
CSVデータの受け先となるAzure Synapse Analyticsリソースを作成します。
「ホーム」→「+リソースの作成」→「分析」の中にあります。
この辺が参考になります。
 [→https://docs.microsoft.com/ja-jp/learn/modules/design-azure-sql-data-warehouse/](https://docs.microsoft.com/ja-jp/learn/modules/design-azure-sql-data-warehouse/)

ここまでで、前準備は完了です。
次からはDWHのクエリエディタで操作します。

## 以降の作業はデータウェアハウス側で操作します
「すべてのリソース」などから作成したSynapse SQLプール(データウェアハウス)を開きます。
ちなみに、SQLプール名、SQLサーバー名は例として下記とします。

- SQLプール名：mySampleDW
- SQLサーバ名：samplesvr

データウェアハウスの左側領域から「クエリエディター(プレビュー)」を起動します。
データベースにログインします。（私はSQL Server認証にしました）
※ログインエラーになる場合、SQL Serverリソースから「ファイアウォールと仮想ネットワーク」を開き、クライアントIPの追加をして保存します。

### インポートデータベース作成
マスターキーの作成をします。

```
CREATE MASTER KEY;

CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
WITH
    IDENTITY = 'mysamplegen2',
    SECRET = '上記で控えたアクセスキー'
;
```

### 外部データソース接続を作成
LOCATIONには、Genアカウント作成時に控えたストレージアカウント、コンテナー名を使用します。
ex)　LOCATION = 'wasbs://[コンテナー名]@[ストレージアカウント名].blob.core.windows.net',

```
CREATE EXTERNAL DATA SOURCE AzureStorageAdd
WITH (
    TYPE = HADOOP,
    LOCATION = 'wasbs://sample-output@mysamplegen2.blob.core.windows.net',
    CREDENTIAL = AzureStorageCredential
);
```
### インポートファイルの形式を定義
CSVファイルの形式を定義します。

```
CREATE EXTERNAL FILE FORMAT CSV
WITH (FORMAT_TYPE = DELIMITEDTEXT,
      FORMAT_OPTIONS(
          FIELD_TERMINATOR = ',',
          STRING_DELIMITER = '',
          DATE_FORMAT = '',
          USE_TYPE_DEFAULT = False)
);
```

## 一時テーブルを作成
一時テーブル名はTempとしています。
カラム名はCSVデータと合わせます。
※ローカルのSQLServerからエクスポートした場合は、カラム数やデータ型などはテーブル定義に合わせます。

```
CREATE EXTERNAL TABLE dbo.TempInit (
    [DATEID] int NULL,
    [KEYWORD] nvarchar(100) NULL,
    [FIXDATE] date NULL,
    [RANK] int NULL,
    [URL] nvarchar(500) NULL,
    [TITLENM] nvarchar(200) NULL,
    [CREATE_USER] nvarchar(50) NULL,
    [CREATE_DATE] datetime NULL
)
WITH (
    LOCATION='/',
    -- LOCATION='/datarank.csv',
    DATA_SOURCE=AzureStorageInit,
    FILE_FORMAT=CSV
);
```
こちらを実行すると、一時テーブルにデータが反映されます。
感覚的にはビューみたいな感じです。
Gen2にアップロードしているCSVが変われば、こちらにも反映されます。

## ターゲットテーブルの作成
一時テーブルからターゲットテーブルを作成します。

```
CREATE TABLE [dbo].[KEYWORDRANK]
WITH (   
    CLUSTERED COLUMNSTORE INDEX,
    DISTRIBUTION = ROUND_ROBIN
)
AS
SELECT * FROM [dbo].[TempInit];
```

### データを追加したい時のクエリ
ちなみにデータ追加は下記のような感じのクエリで実行できます。

```
INSERT INTO [dbo].[KEYWORDRANK]
SELECT * FROM Temp;
```

## クエリパフォーマンス向上のために
感覚的にはインデックスです。

```
CREATE STATISTICS [FIXDATE] on [KEYWORDRANK] ([FIXDATE]);
CREATE STATISTICS [URL] on [KEYWORDRANK] ([URL]);
CREATE STATISTICS [SAM] on [KEYWORDRANK] ([TITLENM],[RANK]);
```

これでGen2からDWHへのデータ取り込みは終わりです。
以上です。