Export a table to a JSON file

Exports a table to a newline-delimited JSON file in a Cloud Storage bucket.

Code sample

C#

Before trying this sample, follow the C# setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery C# API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

using Google.Cloud.BigQuery.V2;
using System;

public class BigQueryExtractTableJson
{
    public void ExtractTableJson(
        string projectId = "your-project-id",
        string bucketName = "your-bucket-name")
    {
        BigQueryClient client = BigQueryClient.Create(projectId);
        string destinationUri = $"gs://{bucketName}/shakespeare.json";
        var jobOptions = new CreateExtractJobOptions()
        {
            DestinationFormat = FileFormat.NewlineDelimitedJson
        };
        BigQueryJob job = client.CreateExtractJob(
            projectId: "bigquery-public-data",
            datasetId: "samples",
            tableId: "shakespeare",
            destinationUri: destinationUri,
            options: jobOptions
        );
        job = job.PollUntilCompleted().ThrowOnAnyError();  // Waits for the job to complete.
        Console.Write($"Exported table to {destinationUri}.");
    }
}

Go

Before trying this sample, follow the Go setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Go API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

import (
	"context"
	"fmt"

	"cloud.google.com/go/bigquery"
)

// exportTableAsJSON demonstrates using an export job to
// write the contents of a table into Cloud Storage as newline delimited JSON.
func exportTableAsJSON(projectID, gcsURI string) error {
	// projectID := "my-project-id"
	// gcsURI := "gs://mybucket/shakespeare.json"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %w", err)
	}
	defer client.Close()

	srcProject := "bigquery-public-data"
	srcDataset := "samples"
	srcTable := "shakespeare"

	gcsRef := bigquery.NewGCSReference(gcsURI)
	gcsRef.DestinationFormat = bigquery.JSON

	extractor := client.DatasetInProject(srcProject, srcDataset).Table(srcTable).ExtractorTo(gcsRef)
	// You can choose to run the job in a specific location for more complex data locality scenarios.
	// Ex: In this example, source dataset and GCS bucket are in the US.
	extractor.Location = "US"

	job, err := extractor.Run(ctx)
	if err != nil {
		return err
	}
	status, err := job.Wait(ctx)
	if err != nil {
		return err
	}
	if err := status.Err(); err != nil {
		return err
	}
	return nil
}

Java

Before trying this sample, follow the Java setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Java API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

import com.google.cloud.RetryOption;
import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.FormatOptions;
import com.google.cloud.bigquery.Job;
import com.google.cloud.bigquery.Table;
import com.google.cloud.bigquery.TableId;
import org.threeten.bp.Duration;

public class ExtractTableToJson {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "bigquery-public-data";
    String datasetName = "samples";
    String tableName = "shakespeare";
    String bucketName = "my-bucket";
    String destinationUri = "gs://" + bucketName + "/path/to/file";
    // For more information on export formats available see:
    // https://cloud.google.com/bigquery/docs/exporting-data#export_formats_and_compression_types
    // For more information on Job see:
    // https://googleapis.dev/java/google-cloud-clients/latest/index.html?com/google/cloud/bigquery/package-summary.html

    // Note that FormatOptions.json().toString() is not "JSON" but "NEWLINE_DELIMITED_JSON"
    // Using FormatOptions Enum for this will prevent problems with unexpected format names.
    String dataFormat = FormatOptions.json().getType();

    extractTableToJson(projectId, datasetName, tableName, destinationUri, dataFormat);
  }

  // Exports datasetName:tableName to destinationUri as a JSON file
  public static void extractTableToJson(
      String projectId,
      String datasetName,
      String tableName,
      String destinationUri,
      String dataFormat) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      TableId tableId = TableId.of(projectId, datasetName, tableName);
      Table table = bigquery.getTable(tableId);

      Job job = table.extract(dataFormat, destinationUri);

      // Blocks until this job completes its execution, either failing or succeeding.
      Job completedJob =
          job.waitFor(
              RetryOption.initialRetryDelay(Duration.ofSeconds(1)),
              RetryOption.totalTimeout(Duration.ofMinutes(3)));
      if (completedJob == null) {
        System.out.println("Job not executed since it no longer exists.");
        return;
      } else if (completedJob.getStatus().getError() != null) {
        System.out.println(
            "BigQuery was unable to extract due to an error: \n" + job.getStatus().getError());
        return;
      }
      System.out.println(
          "Table export successful. Check in GCS bucket for the " + dataFormat + " file.");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Table extraction job was interrupted. \n" + e.toString());
    }
  }
}

Python

Before trying this sample, follow the Python setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Python API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

import bigframes.pandas as bpd
from google.cloud import bigquery

# Set partial ordering mode for BigQuery DataFrames.
bpd.options.bigquery.ordering_mode = "partial"


def extract_table_json_bigframes(bucket_name: str = "my-bucket") -> None:
    """Exports a BigQuery table to Cloud Storage in newline-delimited JSON format using BigQuery DataFrames."""
    project = "bigquery-public-data"
    dataset_id = "samples"
    table_id = "shakespeare"
    destination_uri = f"gs://{bucket_name}/shakespeare-*.json"

    full_table_id = f"{project}.{dataset_id}.{table_id}"
    df = bpd.read_gbq(full_table_id)
    df.to_json(destination_uri, orient="records", lines=True)
    print(f"Exported {full_table_id} to {destination_uri} using BigQuery DataFrames.")


def extract_table_json_client(bucket_name: str = "my-bucket") -> None:
    """Exports a BigQuery table to Cloud Storage in newline-delimited JSON format using google-cloud-bigquery."""
    project = "bigquery-public-data"
    dataset_id = "samples"
    table_id = "shakespeare"
    destination_uri = f"gs://{bucket_name}/shakespeare-*.json"

    client = bigquery.Client()
    dataset_ref = bigquery.DatasetReference(project, dataset_id)
    table_ref = dataset_ref.table(table_id)
    job_config = bigquery.job.ExtractJobConfig()
    job_config.destination_format = (
        bigquery.job.DestinationFormat.NEWLINE_DELIMITED_JSON
    )

    extract_job = client.extract_table(
        table_ref,
        destination_uri,
        job_config=job_config,
        # Location must match that of the source table.
        location="US",
    )  # API request
    extract_job.result()  # Waits for job to complete.

    print(
        f"Exported {project}:{dataset_id}.{table_id} to {destination_uri} using client library."
    )


# [Preferred] Run using BigQuery DataFrames:
# extract_table_json_bigframes("my-bucket")

# Alternatively, run using google-cloud-bigquery client library:
# extract_table_json_client("my-bucket")

What's next

To search and filter code samples for other Google Cloud products, see the Google Cloud sample browser.