Understanding Federated Query BigQuery Made Easy

What if someone asked you some incredibly specific questions regarding your customers. With this in mind, this post dives into Big Data Analytics and Queries. Specifically, it is designed to make you understand Federated Query BigQuery.

Say, how many clients does your business have? How many of these customers are actively engaged with your business? How many clients are in specific regions?

What are BigQuery Federated Queries?

• When using data to make critical business decisions, you need information from different Data Marts, Warehouses, and Transactional Databases to draw the required statistics.
• Let’s take a situation where you work with BigQuery as your Data Warehouse and CloudSQL as your Relational Database. Y
• ou need to find a bridge between these two systems, right?
• Simply put, a way to send a query to an external Database and get the output in the form of a temporary table. These queries rely on the BigQuery Connection API to connect with an external Database. 

In our case, we would use the EXTRENAL_QUERY function to connect with CloudSQL. We would then query the data in this platform and get the results as temporary tables.

Below is a sample of a Federated Query BigQuery:

SELECT * FROM EXTERNAL_QUERY 
("test-fedquery-mysql", "SELECT customer_id, MIN(order date) AS first_order_date
 FROM orders
 GROUP BY customer_id;");

Setting Up Federated Query BigQuery

Step 1: Adding External Data Source

Navigate to BigQuery, select “Add Data” and click “External Data Source“.

Step 2: Input Source Details

Key in the “External data source” credentials.

Step 3: Connect to Instance

Feel free to copy the Cloud SQL Instance ID from the SQL instance page under “Connection name“.

That’s it! By following the steps above. You have successfully set up a connection from Google BigQuery to an External Database using the Federated Query BigQuery.

Conclusion

In this post, you learnt what Google BigQuery is and some of the features it entails. More importantly, you learnt what Federated Query BigQuery is and how to implement them. Now, you stand a better chance to use Cloud SQL and Cloud Spanner together with Bug Query.

However, extracting data from a wide variety of sources and connecting to BigQuery is a tedious and time taking process but using a Data Integration tool like Hevo can perform this process with no effort and no time.

Sign up for a 14-day free trial today. Hevo offers plans & pricing for different use cases and business needs, check them out!