Loading Multiple Files Dynamically into BigQuery

Introduction

In this article, we will look at another workspace loading multiple shapefiles in a nested folder structure into Google BigQuery.
 

Requirements

• FME 2020.0 or newer

• Google BigQuery Account

• Granted the required permissions to create tables in BigQuery

Step-by-Step Instructions

Download and open the BigQueryGIS - Dynamic workspace template. For detailed instructions on how to download templates within Workbench, please see the 1. Download Exercise Workspace step in Loading a Single File to BigQuery article. Note: This exercise is also available as an FME template file named BigQueryGIS - Dynamic on the FME Hub for manual download.
 

1. Enable Feature Caching and Prompt for User Parameters

Once the workspace has been opened, ensure both the Feature Caching and Prompt for User Parameters options are enabled in Workbench. This can be done using the toolbar or the Run menu.

Ensure both Feature Caching and Prompt for User Parameters options are enabled in Workbench.
 

2. Set Reader to Read From Folders/Subfolders

Select the Shapefile Reader feature type <ALL> and click on the green Run Just This button to read in the data. The Translation Parameter Values dialog should appear.

Select the source shapefiles data folder by selecting the dropdown arrow to the right of the Source Esri Shapefile(s) parameter and select the “Select Multiple Folders/Files…” option.

Click on the down arrowhead to the right of the Source Esri Shapefile(s) parameter and select the “Select Multiple Folders/Files…” option.

Note the Subfolders checkmark which ensures Shapefiles contained within the directory and any subfolders will be read into FME. Double-click on the existing path in the Select Source ESRI Shapefiles dialog that appears.

Ensure the Subfolders checkbox is enabled which tells the reader to read files in subfolders.

In the Folder Selector dialog navigate to the directory called “Folder” (...\1\USA\Folder) and select it by clicking the 'Select Folder' button. This directory contains 3 Shapefiles and a directory called “Subfolder”, which contains another 3 Shapefiles. Click 'OK' to exit out of the Select Source Esri Shapefile(s) dialog.
 

3. Inspect Source Datasets

In the Translation Parameter Values dialog, select the BigQuery Web Connection created in the previous exercise and choose your Google Cloud Project and type in the name of an existing Google BigQuery Dataset. Then click the 'Run' button on the dialog. The files will be read in FME and you can inspect the 6 different Shapefiles in the Visual Preview window.

Inspect the 6 Shapefiles that will be loaded into BigQuery.
 

4. Write Dynamically to BigQuery

Now inspect the BigQuery Writer settings. Click on the cogwheel on the BigQuery feature type to open up the Writer feature type parameters. Notice that the “Dynamic Schema Definition” is selected which means this Feature Type will create multiple BigQuery Tables in the specified Dataset. Each one will have its own schema based on the schema of its source file.

Enabling Dynamic Schema Definition creates multiple BigQuery tables. Each table will have its own table schema based on their respective incoming file.

Run the translation by clicking on the big green button in the menu bar. If the Translation Parameter Values dialog appears, select the 'Run' button again.
 

5. Inspect the Data Written in BigQuery

In the final step of this exercise, we will take a look at the tables created in the Google BigQuery user interface and the BigQuery GeoViz tool by using the Export to GeoViz functionality. Notice the six new tables created in the dataset specified in the workspace.

Note: if you do not see the new tables in the BigQuery user interface, ensure the workspace has finished and refresh the BigQuery user interface.

New tables created in BigQuery from the six Shapefiles within the directory selected and its subfolder.

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

Visualize spatial data in BigQuery using the GeoViz web tool.

You have now successfully uploaded multiple files dynamically 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.