Skip to main content

2 posts tagged with "logging"

View All Tags

Enable Logging for Google Cloud Storage Buckets and Analyzing Logs in Big Query (Part II)

· 4 min read
Jeffrey Aven
Technologist and Cloud Consultant

In the previous post, we showed you how to enable usage and storage logging for GCS buckets. Now that we have enabled logging, let's load and analyze the logs using Big Query. We will build up a data file vars.jsonnet as we go and show the queries step by step, at the end we will show how to run this as one batch using StackQL.

Step 1 : Create a Big Query dataset​

We will need a dataset (akin to a schema or a database in other RDMBS parlance), basically a container for objects such as tables or views, the data and code to do this are shown here:

INSERT INTO google.bigquery.datasets(
  projectId,
  data__location,
  data__datasetReference,
  data__description,
  data__friendlyName
)
SELECT
  '{{ .projectId }}',
  '{{ .location }}',
  '{ "datasetId": "{{ .datasetId }}", "projectId": "{{ .projectId }}" }',
  '{{ .description }}',
  '{{ .friendlyName }}'
;

Step 2 : Create usage table​

Let's use StackQL to create a table named usage to host the GCS usage logs, the schema for the table is defined in a file named cloud_storage_usage_schema_v0.json which can be downloaded from the location provided, for reference this is provided in the Table Schema tab in the example provided below:

/* create_table.iql */

INSERT INTO google.bigquery.tables(
  datasetId,
  projectId,
  data__description,
  data__friendlyName,
  data__tableReference,
  data__schema
)
SELECT
  '{{ .datasetId }}',
  '{{ .projectId }}',
  '{{ .table.usage.description }}',
  '{{ .table.usage.friendlyName }}',
  '{"projectId": "{{ .projectId }}", "datasetId": "{{ .datasetId }}", "tableId": "{{ .table.usage.tableId }}"}',
  '{{ .table.usage.schema }}'
;

Run the following to execute the StackQL command with the input data shown:

stackql exec -i ./create_table.iql --iqldata ./vars.jsonnet

Step 3 : Load the usage data​

We have a Big Query dataset and a table, lets load some data. To do this we will need to create and submit a load job, we can do this by inserting into the google.bigquery.jobs resource as shown here:

/* bq_load_job.iql */

INSERT INTO google.bigquery.jobs(
  projectId,
  data__configuration
)
SELECT
  'stackql',
  '{
    "load": {
      "destinationTable": {
        "projectId": "{{ .projectId }}",
        "datasetId": "{{ .datasetId }}",
        "tableId": "{{ .table.usage.tableId }}"
      },
      "sourceUris": [
        "gs://{{ .logs_bucket }}/{{ .object_prefix }}"
      ],
      "schema": {{ .table.usage.schema }},
	  "skipLeadingRows": 1,
      "maxBadRecords": 0,
      "projectionFields": []
    }
  }'
;

Run the following to execute:

stackql exec -i ./bq_load_job.iql --iqldata ./vars.jsonnet

Clean up (optional)​

If you want to clean up what you have done, you can do so using StackQL DELETE statements, as provided below:

NOTE: To delete a Big Query dataset, you need to delete all of the tables contained in the dataset first, as shown in the following example

-- delete table(s) 

DELETE FROM google.bigquery.tables 
WHERE projectId = '{{ .projectId }}' 
AND datasetId = '{{ .datasetId }}' 
AND tableId = '{{ .table.usage.tableId }}';

-- delete dataset

DELETE FROM google.bigquery.datasets 
WHERE projectId = '{{ .projectId }}' 
AND datasetId = '{{ .datasetId }}';

Enable Logging for Google Cloud Storage Buckets and Analyzing Logs in Big Query (Part I)

· 3 min read
Jeffrey Aven
Technologist and Cloud Consultant

In a previous article, Deploying and Querying GCS Buckets using StackQL, we walked through some basic creation and query operations on Google Cloud Storage buckets. In this post we will extend on this by enabling logging on a GCS bucket using StackQL. This post is based upon this article: Usage logs & storage logs.

Assuming we have deployed a bucket which we want to log activities on, follow the steps below:

Step 1 : Create a bucket to store the usage logs​

One bucket in a project can be used to collect the usage logs from one or more other buckets in the project. Use the StackQL Command Shell (stackql shell) or stackql exec to create this logs bucket as shown here:

INSERT INTO google.storage.buckets(
  project,
  data__name,
  data__location,
  data__locationType
)
SELECT
  'stackql',
  'stackql-download-logs',
  'US',
  'multi-region'
;

for more examples of creating Google Cloud Storage buckets using StackQL, see Deploying and Querying GCS Buckets using StackQL.

Step 2: Set IAM policy for the logs bucket​

You will need to create an IAM binding to enable writes to this bucket, do this by using the setIamPolicy method as shown here:

EXEC google.storage.buckets.setIamPolicy
@bucket = 'stackql-download-logs'
@@json = '{
  "bindings":[
    {
      "role": "roles/storage.legacyBucketWriter",
      "members":[
        "group:cloud-storage-analytics@google.com"
      ]
    }
  ]
}';

TIP: you should also add role bindings to the roles/storage.legacyBucketOwner role for serviceAccount or users who will be running StackQL SELECT queries against this logs bucket.

Step 3: Enable logging on the target bucket​

To enable logging on your target bucket (or buckets) run the following StackQL EXEC method:

EXEC google.storage.buckets.patch
@bucket = 'stackql-downloads'
@@json = '{
 "logging": {
  "logBucket": "stackql-download-logs",
  "logObjectPrefix": "stackql_downloads"
 }
}';

TIP: use SHOW METHODS IN google.storage.buckets; to see what operations are avaialable such as the patch and setIamPolicy examples shown in the previous steps.

Step 4: Check logging status on target bucket​

To see that logging has been enabled run the StackQL query below:

select name, logging
from google.storage.buckets
WHERE project = 'stackql'
and logging is not null;

To unpack the logging object, you can use the [JSON_EXTRACT]](/language-spec/functions/json/json_extract) built in function as shown here:

select name, json_extract(logging, '$.logBucket') as logBucket,
json_extract(logging, '$.logObjectPrefix') as logObjectPrefix
from google.storage.buckets
WHERE project = 'stackql'
and logging is not null;

In Part II of this post, we will demonstrate how to create a Big Query dataset, then load and analyze the GCS usage logs you have collected using Big Query, stay tuned!