Transport Data Ingestion Pipeline

Architecture

Table of Contents

1. Introduction
2. Tools Used
3. Data Set
4. Setting Up Azure Event Hub
5. Setting Up Azure Stream Analytics
   • Setting Up Input as Azure Event Hub
   • Setting Up Output as SQL Database
6. Create an SQL Database
7. Create a Table in the Database
8. Run the Python Script on the Local Machine
9. Perform Your Visualization with Power BI
   • Connecting to Azure SQL Database
   • Building Visualizations

Introduction

This project is aimed at building a data ingestion pipeline for a transport dataset. The pipeline streams data from a local machine into Azure Event Hubs, uses Azure Stream Analytics to process this data in real-time, and stores it in an Azure SQL Database for further visualization using Power BI.

Tools Used

• Azure Event Hubs: For ingesting real-time streaming data.
• Azure Stream Analytics: For processing the streaming data in real-time.
• Azure SQL Database: For storing the processed data.
• Python: For scripting the data ingestion from the local machine.
• Power BI: For visualizing the stored data and uncovering insights.

Data Set

The transport dataset contains various attributes related to transportation, such as transportation type, geographical name, race, timestamp, etc. You can find the file in the data folder above.

Setting Up Azure Event Hub

1. Create a Namespace: Navigate to the Azure Portal, and create a new Event Hubs Namespace.

2. Create an Event Hub: Inside the namespace, create an Event Hub.

3. Get Connection String: Go to Shared access policies, create a new policy, and copy the connection string, save it somewhere for later as your Python script would need it to create a connection

Create an SQL Database

1. Create SQL Database: In the Azure Portal, create a new SQL Database.
2. Configure Database: Set up the server, login credentials, and firewall rules to allow connections.

Create a Table in the Database

Navigate to the SQL Database created and use the SQL query window or SQL Server Management Studio to create a table that aligns with the data schema of the incoming data.

Setting Up Azure Stream Analytics

Setting Up Input as Azure Event Hub

1. Create a Stream Analytics Job: Navigate to Azure Portal and create a Stream Analytics job.
2. Add Input: In the job topology, add an input and choose Azure Event Hub. Paste the connection string copied from the Event Hub setup.

Setting Up Output as SQL Database

1. Create Output: In the job topology, add an output and select Azure SQL Database.
2. Configure Output: Fill in the necessary details such as SQL Database credentials, table name, etc. once this is done write you query and start the job

Run the Python Script on the Local Machine

Use a Python script to read the transport dataset and send events to the Azure Event Hub as messages.

Refresh your table to check if the messages are being streamed to your database

Perform Your Visualization with Power BI

Connecting to Azure SQL Database

1. Open Power BI Desktop: If you don’t have Power BI Desktop, you can download it here.
2. Get Data: On the Home tab, click on "Get Data".
3. SQL Server Database: In the Get Data window, select "SQL Server" and click on "Connect".
4. Database Connection: Enter the server address and database details. Use the credentials set up earlier for the Azure SQL Database.
5. Load Data: Select the table you have created and click on "Load".

Building Visualizations

1. Drag and Drop Fields: Use the Fields pane to select the columns you want to visualize. Drag and drop them into the Values and Axis areas.
2. Choose Visualization Type: Select the type of visualization you want (e.g., bar chart, line chart, map) from the Visualizations pane.
3. Filter and Customize: Use filters to refine your visualization(the geographical location was used as a filter in this case) and the Format pane to customize the appearance.