[Azure SQL Database]Azureで遊んでみる ~ 住所のダミーデータ作成

## やってみること
- AzureでSQLServer/SQLDatabaseの構築。
- [郵便番号の住所データ](https://www.post.japanpost.jp/zipcode/dl/kogaki-zip.html)を入れる。
- 入れたデータから住所のサンプルデータとしてランダムに10件取得する。

## 背景
- Azureでサブスクリプションとったけど、何ができるのかいまいちわからない。
- とりあえずAzureでなんかやってみたい。
- Oracle畑で育ったけど、Databaseならなんとなくわかるはず。


## 前提条件
- Microsoftのアカウント取得済み　→未取得の場合は[こちら](https://account.microsoft.com/)
- Azureのサブスクリプションを導入済み　→未取得の場合は[こちら](https://azure.microsoft.com/ja-jp/free/)
- SQLServerManagementStudioをインストール済み →未取得の場合は[こちら](https://docs.microsoft.com/ja-jp/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017)

## 実行環境
- Windows10
- Microsoft Edge 17
- SSMS v17.3

## Azure上にSQLServerを作成
![image.png](https://qiita-image-store.s3.amazonaws.com/0/94824/0f1fde1a-c5e8-cf11-b357-61d4f550b9ab.png)
ResourceGroupがなければcreate newで。
名前に迷ったら： [Azure リソースの名前付け規則 | Microsoft Docs](https://docs.microsoft.com/ja-jp/azure/architecture/best-practices/naming-conventions)
Select SourceはデフォルトのBlank Databaseで。SampleでもOK。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/94824/493b3c28-032a-afd1-9be9-0cf91de0d462.png)
Server nameはAzure全体で名前がかぶらなければOK。
Locationは画面だとWest-Europeですが、当然Japanがおすすめ。
※MSDN特典クレジットは日本リージョンが選べない。
あとはデフォルトで作成。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/94824/877174b1-890b-d74b-33a8-9ef0cedbb629.png)
Pricing tireはデフォルトのS0(10 DTUs, 250GB)でよい。
Locationにもよるが、Basicだと動作が重い気がする。
Standardシリーズでつくって重いと感じたらDTU上げればよい。
DTUって何？：[Azure SQL Databaseの適切な価格レベル(DTU)を選択する指標について](https://blogs.msdn.microsoft.com/jpsql/2017/09/06/azure-sql-database-dtu/)

![image.png](https://qiita-image-store.s3.amazonaws.com/0/94824/1f4e67b5-438f-40a2-a25c-309f18c61c7b.png)
Collationは注意。今回やりたいのは住所データの投入なので、辞書順でかつできるだけ細かく区別できる照合順序が望ましい。
というわけで「Japanese_XJIS_140_CS_AS_KS_WS_VSS」を指定。
照合順序って何？：[照合順序 – 文字の比較と並び順 (その 1)](https://blogs.msdn.microsoft.com/jpsql/2016/07/26/1-3/)

一通り設定できたのでCreate。
デプロイには数分かかるのでコーヒーでも飲んで待ちます。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/94824/a1d49009-40d1-8679-2c5e-03481cbeebfe.png)
できました。

## データベースオブジェクトを作成
### SQLServer接続
SQL Server Management Studioを立ち上げて作成したSQLServerへ接続。

<img width="600" src="https://qiita-image-store.s3.amazonaws.com/0/94824/df2f3451-9baa-1121-586d-25cf6b07ecc3.png">
初回アクセス時は下記のようなFirewall設定の表示が出る。
[Azureポータル側でも設定可能](https://aspnet-mvc.clock-up.jp/contents/azure/sqlserver-firewall)だが、ここでSignInして設定する。
<img width="400" src="https://qiita-image-store.s3.amazonaws.com/0/94824/40df54d0-de81-84a4-9e5a-382aaddbdfe4.png">

### テーブルの作成
[郵便番号データの説明](https://www.post.japanpost.jp/zipcode/dl/readme.html)を元にテーブル定義を決める。
※旧郵便番号は別にデータとして要らないし現郵便番号からLEFT()でとれるので列には加えない。

```sql

CREATE TABLE [dbo].[T_JP_ZIPCODE](
	[address_id] [int] IDENTITY(1,1) NOT NULL,
	[japan_local_public_org_code] [varchar](5) NULL,
	[zip_code] [varchar](7) NULL,
	[prefectures_kana] [varchar](8) NULL,
	[city_kana] [varchar](64) NULL,
	[town_kana] [varchar](128) NULL,
	[prefectures_name] [varchar](8) NULL,
	[city_name] [varchar](64) NULL,
	[town_name] [varchar](128) NULL,
	[town_duplicate_zip_flag] [bit] NOT NULL DEFAULT 0,
	[exists_koaza_by_zip_flag] [bit] NOT NULL DEFAULT 0,
	[exists_chome_flag] [bit] NOT NULL DEFAULT 0,
	[zip_duplicate_town_flag] [bit] NOT NULL DEFAULT 0,
	[update_note] [tinyint] NOT NULL DEFAULT 0,
	[update_reason] [tinyint] NOT NULL DEFAULT 0,
PRIMARY KEY CLUSTERED 
(
	[address_id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

```

![image.png](https://qiita-image-store.s3.amazonaws.com/0/94824/d01927cb-013b-9422-d28a-bec5e7ccd2f0.png)


## 住所データを入れてみる
いよいよデータを投入。
手順は下記の通り。
1. CSVをダウンロード。
2. インポート機能を使ってCSVデータ投入。
3. 手順2で入れたデータを上記で作成した「T_JP_ZIPCODE」テーブルへINSERTする。
4. 手順2のテーブルをDROP。

### CSVをダウンロード
[こちら](https://www.post.japanpost.jp/zipcode/dl/kogaki-zip.html)
今回使用するのは「読み仮名データの促音・拗音を小書きで表記するもの」の「全国一括」版。

### インポート機能を使ってCSVデータ投入

![image.png](https://qiita-image-store.s3.amazonaws.com/0/94824/fedf30d8-2456-e017-baf9-9fdc7fb4d641.png)
![image.png](https://qiita-image-store.s3.amazonaws.com/0/94824/0d83fbee-961e-b0b2-c04c-b8f00d003c11.png)
![image.png](https://qiita-image-store.s3.amazonaws.com/0/94824/cee08e53-750a-c1b4-d2b5-e2566dc9f637.png)
![image.png](https://qiita-image-store.s3.amazonaws.com/0/94824/cc3b3e94-5cf8-f6fc-9766-2b7c73074a56.png)
![image.png](https://qiita-image-store.s3.amazonaws.com/0/94824/c773ea03-f950-8d3b-11d6-aad2f70e4977.png)
![image.png](https://qiita-image-store.s3.amazonaws.com/0/94824/5c7cbc9b-300f-4c3a-a39a-7549a3c84cef.png)
![image.png](https://qiita-image-store.s3.amazonaws.com/0/94824/83488bb8-6335-1586-1c68-36289c475bf0.png)

確認してみる

```sql
select count(1) from [dbo].[KEN_ALL]
```

実行結果
124231

### 「T_JP_ZIPCODE」テーブルへINSERT

```sql
--全件登録
INSERT INTO [dbo].[T_JP_ZIPCODE]
SELECT
       CONVERT(nvarchar(5),[Column 0]) [japan_local_public_org_code]
      --,[Column 1] [OLD_zip_code]
      ,CONVERT(nvarchar(7),[Column 2]) [zip_code]
      ,CONVERT(nvarchar(8),[Column 3]) [prefectures_kana]
      ,CONVERT(nvarchar(64),[Column 4]) [city_kana]
      ,CONVERT(nvarchar(128),[Column 5]) [town_kana]
      ,CONVERT(nvarchar(8),[Column 6]) [prefectures_name]
      ,CONVERT(nvarchar(64),[Column 7]) [city_name]
      ,CONVERT(nvarchar(128),[Column 8]) [town_name]
      ,CONVERT(bit,[Column 9]) [town_duplicate_zip_flag]
      ,CONVERT(bit,[Column 10]) [exists_koaza_by_zip_flag]
      ,CONVERT(bit,[Column 11]) [exists_chome_flag]
      ,CONVERT(bit,[Column 12]) [zip_duplicate_town_flag]
      ,CONVERT(tinyint,[Column 13]) [update_note]
      ,CONVERT(tinyint,[Column 14]) [update_reason]
  FROM [dbo].[KEN_ALL]

--確認
select count(1) from [dbo].[T_JP_ZIPCODE]

```

#### 実行結果
(124231 rows affected)

(1 row affected)
124231

### テーブル「KEN_ALL」をDROP

```sql
drop table [dbo].[KEN_ALL]
```


## サンプルデータの作成
データが揃ったので早速サンプルデータを抽出。
要件は下記の通り。

- ランダムに取得できる。
- 件数はとりあえず10件。
- 丁目番地以降も適当な数字で埋める。

※厳密には、丁目番地枝番などがある程度決められるが、どうせダミーなので適当な数値にする。

```sql
SELECT TOP 10
   [no]
  ,[zip_code]
  ,CONCAT([prefectures_name], [city_name], [town_name], LEFT(id,2),'-',RIGHT(id,1)+1) as [dammy_address]
  ,CONCAT([prefectures_name], [city_name], [town_name]) as [address_name]
  ,[prefectures_name]
  ,[city_name]
  ,[town_name]
FROM (
  SELECT 
    row_number() over(order by NEWID()) as no    -- ランダム取得のための並び替え
    ,CONVERT(bigint, CONVERT(VARBINARY(4), NEWID())) as id    --番地・枝番用の疑似乱数値
    ,SUBSTRING( [zip_code], 1, 3) + '-' + SUBSTRING( [zip_code], 4, 4) as [zip_code]    --ハイフンありの書式
    ,[prefectures_name]
    ,[city_name]
    ,CASE  -- 括弧書きの記述は削除する
       WHEN CHARINDEX('(',[town_name]) = 0 THEN [town_name]
       ELSE LEFT([town_name] , CHARINDEX('(',[town_name])-1 )
     END [town_name]
  FROM [dbo].[T_JP_ZIPCODE]
  WHERE [town_name] != '以下に掲載がない場合'
) T
```

#### 実行結果
<img width="800" src="https://qiita-image-store.s3.amazonaws.com/0/94824/5aa264a3-f014-bc09-b36e-6af4318b4bd5.png">

以上。