Ingest data into Azure Data Explorer via Data Factory
Tags: azure, adx, adf
Published: 2020-09-17 07:01:04

Azure Data Explorer (ADX) is a great service to analyze log types of data. And you need data to play with it. There are many ways to ingest data into ADX, and I explain how to ingest data from blob storage by using Azure Data Factory (ADF).

# Prerequisites

I created following Azure resources.

- Azure Data Explorer
- Azure Data Factory
- Azure Storage Account

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/wzl13hjb1nwlw6id4pja.png)

# Upload log to storage

I uploaded csv as data source to blob storage.

1\. Go to Storage Account and select "Containers". Then add new container. I named it as "catlogs".

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/4borsyx59q2xhupy2aea.png)

2\. Then I uploaded CSV which contains following data.

```csv
name,age
chibi,3
mei,3
chelsea,2
```

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/7mllpv97y1niikl2hvka.png)

# Create Database and Table in ADX

1\. Go to ADX account from Azure Portal and click "Add database".

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/uhdqhmdlh9tbc7xgmhq0.png)

2\. Create database by giving any name. I call it "catlogsdb".

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/a711x7mios6vegzl3gkc.png)

3\. Once database is created, then click "Ingest new data" which brings you to ADX portal.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/q1zlcdb98iwk4n0q5xsu.png)

4\. Click "Query" to display created database.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/94l3khrn1s2lacp4t9za.png)

5\. Right click the database and click "Create table".

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/diymluf6n9w8oxfs6xt5.png)

6\. Enter table name and select csv from local file.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/zitic3jdisu0v7f604op.png)

7\. Click "Edit schema", select "Includes column names" to make first row as header, and "Create mapping". Note the mapping name.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/ptbtp4dieumjopcq358l.png)

8\. Finally click create and close to complete the process.

9\. Query cats table and confirm you don't have any data yet.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/wl1boll6n0s7hciwilki.png)

# Create pipeline to ingest data

Finally create pipeline in ADF.

## Add service principal

To let ADF access to ADX, I need to create service principal. You can give permission at two levels.

- ADX resource level
- ADX database level

I give permission on resource level this time.

1\. Go to Azure Portal | Azure Active Directory | App registrations and click "New registration".

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/8qm6fyoezj7o4gzqpiso.png)

2\. Enter service principal name and click "Register". I named it as "mycatadx-sp".

3\. Note "Application ID".

4\. Select "Certificates & secrets" and generate new key.  Note the key.

5\. Then go back to Azure Portal and select ADX resource. Go to "Permissions". Click "Add".

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/sjh7qiid13ojdn26sq59.png)

6\. Select the created service principal and add.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/hmb2o13rhupqezz0lwqr.png)

If you want to give permissions on database level, rather than go to "Permissions" on resource, go to databases and select a database, then select "Permissions".

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/ven4fcr42f9me73afqsz.png)

## Add ADX as Linked Service

1\. Go to ADF resource from Azure Portal and click "Author & Monitor" which brings you to ADF portal.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/1s2awe7f9ne5c67ac2dc.png)

2\. Select "Manage" icon and click "New" in Linked services.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/7onc648vfeu7eczom3kr.png)

3\. Select " Azure Data Explorer" from the list.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/r7bs07x9x4mhuw150ykv.png)

4\. Select ADX resource from Azure Subscription, and enter service principal ID/key which you created.

5\. Enter database name (catlogsdb) and click "Create".

## Add Blob storage as Linked Service

By using similar step, add "Azure Blob storage" as linked service. 

## Add Dataset

After created linked service, I add dataset to define data model.

1\. Go to "Author" menu, and click "+" to add datasets.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/jyus4jhbq82dyi6zp0ry.png)

2\. Select "Azure Blob Storage", then "DeliminatedText".

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/jdw3om62repvzje09c83.png)

3\. Select created blob linked service and specify file path. Also check "First row as header".

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/z9b61hn2a6binddqvv7j.png)

4\. Once dataset it created, make sure to add "cats.csv" as file name path and click "Preview for File to confirm you can see the csv file.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/owesjwsyejjm8dujk8f5.png)

5\. Add another dataset. Select ADX and select created linked service. Database is auto resolved. Select table. I also renamed the dataset as "adxcats" to distinguish from blob cats dataset.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/loxa8lgoqgsska5ksdeq.png)

## Create pipeline

Finally create pipeline.

1\. From "+" button, select pipeline.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/z8eekbmj3f1qnykkiip9.png)

2\. Drag and drop "Copy data" from "Move & transform" to the pipeline campus.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/v8jqat8x1qhfw11mvyjt.png)

3\. Select "Source" property and select "cats" dataset which you just created. Click "Preview data" to see the data.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/tkw2h0p92i45dchrmwn5.png)

4\. Select "Sink" and select "adxcats" from dropdown. Enter mapping name which you obtain when you created table in ADX.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/pub6oom7tpn6oxdv0za6.png)

## Run the pipeline

Click "Debug" menu to test the pipeline, which triggers the pipeline in debug mode.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/gpznc5x6vgs92lqzn0dm.png)

Once the pipeline completed, go to ADX portal and query cats table.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/14kxj2gdg33m38hevayi.png)

# Summary

Even though there are multiple steps to provision resources, settings and permissions, everything can be done via GUI.

If you want to automate the process with flexibility, please consider using ADF.

In the [next article](https://dev.to/kenakamu/ingest-data-into-azure-data-explorer-via-data-factory-4p8), I will explain how to consume the data from Databricks.