Edit on GitHub
Report issue
Page history

Export Cloud Monitoring metrics using Monitoring Query Language (MQL)

Author(s): @xiangshen-dk ,   Published: 2020-11-11

Xiang Shen | Solutions Architect | Google

Contributed by Google employees.

This tutorial describes a solution that uses the Google Cloud Monitoring Query Language (MQL) to export specific monitoring metrics.

The following diagram shows the high-level architecture of this solution:

export-metrics-arch

Costs

This tutorial uses billable components of Google Cloud, including the following:

Use the pricing calculator to generate a cost estimate based on your projected usage.

Before you begin

For this tutorial, you need a Google Cloud project. You can create a new project or select a project that you have already created. When you finish this tutorial, you can avoid continued billing by deleting the resources that you created. To make cleanup easiest, you may want to create a new project for this tutorial, so that you can delete the project when you're done. For details, see the "Cleaning up" section at the end of the tutorial.

  1. Select or create a Google Cloud project.

  2. Enable billing for your project.

  3. Enable the Cloud Functions, Cloud Scheduler, Pub/Sub, and BigQuery APIs.

  4. Make sure that you have either a project owner or editor role, or sufficient permissions to use the services listed above.

Using Cloud Shell

This tutorial uses the following tool packages:

Because Cloud Shell automatically includes these packages, we recommend that you run the commands in this tutorial in Cloud Shell, so that you don't need to install these packages locally.

Preparing your environment

Get the sample code

The sample code for this tutorial is in the Google Cloud Community GitHub repository.

  1. Clone the repository:

    git clone https://github.com/GoogleCloudPlatform/community.git
    
  2. Go to the tutorial directory:

    cd community/tutorials/metrics-export-with-mql
    

Implementation steps

Set the environment variables

export PROJECT_ID=`gcloud config get-value core/project`
export PUBSUB_TOPIC=mql_metric_export
export BIGQUERY_DATASET=metric_export
export BIGQUERY_TABLE=mql_metrics

Update configuration

  1. Update the variables in the configuration file:

    envsubst < config-template.py > config.py
    

    If you don't have envsubst installed, you can open the config-template.py file, update the variables, and save it as config.py.

  2. Add and update the MQL queries as needed in the config.py file. You can find examples in the MQL documentation.

Create the export pipeline

  1. Create a BigQuery dataset:

    bq mk $BIGQUERY_DATASET
    
  2. Create a table using the schema JSON file:

    bq mk --table ${BIGQUERY_DATASET}.${BIGQUERY_TABLE}  ./bigquery_schema.json
    
  3. Create a service account for the export Cloud Function:

    gcloud iam service-accounts create mql-export-metrics \
        --display-name "MQL export metrics SA" \
        --description "Used for the function that export monitoring metrics"
    
    export EXPORT_METRIC_SERVICE_ACCOUNT=mql-export-metrics@$PROJECT_ID.iam.gserviceaccount.com 
    
  4. Assign IAM roles to the service account:

    gcloud projects add-iam-policy-binding  $PROJECT_ID --member="serviceAccount:$EXPORT_METRIC_SERVICE_ACCOUNT" --role="roles/compute.viewer"
    gcloud projects add-iam-policy-binding  $PROJECT_ID --member="serviceAccount:$EXPORT_METRIC_SERVICE_ACCOUNT" --role="roles/monitoring.viewer"
    gcloud projects add-iam-policy-binding  $PROJECT_ID --member="serviceAccount:$EXPORT_METRIC_SERVICE_ACCOUNT" --role="roles/bigquery.dataEditor"
    gcloud projects add-iam-policy-binding  $PROJECT_ID --member="serviceAccount:$EXPORT_METRIC_SERVICE_ACCOUNT" --role="roles/bigquery.jobUser"
    
  5. Create the Pub/Sub topic:

    gcloud pubsub topics create $PUBSUB_TOPIC
    
  6. Deploy the Cloud Function:

    gcloud functions deploy mql_export_metrics \
    --trigger-topic $PUBSUB_TOPIC \
    --runtime python38 \
    --entry-point export_metric_data \
    --service-account=$EXPORT_METRIC_SERVICE_ACCOUNT
    
  7. Deploy the Cloud Scheduler job with a schedule to run it every 5 minutes:

    gcloud scheduler jobs create pubsub get_metric_mql \
    --schedule "*/5 * * * *" \
    --topic $PUBSUB_TOPIC \
    --message-body "Exporting metric..."
    

Verify the result

  1. Manually invoke the scheduled job:

    gcloud scheduler jobs run get_metric_mql
    
  2. Verify the setup by running the following query:

    bq query \
    --use_legacy_sql=false \
    "SELECT
    *
    FROM
    ${PROJECT_ID}.${BIGQUERY_DATASET}.${BIGQUERY_TABLE}
    LIMIT 10
    "
    

    If you have metrics data in Cloud Monitoring, they should be exported, and you will receive some query results.

    Here is an example if you export the metric compute.googleapis.com/instance/cpu/utilization:

    bq query \
    --use_legacy_sql=false \
    "
    SELECT
        EXTRACT(HOUR FROM pointData.timeInterval.end_time) AS extract_date,
        min(pointData.values.double_value) as min_cpu_util,
        max(pointData.values.double_value) as max_cpu_util,
        avg(pointData.values.double_value) as avg_cpu_util
    FROM
        ${PROJECT_ID}.${BIGQUERY_DATASET}.${BIGQUERY_TABLE}
    WHERE
        pointData.timeInterval.end_time > TIMESTAMP(DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY))
        AND  pointData.timeInterval.end_time <= CURRENT_TIMESTAMP
    
    GROUP BY extract_date
    ORDER BY extract_date
    "
    

Alternatively, you can open the BigQuery console and query the export data there.

Cleaning up

To avoid incurring charges to your Google Cloud account for the resources used in this tutorial, you can delete the resources that you created. You can either delete the entire project or delete individual resources.

Deleting a project has the following effects:

  • Everything in the project is deleted. If you used an existing project for this tutorial, when you delete it, you also delete any other work you've done in the project.
  • Custom project IDs are lost. When you created this project, you might have created a custom project ID that you want to use in the future. To preserve the URLs that use the project ID, delete selected resources inside the project instead of deleting the whole project.

If you plan to explore multiple tutorials, reusing projects can help you to avoid exceeding project quota limits.

Delete the project

The easiest way to eliminate billing is to delete the project you created for the tutorial.

  1. In the Cloud Console, go to the Manage resources page.
  2. In the project list, select the project that you want to delete and then click Delete.
  3. In the dialog, type the project ID and then click Shut down to delete the project.

Delete the resources

If you don't want to delete the project, you can delete the provisioned resources:

gcloud scheduler jobs delete get_metric_mql

gcloud functions delete mql_export_metrics

gcloud pubsub topics delete $PUBSUB_TOPIC

gcloud iam service-accounts delete $EXPORT_METRIC_SERVICE_ACCOUNT

bq rm --table ${BIGQUERY_DATASET}.${BIGQUERY_TABLE}

bq rm $BIGQUERY_DATASET

What's next

Submit a tutorial

Share step-by-step guides

Submit a tutorial

Request a tutorial

Ask for community help

Submit a request

View tutorials

Search Google Cloud tutorials

View tutorials

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see our Site Policies. Java is a registered trademark of Oracle and/or its affiliates.