Let's Query Kafka with Hive
Tags: 2020, apachekafka
Published: 2020-05-20 14:29:13

---
title: Let's Query Kafka with Hive
published: true
date: 2020-05-20 02:18:00 UTC
tags: 2020,apache kafka
canonical_url: 
---

<font size="6"><b>Let's Query Kafka with Hive</b></font>  

  

  

<font size="4">I can hop into beeline and build an external Hive table to access my Cloudera CDF Kafka cluster whether it is in the public cloud in CDP DataHub, on-premise in HDF or CDF or in CDP-DC.</font>

<font size="4"><br></font>

<font size="4">I just have to set my KafkaStorageHandler, Kafka Topic Name and my bootstrap servers (usually port 9092).   Now I can use that table to do ELT/ELT for populating Hive tables or populating Kafka topics from Hive tables.   This is a nice and easy way to do data engineering on the quick and easy.</font>

<font size="4"><br></font>

<font size="4">This is a good item to augment CDP Data Engineering with Spark, CDP DataHub with NiFi, CDP DataHub with Kafka and KafkaStreams and various SQOOP or Python utilities you may have in your environment.</font>

<font size="4"><br></font>

<font size="4">For real-time continuous queries on Kafka with SQL, you can use Flink SQL.  </font>[https://www.datainmotion.dev/2020/05/flank-low-code-streaming-populating.html](https://dev.to/tspannhw/flank-low-code-streaming-populating-kafka-topics-with-flinksql-joins-in-real-time-3jna)

  

[![](https://1.bp.blogspot.com/-I4B1p-iO5g4/Xrr69p0af1I/AAAAAAAAaSw/tUjliVoAQJU9Q_lq_OLmxaOLicvHy0WkACK4BGAsYHg/w640-h106/flinksqlclient2.png)](https://1.bp.blogspot.com/-I4B1p-iO5g4/Xrr69p0af1I/AAAAAAAAaSw/tUjliVoAQJU9Q_lq_OLmxaOLicvHy0WkACK4BGAsYHg/flinksqlclient2.png)

  

<font size="4"><br></font>

<font size="4"><b>Example Table Create</b></font>

<font size="4"><br></font>

<font size="4">CREATE EXTERNAL TABLE &lt;tableName&gt;</font>

<font size="4">  (`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)</font>

<font size="4">  STORED BY 'org.apache.hadoop.hive.kafka.KafkaStorageHandler'</font>

<font size="4">  TBLPROPERTIES</font>

<font size="4">  ("kafka.topic" = "&lt;TopicName&gt;", </font>

<font size="4">  "kafka.bootstrap.servers"="&lt;ServerName&gt;:9092");</font>

<font size="4"><br></font>

<font size="4">show tables;</font>

<font size="4"><br></font>

<font size="4">describe extended kafka_table;</font>

<font size="4"><br></font>

<font size="4">select *</font>

<font size="4">from kafka_table;</font>

<font size="4"><br></font>

<font size="4">I can browse my Kafka topics with Cloudera SMM to see what the data is and why I want to load or need to load.</font>

<font size="4"><br></font>

[![](https://1.bp.blogspot.com/-nDcSabsEkZc/XrXH4KB432I/AAAAAAAAaI8/AlpcWz3o2Y8gfdSEnECz4OLm6UMJ8CJvACK4BGAsYHg/w640-h290/sensoreventssmm.png)](https://1.bp.blogspot.com/-nDcSabsEkZc/XrXH4KB432I/AAAAAAAAaI8/AlpcWz3o2Y8gfdSEnECz4OLm6UMJ8CJvACK4BGAsYHg/sensoreventssmm.png)
<font size="4"><br></font>

<font size="4"><br></font>

<font size="4">For more information take a look at the documentation for Integrating Hive and Kafka at Cloudera below:</font>

 

- <font size="4"><span style='background-color: white; color: #24292e; font-family: SFMono-Regular, Consolas, "Liberation Mono", Menlo, monospace; font-size: 12px; white-space: pre;'>https:</span><span style='background-color: white; box-sizing: border-box; color: #d73a49; font-family: SFMono-Regular, Consolas, "Liberation Mono", Menlo, monospace; font-size: 12px; white-space: pre;'>//</span><span style='background-color: white; box-sizing: border-box; color: #005cc5; font-family: SFMono-Regular, Consolas, "Liberation Mono", Menlo, monospace; font-size: 12px; white-space: pre;'>docs</span><span style='background-color: white; color: #24292e; font-family: SFMono-Regular, Consolas, "Liberation Mono", Menlo, monospace; font-size: 12px; white-space: pre;'>.</span><span style='background-color: white; box-sizing: border-box; color: #005cc5; font-family: SFMono-Regular, Consolas, "Liberation Mono", Menlo, monospace; font-size: 12px; white-space: pre;'>cloudera</span><span style='background-color: white; color: #24292e; font-family: SFMono-Regular, Consolas, "Liberation Mono", Menlo, monospace; font-size: 12px; white-space: pre;'>.com</span><span style='background-color: white; box-sizing: border-box; color: #d73a49; font-family: SFMono-Regular, Consolas, "Liberation Mono", Menlo, monospace; font-size: 12px; white-space: pre;'>/</span><span style='background-color: white; color: #24292e; font-family: SFMono-Regular, Consolas, "Liberation Mono", Menlo, monospace; font-size: 12px; white-space: pre;'>HDPDocuments</span><span style='background-color: white; box-sizing: border-box; color: #d73a49; font-family: SFMono-Regular, Consolas, "Liberation Mono", Menlo, monospace; font-size: 12px; white-space: pre;'>/</span><span style='background-color: white; color: #24292e; font-family: SFMono-Regular, Consolas, "Liberation Mono", Menlo, monospace; font-size: 12px; white-space: pre;'>HDP3</span><span style='background-color: white; box-sizing: border-box; color: #d73a49; font-family: SFMono-Regular, Consolas, "Liberation Mono", Menlo, monospace; font-size: 12px; white-space: pre;'>/</span><span style='background-color: white; color: #24292e; font-family: SFMono-Regular, Consolas, "Liberation Mono", Menlo, monospace; font-size: 12px; white-space: pre;'>HDP</span><span style='background-color: white; box-sizing: border-box; color: #d73a49; font-family: SFMono-Regular, Consolas, "Liberation Mono", Menlo, monospace; font-size: 12px; white-space: pre;'>-</span><span style='background-color: white; box-sizing: border-box; color: #005cc5; font-family: SFMono-Regular, Consolas, "Liberation Mono", Menlo, monospace; font-size: 12px; white-space: pre;'>3</span><span style='background-color: white; color: #24292e; font-family: SFMono-Regular, Consolas, "Liberation Mono", Menlo, monospace; font-size: 12px; white-space: pre;'>.</span><span style='background-color: white; box-sizing: border-box; color: #005cc5; font-family: SFMono-Regular, Consolas, "Liberation Mono", Menlo, monospace; font-size: 12px; white-space: pre;'>1</span><span style='background-color: white; color: #24292e; font-family: SFMono-Regular, Consolas, "Liberation Mono", Menlo, monospace; font-size: 12px; white-space: pre;'>.</span><span style='background-color: white; box-sizing: border-box; color: #005cc5; font-family: SFMono-Regular, Consolas, "Liberation Mono", Menlo, monospace; font-size: 12px; white-space: pre;'>5</span><span style='background-color: white; box-sizing: border-box; color: #d73a49; font-family: SFMono-Regular, Consolas, "Liberation Mono", Menlo, monospace; font-size: 12px; white-space: pre;'>/</span><span style='background-color: white; color: #24292e; font-family: SFMono-Regular, Consolas, "Liberation Mono", Menlo, monospace; font-size: 12px; white-space: pre;'>integrating</span><span style='background-color: white; box-sizing: border-box; color: #d73a49; font-family: SFMono-Regular, Consolas, "Liberation Mono", Menlo, monospace; font-size: 12px; white-space: pre;'>-</span><span style='background-color: white; color: #24292e; font-family: SFMono-Regular, Consolas, "Liberation Mono", Menlo, monospace; font-size: 12px; white-space: pre;'>hive</span><span style='background-color: white; box-sizing: border-box; color: #d73a49; font-family: SFMono-Regular, Consolas, "Liberation Mono", Menlo, monospace; font-size: 12px; white-space: pre;'>/</span><span style='background-color: white; color: #24292e; font-family: SFMono-Regular, Consolas, "Liberation Mono", Menlo, monospace; font-size: 12px; white-space: pre;'>content</span><span style='background-color: white; box-sizing: border-box; color: #d73a49; font-family: SFMono-Regular, Consolas, "Liberation Mono", Menlo, monospace; font-size: 12px; white-space: pre;'>/</span><span style='background-color: white; box-sizing: border-box; color: #005cc5; font-family: SFMono-Regular, Consolas, "Liberation Mono", Menlo, monospace; font-size: 12px; white-space: pre;'>hive_kafka_query_table</span><span style='background-color: white; color: #24292e; font-family: SFMono-Regular, Consolas, "Liberation Mono", Menlo, monospace; font-size: 12px; white-space: pre;'>.</span><span style='background-color: white; box-sizing: border-box; color: #005cc5; font-family: SFMono-Regular, Consolas, "Liberation Mono", Menlo, monospace; font-size: 12px; white-space: pre;'>html</span></font>
- [https://docs.cloudera.com/runtime/7.0.3/integrating-hive-and-bi/topics/hive-kafka-integration.html](https://docs.cloudera.com/runtime/7.0.3/integrating-hive-and-bi/topics/hive-kafka-integration.html)
- https://docs.cloudera.com/runtime/7.1.0/integrating-hive-and-bi/topics/hive-kafka-integration.html
- https://docs.cloudera.com/runtime/7.1.0/integrating-hive-and-bi/topics/hive\_ingest\_kafka\_data\_into\_hive.html