BigQuery is a fully managed data warehouse by Google Cloud that allows you to analyze massive datasets using SQL-like queries. It's designed for speed and scalability, enabling organizations to process and gain insights from large volumes of data quickly and efficiently.
To establish a connection with Google BigQuery as a connector, follow these steps:
Google Cloud Account: Ensure you have a valid Google Cloud Platform (GCP) account with the necessary permissions for BigQuery access.
Google Cloud Credentials: Obtain Google Cloud Credentials for authentication. For Service Account Authentication and the private key, refer to this detailed article here.
Service Account Creation: Create a Service Account within
Google Cloud Storage > APIs and Services > Credentials. If not created, select
Service Accountand provide account details, description, and access permissions to projects and users.
- Generate JSON Key: From the created Service Account, generate a JSON Key. Provide necessary details and permissions, and download the JSON file. Keep it accessible for configuring the connector.
- Connector Configuration: Configure the BigQuery connector using the obtained Google Cloud Credentials. Set the necessary authentication method and use the downloaded JSON Key for private key authentication.
Configuring Connector in DronaHQ
Configure your database category using the provided details. Validate connection with
Test connection and
settings for secure database setup.
|If using a Service Account key, provide the JSON key file obtained during Service Account creation.
|Mention the region to specify the geographic location where the data resides.
value connection options in database configuration refer to specific settings (keys) and their corresponding values that dictate how the application connects to the database
|Enhance security by restricting database access to specific whitelisted IP addresses.
Adding Database Queries
After configuring the connector, access it in your Connector Library.
Add query once the connection is established. Create queries, run them, and view responses.
Use double curly brackets for dynamic variables. Test with sample values before saving. Link dynamic variables to controls/actions for runtime use.
Saved queries appear under your connector in Connector Library.
Using BigQuery Connector
Data Bind using Data Query
Read/Display Data Query for BigQuery:
Start by crafting an SQL query to extract data from your Google BigQuery database. Consider a scenario where you want to
retrieve names from the
bigquery-public-data.usa_names.usa_1910_2013 table for the state 'TX', limiting the results to
100 rows. The SQL query is:
SELECT name FROM bigquery-public-data.usa_names.usa_1910_2013 WHERE state = 'TX' LIMIT 100;
This SQL query retrieves names from the
bigquery-public-data.usa_names.usa_1910_2013 table where the state is 'TX'.
The results are limited to 100 rows.
Integrate the fetched data into your application's interface.
a. Access the Controls section and introduce the Table grid control.
b. Navigate to
Data Bind Options -> Quick Select -> Database Queries.
c. Opt for the BigQuery connector and choose the query that aligns with your data presentation goals.
By following these steps, you can seamlessly retrieve and display data from the
bigquery-public-data.usa_names.usa_1910_2013 table using the BigQuery Connector. The data is filtered and limited
based on your specified criteria, enhancing your application's functionality and user experience.