1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

ZOZOAdvent Calendar 2023

Day 24

SpreadSheet x BigQuery 外部テーブル参照型エラー事件(date編)

Last updated at Posted at 2023-12-23

はじめに

これはSpreadSheetをBigQueryから外部テーブル参照した時に
安直にSpreadSheetを触って、それが起因でどん詰まったお話です。
ほぼSpreadSheetの話なのでご了承ください。

やりたかったこと

SpreadSheetの日付列start_dateを、BigQueryから外部テーブルとして参照しました。

スクリーンショット 2023-12-14 0.15.09.png

BigQueryのテーブルスキーマはこんな感じ
スクリーンショット 2023-12-14 0.17.03.png

selectしてちゃんと値とれました。

select * from `{project}.{dataset}.test_table`

/*
2023-01-01
2023-01-13
*/

事件発生

ここから、ちょっとSpreadSheetを触りました。

スクリーンショット 2023-12-14 0.19.29.png

再びselectをすると。。。

select * from `{project}.{dataset}.test_table`

/*
Error while reading table: {project}.{dataset}.test_table, error message: Could not convert value to date. Error: Invalid date: '2023/01/01'. Row 1; Col 0. File: {spreadSheetのid}
*/

目に見えるものが真実とは限らない的な?
見た目は子供頭脳は大人的な?
一体何が起こったのでしょう。

調査

ちょっとSpreadSheetを触った

何をやったかというと、表示形式を数字の"自動"にチェックをいれたのです。

スクリーンショット 2023-12-14 0.23.34.png

編集履歴を見ると、なにかがかわった跡がありますね。
よく見ると、触った時にセル表示が右寄りから左寄りに変わってるので何かが変わったのは間違いなさそうです。

スクリーンショット 2023-12-14 0.24.36.png

型を調べてみます。

スクリーンショット 2023-12-14 0.27.00.png

2なので、どうやらテキスト型にされてしまったのが原因のようです。

試行錯誤

では、一度列を日付形式に戻してみましょう。

スクリーンショット 2023-12-14 0.32.56.png

select * from `{project}.{dataset}.test_table`

/*
2023-01-01
2023-01-13
null
null
.
.
.
null
(999件)
*/

selectはできるようになりましたが、大量のnullが発生しました。

どうやらSpreadSsheetのデフォルトの1000行表示分をselectしており、列全体に対して操作したことが起因してる模様です。SpreadSheetの行をふやすとその分レコード数増えました。

スクリーンショット 2023-12-23 15.29.04.png

どうしたらnullを消せるのでしょうか。もう一度自動を試します。

スクリーンショット 2023-12-14 0.37.03.png

もはや日付でもなくなってしまいました。

スクリーンショット 2023-12-14 0.37.12.png

どうやらシリアル値とやらに変換されてしまった模様です。

さて、じゃあシリアルになった時にBigQuery側はといいますと。。

select * from `{project}.{dataset}.test_table`

/*
Error while reading table: {project}.{dataset}.test_table, error message: Could not convert value to date. Row 1; Col 0. File: {spreadSheetのid}
*/

数値ですのでまた変換できなくなりました。詰んだ?

自動やめるお

対象のセルだけ選んでもっかい日付フォーマットにしてみます。

スクリーンショット 2023-12-14 0.48.59.png

見た目は戻りました。

スクリーンショット 2023-12-14 0.15.09.png

そしてBigQueryは....

select * from `{project}.{dataset}.test_table`

/*
2023-01-01
2023-01-13
*/

戻りました。
危うく"自動"に刺されるところでした。
なんとも腑に落ちない事件ですが警部、解決です。

え?表示形式いじらずに、そもそもセルの値書き直した方が早かったんじゃないか?

・・・・・

クリスマスなんでケーキでも買ってきますか。
え?あんぱんと牛乳?好きですねえ。

(date編 完)

わかったこと

  • 日付フォーマットは、安易に表示形式を自動にすると見た目は変わらなくても実体を勝手に変えられることがある
  • 列単位で表示形式をいじると、列全体にnull含めて外部テーブルとしてはmax行レコードが存在する扱いにされることがある
  • 値がある対象のセルだけ表示形式を操作すると対象のセルだけselectに戻ることがある
  • 日付(DATEVALUE)をさらに安直に表示形式を自動にすると、シリアル(数値)に変換されることがある

まとめ

SpreadSheetをBigQueryから外部テーブル参照する場合、"表示形式"の"自動"はむやみに触るべからず。見た目同じでも勝手に内部で値(型なのかフォーマットなのか)が変えられてBigQueryの指定した型と不整合が発生する可能性があるお。

1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?