Wildcard Table Input

Wildcard Table Input


This article is specific to the following platforms - BigQuery.

Wildcard Table Input

BigQuery allows the creation of 'Wildcard' tables that combines all tables found through a wildcard expression.

A wildcard expression can be any string followed by the wildcard character (*) that will then represent any other possible string. Thus, for example, using the wildcard expression Flights_20* might return Flights_2014, Flights_2015, Flights_2016 and Flights_20Temp as a single, united table.

For full information, see the Google Cloud Platform documentation on Wildcard Tables and Querying them.

Properties

Property Setting Description
Name Text The descriptive name for the component.

This is automatically determined from the table name when the Table Name property is first set.
Project Text Enter the name of the Google Cloud Platform Project that the table belongs to.
Dataset Text Enter the name of the Google Cloud Platform Dataset that the table belongs to.
Table Prefix Text A prefix that all unioned tables must have. The wildcard character is positioned exactly after this prefix.
Include Table Suffix Select Yes: The remaining string after the table prefix is removed from each tablename is to be given in a column, showing the origin table of each row. No: Do not store suffixes in the Wildcard table table.
Table Suffix Column Name Text Name of the column where table suffixes are listed. Property only visible if "Include Table Suffix" is 'Yes'.
Where Clause Text Adds an option freeform SQL filter that is appended to the SELECT statement generated by the component. This allows a user to filter which tables the component unions.

Example

In this example we have 2 tables containing basic client data; name, age, state and plan. One of these tables is for clients on the west coast of the USA, the other on the east cost. We will see in this example that the Wildcard component can easily find and join these tables.


These tables are named 'docs_t1_o' and 'docs_t2_o'. The Wildcard component can be used to collect all tables that fit a certain prefix and, in this case, that prefix must be 'docs_t'. This is the value we will enter into the 'Table Prefix' property of the Wildcard Table component. Note that the Table Prefix property does not require the enclosing quotes nor do we include an asterisk to denote the wildcard character/s, as it is implied.

We must be careful that a completely unrelated but similarly named table does not exist. If we had a table named 'docs_tdo_final', it would be included and we may not wish it to be.

The Wildcard Table component will take our two tables and append their common columns. Take care that your input tables share their structures so that the Wildcard Table component can effectively unite the data. The output from our two data sources is shown below.

Finally, we have the option to enter a 'where' clause to the SQL that will filter the output data. In the below example, we choose to keep only the customers who are part of the 'Plus' plan.

Sampling the data shows that we have indeed united both tables as well as filtered the final table for only the 'Plus' plan customers.