In this video we are going to use Big
Data SQL to access a kafka stream of
current bike activity at NYC Citi Bikes
in order to perform some real-time
analysis. A kafka stream enables you to
view data that is being generated in
real-time. To access this real-time data
with Big Data SQL, you need to create
a few objects that support kafka. But
first, we'll create a kafka producer to
generate events for our bike activity. In
this case, we want to see which bikes are
coming and going from the various
stations, and when these events occur.
Then, we'll create a kafka consumer so
that we can preview the events. We run a
shell script named start-kafka-producer.
This script creates a producer, and the
producer publishes a kafka topic named
bike_trip_stream. The producer simply
generates events that are written to
this kafka topic. To get the output, we
create a consumer of the kafka stream.
The Kafka screen output is in JSON
format. So every time a bike is picked up
or dropped off, an event is generated, and
the output for the event is recorded in
a JSON document, which we also call the
kafka topic. Let's stop the consumer that
we just created. Next we'll create a
couple of Big Data SQL objects that
enable access to this Kafka stream.
Now that we have a working kafka
producer, we first create a Hive table
over the Kafka stream. In the code,
Oracle provides a Kafka driver used in
the SERDE option -- and a storage handler
for kafka streams in Hadoop -- used in the
STORED BY option. Now this storage
handler enables Oracle to query Kafka
streams through Hive.
So, with this CREATE EXTERNAL TABLE
statement, we create a Hive metadata
table over the kafka stream. Then we
reference a Kafka topic that the
producer generates in the
TBLPROPERTIES clause. Now that we have a Hive metadata table over the kafka
stream, we simply create a Big Data
SQL table, an external table, over the
Hive table. This statement uses the same
ORACLE_HIVE driver shown in the second
video in this series. So this is the
external table that makes the kafka
stream activity available to Oracle
Database, enabling Big Data SQL to
query the kafka stream directly. Now this
table can also be used by other
processes in addition to Big Data SQL.
Now using Oracle database JSON parsing
features we query the kafka stream using
Oracle Big Data SQL. The source table
is the Big Data external table that was
created over the Oracle Hive table. The
query results show bike inventory
patterns at different stations during
the day, straight from the kafka stream.
The start time, stop time, and duration of
the bike ride has been parsed out of the
JSON document, per the query. And as
you've learne,d you can join Big Data and
Oracle data in your Big Data SQL
queries. Here we combine the kafka stream
data with the station table in Oracle.
The query result is presented in this
stacked bar graph. We have a lot more
bikes that are ending up at the Grove
Street Station than originated from the
same station. So there's a surplus of
bikes at Grove Street at the end of the
day. So here we've combined dimensional
data from the Oracle database with real-time
data from a kafka stream to provide
some helpful analysis. Learn more at
cloud.oracle.com/bigdata