google-app-engine - 将日志文件从 Google Cloud Storage 导入 BigQuery

Question

我们正在开发将日志文件(下面的示例)从 logstach 上传到 Google Cloud Storage 的项目。然后让 App Engine 将日志数据导入 BigQuery。问题是

1. BigQuery 不接受日志文件中由 logstach 创建的某些字段名称，例如 @timestamp。我该如何处理这个问题。App Engine 可以做些什么来解决这个问题吗？

2. 如何为嵌套 JSON (geoip:) 定义 BigQuery 架构？

   {“uuid”:“8806ceef34123122cdd009063f301a34158252f53b9a7d3147639fb71f68b585”，“item_id”:1234，“member_id”:1234，“admin_id”:0，“cate_id”:131，“listing_status”:3，“mon itor_status":2,"注意":"","txn_type":"edit","ip_address":"13.89.42.18","email":"xxxx@gmail.com","post_name":"","user_agent":"COM Mozilla/5.0 (Windows NT 6.1; rv:31.0) Gecko/20100101 Firefox/31.0","timestamp":"2014-08-22 06:38:53","http_host":"EA1-ZoneS1","@ version":"1","@timestamp":"2014-08-21T23:38:59.737Z","type":"redis","ua.name": "Firefox","ua.os":"Windows 7","ua.os_name":"Windows 7","ua.device":"其他","ua.major":"31","ua.minor"":"0","geoip":{"ip":"13.89.42.18","country_code2":"XX","country_code3":"XXX","country_name":"XXXXXXX","continent_code ":"AS","re​​gion_name":"40","city_name":"XXXX","latitude":123.45,"longitude":123.45,"timezone":"亚洲/曼谷","re​​al_region_name":"XXXXXX ","位置":[123.45,123.45]}}

对不起，我是新来的。我无法添加图片。

请给我建议

谢谢。

Answer 1

1) 不能在名称中使用@ 或. 符号。您需要通过类似这样的方式运行数据来删除它们。

line = line.replace("@", "_")    
line = line.replace("ua.", "ua_")

2) 您可能需要更改某些类型，但我能够使用此 JSON 构造函数加载您的示例数据(经过上述修改):

[{
    "name": "uuid",
    "type": "STRING"
}, {
    "name": "item_id",
    "type": "INTEGER"
}, {
    "name": "member_id",
    "type": "INTEGER"
}, {
    "name": "admin_id",
    "type": "INTEGER"
}, {
    "name": "cate_id",
    "type": "INTEGER"
}, {
    "name": "listing_status",
    "type": "INTEGER"
}, {
    "name": "monitor_status",
    "type": "INTEGER"
}, {
    "name": "note",
    "type": "STRING"
}, {
    "name": "txn_type",
    "type": "STRING"
}, {
    "name": "ip_address",
    "type": "STRING"
}, {
    "name": "email",
    "type": "STRING"
}, {
    "name": "post_name",
    "type": "STRING"
}, {
    "name": "user_agent",
    "type": "STRING"
}, {
    "name": "timestamp",
    "type": "TIMESTAMP"
}, {
    "name": "http_host",
    "type": "STRING"
}, {
    "name": "_version",
    "type": "STRING"
}, {
    "name": "_timestamp",
    "type": "TIMESTAMP"
}, {
    "name": "type",
    "type": "STRING"
}, {
    "name": "ua_name",
    "type": "STRING"
}, {
    "name": "ua_os",
    "type": "STRING"
}, {
    "name": "ua_os_name",
    "type": "STRING"
}, {
    "name": "ua_device",
    "type": "STRING"
}, {
    "name": "ua_major",
    "type": "STRING"
}, {
    "name": "ua_minor",
    "type": "STRING"
}, {
    "name": "geoip",
    "type": "RECORD",
    "fields": [{
        "name": "ip",
        "type": "STRING"
    }, {
        "name": "country_code2",
        "type": "STRING"
    }, {
        "name": "country_code3",
        "type": "STRING"
    }, {
        "name": "country_name",
        "type": "STRING"
    }, {
        "name": "continent_code",
        "type": "STRING"
    }, {
        "name": "region_name",
        "type": "STRING"
    }, {
        "name": "city_name",
        "type": "STRING"
    }, {
        "name": "latitude",
        "type": "FLOAT"
    }, {
        "name": "longitude",
        "type": "FLOAT"
    }, {
        "name": "timezone",
        "type": "STRING"
    }, {
        "name": "real_region_name",
        "type": "STRING"
    }, {
        "name": "location",
        "type": "FLOAT",
        "mode": "REPEATED"
    }]
}]