In this lab you will use an Azure Logic App to retrieve high-frequency stock market of Cryptocurrency. Every second the LogicApp retrieve stock purchase transactions for BTC / USDT. The LogicApp then sends stock transaction messages to Event Hubs. You will use Stream Analytics queries to join hot and cold data streams to process the high volume of transactions and generate aggregate calculations. The results will be sent to a real-time dataset in Power BI.
IMPORTANT: This lab requires you have a valid Power BI account. If you don’t have one you can register for a 60-day trial here: https://powerbi.microsoft.com/en-us/power-bi-pro/
The estimated time to complete this lab is: 60 minutes.
ONCE YOU HAVE COMPLETED, PLEASE DELETE ALL RESOURCES YOU USED IN THE WORKSHOP FROM YOUR AZURE PORTAL TO PREVENT CONTINUED USAGE OF YOUR CREDITS.
The following Azure services will be used in this lab. If you need further training resources or access to technical documentation please find in the table below links to Microsoft Learn and to each service's Technical Documentation.
| Azure Service | Microsoft Learn | Technical Documentation |
|---|---|---|
| Azure Data Lake Gen2 | Large Scale Data Processing with Azure Data Lake Storage Gen2 | Azure Data Lake Gen2 Technical Documentation |
| Azure Logic Apps | Build automated workflows to integrate data and apps with Azure Logic Apps | Azure Logic Apps Technical Documentation |
| Azure Event Hubs | Enable reliable messaging for Big Data applications using Azure Event Hubs | Azure Event Hubs Technical Documentation |
| Azure Stream Analytics | Implement a Data Streaming Solution with Azure Streaming Analytics | Azure Stream Analytics Technical Documentation |
| Power BI | Create and use analytics reports with Power BI | Power BI Technical Documentation |
| Step | Description |
|---|---|
 |
Review the Azure Logic App logic that retrieves the Crypto transaction stream sent to EventHubs |
 |
Save Crypto stock transaction messages into your data lake for future analysis (cold path) |
 |
Send stream of Crypto stock transaction messages to Stream Analytics for real-time analytics (hot path) |
 |
Visualize real-time data generated by Stream Analytics with Power BI |
IMPORTANT: Some of the Azure services provisioned require globally unique name and a “-suffix” has been added to their names to ensure this uniqueness. Please take note of the suffix generated as you will need it for the following resources in this lab:
| Name | Type |
|---|---|
| synapsedatalakesuffix | Storage Account |
| ADPEventHubs-suffix | Event Hubs Namespace |
| SynapseStreamAnalytics-suffix | Stream Analytics job |
In this section you will create the CryptoTrades container in your SynapseDataLake that will be used as a repository for the Crypto stock transactions. You will use the EventHubs Capture feature to save the incoming stream of transactions in your data lake. We will use an API from Binance.
| IMPORTANT |
|---|
| Execute these steps on your host computer |
-
In the Azure Portal, go to the lab resource group, and then locate and click the Azure Storage account synapsedatalakesuffix.
-
On the Overview panel, click Containers.
-
On the synapsedatalakesuffix – Containers blade, click + Container. On the New container blade, enter the following details:
- Name: cryptotrades
- Public access level: Private (no anonymous access)
-
Click OK to create the new container.
In this section you will review the implementation of the LogicApp used to retrieve high-frequency stock purchase transactions. These transactions will be formatted as JSON messages and sent to Event Hubs for processing.
- Add HTTP Trigger and configure as following
- Method: GET
- URI: https://api.binance.com/api/v3/trades
- Queries: symbol = BTCUSDT & limit = 1000
- Run Every: 2 seconds
- Add ParseJSON action and configure using the following schema
{
"items": {
"properties": {
"id": {
"type": "integer"
},
"isBestMatch": {
"type": "boolean"
},
"isBuyerMaker": {
"type": "boolean"
},
"price": {
"type": "string"
},
"qty": {
"type": "string"
},
"quoteQty": {
"type": "string"
},
"symbol": {
"default": "BTC/USDT",
"type": "string"
},
"time": {
"type": "integer"
}
},
"required": [
"id",
"price",
"qty",
"quoteQty",
"time",
"isBuyerMaker",
"isBestMatch"
],
"type": "object"
},
"type": "array"
}
- Add a EventHub send event action and configure as following
-
Save the Logic App
-
The format of each stock purchase transaction message generated looks like this:
{
"id": 420536769,
"price": "0.08310000",
"qty": "1961.00000000",
"quoteQty": "162.95910000",
"time": 1653493514429,
"isBuyerMaker": true,
"isBestMatch": true
}-
Return to the Overview panel. Note that the LogicApp is disabled by default. Click Enable to enable the LogicApp to start firing every second.
In this section you will prepare Event Hubs to ingest Crypto stock trade messages generated by the LogicApp and save to your Synapse Data Lake account.
| IMPORTANT |
|---|
| Execute these steps on your host computer |
-
In the Azure Portal, go to the lab resource group and locate the Event Hubs resource ADPEventHubs-suffix.
-
On the Event Hubs panel, note that the cryptotrade Event Hub has already been created for you. This is the same Event Hub you saw referenced by the LogicApp in the previous section.
-
Click on the cryptotrade Event Hub to open its settings. Then click on the Capture item on the left-hand side menu.
-
Enter the following details:
- Capture: On
- Time window (minutes): 1
- Do not emit empty files when no events occur during the capture time window: Checked.
- Capture Provider: Azure Storage
- Azure Storage Container: [select the cryptotrades container in your synapsedatalakesuffix storage account] -
Leave remaining fields with their default values.
-
Click Save Changes.
-
On your SynapseDataLake account, navigate to the CryptoTrades container you created in the previous section.
-
You should be able to see the folder structure created by Event Hubs Capture with AVRO files containing the individual stock purchase transaction messages generated by the LogicApp. These files can then be used in other analytics worlkloads whenever the granular detail about each individual transaction is required.
In this section you will configure your Stream Analytics job to join hot and cold data streams and execute queries on data sent by Event Hubs and generate outputs to Power BI.
| IMPORTANT |
|---|
| Execute these steps on your host computer |
-
In the Azure Portal, go to the lab resource group and locate the Stream Analytics resource SynapseStreamAnalytics-suffix.
-
On the Inputs panel, click + Add stream input button and select Event Hub to create a new input stream.
-
On the Event Hub New input blade enter the following details:
- Input alias: CryptoTrades
- Event Hub namespace: ADPEventHubs-suffix
- Event hub name > Use existing: cryptotrades -
Leave remaining fields with their default values.
-
Not click on the Outputs panel on the left-hand side menu. Once it is loaded, click + Add button and select Power BI to create a new output stream.
-
On the Power BI New Output blade, click Authorize to authenticate with Power BI. Enter your credentials to authenticate.
-
Once authenticated, enter the following details:
- Output alias: BTCUSDTCandleSticks
- Authentication Mode: User Token
- Group Workspace: My Workspace
- Dataset name: BTCUSDTCandleSticks
- Table name: BTCUSDTCandleSticks
IMPORTANT: Set Authentication Mode to User Token before you can select My Workspace for Group Workspace.
-
Leave remaining fields with their default values.
-
Click Save to save your output stream and return to the Outputs panel.
-
On the Query panel, note the inputs and outputs you created in the previous steps.
-
Enter the following SQL commands in the query window.
--BTC/USDT Candlesticks for past 30 seconds and calculated every 5 seconds
SELECT
'BTC/USDT' as Symbol
,SUM(CAST(Trades.qty AS float)) AS Volume
,MAX(CAST(Trades.price AS float)) AS High
,MIN(CAST(Trades.price AS float)) AS Low
,CAST(GetRecordPropertyValue((TopOne() OVER (ORDER BY Trades.time ASC)),'price') AS float) AS [Open]
,CAST(GetRecordPropertyValue((TopOne() OVER (ORDER BY Trades.time DESC)),'price') AS float) AS [Close]
,COUNT(*) as NoOfTrades
,SUM(CAST(Trades.quoteQty AS float)) AS TotalAmount
, System.Timestamp AS WindowDateTime
INTO
[BTCUSDTCandleSticks]
FROM
[CryptoTrades] AS Trades TIMESTAMP BY dateadd(MS, [time], '1970-01-01')
GROUP BY 'BTC/USDT', HoppingWindow(second, 30, 5)-
Click Save query.
-
On the Overview panel, click Start to start the Stream Analytics job.
-
On the Start job blade, select Now and click the Start button.
In this section you will log on to the Power BI portal to create a dashboard to visualize real-time stock transactions statistics data sent by Stream Analytics.
| IMPORTANT |
|---|
| Execute these steps on your host computer |
-
Open a new browser tab and navigate to https://www.powerbi.com
-
Enter your credentials to authenticate with the Power BI service.
-
Once authenticated, open the Workspaces menu and click My Workspace at the top of the Workspaces list.
-
Navigate to the Datasets tab and verify that two datasets have been created by Stream Analytics: BTCUSDTCandleSticks.
-
On the top lef-hand side corner click + Create and then click Dashboard from the dropdown menu to create a new dashboard.
-
Type Crypto Trade Activity in the Dashboard name field and click Create.
-
Click on the (elipsis) ... button from the toolbar and then click on the + Add tile menu item.
-
On the Add tile blade, select Custom Streaming Data under the Real-Time Data section.
-
Click Next.
-
On the Add a custom streaming data tile blade, select the StockTradeTotals dataset.
-
Click Next.
-
On the Visualization Type field select Card.
-
On the Fields field select TotalAmount.
-
Click on the brush icon to set the Value decimal places field to 2.
-
Click Next.
-
On the Tile details blade, enter the following details:
- Title: Total Traded Amount (USD)
- Subtitle: in the last 30 seconds
-
Leave remaining fields with their default values. Click Apply.
-
Repeat the process to create another tile, this time to display the total trade count. Use the following details:
- Visualization Type: Card
- Fields: TotalTradeCount
- Details > Title: Total Trade Count
- Details > Subtitle: in the last 30 seconds
-
You should be able to see the values for both tiles changing every 5 seconds.
-
Repeat the process to create another tile, this time to display the historical values for BTC/USDT CandleSticks over the last 5 minutes.
- Visualization Type: Line Chart
- Axis: WindowDateTime
- Value: High
- Value: Low
- Value: Open
- Value: Close
- Time window to display: 5 minutes
- Details > Title: BTC/USDT Candlesticks
- Details > Subtitle: 5 min history window
-
Your real-time dashboard should look similar to the picture below. Every tile should be refreshed approximately every 5 seconds.
