I’m working on project that pulls gharchive data into Azure Data Explorer (kusto) and I decided to try to do all of my setup with the Azure CLI. I could easily create my cluster and database with the CLI, but could not create the table, so I resorted to using the REST APIs. I could have used the management plane SDKs or the Azure Portal, but I didn’t want to write any code or use a UI for this particular exercise.
I found the Azure Data Explorer REST API docs here: https://docs.microsoft.com/en-us/azure/kusto/api/rest/. They do a decent job of explaining the expected format and parameters, but when it comes to which URLs to use, it can use some work. It was not a smooth experience and it took me too long. So blogging this now to hopefully help you out. I also have a PR to clear things up here: https://github.com/MicrosoftDocs/Kusto/pull/115
If you want to follow along, then you’ll need to create the cluster and database as described here: Create an Azure Data Explorer cluster and database by using Azure CLI
After the cluster and database have been created, then we’ll want to create a table and a mapping - but that’s not possible with the CLI.
.create table GithubEvent ( Id:int64, Type: string, Actor: dynamic, Repo: dynamic, Payload: dynamic, Public:bool, CreatedAt: datetime)
.create table GithubEvent ingestion json mapping "GitMapping" '[{"column":"Id","path":"$.id"},{"column":"Type","path":"$.type"},{"column":"Actor","path":"$.actor"},{"column":"Repo","path":"$.repo"},{"column":"Payload","path":"$.payload"},{"column":"Public","path":"$.public"},{"column":"CreatedAt","path":"$.created_at"}]'
BTW, the original code for this is from: https://medium.com/microsoftazure/exploring-github-events-with-azure-data-explorer-69f28eb705b9
Get Access Token
You need to pass a bearer token in the Authorization header, you can get one that is scoped to the kusto cluster by executing the following Azure CLI command:
az account get-access-token --resource https://{cluster}.{location}.kusto.windows.net --query accessToken --output tsv
Install httpie
httpie is alternative to curl: pip install httpie.
Create GitHubEvents Table and Mapping
You can use the /v1/rest/mgmt endpoint to create a table and mapping, the body payload schema is:
{
"db":"database",
"csl":"command"
}
Create Table
httpie
http POST https://{cluster}.{location}.kusto.windows.net/v1/rest/mgmt db={database} csl=".create table GithubEvent ( Id:int64, Type: string, Actor: dynamic, Repo: dynamic, Payload: dynamic, Public:bool, CreatedAt: datetime)" Authorization:"Bearer {bearer_token}"
Replace cluster, location, database, and bearer_token before running the following commands.
You’ll see that with httpie db and csl are name value pairs separated by = and the Authorization header is separated by :
Execute that command and the table will be created.
Create Mapping
httpie
http POST https://{cluster}.{location}.kusto.windows.net/v1/rest/mgmt db=gharchive csl=".create table GithubEvent ingestion json mapping \"GitMapping\" '[{\"column\":\"Id\",\"path\":\"$.id\"},{\"column\":\"Type\",\"path\":\"$.type\"},{\"column\":\"Actor\",\"path\":\"$.actor\"},{\"column\":\"Repo\",\"path\":\"$.repo\"},{\"column\":\"Payload\",\"path\":\"$.payload\"},{\"column\":\"Public\",\"path\":\"$.public\"},{\"column\":\"CreatedAt\",\"path\":\"$.created_at\"}]'" Authorization:"Bearer {bearer_token}"
Replace cluster, location, database, and bearer_token before running the following commands.
Take note that we had to escape all of the double quotes in the csl command.
Execute that command and the mapping will be created.
That’s it for now, hopefully this helps you out and hopefully they merge my PR soon so others don’t face the same issue.
Jon