×
PLC Shift Reference Manual

ClickHouse

Starting version 2.10 (July 2025), tabular data generated by Data Logger v1.3 can be exported directly to ClickHouse. ClickHouse is an open-source column-oriented DBMS for online analytical processing that allows users to generate analytical reports using SQL queries in real-time.
 

Prepare the Database

Before the runtime can insert data into the database, the database must be prepared. We have provided ClickHouse specific scripts to prepare the database. To view these scripts, select View->Show SQL Folder from the top menu in PLC Shift Manager. This will open Windows Explorer to the base directory for SQL scripts. From there, open the ClickHouse folder and then the folder for the app that you want to be able to send data for.
 
View Menu, Show SQL Folder
 
 
Contents of SQL/ClickHouse/DataLogger folder
 
The one .txt files contains multiple queries. Each query creates a single table. The text from the file can be copied into a new query in the ClickHouse SQL Console.
 
Create Table Query in ClickHouse SQL Console
 
Tables must be created using the SQL Console. The PLC Shift runtime can only export data to existing tables and cannot create tables.
 

Configure the Device

In PLC Shift Manager, set the 'Enable Table Export to ClickHouse' configuration option the 'Export Settings' group at the device level to 'True'. Configure 'ClickHouse Connection String' in the same group as required.
 
Configure Device Level Settings
 
ClickHouse connection strings contain all the information that the app runtime needs to connect to the database server.  At a minimum, a connection string should contain:
 
  • The database server host name and port.
  • A user name.
  • A password.
  • The database name.
 
This information can be found under the 'Connect' section in the ClickHouse dashboard.
 
Connection Information from ClickHouse
 
An example connection string with a broken host name and the password removed is below:
Host=utq8rw.westus3.azure.clickhouse.cloud;Protocol=https;Port=8443;Username=default;Password=<password>;Database=default
 

Configure the Application

At the application level, in the 'Export Settings' group, choose the tables that you want to export. Selecting the 'Export Logged Data' exports logged data as well as output value change records.
 
Application Level Table Export Settings
 

Additional Information

In the PLC Shift runtime, for each app that has table export enabled, and for each table that is exported, we keep track of the newest record that was exported. This means that any individual record will never be exported twice.
 
In an effort to not overload the processor or the network, table export is a slow process. Records are only exported a few times per hour, and no records are exported in the first 30 minutes after the runtime is started.
 
Logged Data in ClickHouse