Google BigQuery Connectors

Back Next

Adding multiple source objects

When you create a Source transformation, you can select Google BigQuery V2 multiple object as the source type and then configure a join to combine the tables. You can define an advanced relationship or a query to join the tables. You must use the standard SQL to define the query to join the tables.

In the Source transformation, click the

Source Type

Multiple Objects

From the

Actions

menu, click

Add Source Object

Select the source object that you want to add from the displayed list and click

From the

Related Objects Actions

menu, select

Advanced Relationship

In the

Advanced Relationship

window, you can click

Add Object

to add more objects.

Set your own conditions or specify a query to define the relationship between the tables.

When you configure a join expression, select the fields and define a join condition or a query syntax. You must prefix the Project ID before the Dataset ID in the join condition to combine multiple tables.

Use the following example to configure the join condition or join query with the Project ID prefix:

Join condition:

`P1.D3.T3`.col5 = `P1.D2.T2`.col3 ON `P1D2.T2`.col4 = `P1.D1.T1`.col2

Join query:

`P1.D1.T1` LEFT OUTER JOIN `P1.D2.T2` FULL OUTER JOIN `P1.D3.T3` ON `P1.D3.T3`.col5 = `P1.D2.T2`.col3 ON `P1.D2.T2`.col4 = `P1.D1.T1`.col2

In the example,

represents the Project ID,

represents the Dataset ID, and

represents the Table Name.

If you configure a filter, prefix the Project ID before the Dataset ID in the filter condition. For example, provide the simple filter condition as,

`Project.Dataset.Table`.column

If you specify a SQL override query for multiple source tables, you must use the following format for the SQL override query:

select `project_id.dataset.table`.col1 AL_dataset_table_col1,`project_id.dataset.table`.col2 AL_dataset_table_col2, `project_id.dataset1.table1`.col1 AL_dataset1_table1_col1,`project_id.dataset1.table1`.col2 AL_dataset1_table1_col2 from `project_id.dataset.table` <join condition> `project_id.dataset1.table1` ON `project_id.dataset.table`.col = `project_id.dataset1.table1`.col1 where <condition>

In the example,

represents the alias prefix for the column names.

Click

The following image shows an example of an advanced join condition defined between the Google BigQuery V2 tables: