Connect Azure Data Explorer using a dynamic selection of database from Azure Data Factory.
Azure data factory(ADF), a cloud based data integration service, allows to create data-driven workflows for data movement and data transformation. One such use case is to work with data in Azure data explorer via ADF activities.
The Azure Data Explorer Command activity in Azure Data Factory enables you to run Azure Data Explorer control commands within an ADF workflow.
Problem Statement : Execute Data explorer control command in ADF to dynamically select database in Azure Data explorer.
Create a new linked service for Azure Data explorer(Kusto) in ADF. In ADF, New Linked Service for Azure Data Explorer doesn’t process parameterized (dynamic) database name as shown below.
Then, How does it work?
While creating new linked service, select Specify dynamic contents in JSON format and paste below JSON content in Textbox.
The linked service JSON should look as follows:
{
“properties”: {
“annotations”: [],
“type”: “AzureDataExplorer”,
“typeProperties”: {
“endpoint”: “<cluster endpoint>”,
“tenant”: “<tenant>”,
“servicePrincipalId”: “<principal id>”,
“servicePrincipalKey”: “<principal key>”,
“database”: “@{linkedService().DatabaseName}”
},
“parameters”: {
“DatabaseName”: {
“type”: “String”
}
}
}
}
Replace endpoint, tenant, principal id and principal key in the JSON. Before creating linked service, click on test connection, provide database name in parameter value and validate the connection. Connection should succeed. Save this linked service configuration to use in our Data factory pipeline.
Use this linked service to run control command in ADX from ADF
Select Azure Data Explorer Command from ADF activities pane and drop it in the pipeline editor window. Select newly created linked service and add control command to run in command section.
Sample command -> .show tables | Take 5
Oh no, there is no option to provide database name dynamically for this activity from UI. What to do next ??
How will this work ?
To select database name dynamically, update pipeline JSON to pass dynamic value to linked service manually. Edit pipeline JSON. In the pipeline definition under linked service settings, add parameters object as shown below:
“linkedServiceName”: {
“referenceName”: “<name>”,
“type”: “LinkedServiceReference”,
“parameters”: {
“DatabaseName”: “<database name>”
}
}
Click on OK to save these changes.
Run the pipeline in debug mode to validate changes. Azure data explorer control command should succeed.
Same configuration can work while pushing data to Azure Data explorer via Copy Data activity, where dataset connection needs to be updated to provide dynamic database name. Stay tuned for my next blog for more explanation.
Next Steps?
Congratulations! you can dynamically select database name to connect to ADX. Please go ahead and implement your workflows.
Feel free to ask me questions, if any.