How can I connect Azure Data Explorer(ADX) tables from Sql Server?

Question

Hi,

I want to fetch some ADX(azure data explorer) tables data in sql server, basically creating external tables in SqlServer, which are referring from ADX.

How do I create external tables in Sql Server, which are of from ADX and use them in queries/joins?

Answer 1

You need to create an external data source and define external tables that map to the ADX tables. I used PolyBase for this purpose (Make sure that PolyBase is installed on your SQL Server instance. It comes with SQL Server 2016+)

To enable Polybase :

   EXEC sp_configure 'polybase enabled', 1;
   RECONFIGURE;

Then I defined a data source that points to the Azure Data Explorer. (You need to replace the placeholders with the actual values )

   CREATE EXTERNAL DATA SOURCE AzureDataExplorerSource
   WITH (
      LOCATION = 'azuredataexplorer://<adx-cluster-name>.region.kusto.windows.net:443',
      CREDENTIAL = <credential_name>,
      PUSHDOWN = ON,
      CONNECTION_OPTIONS = 'UseNativeQuery=1;'
   );

Now I need to tell SQL Server how to read the data:

   CREATE EXTERNAL FILE FORMAT KustoFileFormat
   WITH (
      FORMAT_TYPE = DELIMITEDTEXT,
      FORMAT_OPTIONS (FIELD_TERMINATOR = '\t', STRING_DELIMITER = '"')
   );

Then I mapped the external table to the ADX table:

 CREATE EXTERNAL TABLE dbo.MyExternalTable (
      Column1 INT,
      Column2 NVARCHAR(100),
      -- additional columns here
   )
   WITH (
      LOCATION = '<database>.<table>',
      DATA_SOURCE = AzureDataExplorerSource,
      FILE_FORMAT = KustoFileFormat
   );

Now I am able to query the external table as if it were a regular table in SQL Server:

   SELECT * FROM dbo.MyExternalTable;

And it is possible to join the external table with other tables in SQL Server:

   SELECT *
   FROM dbo.MyLocalTable AS local
   JOIN dbo.MyExternalTable AS external
   ON local.id = external.id;