Export asset metadata to BigQuery

This document shows you how to export an asset snapshot from your organization, folder, or project to a BigQuery table, and then run data analysis on your inventory. BigQuery provides a SQL-like experience for users to analyze data and produce meaningful insights without the use of custom scripts.

Before you begin

1. Enable the Cloud Asset Inventory API in the project you're running Cloud Asset Inventory commands from.

   Enable the Cloud Asset Inventory API

2. Make sure your account has the correct role to call the Cloud Asset Inventory API. For individual permissions for each call type, see Permissions.

3. Create a BigQuery dataset to export to, if you don't have one already.

Limitations

• When exporting BigQuery table data, not all fields are supported by Cloud Asset Inventory.

• Frequently changing asset fields such as numBytes, numLongTermBytes, numPhysicalBytes, and numRows might be exported with a null value.

• Exporting to BigQuery clustered tables isn't supported.

• Appending the export output to an existing table isn't supported unless you export to a partitioned table. The destination table must be empty or you must overwrite it. To overwrite it, use the --output-bigquery-force flag with the gcloud CLI, or use "force": true with the REST API.

• Google Kubernetes Engine (GKE) resource types, except for container.googleapis.com/Cluster and container.googleapis.com/NodePool, are not supported when exporting to separate tables for each resource type.

• Cloud Asset Inventory rejects export requests if a previous request to the same destination started less than 15 minutes ago and is still running. However, if an export has taken longer than 15 minutes to complete, it is marked as done and new export requests to the same destination are permitted.

• The ACCESS_POLICY content type can only be exported at the organization level.

• If the table you're exporting to already exists and is in the process of being exported to, a 400 error is returned.

Casing

Field names use different casing conventions depending on the requested content type and export settings:

• For the RESOURCE content type when you export assets to tables per asset type, there are no spaces between words, and the first letter of each new word after the first is capitalized. For example,assetType.

• For all other content types, the field name is lowercase, with spaces replaced by underscores. For example, asset_type.

BigQuery schemas used for export

Every BigQuery table is defined by a schema that describes the column names, data types, and other information. Setting the content type for an export determines the schema for your table:

• Resource or unspecified: When you set the content type to RESOURCE or don't specify it, and you set the per-asset-type flag to false or don't use it, you create a BigQuery table that has the following schema.

  Resource schema

  [
    {
      "mode": "NULLABLE",
      "name": "name",
      "type": "STRING"
    },
    {
      "mode": "NULLABLE",
      "name": "asset_type",
      "type": "STRING"
    },
    {
      "fields": [
        {
          "mode": "NULLABLE",
          "name": "version",
          "type": "STRING"
        },
        {
          "mode": "NULLABLE",
          "name": "discovery_document_uri",
          "type": "STRING"
        },
        {
          "mode": "NULLABLE",
          "name": "discovery_name",
          "type": "STRING"
        },
        {
          "mode": "NULLABLE",
          "name": "resource_url",
          "type": "STRING"
        },
        {
          "mode": "NULLABLE",
          "name": "parent",
          "type": "STRING"
        },
        {
          "mode": "NULLABLE",
          "name": "data",
          "type": "STRING"
        },
        {
          "mode": "NULLABLE",
          "name": "location",
          "type": "STRING"
        }
      ],
      "mode": "NULLABLE",
      "name": "resource",
      "type": "RECORD"
    },
    {
      "mode": "REPEATED",
      "name": "ancestors",
      "type": "STRING"
    },
    {
      "mode": "NULLABLE",
      "name": "update_time",
      "type": "TIMESTAMP"
    }
  ]

  The resource.data column is the resource metadata represented as a JSON string.

  When you set the content type to RESOURCE or don't set the content type, and set the per-asset-type flag to true, you create separate tables for each asset type. The schema of each table includes RECORD-type columns mapped to the nested fields in the Resource.data field of that asset type (up to the 15 nested levels that BigQuery supports). For example tables, see export-assets-examples in the Google Cloud console.

• IAM policy: When you set the content type to IAM_POLICY in the REST API or iam-policy in the gcloud CLI, you create a BigQuery table that has the following schema.

  IAM policy schema

  [
    {
      "mode": "NULLABLE",
      "name": "name",
      "type": "STRING"
    },
    {
      "mode": "NULLABLE",
      "name": "asset_type",
      "type": "STRING"
    },
    {
      "fields": [
        {
          "mode": "NULLABLE",
          "name": "version",
          "type": "INTEGER"
        },
        {
          "fields": [
            {
              "mode": "NULLABLE",
              "name": "role",
              "type": "STRING"
            },
            {
              "mode": "REPEATED",
              "name": "members",
              "type": "STRING"
            },
            {
              "fields": [
                {
                  "mode": "NULLABLE",
                  "name": "expression",
                  "type": "STRING"
                },
                {
                  "mode": "NULLABLE",
                  "name": "title",
                  "type":