データサイエンス100本ノック楽しい(Azureを添えて）

概要

お盆休みは暇なので「データサイエンス100本ノック（構造化データ加工編）」に取り組んだ。

環境はDockerで整備されていてすぐ取り組めるが、せっかくなので自分でAzureにSQL Database作ってから取り組む（Azureの無料枠の範囲でやる）

本記事の流れ

環境構築
問題を解く！

1.1 環境構築（Azure Database接続まで）

Microsoftのドキュメント¹に沿って、Azure Databaseを作成した（Standard S0 10 DTU, 250GB）。無料期間を活用するので、料金が出ているが課金はされない。

azuredb

SQL Server Management Studioで接続できるか確認。接続できなければ、パブリックエンドポイントの設定が怪しい（一敗）

ssms

1.2 環境構築（テーブル準備）

テーブル作成のために一工夫必要。CSVファイルをBULK INSERTなりして初期データを入れたいが、Azure DatabaseにローカルのCSVをBULK INSERTしたりできないので、Blob Strage上におく必要がある。

仕方がないのでコンテナをつくって、適当な場所にCSVをアップロードした。また、SAS（Shared Access Signature ）を発行して、指定のファイルに時間制限付きのアクセス権を許可する URI取得しておいた。²

テーブルをCREATEした後に、下記SQLを実行した。BULK INSERTの引数はかなり厄介だった…。³

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
USE TESTDB;

-- 一度だけ実行が必要
CREATE MASTER KEY ENCRYPTION BY PASSWORD='input here';

-- 外部ソースとCREDENTIALを削除
IF EXISTS (SELECT * FROM sys.external_data_sources WHERE name = 'MyAzureBlobStorage')
	DROP EXTERNAL DATA SOURCE MyAzureBlobStorage 

-- 外部ソースとCREDENTIALを作成
DROP DATABASE SCOPED CREDENTIAL credtest;
CREATE DATABASE SCOPED CREDENTIAL credtest
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'input token here'

CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH ( TYPE = BLOB_STORAGE,
        LOCATION = 'https://コンテナ名.blob.core.windows.net', 
		CREDENTIAL = credtest
);

-- 参考：UTF-8=コードページ65001と指定。
-- \nでは上手くいかないので0x0aで改行文字を指定。
TRUNCATE TABLE category
BULK INSERT category
FROM 'コンテナ名/category.csv' 
WITH (
   DATA_SOURCE = 'MyAzureBlobStorage'
	,FIRSTROW = 2
	,FIELDTERMINATOR = ','
	,DATAFILETYPE='char'
	,ROWTERMINATOR = '0x0a'
	,CODEPAGE = '65001'
);

これで環境の準備ができた！

2．問題を解く！

まだ途中だが、以下にまとめていく。

dannyso16/sql-100knocks

３．完走した感想

そのうち書きたい!!!

４．参考

Microsoftのドキュメントに大体乗っている。Azure SQL Databaseを無料で試すには、Azureの無料アカウントをご利用ください ↩︎
Azure SQL Databaseに対してBULK INSERTを実行する方法 ↩︎
「コードページ 65001 (UTF-8 エンコード) がサポートされません。」に対処しないといけない。UTF-8 形式のテキストファイルを BULK INSERT で一括取り込みする( SQL Server )

↩︎