---
title: 'Log streaming: Google BigQuery'
summary: null
url: >-
  https://www.fastly.com/documentation/guides/integrations/logging-endpoints/data-warehouses-and-analytics/log-streaming-google-bigquery
---

Fastly's [Real-Time Log Streaming](https://www.fastly.com/documentation/guides/integrations/streaming-logs/about-fastlys-realtime-log-streaming-features) feature can send log files to [BigQuery](https://cloud.google.com/bigquery/), Google's managed enterprise data warehouse.

> **NOTE:** 
>
> Fastly does not provide direct support for third-party services. Read [Fastly's Terms of Service](https://www.fastly.com/terms) for more information.
>
>

## Prerequisites

Before adding BigQuery as a logging endpoint for Fastly services you will need to:

- Register for a [Google Cloud Platform](https://cloud.google.com/) (GCP) account.
- Create a [service account](https://www.fastly.com/documentation/guides/integrations/logging-endpoints/data-warehouses-and-analytics/log-streaming-google-bigquery#creating-a-service-account) on Google's website.
- Use Google IAM service account impersonation or key-based access to configure the access method that Fastly uses to send logs.
- [Enable the BigQuery API](https://www.fastly.com/documentation/guides/integrations/logging-endpoints/data-warehouses-and-analytics/log-streaming-google-bigquery#enabling-the-bigquery-api).
- [Create a BigQuery dataset](https://www.fastly.com/documentation/guides/integrations/logging-endpoints/data-warehouses-and-analytics/log-streaming-google-bigquery#creating-the-bigquery-dataset).
- [Add a BigQuery table](https://www.fastly.com/documentation/guides/integrations/logging-endpoints/data-warehouses-and-analytics/log-streaming-google-bigquery#adding-a-bigquery-table).

### Creating a service account

Google Cloud Platform (GCP) uses [service accounts](https://cloud.google.com/iam/docs/service-account-overview) for third-party application authentication and role-based access to Google Cloud resources. To create a new service account, follow the instructions in the [Google Cloud documentation](https://cloud.google.com/iam/docs/creating-managing-service-accounts#creating).

When creating the service account for logging to BigQuery, the service account must be assigned the [`Big Query Data Editor`](https://cloud.google.com/iam/docs/understanding-roles#bigquery-roles) role to write to the table you use for Fastly logging. You can assign the `Big Query Data Editor` role to the entire project using [Google Console](https://console.cloud.google.com/) or apply the `roles/bigquery.dataEditor` to the project using [Google Terraform - IAM Project](https://registry.terraform.io/providers/hashicorp/google/latest/docs/resources/google_project_iam).

![Create service account panel](/img/service_account_bigquery.png)

### Configure access for the service account

Fastly requires access to write logs to your BigQuery table. You can use either Google IAM service account impersonation or key-based access to allow Fastly to send logs to BigQuery.

#### Configuring Google IAM service account impersonation

If you elect to use Google IAM service account impersonation to avoid storing keys with Fastly, you'll need to specify a service account name and a project ID when adding GCS as your logging endpoint. The service account ID comes before the `@` in the service account email and the project ID immediately after. For example, if your service account email is `my-name@projectid.iam.gserviceaccount.com`, the service account ID is `my-name` and the project ID is `projectid`. Our guide to [creating an Google IAM role](https://www.fastly.com/documentation/guides/integrations/streaming-logs/configuring-google-iam-service-account-impersonation-for-fastly-logging) provides specific details on configuring this feature.

#### Configuring key-based access

If you elect to use key-based access, refer to Google's guide on [generating a service account credential](https://cloud.google.com/iam/docs/keys-create-delete#creating_service_account_keys).

When configuring key-based access, you must select the JSON format. Upon saving the configuration, a JSON file will be downloaded to your computer, containing the credentials for the GCS service account you just created. Open the file with a text editor and make a note of the `private_key` and `client_email`.

### Enabling the BigQuery API

To send your Fastly logs to your BigQuery table, you'll need to enable the BigQuery API in the Google Cloud Platform [API Manager](https://console.cloud.google.com/apis/library).

### Creating the BigQuery dataset

After you've enabled the BigQuery API, follow these instructions to create a BigQuery dataset:

1. Open the [BigQuery page](https://console.cloud.google.com/bigquery) in the Cloud Console.
2. In the **Explorer** panel, select the project where you want to create the dataset.
3. In the details panel, click **Create dataset**.
4. In the **Dataset ID** field, enter a name for the dataset (e.g., `fastly_bigquery`).
5. Click **Create dataset**.

### Adding a BigQuery table

After you've created the BigQuery dataset, you'll need to add a BigQuery table. There are four ways of creating the schema for the table:

- Edit the schema using the BigQuery web interface.
- Edit the schema using the text field in the BigQuery web interface.
- Use an existing table.
- Set the table to [automatically detect the schema](https://cloud.google.com/bigquery/docs/schema-detect).

> **IMPORTANT:** Setting the table to automatically detect the schema may give unpredictable results.

Follow these instructions to add a BigQuery table:

1. Open the [BigQuery page](https://console.cloud.google.com/bigquery) in the Cloud Console.

2. In the Explorer panel, expand your project and select the BigQuery dataset you created [previously](https://www.fastly.com/documentation/guides/integrations/logging-endpoints/data-warehouses-and-analytics/log-streaming-google-bigquery#creating-the-bigquery-dataset).

3. In the **Source** section, select **Empty Table** from the  **Create table from:** menu.

   ![The BigQuery create table page](/img/bigquery-table.png)

4. In the **Table name** field, enter a name for the table (e.g., `logs`).

5. In the **Schema** section of the BigQuery website, use the interface to add fields and complete the schema. Check out the [example schema section](https://www.fastly.com/documentation/guides/integrations/logging-endpoints/data-warehouses-and-analytics/log-streaming-google-bigquery#example-schema) for details.

6. Click **Create Table**.

## Adding BigQuery as a logging endpoint

Follow these instructions to add BigQuery as a logging endpoint. As part of configuration, you can elect to configure Google IAM role-based service account impersonation to avoid storing secrets. Read our guide on [creating a Google IAM role](https://www.fastly.com/documentation/guides/integrations/streaming-logs/configuring-google-iam-service-account-impersonation-for-fastly-logging) for more information on this feature.

### Cdn Services

1.   Review the information in our guide to [setting up remote log streaming](/guides/integrations/streaming-logs/setting-up-remote-log-streaming).

2. In the Google BigQuery area, click **Create endpoint**.
3. Fill out the **Create a BigQuery endpoint** fields as follows:
   -   In the **Name** field, enter a human-readable name for the endpoint.

   -   In the **Placement** area, select where the logging call should be placed in the generated VCL. Valid values are **Format Version Default** and **None**. Read our guide on [changing log placement](/guides/integrations/streaming-logs/changing-log-placement) for more information.

   - In the **Log format** field, enter the data to send to BigQuery. Check out the [example format section](https://www.fastly.com/documentation/guides/integrations/logging-endpoints/data-warehouses-and-analytics/log-streaming-google-bigquery#example-format) for details.
   - In the **Access Method** area, select how Fastly will access Google resources for purposes of log delivery. Select either **User Credentials** or **IAM Role**.
   - If you selected **User Credentials**, fill out the following fields:
     - In the **Email** field, enter the `client_email` address associated with the BigQuery service account.
     - In the **Secret key** field, enter the value of the [`private_key`](https://www.fastly.com/documentation/guides/integrations/logging-endpoints/data-warehouses-and-analytics/log-streaming-google-bigquery#configuring-key-based-access) associated with your BigQuery service account.
     - In the **Project ID** field, enter the ID of your Google Cloud Platform project.
     - In the **Dataset** field, enter the name of your BigQuery dataset.
     - In the **Table** field, enter the name of your BigQuery table.
     - _(Optional)_ In the **Template** field, enter an `strftime` compatible string to use as the [template suffix for your table](https://cloud.google.com/bigquery/streaming-data-into-bigquery#template-tables).
   - If you selected **IAM Role**, fill out the following fields:
     - In the **Service Account Name** field, enter the name of the service account email address you selected when configuring Google IAM service account impersonation.
     - In the **Project ID** field, enter the ID of your Google Cloud Platform project.
     - In the **Dataset** field, enter the name of your BigQuery dataset.
     - In the **Table** field, enter the name of your BigQuery table.
     - _(Optional)_ In the **Template** field, enter an `strftime` compatible string to use as the [template suffix for your table](https://cloud.google.com/bigquery/streaming-data-into-bigquery#template-tables).
     -   *(Optional)* From the **Processing region** menu, select a geographic region where logs are processed before being sent to the logging endpoint. Our guide on [regional log aggregation](/guides/integrations/streaming-logs/setting-up-regional-log-aggregation) provides more information.

4.   Click **Create** to create the new logging endpoint.

5.   From the **Activate** menu, select **Activate on Production** to deploy your configuration changes.

### Example format

Data sent to BigQuery must be serialized as a JSON object, and every field in the JSON object must map to a string in your table's schema. The JSON can have nested data in it (e.g., the value of a key in your object can be another object). Here's an example format string for sending data to BigQuery:

```plain
{
  "timestamp": "%{strftime(\{"%Y-%m-%dT%H:%M:%S"\}, time.start)}V",
  "client_ip": "%{req.http.Fastly-Client-IP}V",
  "geo_country": "%{client.geo.country_name}V",
  "geo_city": "%{client.geo.city}V",
  "host": "%{if(req.http.Fastly-Orig-Host, req.http.Fastly-Orig-Host, req.http.Host)}V",
  "url": "%{json.escape(req.url)}V",
  "request_method": "%{json.escape(req.method)}V",
  "request_protocol": "%{json.escape(req.proto)}V",
  "request_referer": "%{json.escape(req.http.referer)}V",
  "request_user_agent": "%{json.escape(req.http.User-Agent)}V",
  "response_state": "%{json.escape(fastly_info.state)}V",
  "response_status": %{resp.status}V,
  "response_reason": %{if(resp.response, "%22"+json.escape(resp.response)+"%22", "null")}V,
  "response_body_size": %{resp.body_bytes_written}V,
  "fastly_server": "%{json.escape(server.identity)}V",
  "fastly_is_edge": %{if(fastly.ff.visits_this_service == 0, "true", "false")}V
}
```

#### Example schema

The BigQuery schema for the example format shown above would look something like this:

```plain
timestamp:TIMESTAMP,client_ip:STRING,geo_country:STRING,geo_city:STRING,host:STRING,url:STRING,request_method:STRING,request_protocol:STRING,request_referer:STRING,request_user_agent:STRING,response_state:STRING,response_status:STRING,response_reason:STRING,response_body_size:INTEGER,fastly_server:STRING,fastly_is_edge:BOOLEAN
```

### Compute Services

1.   Review the information in our guide to [setting up remote log streaming for Compute](/guides/integrations/streaming-logs/setting-up-remote-log-streaming-for-compute). Additionally, our developer documentation provides more [information about logging](/guides/integrations/non-fastly-services/developer-guide-logging/) with Compute code written in our [supported languages](/reference/compute/sdks/).

2. In the Google BigQuery area, click **Create endpoint**.

3. Fill out the **Create a BigQuery endpoint** fields as follows:
   -   In the **Name** field, enter the endpoint name you specified in your Compute code. For example, in our [Rust code example](/guides/compute/developer-guides/rust/#logging), the name is `my_endpoint_name`.

   - In the **Access Method** area, select how Fastly will access Google resources for purposes of log delivery. Select either **User Credentials** or **IAM Role**.
   - If you selected **User Credentials**, fill out the following fields:
     - In the **Email** field, enter the `client_email` address associated with the BigQuery service account.
     - In the **Secret key** field, enter the value of the [`private_key`](https://www.fastly.com/documentation/guides/integrations/logging-endpoints/data-warehouses-and-analytics/log-streaming-google-bigquery#configuring-key-based-access) associated with your BigQuery service account.
     - In the **Project ID** field, enter the ID of your Google Cloud Platform project.
     - In the **Dataset** field, enter the name of your BigQuery dataset.
     - In the **Table** field, enter the name of your BigQuery table.
     - _(Optional)_ In the **Template** field, enter an `strftime` compatible string to use as the [template suffix for your table](https://cloud.google.com/bigquery/streaming-data-into-bigquery#template-tables).
   - If you selected **IAM Role**, fill out the following fields:
     - In the **Service Account Name** field, enter the name of the service account email address you selected when configuring Google IAM service account impersonation.
     - In the **Project ID** field, enter the ID of your Google Cloud Platform project.
     - In the **Dataset** field, enter the name of your BigQuery dataset.
     - In the **Table** field, enter the name of your BigQuery table.
     - _(Optional)_ In the **Template** field, enter an `strftime` compatible string to use as the [template suffix for your table](https://cloud.google.com/bigquery/streaming-data-into-bigquery#template-tables).

4.   Click **Create** to create the new logging endpoint.

5.   From the **Activate** menu, select **Activate on Production** to deploy your configuration changes.

### Recommended log format

Data sent to BigQuery must be serialized as a JSON object, and every field in the JSON object must map to a field in your table's schema. The JSON can have nested data in it (e.g., the value for a key in your object can be another object). Here's an example format string for sending data to BigQuery:

```json
{
  "client_ip": "127.0.0.1",
  "timestamp": "2022-05-17 15:09:24.037547 UTC",
  "geo_country": "USA",
  "geo_city": "boston",
  "host": "curiously-selected-polecat.edgecompute.app",
  "url": "https://curiously-selected-polecat.edgecompute.app/",
  "request_method": "GET",
  "request_protocol": "https",
  "request_referer": "",
  "request_user_agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/101.0.0.0 Safari/537.36",
  "response_status": "200",
  "response_reason": "OK",
  "response_body_size": 1234,
  "fastly_server": "IAD"
}
```

#### Example schema

The BigQuery schema for the example format shown above would look like this:

```plain
client_ip:STRING,timestamp:TIMESTAMP,geo_country:STRING,geo_city:STRING,host:STRING,url:STRING,request_method:STRING,request_protocol:STRING,request_referer:STRING,request_user_agent:STRING,response_status:STRING,response_reason:STRING,response_body_size:INTEGER,fastly_server:STRING
```

## Related content

- [API reference: BigQuery log streaming](https://www.fastly.com/documentation/reference/api/logging/bigquery/)
- [CLI reference: BigQuery log streaming](https://www.fastly.com/documentation/reference/cli/logging/bigquery/)