BigQuery: Ingest CSV Data from Cloud Storage

Lab Introduction

In BigQuery Basics, you loaded a CSV by choosing a file from your own computer. That is often the quickest way to get a table, at least a small one, into BigQuery, but there are many reasons you might instead want to load data from Cloud Storage. Maybe the data was already there, or maybe your teammate uploaded a file, or maybe you have a system or pipeline that outputs data to a bucket.

So in this lab we are going to go over loading data into BigQuery from a Cloud Storage bucket. BigQuery can read a file straight out of a bucket and load it into a table. Nothing is downloaded to your machine on the way, which means you don't have to worry about the size of the file.

This is also why Cloud Storage is often used as the data lake in GCP. It's where your data first lands before it gets ingested into your warehouse (which is usually BigQuery).

One thing you'll learn in this lab is the importance of the schema matching the source data. You give BigQuery a schema, which is the list of columns and the type of each one, and you tell it whether the first line of the file holds column names rather than data. The schema and the actual file need to match. When the two do not agree, the load fails. You will see that happen, and then load the data anyway, without editing a file that in a real pipeline you would not own.

Requirements to Pass This Lab

A bucket starting with gcpstudyhub-lab-bq-ingest- containing employees.csv
A BigQuery dataset named lab_ingest
A table employees with 3 rows loaded from the bucket
A table contractors that has a contract_end_date column
contractors also has 3 rows, with contract_end_date empty

Before You Begin

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:

Start Lab

Instructions

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: Get the Data into a Bucket

This file stands in for data an application would have written for you overnight. Download it:

Download employees.csv

Open it in any text editor before you go on, because its shape is the whole point of this lab:

employee_id,name,department,start_date
1,Ada,Engineering,2020-01-15
2,Grace,Research,2019-06-01
3,Alan,Engineering,2021-03-22

One header row, then three rows of data, and four columns. Remember that number.

Now put it in a bucket:

Go to Navigation menu (☰) > Cloud Storage > Buckets and click CREATE
Under Get started, name it beginning with gcpstudyhub-lab-bq-ingest- followed by your own numbers, for example gcpstudyhub-lab-bq-ingest-4821. The console asks for a globally unique name, so if yours is taken, change the numbers
Click Continue. Under Choose where to store your data, set Location type to Region and pick us-central1 (Iowa)
Leave everything else as it comes and click CREATE. If a dialog about public access appears, click CONFIRM
In the bucket's Objects tab, click Upload and choose Upload files from the menu, then select the employees.csv you just downloaded

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

A dataset is the container that holds tables. Tables cannot exist outside one.

Go to Navigation menu (☰) > BigQuery
In the Explorer pane, click Datasets, then + Create dataset
Dataset ID: lab_ingest. The field allows letters, numbers and underscores, which is why it is not lab-ingest
Data location: select us-central1 (Iowa), the same region as your bucket. A load reads from Cloud Storage and writes into BigQuery, and keeping the two in the same region means the data never crosses a region boundary, which is faster and avoids transfer charges. It is also the only option that works here: the lab project has an organization policy restricting where resources can live, and the default US multi-region is not on its list
Skip Tags, then expand Advanced options and find Default table expiration. The other settings in there (Case Insensitive, Default Collation, Default Rounding Mode, Storage Billing Model) set defaults that every table in the dataset inherits, and none of them need changing
Under Default table expiration, tick Enable table expiration, then set Default maximum table age to 1. The units are fixed at Days
Click CREATE DATASET

Why set an expiration at all? Any table created in this dataset now deletes itself after a day. That is a cost control worth knowing: a scratch dataset that cleans up after itself cannot quietly accumulate storage charges for years after everyone has forgotten it exists.

Note the units. The console only offers days here, with one day the shortest you can pick. The API and the command line accept seconds, so if you ever need an hour, that is where you set it.

Step 4: Load the CSV from the Bucket

This is the step that matters. The source is the bucket, not your computer.

Click lab_ingest in the Explorer, then Create Table. If lab_ingest has not appeared under your project yet, refresh the page; the Explorer sometimes lags a step behind
Under Source, set Create table from to Google Cloud Storage
Click Browse and select your employees.csv. Watch File format as you do: it switches itself to CSV, because the console reads the file extension
Under Destination, set Table to employees. Project and dataset are already filled in
Under Schema, turn on Edit as text and paste this in:

employee_id:INTEGER,name:STRING,department:STRING,start_date:DATE

Read that line back against the file, because you are describing the file to BigQuery. It is one entry per column, in the file's order, and each entry is a name, a colon, and a type. The names are the four headings from the file's first line. The types come from looking at the values: employee_id holds whole numbers, so INTEGER. name and department are text, so STRING. start_date is written like 2020-01-15, which is the YYYY-MM-DD form the DATE type expects.

The order matters as much as the names. A CSV load matches columns by position, first schema entry to first value in each row, so the schema has to list the columns in the same left to right order as the file.

The box grows tall once you turn on Edit as text, so keep scrolling. Below it are Partitioning settings and Cluster settings, which decide how BigQuery physically splits and sorts a large table, and Tags, which is for policy. You do not need any of them in this lab.

Expand Advanced options, at the very bottom, and set Header rows to skip to 1. It starts at 0
Click CREATE TABLE

A notification appears at the bottom of the screen:

"employees" created.

Header rows to skip is telling BigQuery that the first line of the file is column names, not data. Leave it at 0 and the load fails, because the word employee_id cannot go into an INTEGER column.

You could have ticked Auto detect instead of writing the schema out, and BigQuery would have guessed the types. Writing it yourself is the habit worth having: autodetect guesses from a sample, and a guess that comes back wrong is the subject of a later lab.

Step 5: Look at What Landed

Click + to open a query tab and run:

SELECT * FROM lab_ingest.employees ORDER BY employee_id

employee_id  name   department   start_date
1            Ada    Engineering  2020-01-15
2            Grace  Research     2019-06-01
3            Alan   Engineering  2021-03-22

Three rows, the header nowhere in sight. Nothing was downloaded to get here: BigQuery read the file directly out of the bucket, which is exactly what makes this pattern work on files far too big to move.

You can also skip the SQL entirely: click the employees table in the Explorer and open its Preview tab, which shows the rows without running a query. The query is only needed here because ORDER BY gives everyone the same output to compare against.

Step 6: A Table That Does Not Match the File

Now the realistic problem. You need a second table for contractors, who have everything an employee has plus a contract end date. The CSV you were given does not have that column, and you cannot edit it, because in a real pipeline you do not own the file.

Click lab_ingest in the Explorer again, then Create Table. Set Create table from to Google Cloud Storage and Browse to the same employees.csv
Table: contractors
Edit as text, and this time give it five columns:

employee_id:INTEGER,name:STRING,department:STRING,start_date:DATE,contract_end_date:DATE

Advanced options, Header rows to skip to 1 again
Click CREATE TABLE

It fails. The notification says:

Failed to create table: Error while reading data, error message: CSV
processing encountered too many errors, giving up. Rows: 0; errors: 3;
max bad: 0; error percent: 0

Three errors, no rows loaded, and not a word about which column caused it. That is not very informative, so go and get the details:

In that same notification, click Go to job

A Load job details panel opens with one error per bad row, and these ones name the culprit:

Error while reading data, error message: CSV table references column
position 4, but line contains only 4 columns.; line_number: 2
byte_offset_to_start_of_line: 39 column_index: 4
column_name: "contract_end_date" column_type: DATE

Yours will carry one more piece on the end, File: gs://your-bucket/employees.csv, naming the file it was reading. It is trimmed above because your bucket name is not the same as anyone else's.

There it is: column_name: "contract_end_date". BigQuery reached the end of a row after four values, was still expecting a fifth, and refused rather than guessing. It did that three times, once per data row, which is where the errors: 3 in the summary came from.

Below the errors the same panel lists the job's settings, one of which is Max bad records: 0. That is the max bad: 0 from the first notification: the number of bad rows a load is allowed to skip before giving up. It defaults to zero, which is why one bad row was enough to stop the whole load.

Step 7: Allow the Rows to Be Short

The setting you need is Jagged rows, which tells BigQuery that rows may end early and any missing trailing columns should be left empty.

Close the Load job details panel and you will find the Create table panel still sitting behind it, with everything you typed still in place. A failed load does not throw your settings away, so there is nothing to re-enter.

Scroll down to Advanced options, which is still expanded, and tick Jagged rows
Click CREATE TABLE

"contractors" created.

Query it:

SELECT * FROM lab_ingest.contractors ORDER BY employee_id

employee_id  name   department   start_date   contract_end_date
1            Ada    Engineering  2020-01-15   null
2            Grace  Research     2019-06-01   null
3            Alan   Engineering  2021-03-22   null

Three rows, and contract_end_date shows null in every one. That is BigQuery saying "no value here", which is a different thing from zero and a different thing from an empty string. Someone can fill those in later without the rows having to be reloaded.

Why not just tick Auto detect? It is the obvious thing to reach for, and it would even make the error go away. But look at what it does: it builds the schema from what is in the file, and the file has four columns. Tick it and the five column schema you wrote is discarded, the load succeeds, and you get a contractors table with no contract_end_date at all. Nothing fails, and that is exactly the problem. The table is just not the one you were asked to build.

Auto detect can only describe the data you already have. It cannot ask for a column the file does not contain. The schema you wrote is a statement of what the table should hold; Jagged rows keeps that statement and lets the file fall short of it.

Jagged rows only forgives columns missing from the end of a row. If the file were missing a column in the middle, every value after it would shift one place to the left and load into the wrong field. Departments would land in start dates, and nothing would error.

That silent version is far worse than the failure you just saw, which is precisely why the strict behaviour is the default and you have to ask for anything looser.

Validate Your Work

Get Your Completion Token

Click the button below to open the lab checker in a new window. Sign in with Google to verify your work and get your completion token.

Open Lab Checker

Submit Your Token

Paste your token below and click Validate. If valid, a button will appear to mark the lesson complete.

What You Learned

That BigQuery loads straight out of a bucket, so the file never touches your machine and its size stops being your problem
That the dataset belongs in the same region as the bucket it reads from, and that the lab project only allows specific regions
How to write a schema by hand with Edit as text rather than letting autodetect guess
That Header rows to skip is what stops your column names being loaded as a row of data
That a load failure notification does not tell you which column broke, and that Go to job opens the per-row errors that do
That Max bad records of 0 is why one bad row stops the whole load
That Jagged rows lets rows end early and fills the missing trailing columns with null
That it only forgives columns missing from the end, because a gap in the middle would shift every later value into the wrong field without erroring

Troubleshooting

Failed to create dataset: US violates constraint

The full message is US violates constraint constraints/gcp.resourceLocations. You left Data location on its default, which is the US multi-region, and the lab project's organization policy does not allow it. Select us-central1 (Iowa) and create the dataset again.

The first load failed too

Check Header rows to skip is 1. At 0, BigQuery tries to load the word employee_id into an INTEGER column and refuses. The error names employee_id rather than contract_end_date, which tells you which mistake you made.

"Already Exists: Table" when I retry

A load that failed leaves no table behind, but a load that succeeded does. If you are redoing Step 7 after it already worked, either delete contractors first, or set Write preference in Advanced options to Overwrite table.

My contractors table exists but has no contract_end_date column

You ticked Auto detect, so BigQuery built the table from the file, and the file does not have that column. Delete the contractors table, then redo Step 6 and Step 7 with the five column schema in Edit as text and Jagged rows ticked.

I cannot find my file in the Browse dialog

It has to be in the bucket, not on your computer. Go back to Cloud Storage > Buckets, open your bucket, and confirm employees.csv is listed there. Uploading it is Step 2.

Validation Fails

If your validation fails, check that:

Your bucket name starts with gcpstudyhub-lab-bq-ingest- and contains employees.csv
The dataset is named exactly lab_ingest
employees holds 3 rows, and contractors holds 3 rows
contractors has a contract_end_date column and every row in it is null
You are using the same email address for validation that you used to start the lab