📹

BigQueryへデータインポートするためにデータ整理した話

2023/04/12に公開

Google

Cloud

tech

はじめに・・

前回、運営しているブログサイトのアクセスログをBigQueryでインポートするのに四苦八苦し、結局インポートが正常に出来ませんでした。

今回は、インポートするためにデータを綺麗に整理し無事にインポート出来たお話です。実際に整理した手順や疑問点、インポート後に気づいたことなどを備忘録的に以下まとめていきます。

前回の状況

ブログを運用しているレンタルサーバーのサイト、mixhostから生データを取得しCSV形式に変換。そのデータをBigQueryにインポートしようとしてエラーメッセージが多発するという内容でした。

エラー内容↓

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

ちなみにレコード件数は4161765件でしたね(;_;)

データの整理をすることにした

前回の勉強で、データインポートのエラーが発生したのですが、よく調べてみると以下の問題があることが判明しました。

カラム内容がレコードによって変動している。
一部のスキーマ内容が列分割している。
スキーマ情報の一部が2バイト文字で設定されている。
不要なデータもあるの

それぞれ修正対応する必要があるのですが、今回は件数も少なかったのでGoogleスプレッドシートで作業をすることにしました。

分割されているスキーマ内容を合体

おそらくPythonでスペース区切りの内容を、カンマ区切りに変更する際に分割されたんだと思われます。

仕方ないのでGoogleスプレッドシートの関数CONCATENATE関数を使って文字列を合体して、別の列に投入することにしました。

必要な情報のみに絞り、不要な列などは削除した

カラム内容に変動がありましたので、必要な列のみを残すように精査。
また「これいらんだろう！」って情報はばっさり捨てる事にしました。

その結果、シンプルで必要な情報のみが残るようになりました。

１行目にカラム名を設定した

BigQueryにインポートする際に、カラム名を手動で設定する際。スキーマ情報が多すぎると画面がチラツキます。

それを踏まえてデータの１行目にはAscii文字で、各カラム名を指定しました。

これでインポート後に名前も自動で入ってくれるな。よしよし！

実際にデータをBigQueryにインポートしてみる

Googleスプレッドシートでの編集作業も終わったので、BigQueryへのインポートです。

Googleドライブの保存場所を指定し読み込みます！

すると前回はエラー多発でインポートできなかったのが、すんなりBigQueryにインポートできました。

大歓喜＼(^o^)／
初歩的かもしれませんが、勉強始めたばかりなのでこれだけでも嬉しいんですよ♪

ここで気づいた事。

Googleスプレッドシートで整形作業を行い、BigQueryにインポートしましたがここでいくつか気づいた事があります。

データが多すぎるとGoogleスプレッドシートでは出来ないだろう。

40MB程度のテキストファイルだったので、今回はデータ整理などは出来ました。でも大容量のデータだとそもそもGoogleスプレッドシートでもファイルオープンすることさえ出来ないだろうと見込んでます。

「じゃぁGoogle CloudでBigQueryにインポートする際にそういう整形するツールってあるのだろうか？」と思い、次回の宿題にすることにしました。

インポート作業後に気づいた事。

無事にBigQueryにインポート出来たのですが、ちょっとクエリー実行してみるとあまりに遅いことに気づきました。

たかだかレコード件数数えるだけなのに、30秒以上も時間を要しています。

うん！31秒。

20万件ほどのデータなのになぜこんなに遅いのか？

これは遅すぎる！なにか問題があるのではないか？と思い調べて見ることにしました。

テーブル情報を調べてみると外部参照していた！

今回、GoogleドライブにあるGoogleスプレッドシートのデータをインポートしました。

その場合ってデータとしてはBigQuery内に格納されるのではなく、どうやらGoogleドライブにデータは残りBigQueryでは外部データってことで参照している様子でした。

なるほどね。。だからクエリ実行が遅かったのか。。

ちなみにCSVファイルとしてローカルPCからアップロードする形でBigQueryに読み込みし、別テーブルとして格納してみると、クエリもいつものBigQueryよろしくな激速でした！

今度は中身のデータ整理が必要

インポートは出来ました。でも課題はあります。

空白の値（欠損値）はどう対処するのか？
突出した値（特にデータ転送量の部分）はどうするのか？
型が異なっている場合はどうするのか？

このあたりの課題を対策する必要がありますね。
大容量のデータインポートする時の対応と合わせて、今後の勉強項目ですね。

まとめ

今回の学習内容のまとめです。

データの整理をすればインポートはできる
どのような整理作業が必要なのか → 人の判断が必要になる
- 整理作業はツール使う必要があるだろうなと予測してます。
インポートした後のデータってGoogleドライブへの外部参照もあるんだ！
インポート後のデータ中身の整理も必要

前回はインポート作業が出来ませんでしたが、今回は出来ました。
ただ課題もあることが解ったので次に反映したいと考えています。

以上になります。

次回も、データ整形やインポートを行う際のツール等について調べてみて、またTwitterLiveでアウトプットしてみます！