Abstract

Machine learning and supervised learning at scale is a common task that can be addressed with Apache Hive by leveraging Hivemall features and user-defined functions (UDFs) for HiveQL, which is strongly optimized for machine learning (ML) and data science. For example, logistic regression models, one of the basic tenants of ML, can also be easily built with Hivemall. The following example should explain the basic usage of Hivemall with examples of supervised learning of a simple regressor.

Setup the environment

-> HDP 3.0.1 Sandbox - Hivemall 0.5.2 see Machine Learning with Apache Hive SQL using Hivemall

Explore the Training Data

Database: foodmart - Table: sales_fact _dec_1998 and customer

Create Table customer_training as
select s1.customer_id, c1.country, c1.gender, c1.member_card,c1.num_cars_owned, count(s1.product_id) as purchase_cnt
from sales_fact_dec_1998 s1
join customer c1
WHERE s1.customer_id = c1.customer_id
group by s1.customer_id, c1.country, c1.gender, c1.member_card, c1.num_cars_owned;


Customer Feature Representation

Create the feature vector for the customer that relevant i.e. country, gender, member_card and num_cars_owned. Hivemall can have quantitive (mumeric) features like num_cars_owned or categorical (char/strings) as gender or country.

create table customer_features (customer_id int, features array<string>, purchase_cnt int);


insert overwrite table customer_features
select
customer_id,
hivemall.array_concat(
hivemall.quantitative_features( array("num_cars_owned"),num_cars_owned ) ,
hivemall.categorical_features( array("country", "gender","member_card"), country, gender, member_card )
) as features,
sum(purchase_cnt)
from
customer_training
group by customer_id,
hivemall.array_concat(
hivemall.quantitative_features( array("num_cars_owned"),num_cars_owned ) ,
hivemall.categorical_features( array("country", "gender","member_card"), country, gender, member_card )
)
;


The customer_features should look like the following:

Training

Now that we have the customers with a defined set of features, the next step is to create the regressor. This requires us to specify an appropriate -loss_function. As this is squared for train_regressor, this means this query builds a simple linear regressor with the squared loss.

create table if not exists customer_regressor as
select
hivemall.train_regressor(
features, -- feature vector
purchase_cnt, -- target value
'-loss_function squared -optimizer AdaGrad -regularization l2' -- hyper-parameters
) as (feature, weight)
from
customer_features
;

Table: Customer_Regression table

Prediction

Now we can compute the prediction for the number of purchases of a new customer.

create table if not exists customer_new as
select 1 as id, array("num_cars_owned:3.0","country#USA","gender#F","member_card#Silver") as features
;
with features_exploded as (
select
id,
hivemall.extract_feature( fv ) as feature,
hivemall.extract_weight( fv ) as value
from customer_new t1 LATERAL VIEW explode(features) t2 as fv
)
select
t1.id,
sum(p1.weight * t1.value) as predicted_num_purchases
from
features_exploded t1
LEFT OUTER JOIN customer_regressor p1 ON (t1.feature = p1.feature)
group by
t1.id
;

PREDICTED_NUM_PURCHASES
2.9394720904529095