Response format for Timestamp type data is different from actual Big Query? about bigquery-emulator

[goccy]

Go's client library seems to expect float64 value as timestamp value ... 🤔
FYI: https://github.com/googleapis/google-cloud-go/blob/fee0f68b5005222a8bcf1f2c662d6587eac7bd0f/bigquery/value.go#L957-L966

[halnique]

It expects Unix Timestamp with microseconds format. e.g.) 1661864400.5699711
https://www.php.net/manual/en/datetime.formats.compound.php

The following client code causes an error.
https://github.com/googleapis/google-cloud-php-bigquery/blob/main/src/ValueMapper.php#L364

[goccy]

@exageraldo
BigQuery uses float representation when returning timestamp type value, but when sending timestamp type value from client to BigQuery, it must be in the following format
https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types

So, you should use representation like 2022-01-01T00:00:00+09:00 as timestamp value

[exageraldo]

Now it worked perfectly. Thanks a lot!

[goccy]

I see ! I also found that in the docs
https://cloud.google.com/bigquery/docs/reference/rest/v2/DataFormatOptions

[goccy]

Do you know what format php client expects for timestamp type ? e.g.) unixtime nano or RFC3339 etc

[goccy]

Thank you for your kind information !
This problem fixed with v0.1.9 !
Thanks

[halnique]

Thanks!

[exageraldo]

Hey, I'm trying to run it this way:

FROM ghcr.io/goccy/bigquery-emulator:0.1.9

COPY load-bigquery.yaml .

CMD ["bigquery-emulator", "--project=project-id", "--port=9050", "--database=saved-bigquery", "--data-from-yaml=load-bigquery.yaml"]

But I believe I'm facing the same problem initially reported here.

failed to convert value from {Name: Ordinal:2 Value:1.6618644e+09}: failed to convert TIMESTAMP from float64

load-bigquery.yam:

projects:
- id: project-id
  datasets:
    - id: dataset-one
      tables:
        - id: some-tamble
          columns:
            - name: id
              type: INT64

            - name: record
              type: STRING

            - name: created_at
              type: TIMESTAMP

            - name: actor
              type: STRING

            - name: user
              type: STRING

          data:
            - id: 0
              record: add.user
              created_at: 1661864400.5699711
              actor: actor
              user: user-one

Do I have to do something different?

[kitagry]

Hi @goccy @halnique

I use timestamp type by python bigquery library. And I got the following error.

I think timestamp format should return int instead of float. Do you think about it? Thank you.

https://github.com/googleapis/python-bigquery/blob/main/google/cloud/bigquery/_helpers.py#L207

Traceback (most recent call last):
  File "/Users/kitagry/go/src/github.com/kitagry/bigquery-test/a.py", line 155, in <module>
    res = emulator.query(sql)
  File "/Users/kitagry/go/src/github.com/kitagry/bigquery-test/a.py", line 98, in query
    return self.client.query(sql).to_dataframe(create_bqstorage_client=False)
  File "/Users/kitagry/Library/Caches/pypoetry/virtualenvs/bigquery-test-5qsCeeNA-py3.9/lib/python3.9/site
-packages/google/cloud/bigquery/job/query.py", line 1696, in to_dataframe
    return query_result.to_dataframe(
  File "/Users/kitagry/Library/Caches/pypoetry/virtualenvs/bigquery-test-5qsCeeNA-py3.9/lib/python3.9/site
-packages/google/cloud/bigquery/table.py", line 1985, in to_dataframe
    record_batch = self.to_arrow(
  File "/Users/kitagry/Library/Caches/pypoetry/virtualenvs/bigquery-test-5qsCeeNA-py3.9/lib/python3.9/site
-packages/google/cloud/bigquery/table.py", line 1779, in to_arrow
    for record_batch in self.to_arrow_iterable(
  File "/Users/kitagry/Library/Caches/pypoetry/virtualenvs/bigquery-test-5qsCeeNA-py3.9/lib/python3.9/site
-packages/google/cloud/bigquery/table.py", line 1635, in _to_page_iterable
    yield from result_pages
  File "/Users/kitagry/Library/Caches/pypoetry/virtualenvs/bigquery-test-5qsCeeNA-py3.9/lib/python3.9/site
-packages/google/cloud/bigquery/_pandas_helpers.py", line 631, in download_arrow_row_iterator
    yield _row_iterator_page_to_arrow(page, column_names, arrow_types)
  File "/Users/kitagry/Library/Caches/pypoetry/virtualenvs/bigquery-test-5qsCeeNA-py3.9/lib/python3.9/site
-packages/google/cloud/bigquery/_pandas_helpers.py", line 598, in _row_iterator_page_to_arrow
    next(iter(page))
  File "/Users/kitagry/Library/Caches/pypoetry/virtualenvs/bigquery-test-5qsCeeNA-py3.9/lib/python3.9/site
-packages/google/api_core/page_iterator.py", line 131, in __next__
    result = self._item_to_value(self._parent, item)
  File "/Users/kitagry/Library/Caches/pypoetry/virtualenvs/bigquery-test-5qsCeeNA-py3.9/lib/python3.9/site
-packages/google/cloud/bigquery/table.py", line 2684, in _item_to_row
    _helpers._row_tuple_from_json(resource, iterator.schema),
  File "/Users/kitagry/Library/Caches/pypoetry/virtualenvs/bigquery-test-5qsCeeNA-py3.9/lib/python3.9/site
-packages/google/cloud/bigquery/_helpers.py", line 430, in _row_tuple_from_json
    row_data.append(_field_from_json(cell["v"], field))
  File "/Users/kitagry/Library/Caches/pypoetry/virtualenvs/bigquery-test-5qsCeeNA-py3.9/lib/python3.9/site
-packages/google/cloud/bigquery/_helpers.py", line 406, in _field_from_json
    return converter(resource, field)
  File "/Users/kitagry/Library/Caches/pypoetry/virtualenvs/bigquery-test-5qsCeeNA-py3.9/lib/python3.9/site
-packages/google/cloud/bigquery/_helpers.py", line 272, in _timestamp_from_json
    return _datetime_from_microseconds(int(value))
ValueError: invalid literal for int() with base 10: '1666353600.0'

[kitagry]

Ok, I understand it. Python client send formatOptions.useInt64Timestamp = true. So, I think emulator should implement thi s option in order to use timestamp in python library.

googleapis/python-bigquery@04510a7