Google BigQuery Connectors

Back Next

Configuring an SQL transformation

You can configure a SQL transformation to process a stored procedure on the

SQL

tab of the SQL transformation.

This example lists the tasks required to configure an SQL transformation that calls a stored procedure in Google BigQuery.

Your mapping includes user IDs in the data flow. You want to include user names in addition to user IDs. You have a stored procedure that matches user IDs with user names in the database. You add an SQL transformation to your mapping, select the stored procedure, and map the userId incoming field with the userId input field in the stored procedure. Add a SQL transformation in a Google BigQuery mapping.

You check the

Output Fields

tab for the SQL transformation to confirm that it includes the username field. When you run the mapping, the username value is returned with the user ID.

Configure a mapping with the Source, Target, and SQL transformations.

Perform the following tasks in the SQL transformation:

Enter a name and description for the SQL transformation.

In the

Incoming Fields

tab, define field rules that determine the data to include in the transformation.

In the

Properties

panel of the SQL transformation, click the

SQL

tab.

In the

SQL

tab, perform the following tasks:

Select the connection to the database.

You can select the connection or use a parameter.

Set the SQL type to

Stored Procedure

Click

Select

to select the stored procedure from the database, or enter the exact name of the stored procedure to call.

The stored procedure name is case-sensitive.

If you add a new stored procedure to the database while you have the mapping open, the new stored procedure does not appear in the list of available stored procedures. To refresh the list, close and reopen the mapping.

The following image shows the configured SQL transformation properties: