Official |[Google BigQuery] Execute Select SQL

Operation name

Execute Select SQL

Function overview

On Google BigQuery, writes data to a table using a SQL statement.

Properties

= Remarks =

For details on use of variables, refer to Variables.

Basic settings

Item name	Required/Optional	Use of variables	Description	Remarks
Name	Required	Not available	Enter a name that is used on the script canvas.

Required settings

Item name	Required/Optional	Use of variables	Description	Remarks
Destination	Required	Not available	Select a connection resource. Add A new connection resource can be added. Edit list Connection resource settings can be edited in > HULFT INTEGRATE > Connections.
Project ID	Required	Available	Select or enter the project ID.
SQL statement	Required	Available	Enter the SQL statement to be issued to Google BigQuery.	Note For more details on executable SQL statements other than the SELECT statement, contact Google.
Dataset name of the save destination	Required	Available	Select or enter the dataset name of the table where the query result is to be stored.
Table name of the save destination	Required	Available	Select or enter the table name where the query result is to be stored.

Property actions

Item name	Description	Remarks
Update the list of project IDs	Get projects in the specified destination and set them to Project ID.	If Destination is specified/edited or projects have been added/edited on Google BigQuery, the changes can be reflected using this property action.
Update the list of dataset names of the save destination	Get datasets in the specified project and set them in Dataset name of the save destination.	If Project ID is specified/edited or a dataset has been added/edited on Google BigQuery, the changes can be reflected using this property action. If a variable is set to Project ID, this action will be invalid.
Update the list of table names of the save destination	Get tables in the specified dataset and set them in Table name of the save destination.	If Dataset name of the save destination is specified/edited or a table has been added/edited on Google BigQuery, the changes can be reflected using this property action. If a variable is set to Project ID or Dataset name of the save destination, this action will be invalid.

Write settings

Item name	Required/Optional	Use of variables	Description	Remarks
Write settings of destination tables	Required	Not available	Select an option to use when writing. Add to table (Default) Add data to the end of an existing table. Overwrite table Delete all the data in the write destination table, and write data.

Item name

Required/Optional

Use of variables

Description

Remarks

Write settings of destination tables

Required

Not available

Select an option to use when writing.

Add to table

(Default)

Add data to the end of an existing table.

Overwrite table

Delete all the data in the write destination table, and write data.

Query result settings

Item name	Required/Optional	Use of variables	Description	Remarks
Output query results to data flow	-	-	Select whether to get the query results. Selected Query results are output to the data flow. Not selected (Default) Query results aren't output to the data flow.
Output schema definition	Optional	-	The schema definition of the SQL statement specified in SQL statement is displayed.	The field names displayed in Output schema definition will be displayed in the input schema of Mapper. Enabled when Output query results to data flow is selected.
Output schema definition/Field name	Required	Not available	The field names specified in SQL statement are displayed.
Output schema definition/Data type	Required	Not available	The field types specified in SQL statement are displayed.
Output schema definition/Internal data type	Required	Not available	The internal data types of Field name are displayed.

Property actions

Item name	Description	Remarks
Execute SQL statement and set output schema	Get output information from the SQL statement specified in SQL statement and set it in Output schema definition.	Enabled when Output query results to data flow is selected.

Data processing method

Item name	Required/Optional	Use of variables	Description	Remarks
Data processing method	Required	Not available	Select a data processing method. Apply script settings (Default) The setting for mass data processing in the script properties is applied to the connector. Do not perform mass data processing Mass data processing isn't performed. Perform mass data processing Mass data processing is performed.

Item name

Required/Optional

Use of variables

Description

Remarks

Data processing method

Required

Not available

Select a data processing method.

Apply script settings

(Default)

The setting for mass data processing in the script properties is applied to the connector.

Do not perform mass data processing

Mass data processing isn't performed.

Perform mass data processing

Mass data processing is performed.

Comment

Item name	Required/Optional	Use of variables	Description	Remarks
Comment	Optional	Not available	You can write a short description of this connector.

Schemas

Input schema

None.

Output schema

Output according to the setting of Output query results to data flow.

The number of columns varies depending on the Output schema definition settings.

Transaction

Transaction isn't supported.

Mass data processing

Mass data processing is supported.

Parallel Stream Processing

PSP isn't supported.

Supported types

When Output query results to data flow is enabled, the types that are supported are limited.

For more details on the supported types, refer to Messages and limitations of the Google BigQuery connector.

Available component variables

Component variable name	Description	Remarks
message_category	When an error occurs, the category of the message code corresponding to the error is stored.	The default value is null.
message_code	When an error occurs, the code of the message code corresponding to the error is stored.	The default value is null.
message_level	When an error occurs, the severity of the message code corresponding to the error is stored.	The default value is null.
error_type	When an error occurs, the error type is stored.	The default value is null. The format of the error type is as follows. Example: java.io.FileNotFoundException
error_message	When an error occurs, the error message is stored.	The default value is null.
error_trace	When an error occurs, the trace information for the error is stored.	The default value is null.

Message codes, exception messages, and limitations

Connector	Message code	Exception message	Limitations
Messages and limitations of the Google BigQuery connector