My first tests with QuestDB on 10 years of home automation data (1.4 billion rows) are promising.
π Fast ingestion of parquet files (~1 hour on an old Intel NUC i5)
I have stored my data in parquet files, one per month, and imported it with a simple Python script. The import on my really old Intel NUC i5 took only about one hour. I’ve never been able to do this so quickly with any other database.
π€ Btw.: I think storing data in #parquet files, or any other open table format, like Apache Iceberg, is one of the best choices to keep data. Because itβs independent of a database engine.
π Familiar SQL syntax. I experienced that QuestDB has a powerful SQL engine. I converted some Postgres SQL statements to QuestDB without big issues or changes. And I love SQL π
π Great query response times – see image. Not a representative query, but still impressive speed.
π By using ZFS with compression the used disk space can be reduced to a good value.
Do you want to log your OPCUA data to QuestDB? I have added this option to the automation-gateway.com seven days ago.
For the OpenSearch and Elasticsearch fans: the automation-gateway.com can now log OPC UA, MQTT, PLC4X values to OpenSearch!
Makes sense if you have lot of text values, to leverage the full text search capabilities. But for timeseries it is nice as well, the Kibana tool is great to create dashboards.
π₯³ Last weekend I found some time to try out an upcoming feature in WinCC Open Architecture 3.20. With the Node.js integration you can write your business logic in JavaScript with native connectivity to WinCC OA. You can take full advantage of the Node.js ecosystem.
π§ But I am a Java enthusiast and I love the JVM ecosystem. Have you ever heard about GraalVM? It is an advanced JDK written in Java. And it has a Node.js Runtime, which gives you the power of Node.js plus the power of polyglot programming, you can mix JavaScript with Java.
π And it turned out that the GraalVM Node.js Runtime also works with WinCC OA! It took me some time to figure out how the polyglot interoperability works, but now I have a first draft of a Java-Library which makes it easy to use Java and OA in the Node.js environment.
You can now add a Node. In that example we will use the dpQuery node and use “SELECT ‘_online.._value’ FROM ‘Meter_Input_WattAct.'” as query. So we just query the online value of one tag.
You have to configure the Server by clicking on the pencil button. This points to the before started Websocket Control Manager and you have to set the username and password we have added in one of the previous steps.
In this scenario we will host Grafana over the IIS from WinCC Unified. So that it comes from the same origin and that we do not come over a CORS (Cross-Origin Request Blocked) problem.
What is needed to allow Grafana to be embedded in another application is to set allow_embedding = true in the Grafana configuration file.
To host Grafana over the IIS the following settings must be made:
Add a URL Rewrite to your IIS configuration file. Change “desktop-khlb071” to your computer where Grafana is running on. Restart the Webpage with the IIS Manager.
The IIS configuration file can be found here: (C:\Program Files\Siemens\Automation\WinCCUnified\SimaticUA\web.config)
Change the following configuration of Grafana (defaults.ini). Change the domain to your computer name where Grafana is running on. It must be the same name what you use in the IIS configuration file!
# The public facing domain name used to access grafana from a browser
domain = desktop-khlb071
# Redirect to correct domain if host header does not match domain
# Prevents DNS rebinding attacks
enforce_domain = false
# The full public facing url
root_url = %(protocol)s://%(domain)s:%(http_port)s/grafana
# Serve Grafana from subpath specified in `root_url` setting. By default it is set to `false` for compatibility reasons.
serve_from_sub_path = true
# set to true if you want to allow browsers to render Grafana in a <frame>, <iframe>, <embed> or <object>. default is false.
allow_embedding = true