🤟

BigQuery触って気づいた事::データインポートのエラー

2023/03/23に公開

Google

Cloud

tech

はじめに・・

1月から触り始めたGoogle Cloud。
その中でもBigQueryはGoogle Cloudのキラーコンテンツだと個人的に思っています

市場の状況を見ていると、AI系とデータ分析系をちゃんと触っておけば、エンジニアのキャリア的には”外さない”だろうと予想。

以上を踏まえて最近はBigQueryのハンズオンを行い気づいた事をまとめて参ります。

パブリックなデータセットを触ってみる

BigQueryで分析など行う際。肝心のデータが無いと何もできません。

INSERT文などでしこしこデータを追加するのも良いのですが、INSERTするのが目的ではなく、分析するのが主目的です。

「ならパブリックなデータセットがあるから、それ使えばいいじゃない！」と考えて、Googleが公開しているデータセットを利用することにしました。

今回はCovid-19な公開データセットがありましたので、それを利用。

簡単にデータセットとして追加が出来ました。

あとはココからSQL文をかいて実行すれば良い算段ですね。
試しに適当なテーブルのレコード数をカウントしてみます。

SELECT count(date) FROM `bigquery-public-data.covid19_open_data.covid19_open_data` LIMIT 1000

うん！すぐ集計してくれました。

ここまでSQLサーバーの構築など全く無しに、実行出来るのがBigQueryのメリットだと再確認できました。

パブリックではなく、プライベートなデータを分析したい

業務で分析を行う際、ほとんどの方は業務利用なデータを分析されたいですよね？

パブリックで公開されているデータを分析するのは、シチュエーション的には非常にすくないはずです。プライベートなデータを分析し、分析結果をビジネスに反映させるのが主目的なはずです。

以上を踏まえて・・

「なんか個人のデータをBigQueryにテーブルとしてインポートして分析したいな」と考えて、実際にトライしてみることにしました。

データはアクセスログを利用

じゃぁどのデータを分析に利用するか？
すぐ思いついのは、自ら運営公開している独自ドメインブログのアクセスログの分析。

実は2010年から以下ブログを作成し、現在もコンテンツ公開しているんですね。

https://netply.net/

上記ブログのアクセスログをBigQueryにインポートし、分析できるかをトライしてみることにしました。

ホスティング先からアクセスログデータを取得

上記個人ブログで利用しているホスティング先はMixhostを利用しています。
MixhostではcPanelにて各種管理業務が可能なんですが、アクセスログもブラウザのコンソールから確認が出来ます。

でもアクセスログのダウンロードが出来ない。
ROWデータとして取得できないかを調べてみるとありました。

ROWデータがダウンロードできたので、それをBigQueryに使えないか試すことにします。

アクセスログデータの変換

上記で取得したアクセスログ。
スペース区切りのデータ形式で、BigQueryで読み込む際の形式には合いません。

GCSに保存して、何かCSV形式に変換できないかと調べますが何を使ったら良いのか判らず。仕方が無いのでローカルのMacに入れているPythonで、スペース区切りをカンマ区切りに変更することにしました。

mkcsv.py

import re
import sys
pattern=re.compile(' +')
ifile=sys.argv[1]
ofile=open('c'+ifile.split('.')[0]+'.csv','tw')
with open(ifile,'tr') as fin:
    for iline in fin:
        ofile.write(pattern.sub(',',iline))
    ofile.close()

作成したスクリプトで元データからcsvに変換します。

python3 mkcsv.py ダウンロードしたスペース区切りのアクセスログデータ.txt

BigQueryで読み込みエラーが発生

CSVデータが準備できたので、BigQueryにインポート作業です。

スキーマについても自動検出するように指定し、テーブルとしてインポートを試みます。

しかしインポート時のジョブとしてエラーメッセージが出てきました。

もっと細かい情報など探せないかな？と考えてエラーログを確認出来るサービスが無いか調べてみると、Cloud Loggingなるサービスを発見しました。

Google Cloud Loggingではエラー内容を俯瞰で確認することをこの時初めて知って一人ちょっと感動していまいました。

一応当該エラーが見つかったので見てみると、スキーマ関係のエラーなんだろうなと判断。

じゃぁスキーマ判別を自動検出ではなく、手動でしてみようかと考えて一つずつ指定することにしました。

しかしここで問題が発生。

スキーマを手作業で指定していく際に、数が多くなるとブラウザが頻繁にチラツキ正常に表示が出来なくなりました。

キャッシュ削除したり、ブラウザを変更しても同様な症状が出ちゃうので、ブラウザコンソールでスキーマ指定するのを諦めて別の方法を採用することにしました。

CloudShellでインポート作業しちゃう

ブラウザコンソール使えないなら、CLIでやればいいじゃない！

そう考えてドキュメントを探してみると、bqコマンドでスキーマ定義およびデータ読み込みする方法がありました。

ちなみにエバンジェリストのG-gen 又吉さんがブログにも同じような事を紹介していたので、めちゃ参考になりました。

CloudShellを開いてスキーマ設定ファイルや、CSVデータを保存しCLIでスキーマ設定やインポート処理を実行します。

bq mk --table 保存するテーブルを作成指定 schema.json（スキーマ設定のjsonファイル）

bq --location=asia-northeast1 load --source_format=CSV --skip_leading_rows=1 保存するテーブル名指定 読み込ませたいアクセスログデータ

結論からいうとテーブルの作成は出来ました。しかしそのテーブルにアクセスログデータのインポートは出来ませんでした。

よくよくアクセスログデータを精査してみると、カラム数がレコードによって異なっている事が判明し、そこで萎えてしまいました。

アクセスログデータの整形をすればよいのかもしれませんが、量が4161765件と多いのでデータクレンジングは今回は早々に諦めることにしました。

煮え切らないからオープンデータを使ってみる

自前のサイトのアクセスログデータの整形が面倒なら、整っているであろうオープンデータを使うことにしました。

今回は政府統計の総合窓口、e-Statから「男女別人口－全国，都道府県（大正９年～平成27年）」を取得し使うことにしました。

まぁ件数が少ないから整形するのも楽だろうと考えて、インポートしてみると。。

＼＼スキーマ情報がほぼ意味不明な状態／／

またスキーマを手動で指定すれば解決するであろうと理解はしたのですが、ここまで来ると勉強の理解を深めるフェーズじゃなくて、作業モードになってしまったので諦めてハンズオン学習を終了することにしました。

安西先生。。諦めてごめんなさい。

まとめ

実際にリアルなデータを元にBigQueryで集計とか分析しようと考えて、今回も触って学習をしていったのですが解ったことを以下にまとめます。

分析集計したい元データのクレンジングって大事
スキーマ情報をしっかり判別理解していないと、肝心の集計業務にも影響でそう
BigQueryのブラウザコンソール画面ではスキーマ指定は多くなるとバグる
エラー情報はGoogle Cloud Loggingで探せる
bqコマンドなどCLIツールでも、テーブルの作成やデータインポートができる

以上になります。

来週あたりは、データのクレンジング方法について調べてみて、またTwitterLiveでアウトプットしてみます！