python - python 客户端在流式插入时出现非确定性错误

Question

我有一个具有以下架构的 BigQuery 表:

    [
    {
        "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"
    }
]

数据行示例如下:

{
    "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"
}

我尝试关注 example code ，但是每当我尝试使用 python 客户端将上述字典插入_data 时，我都会遇到不确定的错误，例如

[{'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'}]}]

但是错误会在每次尝试插入时发生变化。有哪些方法可以帮助调试为什么错误不是确定性的，以及为什么转换后的值往往是字典的键而不是值？

编辑

修改事件如下，还是报错

{
    "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"
}

我尝试发布同一个对象 2 次不同的时间并以以下错误消息结束(每次都不同)

[{'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'}]}]

编辑 2:

通过使用表的返回模式构造数组而不是传入对象来修复

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

rows = [e]

Answer 1

您用作示例的行确实不符合您的架构。它应该是这样的:

{
    "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"
}

请注意，FLOATS 应表示为 FLOAT 数字，而不是 STRING(例如，19.2 而不是“19.2”)。

仍然存在的一个问题是字段 STATFLAG，您将其定义为 INTEGER 但它具有字符“x”，也许您必须将其类型更改为 字符串。

[编辑]:

至于 python 代码，请记住您应该使用 Table 类中的方法 insert_data 并且对于 rows 输入您应该有一个列表，其中包含要插入的每一行的元组，例如:

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)