DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Last call! Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • Data Ingestion Into Azure Data Explorer Using Kafka Connect
  • Tutorial: Data Ingestion From Kafka to Azure Data Explorer
  • Implement a Distributed Database to Your Java Application
  • Using PostgreSQL pgoutput Plugin for Change Data Capture With Debezium

Trending

  • Scalable System Design: Core Concepts for Building Reliable Software
  • Unlocking AI Coding Assistants Part 3: Generating Diagrams, Open API Specs, And Test Data
  • Accelerating AI Inference With TensorRT
  • Scalable, Resilient Data Orchestration: The Power of Intelligent Systems
  1. DZone
  2. Software Design and Architecture
  3. Cloud Architecture
  4. Getting Started With Azure Data Explorer Using the Go SDK

Getting Started With Azure Data Explorer Using the Go SDK

Hands-on tutorial on how to interact with Azure Data Explorer using Go.

By 
Abhishek Gupta user avatar
Abhishek Gupta
DZone Core CORE ·
Jul. 28, 20 · Tutorial
Likes (4)
Comment
Save
Tweet
Share
5.3K Views

Join the DZone community and get the full member experience.

Join For Free

With the help of an example, this blog post will walk you through how to use the Azure Data explorer Go SDK to ingest data from an Azure Blob storage container and query it programmatically using the SDK. After a quick overview of how to setup Azure Data Explorer cluster (and a database), we will explore the code to understand what's going on (and how) and finally test the application using a simple CLI interface

The sample data is a CSV file that can be downloaded from here.

The code is available on GitHub https://github.com/abhirockzz/azure-dataexplorer-go

What Is Azure Data Explorer?

Azure Data Explorer (also known as Kusto) is a fast and scalable data exploration service for analyzing large volumes of diverse data from any data source, such as websites, applications, IoT devices, and more. This data can then be used for diagnostics, monitoring, reporting, machine learning, and additional analytics capabilities.

It supports several ingestion methods, including connectors to common services like Event Hub, programmatic ingestion using SDKs, such as .NET and Python, and direct access to the engine for exploration purposes. It also integrates with analytics and modeling services for additional analysis and visualization of data using tools such as Power BI

Go SDK for Azure Data Explorer

The Go client SDK allows you to query, control and ingest into Azure Data Explorer clusters using Go. Please note that this is for interacting with the Azure Data Explorer cluster (and related components such as tables etc.). To create Azure Data Explorer clusters, databases etc. you should the use the admin component (control plane) SDK which is a part of the larger Azure SDK for Go

API docs - https://godoc.org/github.com/Azure/azure-kusto-go

Before getting started, here is what you would need to try out the sample application

Prerequisites

Install Go 1.13 or above

You will need a Microsoft Azure account. Go ahead and sign up for a free one!

Install the Azure CLI if you don't have it already (should be quick!)

Setup Azure Data Explorer Cluster, Create a Database, and Configure Security

Start by creating a cluster using az kusto cluster create. Once that's done, create a database with az kusto database create, e.g.

Java
 




x


 
1
az kusto cluster create -l "Central US" -n MyADXCluster -g MyADXResGrp --sku Standard_D11_v2 --capacity 2
2

          
3
az kusto database create --cluster-name MyADXCluster -g MyADXResGrp -n MyADXdb
4

          
5
az kusto database show --cluster-name MyADXCluster --name MyADXdb --resource-group MyADXResGrp



Create a Service Principal using az ad sp create-for-rbac

Java
 




xxxxxxxxxx
1


 
1
az ad sp create-for-rbac -n "test-datax-sp"



You will get a JSON response as such - please note down the appId, password and tenant as you will be using them in subsequent steps

Java
 




xxxxxxxxxx
1


 
1
{
2
  "appId": "fe7280c7-5705-4789-b17f-71a472340429",
3
  "displayName": "test-datax-sp",
4
  "name": "http://test-datax-sp",
5
  "password": "29c719dd-f2b3-46de-b71c-4004fb6116ee",
6
  "tenant": "42f988bf-86f1-42af-91ab-2d7cd011db42"
7
}



You will need to assign roles to the Service Principal so that it can access the database you just created. To do so using the Azure Portal, open the Azure Data Explorer cluster, navigate to Data > Databases and select the database. Choose Permissions form the left menu and and click Add to proceed.

For more information, please refer to Secure Azure Data Explorer clusters in Azure

Code Walkthrough

At a high level, this is what the sample code does:

  • Connect to an Azure Data Explorer cluster (of course!)
  • Create a table (and list them just to be sure)
  • Create data mapping
  • Ingest/load existing data from a CSV file in Azure Blob storage
  • Run a query on the data you just ingested

Let's look at each of these steps

Connect to an Azure Data Explorer Cluster

We use Service Principal to authenticate to Azure Data Explorer and provide the Azure tenant ID, client ID and client secret (which were obtained after creating the principal using az ad sp create-for-rbac)

Java
 




xxxxxxxxxx
1


 
1
auth := kusto.Authorization{Config: auth.NewClientCredentialsConfig(clientID, clientSecret, tenantID)}
2
kc, err := kusto.New(kustoEndpoint, auth)
3
if err != nil {
4
    log.Fatal("failed to create kusto client", err)
5
}


You can check out the code here

Create Table and Data Mappings

To create a table, we simply execute create table

Java
 




xxxxxxxxxx
1


 
1
func CreateTable(kc *kusto.Client, kustoDB string) {
2
    _, err := kc.Mgmt(context.Background(), kustoDB, kusto.NewStmt(createTableCommand))
3
    if err != nil {
4
        log.Fatal("failed to create table", err)
5
    }
6

          
7
    log.Printf("table %s created\n", kustoTable)
8
}


You can check out the code here

Notice how we use client.Mgmt to execute this operation since this is a management query. Later, you will see how to execute query to read data from Azure Data Explorer.

To confirm, we run a query to check the tables in database i.e. show tables

Java
 




xxxxxxxxxx
1
26


 
1
func FindTable(kc *kusto.Client, kustoDB string) []TableInfo {
2
    var tables []TableInfo
3
    ri, err := kc.Mgmt(context.Background(), kustoDB, kusto.NewStmt(testQuery))
4
    if err != nil {
5
        log.Fatalf("failed to execute query %s - %s", testQuery, err)
6
    }
7
    var t TableInfo
8
    for {
9
        row, err := ri.Next()
10
        if err != nil {
11
            if err == io.EOF {
12
                break
13
            } else {
14
                log.Println("error", err)
15
            }
16
        }
17
        row.ToStruct(&t)
18
        tables = append(tables, t)
19
    }
20
    return tables
21
}
22
...
23
type TableInfo struct {
24
    Name string `kusto:"TableName"`
25
    DB   string `kusto:"DatabaseName"`
26
}


You can check out the code here

After executing the query, ToStruct is used to save the result to an instance of a user-defined TableInfo struct

Once the table is created, we can configure data mappings that are used during ingestion to map incoming data to columns inside Kusto tables

Java
 




xxxxxxxxxx
1


 
1
func CreateMapping(kc *kusto.Client, kustoDB string) {
2
    _, err := kc.Mgmt(context.Background(), kustoDB, kusto.NewStmt(createMappingCommand))
3
    if err != nil {
4
        log.Fatal("failed to create mapping", err)
5
    }
6
    log.Printf("mapping %s created\n", kustoMappingRefName)
7
}


You can check out the code here

Ingest Data From Azure Blob Storage

To ingest data we use the Ingestion client

Java
 




xxxxxxxxxx
1
15


 
1
const blobStorePathFormat = "https://%s.blob.core.windows.net/%s/%s%s"
2

          
3
func CSVFromBlob(kc *kusto.Client, blobStoreAccountName, blobStoreContainer, blobStoreToken, blobStoreFileName, kustoMappingRefName, kustoDB, kustoTable string) {
4
    kIngest, err := ingest.New(kc, kustoDB, kustoTable)
5
    if err != nil {
6
        log.Fatal("failed to create ingestion client", err)
7
    }
8
    blobStorePath := fmt.Sprintf(blobStorePathFormat, blobStoreAccountName, blobStoreContainer, blobStoreFileName, blobStoreToken)
9
    err = kIngest.FromFile(context.Background(), blobStorePath, ingest.FileFormat(ingest.CSV), ingest.IngestionMappingRef(kustoMappingRefName, ingest.CSV))
10

          
11
    if err != nil {
12
        log.Fatal("failed to ingest file", err)
13
    }
14
    log.Println("Ingested file from -", blobStorePath)
15
}


You can check out the code here

We have the path to the file in Azure Blob storage and we refer to it in FromFile function along with file type (CSV in this case) as well as data mapping we just created (StormEvents_CSV_Mapping)

Query Data

We fetch some data from the StormEvents table using the following query:

Java
 




xxxxxxxxxx
1


 
1
StormEvents | where EventType == 'Flood' and State == 'WASHINGTON' | sort by DamageProperty desc | project StartTime, EndTime, Source, DamageProperty



This time, we use client.Query (not Mgmt) to read data from the table.

Java
 




xxxxxxxxxx
1
28


 
1
func Get(kc *kusto.Client, kustoDB string) []StormDetails {
2
    var events []StormDetail
3
    ri, err := kc.Query(context.Background(), kustoDB, kusto.NewStmt(query))
4

          
5
    if err != nil {
6
        log.Fatalf("failed to execute query %s - %s", query, err)
7
    }
8
    for {
9
        row, err := ri.Next()
10
        if err != nil {
11
            if err == io.EOF {
12
                break
13
            } else {
14
                log.Println("error", err)
15
            }
16
        }
17
        var event StormDetail
18
        row.ToStruct(&event)
19
        events = append(events, event)
20
    }
21
    return events
22
...
23
type StormDetail struct {
24
    Start  time.Time `kusto:"StartTime"`
25
    End    time.Time `kusto:"EndTime"`
26
    From   string    `kusto:"Source"`
27
    Damage int32     `kusto:"DamageProperty"`
28
}



Each row in the result in converted into a StormDetail struct using ToStruct

You can check out the code here

The list of StormDetails is then displayed to stdout in the form a user-friendly tabular format

Java
 




xxxxxxxxxx
1
14


 
1
...
2
data := [][]string{}
3
for _, detail := range details {
4
    data = append(data, []string{detail.Start.String(), detail.End.String(), detail.From, strconv.Itoa(int(detail.Damage))})
5
}
6
log.Println("StormEvents data....")
7
table := tablewriter.NewWriter(os.Stdout)
8
table.SetHeader([]string{"Start Time", "End Time", "From", "Damage"})
9

          
10
for _, v := range data {
11
    table.Append(v)
12
}
13
table.Render()
14
...


You can check out the code here

Finally, to drop the table, we use .drop table StormEvents

Java
 




xxxxxxxxxx
1


 
1
const dropTableQ = ".drop table StormEvents"
2

          
3
func dropTable(kc *kusto.Client) {
4
    _, err := kc.Mgmt(context.Background(), kustoDB, kusto.NewStmt(dropTableQ))
5
    if err != nil {
6
        log.Fatal("failed to drop table - ", err)
7
    }
8
}


You can check out the code here

Run the Example

Now that you have an idea of what's going on, let's try it out using a CLI

Set the required environment variables

Java
 




xxxxxxxxxx
1


 
1
export AZURE_SP_CLIENT_ID="service principal client id"
2
export AZURE_SP_CLIENT_SECRET="<service principal client secret>"
3
export AZURE_SP_TENANT_ID="<tenant ID>"
4
#e.g. https://mykusto.southeastasia.kusto.windows.net
5
export KUSTO_ENDPOINT="https://<cluster name>.<azure region>.kusto.windows.net"



Get the code and build it

Java
 




xxxxxxxxxx
1
26


 
1
git clone https://github.com/abhirockzz/azure-dataexplorer-go
2
cd azure-dataexplorer-go
3
go build -o azdatax
4

          
5
//to confirm
6
chmod a+x azdatax && ./azdatax
7

          
8
//output
9
CLI to test sample program for Azure Data Explorer
10

          
11
Usage:
12
  azdatax [command]
13

          
14
Available Commands:
15
  create-mapping creates a mapping named StormEvents_CSV_Mapping
16
  create-table   creates a table named StormEvents
17
  drop-table     drops the StormEvents table
18
  get            queries data from StormEvents
19
  help           Help about any command
20
  ingest         ingests CSV file from blob store
21
  list-tables    lists tables
22

          
23
Flags:
24
  -h, --help   help for azdatax
25

          
26
Use "azdatax [command] --help" for more information about a command.



Let's start by creating a table:

Java
 




xxxxxxxxxx
1


 
1
./azdatax create-table --dbname <name of the database you created initially>
2

          
3
//output
4
Connected to Azure Data Explorer
5
table StormEvents created



To list tables:

Java
 




xxxxxxxxxx
1


 
1
./azdatax list-tables --dbname <name of the database you created initially>
2

          
3
//output
4
Connected to Azure Data Explorer
5
Table name: StormEvents, Database name: testkustodb



To create the data mapping

Java
 




xxxxxxxxxx
1


 
1
./azdatax create-mapping --dbname <name of the database you created initially>
2

          
3
//output
4
Connected to Azure Data Explorer
5
mapping StormEvents_CSV_Mapping created



To ingest data:

Java
 




xxxxxxxxxx
1


 
1
./azdatax ingest --dbname <name of the database you created initially>
2

          
3
//output
4
Connected to Azure Data Explorer
5
Ingested file from - https://kustosamplefiles.blob.core.windows.net/samplefiles/StormEvents.csv?st=2018-08-31T22%3A02%3A25Z&se=2020-09-01T22%3A02%3A00Z&sp=r&sv=2018-03-28&sr=b&sig=LQIbomcKI8Ooz425hWtjeq6d61uEaq21UVX7YrM61N4%3D



Wait for a while for the ingestion to complete before you try to query the data (next step)

To query data

Java
 




xxxxxxxxxx
1
15


 
1
./azdatax get --dbname <name of the database you created initially>
2

          
3
//output
4
Connected to Azure Data Explorer
5
StormEvents data....
6
+-------------------------------+-------------------------------+---------------------------+----------+
7
|          START TIME           |           END TIME            |           FROM            |  DAMAGE  |
8
+-------------------------------+-------------------------------+---------------------------+----------+
9
| 2007-12-02 23:00:00 +0000 UTC | 2007-12-05 23:00:00 +0000 UTC | Official NWS Observations | 50000000 |
10
| 2007-01-03 00:00:00 +0000 UTC | 2007-01-03 22:00:00 +0000 UTC | Newspaper                 |    20000 |
11
| 2007-12-03 03:00:00 +0000 UTC | 2007-12-05 19:00:00 +0000 UTC | Official NWS Observations |    12000 |
12
| 2007-01-03 00:00:00 +0000 UTC | 2007-01-03 22:00:00 +0000 UTC | Newspaper                 |     5000 |
13
| 2007-03-12 00:00:00 +0000 UTC | 2007-03-13 23:00:00 +0000 UTC | Public                    |        0 |
14
| 2007-03-12 00:00:00 +0000 UTC | 2007-03-14 23:00:00 +0000 UTC | Other Federal             |        0 |
15
+-------------------------------+-------------------------------+---------------------------+----------+



Finally, to drop the StormEvents table:

Java
 




xxxxxxxxxx
1


 
1
./azdatax drop-table --dbname <name of the database you created initially>
2

          
3
//output
4
Connected to Azure Data Explorer
5
Table StormEvents dropped



Conclusion

Hopefully this was helpful in demonstrating how to use the Go SDK to interact with Azure Data Explorer. This obviously just one of the ways! For more, dig into the documentation and happy exploring!

azure Azure Data Explorer Data (computing) Software development kit Database Java (programming language) cluster Data mapping

Opinions expressed by DZone contributors are their own.

Related

  • Data Ingestion Into Azure Data Explorer Using Kafka Connect
  • Tutorial: Data Ingestion From Kafka to Azure Data Explorer
  • Implement a Distributed Database to Your Java Application
  • Using PostgreSQL pgoutput Plugin for Change Data Capture With Debezium

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!