Ingest data into Azure Data Explorer via Data Factory
Tags: azure, adx, adf
Published: 2023-10-17 10:43:00

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0nop9zwtecyuy7zgsntz.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".


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2ao1bj3vhow66xhd54j9.png)
2.Then I uploaded CSV which contains following data.
name,age
chibi,3
mei,3
chelsea,2


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nr9zy6a3f2hl9a97bpyf.png)
**Create Database and Table in ADX**
1.Go to ADX account from Azure Portal and click "Add database".


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6csnfjcu5s0clippl9qq.png)
2.Create database by giving any name. I call it "catlogsdb".

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dc20e5r0dopkxu5mjiwz.png)
3.Once database is created, then click "Ingest new data" which brings you to ADX portal.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8zskkz99655zp36fwd0p.png)
4.Click "Query" to display created database.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8qhw161jcfu3n7hpjckt.png)
5.Right click the database and click "Create table".
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/sm44xisqs3hvsi8rudj3.png)
6.Enter table name and select csv from local file.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/d85ldm0ln8zfljlh6tjk.png)
7.Click "Edit schema", select "Includes column names" to make first row as header, and "Create mapping". Note the mapping name.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3lz6939udv1gr2l72ecy.png)
8.Finally click create and close to complete the process.

9.Query cats table and confirm you don't have any data yet.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8z9th38dhkybxe1ndazq.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".

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lfgrpwa8huc2vrwglqyn.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".

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9u2oelv0plvgl2pwcra6.png)

6.Select the created service principal and add.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5blp38dfdcdhdfbq2cww.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".

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xsfxptow5uxsmrovmnb2.png)
**Add ADX as Linked Service**
1.Go to ADF resource from Azure Portal and click "Author & Monitor" which brings you to ADF portal.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/10zp1otg9dzt0d6t2588.png)
2.Select "Manage" icon and click "New" in Linked services.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/42uilwn9945zgsm64cmm.png)
3.Select " Azure Data Explorer" from the list.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nxq83fc3ed0oo1a1vga3.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.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/el4qp3mv1xmtd5bb8tr3.png)
2.Select "Azure Blob Storage", then "DeliminatedText".
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/g6t43ya13zouqxlt2s8l.png)
3.Select created blob linked service and specify file path. Also check "First row as header".


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6owjjgger20e2khs42m3.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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/u645swq7bg2yo1vakbub.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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/g2zza53wn7ehzr4teicd.png)
**Create pipeline**
Finally create pipeline.

1.From "+" button, select pipeline.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7q494wi7ctqmhbsazjqf.png)
2.Drag and drop "Copy data" from "Move & transform" to the pipeline campus.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/a6f9d6lk1hls2zqqkajt.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zkg5jdlciwdfvz5g8yrg.png)

4.Select "Sink" and select "adxcats" from dropdown. Enter mapping name which you obtain when you created table in ADX.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rhhesfd0xsq68lp3jwwx.png)
**Run the pipeline**
Click "Debug" menu to test the pipeline, which triggers the pipeline in debug mode.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mse6c3027xveep35xkap.png)
Once the pipeline completed, go to ADX portal and query cats table.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vxjsm2ebwuwbsxnotwl7.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.