DB

BigQueryでスプレッドシートのDateTimeを読み込めない原因

BigQueryでスプレッドシートに記入した日時を読もうとしたらエラーになったんだけど…

データの分析や処理に便利なBigQuery(BQ)

大規模データの処理に優れており、テーブルに保存した大きなデータでも短時間で集計することができる。

そしてデータソースとしてスプレッドシートを指定することもできる。


しかしスプレッドシートに対してSELECTを行うとこのようなメッセージが。

Error while reading table: test.datetime_sample, error message: 
Could not convert value to datetime. Row 1; Col 0.

datetime関連のエラーのようだが、どう修正すればいいのか

そこで今回はBigQueryでスプレッドシートのDateTimeを読み込めない原因について紹介する。

この記事を書いている人


システムエンジニア、AIエンジニアと、IT業界で10年以上働いている中堅。PythonとSQLが得意。

記事を読むメリット

BigQueryでスプレッドシートのDateTimeを読み込めない原因がわかり、データ処理で躓かなくなる


データ準備

まずはデータの準備だが、今回はスプレッドシートを使う。


step
1
スプレッドシート準備

スプレッドシートを作成するには、Googleドライブの「新規」ボタンをクリックする。


Googleドライブ 新規


すると新規作成するファイルのメニューが表示されるので「Googleスプレッドシート」をクリックしてスプレッドシートを作成する。


Googleドライブ スプレッドシート


そして新しく開かれたブラウザ上でExcelのようにデータを入力する。


スプレッドシート

今回は日時項目を取り込むので、以下の入力を行った。

2021/09/08 20:0:00


step
2
テーブル作成

次にテーブルを作成する。

テーブルを作るにはGCPのBigqueryのSQLワークスペース上で、データセット(この場合は"test")の右側の三点リーダ から open を選んで右側のスペースにデータセットを表示させる。

そしてデータセットが表示されているエリアの右側に「CREATE TABLE」とあるのでそれをクリックする


SQLワークスペース CREATE TABLE


すると右から詳細設定画面がせり出してくるので、詳細を入力する。

ここで設定しているのは、以下の通り。

パラメータ
Source Drive
Select Drive URL (スプレッドシートのブラウザに表示されているURL)
Schema -
Name (任意)
Type DATETIME
Advanced options -
Header rows to skip 1



これで以下のようにスプレッドシートをデータソースとしたテーブルができる。

ただしこれはスプレッドシートの中身を取り込んだテーブルではなく、スプレッドシートを参照するテーブル

SELECTの度にスプレッドシートが読まれることに注意しよう。




SELECTしてみる

では作ったテーブルをSELECTしてみる。

SELECT * FROM test.datetime_sample

すると以下のようになる。


エラーになっており、エラーメッセージは以下のように表示される。

Error while reading table: test.datetime_sample, error message: 
Could not convert value to datetime. Row 1; Col 0.

訳すなら「0番目のカラムの1行目の値をdatetimeに変換できませんでした。」といったところ。

しかしスプレッドシート上に入力した値はカレンダーが表示されるくらい日付フォーマットに則っている


スプレッドシート


これをどうすればdatetimeとして認識するのか。



日付を文字列にしてみる

スプレッドシート上の日付をdatetimeとして認識しないなら、文字列だとどうか。

スプシで値を文字列にするにはExcelと同様に頭に ' を付ける。


日付を文字列にしてみる


そしてSELECTを実行


はい、エラー。

エラーメッセージはこんな感じ。

Error while reading table: theta-bliss-258006.test.datetime_sample, 
error message: Could not convert value to datetime. 
Error: Invalid datetime string "2021/09/08 20:00:00". Row 1; Col 0.

「datetimeに変換できない」と言っているのは同じだが、入力値を 2021/09/08 20:00:00 と認識できている。



文字列の日付書式を変えてみる

では日付の書式を変えたらどうだろうか。

日付書式の区切りを / から - に変えてみる。

2021-09-08 20:00:00


これでSELECTすると


はい、成功!

YYYY/MM/DD hh:mm:ss 形式ではなく YYYY-MM-DD hh:mm:ss 形式ならdatetimeとして変換できた。



まとめ

今回はBigQueryでスプレッドシートのDateTimeを読み込めない原因について紹介した。

BigQueryでスプレッドシートのDateTimeを読み込めない原因、それは日付の書式

スプレッドシート上で日付形式ではdatetimeに変換できずにエラーになる。


datetime項目としてselectするとエラーになる例

頭に ' を付けて文字列にした上で、YYYY-MM-DD hh:mm:ss 形式にするとBigQueryでSELECTした際にdatetime項目として認識できる。


datetime項目としてselectできる例


現象はわかったけど納得しづらいなぁ
スプシ上で日付として認識されてるんだからBQに取り込む際もdatetime変換できてほしいですね


他にもBigQueryに関する記事もあるので、興味があれば見てみて欲しい。

ITipsと同じようなブログを作る方法

ブログに興味がありますか?

もしブログに興味がある場合は↓このページ↓を参考にすれば、ITipsと同じ構成でブログを作ることができます

サーバー、ドメイン、ASPと【ブログに必要なものは全て】このページに書きました。
同じ構成でブログ作るのはいいけど、記事はマネしないでネ (TДT;)

ランキング参加中

にほんブログ村 IT技術ブログへ

他にもブログやSNSで紹介してくれると励みになります。

はてブのコメントで酷評されると泣きます(´;ω;`)

-DB
-,

Translate »

© 2021 ITips