Visualize data from Azure Data Explorer in Redash

Redash connects and queries your data sources, builds dashboards to visualize data and share them with peers. In this article, you learn how to set up Azure Data Explorer as a data source for Redash, and then visualize data.

Prerequisites

  1. Create cluster and database.
  2. Ingest data as explained in ingest sample data into Azure Data Explorer. For more ingestion options, see ingestion overview.

Configure the data source

You perform the following steps to configure Azure Data Explorer as a data source for your dashboard tool. We'll cover these steps in more detail in this section:

  1. Create an Azure Active Directory (Azure AD) service principal. The service principal is used by your dashboard tool to access the Azure Data Explorer service.

  2. Add the Azure AD service principal to the viewers role in the Azure Data Explorer database.

  3. Specify your dashboard tool connection properties based on information from the Azure AD service principal, then test the connection.

Create a service principal

You can create the service principal in the Azure portal or using the Azure CLI command-line experience. Regardless of which method you use, after creation you get values for four connection properties that you'll use in later steps.

Azure portal

  1. To create the service principal, follow the instructions in the Azure portal documentation.

    1. In the Assign the application to a role section, assign a role type of Reader to your Azure Data Explorer cluster.

    2. In the Get values for signing in section, copy the three property values covered in the steps: Directory ID (tenant ID), Application ID, and Password.

  2. In the Azure portal, select Subscriptions then copy the ID for the subscription in which you created the service principal.

    Subscription ID - portal

Azure CLI

  1. Create a service principal. Set an appropriate scope and a role type of reader.

    az ad sp create-for-rbac --name "https://{UrlToYourDashboard}:{PortNumber}" --role "reader" \
                             --scopes /subscriptions/{SubID}/resourceGroups/{ResourceGroupName}
    

    For more information, see Create an Azure service principal with Azure CLI.

  2. The command returns a result set like the following. Copy the three property values: appID, password, and tenant.

    {
      "appId": "XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX",
      "displayName": "{UrlToYourDashboard}:{PortNumber}",
      "name": "https://{UrlToYourDashboard}:{PortNumber}",
      "password": "XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX",
      "tenant": "XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX"
    }
    
  3. Get a list of your subscriptions.

    az account list --output table
    

    Copy the appropriate subscription ID.

    Subscription ID - CLI

Add the service principal to the viewers role

Now that you have a service principal, you add it to the viewers role in the Azure Data Explorer database. You can perform this task under Permissions in the Azure portal, or under Query by using a management command.

Azure portal - Permissions

  1. In the Azure portal, go to your Azure Data Explorer cluster.

  2. In the Overview section, select the database with the StormEvents sample data.

    Select database

  3. Select Permissions then Add.

    Database permissions

  4. Under Add database permissions, select the Viewer role then Select principals.

    Add database permissions

  5. Search for the service principal you created. Select the principal, then Select.

    Screenshot of the Azure portal New Principals pane. The Select button and two fields with indecipherable service principal properties are highlighted.

  6. Select Save.

    Screenshot of the Add Database Permissions pane in the Azure portal. The Save button is highlighted.

Management command - Query

  1. In the Azure portal, go to your Azure Data Explorer cluster, and select Query.

    Screenshot of an Azure Data Explorer cluster in the Azure portal. The Query item is highlighted.

  2. Run the following command in the query window. Use the application ID and tenant ID from the Azure portal or CLI.

    .add database {TestDatabase} viewers ('aadapp={ApplicationID};{TenantID}')
    

    The command returns a result set like the following. In this example, the first row is for an existing user in the database, and the second row is for the service principal that was just added.

    Result set

Create Azure Data Explorer Connector in Redash

  1. Sign in to Redash. Select Get Started to create an account.

  2. Under Let's get started, Select Connect a Data Source.

    Connect a data source

  3. In Create a New Data Source window, select Azure Data Explorer (Kusto), then select Create.

    Select Azure Data Explorer data source

  4. In Azure Data Explorer (Kusto) window, complete the following form and select Create.

    Azure Data Explorer (Kusto) settings window

  5. In Settings window, select Save and Test Connection to test your Azure Data Explorer (Kusto) data source connection.

Create queries in Redash

  1. On top left of Redash, select Create > Query. Click on New Query and rename the query.

    Create query

  2. Type your query in the top editing pane and select Save and Execute. Select Publish to publish query for future use.

    Save and execute query

    In the left pane, you can see the data source connection name (Github connector in our flow) in the drop-down menu, and the tables in the selected database.

  3. View the query results in the bottom central pane. Create a visualization to go with the query by selecting the New Visualization button.

    New visualization

  4. In the visualization screen, select the Visualization Type and the relevant fields such as X Column and Y Column. Save the visualization.

    Configure and save visualization

Create a query using a parameter

  1. Create > Query to create a new query. Add a parameter to it using {{}} curly brackets. Select {{}} to open Add Parameter window. You can also select the settings icon to modify the attributes of an existing parameter and open the <parameter_name> window.

    insert parameter

  2. Name your parameter. Select Type: Query Based Dropdown List from dropdown menu. Select OK

    query based dropdown list

    Note

    The query uses multiple values, therefore you must include the following syntax | where Type in ((split('{{Type}}', ','))). For more information, see in operator. This results in multiple query parameter options in redash app

Create a dashboard in Redash

  1. To create your dashboard, Create > Dashboard. Alternatively, select existing dashboard, Dashboards > select a dashboard from the list.

    Create dashboard

  2. In New Dashboard window, name your dashboard and select Save. In <Dashboard_name> window, select Add Widget to create a new widget.

  3. In Add Widget window, select query name, Choose Visualization, and Parameters. Select Add to Dashboard

    Choose visualizations and add to dashboard

  4. Select Done Editing to complete dashboard creation.

  5. In the dashboard edit mode, select Use Dashboard Level Filters to use the Type parameter previously defined.

    Complete dashboard creation

Next steps