How To Send Data From Azure Event Hub to Google Bigquery

Both Microsoft and Google are pillars in the cloud services landscape. While Microsoft's Azure provides a robust real-time event streaming service in Azure Event Hubs, Google Bigquery is a fully managed data warehouse that promises lightning-fast SQL analytics across large datasets. Imagine a scenario where a company leverages Azure's power for its IoT devices, producing a deluge of real-time event data. The logical progression would be to analyze this wealth of information to derive actionable insights, something Google Bigquery excels at. But how does one bridge the gap between these two platforms? In this tutorial, we will explore the capabilities of both platforms and learn how to set up and configure the integration between these two.

Understanding Google BigQuery and Azure Event Hubs

Before diving into the technical details, let's briefly overview Google BigQuery and Azure Event Hubs.

What is Google BigQuery?

Google BigQuery is a fully managed, serverless data warehouse offered by Google Cloud. It is designed to handle massive datasets, enabling businesses to run fast, SQL-like queries on petabytes of data in real-time. With its scalable architecture and advanced analytics capabilities, BigQuery empowers organizations to unlock valuable insights from their data.

BigQuery's serverless architecture eliminates the need for businesses to manage infrastructure, allowing them to focus on data analysis rather than system administration. It automatically scales resources based on the workload, ensuring optimal performance and cost efficiency. This scalability makes BigQuery perfect for organizations with unpredictable or fluctuating data volumes.

Furthermore, BigQuery supports various data formats, including structured, semi-structured, and unstructured data. This flexibility allows businesses to analyze diverse data types, such as JSON, CSV, and Avro, without complex data transformations.

With BigQuery's advanced analytics capabilities, organizations can efficiently perform complex queries, aggregations, and joins on large datasets. It also integrates seamlessly with other Google Cloud services, such as Google Data Studio and Google Cloud Storage, enabling businesses to build end-to-end data analytics solutions.

While BigQuery is primarily a data warehouse, Google has also extended its capabilities to interact with "data lake" architectures.

In summary, what sets BigQuery apart is:

  • Speed: Thanks to Google's infrastructure, even complex SQL queries across massive datasets execute in seconds or minutes.
  • Serverless architecture: There's no infrastructure to manage or database administrator needed. This ensures cost efficiency and a focus on analytics instead of operations making BigQuery a perfect choice for organizations dealing with unpredictable or fluctuating data volumes.
  • Machine Learning capabilities: With BigQuery ML, data scientists can build machine learning models directly within the platform using SQL.

What is Azure Event Hubs?

Azure Event Hubs is a highly scalable and secure event streaming platform provided by Microsoft Azure. It acts as a real-time data ingestion service, capable of receiving and processing millions of events per second. Event Hubs allows businesses to build real-time analytics solutions, monitor IoT devices, and process live data streams at scale.

Event Hubs supports multiple protocols, including AMQP, HTTPS, and Apache Kafka, allowing businesses to ingest data from various sources. It also provides features like event capture, which enables organizations to store and analyze historical data, and event routing, which allows for intelligent routing of events to different endpoints based on specific conditions or rules.

With its seamless integration with other Azure services like Azure Functions, Azure Stream Analytics, and Azure Machine Learning, Event Hubs enables businesses to build comprehensive real-time analytics solutions. These solutions can process, analyze, and visualize data as it arrives, enabling organizations to make informed decisions and take immediate actions based on real-time insights.

In summary, Azure Event Hubs is especially notable for:

  • Scalability: Its ability to auto-scale allows it to handle millions of events per second, ensuring that data is reliably ingested and processed in real-time or batch.
  • Integration with Popular Frameworks: Azure Event Hubs fits well with tools that support Kafka. This ensures a more seamless integration process, even in complex data architectures.

Primary use cases for Azure Event Hubs:

  • Telemetry Data: From IoT devices, user interactions, and application logs.
  • Stream Processing: Real-time analytics and dashboards.
  • Big Data Pipelines: Integration with other Azure services for big data analytics.

Why integrate Azure Event Hubs with Google Big Query

Integrating Azure Event Hubs with Google BigQuery lets you channel real-time event data into a potent analytics engine. The advantages are manifold:

  • Real-Time analytics: BigQuery's processing power combined with Azure Event Hub's real-time data capabilities means businesses can deliver insights quickly.
  • Cost efficiency: Businesses can utilize these existing platforms to achieve their data goals instead of investing in additional infrastructure or tools.
  • Comprehensive data solutions: From ingesting raw data using Azure to processing and visualizing it with BigQuery, businesses can have an end-to-end data solution with just these two platforms.

To fully exploit the capabilities of these two giants in the world of cloud and analytics, a seamless integration is imperative. In the following sections, we'll delve deeper into how this integration can be achieved, unlocking the full potential of real-time data analytics and insights.

Setting Up Your Google BigQuery Account

Before sending data from Event Hubs to BigQuery, you need to set up your Google BigQuery account.

As Google BigQuery is a part of the Google Cloud Platform, you must sign up there and enable BigQuery API. Here are the steps to get you started:

Creating a Google Cloud project

First, create a new Google Cloud project or use an existing one. A Google Cloud project provides the organizational structure for managing resources and services within Google Cloud.

When creating a new project, you'll need to provide a unique project name and select the appropriate billing account. You can also enable APIs and services relevant to your project's needs.

Once your project is created, you can configure the necessary permissions to control access to your project's resources. This includes managing roles and permissions for project members, service accounts, and external identities.

It's important to carefully consider the permissions you grant to ensure the security and privacy of your data. You can assign different roles to different users or groups, allowing them to perform specific actions within the project.

For an existing project, you can review and update the project's settings and permissions to align with your requirements. This may involve adding or removing members, adjusting roles, or configuring service accounts.

Enabling BigQuery API

Once you set up your Google Cloud project, you need to enable the BigQuery API. The API provides programmatic access to BigQuery, allowing you to interact with datasets, tables, and other BigQuery resources via RESTful API calls.

To enable the BigQuery API, navigate to the Google Cloud Console and select your project. Click "APIs & Services" in the navigation menu and then "Library". Search for "BigQuery API" and click on the result.

On the BigQuery API page, click on the "Enable" button to enable the API for your project. This will give you access to the API's features and functionalities.

In addition to enabling the API, you'll need to authenticate your requests to BigQuery. This can be done by creating and managing service accounts, which are used to represent your application or service when interacting with BigQuery.

Service accounts have their own set of credentials, including a private key, which can be used to authenticate API requests. You can create and manage service accounts through the Google Cloud Console and assign them the necessary roles and permissions.

Once you have enabled the BigQuery API and obtained the necessary credentials, create a dataset for Event Hubs data and define tables. You will need this later in this tutorial.

Setting Up Your Azure Event Hubs Account

Now that your Google BigQuery account is ready, it's time to set up your Azure Event Hubs account. Here's how you can get started:

Creating an Azure Account​​

If you don't already have an Azure account, sign up for one and create a new subscription. Azure provides a wide range of cloud services, including Event Hubs, that can be seamlessly integrated with other platforms.

Azure offers a free trial subscription that allows you to explore and experiment with various Azure services, including Event Hubs. This trial subscription provides you with a limited amount of resources to get started, making it an excellent option for beginners.

Once you have signed up for an Azure account, you will have access to the Azure portal, a web-based interface that allows you to manage and monitor your Azure resources. The portal provides a user-friendly experience, making navigating and setting up your Event Hubs account easy.

Configuring an Event Hubs Namespace

After creating your Azure account, you must set up an Event Hubs namespace. A namespace acts as a container for multiple Event Hubs, providing an isolated environment for handling event data. You can create a namespace using the Azure portal or programmatically via the Azure SDKs.

When creating a namespace, you will need to choose a unique name that identifies your namespace within Azure. It's important to choose a descriptive and meaningful name that reflects the purpose of your Event Hubs account. This will make it easier for you to manage and identify your namespaces in the future.

Once you have created your namespace, you can start creating Event Hubs within it. Event Hubs are the entities that receive and store the event data. You can create multiple Event Hubs within a single namespace, allowing you to organize and manage your event data effectively.

When creating an Event Hub, you will need to specify a name for the Event Hub and configure various settings, such as the number of partitions and retention period. Partitions allow you to scale your Event Hubs and handle high volumes of event data, while the retention period determines how long event data will be stored in the Event Hub before it is automatically deleted.

Once your Event Hubs namespace and Event Hubs are set up, you can start streaming data. Azure provides various client libraries and SDKs that make it easy to stream data to Event Hubs from your applications, regardless of the programming language or platform you are using.

With your Azure Event Hubs account ready, you can now begin leveraging the power of event streaming and real-time data processing in your applications.

Sending data from Azure Event Hubs to Google BigQuery

Azure Event Hubs do not provide direct integration with Google BigQuery, so you’ll need to do some programming to receive data from Event Hubs and then send it to BigQuery. You can use the SDK (‘azure-eventhub` Python package) to receive data from Event Hubs. Then, to send data to BigQuery, you may use its SDK (`google-cloud-bigquery` Python package). Azure Events Hubs and Google BigQuery provide SDKs in popular programming languages such as Java, Go, Javascript, and Python. For this tutorial, we will use Python for programming, but the same concepts will apply to all other languages.

Let’s dive into the steps in sending data from Azure Event Hubs to Google BigQuery.

Installing the necessary packages

Azure Event Hubs provides a Python SDK `azure-eventhub` to interact with it. Google BigQuery also provides an API client library in Python, `google-cloud-bigquery`. Let’s install both of these Python packages for our project.

SH
pip install azure-eventhub google-cloud-bigquery

Setting up credentials for authentication

Ensure you have a service account JSON key from the Google Cloud Console. Then set the `GOOGLE_APPLICATION_CREDENTIALS` environment variable to the path of the service account key. Also, note down your BigQuery project id and dataset id.

Similarly, get your Event Hub Namespace connection string from your Azure account. We will need that to connect to Event Hubs from our Python program.

Writing Python code to receive events from Azure Event Hubs and sending them to Google BigQuery

PYTHON
from azure.eventhub import EventHubConsumerClient


from google.cloud import bigquery








# Initialize BigQuery client


bigquery_client = bigquery.Client()


table_id = "YOUR_PROJECT_ID.YOUR_DATASET_ID.YOUR_TABLE_ID"








def on_event(partition_context, event):


# Handle the event data


print("Received event from partition: {}".format(partition_context.partition_id))


event_data = event.body_as_str()








# Convert event data to BigQuery row format if necessary


# For this example, let's assume event_data is a JSON string


row = [json.loads(event_data)]








# Send data to BigQuery


errors = bigquery_client.insert_rows_json(table_id, row)


if errors:


print("Failed to load row to BigQuery:", errors)


else:


print("Row successfully sent to BigQuery")








def on_error(partition_context, error):


# Handle errors


print("Error on partition {}: {}".format(partition_context.partition_id, error))








if __name__ == '__main__':


consumer_client = EventHubConsumerClient.from_connection_string(


conn_str="YOUR EVENT HUBS NAMESPACE CONNECTION STRING",


consumer_group="$Default",


eventhub_name="YOUR EVENT HUB NAME"


)








try:


consumer_client.receive(


on_event=on_event,


on_error=on_error,


starting_position="@latest", # `@latest` to receive only new events. Set to "-1" for all the events since the beginning of the partition


)


except KeyboardInterrupt:


print("Receiving has stopped.")


finally:


consumer_client.close()

Replace placeholders:

  • Replace YOUR EVENT HUBS NAMESPACE CONNECTION STRING and YOUR EVENT HUB NAME with your Azure Event Hubs details. Instead of hardcoding credentials, it is a best practice to use environment variables to store and access them.
  • Replace YOUR_PROJECT_ID, YOUR_DATASET_ID, and YOUR_TABLE_ID with your Google BigQuery details.

Run the Python script:

  • Execute the script. It will start listening for events from the specified Event Hub, and as events arrive, they will be sent to the specified BigQuery table.

Let's break down the code and explain each section:

1. Importing necessary libraries

PYTHON
from azure.eventhub import EventHubConsumerClient


from google.cloud import bigquery

Here, we're importing the necessary libraries. `EventHubConsumerClient` is used to consume events from Azure Event Hubs, and `bigquery` is used to interact with Google BigQuery.

2. Initialize BigQuery client

PYTHON
bigquery_client = bigquery.Client()


table_id = "YOUR_PROJECT_ID.YOUR_DATASET_ID.YOUR_TABLE_ID"

We're initializing the BigQuery client and specifying the table where we want to insert our data. Replace the placeholders with your actual BigQuery project, dataset, and table details.

3. Initialize Event Hub Consumer client and receive events

PYTHON
if __name__ == '__main__':


consumer_client = EventHubConsumerClient.from_connection_string(


conn_str="YOUR EVENT HUBS NAMESPACE CONNECTION STRING",


consumer_group="$Default",


eventhub_name="YOUR EVENT HUB NAME"


)








try:


consumer_client.receive(


on_event=on_event,


on_error=on_error,


starting_position="@latest",


)


….

This is the main execution block:

- We initialize the `EventHubConsumerClient` with connection details to Azure Event Hubs.

- We start the event receiving process with `consumer_client.receive(...)`. This will keep running and listening for new events. We have plugged our `on_event` function here to capture those events.

4. Handle new events

PYTHON
def on_event(partition_context, event):


...

We will use this function to capture events received from Azure Event Hubs. Inside this function:

- We print the partition from which the event was received.

- We extract the event data.

- We assume the event data is in JSON format and convert it to a format suitable for BigQuery.

- We then send this data to BigQuery.

5. Send data to BigQuery:

Inside the `on_event` function, we use the following code to send data to BigQuery:

PYTHON
errors = bigquery_client.insert_rows_json(table_id, row)

This line attempts to insert the received event data (formatted as a JSON row) into the specified BigQuery table. If there are any errors during this process, they are captured in the `errors` variable and printed.

Overall, the script acts as a bridge between Azure Event Hubs and Google BigQuery. It listens for events from Azure Event Hubs in real time. When an event is received, the script processes the event data and sends it directly to a specified table in Google BigQuery. This allows for real-time data ingestion from Azure Event Hubs into BigQuery without the need for intermediate storage.

This example provides a basic structure to get you started. Remember to appropriately handle exceptions, potential errors, and rate limits in a production environment. Based on your requirements, you might have specific constraints related to storage, CPU capacity, fault tolerance, expected data format, concurrency, and expected performance. Depending on your requirements, you might use different approaches, such as modifying code to process events in batches, using checkpoints, using temporary storage, using a serverless function to process events, or using 3rd party connectors.

Conclusion

Integrating Azure Event Hubs and Google BigQuery can enable several use cases. With this guide, you can complete the steps required to send data from Azure Event Hubs to Google Big Query. While we covered a basic implementation, You may find it helpful to read the Azure Event Hubs docs and Google BigQuery docs to further understand the nuances of this integration.

Don't want to go through the pain of direct integration? RudderStack's Reverse ETL connection makes it easy to send data from your Google BigQuery Data Warehouse to Azure Event Hubs.