12747


App Engineアプリからjson形式でログを出力し、Cloud
LoggingのBQへのExport機能を使って、BQに入れ、そこからjsonをjson
functionを使って、中身を抽出して、Viewを生成しようと思ったのですが、以下のQueryだと、Queryとしては実行できるけど、Viewを生成しようとするErrorが出ます。

[code]

    SELECT
      JSON_EXTRACT(protoPayload.line.logMessage, '$.__DS__KIND__PUGEVENT__.id') AS id,
      JSON_EXTRACT(protoPayload.line.logMessage, '$.__DS__KIND__PUGEVENT__.organizationId') AS organizationId,
      JSON_EXTRACT(protoPayload.line.logMessage, '$.__DS__KIND__PUGEVENT__.title') AS title,
      JSON_EXTRACT(protoPayload.line.logMessage, '$.__DS__KIND__PUGEVENT__.description') AS description,
      JSON_EXTRACT(protoPayload.line.logMessage, '$.__DS__KIND__PUGEVENT__.url') AS url,
      JSON_EXTRACT(protoPayload.line.logMessage, '$.__DS__KIND__PUGEVENT__.startAt') AS startAt,
      JSON_EXTRACT(protoPayload.line.logMessage, '$.__DS__KIND__PUGEVENT__.createdAt') AS createdAt,
      JSON_EXTRACT(protoPayload.line.logMessage, '$.__DS__KIND__PUGEVENT__.updatedAt') AS updatedAt,
      protoPayload.line.logMessage AS source
    FROM
      [appenginelog.appengine_googleapis_com_request_log_20150726]
    WHERE
      protoPayload.line.logMessage CONTAINS "{\"__DS__KIND__PUGEVENT__"
    
[/code]

# Error Message

Failed to create view. Cannot create valid output schema for field
description. Try renaming description to protoPayload.line.description in the
outermost SELECT.

# Table Schema

[code]

    httpRequest RECORD  NULLABLE    
    httpRequest.referer STRING  NULLABLE    
    httpRequest.remoteIp    STRING  NULLABLE    
    httpRequest.requestMethod   STRING  NULLABLE    
    httpRequest.requestSize INTEGER NULLABLE    
    httpRequest.requestUrl  STRING  NULLABLE    
    httpRequest.responseSize    INTEGER NULLABLE    
    httpRequest.status  INTEGER NULLABLE    
    httpRequest.userAgent   STRING  NULLABLE    
    insertId    STRING  NULLABLE    
    log STRING  NULLABLE    
    metadata    RECORD  NULLABLE    
    metadata.labels RECORD  REPEATED    
    metadata.labels.key STRING  NULLABLE    
    metadata.labels.value   STRING  NULLABLE    
    metadata.projectId  STRING  NULLABLE    
    metadata.region STRING  NULLABLE    
    metadata.serviceName    STRING  NULLABLE    
    metadata.severity   STRING  NULLABLE    
    metadata.timestamp  TIMESTAMP   NULLABLE    
    metadata.userId STRING  NULLABLE    
    metadata.zone   STRING  NULLABLE    
    protoPayload    RECORD  NULLABLE    
    protoPayload.appEngineRelease   STRING  NULLABLE    
    protoPayload.appId  STRING  NULLABLE    
    protoPayload.cost   FLOAT   NULLABLE    
    protoPayload.endTime    TIMESTAMP   NULLABLE    
    protoPayload.finished   BOOLEAN NULLABLE    
    protoPayload.host   STRING  NULLABLE    
    protoPayload.httpVersion    STRING  NULLABLE    
    protoPayload.instanceId STRING  NULLABLE    
    protoPayload.instanceIndex  INTEGER NULLABLE    
    protoPayload.ip STRING  NULLABLE    
    protoPayload.latency    STRING  NULLABLE    
    protoPayload.line   RECORD  REPEATED    
    protoPayload.line.logMessage    STRING  NULLABLE    
    protoPayload.line.severity  STRING  NULLABLE    
    protoPayload.line.sourceLocation    RECORD  NULLABLE    
    protoPayload.line.sourceLocation.file   STRING  NULLABLE    
    protoPayload.line.sourceLocation.functionName   STRING  NULLABLE    
    protoPayload.line.sourceLocation.line   INTEGER NULLABLE    
    protoPayload.line.time  TIMESTAMP   NULLABLE    
    protoPayload.megaCycles INTEGER NULLABLE    
    protoPayload.method STRING  NULLABLE    
    protoPayload.moduleId   STRING  NULLABLE    
    protoPayload.nickname   STRING  NULLABLE    
    protoPayload.pendingTime    STRING  NULLABLE    
    protoPayload.referrer   STRING  NULLABLE    
    protoPayload.requestId  STRING  NULLABLE    
    protoPayload.resource   STRING  NULLABLE    
    protoPayload.responseSize   INTEGER NULLABLE    
    protoPayload.sourceReference    RECORD  REPEATED    
    protoPayload.sourceReference.repository STRING  NULLABLE    
    protoPayload.sourceReference.revisionId STRING  NULLABLE    
    protoPayload.startTime  TIMESTAMP   NULLABLE    
    protoPayload.status INTEGER NULLABLE    
    protoPayload.taskName   STRING  NULLABLE    
    protoPayload.taskQueueName  STRING  NULLABLE    
    protoPayload.traceId    STRING  NULLABLE    
    protoPayload.urlMapEntry    STRING  NULLABLE    
    protoPayload.userAgent  STRING  NULLABLE    
    protoPayload.versionId  STRING  NULLABLE    
    protoPayload.wasLoadingRequest  BOOLEAN NULLABLE        
    
[/code]


CC BY-SA 3.0
2015-07-26T10:51:22.773
12718
2015-07-27T12:25:24.757
4361
question
google-app-engine google-bigquery
App Engine Log TableからViewを生成したい


`protoPayload.line`が`Repeated Data`なSchemaなのでFLATTENする必要があります。

<https://cloud.google.com/bigquery/docs/data#flatten>

なので以下のように修正して下さい。

[code]

    SELECT
      JSON_EXTRACT(protoPayload.line.logMessage, '$.__DS__KIND__PUGEVENT__.id') AS id,
      JSON_EXTRACT(protoPayload.line.logMessage, '$.__DS__KIND__PUGEVENT__.organizationId') AS organizationId,
      JSON_EXTRACT(protoPayload.line.logMessage, '$.__DS__KIND__PUGEVENT__.title') AS title,
      JSON_EXTRACT(protoPayload.line.logMessage, '$.__DS__KIND__PUGEVENT__.description') AS description,
      JSON_EXTRACT(protoPayload.line.logMessage, '$.__DS__KIND__PUGEVENT__.url') AS url,
      JSON_EXTRACT(protoPayload.line.logMessage, '$.__DS__KIND__PUGEVENT__.startAt') AS startAt,
      JSON_EXTRACT(protoPayload.line.logMessage, '$.__DS__KIND__PUGEVENT__.createdAt') AS createdAt,
      JSON_EXTRACT(protoPayload.line.logMessage, '$.__DS__KIND__PUGEVENT__.updatedAt') AS updatedAt,
      protoPayload.line.logMessage AS source
    FROM
      FLATTEN([appenginelog.appengine_googleapis_com_request_log_20150726], protoPayload.line)  //<-- FLATTENを追加
    WHERE
      protoPayload.line.logMessage CONTAINS "{\"__DS__KIND__PUGEVENT__"
    
[/code]


CC BY-SA 3.0
2015-07-27T12:25:24.757
12747
2015-07-27T12:25:24.757
4733
12718
answer