# Load data into BigQuery

This document shows how to use Managed Service for Apache Spark to run a Spark job that loads processed
data from Cloud Storage into a BigQuery table. Managed Service for Apache Spark streamlines this process by managing the Spark environment and the necessary connectors.

> [!IMPORTANT]
> See [Transform data](https://docs.cloud.google.com/managed-spark/docs/guides/transform-data.md.txt) for a guide to processing data using PySpark on Managed Service for Apache Spark.

## Before you begin

1. [](https://docs.cloud.google.com/storage/docs/creating-buckets.md.txt)Create a Cloud Storage bucket.
2. [Create a Managed Service for Apache Spark
   cluster](https://docs.cloud.google.com/managed-spark/docs/guides/create-cluster) that uses image version `2.1` or later.
3. [Create a BigQuery dataset](https://docs.cloud.google.com/bigquery/docs/datasets.md.txt).

<br />

## Prepare the PySpark script

1. Create a local Python file named `load_analytics_data.py`.

2. Add the following code to the file. This script reads data from a Cloud Storage path, performs an aggregation, and writes the result to BigQuery.

   ```python
   from pyspark.sql import SparkSession
   from pyspark.sql.functions import sum as _sum
   import sys

   # --- Configuration ---
   gcs_bucket = "BUCKET_NAME"
   bq_project = "PROJECT_ID"
   bq_dataset = "DATASET"
   bq_table = "corpus_word_counts"

   # --- Paths ---
   processed_path = f"gs://{gcs_bucket}/processed/processed_data"
   temp_gcs_path = f"{gcs_bucket}"

   # --- Spark Session Initialization ---
   spark = SparkSession.builder \
      .appName("Dataproc-BigQuery-Load") \
      .config("spark.hadoop.google.cloud.bigdata.connector.temporary.gcs.bucket", temp_gcs_path) \
      .getOrCreate()

   # --- Read Processed Data from Cloud Storage ---
   processed_df = spark.read.parquet(processed_path)

   # --- Final Aggregation for Analytics-Ready Stage ---
   analytics_df = processed_df.groupBy("corpus") \
      .agg(_sum("word_count_int").alias("total_word_count")) \
      .orderBy("corpus")

   print("Aggregated Analytics-Ready data:")
   analytics_df.show()

   # --- Write DataFrame to BigQuery ---
   print(f"Writing data to BigQuery table: {bq_dataset}.{bq_table}")

   analytics_df.write \
      .format("bigquery") \
      .option("table", f"{bq_project}.{bq_dataset}.{bq_table}") \
      .mode("append") \
      .save()

   print("Successfully wrote data to BigQuery.")

   # --- Stop Spark Session ---
   spark.stop()
   ```
3. Replace the following placeholders:

   - `BUCKET_NAME`: the name of your Cloud Storage bucket.
   - `PROJECT_ID`: your Google Cloud project ID.
4. Upload the `load_analytics_data.py` script to your Cloud Storage bucket.

## Submit the Managed Service for Apache Spark job

Submit the PySpark script as a job to your Managed Service for Apache Spark cluster.

1. In a terminal, run the `gcloud dataproc jobs submit pyspark` command:

   ```bash
   gcloud dataproc jobs submit pyspark gs://YOUR_BUCKET_NAME/scripts/load_analytics_data.py \
       --cluster=CLUSTER_NAME \
       --region=REGION
   ```
2. Replace the following placeholders:

   - `BUCKET_NAME`: the name of your Cloud Storage bucket.
   - `CLUSTER_NAME`: the name of your Managed Service for Apache Spark cluster.
   - `REGION`: the region where your cluster is located.

   The command submits a PySpark job to Managed Service for Apache Spark. Managed Service for Apache Spark workers
   fetch the script from the specified Cloud Storage path and execute it on the
   cluster.

## Verify the data load

1. In the Google Cloud console, go to the Managed Service for Apache Spark **Jobs** page to monitor the
   job's execution and view driver output logs.

2. After the job completes, go to the **BigQuery** page.

3. In the Explorer panel, find your project and dataset, then select the
   `corpus_word_counts` table.

4. Click the **Preview** tab to inspect the loaded data.

## How the Spark-BigQuery connector works

The Spark-BigQuery Connector enables Spark applications to read from and write
to BigQuery. On Managed Service for Apache Spark clusters with image versions 2.1 or later, the
connector is pre-installed.

The connector uses an indirect write method to load data. This method leverages
both Managed Service for Apache Spark and BigQuery for high performance.

1. The Spark job on the Managed Service for Apache Spark cluster writes the final DataFrame to a
   temporary location in a Cloud Storage bucket.

2. After the write to Cloud Storage completes, the connector triggers a
   BigQuery Load Job.

3. BigQuery ingests the data from the temporary Cloud Storage location into the
   target table.

This indirect approach decouples the Spark computation from the BigQuery
ingestion. This decoupling allows each service to operate efficiently and
ensures high throughput for large data loads.

## What's next

- Learn more about the [Spark-BigQuery connector](https://docs.cloud.google.com/managed-spark/docs/tutorials/bigquery-connector-spark-example.md.txt).
- Learn how to [query and visualize data in BigQuery](https://docs.cloud.google.com/bigquery/docs/visualize-data-colab.md.txt).
- Explore how to [visualize BigQuery data using Looker](https://docs.cloud.google.com/bigquery/docs/visualize-looker-studio.md.txt).