BigQuery - Select actions

Select rows

This action lets you select rows based on certain criteria defined by a WHERE predicates. Rows from the selected table that match the WHERE predicates will be returned as the output of this action. When run, if no data is returned in the first 60 seconds of the job execution in BigQuery, Workato will put the job to sleep for a periodic amount of time before checking if the data is ready to be retrieved from BigQuery. This is controlled by the "Wait for query to complete?" input field.

Input fields

Field	Description
Project	The project available in the connection to be billed for the query.
Dataset	The dataset which the action or trigger will pull the possible tables from.
Table	The table inside the dataset.
Table Fields	Only required if table ID is a datapill Declare the columns of the table. This should be the same across all possible values of the datapill.
WHERE Predicates	Build your where conditions with predicates. We assume that all predicates are joined the the AND condition. User select rows with custom SQL for more complex queries.
Wait for query to complete?	Set to true to wait for your query to complete, regardless of how long it took. If false, this action only waits a max of 60 seconds for the query to run before returning only the Job ID. You may use the Get batch of rows by Job ID action to retrieve the results.
Output columns	After selecting your table, you'll also be able to select which columns you want returned. Selecting only what you need for your recipe increases job throughput and overall efficiency of the recipe.
Order by	Rows returned from this action can be ordered based on the Order by input field. This field is used to change the default ordering of rows from your BigQuery database. You can also define the direction of order for each column you wish to order by. The following order by statement will order rows by priority in ascending order followed by created_date in descending order (latest first). priority asc, created_date desc
Limit	This input field determines the maximum number of rows to return. The default limit is 50000 and this can be left blank for the entire result set to return. To retrieve all rows, you will need to use the get query result action to retrieve more rows
Offset	This input field gives you the option to fetch only a page of results from the entire results set. For example, to skip the first 100 rows of the selected results set, input 100 to this field. The default is 0.
Location	The geographical location of where the job should be run. This field isn't required in most cases.

Output fields

Field	Description
Rows	An array of the rows. Each datapill in the row object corresponds to a single column.
Kind	The kind of job that was executed. This should be queryResponse
Job reference	Details about the job itself. This object returns the Project ID, Job ID and location of the query. In cases where you need more results, you can use the get query result action to retrieve more rows
Total rows	Total rows returned by this query. Useful when helping with Pagination using the get query result action
Etag	Schema of the table or result.
Jobs complete	Should always be "Done".
Total Bytes processed by the query	The total number of bytes processed by this action. This counts towards your BigQuery bill

Select rows using custom SQL

This action lets you select rows based on a custom SQL query. Rows that are returned from the query will be returned as the output of this action. When run, if no data is returned in the first 60 seconds of the job execution in BigQuery, Workato will put the job to sleep for a periodic amount of time before checking if the data is ready to be retrieved from BigQuery. This is controlled by the "Wait for query to complete?" input field.

Input fields

Field	Description
Project	The project available in the connection to be billed for the query.
Query	The Query to execute. You can provide bind variables in your query which will be parameterized for security. Declare bind variables with the @ keyword.
Parameters	This allows your to parameterize your query. When you give a query with bind variables, you must give your bind variable here. For example, if query is select * from table where id = @id, your bind variable is @id and your parameter should be id.
Output fields	Use this to manually define the output fields of your query. The names given to the output should be identical to the column names expected.
Wait for query to complete?	Set to true to wait for your query to complete, regardless of how long it took. If false, this action only waits a max of 60 seconds for the query to run before returning only the Job ID. You may use the Get batch of rows by Job ID action to retrieve the results.
Location	The geographical location of where the job should be run. This field isn't required in most cases.

Output fields

Field	Description
Rows	An array of the rows. Each datapill in the row object corresponds to a single column.
Kind	The kind of job that was executed. This should be queryResponse
Job reference	Details about the job itself. This object returns the Project ID, Job ID and location of the query. In cases where you need more results, you can use the get query result action to retrieve more rows
Total rows	Total rows returned by this query. Useful when helping with Pagination using the get query result action
Etag	Schema of the table or result.
Jobs complete	State of job. Most often, this value will be "DONE" if query if completed successfully. If Wait for query to complete? is set to false and query takes longer than 60 seconds, this will be "PENDING".
Total Bytes processed by the query	The total number of bytes processed by this action. This counts towards your BigQuery bill

Select rows using custom SQL and insert into table

This action lets you select rows based on a custom SQL query. There is a further optional input field here that includes a Destination table. This allows you to store your query results in a temporary table for use later on.

Input fields

Field	Description
Project	The project available in the connection to be billed for the query.
Query	The Query to execute. You can provide bind variables in your query which will be parameterized for security. Declare bind variables with the @ keyword.
Parameters	This allows your to parameterize your query. When you give a query with bind variables, you must give your bind variable here. For example, if query is select * from table where id = @id, your bind variable is @id and your parameter should be id.
Output fields	Use this to manually define the output fields of your query. The names given to the output should be identical to the column names expected.
Wait for query to complete?	Set to true to wait for your query to complete, regardless of how long it took. If false, this action only waits a max of 60 seconds for the query to run before returning only the Job ID. You may use the Get batch of rows by Job ID action to retrieve the results.
Destination table	Allows you to provide the destination table for your query results.
Location	The geographical location of where the job should be run. This field isn't required in most cases.

Output fields

Field	Description
Rows	An array of the rows. Each datapill in the row object corresponds to a single column.
Kind	The kind of job that was executed. This should be queryResponse.
Job reference	Details about the job itself. This object returns the Project ID, Job ID and location of the query. In cases where you need more results, you can use the get query result action to retrieve more rows.
Total rows	Total rows returned by this query. Useful when helping with Pagination using the get query result action.
Etag	Schema of the table or result.
Jobs complete	Should always be "Done".
Total Bytes processed by the query	The total number of bytes processed by this action. This counts towards your BigQuery bill.