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.
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
- BigQuery Pricing
- There is also a free tier, so if you use a small amount, you will not be charged
- The project you created along this article is also subject to storage, so delete it if you do not need it after the test is completed.
- Enable, disable, or change billing for a project
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.
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.
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.
Data Item settings
The Data Item settings in this example are as follows.
| Data Item Name | Data-type | Required | [Input] Step | Description |
| Title | No | No | Editable | The title of the Process |
| Name | String-type (single line) | No | Editable | |
| Company | String-type (single line) | No | Editable | |
| Added date | Date-type (Y/M/D) | No | Editable | The current date is set in the preceding [Update Data] step. |
| Gender | Select-type (radio button) | No | Editable | Two choices with display label Male and Female The Choice ID are respectively M, F |
| Age | Select-type (radio button) | No | Editable | 6 choices with the following both for the Display labels and Choice IDs “20s” ”30s” ”40s” ”50s” ”60s” “70s+” |
| Address | String-type (multiple lines) | No | Editable | Value including line breaks, such as a postal address |
| Address (single-lilne) | String-type (single line) | No | No display | Stores 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 name | Required | Description |
| C1: OAuth2 Setting | Yes | Specify 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 ID | Yes | Specify the Project ID you noted in “Create a BigQuery table” |
| C3: Dataset ID | Yes | Specify the Dataset ID you noted in “Create a BigQuery table” |
| C4: Table ID | Yes | Specify the Table ID you noted in “Create a BigQuery table” |
| C5: Template Suffix | No | Specify only when using a Template Table * |
| C6F: Field 1 | No | The Field name of (the target) table schema (e.g. Name) |
| C6V: Value 1 | No | Reference 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 2 | No | Reference expression to the Data Item that stores the value to be added (e.g. {#q_Company}) |
| C8F: Field 3 | No | The Field name of (the target) table schema (e.g. Add_date) |
| C8V: Value 3 | No | Reference expression to the Data Item that stores the value to be added (e.g. {#q_Added_date}) |
| C9F: Field 4 | No | The Field name of (the target) table schema (e.g. Gender) |
| C9V: Value 4 | No | Reference expression to the Data Item that stores the value to be added (e.g. {#q_Gender?.![value]} reference to the Choice ID) |
| C10F: Field 5 | No | The Field name of (the target) table schema (e.g. Age) |
| C10V: Value 5 | No | Reference expression to the Data Item that stores the value to be added (e.g. {#q_Age}) |
| C11F: Field 6 | No | The Field name of (the target) table schema (e.g. Address) |
| C11V: Value 6 | No | Reference expression to the Data Item that stores the value to be added (e.g. {#q_Address_single_lilne}) |
| C12F: Field 7 | No | – |
| C12V: Value 7 | No | – |
* 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 value | Value 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)
Pingback: Google BigQuery: Insert New Data – Questetra Support