Monday, June 29, 2026

Priorities

Work Log

Export BigQuery results into Google Drive — DA-1348

Situation

There is a use case where the data team needs to export BigQuery query results into Google Drive as CSV files. These files then will be consumed by the related stakeholders for their analysis and reporting. At the moment, that integration does not already exist. Therefore, I need to develop it because there will be similar use cases in the future.

Tasks

Create an integration for exporting BigQuery results into Google Drive.

Actions

The direct integration between BigQuery and Google Drive is still in Beta stage. Therefore, I achieve this with the following methods:

  1. Export BigQuery results into Cloud Storage as CSV files.
  2. Download the CSV files into the container storage.
  3. Sync the CSV files to Google Drive.

Find the script of above method below:

#!/bin/bash

GCS_BUCKET="gs://<company-bucket>"
DRIVE_REMOTE="gdrive"
GCS_GLOB="${GCS_BUCKET}/${OUTPUT_NAME}/${OUTPUT_NAME}_*.csv"

bq query --use_legacy_sql=false \
"EXPORT DATA OPTIONS (
   uri      = '${GCS_GLOB}',
   format   = 'CSV',
   header   = true,
   overwrite = true
 ) AS
 ${BQ_QUERY}"

TMP="$(mktemp -d)"
trap 'rm -rf "$TMP"' EXIT
gcloud storage cp "${GCS_GLOB}" "${TMP}/"
rclone --config "$RCLONE_CONFIG" copy "${TMP}" "${DRIVE_REMOTE}:${OUTPUT_NAME}"

Result

As a result, I have a script that able to export BigQuery results into Google Drive. Furthermore, I need to create a container image so that it can be run as a container, and then schedule within Airflow.

Remarks

Of course, the script will be using service account for exporting CSV files into Google Drive. However, it can only export into the Shared Drive because the service account does not have storage.

Blockers

N/A

Carry-overs

Reflection

N/A