Non-deterministic errors with python client on streaming inserts

Question

I have a BigQuery table with the following schema:

    [
    {
        "mode": "REQUIRED",
        "name": "LOTRANS",
        "type": "FLOAT"
    },
    {
        "mode": "REQUIRED",
        "name": "FIRMWARE",
        "type": "STRING"
    },
    {
        "mode": "REQUIRED",
        "name": "MBATTCHG",
        "type": "INTEGER"
    },
    {
        "mode": "REQUIRED",
        "name": "BATTV",
        "type": "FLOAT"
    },
    {
        "mode": "REQUIRED",
        "name": "MINTIMEL",
        "type": "FLOAT"
    },
    {
        "mode": "REQUIRED",
        "name": "STARTTIME",
        "type": "DATETIME"
    },
    {
        "mode": "REQUIRED",
        "name": "NOMBATTV",
        "type": "FLOAT"
    },
    {
        "mode": "REQUIRED",
        "name": "DATE",
        "type": "DATETIME"
    },
    {
        "mode": "REQUIRED",
        "name": "MODEL",
        "type": "STRING"
    },
    {
        "mode": "REQUIRED",
        "name": "HITRANS",
        "type": "FLOAT"
    },
    {
        "mode": "REQUIRED",
        "name": "TIMELEFT",
        "type": "FLOAT"
    },
    {
        "mode": "REQUIRED",
        "name": "BCHARGE",
        "type": "FLOAT"
    },
    {
        "mode": "REQUIRED",
        "name": "XONBATT",
        "type": "DATETIME"
    },
    {
        "mode": "REQUIRED",
        "name": "TONBATT",
        "type": "INTEGER"
    },
    {
        "mode": "REQUIRED",
        "name": "LINEV",
        "type": "FLOAT"
    },
    {
        "mode": "REQUIRED",
        "name": "XOFFBATT",
        "type": "DATETIME"
    },
    {
        "mode": "REQUIRED",
        "name": "ALARMDEL",
        "type": "STRING"
    },
    {
        "mode": "REQUIRED",
        "name": "STATUS",
        "type": "STRING"
    },
    {
        "mode": "REQUIRED",
        "name": "CUMONBATT",
        "type": "INTEGER"
    },
    {
        "mode": "REQUIRED",
        "name": "CABLE",
        "type": "STRING"
    },
    {
        "mode": "REQUIRED",
        "name": "UPSMODE",
        "type": "STRING"
    },
    {
        "mode": "REQUIRED",
        "name": "LASTXFER",
        "type": "STRING"
    },
    {
        "mode": "REQUIRED",
        "name": "DRIVER",
        "type": "STRING"
    },
    {
        "mode": "REQUIRED",
        "name": "VERSION",
        "type": "STRING"
    },
    {
        "mode": "REQUIRED",
        "name": "LOADPCT",
        "type": "FLOAT"
    },
    {
        "mode": "REQUIRED",
        "name": "NOMINV",
        "type": "FLOAT"
    },
    {
        "mode": "REQUIRED",
        "name": "ENDAPC",
        "type": "DATETIME"
    },
    {
        "mode": "REQUIRED",
        "name": "SERIALNO",
        "type": "STRING"
    },
    {
        "mode": "REQUIRED",
        "name": "HOSTNAME",
        "type": "STRING"
    },
    {
        "mode": "REQUIRED",
        "name": "UPSNAME",
        "type": "STRING"
    },
    {
        "mode": "REQUIRED",
        "name": "NUMXFERS",
        "type": "INTEGER"
    },
    {
        "mode": "REQUIRED",
        "name": "SENSE",
        "type": "STRING"
    },
    {
        "mode": "REQUIRED",
        "name": "APC",
        "type": "STRING"
    },
    {
        "mode": "REQUIRED",
        "name": "MAXTIME",
        "type": "INTEGER"
    },
    {
        "mode": "REQUIRED",
        "name": "BATTDATE",
        "type": "DATE"
    },
    {
        "mode": "REQUIRED",
        "name": "STATFLAG",
        "type": "INTEGER"
    }
]

An example of the data row is as follows:

{
    "ALARMDEL": "No alarm",
    "APC": "001,035,0928",
    "BATTDATE": "2016-12-02",
    "BATTV": "13.7",
    "BCHARGE": "100.0",
    "CABLE": "USB Cable",
    "CUMONBATT": "2758",
    "DATE": "2017-07-04T20:20:26",
    "DRIVER": "USB UPS Driver",
    "ENDAPC": "2017-07-04T20:20:35",
    "FIRMWARE": "904.W1 .D USB FW:W1",
    "HITRANS": "139.0",
    "HOSTNAME": "raspberrypi-master",
    "LASTXFER": "Unacceptable line voltage changes",
    "LINEV": "120.0",
    "LOADPCT": "0.0",
    "LOTRANS": "92.0",
    "MAXTIME": "0",
    "MBATTCHG": "5",
    "MINTIMEL": "3",
    "MODEL": "Back-UPS ES 550G",
    "NOMBATTV": "12.0",
    "NOMINV": "120",
    "NUMXFERS": "188",
    "SENSE": "Medium",
    "SERIALNO": "REDACTED",
    "STARTTIME": "2017-05-23T13:27:07",
    "STATFLAG": "0x05000008",
    "STATUS": "ONLINE",
    "TIMELEFT": "335.8",
    "TONBATT": "0",
    "UPSMODE": "Stand Alone",
    "UPSNAME": "raspberrypi-master",
    "VERSION": "3.14.12 (29 March 2014) debian",
    "XOFFBATT": "2017-07-04T03:55:55",
    "XONBATT": "2017-07-04T03:55:53"
}

I tried following the example code, but whenever I try to insert_data with the above dictionary using the python client I get non-deterministic errors such as

[{'index': 0, 'errors': [{u'debugInfo': u'generic::invalid_argument: Cannot convert value to integer (bad value):MBATTCHG', u'reason': u'invalid', u'message': u'Cannot convert value to integer (bad value):MBATTCHG', u'location': u'mbattchg'}]}]

But the error changes on every attempted insert. What are ways to help debug why the errors are not deterministic, and why the converted values tend to be the keys of the dictionary instead of the values?

EDIT

Modified the event to the following, still getting errors

{
    "ALARMDEL": "No alarm",
    "APC": "001,035,0928",
    "BATTDATE": "2016-12-02",
    "BATTV": 13.7,
    "BCHARGE": 100.0,
    "CABLE": "USB Cable",
    "CUMONBATT": 2758,
    "DATE": "2017-07-05T02:41:00",
    "DRIVER": "USB UPS Driver",
    "ENDAPC": "2017-07-05T02:41:07",
    "FIRMWARE": "904.W1 .D USB FW:W1",
    "HITRANS": 139.0,
    "HOSTNAME": "raspberrypi-master",
    "LASTXFER": "Unacceptable line voltage changes",
    "LINEV": 123.0,
    "LOADPCT": 0.0,
    "LOTRANS": 92.0,
    "MAXTIME": 0,
    "MBATTCHG": 5,
    "MINTIMEL": 3.0,
    "MODEL": "Back-UPS ES 550G",
    "NOMBATTV": 12.0,
    "NOMINV": 120.0,
    "NUMXFERS": "188",
    "SENSE": "Medium",
    "SERIALNO": "REDACTED",
    "STARTTIME": "2017-05-23T13:27:07",
    "STATFLAG": 83886088,
    "STATUS": "ONLINE",
    "TIMELEFT": 335.8,
    "TONBATT": 0,
    "UPSMODE": "Stand Alone",
    "UPSNAME": "raspberrypi-master",
    "VERSION": "3.14.12 (29 March 2014) debian",
    "XOFFBATT": "2017-07-04T03:55:55",
    "XONBATT": "2017-07-04T03:55:53"
}

I tried to post the same object 2 different times and ended up with the following error messages (different each time)

[{'index': 0, 'errors': [{u'debugInfo': u'generic::invalid_argument: Cannot convert value to integer (bad value):CUMONBATT', u'reason': u'invalid', u'message': u'Cannot convert value to integer (bad value):CUMONBATT', u'location': u'cumonbatt'}]}]
[{'index': 0, 'errors': [{u'debugInfo': u'generic::out_of_range: Invalid datetime string "LOADPCT"', u'reason': u'invalid', u'message': u'Invalid datetime string "LOADPCT"', u'location': u'endapc'}]}]

EDIT 2:

Fixed by using the returned schema for the table to construct an array instead of passing in an object

e = []
for schema_item in table._schema:
    e.append(d[schema_item.name])

rows = [e]

Try printing the row before inserting it (or printing it only when there is an error). What do you see? — Elliott Brossard
– Elliott Brossard, Commented Jul 4, 2017 at 20:41
@ElliottBrossard it is a python object. json.dumps on the object gives the simple maps above. — user665049
– user665049, Commented Jul 5, 2017 at 2:48

Willian Fuks · Accepted Answer · 2017-07-05 03:54:46Z

4

The row you used as example isn't in accordance with your schema indeed. It should be like:

{
    "ALARMDEL": "No alarm",
    "APC": "001,035,0928",
    "BATTDATE": "2016-12-02",
    "BATTV": 13.7,
    "BCHARGE": 100.0,
    "CABLE": "USB Cable",
    "CUMONBATT": 2758,
    "DATE": "2017-07-04T20:20:26",
    "DRIVER": "USB UPS Driver",
    "ENDAPC": "2017-07-04T20:20:35",
    "FIRMWARE": "904.W1 .D USB FW:W1",
    "HITRANS": 139.0,
    "HOSTNAME": "raspberrypi-master",
    "LASTXFER": "Unacceptable line voltage changes",
    "LINEV": 120.0,
    "LOADPCT": 0.0,
    "LOTRANS": 92.0,
    "MAXTIME": 0,
    "MBATTCHG": 5,
    "MINTIMEL": 3,
    "MODEL": "Back-UPS ES 550G",
    "NOMBATTV": 12.0,
    "NOMINV": 120,
    "NUMXFERS": 188,
    "SENSE": "Medium",
    "SERIALNO": "REDACTED",
    "STARTTIME": "2017-05-23T13:27:07",
    "STATFLAG": "0x05000008",
    "STATUS": "ONLINE",
    "TIMELEFT": 335.8,
    "TONBATT": 0,
    "UPSMODE": "Stand Alone",
    "UPSNAME": "raspberrypi-master",
    "VERSION": "3.14.12 (29 March 2014) debian",
    "XOFFBATT": "2017-07-04T03:55:55",
    "XONBATT": "2017-07-04T03:55:53"
}

Notice that FLOATS should be represented as a FLOAT number, not as a STRING (19.2 instead of "19.2" for instance).

One problem that would remain is the field STATFLAG, you defined it as an INTEGER but it has the character "x", maybe you'll have to change its type to be a STRING.

[EDIT]:

As for the python code, just remember that you should use the method insert_data from the Table class and for the rows input you should have a list with tuples for each row being insert, for instance:

rows=[(92.0, '904.W1 .D USB FW:W1', 5, 13.7, 3, '2017-05-23T13:27:07', 12.0, '2017-07-04T20:20:26', 'Back-UPS ES 550G', 139.0, 335.8, 100.0, '2017-07-4T03:55:53', 0, 120.0, '2017-07-04T03:55:55', 'No alarm', 'ONLINE', 758, 'USB Cable', 'Stand Alone', 'Unacceptable line voltage changes', 'USB UPS river', '3.14.12 (29 March 2014) debian', 0.0, 120, '2017-07-4T20:20:35', 'REDACTED', 'raspberrypi-master', 'raspberrypi-master', 88, 'Medium', '001,035,0928', 0, '2016-12-02', 5000008)]
table.insert_data(rows)

edited Jul 5, 2017 at 3:54

answered Jul 4, 2017 at 20:47

Willian Fuks

11.9k10 gold badges55 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

user665049 Over a year ago

This did not work. Updated comment with example with the suggested modifications.

Willian Fuks Over a year ago

If you try to stream just this updated row for testing purpose, does it work?

user665049 Over a year ago

Unfortunately no:

[{'index': 0, 'errors': [{u'debugInfo': u'generic::invalid_argument: Cannot convert value to integer (bad value):ENDAPC', u'reason': u'invalid', u'message': u'Cannot convert value to integer (bad value):ENDAPC', u'location': u'cumonbatt'}]}]

user665049 Over a year ago

I feel like cumonbatt not being ENDAPC should be important, but I don't know enough about the python client to know why it is trying to parse dictionary keys as values.

Willian Fuks Over a year ago

please check the edit I made to my post. Notice you must send a tuple with values and not a dict to insert_data. I created a table like yours here and I've already streamed 1k rows with no errors so far.

|

Collectives™ on Stack Overflow

Non-deterministic errors with python client on streaming inserts

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related