集めて共有！Snowflakeで全国犯罪発生情報をシェアするデータベースを構築する

さて、本日私が共有するのは全国版の犯罪発生情報です。

2020.10.22

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

こんにちは。データアナリティクス事業本部ソリューション部プリセールススペシャリストの兼本です。

弊社たまちゃんの奈良県内の犯罪発生情報を利用したエントリを読んで、なんで奈良限定なんだろうと思ったら、犯罪発生情報は各都道府県警察が管轄内のデータを個別に公開しているようです。

都道府県を跨いでデータを閲覧したい場合はそれぞれの都道府県からデータを集めて集約しないといけないので、これは少しもったいない気がします。

というわけで、各都道府県のデータを集めて集約し、それをSnowflakeのデータ共有の仕組み（Secure Data Sharing）を使って共有してみました。

公開サイトが都道府県ごとに違うのでデータを探すのが大変でしたが、以下は各都道府県の犯罪発生情報のダウンロードサイトもしくはページへのリンクです。（2020年10月20日時点での情報です。）

都道府県コード	都道府県	2019	2018	備考
1	北海道	〇	〇
2	青森県	〇	〇
3	岩手県	〇	〇
4	宮城県	〇	〇
5	秋田県	〇	〇
6	山形県	〇	〇	ファイル名に県名のついてないものがあります。
7	福島県	〇	〇

都道府県コード	都道府県	2019	2018	備考
8	茨城県	〇	〇
9	栃木県	〇	〇	サイト内検索で「犯罪発生情報」を検索します。
10	群馬県	〇	〇
11	埼玉県	〇	〇
12	千葉県	〇	〇	ファイル名が数字のみで識別できないので注意が必要です。
13	東京都	〇	〇
14	神奈川県	〇	〇

都道府県コード	都道府県	2019	2018	備考
15	新潟県	〇	〇
16	富山県	〇	〇
17	石川県	〇	〇
17	福井県	〇	〇
19	山梨県	〇	〇
20	長野県	〇	〇
21	岐阜県	〇	〇
22	静岡県	〇	〇	テキストファイル（.txt）ファイルとしてダウンロードされます。
23	愛知県	〇	〇

都道府県コード	都道府県	2019	2018	備考
24	三重県	〇	〇
25	滋賀県	〇	〇
26	京都府	〇	〇	ファイル名が数字のみで識別できないので注意が必要です。
27	大阪府	〇	〇
28	兵庫県	〇	〇
29	奈良県	〇	〇
30	和歌山県	〇	〇

都道府県コード	都道府県	2019	2018	備考
31	鳥取県	〇	〇
32	島根県	〇	〇
33	岡山県	〇	〇
34	広島県	〇	〇	ファイル名が数字のみで識別できないので注意が必要です。
35	山口県	〇	〇
36	徳島県	〇	〇
37	香川県	〇	〇
38	愛媛県	〇	〇
39	高知県	〇	〇

都道府県コード	都道府県	2019	2018
40	福岡県	〇	〇
41	佐賀県	〇	〇
42	長崎県	〇	〇
43	熊本県	〇	〇
44	大分県	〇	〇
45	宮崎県	〇	〇
46	鹿児島県	〇	〇
47	沖縄県	〇	〇

47都道府県すべての犯罪発生件数データがオープンデータとしてクリエイティブコモンズ4.0ライセンスに基づいて公開されているとか、ひと昔前なら考えられなかったですね。

データの前処理とロード

ダウンロードしたCSVファイルを確認したところ、都道府県ごとにファイルの命名規則やデータフォーマットに若干の個性があったので、Alteryxで軽く前処理をしたうえで、Snowflakeにバルクロードします。

今回作成したワークフローはこちら。

あまり複雑な処理はしていませんが、ファイル内のデータフォーマットに若干の違いがあったので、データ読み込み部分はバッチマクロで対処しています。

Snowflake上に作成したテーブルをデータシェアリングします。データシェアはデータベースごとに行いますが、共有する際に別のデータベース名を設定でき、公開するテーブルやビューも選択できるので、わざわざ共有のためだけに別のデータベースを構築する必要はありません。

なお、データ共有の処理を実行するには、ACCOUNTADMINロール（またはCREATE SHARES グローバル権限が付与されたロール）を使用する必要があります。

コンソールで共有ページを開き、「作成」ボタンを押下します。
共有データベースの名称を入力し、元になるデータベースをリストから選択します。
公開するテーブルとビューをリストから選択します。今回は「crime_case」という名称のテーブルをひとつだけ公開することとします。
設定内容の確認をし、問題なければ次へ進みます。
ここでは、公開するデータに間違いがないかをプレビューで確認できます。
最後にこのデータを公開する相手（コンシューマー）を指定します。この作業はスキップして後で実施することも可能です。
今回は検証用に作成した別アカウントをコンシューマに設定して、このユーザにデータを共有します。なお、Data Sharingを行うにはそれぞれのアカウントが同じクラウドサービスかつ同じリージョンである必要があります。
データ共有の設定はこれで終了です。本当に数クリックで作業が完了してしまいます。

コンシューマとして設定できるのは、Snowflakeのライセンスを所有しているアカウントのみですが、リーダーアカウントを設定するとSnowflakeのライセンスを持っていないユーザに対してデータを共有することも可能です。

リーダーアカウントについては以下のエントリをご参照ください。

データの共有を受けたアカウントでSnowflakeにアクセスします。

ACCOUNTADMINロールでコンソールにアクセスして共有ページを開くと、プロバイダーから共有されたデータベースがリストされているので、利用したいデータベースを選択して「安全な共有からデータベースを作成」ボタンを押下します。
アカウント内でのデータベース名を入力し、あくっすを許可するロールを設定します。
これで共有されたデータを利用する準備ができました。
SQLワークブックからデータベースにアクセスし、無事にデータを取得することができました。