Studio enables you to use the database connectors to connect to your relational databases like MySQL, BigQuery and Microsoft SQL, MongoDB, and so on.
Let us now understand how to use the BigQuery connector. BigQuery is a fully managed enterprise data warehouse that enables high-speed SQL querying.
Configuring BigQuery Connector
To add third party DB connectors, under Studio > Connectors, click (+) Connector.
To build on top of your Bigquery data, you need to make use of the Google Cloud Credentials for authentication. For detailed information on Service Account Authentication to get the private key, you can refer to this article here.
The Service Account should be created from the Google Cloud Storage > APIs and Services > Credentials. If the Service account is not created, then to create it select Service Account.
Enter all the details as required to create the Service account like Account details, description, granting access to projects and granting user access
From the Service Account, you need to create a Key after you have added the details and given the necessary permissions. Create a JSON Key type and download the file. Keep it handy for use for connector configuration.
You need to first configure the BigQuery connector with the necessary authentication method and Private key.
To configure the connector, enter a Connector Name.
Enter the Authentication and copy the Private Key for your BigQuery account.
Once these configurations are done, you need to Test the request and connection. If the authentication is successful you would get the response accordingly. You can now click to Save your configuration.
Adding queries to the connector
Now that you have configured the connector you will find it under the Custom Database connectors list. Now to fetch data or undertake any other action, click the Add query option available once your connection is ready.
Here you can add your queries - simple as well as complex ones. To the right-hand side of the screen, you can see the list of tables from the database. You can expand the tables to view the fields from the table.
You can provide a name to the query using Enter a Service name. Add your query and click Run or press Ctrl + enter to run the query. The rows returned from the table are shown in the Response section.
You can also add dynamic values using the Variables. To add the variable, click Variables > +Add. Add the FieldName and select the Field type. Ensure that you select the field type that matches the column type of the table so that the query works correctly. Add a Test Value to check the results before saving. You can also add a Default value or make it Mandatory. Add the Help text that would be shown with the field. You can also add any preset formula. Once you have entered the details, click Add Variable. The variable would be listed under Variables. You can make use of the variable in the query where you created the variable.
To use a variable inside a query, you simply need to put it into double Curly brackets. The Test value that you entered would be considered for fetching data.
You can now view the queries that you saved for use in your apps later under your specific connector under Custom Database connectors.
Using BigQuery connector
Fetch all rows and apply pagination
Now consider an example where we want to find the author names from the table. Let us first fetch data and apply pagination using the BigQuery Connector. So click Add Query to go to the Query Editor. Here you would add the query with the OFFSET clause. In BigQuery it is important to have an OFFSET to enable the pagination feature.
Run the query to view the results. You can add a dynamic variable if you want to define the OFFSET clause value to specify the number of rows for pagination. Save the query. Your connector is now configured.
Now whenever you want to make use of these Database connectors in your Apps, under Studio Builder, click Connectors > + Add. Select your ready connector and click Continue. Now from the connector select the query for pagination and click Continue. Connect your authenticated account. Based on the query selected, you would have the connector fields that would be used in the BINDAPI formula. Add the connector name and other details and click Finish.
In this example form, to bind the fetched data to the Tablegrid control need to use BINDAPI formula either by adding it to the Custom Formula or through Data > Connectors > Dropdown selection (API name and keys) as shown below.
Using the DB Connector for BigQuery is similar to any other SQLs like PostgreSQL, MySQL and so on. You can make use of the Queries to fetch data as well bind data such that you can undertake the insert, update, delete actions as well. You need to ensure the syntax which would vary a bit for different SQL systems.