search

grafana / grafana

The open and composable observability and data visualization platform. Visualize metrics, logs, and traces from multiple sources like Prometheus, Loki, Elasticsearch, InfluxDB, Postgres and many more.
https://grafana.com
GNU Affero General Public License v3.0
64.66k stars 12.1k forks source link

Grafana + Azure Synapse Serverless: Possible via existing data source? (e.g. Microsoft SQL Server) #88427

Closed MatinF closed 4 months ago

MatinF commented 5 months ago

Why is this needed:

Today, it is easy to visualize data in Grafana from Parquet data lakes stored in Amazon (via Athena) or Google (via BigQuery) in a serverless manner. This is a critical and powerful tool for working with big data.

In Azure, there is an identical interface concept through Synapse Serverless SQL Pools. These enable identical functionality/pricing as Athena/BigQuery - but with the major downside that no data source plugin exists for Grafana.

To us, this seems like a major 'white spot' in the data source options.

You can see our step-by-step documentation on integrating vehicle/machine data with Grafana below - as evident, we cover Grafana-Athena, Grafana-BigQuery - and would love to cover Grafana-Synapse. However, today we are forced to instead cover 'PowerBI-Synapse': https://canlogger.csselectronics.com/canedge-getting-started/ce3/log-file-tools/browser-dashboards/

What would you like to be added:

We would ideally like an Azure Synapse data source plugin to be added for Grafana. However, if this is not possible, perhaps an alternative would be to integrate via one of the existing SQL plugins - however, we have not been able to successfully do so. If somebody at Grafana can clarify if this would be possible, it would be very helpful.

Who is this feature for?

This would be useful for users that work with Parquet data lakes in Azure using a serverless Synapse interface. They would be able to directly visualize their data lake in Grafana, which would enable Grafana to claim full support for serverless data lake queries across the top 3 clouds.

MatinF commented 5 months ago

Edits to title

MatinF commented 5 months ago

I tried the Microsoft SQL Server data source route and got a bit further, but I seem stuck on the permissions.

Within Synapse Studio, I can create an SQL script as below, which works:

SELECT TOP(50) AVG(Speed)
FROM serverlessdbtest.dbo.CAN2_gnssspeed

I am now trying to do this externally from a Grafana Cloud account. I have done the following:

1) Added a Microsoft SQL Server data source 2) In the authentication I used the below settings:

enter image description here

Note here that the user details I have specified come from below:

enter image description here

The above lets me connect to the Azure Synapse database and within a Grafana panel I can e.g. use the builder to show my tables, columns etc. But when I try to execute the aforementioned query in Grafana, I get the below error:

db query error: mssql: External table 'serverlessdbtest.dbo.CAN2_gnssspeed' is not accessible because content of directory cannot be listed.

It seems the issue is that I need to add a Storage Blob Data Reader role to my user, sqladminuser - but it is unclear where I do this and how. Any hints would be appreciated.

marefr commented 4 months ago

@grafana/partner-datasources FYI

MatinF commented 4 months ago

Note that we since resolved this. I describe how in the links below:

https://canlogger.csselectronics.com/canedge-getting-started/ce3/log-file-tools/mdf4-decoders/parquet-data-lake/azure/ https://canlogger.csselectronics.com/canedge-getting-started/ce3/log-file-tools/browser-dashboard/grafana-synapse/