Loading a Single File to BigQuery

Introduction

In this article, we will look at a very simple workspace loading a single shapefile and GeoJSON file into Google BigQuery.
 

Video

Requirements

• FME 2020.0 or newer

• Google BigQuery Account

• Granted the required permissions to create tables in BigQuery

Step-by-Step Instructions

1. Download the Exercise Workspace

To get started, launch FME Workbench and select Create Workspace button located at the top left (or enter the shortcut Ctrl-N on Windows).

Select the Create Workspace button or enter the shortcut Ctrl-N to bring up the Create Workspace dialog.

In the Create Workspace dialog click on FME Hub and search for “BigQuery” with the Filter field and select BigQueryGIS - Simple. The workspace should automatically download and open in Workbench.

Search for “BigQuery” using the Filter field in the Create Workspace dialog and select BigQueryGIS - Simple to download the workspace.

This exercise workspace is also available for manual download on the FME Hub.

Note: if you have chosen to download the workspace manually using FME Hub or from the Downloads section above, double click open the file after downloading.
 

2. Enable Feature Caching

Ensure Enable Feature Caching is enabled using the green magnifying glass icon. Alternatively, use the menu bar (Run > Enable Feature Caching).

Ensure Enable Feature Caching is enabled using the green magnifying glass icon or using the Run menu.

Inspect the source data by selecting the Shapefile Feature Type. A button bar will appear above feature type. Click on the green “Run Just This” button to read in the data. At this point, the Translation Parameter Values dialog should appear.

Select the reader feature type and inspect the data by clicking on the green “Run Just This” button.
 

3. Add Web Connection for BigQuery

In the Translation Parameters Values dialog, select the Account parameter, and from the dropdown click “Add Web Connection...”. In the next dialog, select the Authenticate button. Follow the Google sign-in procedure to enter your credentials and finish creating the Web Connection for Google BigQuery.

Select Add Web Connection to create a Web Connection and select Authenticate button to enter your credentials.

Enter your Google credentials and follow the sign in procedure.

After entering your credentials and following sign-in procedure, your Google BigQuery web connection will have been added to Workbench and the Account parameter will no longer appear red (invalid value).
 

4. Specify BigQuery Project and Dataset

Once the Web Connection is created click on the ellipsis next to the Project parameter to browse Google Cloud Projects available to your Google Account. If you do not see any projects listed in the dialog, please ensure you have either created a Google Cloud Project or have been granted permissions to project.

Browse Google Cloud Projects available to your Google account using the Project parameter ellipsis.

After selecting the Project you can now type in the name of the Google BigQuery dataset the files should be written to. The Google BigQuery Dataset needs to already exist in your Google Cloud Project before FME can write to it. Please reach out to us if this is an inconvenience and you would like FME to create new BigQuery datasets when writing.

Note: The term dataset here refers to the BigQuery concept of datasets, which are top-level containers for tables and views. This differs from FME’s concept of datasets. Please see the Getting Started with Google BigQuery article for more details.

Specify the existing BigQuery dataset the files should be written to.
 

5. Inspect Source Data

Click the Run button in the Translation Parameter Values dialog. Once the data is read and cached, click the green magnifying glass to inspect the data. Feel free to view the data as well for the GeoJSON feature type.

Inspect source data in Workbench.
 

6. Inspect BigQuery Writer Settings

Inspect the BigQuery Writer settings. Click on the cog wheel on one of the BigQuery feature types to open up the Writer feature type parameters. These parameters control the writing behavior and the geography column settings.

Writer feature type parameters that control writing behavior and geography column settings.

Select the User Attributes tab and notice that the schema settings that allow you to map certain attributes to data types supported in Google BigQuery using the Type dropdown field when Attribute Definition is set to Manual.

User Attribute tab allows users to map attributes to a number of data types supported in BigQuery.
 

7. Write to BigQuery

Now we are ready to run the translation and load the 2 files into the previously selected Google BigQuery Dataset. Click the big green button in the menu bar. If the Translation Parameter Values dialog appears again, ensure no parameters are invalid (red), and select the Run button.

Run the translation and write the 2 files into the selected BigQuery Dataset.

Check Workbench to see the translation has finished and the features were written by using the Translation Log pane. You should see the number of features written in the log. For this exercise, 1628 ROADS features and 52 STATES feature were written.

Verify the data has been uploaded to BigQuery by using the Translation Log pane.
 

8. Inspect Output in BigQuery

In the final step of this exercise, we will take a look at the tables written in the Google BigQuery user interface. Navigate to the tables written and select Preview. Notice the Well-Known Text geometry contained in the geo column.

Note: if you had the Google BigQuery user interface open before running the workspace in FME, you may need to refresh the page to view the newly created tables.

Users can visualize spatial data in BigQuery using the GeoViz web tool via the Explore with GeoViz functionality under the Export dropdown.

Visualize spatial data using the GeoViz tool in BigQuery.

You have now successfully uploaded two files to BigQuery with FME!
 

Data Attribution
The data used here originates from open data made available by the US Census Bureau, Department of Commerce. It contains information licensed under U.S. Government Work.