Using ClickHouse HTTP interface to bring Azure data into ClickHouse

Using ClickHouse’s HTTP interface to bring data from Azure Data Factory into ClickHouse

Using ClickHouse HTTP interface in Azure data factory

The azureBlobStorage Table Function 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 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.

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

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. You can learn more in the ClickHouse HTTP Interface documentation.

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

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, and create a Copy Data activity to send data from Azure to ClickHouse.

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 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

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 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

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.

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:

   CREATE TABLE sensors
   (
       sensor_id UInt16,
       lat Float32,
       lon Float32,
       timestamp DateTime,
       temperature Float32
   )
   ENGINE = MergeTree
   ORDER BY (timestamp, sensor_id);
   

2. In Azure Data Factory Studio, select Author in the left-hand pane. Hover over the Dataset item, click the three-dot icon, and choose New dataset.

3. In the search bar, type REST, select REST, and click Continue. Enter a name for your dataset and select the linked service you created in the previous step. Click OK to create the dataset.

4. You should now see your newly created dataset listed under the Datasets section in the Factory Resources pane on the left. Select the dataset to open its properties. You’ll see the pQuery parameter that was defined in the linked service. Click the Value text field. Then click Add dynamic content.

5. In the pane that opens, paste the following query:

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

All single quotes ' in the query must be replaced with two single quotes ''. This is required by Azure Data Factory’s expression parser. If you don’t escape them, you may not see an error immediately — but it will fail later when you try to use or save the dataset. For example, 'best_effort' must be written as ''best_effort''.

6. Click OK to save the expression. Click Test connection. If everything is configured correctly, you’ll see a Connection successful message. Click Publish all at the top of the page to save your changes.

Setting up an example dataset

In this example, we won’t use the full Environmental Sensors Dataset, but just a small subset available at the Sensors Dataset Sample.

To keep this guide focused, we won’t go into the exact steps for creating the source dataset in Azure Data Factory. You can upload the sample data to any storage service of your choice — for example, Azure Blob Storage, Microsoft SQL Server, or even a different file format supported by Azure Data Factory.

Upload the dataset to your Azure Blob Storage (or another preferred storage service), Then, in Azure Data Factory Studio, go to the Factory Resources pane. Create a new dataset that points to the uploaded data. Click Publish all to save your changes.

Creating a Copy Activity to transfer data to ClickHouse

Now that we’ve configured both the input and output datasets, we can set up a Copy Data activity to transfer data from our example dataset into the sensors table in ClickHouse.

1. Open Azure Data Factory Studio, go to the Author tab. In the Factory Resources pane, hover over Pipeline, click the three-dot icon, and select New pipeline.

2. In the Activities pane, expand the Move and transform section and drag the Copy data activity onto the canvas.

3. Select the Source tab, and choose the source dataset you created earlier.

4. Go to the Sink tab and select the ClickHouse dataset created for your sensors table. Set Request method to POST. Ensure HTTP compression type is set to None.

HTTP compression doesn’t work correctly in Azure Data Factory’s Copy Data activity. When enabled, Azure sends a payload consisting of zero bytes only — likely a bug in the service. Be sure to leave compression disabled.

We recommend keeping the default batch size of 10,000, or even increasing it further. For more details, see Selecting an Insert Strategy / Batch inserts if synchronous for more details.

5. Click Debug at the top of the canvas to run the pipeline. After a short wait, the activity will be queued and executed. If everything is configured correctly, the task should finish with a Success status.

6. Once complete, click Publish all to save your pipeline and dataset changes.

Additional resources

• HTTP Interface
• Copy and transform data from and to a REST endpoint by using Azure Data Factory
• Selecting an Insert Strategy
• Create and configure a self-hosted integration runtime