BigQuery:加载日期列数据类型为长时间转换为时间戳的avro文件

Question

我很难让BigQuery正确地从avro文件中加载时间戳。

avro-文件的日期列存储的长度相同，具有逻辑类型的时间戳-micros。根据文档，BigQuery应该将其存储为时间戳数据类型。我也尝试了时间戳-millis的逻辑类型。

数据存储在avro中，如下所示：

{'id': '<masked>', '<masked>': '<masked>', 'tm': 1553990400000, '<masked>': <masked>, '<masked>': <masked>, 'created': 1597056958864}

字段tm和已创建的字段分别为长、2019-03-31T00:00:00Z和2020-08-10T11:50:58.986816592Z。

avro的架构是

{"type":"record","name":"SomeMessage","namespace":"com.df",
"fields":
[{"name":"id","type":"string"},
{"name":"<masked>","type":"string"},
{"name":"tm","type":"long","logicalType":"timestamp-micros"},
{"name":"<masked>","type":"int"},
{"name":"<masked>","type":"float"},
{"name":"created","type":"long","logicalType":"timestamp-micros"}]}";

当通过bq加载导入到BigQuery时，记录结果如下：

<masked>   <masked>   tm                            <masked>   <masked>  created
________________________________________________________________________________________________________
<masked> | <masked> | 1970-01-18 23:39:50.400 UTC | <masked> | <masked> | 1970-01-19 11:37:36.958864 UTC
________________________________________________________________________________________________________

使用的import命令是：

bq load --source_format=AVRO --use_avro_logical_types some_dataset.some_table "gs://some-bucket/some.avro"

BigQuery中的时间戳与avro中提供的实际值相差甚远。

有谁对如何做好这件事有什么想法吗？

Answer 1

我发现avro模式实际上是错误的。时间戳字段应该如下所示：

{"name":"created","type":{"type":"long", "logicalType":"timestamp-millis"}}