Join operation on SQL table and Azure data explorer table via SQL Request Plugin

Question

I want to use Azure Sql db to store master data and Azure data explorer to store Telemetry data , i am using SQL request plugin to read data in Azure data explorer.
I want to perform join operations on SQL table and ADX table.

Is it recommend to use SQL request plugin to read data from SQL in Azure data explorer for production?

if not ,what else can be used?

and what are the recommendations for the achieving the above mentioned scenario

Answer 1

@siddharth bansal Welcome to Microsoft Q&A platform and thanks for using Azure Services.

You can use either the SQL external table or the plugin. The external table enables you to define it once and then reuse it just as you would any table, without any SQL needed, and you can also use managed identity authentication (which is not permitted when using the plugin). For these reasons, I believe you should only use the plugin if your SQL table is very big and you need a specific SQL query that can only be executed using the plugin.

 If by "master data" on the SQL side you mean dimensional data in a table much smaller than the ADX table, I would recommend to use ADX's "lookup" operator instead of a regular join. It is meant specifically for these scenarios, you can read about it here

It is always recommended to perform join operations between tables in the same database so the database engine can optimize the join. Joining tables from different sources may cause massive data movements from one database to the other for every query.  It is probably much more efficient to create a flow that will copy the master data from SQL to ADX once a day or more frequently if needed, Usually master data does not change so often

I hope this information helps

Regards

Geetha