Using ClickHouse HTTP interface in Azure data factory

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/data-ingestion/azure-data-factory/using_http_interface.md)

The [`azureBlobStorage` Table Function](https://clickhouse.com/docs/sql-reference/table-functions/azureBlobStorage)
is a fast and convenient way to ingest data from Azure Blob Storage into
ClickHouse. Using it may however not always be suitable for the following reasons:

- Your data might not be stored in Azure Blob Storage — for example, it could be in Azure SQL Database, Microsoft SQL Server, or Cosmos DB.
- Security policies might prevent external access to Blob Storage
  altogether — for example, if the storage account is locked down with no public endpoint.

In such scenarios, you can use Azure Data Factory together with the
[ClickHouse HTTP interface](https://clickhouse.com/docs/interfaces/http)
to send data from Azure services into ClickHouse.

This method reverses the flow: instead of having ClickHouse pull the data from
Azure, Azure Data Factory pushes the data to ClickHouse. This approach
typically requires your ClickHouse instance to be accessible from the public
internet.

References

It is possible to avoid exposing your ClickHouse instance to the internet by
using Azure Data Factory's Self-hosted Integration Runtime. This setup allows
data to be sent over a private network. However, it's beyond the scope of this
article. You can find more information in the official guide:
[Create and configure a self-hosted integration
runtime](https://learn.microsoft.com/en-us/azure/data-factory/create-self-hosted-integration-runtime?tabs=data-factory)

## Turning ClickHouse into a REST service[​](#turning-clickhouse-to-a-rest-service "Direct link to Turning ClickHouse into a REST service")

Azure Data Factory supports sending data to external systems over HTTP in JSON
format. We can use this capability to insert data directly into ClickHouse
using the [ClickHouse HTTP interface](https://clickhouse.com/docs/interfaces/http).
You can learn more in the [ClickHouse HTTP Interface
documentation](https://clickhouse.com/docs/interfaces/http).

For this example, we only need to specify the destination table, define the
input data format as JSON, and include options to allow more flexible timestamp
parsing.

```
INSERT INTO my_table
SETTINGS 
    date_time_input_format='best_effort',
    input_format_json_read_objects_as_strings=1
FORMAT JSONEachRow
```

To send this query as part of an HTTP request, you simply pass it as a
URL-encoded string to the query parameter in your ClickHouse endpoint:

```
https://your-clickhouse-url.com?query=INSERT%20INTO%20my_table%20SETTINGS%20date_time_input_format%3D%27best_effort%27%2C%20input_format_json_read_objects_as_strings%3D1%20FORMAT%20JSONEachRow%0A
```

References

Azure Data Factory can handle this encoding automatically using its built-in
`encodeUriComponent` function, so you don't have to do it manually.

Now you can send JSON-formatted data to this URL. The data should match the
structure of the target table. Here's a simple example using curl, assuming a
table with three columns: `col_1`, `col_2`, and `col_3`.

```
curl \
    -XPOST "https://your-clickhouse-url.com?query=<our_URL_encded_query>" \
    --data '{"col_1":9119,"col_2":50.994,"col_3":"2019-06-01 00:00:00"}'
```

You can also send a JSON array of objects, or JSON Lines (newline-delimited
JSON objects). Azure Data Factory uses the JSON array format, which works
perfectly with ClickHouse's `JSONEachRow` input.

As you can see, for this step you don't need to do anything special on the ClickHouse
side. The HTTP interface already provides everything needed to act as a
REST-like endpoint — no additional configuration required.

Now that we've made ClickHouse behave like a REST endpoint, it's time to
configure Azure Data Factory to use it.

In the next steps, we'll create an Azure Data Factory instance, set up a Linked
Service to your ClickHouse instance, define a Dataset for the
[REST sink](https://learn.microsoft.com/en-us/azure/data-factory/connector-rest),
and create a Copy Data activity to send data from Azure to ClickHouse.

## Creating an Azure data factory instance[​](#create-an-azure-data-factory-instance "Direct link to Creating an Azure data factory instance")

This guide assumes that you have access to Microsoft Azure account, and you
already have configured a subscription and a resource group. If you have
an Azure Data Factory already configured, then you can safely skip this step
and move to the next one using your existing service.

1. Log in to the [Microsoft Azure Portal](https://portal.azure.com/) and click
   **Create a resource**.
2. In the Categories pane on the left, select **Analytics**, then click on
   **Data Factory** in the list of popular services.
3. Select your subscription and resource group, enter a name for the new Data
   Factory instance, choose the region and leave the version as V2.
4. Click **Review + Create**, then click **Create** to launch the deployment.

Once the deployment completes successfully, you can start using your new Azure
Data Factory instance.

## Creating a new REST-Based linked service[​](#-creating-new-rest-based-linked-service "Direct link to Creating a new REST-Based linked service")

1. Log in to the Microsoft Azure Portal and open your Data Factory instance.
2. On the Data Factory overview page, click **Launch Studio**.
3. In the left-hand menu, select **Manage**, then go to **Linked services**,
   and click **+ New** to create a new linked service.
4. In the **New linked service search bar**, type **REST**, select **REST**, and click **Continue**
   to create [a REST connector](https://learn.microsoft.com/en-us/azure/data-factory/connector-rest)
   instance.
5. In the linked service configuration pane enter a name for your new service,
   click the **Base URL** field, then click **Add dynamic content** (this link only
   appears when the field is selected).
6. In the dynamic content pane you can create a parameterized URL, which
   allows you to define the query later when creating datasets for different
   tables — this makes the linked service reusable.
7. Click the **"+"** next to the filter input and add a new parameter, name it
   `pQuery`, set the type to String, and set the default value to `SELECT 1`.
   Click **Save**.
8. In the expression field, enter the following and click **OK**. Replace
   `your-clickhouse-url.com` with the actual address of your ClickHouse
   instance.

   ```
   @{concat('https://your-clickhouse-url.com:8443/?query=', encodeUriComponent(linkedService().pQuery))}
   ```
9. Back in the main form select Basic authentication, enter the username and
   password used to connect to your ClickHouse HTTP interface, click **Test
   connection**. If everything is configured correctly, you'll see a success
   message.
10. Click **Create** to finalize the setup.

You should now see your newly registered REST-based linked service in the list.

## Creating a new dataset for the ClickHouse HTTP Interface[​](#creating-a-new-dataset-for-the-clickhouse-http-interface "Direct link to Creating a new dataset for the ClickHouse HTTP Interface")

Now that we have a linked service configured for the ClickHouse HTTP interface,
we can create a dataset that Azure Data Factory will use to send data to
ClickHouse.

In this example, we'll insert a small portion of the [Environmental Sensors
Data](https://clickhouse.com/docs/getting-started/example-datasets/environmental-sensors).

1. Open the ClickHouse query console of your choice — this could be the
   ClickHouse Cloud web UI, the CLI client, or any other interface you use to
   run queries — and create the target table: