IoT - Databricks Deltalake - access in C# api or Node js API

Question

I am working on IoT solution, where there are multiple sensors which are sending data. I have one job which listen to Event hub, get the IoT sensor data and store in in Delta lake table. (Underlying Azure ADLS Gen-2 Storage in parquet file format)

I have to display the sensor data on UI (custom UI - developed in React). For that, I have API layer developed in .NET core API / Node JS API.

So finally I have to query Delta table created in data bricks to retrieve the sensor data - using Node JS / .NET CORE and display it on UI. How I can query the data from C# / Node JS API from delta lake table?

Answer 1

You may be better off using query compute that already is capable of reading the delta formatted data. A low cost option would be to create an Azure Synapse Analytics workspace and use a serverless SQL pool to query the delta content. A serverless pool exposes itself as an Azure SQL DB. So, any tool that can query an Azure SQL DB (not a problem for either C# or node.js) can then query those delta tables.

The SQL syntax is a little different looking as it uses an OPENROWSET like follows:

SELECT
    *
FROM
    OPENROWSET(
        BULK 'https://<my-storage-account>.dfs.core.windows.net/<my-container>/<path-to-delta-folder>/',
        FORMAT='DELTA'
    ) AS [recordset];

Alternatively, you can create a logical database in the serverless pool and create external tables for each of your delta folders. Doing this would make it seems a little closer to a traditional relational database.

Since this is a serverless instance there is no provisioning costs. You'd only pay for the consumption (e.g. the actual query). You can find the current pay-as-you go pricing here: Azure Synapse Analytics pricing