BigQuery: Export Tables to Cloud Storage

Step 0: Authentication

How this works: You need a Google account that uses the same email address as your GCP Study Hub account. When you sign into that Google account, the system will be able to grant you temporary access to a GCP project when you start the lab.

Click the button below to start your lab:

Step 1: Open Google Cloud Console

Once your lab starts, use the Open Lab in GCP Console button from the lab portal to open the Google Cloud Console with your temporary lab project already selected.

Step 2: Create the Destination Bucket

Exports land in a bucket. You can download small query results straight from the results pane, but exporting a table goes through Cloud Storage.

1. Go to Navigation menu (☰) > Cloud Storage > Buckets and click CREATE
2. Name it beginning with gcpstudyhub-lab-bq-export- followed by your own numbers, for example gcpstudyhub-lab-bq-export-4821. Bucket names are globally unique, so if yours is taken, change the numbers
3. Set Location type to Region and pick us-central1 (Iowa). It defaults to a different region, so this one you have to change
4. Leave the rest as it comes and click CREATE. If a dialog about public access appears, click CONFIRM

Bucket creation is covered properly in Cloud Storage Basics, so it is compressed here. If any of it is unfamiliar, go back and review that lab.

Step 3: Create the Dataset and a Table to Export

You need something to export. Rather than loading a file, you can build a table straight from a query.

1. Go to Navigation menu (☰) > BigQuery
2. Create a dataset named lab_export with Data location set to us-central1 (Iowa), as you did in BigQuery Ingest from Cloud Storage. The default US multi-region is refused by the lab project's organization policy, here and in every lab
3. Click + to open a query tab, paste this in, and click Run:

CREATE OR REPLACE TABLE lab_export.employees AS
SELECT
  id AS employee_id,
  CONCAT('Employee ', CAST(id AS STRING)) AS name,
  CASE MOD(id, 4)
    WHEN 0 THEN 'Engineering'
    WHEN 1 THEN 'Research'
    WHEN 2 THEN 'Sales'
    ELSE 'Support'
  END AS department,
  DATE_ADD(DATE '2015-01-01', INTERVAL MOD(id * 7, 3650) DAY) AS start_date
FROM UNNEST(GENERATE_ARRAY(1, 100000)) AS id

GENERATE_ARRAY(1, 100000) produces the numbers 1 to 100,000 and UNNEST turns them into rows. Everything else is derived from that number: a name, one of four departments, and a start date scattered across a decade. The data is synthetic, but 100,000 rows is enough for exporting to feel like exporting, which a handful of sample rows would not be.

The results pane reports what it did rather than showing rows:

This statement created a new table named employees.

CREATE OR REPLACE TABLE ... AS SELECT writes the query's results into a table instead of just displaying them. The OR REPLACE matters here: run it twice and you still have 100,000 rows, not 200,000, because it rewrites the table rather than appending. Go to Datasets, click lab_export, then employees, and its Details tab confirms the row count.

Step 4: Export to CSV

Exporting is a property of the table, so it lives on the table's own page rather than anywhere in the query editor.

1. In the Explorer, click Datasets, then lab_export, then the employees table
2. Click Export / sync in the row of actions above the tabs, then Cloud Storage

An Export to Google Cloud Storage panel opens with three fields.

3. In GCS Location, type your bucket and a file name, with no gs:// in front of it: YOUR-BUCKET/employees.csv. A green tick appears once it is a valid location. Or click Browse, pick your bucket, and type the name into the Filename box at the bottom of the picker
4. Leave Export format on CSV and Compression on None
5. Click SAVE

Two notifications appear at the bottom of the screen, a moment apart:

"employees" export initiated.

"employees" export finished.

Read those two carefully, because the gap between them is the point. An export is a job, not an instant action. The console accepts your request, hands it to BigQuery and tells you it has started. Whether it worked is a separate message that arrives later. The second one carries an Open in Cloud Storage link.

Step 5: Read What Landed

Do not take the notification's word for it. Go and look at the file.

1. Click Open in Cloud Storage on the notification, or go to Cloud Storage > Buckets and open your bucket
2. Click employees.csv to open its Object details page, then click DOWNLOAD
3. Open it locally

employee_id,name,department,start_date
<100,000 rows, in whatever order the export wrote them>

The file is about 4 MB and 100,001 lines counting the header. Two things to notice.

First, there is a header row. BigQuery adds one on export, the mirror image of the load side where you told it to skip one.

Second, the rows might not be in employee_id order. An export makes no promise about row order, so yours might differ from a classmate's, or even be sequential. If the thing reading the file needs an order, export the result of a query with ORDER BY rather than the raw table.

Step 6: Compress the Export

Exports are usually large and usually going somewhere over a network, so compression is worth reaching for by default.

1. Go back to the employees table and click Export / sync > Cloud Storage again
2. GCS Location: YOUR-BUCKET/employees.csv.gz. If you use Browse, the last part, employees.csv.gz, is what goes in the Filename box
3. Leave Export format on CSV, and set Compression to GZIP
4. Click SAVE

Notice you had to type the .gz on the end yourself. BigQuery compresses the contents but it does not rename anything for you, and nothing stops you from writing gzipped bytes into a file called employees.csv. That file would then confuse everything that opened it. The suffix is a promise you are making to the next reader, and keeping it is your job.

Now compare the two objects in your bucket. employees.csv is 4,052,829 bytes; employees.csv.gz comes in around 880 KB, between a fourth and a fifth of the size. (The exact compressed size varies a little between runs, because it depends on the order the rows were written.) Text with this much repetition compresses well, and export files are usually text with a lot of repetition.

Step 7: Split the Export Across Multiple Files

A single export file is capped at 1 GB. In many cases in the real world, your tables will be bigger than that, and the way you deal with it is to put an asterisk, *, in the destination path.

1. Export / sync > Cloud Storage once more
2. GCS Location: YOUR-BUCKET/shard/employees-*.csv. Everything after the bucket name, shard/employees-*.csv, is the file name. Typing it is simplest here, because the shard folder does not exist yet for Browse to find
3. Leave the format on CSV and compression on None, then click SAVE

Open the shard folder in your bucket. The file is not called what you typed:

employees-000000000000.csv

The * became 000000000000. Even 100,000 rows only comes to about 4 MB, nowhere near the cap, so BigQuery needed exactly one file and the counter never got past zero. A table large enough to need more would have produced 000000000001, 000000000002 and so on alongside it.

Also notice that shard/ appeared without you creating it. Cloud Storage folders are just name prefixes: writing an object called shard/employees-000000000000.csv is what makes the folder seem to exist.

Step 8: A Column That Holds a List

So far every value has been a single number or string. BigQuery can also store a list inside one column. Build a table with one row per department, where each row carries all the names in that department. Back in your query tab:

CREATE OR REPLACE TABLE lab_export.teams AS
SELECT department,
       ARRAY_AGG(name ORDER BY employee_id LIMIT 5) AS members
FROM lab_export.employees
GROUP BY department

ARRAY_AGG is an aggregate like COUNT or SUM, but instead of collapsing the grouped rows into one number it collects them into a list. The LIMIT 5 inside it keeps just the first five names per department so the lists stay readable; without it, each one would hold 25,000 names. 100,000 employees in four departments become four rows.

Go to Datasets, then lab_export, then the new teams table, and look at the Schema tab:

Field name    Type      Mode
department    STRING    NULLABLE
members       STRING    REPEATED

The Mode column is the one to read. members is a STRING like department is, but its mode is REPEATED rather than NULLABLE. REPEATED means the column is an array: each row holds a list of strings instead of one string. The Schema tab writes that as type STRING plus mode REPEATED; in SQL the same thing is written ARRAY<STRING>, and you'll see that spelling in a moment.

Step 9: Export It the Way You Exported the First Table

1. On the teams table, click Export / sync > Cloud Storage
2. GCS Location: YOUR-BUCKET/teams.csv, with teams.csv in the Filename box if you use Browse
3. Leave Export format on CSV and Compression on None, then click SAVE

This time only one notification arrives:

"teams" export failed.

That is the entire message. No reason, no error code, and no link to go and find out.

Check your bucket if you like. There is no teams.csv in it. A failed export writes nothing at all, rather than leaving a half‑written file behind.

Step 10: Find Out Why

The reason exists, it is just not where the failure was announced. Every export is a job, and jobs keep their own records.

1. In the BigQuery Explorer pane, click Job history
2. Stay on the Personal history tab. Your export is the row at the top, with a red icon in the left column and EXTRACT in the Type column
3. Click its Job ID

An Extract job details panel opens, and the reason is the first thing in it:

Only simple types may be exported as CSV but variable has type ARRAY<STRING>

There it is. Below the error the same panel shows the Source table and the Destination URI you asked for, which is how you tell two similar‑looking failures apart when there are several.

Step 11: Export to a Format That Can Hold It

The answer is not a different setting on CSV, it is a different format. JSON nests, so it can carry a list inside a field.

1. On the teams table, click Export / sync > Cloud Storage
2. GCS Location: YOUR-BUCKET/teams.json, with teams.json in the Filename box if you use Browse
3. Set Export format to JSON (Newline delimited)
4. Click SAVE

This time you get both notifications again, ending in "teams" export finished. Download teams.json the same way you downloaded employees.csv in Step 5. The four lines may be in any order, but their contents are exactly this:

{"department":"Sales","members":["Employee 2","Employee 6","Employee 10","Employee 14","Employee 18"]}
{"department":"Support","members":["Employee 3","Employee 7","Employee 11","Employee 15","Employee 19"]}
{"department":"Engineering","members":["Employee 4","Employee 8","Employee 12","Employee 16","Employee 20"]}
{"department":"Research","members":["Employee 1","Employee 5","Employee 9","Employee 13","Employee 17"]}

As you can see, the lists are included, as real JSON arrays inside the field.

Avro would also have worked, and is the better choice when the data is going to another system rather than to a person, since it carries the schema with it and is far smaller. JSON is the option you can read, and you just read it.

The export failed and I dismissed the notification

The notification is the only place the failure is announced, but not the only place it is recorded. Go to Job history in the BigQuery Explorer pane, stay on Personal history, and look for a row with a red icon and EXTRACT in the Type column. Click its Job ID for the reason.

The GCS Location field will not accept my path

Leave off the gs:// prefix. The field wants your-bucket/folder/file.csv, and it shows a green tick once the value is valid.

Cannot read the exported file in the console

You cannot. Object details shows size and type but never contents, so click DOWNLOAD and open the file in a text editor. BigQuery writes exports as application/octet-stream, so opening the object URL downloads it too rather than displaying it.

My employees table has the wrong number of rows

Run the Step 3 statement again. CREATE OR REPLACE TABLE rewrites the table from scratch every time, so one run leaves you with exactly 100,000 rows. A plain INSERT would have appended instead.

Validation Fails

If your validation fails, check that:

• Your bucket name starts with gcpstudyhub-lab-bq-export-
• The dataset is named exactly lab_export, with an underscore
• employees has 100,000 rows and teams has 4, one per department
• The JSON export is of teams, not employees. The point is the array
• Your sharded file name ends in -000000000000.csv. If it does not, the * was missing from the GCS Location in Step 7
• You are using the same email address for validation that you used to start the lab