Using ClickHouse HTTP interface in Azure data factory

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:

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

   Danger

   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[​](#setting-up-an-example-dataset "Direct link to 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](https://datasets-documentation.s3.eu-west-3.amazonaws.com/environmental/sensors.csv).

References

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[​](#creating-the-copy-activity-to-transfer-data-to-clickhouse "Direct link to 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**.

   Note

   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.

   References

   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](https://clickhouse.com/docs/best-practices/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[​](#additional-resources-1 "Direct link to Additional resources")

- [HTTP Interface](https://clickhouse.com/docs/interfaces/http)
- [Copy and transform data from and to a REST endpoint by using Azure Data Factory](https://learn.microsoft.com/en-us/azure/data-factory/connector-rest?tabs=data-factory)
- [Selecting an Insert Strategy](https://clickhouse.com/docs/best-practices/selecting-an-insert-strategy)
- [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)- [Creating an Azure data factory instance](#create-an-azure-data-factory-instance)- [Creating a new REST-Based linked service](#-creating-new-rest-based-linked-service)- [Creating a new dataset for the ClickHouse HTTP Interface](#creating-a-new-dataset-for-the-clickhouse-http-interface)
        - [Setting up an example dataset](#setting-up-an-example-dataset)- [Creating a Copy Activity to transfer data to ClickHouse](#creating-the-copy-activity-to-transfer-data-to-clickhouse)- [Additional resources](#additional-resources-1)

Was this page helpful?