select_cloud_logging.sql

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__"

TableはApp EngineのLogをbq streaming insertしているもの

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