Google Cloud function crashing when insert datetime object into BigQuery

Question

I have a Cloud Function written in Python which is triggered by PubSub messages and basically splits the incoming messages and inserts them into a BigQuery table. This was previously working when I was just inserting the sensor values along with a datetime.now() timestamp which I used for testing. However as I needed the timestamp from the sensor itself I had to rewrite the function and now it is crashing and I'm not sure exactly where the problem is or how to debug it further. My function code is as follows:

import base64
import datetime
from google.cloud import bigquery
import json


def sensor_pubsub(data, context):
    """Triggered from a message on a Cloud Pub/Sub topic.
    Args:
         event (dict): Event payload.
         context (google.cloud.functions.Context): Metadata for the event.
    """
    pubsub_message = base64.b64decode(data['data']).decode('utf-8')
    print(pubsub_message)
    accelX, accelY, accelZ, gyroX, gyroY, gyroZ, roll, pitch, yaw, ts = pubsub_message.split(", ")

    device_id = data['attributes']['deviceId']
    project_id = data['attributes']['projectId']
    registry_id = data['attributes']['deviceRegistryId']
    mytime = datetime.datetime.strptime(ts, "%H:%M:%S").time()
    mydatetime = datetime.datetime.combine(datetime.date.today(), mytime)
    client = bigquery.Client()
    dataset_id = 'sensorData'
    table_id = 'sensorTable'
    table_ref = client.dataset(dataset_id).table(table_id)
    table = client.get_table(table_ref)  # API request
    rows_to_insert = [
        (device_id, accelX, accelY, accelZ, gyroX, gyroY, gyroZ, roll, pitch, yaw, mydatetime)
    ]

    errors = client.insert_rows(table, rows_to_insert)
    assert errors == []

The BigQuery table schema are all of String Type apart from the column to store the timestamp which is in DATETIME format. The incoming timestamp is in the following format: hh:mm:ss which is why I had to add the date in the function itself and seems to work in a standard Python IDE. Not sure if this is something wrong with the code or if the format of the timestamp is incompatible with BigQuery.

Can anyone point out where I'm going wrong or how I can get this working?

Hi @AlanSpillane could you please edit your question and add the full error message that you are facing? This should help understand further, the exactly error that you are facing. Besides that, if you could edit and as well, the code you were using before and that was working, would help the Community understand the changes you performed. — gso_gabriel
I managed to figure it out earlier it was an issue with how I formatted the timestamp from the pubsub message after splitting it. I updated with the working code below. Thanks for all your responses. — Alan Spillane

Alan Spillane Alan Spillane · Accepted Answer · 2020-04-27T20:26:56

I managed to figure it out earlier. It was an issue with how I formatted the datetime after splitting the pubsub message. I had to convert the timestamp back into a string type after splitting it from the pubsub message then the same code worked(I also removed some of the additional sensor values I was no longer using):

import base64
import datetime
from google.cloud import bigquery
import json


def sensor_pubsub(data, context):
    """Triggered from a message on a Cloud Pub/Sub topic.
    Args:
         event (dict): Event payload.
         context (google.cloud.functions.Context): Metadata for the event.
    """
    pubsub_message = base64.b64decode(data['data']).decode('utf-8')
    print(pubsub_message)
    accelX, accelY, accelZ, gyroX, gyroY, gyroZ, ts = pubsub_message.split(", ")

    device_id = data['attributes']['deviceId']
    project_id = data['attributes']['projectId']
    registry_id = data['attributes']['deviceRegistryId']
    ts1 = str(ts)
    mytime = datetime.datetime.strptime(ts1, "%H:%M:%S").time()
    mydatetime = datetime.datetime.combine(datetime.date.today(), mytime)
    print(mydatetime)
    print(type(mydatetime))
    client = bigquery.Client()
    dataset_id = 'sensorData'
    table_id = 'sensorTable'
    table_ref = client.dataset(dataset_id).table(table_id)
    table = client.get_table(table_ref)  # API request
    rows_to_insert = [
        (device_id, accelX, accelY, accelZ, gyroX, gyroY, gyroZ, mydatetime)
    ]

    errors = client.insert_rows(table, rows_to_insert)
    assert errors == []

Google Cloud function crashing when insert datetime object into BigQuery

1 Answers