In this article I will explain an automatic process relating to Google Big Query.

BigQuery is a product provided by Google Cloud Platform, which is a service that can store big data and analyze it at ultra-high speed. It is used by many companies that utilize big data.

  • This automatic step does not work on the free trial version of Google Cloud Platform (registration of billing method is required)
  • This article is written for those who are already using BigQuery
  • For more information on BigQuery, see the BigQuery documentation

About the Auto-step that works with Google BigQuery

First, I will briefly explain the auto-step [Google BigQuery: Insert New Data].

Google BigQuery: Insert New Data

It inserts a new data row into a table in BigQuery. The table to be added is specified by the Project ID, Dataset ID, and Table ID. You can specify up to 7 fields for the table schema. You can also specify a suffix to create a template table.

[Google BigQuery: Insert New Data] settings screen

BigQuery and Questetra integration settings

I will explain how to set up integration between Questetra and BigQuery.

  • The System Administrator privilege is required to set up HTTP Authorization settings
  • The registered HTTP Authorization settings are also shared with other Apps on the same workflow platform

After placing the aforementioned automatic Step in the workflow App editing screen in Questetra, open its properties screen. When you click the [Set up Settings] button below the C1: OAuth2 settings item in Google BigQuery: Insert New Data, the HTTP Authorization Setting Google BigQuery API settings screen will open.

Click [Add] on the screen and specify a name of your choice. (Arbitrary string: It’s better to give a name that is easy to understand later.)

Click [Get Token] in the added setting to move to the Google authentication screen.

Sign in with the account whose inbox is to be monitored and allow the request.

You now have obtained a token for OAuth2 authentication with BigQuery. You can confirm it has worked by checking the “○ (refresh token)” indicator above the [Get Token] button.

Go back to the Google BigQuery: Insert New Data settings and select the setting name that was added in C1: OAuth2 settings.

Create a BigQuery table

Create a table in BigQuery that accepts data from Questetra BPM Suite.

  • In case you want to test the operation, create a new project and use it
    • You will need to register a billing account for the project
    • BigQuery charges according to usage

Create a dataset in the project. Open the properties of the created dataset and create a table.

Select [Empty Table] in the source, and specify the table name. Add fields to the schema one by one. The fields will be the columns in the table. Specify the Name, Type, and Mode respectively. Select the Type according to the Data-type of the data to be added from Questetra BPM Suite. [Google BigQuery: Insert New Data] supports STRING, BYTES, INTEGER, FLOAT, NUMERIC, BIGNUMERIC, BOOLEAN, TIMESTAMP, DATE, TIME and DATETIME. Select either NULLABLE or REQUIRED as the mode. You can set up to 7 fields.

After specifying all the required items, click [Create table]. The table name you created will appear below the dataset displayed in Explorer. Open the created table and check the Project ID, Dataset ID, and Table ID in the [Details] tab. All of them are listed in the Table ID section. (Similar to the path notation, it is displayed Project ID: Dataset ID. Table ID.) Make a note of each of them as you will need them later in the [Google BigQuery: Insert New Data] settings.

Table Details screen

Similarly, open the [Schema] tab and make a note of the character string displayed in each row in the Field name column. [Google BigQuery: Insert New Data] will add values by targeting these character strings.

Table Schema screen

Example App

Below is the Workflow Diagram of an example App using the auto-step.

This App is just for seeing how the values specified in the Input Step will be added to the BigQuery table. When using it in actual business, data will be accumulated in BigQuery simply by installing the Google BigQuery: Insert New Data step icon in the workflow where customer information is input, such as Inquiry Handling or Webform Application Reception.

In addition, in the Convert data (Update Data) step, an example that converts a String-type (multiple lines) value to single line data and an example of specifying the current date for the Date-type are described.

Workflow App settings

If you want to add the Google BigQuery: Insert New Data step icon to an existing App, set it using the Data Items that have already been defined. Specify the Field Name of the BigQuery table and set the inserting value to reference the workflow Data Item.
However, the format of the data to be added must satisfy the BigQuery specification. (Multi-line character strings are not allowed, etc.) Please convert to an appropriate format such as by using the [Update Data] step icon. Please refer to the following documents for details.

BigQuery Documents

Data Item settings

The Data Item settings in this example are as follows.

Data Item NameData-typeRequired[Input] StepDescription
TitleNoNoEditableThe title of the Process
NameString-type (single line)NoEditable
CompanyString-type (single line)NoEditable
Added dateDate-type (Y/M/D)NoEditableThe current date is set in the preceding [Update Data] step.
GenderSelect-type (radio button)NoEditableTwo choices with display label Male and Female
The Choice ID are respectively M, F
AgeSelect-type (radio button)NoEditable6 choices with the following both for the Display labels and Choice IDs
“20s” ”30s” ”40s” ”50s” ”60s” “70s+”
AddressString-type (multiple lines)NoEditableValue including line breaks, such as a postal address
Address (single-lilne) String-type (single line)NoNo displayStores the value that line breaks removed from multiple lines data by the [Update Data] step
Append to Table (Google BigQuery: Insert New Data) settings

Settings in Google BigQuery: Insert New Data are as follows.

Config nameRequiredDescription
C1: OAuth2 SettingYesSpecify the HTTP Authorization Setting name for connecting to Google BigQuery APIs. You need to get the OAuth2 token in advance with the setting name to be specified. (Refer to “BigQuery and Questetra integration settings“)
C2: Project IDYes Specify the Project ID you noted in “Create a BigQuery table”
C3: Dataset IDYesSpecify the Dataset ID you noted in “Create a BigQuery table”
C4: Table IDYesSpecify the Table ID you noted in “Create a BigQuery table”
C5: Template SuffixNoSpecify only when using a Template Table *
C6F: Field 1NoThe Field name of (the target) table schema
(e.g. Name)
C6V: Value 1NoReference expression to the Data Item that stores the value to be added
(e.g. {#q_Name})
C7F: Field 2 No The Field name of (the target) table schema
(e.g. Company)
C7V: Value 2NoReference expression to the Data Item that stores the value to be added
(e.g. {#q_Company})
C8F: Field 3No The Field name of (the target) table schema
(e.g. Add_date)
C8V: Value 3NoReference expression to the Data Item that stores the value to be added
(e.g. {#q_Added_date})
C9F: Field 4No The Field name of (the target) table schema
(e.g. Gender)
C9V: Value 4NoReference expression to the Data Item that stores the value to be added
(e.g. {#q_Gender?.![value]} reference to the Choice ID)
C10F: Field 5No The Field name of (the target) table schema
(e.g. Age)
C10V: Value 5NoReference expression to the Data Item that stores the value to be added
(e.g. {#q_Age})
C11F: Field 6No The Field name of (the target) table schema
(e.g. Address)
C11V: Value 6NoReference expression to the Data Item that stores the value to be added
(e.g. {#q_Address_single_lilne})
C12F: Field 7No
C12V: Value 7No

* Template table is a mechanism for creating a table with the same schema. For example, you can create separate tables for each gender that stores the data. (If you specify “Gender” as the suffix, C5 will be _#{#q_Gender})

Template suffix value
For templateSuffix (or –template_suffix) value, only alphanumerics and underscores (_) must be included. The maximum number of concatenated characters for a Table name and a Table Suffixes is 1,024.

Creating tables automatically using template tables

Convert Data (Update Data) settings

If necessary, set up a Convert data Step to convert the input data into a format that can be registered in BigQuery table. In this example, it is assumed that 1) String-type multiple-line data is converted into a String-type single-line Data Item by removing line breaks, and 2) the current date is set to the Date-type Data Item (to register the actual addition date instead of the initial value of the Date-type (Process start date).

Settings are as follows.

Data Item to be set valueValue or expression
Address (single-lilne)#{#joiner.splitJoin(#q_Address, ' ')}
(Remove line breaks from #q_Address, the String-type multiple-line data (separate by a single-byte space and concatenate))
Added date#now
(Insert current date)

When executed, the data will be added to BigQuery as the following image. (An example using table suffix)

1 thought on “Adding Data to BigQuery Automatically”

  1. Pingback: Google BigQuery: Insert New Data – Questetra Support

Comments are closed.

%d bloggers like this: