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/

①コンテナの作成

デプロイされた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/

ここまでで、前準備は完了です。
次からは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へのデータ取り込みは終わりです。
以上です。

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