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の度にスプレッドシートが読まれることに注意しよう。

参考

Google スプレッドシートで複雑な CSV ファイルを BigQuery に読み込む | Google Cloud Blog

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番目のカラムの１行目の値を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 と認識できている。