[Bigquery insert data error] table.insert_data() issue

I got this error while using table.insert_data(rows)

--> 246     if not value.tzinfo:
    247         value = value.replace(tzinfo=UTC)
    248     # Regardless of what timezone is on the value, convert it to UTC.

AttributeError: 'str' object has no attribute 'tzinfo'

The doc of table.insert_data() said rows is list of tuple, so this is my schema

sql_schema = [('viewer_id', 'INTEGER'),
              ('ip','STRING'),
              ('author_id', 'INTEGER'),
              ('post_id', 'STRING'),
              ('time', 'TIMESTAMP')]

And this is a test data:

[['25512',
  '126.107.92.215',
  '6524',
  '9f4047d3-7bf8-406c-823c-9803b4730fb9',
  '2015-12-14 18:29:01.245142'],
 ['24534',
  '87.13.200.79',
  '29096',
  'e09baadc-9647-4cde-9019-7c6846dfd662',
  '2015-12-14 18:29:01.245237']]

I know '2015-12-14 18:29:01.245237' is a string which doesn't have tzinfo field.
Do I have to convert it to Datetime object every time I try to load time data into bigquery?
Do I have better options?

Thanks

@icrtiou Are you on

http://googlecloudplatform.github.io/gcloud-python/latest/

or

http://googlecloudplatform.github.io/gcloud-python/0.8.0/

Also which version do you have installed?

$ pip show gcloud
---
Metadata-Version: 2.0
Name: gcloud
Version: 0.8.0
Summary: API Client library for Google Cloud
Home-page: https://github.com/GoogleCloudPlatform/gcloud-python
Author: Google Cloud Platform
Author-email: jjg+gcloud-python@google.com
License: Apache 2.0
Location: /usr/local/lib/python2.7/dist-packages
Requires: six, httplib2, pycrypto, protobuf, google-apitools, oauth2client

@icrtiou Also thanks a lot for kicking the tires here AND for taking the time to report the bugs.

Sorry the doc is vague:

:type rows: list of tuples
:param rows: row data to be inserted

As an example, our system test uses the schema

full_name = bigquery.SchemaField('full_name', 'STRING',
                                 mode='REQUIRED')
age = bigquery.SchemaField('age', 'INTEGER', mode='REQUIRED')
now = bigquery.SchemaField('now', 'TIMESTAMP')

to create the table

table = dataset.table(TABLE_NAME, schema=[full_name, age, now])

and then uses rows as a list of triples

ROWS = [
    ('Phred Phlyntstone', 32, NOW),
    ('Bharney Rhubble', 33, NOW + datetime.timedelta(seconds=10)),
    ('Wylma Phlyntstone', 29, NOW + datetime.timedelta(seconds=20)),
    ('Bhettye Rhubble', 27, None),
]

that correspond to the schema (i.e. a required string, a required integer and an optional timestamp). After does this, they are inserted with a set of row IDS:

ROW_IDS = [1, 2, 3, 4]
errors = table.insert_data(ROWS, ROW_IDS)

(For the record, I wouldn't have expected you to have looked here, just wanted to provide an example.)

As of right now, we expect a datetime.datetime object for a TIMESTAMP schema field.
What situation are you in that you have lots of datetime's as strings rather than native? (So I can understand the use-case)
Could you provide the code snippets you used to generate the error? It's unclear how sql_schema and your "test data" were used.

Perfect!! Thanks for quick response.
I'm on 0.8.0 doc.

☁  pageview_to_bigquery [master] ⚡ pip show gcloud
---
Metadata-Version: 2.0
Name: gcloud
Version: 0.8.0
Summary: API Client library for Google Cloud
Home-page: https://github.com/GoogleCloudPlatform/gcloud-python
Author: Google Cloud Platform
Author-email: jjg+gcloud-python@google.com
License: Apache 2.0
Location: /Users/lucas/anaconda/lib/python2.7/site-packages
Requires: six, httplib2, pycrypto, protobuf, google-apitools, oauth2client

Thanks you so much by pointing out a good source to learn how to use gcloud API. The system_tests is wonderful.

The use case is as follow:
I got csv files periodically uploaded to s3, and I want to load files into bigquery table.
So I'm going to have many time string (UTC) like this 2015-12-14 18:29:01.245237 in the raw csv waiting to be loaded into bigquery.

About my code, it's very simple:
load data as a pandas dataframe -> transform it to json -> send it to bigquery table

df = pd.read_csv(StringIO(data), names=['viewer_id', 'ip', 'author_id', 'post_id', 'time'])

rows = json.loads(df.to_json(orient='records'))

table.insert_data(rows)

I use the sql_schema to create the bigquery table:

sql_schema = [('viewer_id', 'INTEGER'),
              ('ip','STRING'),
              ('author_id', 'INTEGER'),
              ('post_id', 'STRING'),
              ('time', 'TIMESTAMP')]

table.schema = [ SchemaField(name = n, field_type = t) for n, t in sql_schema ]
table.update()

So I guess I have to transform those raw time string into datetime object before loading into bigquery.
Thank you.

@icrtiou You can upload your CSV files directly to Google Cloud Storage and then tell BigQuery to grab them (ref).

With our library you can use bigquery.Client.load_table_from_storage to accomplish this. See storage docs for info on how to upload the CSV to Google Cloud Storage (there is also an example in the BigQuery system test file).

Got it. That's next I wanna try after I could handle this streaming case without leaving side effects.
Thanks for your time.

Thanks for your time! I am filing a new issue for consideration of how to handle existing string timestamps.

That would be awesome feature to have. For lazy library user XD

Not just for lazy people but for people who don't want to waste compute cycles.

Indeed.

Add more detail to BigQuery Table.insert_data().

Is there any reason table.insert() does not accept dictionaries of {'column_name':value_to_be_inserted,...,}?

This would be clean and allow arbitrary sorting.

@jasonmellone

@jasonmellone Care to file a new feature request?

I just don't want the discussion to get lost among this old, closed issue.

Will do this evening

…

On Mon, May 8, 2017, 15:48 Danny Hermes ***@***.***> wrote: @jasonmellone <https://github.com/jasonmellone> Care to file a feature request? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#1381 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AGBAHRHDiUehLXMMNUJuQbvh4WDjxzvzks5r33GEgaJpZM4HC0MY> .

Thanks

chore(deps): update dependency setuptools to v63.3.0 (#1381)

chore: update sys test cred (#1381)

chore(main): release 2.29.1 (#1381)

chore: remove debugger comment (#1381)

feat: Add support for multiplexed sessions (#1381)

Navigation Menu

Repository navigation

[Bigquery insert data error] table.insert_data() issue #1381

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

[Bigquery insert data error] table.insert_data() issue #1381

Description

Activity

dhermes commented on Jan 12, 2016

dhermes commented on Jan 12, 2016

dhermes commented on Jan 12, 2016

lukaemon commented on Jan 12, 2016

dhermes commented on Jan 12, 2016

lukaemon commented on Jan 12, 2016

dhermes commented on Jan 12, 2016

lukaemon commented on Jan 12, 2016

dhermes commented on Jan 12, 2016

lukaemon commented on Jan 12, 2016

jasonmellone commented on May 8, 2017

dhermes commented on May 8, 2017

jasonmellone commented on May 8, 2017

dhermes commented on May 8, 2017

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions