Cloud Logging経由で出力したjsonをViewに構築し直すためのクエリ

select_syslog_from_cloudlogging.sql

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