{"id":817,"date":"2017-09-04T23:36:37","date_gmt":"2017-09-04T21:36:37","guid":{"rendered":"http:\/\/www.rocworks.at\/wordpress\/?p=817"},"modified":"2017-09-04T23:48:27","modified_gmt":"2017-09-04T21:48:27","slug":"streaming-sql-for-apache-kafka-wincc-oa-with-docker","status":"publish","type":"post","link":"https:\/\/www.rocworks.at\/wordpress\/?p=817","title":{"rendered":"Streaming SQL for Apache Kafka &#038; WinCC OA with Docker&#8230;"},"content":{"rendered":"<p>KSQL makes it easy to read, write, and process streaming data in real-time, at scale, using SQL-like semantics. It offers an easy way to express stream processing transformations as an alternative to writing an application in a programming language such as Java or Python.\u00a0<a href=\"https:\/\/www.confluent.io\/product\/ksql\/\">https:\/\/www.confluent.io\/product\/ksql\/<\/a><\/p>\n<p><iframe loading=\"lazy\" title=\"Developer Preview: KSQL from Confluent\" width=\"474\" height=\"267\" src=\"https:\/\/www.youtube.com\/embed\/A45uRzJiv7I?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe><\/p>\n<p>With WinCC OA Java (<a href=\"https:\/\/github.com\/vogler75\/oa4j\">https:\/\/github.com\/vogler75\/oa4j<\/a>) we can stream data from WinCC OA to Apache Kafka, use KSQL to produce some insights and send it back to WinCC OA by using a WinCC OA Driver written in Java connected to Kafka.<\/p>\n<p>Attached you will find a docker-compose.yml to setup KSQL + WinCC OA Connector and Driver to test it. Just use &#8220;docker-compose up -d&#8221; to start up everything. Before you should set the &#8220;data&#8221; and &#8220;event&#8221; environment variables in the docker-compose.yml to point to a running WinCC OA project.<\/p>\n<pre>root@docker1:~\/docker\/builds\/winccoa# docker-compose up -d\r\n\r\nCreating winccoa_frontend_1 ==&gt; collect data from OA and publish it by ZeroMQ\r\n\r\nCreating winccoa_backend-kafka_1 ==&gt; get the data from the Frontend and write it to Kafka\r\n\r\nCreating winccoa_driver-kafka_1 ==&gt; OA driver to read data from kafka.\r\n\r\nCreating winccoa_zookeeper_1\r\nCreating winccoa_kafka_1\r\nCreating winccoa_schema-registry_1\r\nCreating winccoa_ksql-cli_1<\/pre>\n<p>We use Docker to startup WinCCOA Mangers (frontend, backend) and Drivers.<\/p>\n<p>Afterwards you can start KSQL:\u00a0<span class=\"s1\">docker-compose exec ksql-cli ksql-cli local &#8211;bootstrap-server kafka:29092<\/span><\/p>\n<p>Create a stream of the topic which is sent from WinCC OA to kafka (currently every change of value in WinCC OA is sent to Kafka):<\/p>\n<p class=\"p1\"><span class=\"s1\">CREATE STREAM Scada_FloatVar (TimeMS BIGINT, Status BIGINT, Value DOUBLE) WITH (kafka_topic=&#8217;Scada_FloatVar&#8217;, value_format=&#8217;JSON&#8217;);<\/span><\/p>\n<p>Create a result table in KSQL which will be read by the WinCC OA Driver, here we detect if a datapoint changes more often than 5 times in 10 seconds. Just a simple example to show how KSQL can be used:<\/p>\n<p class=\"p1\"><span class=\"s1\">CREATE TABLE result WITH (PARTITIONS=1) AS SELECT rowkey AS &#8220;Name&#8221;, count(*) AS &#8220;Value&#8221; FROM Scada_FloatVar WINDOW TUMBLING (size 10 second) GROUP BY rowkey HAVING count(*) &gt; 5;<\/span><\/p>\n<p>In WinCC OA you should put a peripheral address on a datapoint with the example driver (num 4) to get the result back (you will need the panels and scripts from here\u00a0<a href=\"https:\/\/github.com\/vogler75\/oa4j\/tree\/master\/Project\/Drivers\/Kafka\">https:\/\/github.com\/vogler75\/oa4j\/tree\/master\/Project\/Drivers\/Kafka<\/a> to use the driver).<\/p>\n<p><a href=\"http:\/\/www.rocworks.at\/wordpress\/?attachment_id=822\" rel=\"attachment wp-att-822\">docker-compose.yml<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>KSQL makes it easy to read, write, and process streaming data in real-time, at scale, using SQL-like semantics. It offers an easy way to express stream processing transformations as an alternative to writing an application in a programming language such as Java or Python.\u00a0https:\/\/www.confluent.io\/product\/ksql\/ With WinCC OA Java (https:\/\/github.com\/vogler75\/oa4j) we can stream data from WinCC &hellip; <a href=\"https:\/\/www.rocworks.at\/wordpress\/?p=817\" class=\"more-link\">Continue reading <span class=\"screen-reader-text\">Streaming SQL for Apache Kafka &#038; WinCC OA with Docker&#8230;<\/span> <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[29,3],"tags":[],"class_list":["post-817","post","type-post","status-publish","format-standard","hentry","category-bigdatanosql","category-wincc-oa"],"_links":{"self":[{"href":"https:\/\/www.rocworks.at\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/817","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rocworks.at\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.rocworks.at\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.rocworks.at\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rocworks.at\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=817"}],"version-history":[{"count":5,"href":"https:\/\/www.rocworks.at\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/817\/revisions"}],"predecessor-version":[{"id":823,"href":"https:\/\/www.rocworks.at\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/817\/revisions\/823"}],"wp:attachment":[{"href":"https:\/\/www.rocworks.at\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=817"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rocworks.at\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=817"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rocworks.at\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=817"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}