e-statのデータをAthenaで読み込む

6900 ワード
Athena e-Stat AWS AWS テキストリンク

データの準備

e-statからCSVファイルをダウンロードする

  1. e-stat APIの利用ガイドを参照して、アプリケーションIDを取得する
  2. API機能テストフォームの統計データ取得を使って、データを取得する。

テストフォームには以下の項目を入力します。取得できるのは、最大１０万行です。
- アプリケーションID
- 統計表ID（0003410379）
- データ形式（CSVを選択）
- セクションヘッダフラグ（出力しないを選択）

統計表IDは「男女別人口及び人口性比－全国，都道府県（大正9年～平成27年）」のものです。必要に応じて変更してください。
セクションヘッダフラグ[sectionHeaderFlg] を「出力しない」にしておくと、出力件数や調査名称などのメタデータが出力されず扱いやすくなります。「出力する」にすると、出力件数が１０万行を超えるとヘッダーの行数が増えるなど扱いづらくなります。

なお、e-statからは(APIではなく)DBからダウンロードすることでユーザ登録なしでCSVファイルをダウンロードできますが、文字コードがShift-JISのため、Athenaでは扱いづらいです。「引用符あり」「ヘッダーあり」「Shift-JIS」の組み合わせがAthenaと相性が良くないようです（こちらのサイトを参照）。
APIから取得した場合は、文字コードがUTF8なので問題なしです。

S3の作業

以降の作業はすべて同じリージョン内で行う前提です。
リージョンをまたぐとうまくいかないことがあるようです。

バケットの作成

AWS マネジメントコンソールからS3を選択　→　バケットを作成　→　任意のバケット名を入力し、（他はデフォルトのまま）バケットを作成

データのアップロード

上で作成しバケット名をクリックし、次の画面でファイルをドラッグ＆ドロップ　→　アップロード

Athenaの作業

AWS マネジメントコンソールからAthenaを選択　→　以下のクエリを入力し実行

データベースの作成

以下のクエリを実行してデータベースを作成します。

create database （データベース名）

テーブルの作成

次に、以下のクエリを実行してテーブルを作成します。

CREATE EXTERNAL TABLE IF NOT EXISTS
    データベース名.テーブル名(
        `tab_code` STRING,
        `表章項目` STRING,
        `cat01_code` STRING,
        `総数，男及び女_時系列` STRING,
        `area_code` STRING,
        `都道府県_時系列（人口集中地区有り一部の市）` STRING,
        `time_code` STRING,
        `時間軸（調査年）` STRING,
        `unit` STRING,
        `value` STRING,
        `annotation` STRING
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES('serialization.format' = ',', 'field.delim' = ',' ,'quoteChar' = '\"', 'escapeChar' = '\\')
    LOCATION 's3のURI'
    TBLPROPERTIES ('has_encrypted_data'='false', 'skip.header.line.count'='1')

  • テーブル名
    小文字で始める、アンダーバーは使えないなどの制約があるので、AWSのドキュメントを参照してください。

  • 列名
    e-statから出力されるものをそのまま使っています。
    データの型はすべてString（文字列）で読み込みます。これは、Value等の数値項目であっても、秘匿や非該当などの理由で「-（ハイフン）」などの記号が入ってあることがあり、Athenaで読み込む際にエラーになるためです。

  • Row Format Serde
    今回は'org.apache.hadoop.hive.serde2.OpenCSVSerde'を指定します。データ形式に応じて、前述のサイトなどを参照して適切なものを選びます。

  • SerdePorperties
    field.delimは列の区切り文字です。ここでは「,（カンマ）」を指定しています。
    quoteCharは項目についている引用符です。e-statデータはすべて「"（ダブルクォート）」で囲まれているので、「"」を指定します。

  • locationには、データを保存したS3のフォルダのURIを指定します。Athenaではファイルごとではなく、フォルダまるごとを同一形式のデータとして読み込みます。「s3://backetName/foldername/」といった形式になります（最初のsは小文字、最後のスラッシュもお忘れなく）。

  • TblPropertiesのskip.header.line.countには、スキップする先頭の行数を指定します。e-stat APIからダウンロードする際に、セクションヘッダフラグ[sectionHeaderFlg] を「出力しない」としている場合は、ヘッダー行は1行なので「1」を指定します。

テーブルの確認

クエリが正常に実行できれば、左側のテーブル一覧にテーブル名が追加されるので、メニューボタン（点３つ）から「テーブルをプレビュー」を選択します。

自動的にプレビューするクエリが実行され、中身を確認することができます。

以上で、e-statデータをAthenaから読み込むことができました。