{"id":3832,"date":"2019-08-06T01:02:34","date_gmt":"2019-08-06T01:02:34","guid":{"rendered":"https:\/\/dalelane.co.uk\/blog\/?p=3832"},"modified":"2019-08-06T01:02:34","modified_gmt":"2019-08-06T01:02:34","slug":"sql-queries-on-kafka-topics-using-apache-hive","status":"publish","type":"post","link":"https:\/\/dalelane.co.uk\/blog\/?p=3832","title":{"rendered":"SQL queries on Kafka topics using Apache Hive"},"content":{"rendered":"

Apache Hive<\/a> is open source data warehouse software built on top of Hadoop. It gives you an SQL-like interface to a wide variety of databases, filesystems, and other systems.<\/p>\n

One of the Hive storage handlers is a Kafka storage handler<\/a>, which lets you create a Hive “external table” based on a Kafka topic.<\/p>\n

And once you’ve created a Hive table based on a Kafka topic, you can run SQL queries based on attributes of the messages on that topic.<\/p>\n

I was having a play with Hive this evening, as a way of running SQL queries against messages on my Kafka topics. In this post, I’ll share a few queries that I tried.<\/strong><\/p>\n


\n<\/p>\n

Setting up Hive<\/h3>\n

If you want to run Hive properly, follow the Getting Started instructions in the Hive documentation<\/a>.<\/p>\n

But if you just want to give it a quick try:<\/p>\n

Get Hadoop<\/h4>\n
    \n
  1. Download a release of Hadoop<\/a><\/li>\n
  2. Unzip<\/li>\n
  3. export HADOOP_HOME=\/location\/you\/unzipped\/hadoop<\/code><\/li>\n<\/ol>\n

    Get Hive<\/h4>\n
      \n
    1. Download a release of Hive<\/a><\/li>\n
    2. Unzip<\/li>\n
    3. export HIVE_HOME=\/location\/you\/unzipped\/hive<\/code><\/li>\n
    4. Download the kafka-handler jar<\/a> and save it in $HIVE_HOME\/lib\/<\/code><\/li>\n<\/ol>\n

      Setup Hive<\/h4>\n

      Prepare folders for use by Hadoop.<\/p>\n

      $ $HADOOP_HOME\/bin\/hadoop fs -mkdir       \/tmp\n$ $HADOOP_HOME\/bin\/hadoop fs -mkdir       \/user\/hive\/warehouse\n$ $HADOOP_HOME\/bin\/hadoop fs -chmod g+w   \/tmp\n$ $HADOOP_HOME\/bin\/hadoop fs -chmod g+w   \/user\/hive\/warehouse\n<\/pre>\n
      Initialise the server.<\/p>\n
      $HIVE_HOME\/bin\/schematool -dbType derby -initSchema\n<\/pre>\n
      Run Hive<\/h4>\n
      For quick development\/testing purposes, running the Hive server and the CLI all in a single process is the simplest:<\/p>\n
      $HIVE_HOME\/bin\/beeline -u jdbc:hive2:\/\/\n<\/pre>\n
      Prepare a Kafka topic<\/h3>\n
      I’m using IBM Event Streams<\/a> because I work on it, but you could use plain Apache Kafka.<\/p>\n
      First, I created a topic DALE.TOPIC<\/code>, and I produced five quick test messages to it.<\/p>\n
      <\/a>
      \n      Click to enlarge<\/small><\/em> <\/a><\/p>\n
      Each message is a JSON string, with a key called message<\/code> and a different value.<\/p>\n
      {\"message\":\"Hello World\"}\n{\"message\":\"This is a test\"}\n{\"message\":\"These are messages\"}\n{\"message\":\"Lorem Ipsum\"}\n{\"message\":\"Lorem ipsum dolor sit amet\"}\n<\/pre>\n
      Next, I downloaded a Java truststore, and created an API key, to let Hive make a secure connection to my Kafka cluster.<\/p>\n
      <\/a>
      \n      Click to enlarge<\/small><\/em> <\/a><\/p>\n
      Then, I got the set of connection properties needed to connect an application to my topic.<\/p>\n
      <\/a>
      \n      Click to enlarge<\/small><\/em> <\/a><\/p>\n
      That was enough info to create the external table in Hive, using Hive’s beehive<\/code> CLI:<\/p>\n
      Connected to: Apache Hive (version 3.1.1)\nDriver: Hive JDBC (version 3.1.1)\nTransaction isolation: TRANSACTION_REPEATABLE_READ\nBeeline version 3.1.1 by Apache Hive\n0: jdbc:hive2:\/\/> <\/font>CREATE EXTERNAL TABLE test_kafka<\/strong>\n. . . . . . . . ><\/font> (`message` string)<\/strong>\n. . . . . . . . ><\/font> STORED BY 'org.apache.hadoop.hive.kafka.KafkaStorageHandler'<\/strong>\n. . . . . . . . ><\/font> TBLPROPERTIES<\/strong>\n. . . . . . . . ><\/font> (\"kafka.topic\" = \"DALE.TOPIC\",<\/strong>\n. . . . . . . . ><\/font>  \"kafka.bootstrap.servers\"=\"9.30.245.82:32342\",<\/strong>\n. . . . . . . . ><\/font>  \"kafka.consumer.sasl.jaas.config\"=\"org.apache.kafka.common.security.plain.PlainLoginModule required username='token' password='9UfXe0KkabBqjXiU9NdGnvtEPVrqJ_tvLo-ZqnQy8Gww';\",<\/strong>\n. . . . . . . . ><\/font>  \"kafka.consumer.sasl.mechanism\"=\"PLAIN\",<\/strong>\n. . . . . . . . ><\/font>  \"kafka.consumer.security.protocol\"=\"SASL_SSL\",<\/strong>\n. . . . . . . . ><\/font>  \"kafka.consumer.ssl.protocol\"=\"TLSv1.2\",<\/strong>\n. . . . . . . . ><\/font>  \"kafka.consumer.ssl.truststore.location\"=\"\/root\/es-cert.jks\",<\/strong>\n. . . . . . . . ><\/font>  \"kafka.consumer.ssl.truststore.password\"=\"password\"<\/strong>\n. . . . . . . . ><\/font>  );<\/strong>\nOK\nNo rows affected<\/font>\n\n<\/pre>\n
      Notice that the first two table properties I’ve used are kafka.topic<\/code> with my topic name, and kafka.bootstrap.servers<\/code> with the bootstrap address for my cluster.<\/p>\n
      The rest are the properties I copied from the Event Streams UI, prefixed with kafka.consumer.<\/code> because I’m using SELECT<\/code> queries to read from my topic.<\/p>\n
      Simple SQL queries<\/h3>\n
      I can use a SELECT<\/code> query to fetch my five messages from the topic.<\/p>\n
      0: jdbc:hive2:\/\/> <\/font>SELECT * from test_kafka;<\/strong>\nOK<\/font>\n+-----------------------------+-------------------+-------------------------+----------------------+-------------------------+\n|     test_kafka.message      | test_kafka.__key  | test_kafka.__partition  | test_kafka.__offset  | test_kafka.__timestamp  |\n+-----------------------------+-------------------+-------------------------+----------------------+-------------------------+\n| Hello World                 |                   | 0                       | 0                    | 1565042407338           |\n| This is a test              |                   | 1                       | 0                    | 1565042419083           |\n| These are messages          |                   | 2                       | 0                    | 1565042429234           |\n| Lorem Ipsum                 |                   | 0                       | 1                    | 1565042458894           |\n| Lorem ipsum dolor sit amet  |                   | 1                       | 1                    | 1565042612579           |\n+-----------------------------+-------------------+-------------------------+----------------------+-------------------------+<\/strong>\n5 rows selected<\/font>\n\n<\/pre>\n
      Hive gives me the field I gave it for the table (the “message” string attribute that I put in every Kafka message), and metadata fields for the key, partition, offset and timestamp.<\/p>\n
      I can use SQL WHERE<\/code> clauses to filter based on this metadata.<\/p>\n
      For example, to get messages between two timestamps:<\/p>\n
      0: jdbc:hive2:\/\/><\/font> SELECT * FROM test_kafka <\/strong>\n. . . . . . . . ><\/font> WHERE <\/strong>\n. . . . . . . . ><\/font>   `__timestamp` > 1565042420000 <\/strong>\n. . . . . . . . ><\/font> AND <\/strong>\n. . . . . . . . ><\/font>   `__timestamp` < 1565042520000;<\/strong>\nOK<\/font>\n+---------------------+-------------------+-------------------------+----------------------+-------------------------+\n| test_kafka.message  | test_kafka.__key  | test_kafka.__partition  | test_kafka.__offset  | test_kafka.__timestamp  |\n+---------------------+-------------------+-------------------------+----------------------+-------------------------+\n| These are messages  |                   | 2                       | 2                    | 1565042429234           |\n| Lorem Ipsum         |                   | 0                       | 1                    | 1565042458894           |\n+---------------------+-------------------+-------------------------+----------------------+-------------------------+<\/strong>\n2 rows selected<\/font>\n\n<\/pre>\n
      A more interesting Kafka topic<\/h3>\n
      Those queries worked because I’d used the same field message<\/code> in every Kafka message.<\/p>\n
      I’ve written before that a better way of ensuring consistency in Kafka messages is to use a schema<\/a>.<\/p>\n
      And Avro schemas work well with Hive queries.<\/p>\n
      I created a schema to define messages about Mario games, and uploaded it to the Event Streams Schema Registry.<\/p>\n
      <\/a>
      \n      Click to enlarge<\/small><\/em> <\/a><\/p>\n
      Each Kafka message on my topic will be about a Mario game, and have attributes with the name of the game, the year it was released, and the number of copies sold.<\/p>\n
      I created a new MARIO.TOPIC<\/code> topic to use the schema with.<\/p>\n
      Then I produced messages to represent stats about a bunch of Mario games<\/a>.<\/p>\n
      <\/a>
      \n      Click to enlarge<\/small><\/em> <\/a><\/p>\n
      The Event Streams UI integrates with the Schema Registry, so if I click on a couple of the messages, you can see the parsed message fields.<\/p>\n
      <\/a>
      \n      Click to enlarge<\/small><\/em> <\/a><\/p>\n
      <\/a>
      \n      Click to enlarge<\/small><\/em> <\/a><\/p>\n
      Now I had some test data suitable for trying out more interesting queries with.<\/p>\n
      Setting up Hive to use Avro schemas<\/h3>\n
      To use the schema, I needed to download a serdes jar that can fetch schemas on-demand from the Event Streams Schema Registry. I used the Event Streams UI to download the jars that I need.<\/p>\n
      <\/a>
      \n      Click to enlarge<\/small><\/em> <\/a><\/p>\n
      I copied the Event Streams serdes jars to $HIVE_HOME\/auxlib<\/code>. (I needed to create that folder first, and then restart my Hive process).<\/p>\n
      And I needed to add a kafka.serdes.class<\/code> table property when creating the external table in Hive.<\/p>\n
      Connected to: Apache Hive (version 3.1.1)\nDriver: Hive JDBC (version 3.1.1)\nTransaction isolation: TRANSACTION_REPEATABLE_READ\nBeeline version 3.1.1 by Apache Hive\n0: jdbc:hive2:\/\/> <\/font>CREATE EXTERNAL TABLE mario_kafka<\/strong>\n. . . . . . . . ><\/font> (`title` string, `releaseYear` int, `salesInMillions` float)<\/strong>\n. . . . . . . . ><\/font> STORED BY 'org.apache.hadoop.hive.kafka.KafkaStorageHandler'<\/strong>\n. . . . . . . . ><\/font> TBLPROPERTIES<\/strong>\n. . . . . . . . ><\/font> (\"kafka.topic\" = \"MARIO.TOPIC\",<\/strong>\n. . . . . . . . ><\/font>  \"kafka.bootstrap.servers\"=\"9.30.245.82:32342\",<\/strong>\n. . . . . . . . ><\/font>  \"kafka.consumer.sasl.jaas.config\"=\"org.apache.kafka.common.security.plain.PlainLoginModule required username='token' password='9UfXe0KkabBqjXiU9NdGnvtEPVrqJ_tvLo-ZqnQy8Gww';\",<\/strong>\n. . . . . . . . ><\/font>  \"kafka.consumer.sasl.mechanism\"=\"PLAIN\",<\/strong>\n. . . . . . . . ><\/font>  \"kafka.consumer.security.protocol\"=\"SASL_SSL\",<\/strong>\n. . . . . . . . ><\/font>  \"kafka.consumer.ssl.protocol\"=\"TLSv1.2\",<\/strong>\n. . . . . . . . ><\/font>  \"kafka.consumer.ssl.truststore.location\"=\"\/root\/es-cert.jks\",<\/strong>\n. . . . . . . . ><\/font>  \"kafka.consumer.ssl.truststore.password\"=\"password\",<\/strong>\n. . . . . . . . ><\/font>  \"kafka.serdes.class\"=\"com.ibm.eventstreams.serdes.EventStreamsSerdes\"<\/strong>\n. . . . . . . . ><\/font>  );<\/strong>\nOK\nNo rows affected<\/font>\n\n<\/pre>\n
      SQL queries with filters<\/h3>\n
      That means I can do queries like getting all Mario games from the last five years.<\/p>\n
      0: jdbc:hive2:\/\/><\/font> SELECT * FROM mario_kafka <\/strong>\n. . . . . . . . ><\/font> WHERE <\/strong>\n. . . . . . . . ><\/font>  `releaseYear` > 2013;<\/strong>\nOK<\/font>\n+-------------------------------------+--------------------------+------------------------------+--------------------+--------------------------+-----------------------+--------------------------+\n|          mario_kafka.title          | mario_kafka.releaseyear  | mario_kafka.salesinmillions  | mario_kafka.__key  | mario_kafka.__partition  | mario_kafka.__offset  | mario_kafka.__timestamp  |\n+-------------------------------------+--------------------------+------------------------------+--------------------+--------------------------+-----------------------+--------------------------+\n| Super Mario Odyssey                 | 2017                     | 14.44                        | NULL               | 0                        | 3                     | 1565041149793            |\n| Mario Kart 8                        | 2014                     | 8.44                         | NULL               | 0                        | 7                     | 1565041149795            |\n| Super Mario Maker                   | 2015                     | 4.0                          | NULL               | 0                        | 13                    | 1565041149798            |\n| Mario Party 10                      | 2015                     | 2.21                         | NULL               | 0                        | 19                    | 1565041149800            |\n| Super Mario Party                   | 2018                     | 6.4                          | NULL               | 2                        | 8                     | 1565041149796            |\n| New Super Mario Bros. U Deluxe      | 2019                     | 3.31                         | NULL               | 2                        | 13                    | 1565041149798            |\n| Mario Tennis Aces                   | 2018                     | 2.64                         | NULL               | 2                        | 15                    | 1565041149799            |\n| Mario Kart 8 Deluxe (NS)            | 2017                     | 16.69                        | NULL               | 1                        | 3                     | 1565041149793            |\n| Super Mario Maker for Nintendo 3DS  | 2016                     | 2.01                         | NULL               | 1                        | 21                    | 1565041149801            |\n+-------------------------------------+--------------------------+------------------------------+--------------------+--------------------------+-----------------------+--------------------------+<\/strong>\n9 rows selected<\/font>\n\n<\/pre>\n
      Or all Mario games that have sold more than 20 million copies.<\/p>\n
      0: jdbc:hive2:\/\/><\/font> SELECT * FROM mario_kafka<\/strong>\n. . . . . . . . ><\/font> WHERE<\/strong>\n. . . . . . . . ><\/font>  `salesInMillions` > 20;<\/strong>\nOK<\/font>\n+----------------------------+--------------------------+------------------------------+--------------------+--------------------------+-----------------------+--------------------------+\n|     mario_kafka.title      | mario_kafka.releaseyear  | mario_kafka.salesinmillions  | mario_kafka.__key  | mario_kafka.__partition  | mario_kafka.__offset  | mario_kafka.__timestamp  |\n+----------------------------+--------------------------+------------------------------+--------------------+--------------------------+-----------------------+--------------------------+\n| Mario Kart Wii             | 2008                     | 37.2                         | NULL               | 0                        | 0                     | 1565041149791            |\n| Mario Kart DS              | 2005                     | 23.6                         | NULL               | 0                        | 1                     | 1565041149792            |\n| New Super Mario Bros.      | 2006                     | 30.8                         | NULL               | 2                        | 0                     | 1565041149791            |\n| Super Mario World          | 1990                     | 20.61                        | NULL               | 2                        | 1                     | 1565041149792            |\n| Super Mario Bros           | 1985                     | 40.24                        | NULL               | 1                        | 0                     | 1565041149783            |\n| New Super Mario Bros. Wii  | 2009                     | 30.26                        | NULL               | 1                        | 1                     | 1565041149791            |\n+----------------------------+--------------------------+------------------------------+--------------------+--------------------------+-----------------------+--------------------------+<\/strong>\n6 rows selected<\/font>\n\n<\/pre>\n
      SQL queries with aggregates<\/h3>\n
      Or get the total number of games sold for Mario games grouped by the year of release.<\/p>\n
      Notice how Hive is handling this by setting up a map-reduce job to handle the SUM<\/code>.<\/p>\n
      0: jdbc:hive2:\/\/><\/font> SELECT <\/strong>\n. . . . . . . . ><\/font>   `releaseYear`, <\/strong>\n. . . . . . . . ><\/font>   SUM(`salesInMillions`) AS totalSales <\/strong>\n. . . . . . . . ><\/font> FROM mario_kafka <\/strong>\n. . . . . . . . ><\/font>   GROUP BY `releaseYear`;<\/strong>\nQuery ID = root_20190805145028_58130aad-b433-4dbe-aed5-8b7fbfea57bb\nTotal jobs = 1\nLaunching Job 1 out of 1\nNumber of reduce tasks not specified. Estimated from input data size: 1\nIn order to change the average load for a reducer (in bytes):\n  set hive.exec.reducers.bytes.per.reducer=<number>\nIn order to limit the maximum number of reducers:\n  set hive.exec.reducers.max=<number>\nIn order to set a constant number of reducers:\n  set mapreduce.job.reduces=<number>\nJob running in-process (local Hadoop)\n2019-08-05 14:50:31,181 Stage-1 map = 100%,  reduce = 0%\n2019-08-05 14:50:33,194 Stage-1 map = 100%,  reduce = 100%\nEnded Job = job_local1519121693_0002\nMapReduce Jobs Launched:\nStage-Stage-1:  HDFS Read: 0 HDFS Write: 0 SUCCESS\nTotal MapReduce CPU Time Spent: 0 msec\nOK<\/font>\n+--------------+---------------------+\n| releaseyear  |     totalsales      |\n+--------------+---------------------+\n| 1983         | 2.2799999713897705  |\n| 1985         | 40.2400016784668    |\n| 1986         | 2.6500000953674316  |\n| 1988         | 24.74000072479248   |\n| 1989         | 18.139999389648438  |\n| 1990         | 30.80000066757202   |\n| 1992         | 22.250000476837158  |\n| 1993         | 10.550000190734863  |\n| 1994         | 5.190000057220459   |\n| 1995         | 4.119999885559082   |\n| 1996         | 23.919999837875366  |\n| 1998         | 2.700000047683716   |\n| 1999         | 9.020000219345093   |\n| 2000         | 6.779999852180481   |\n| 2001         | 17.170000076293945  |\n| 2002         | 11.320000171661377  |\n| 2003         | 17.759999990463257  |\n| 2004         | 18.59000015258789   |\n| 2005         | 28.050000429153442  |\n| 2006         | 33.53999924659729   |\n| 2007         | 35.00000071525574   |\n| 2008         | 38.46000075340271   |\n| 2009         | 34.390000343322754  |\n| 2010         | 9.18999981880188    |\n| 2011         | 34.540000200271606  |\n| 2012         | 23.869999885559082  |\n| 2013         | 13.190000057220459  |\n| 2014         | 8.4399995803833     |\n| 2015         | 6.210000038146973   |\n| 2016         | 2.009999990463257   |\n| 2017         | 31.130000114440918  |\n| 2018         | 9.040000200271606   |\n| 2019         | 3.309999942779541   |\n+--------------+---------------------+<\/strong>\n33 rows selected<\/font>\n\n<\/pre>\n
      If I count how many Mario games were released in each year, and sort that by the number of releases, this causes Hive to need a second map-reduce job.<\/p>\n
      0: jdbc:hive2:\/\/><\/font> SELECT <\/strong>\n. . . . . . . . ><\/font>   `releaseYear`, <\/strong>\n. . . . . . . . ><\/font>   COUNT(*) AS numReleases <\/strong>\n. . . . . . . . ><\/font> FROM mario_kafka <\/strong>\n. . . . . . . . ><\/font> GROUP BY `releaseYear` <\/strong>\n. . . . . . . . ><\/font> ORDER BY numReleases DESC;<\/strong>\nQuery ID = root_20190805145411_dcf88f12-49ec-4a61-aea3-299fb9d964f6\nTotal jobs = 2\nLaunching Job 1 out of 2\nNumber of reduce tasks not specified. Estimated from input data size: 1\nIn order to change the average load for a reducer (in bytes):\n  set hive.exec.reducers.bytes.per.reducer=<number>\nIn order to limit the maximum number of reducers:\n  set hive.exec.reducers.max=<number>\nIn order to set a constant number of reducers:\n  set mapreduce.job.reduces=<number>\nJob running in-process (local Hadoop)\n2019-08-05 14:54:14,191 Stage-1 map = 100%,  reduce = 0%\n2019-08-05 14:54:16,197 Stage-1 map = 100%,  reduce = 100%\nEnded Job = job_local1092517259_0006\nLaunching Job 2 out of 2\nNumber of reduce tasks determined at compile time: 1\nIn order to change the average load for a reducer (in bytes):\n  set hive.exec.reducers.bytes.per.reducer=<number>\nIn order to limit the maximum number of reducers:\n  set hive.exec.reducers.max=<number>\nIn order to set a constant number of reducers:\n  set mapreduce.job.reduces=<number>\nJob running in-process (local Hadoop)\n2019-08-05 14:54:17,511 Stage-2 map = 100%,  reduce = 100%\nEnded Job = job_local116294658_0007\nMapReduce Jobs Launched:\nStage-Stage-1:  HDFS Read: 0 HDFS Write: 0 SUCCESS\nStage-Stage-2:  HDFS Read: 0 HDFS Write: 0 SUCCESS\nTotal MapReduce CPU Time Spent: 0 msec\nOK<\/font>\n+--------------+--------------+\n| releaseyear  | numreleases  |\n+--------------+--------------+\n| 2004         | 5            |\n| 2003         | 5            |\n| 2013         | 5            |\n| 2007         | 5            |\n| 2000         | 4            |\n| 2012         | 4            |\n| 2011         | 4            |\n| 2005         | 4            |\n| 1999         | 3            |\n| 2006         | 3            |\n| 1992         | 3            |\n| 1990         | 3            |\n| 1996         | 3            |\n| 2002         | 3            |\n| 2001         | 3            |\n| 2008         | 2            |\n| 2015         | 2            |\n| 2017         | 2            |\n| 1988         | 2            |\n| 2010         | 2            |\n| 2009         | 2            |\n| 2018         | 2            |\n| 2019         | 1            |\n| 2016         | 1            |\n| 2014         | 1            |\n| 1998         | 1            |\n| 1995         | 1            |\n| 1994         | 1            |\n| 1993         | 1            |\n| 1989         | 1            |\n| 1986         | 1            |\n| 1985         | 1            |\n| 1983         | 1            |\n+--------------+--------------+<\/strong>\n33 rows selected<\/font>\n\n<\/pre>\n
      Creating views<\/h3>\n
      I can also create a view to make it easier to refer to subsets of the messages on my Kafka topic.<\/p>\n
      0: jdbc:hive2:\/\/><\/font> SELECT * from mario_kafka <\/strong>\n. . . . . . . . ><\/font> WHERE `title` LIKE '%Party%';<\/strong>\nOK<\/font>\n+---------------------------+--------------------------+------------------------------+--------------------+--------------------------+-----------------------+--------------------------+\n|     mario_kafka.title     | mario_kafka.releaseyear  | mario_kafka.salesinmillions  | mario_kafka.__key  | mario_kafka.__partition  | mario_kafka.__offset  | mario_kafka.__timestamp  |\n+---------------------------+--------------------------+------------------------------+--------------------+--------------------------+-----------------------+--------------------------+\n| Mario Party DS            | 2007                     | 9.31                         | NULL               | 0                        | 6                     | 1565041149795            |\n| Mario Party 10            | 2015                     | 2.21                         | NULL               | 0                        | 19                    | 1565041149800            |\n| Mario Party 7             | 2005                     | 1.86                         | NULL               | 0                        | 21                    | 1565041149801            |\n| Mario Party 8             | 2007                     | 8.85                         | NULL               | 2                        | 6                     | 1565041149795            |\n| Super Mario Party         | 2018                     | 6.4                          | NULL               | 2                        | 8                     | 1565041149796            |\n| Mario Party 9             | 2012                     | 2.73                         | NULL               | 2                        | 14                    | 1565041149798            |\n| Mario Party 4             | 2002                     | 2.21                         | NULL               | 2                        | 17                    | 1565041149800            |\n| Mario Party 5             | 2003                     | 2.0                          | NULL               | 2                        | 19                    | 1565041149800            |\n| Mario Party 3             | 2000                     | 1.91                         | NULL               | 2                        | 21                    | 1565041149801            |\n| Mario Party 6             | 2004                     | 1.65                         | NULL               | 2                        | 22                    | 1565041149801            |\n| Mario Party: Island Tour  | 2013                     | 1.14                         | NULL               | 2                        | 26                    | 1565041149803            |\n| Mario Party               | 1998                     | 2.7                          | NULL               | 1                        | 15                    | 1565041149799            |\n| Mario Party 2             | 1999                     | 2.48                         | NULL               | 1                        | 16                    | 1565041149799            |\n+---------------------------+--------------------------+------------------------------+--------------------+--------------------------+-----------------------+--------------------------+<\/strong>\n13 rows selected\n0: jdbc:hive2:\/\/>\n0: jdbc:hive2:\/\/><\/font>\n0: jdbc:hive2:\/\/><\/font> CREATE VIEW mario_party_games<\/strong>\n. . . . . . . . ><\/font> AS <\/strong>\n. . . . . . . . ><\/font>   SELECT `title`, `releaseYear`, `salesInMillions`<\/strong>\n. . . . . . . . ><\/font>   ADDED FROM mario_kafka<\/strong>\n. . . . . . . . ><\/font>   WHERE `title` LIKE '%Party%';<\/strong>\nOK\nNo rows affected\n0: jdbc:hive2:\/\/><\/font>\n0: jdbc:hive2:\/\/><\/font>  SELECT * FROM mario_party_games;<\/strong>\nOK<\/font>\n+---------------------------+--------------------------------+--------------------------+\n|  mario_party_games.title  | mario_party_games.releaseyear  | mario_party_games.added  |\n+---------------------------+--------------------------------+--------------------------+\n| Mario Party DS            | 2007                           | 9.31                     |\n| Mario Party 10            | 2015                           | 2.21                     |\n| Mario Party 7             | 2005                           | 1.86                     |\n| Mario Party 8             | 2007                           | 8.85                     |\n| Super Mario Party         | 2018                           | 6.4                      |\n| Mario Party 9             | 2012                           | 2.73                     |\n| Mario Party 4             | 2002                           | 2.21                     |\n| Mario Party 5             | 2003                           | 2.0                      |\n| Mario Party 3             | 2000                           | 1.91                     |\n| Mario Party 6             | 2004                           | 1.65                     |\n| Mario Party: Island Tour  | 2013                           | 1.14                     |\n| Mario Party               | 1998                           | 2.7                      |\n| Mario Party 2             | 1999                           | 2.48                     |\n+---------------------------+--------------------------------+--------------------------+<\/strong>\n13 rows selected\n0: jdbc:hive2:\/\/>\n0: jdbc:hive2:\/\/>\n0: jdbc:hive2:\/\/>\n0: jdbc:hive2:\/\/> <\/font>SELECT * FROM mario_party_games <\/strong>\n. . . . . . . . ><\/font> ORDER BY `releaseYear`;<\/strong>\nQuery ID = root_20190805152257_eb7a4e36-36b4-4e1d-86c2-9bbf5999115b\nTotal jobs = 1\nLaunching Job 1 out of 1\nNumber of reduce tasks determined at compile time: 1\nIn order to change the average load for a reducer (in bytes):\n  set hive.exec.reducers.bytes.per.reducer=<number>\nIn order to limit the maximum number of reducers:\n  set hive.exec.reducers.max=<number>\nIn order to set a constant number of reducers:\n  set mapreduce.job.reduces=<number>\nJob running in-process (local Hadoop)\n2019-08-05 15:23:00,367 Stage-1 map = 100%,  reduce = 0%\n2019-08-05 15:23:02,373 Stage-1 map = 100%,  reduce = 100%\nEnded Job = job_local1254056661_0008\nMapReduce Jobs Launched:\nStage-Stage-1:  HDFS Read: 0 HDFS Write: 0 SUCCESS\nTotal MapReduce CPU Time Spent: 0 msec\nOK<\/font>\n+---------------------------+--------------------------------+--------------------------+\n|  mario_party_games.title  | mario_party_games.releaseyear  | mario_party_games.added  |\n+---------------------------+--------------------------------+--------------------------+\n| Mario Party               | 1998                           | 2.7                      |\n| Mario Party 2             | 1999                           | 2.48                     |\n| Mario Party 3             | 2000                           | 1.91                     |\n| Mario Party 4             | 2002                           | 2.21                     |\n| Mario Party 5             | 2003                           | 2.0                      |\n| Mario Party 6             | 2004                           | 1.65                     |\n| Mario Party 7             | 2005                           | 1.86                     |\n| Mario Party DS            | 2007                           | 9.31                     |\n| Mario Party 8             | 2007                           | 8.85                     |\n| Mario Party 9             | 2012                           | 2.73                     |\n| Mario Party: Island Tour  | 2013                           | 1.14                     |\n| Mario Party 10            | 2015                           | 2.21                     |\n| Super Mario Party         | 2018                           | 6.4                      |\n+---------------------------+--------------------------------+--------------------------+<\/strong>\n13 rows selected\n0: jdbc:hive2:\/\/><\/font>\n\n<\/pre>\n
      Next steps<\/h3>\n
      There are lots more things that Hive can do with Kafka. For example, I’ve only been using it as a consumer and haven’t even started getting Hive queries to produce to my topics.<\/p>\n
      But this was a good first intro to the kinds of thing that Hive can enable.<\/p>\n","protected":false},"excerpt":{"rendered":"
      Apache Hive is open source data warehouse software built on top of Hadoop. It gives you an SQL-like interface to a wide variety of databases, filesystems, and other systems. One of the Hive storage handlers is a Kafka storage handler, which lets you create a Hive “external table” based on a Kafka topic. And once […]<\/p>\n","protected":false},"author":1,"featured_media":3849,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[597,593,582,596,583,584],"class_list":["post-3832","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-code","tag-apachehive","tag-apachekafka","tag-eventstreams","tag-hive","tag-ibmeventstreams","tag-kafka"],"_links":{"self":[{"href":"https:\/\/dalelane.co.uk\/blog\/index.php?rest_route=\/wp\/v2\/posts\/3832","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dalelane.co.uk\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dalelane.co.uk\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dalelane.co.uk\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dalelane.co.uk\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3832"}],"version-history":[{"count":0,"href":"https:\/\/dalelane.co.uk\/blog\/index.php?rest_route=\/wp\/v2\/posts\/3832\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/dalelane.co.uk\/blog\/index.php?rest_route=\/wp\/v2\/media\/3849"}],"wp:attachment":[{"href":"https:\/\/dalelane.co.uk\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3832"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dalelane.co.uk\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3832"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dalelane.co.uk\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3832"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}