syslogにjson出力して、bigqueryでview作るためのsql

select_json_from_syslog.sql

SELECT
  JSON_EXTRACT(REGEXP_REPLACE(textPayload, r'.*{"__SAMPLE__', '{"__SAMPLE__'), '$.__SAMPLE__.id') AS id,
  JSON_EXTRACT(REGEXP_REPLACE(textPayload, r'.*{"__SAMPLE__', '{"__SAMPLE__'), '$.__SAMPLE__.kind') AS kind,
  JSON_EXTRACT(REGEXP_REPLACE(textPayload, r'.*{"__SAMPLE__', '{"__SAMPLE__'), '$.__SAMPLE__.selfLink') AS selfLink,
  JSON_EXTRACT(REGEXP_REPLACE(textPayload, r'.*{"__SAMPLE__', '{"__SAMPLE__'), '$.__SAMPLE__.name') AS name,
  JSON_EXTRACT(REGEXP_REPLACE(textPayload, r'.*{"__SAMPLE__', '{"__SAMPLE__'), '$.__SAMPLE__.bucket') AS bucket,
  JSON_EXTRACT(REGEXP_REPLACE(textPayload, r'.*{"__SAMPLE__', '{"__SAMPLE__'), '$.__SAMPLE__.generation') AS generation,
  JSON_EXTRACT(REGEXP_REPLACE(textPayload, r'.*{"__SAMPLE__', '{"__SAMPLE__'), '$.__SAMPLE__.metageneration') AS metageneration,
  JSON_EXTRACT(REGEXP_REPLACE(textPayload, r'.*{"__SAMPLE__', '{"__SAMPLE__'), '$.__SAMPLE__.contentType') AS contentType,
  JSON_EXTRACT(REGEXP_REPLACE(textPayload, r'.*{"__SAMPLE__', '{"__SAMPLE__'), '$.__SAMPLE__.updated') AS updated,
  JSON_EXTRACT(REGEXP_REPLACE(textPayload, r'.*{"__SAMPLE__', '{"__SAMPLE__'), '$.__SAMPLE__.storageClass') AS storageClass,
  JSON_EXTRACT(REGEXP_REPLACE(textPayload, r'.*{"__SAMPLE__', '{"__SAMPLE__'), '$.__SAMPLE__.size') AS size,
  JSON_EXTRACT(REGEXP_REPLACE(textPayload, r'.*{"__SAMPLE__', '{"__SAMPLE__'), '$.__SAMPLE__.md5Hash') AS md5Hash,
  JSON_EXTRACT(REGEXP_REPLACE(textPayload, r'.*{"__SAMPLE__', '{"__SAMPLE__'), '$.__SAMPLE__.mediaLink') AS mediaLink,
  JSON_EXTRACT(REGEXP_REPLACE(textPayload, r'.*{"__SAMPLE__', '{"__SAMPLE__'), '$.__SAMPLE__.crc32c') AS crc32c,
  JSON_EXTRACT(REGEXP_REPLACE(textPayload, r'.*{"__SAMPLE__', '{"__SAMPLE__'), '$.__SAMPLE__.etag') AS etag,
FROM
  [computeengine.syslog_20150731]
WHERE
  textPayload CONTAINS "__SAMPLE__"
ORDER BY
  metadata.timestamp DESC
LIMIT
  100