Flink SQL Preview
Tags: apachekafka, apacheflink, apachenifi, flank
Published: 2020-05-04 21:18:46

---
title: Flink SQL Preview
published: true
date: 2020-05-04 21:11:00 UTC
tags: apache kafka,apache-flink,apache-nifi,FLANK
canonical_url: 
---

## FLaNK:  Flink SQL Preview

  

  

[![](https://1.bp.blogspot.com/-RGI01e5uFvY/XrCDi-tPGJI/AAAAAAAAZ9w/Tslu0S0fC0ACH-Xu62drPty-ePSI8qErACLcBGAsYHQ/s640/flinksqlquerysensors.png)](https://1.bp.blogspot.com/-RGI01e5uFvY/XrCDi-tPGJI/AAAAAAAAZ9w/Tslu0S0fC0ACH-Xu62drPty-ePSI8qErACLcBGAsYHQ/s1600/flinksqlquerysensors.png)

  

  

  

From our Web Flink Dashboard, we can see how our insert is doing and view the joins and records passing quickly through our tiny cluster.

  

[![](https://1.bp.blogspot.com/-EMOct1mmQoE/XrCDiCfKjuI/AAAAAAAAZ9o/_FYB2xmmGU0vW4Dz78qFgQtVtJfrCy2SQCLcBGAsYHQ/s640/flinkinsert.png)](https://1.bp.blogspot.com/-EMOct1mmQoE/XrCDiCfKjuI/AAAAAAAAZ9o/_FYB2xmmGU0vW4Dz78qFgQtVtJfrCy2SQCLcBGAsYHQ/s1600/flinkinsert.png)

  

  

  

[![](https://1.bp.blogspot.com/-45YamgYhlmg/XrCDiuVjbAI/AAAAAAAAZ9s/L1jhscluCxMgc2PaTYGrnGoEfnYQpxxgwCLcBGAsYHQ/s640/flinkjoin.png)](https://1.bp.blogspot.com/-45YamgYhlmg/XrCDiuVjbAI/AAAAAAAAZ9s/L1jhscluCxMgc2PaTYGrnGoEfnYQpxxgwCLcBGAsYHQ/s1600/flinkjoin.png)
  

  

  

  

  

See:   [https://github.com/tspannhw/meetup-sensors/blob/master/flink-sql/test2.ddl](https://github.com/tspannhw/meetup-sensors/blob/master/flink-sql/test2.ddl)

  

As part of the May 7th, 2020 Virtual [Meetup](https://www.meetup.com/futureofdata-princeton/events/268830725/), I was doing some work with Flink SQL to show for a quick demo as the introduction to the meetup and I found out how easy it was to do some cool stuff.   This was inspired by my Streaming Hero, Abdelkrim, who wrote this amazing article on Flink SQL use cases:   [https://towardsdatascience.com/event-driven-supply-chain-for-crisis-with-flinksql-be80cb3ad4f9](https://towardsdatascience.com/event-driven-supply-chain-for-crisis-with-flinksql-be80cb3ad4f9)

  

As part of our time series meetup, I have a few streams of data coming from one device from a MiNiFi Java agent to NiFi for some transformation, routing and processing and then sent to Apache Flink for final processing.   I decided to join Kafka topics with Flink SQL.   

  

The sensor data:   [https://www.datainmotion.dev/2020/04/predicting-sensor-readings-with-time.html](https://dev.to/tspannhw/predicting-sensor-readings-with-time-series-machine-learning-3ngc)

  

Let's create Flink Tables:

  

This table will be used to insert the joined events from both source Kafka topics.

  

CREATE TABLE **global\_sensor\_events** (

 uuid STRING, 

 systemtime STRING ,  

temperaturef STRING , 

pressure DOUBLE, 

humidity DOUBLE, 

lux DOUBLE, 

proximity int, 

oxidising DOUBLE , 

reducing DOUBLE, 

nh3 DOUBLE , 

gasko STRING,

`current` INT, 

voltage INT ,

`power` INT,

`total` INT,

fanstatus STRING

) WITH (

 'connector.type'    = 'kafka',

 'connector.version' = 'universal',

 'connector.topic'    = 'global\_sensor\_events',

 'connector.startup-mode' = 'earliest-offset',

 'connector.properties.bootstrap.servers' = 'tspann-princeton0-cluster-0.general.fuse.l42.cloudera.com:9092',

 'connector.properties.group.id' = 'flink-sql-global-sensor\_join',

 'format.type' = 'json'

);

  

  

This table will hold Kafka topic messages from our energy reader.

  

CREATE TABLE **energy** (

 uuid STRING, 

 systemtime STRING,  

        `current` INT, 

 voltage INT, 

 `power` INT, 

 `total` INT, 

 swver STRING, 

 hwver STRING,

 type STRING, 

 model STRING, 

 mac STRING, 

 deviceId STRING, 

 hwId STRING, 

 fwId STRING, 

 oemId STRING,

 alias STRING, 

 devname STRING, 

 iconhash STRING, 

 relaystate INT, 

 ontime INT, 

 activemode STRING, 

 feature STRING, 

 updating INT, 

 rssi INT, 

 ledoff INT, 

 latitude INT, 

 longitude INT, 

 `day` INT, 

 `index` INT, 

 zonestr STRING, 

 tzstr STRING, 

 dstoffset INT, 

 host STRING, 

 currentconsumption INT, 

 devicetime STRING, 

 ledon STRING, 

 fanstatus STRING, 

 `end` STRING, 

 te STRING, 

 cpu INT, 

 memory INT, 

 diskusage STRING

) WITH (

 'connector.type'    = 'kafka',

 'connector.version' = 'universal',

 'connector.topic'    = 'energy',

 'connector.startup-mode' = 'earliest-offset',

 'connector.properties.bootstrap.servers' = 'tspann-princeton0-cluster-0.general.fuse.l42.cloudera.com:9092',

 'connector.properties.group.id' = 'flink-sql-energy-consumer',

 'format.type' = 'json'

);

  

  

The **scada** table holds events from our sensors.

  

CREATE TABLE **scada** (

 uuid STRING, 

 systemtime STRING,  

 amplitude100 DOUBLE, 

        amplitude500 DOUBLE, 

 amplitude1000 DOUBLE, 

 lownoise DOUBLE, 

 midnoise DOUBLE,

        highnoise DOUBLE, 

 amps DOUBLE, 

 ipaddress STRING, 

 host STRING, 

 host\_name STRING,

        macaddress STRING, 

 endtime STRING, 

 runtime STRING, 

 starttime STRING, 

        cpu DOUBLE, 

 cpu\_temp STRING, 

 diskusage STRING, 

 memory DOUBLE, 

 id STRING, 

 temperature STRING, 

 adjtemp STRING, 

 adjtempf STRING, 

 temperaturef STRING, 

 pressure DOUBLE, 

 humidity DOUBLE, 

 lux DOUBLE, 

 proximity INT, 

 oxidising DOUBLE, 

 reducing DOUBLE, 

 nh3 DOUBLE, 

 gasko STRING

) WITH (

 'connector.type'    = 'kafka',

 'connector.version' = 'universal',

 'connector.topic'    = 'scada',

 'connector.startup-mode' = 'earliest-offset',

 'connector.properties.bootstrap.servers' = 'tspann-princeton0-cluster-0.general.fuse.l42.cloudera.com:9092',

 'connector.properties.group.id' = 'flink-sql-scada-consumer',

 'format.type' = 'json'

);

  

[![](https://1.bp.blogspot.com/-kTrh8igMasA/XrCDkh2bozI/AAAAAAAAZ-M/nTv_4av76CkDvJWSCFDMr1gOicOIukzBwCLcBGAsYHQ/s640/showtables.png)](https://1.bp.blogspot.com/-kTrh8igMasA/XrCDkh2bozI/AAAAAAAAZ-M/nTv_4av76CkDvJWSCFDMr1gOicOIukzBwCLcBGAsYHQ/s1600/showtables.png)

  

This is the **magic** part:

  

**INSERT** INTO global\_sensor\_events 

SELECT 

 scada.uuid, 

 scada.systemtime ,  

scada.temperaturef , 

scada.pressure , 

scada.humidity , 

scada.lux , 

scada.proximity , 

scada.oxidising , 

scada.reducing , 

scada.nh3 , 

scada.gasko,

energy.`current`, 

energy.voltage ,

energy.`power` ,

energy.`total`,

energy.fanstatus

  

FROM energy,

     scada

WHERE

**    scada.systemtime = energy.systemtime;**  

[![](https://1.bp.blogspot.com/-saYjfGcMpcU/XrCDjJAOeAI/AAAAAAAAZ90/dzhYRAntQKcBSLbDbz5Y-LqVqYgsACRgQCLcBGAsYHQ/s640/insertjobrunning.png)](https://1.bp.blogspot.com/-saYjfGcMpcU/XrCDjJAOeAI/AAAAAAAAZ90/dzhYRAntQKcBSLbDbz5Y-LqVqYgsACRgQCLcBGAsYHQ/s1600/insertjobrunning.png)

  

So we join two Kafka topics and use some of their fields to populate a third Kafka topic that we defined above.

  

With Cloudera, it is so easy to monitor our streaming Kafka events with SMM.

  

[![](https://1.bp.blogspot.com/-7KrZI-GPQBw/XrCDkBF7jCI/AAAAAAAAZ-A/vVRFsb4CXiQoVLjSyzfL-4YF1wUnJHEFwCLcBGAsYHQ/s640/sensoreventssmm.png)](https://1.bp.blogspot.com/-7KrZI-GPQBw/XrCDkBF7jCI/AAAAAAAAZ-A/vVRFsb4CXiQoVLjSyzfL-4YF1wUnJHEFwCLcBGAsYHQ/s1600/sensoreventssmm.png)

  

For context, this is where the data comes from:

  

[![](https://1.bp.blogspot.com/-8gvMdi2Gwm4/XrCDjkLF3wI/AAAAAAAAZ94/WrapnG30dPUL9WV7sSM3K2R_KuJAQ_cIACLcBGAsYHQ/s640/minififlow.png)](https://1.bp.blogspot.com/-8gvMdi2Gwm4/XrCDjkLF3wI/AAAAAAAAZ94/WrapnG30dPUL9WV7sSM3K2R_KuJAQ_cIACLcBGAsYHQ/s1600/minififlow.png)

  

[![](https://1.bp.blogspot.com/-WjCT3F1kJLw/XrCDkvEi3sI/AAAAAAAAZ-E/ByAEawXmWjchacKR-h1EBuDd1nkeWdzkwCLcBGAsYHQ/s640/sensorsLocal.png)](https://1.bp.blogspot.com/-WjCT3F1kJLw/XrCDkvEi3sI/AAAAAAAAZ-E/ByAEawXmWjchacKR-h1EBuDd1nkeWdzkwCLcBGAsYHQ/s1600/sensorsLocal.png)