将日志文件从Google Cloud Storage导入到BigQuery

user3769827 发表于 Dev

用户名

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

BigQuery在日志文件中不接受Logstach创建的某些字段名称，例如@timestamp。我该如何处理？App Engine可以采取任何措施解决此问题吗？
如何为嵌套JSON（geoip :)定义BigQuery模式？

{“ uuid”：“ 8806ceef34123122cdd009063f301a34158252f53b9a7d3147639fb71f68b585”，“ item_id”：1234，“ member_id”：1234，“ admin_id”：0，“ cate_id”：131，“ listing_status”：3，“ monitor_status” :: 2，“ “，” txn_type“：”编辑“，” ip_address“：” 13.89.42.18“，”电子邮件“：” [email protected]“，” 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”，“类型”：“ redis”，“ ua.name”：“Firefox”，“ ua.os”：“ Windows 7”，“ ua.os_name”：“ Windows 7”，“ ua.device”：“ Other”，“ ua.major”：“ 31”，“ ua.minor” ：“ 0”，“ geoip”：{“ ip”：“ 13.89.42.18”，“ country_code2”：“ XX”，“ country_code3”：“ XXX”，“ country_name”：“ XXXXXXX”，“ continent_code”：“ AS”，“ region_name” ：“ 40”，“ city_name”：“ XXXX”，“纬度”：123.45，“经度”：123.45，“时区”：“亚洲/曼谷”，“ real_region_name”：“ XXXXXX”，“位置”：[123.45， 123.45]} }

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

请给我建议

谢谢。

瑞安

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