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

やってみること

AzureでSQLServer/SQLDatabaseの構築。
郵便番号の住所データを入れる。
入れたデータから住所のサンプルデータとしてランダムに10件取得する。

背景

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

前提条件

Microsoftのアカウント取得済み　→未取得の場合はこちら
Azureのサブスクリプションを導入済み　→未取得の場合はこちら
SQLServerManagementStudioをインストール済み →未取得の場合はこちら

実行環境

Windows10
Microsoft Edge 17
SSMS v17.3

Azure上にSQLServerを作成

ResourceGroupがなければcreate newで。
名前に迷ったら： Azure リソースの名前付け規則 | Microsoft Docs
Select SourceはデフォルトのBlank Databaseで。SampleでもOK。

Server nameはAzure全体で名前がかぶらなければOK。
Locationは画面だとWest-Europeですが、当然Japanがおすすめ。
※MSDN特典クレジットは日本リージョンが選べない。
あとはデフォルトで作成。

Pricing tireはデフォルトのS0(10 DTUs, 250GB)でよい。
Locationにもよるが、Basicだと動作が重い気がする。
Standardシリーズでつくって重いと感じたらDTU上げればよい。
DTUって何？：Azure SQL Databaseの適切な価格レベル(DTU)を選択する指標について

Collationは注意。今回やりたいのは住所データの投入なので、辞書順でかつできるだけ細かく区別できる照合順序が望ましい。
というわけで「Japanese_XJIS_140_CS_AS_KS_WS_VSS」を指定。
照合順序って何？：照合順序 – 文字の比較と並び順 (その 1)

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

できました。

データベースオブジェクトを作成

SQLServer接続

SQL Server Management Studioを立ち上げて作成したSQLServerへ接続。

初回アクセス時は下記のようなFirewall設定の表示が出る。
Azureポータル側でも設定可能だが、ここでSignInして設定する。

テーブルの作成

郵便番号データの説明を元にテーブル定義を決める。
※旧郵便番号は別にデータとして要らないし現郵便番号からLEFT()でとれるので列には加えない。


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

住所データを入れてみる

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

CSVをダウンロード

こちら
今回使用するのは「読み仮名データの促音・拗音を小書きで表記するもの」の「全国一括」版。

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

確認してみる

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

実行結果
124231

「T_JP_ZIPCODE」テーブルへINSERT

--全件登録
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

drop table [dbo].[KEN_ALL]

サンプルデータの作成

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

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

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

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

実行結果

以上。