Google BigQuery - Select actions

Select rows

The Select rows 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. If no data is returned in the first 60 seconds of the job execution in Google 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 Google BigQuery. This is controlled by the Wait for query to complete? input field.

Input

Input field	Description
Project	Select the project to bill for the query.
Dataset	Select the dataset to pull tables from.
Table	Select the table inside the dataset.
Table Fields	Only required if table ID is a datapill. Select table fields to 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 using the AND condition. You can 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	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 Google 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. For example, priority asc, created_date desc.
Limit	Enter 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 must use the get query result action to retrieve more rows
Offset	Configure this field 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	Select the geographical location of where the job should be run. This field isn't required in most cases.

Output

Output 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. You can use the get query result action to retrieve more rows in cases where you need more results.
Total rows	Total rows returned by this query. The get query result action is useful for helping with pagination.
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 Google BigQuery bill.

Select rows using custom SQL

The Select rows using custom SQL 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 Google 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 Google BigQuery. This is controlled by the Wait for query to complete? input field.

Input

Input field	Description
Project	Select the project to bill for the query.
Query	Select 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	Select the geographical location of where the job should be run. This field isn't required in most cases.

Output

Output 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 Google BigQuery bill.

Select rows using custom SQL and insert into table

The Select rows using custom SQL and insert into table 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

Input field	Description
Project	Select the project to bill for the query.
Query	Select 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	Select the geographical location of where the job should be run. This field isn't required in most cases.

Output

Output 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 Google BigQuery bill.