Enterprise Java

Hive: Query customer top search query and product views count using Apache Hive

This post covers to use Apache Hive to query the search clicks data stored under Hadoop. We will take examples to generate customer top search query and statistics on total product views.

In continuation to the previous posts on

we already have customer search clicks data gathered using Flume in Hadoop HDFS.

Here will analyze further to use Hive to query the stored data under Hadoop.


Hive allow us to query big data using SQL-like language HiveQL.


Hadoop Data

As shared in last post, we have search clicks data stored under hadoop with the following format “/searchevents/2014/05/15/16/”. The data is stored in separate directory created per hour.

The files are created as:


The data is stored as DataSteam:

{"eventid":"2a4c1e1b-d2c9-4fe2-b38d-9b7d32feb4e0-1399386809743-61","hostedmachinename":"","pageurl":"http://jaibigdata.com/0","customerid":61,"sessionid":"78286f6d-cc1e-489c-85ce-a7de8419d628","querystring":"queryString59","sortorder":"asc","pagenumber":3,"totalhits":32,"hitsshown":9,"createdtimestampinmillis":1399386809743,"clickeddocid":null,"favourite":null,"eventidsuffix":"2a4c1e1b-d2c9-4fe2-b38d-9b7d32feb4e0","filters":[{"code":"searchfacettype_age_level_2","value":"0-12 years"}]}

Spring Data

We will use Spring for Apache Hadoop to run the hive jobs using Spring. To set up hive environment with in your application, use the following configurations:

<hdp:configuration id="hadoopConfiguration"
<hdp:hive-server auto-startup="true" port="10234" min-threads="3" id="hiveServer" configuration-ref="hadoopConfiguration">
<hdp:hive-client-factory id="hiveClientFactory" host="localhost" port="10234">
<hdp:hive-runner id="hiveRunner" run-at-startup="false" hive-client-factory-ref="hiveClientFactory">

Check the spring context file applicationContext-elasticsearch.xml for further details. We will use hiveRunner to run the hive scripts.

All the hive scripts in the application are located under resources hive folder.
The service to run all hive scripts can be found at HiveSearchClicksServiceImpl.java

Set up Database

Let’s setup database to query the data first.


Query Search Events using External Table

We will create an External table search_clicks to read the search events data stored under hadoop.

USE search;
CREATE EXTERNAL TABLE IF NOT EXISTS search_clicks (eventid String, customerid BIGINT, hostedmachinename STRING, pageurl STRING, totalhits INT, querystring STRING, sessionid STRING, sortorder STRING, pagenumber INT, hitsshown INT, clickeddocid STRING, filters ARRAY<STRUCT<code:STRING, value:STRING>>, createdtimestampinmillis BIGINT) PARTITIONED BY (year STRING, month STRING, day STRING, hour STRING) ROW FORMAT SERDE 'org.jai.hive.serde.JSONSerDe' LOCATION 'hdfs:///searchevents/';


Custom SerDe “org.jai.hive.serde.JSONSerDe” is used to map the json data. Check further details on the same JSONSerDe.java

If you are running the queries from Eclipse itself, the dependencies will automatically be resolved. If your are running from hive console, make sure to create a jar file for the class add relevant dependency to hive console before running the hive queries.

#create hive json serde jar
jar cf jaihivejsonserde-1.0.jar org/jai/hive/serde/JSONSerDe.class
# run on hive console to add jar
add jar /opt/hive/lib/jaihivejsonserde-1.0.jar;
# Or add jar path to hive-site.xml file permanently

Create hive partition

We will use hive partitions strategy to read data stored in hadoop under hierarhical locations. Based on above location “/searchevents/2014/05/06/16/”, we will pass following param values (DBNAME=search, TBNAME=search_clicks, YEAR=2014, MONTH=05, DAY=06, HOUR=16).

USE ${hiveconf:DBNAME};
ALTER TABLE ${hiveconf:TBNAME} ADD IF NOT EXISTS PARTITION(year='${hiveconf:YEAR}', month='${hiveconf:MONTH}', day='${hiveconf:DAY}', hour='${hiveconf:HOUR}') LOCATION "hdfs:///searchevents/${hiveconf:YEAR}/${hiveconf:MONTH}/${hiveconf:DAY}/${hiveconf:HOUR}/";

To run the script,

Collection<HiveScript> scripts = new ArrayList<>();
			Map<String, String> args = new HashMap<>();
			args.put("DBNAME", dbName);
			args.put("TBNAME", tbName);
			args.put("YEAR", year);
			args.put("MONTH", month);
			args.put("DAY", day);
			args.put("HOUR", hour);
			HiveScript script = new HiveScript(new ClassPathResource("hive/add_partition_searchevents.q"), args);

In later post, we will cover how to use Oozie coordinator job to automatically create hive parititions for hourly data.

Get all Search Click Events

Get the search events stored in external table search_clicks. Pass folllowing param values (DBNAME=search, TBNAME=search_clicks, YEAR=2014, MONTH=05, DAY=06, HOUR=16).

USE ${hiveconf:DBNAME};
select eventid, customerid, querystring, filters from ${hiveconf:TBNAME} where year='${hiveconf:YEAR}' and month='${hiveconf:MONTH}' and day='${hiveconf:DAY}' and hour='${hiveconf:HOUR}';

This will return you all data under the specified location and will also help you test your custom SerDe.

Find product views in last 30 days

How many time a product has been viewed/clicked in the last n number of days.

Use search;
DROP TABLE IF EXISTS search_productviews;
CREATE TABLE search_productviews(id STRING, productid BIGINT, viewcount INT);
-- product views count in the last 30 days.
INSERT INTO TABLE search_productviews select clickeddocid as id, clickeddocid as productid, count(*) as viewcount from search_clicks where clickeddocid is not null and createdTimeStampInMillis > ((unix_timestamp() * 1000) - 2592000000) group by clickeddocid order by productid;

To run the script,

Collection<HiveScript> scripts = new ArrayList<>();
			HiveScript script = new HiveScript(new ClassPathResource("hive/load-search_productviews-table.q"));

Sample data, select data from “search_productviews” table.

# id, productid, viewcount
61, 61, 15
48, 48, 8
16, 16, 40
85, 85, 7

Find Cutomer top queries in last 30 days

Use search;
DROP TABLE IF EXISTS search_customerquery;
CREATE TABLE search_customerquery(id String, customerid BIGINT, querystring String, querycount INT);
-- customer top query string in the last 30 days
INSERT INTO TABLE search_customerquery select concat(customerid,"_",queryString), customerid, querystring, count(*) as querycount from search_clicks where querystring is not null and customerid is not null and createdTimeStampInMillis > ((unix_timestamp() * 1000) - 2592000000) group by customerid, querystring order by customerid;

Sample data, select data from “search_customerquery” table.

# id, querystring, count, customerid
61_queryString59, queryString59, 5, 61
298_queryString48, queryString48, 3, 298
440_queryString16, queryString16, 1, 440
47_queryString85, queryString85, 1, 47

Analyzing Facets/Filters for Guided Navigation

You can further extend the Hive queries to generate statistics on how the end customers behaving over period of time while using facet/filters to search for the relevant product.

USE search;
-- How many times a particular filter has been clicked.
select count(*) from search_clicks where array_contains(filters, struct("searchfacettype_color_level_2", "Blue"));
-- how many distinct customer clicked the filter
select DISTINCT customerid from search_clicks where array_contains(filters, struct("searchfacettype_color_level_2", "Blue"));
-- top query filters by a customer
select customerid, filters.code, filters.value, count(*) as filtercount from search_clicks group by customerid, filters.code, filters.value order by filtercount DESC limit 100;

The data extraction Hive queries can be scheduled on nightly basis/hourly basis based on the requirements and can be executed using job scheduler like Oozie. The data can further be used to BI analytic or improved customer experience.

In later posts we will cover to analyze the generated data further,

  • Using ElasticSearch Hadoop to index customer top queries and product views data
  • Using Oozie to  schedule coordinated jobs for hive partition and bundle job to index data to ElasticSearch.
  • Using Pig to count total number of unique customers etc.



Jaibeer Malik

Jaibeer is an experienced Java Software Architect and Agile enthusiast with a passion for new technologies, clean code and agile development.
Notify of

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Inline Feedbacks
View all comments
Back to top button