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へのデータ取り込みは終わりです。
以上です。