How to import Stackdriver logs into BigQuery

Question

Is there a way to load logs from app engine into BigQuery on Google Cloud Platform?

I'm attempting to use federated querying to load Stackdriver log files in Cloud Storage. However BigQuery cannot load some of the field names written by Stackdriver.

The log files are newline-delimited JSON, with records that look like

{  
   "insertId":"j594356785jpk",
   "labels":{  
      "appengine.googleapis.com/instance_name":"aef-my-instance-20180204t220251-x59f",
      "compute.googleapis.com/resource_id":"99999999999999999",
      "compute.googleapis.com/resource_name":"c3453465db",
      "compute.googleapis.com/zone":"us-central1-f"
   },
   "logName":"projects/my-project/logs/appengine.googleapis.com%2Fstdout",
   "receiveTimestamp":"2018-02-08T02:59:59.972739505Z",
   "resource":{  
      "labels":{  
         "module_id":"my-instance",
         "project_id":"my-project",
         "version_id":"20180204t220251"
      },
      "type":"gae_app"
   },
   "textPayload":"{\"json\":\"blob\"}\n",
   "timestamp":"2018-02-08T02:59:54Z"
}

But BigQuery returns an error on this input: query: Invalid field name "compute.googleapis.com/zone". Fields must contain only letters, numbers, and underscores, start with a letter or underscore, and be at most 128 characters long.

Is there any way to ingest this kind of log into BigQuery?

I'm specifically interested in extracting just the textPayload fields.

Answer 1

You can export Stackdriver logs into BigQuery

In Google Cloud Platform console, Stackdriver Logging >> Exports >> Create Export. Create a filter resource.type="gae_app" and fill the Sink Name on right and choose BigQuery as Sink Service then select the dataset to export to from Sink Destination. https://console.cloud.google.com/logs/exports

Reference: https://cloud.google.com/logging/docs/export/configure_export_v2#dest-create