Search code examples
pythonpandasdataframegoogle-bigquery

Pandas dataframe to BQ fail with timestamp column

I encountered with the timestamp format.

I want to retrieve current timestamp with a function and use it into a pandas dataframe as below:

df['CURRENT_TS'] = pd.Timestamp.now()

After that, the DF will be converted to CSV on a GCS bucket. Following this, the csv will be ingested to BQ using load_table_from_uri :

Error:load_table_from_uri() : POST https://bigquery.googleapis.com/bigquery/v2/...: Invalid value for mode: TIMESTAMP is not a valid value
'message': 'Invalid value for mode: TIMESTAMP is not a valid value', 'domain': 'global', 'reason': 'invalid'
Solution

  • Have you explored formatting the timestamp column of pandas?

    df['CURRENT_TS'] = pd.Timestamp.now().strftime('%Y-%m-%d %H:%M:%S.%f')

    Just make sure that you specify the correct data format options, such as delimiters, header rows, and data types (sometimes the auto detection might fail).