Process events using SQL code editor (preview)

SQL code editor aka SQL operator (preview) is a new data transformation capability within Fabric Eventstream. SQL operators provide code editing experience where you can easily define your own custom data transformation logic using simple SQL expressions. This article describes how to use SQL code editor for data transformations in Eventstream.

Prerequisites

Before you start, you must complete the following prerequisites:

• Access to a workspace in the Fabric capacity license mode (or) the Trial license mode with Contributor or higher permissions.

Add SQL code editor to an eventstream

To perform stream processing operations on your data streams using SQL operator, add a SQL operator to your eventstream using the following instructions.

1. Create a new eventstream and add SQL operator to your event stream by using one of the following options:

   • Add SQL operator using the Transform Events button at the top ribbon.

     

   • Add SQL operator through the Add destination button in the canvas.

     

2. Once selected, a new SQL node is added to your eventstream. Select the pencil icon to complete the SQL Operator set up.

   

3. In the SQL Code window, specify a unique name for the SQL operator node in the eventstream.

4. Edit the query in the Query window or select Edit query to enter the full screen code editor view.

   

5. The full-screen code editor mode features an input/output explorer panel on the left side. The code editor section is adjustable, allowing you to resize it according to your preferences. The preview section at the bottom enables you to view both your input data and query test results.

   

6. Select the text in the Outputs section, and enter a name for the destination node. SQL operator supports all Real-Time Intelligence destinations: Eventhouse, Lakehouse, Activator, Stream, etc.

   

7. Specify an alias or name for the output destination where the data processed through SQL operator is written.

   

8. Next, add SQL query for the required data transformation.

   Eventstream is built on top of Azure Stream Analytics and it supports the same query semantics of Stream Analytics query language. To learn more about the syntax and usage, see Stream Analytics Query Language Reference - Stream Analytics Query | Microsoft Learn.

   Basic query structure:

   SELECT 
   
       column1, column2, ... 
   
   INTO 
   
       [output alias] 
   
   FROM 
   
       [input alias] 
   

   Query examples:

   Detecting high temperatures in a room every minute:

   
       SELECT 
       System.Timestamp AS WindowEnd, 
       roomId, 
       AVG(temperature) AS AvgTemp 
   INTO 
       output 
   FROM 
       input 
   GROUP BY 
       roomId, 
       TumblingWindow(minute, 1) 
   HAVING 
       AVG(temperature) > 75 
   

   Use CASE Statement to categorize temperature:

   SELECT
       deviceId, 
       temperature, 
       CASE  
           WHEN temperature > 85 THEN 'High' 
           WHEN temperature BETWEEN 60 AND 85 THEN 'Normal' 
           ELSE 'Low' 
       END AS TempCategory 
   INTO 
       CategorizedTempOutput 
   FROM 
       SensorInput 
   

9. Use the Test query button from the top ribbon to validate the transformation logic. Test query result appears under the test-result section at the bottom.

   

10. When you're done, select Save on the ribbon at the top to get back to the eventstream canvas.

    

11. In the SQL Code window, if the Save button is enabled, select it to save the settings.

    

12. Configure the destination.

    

Limitations

1. The SQL operator is designed to centralize all your transformation logic in one place. As a result, it cannot be used alongside other built-in operators within the same processing path. Chaining multiple SQL operators in a single path is also not supported. Additionally, the SQL operator can only output data to the destination node in the topology.

2. Currently, authoring Eventstream topologies is only supported through the user interface (UX). REST API support for the SQL operator is planned and will be available in upcoming releases.

Related content

• Add and manage destinations in an eventstream.