Hive场景模拟SQL编程实例 （销售业绩表）实验

 

Hive场景模拟SQL编程实例

（销售业绩表）实验

实验手册

目录

一. 实验准备................................................................................................................. 2

1.1. 实验概述........................................................................................................ 2

1.2. 实验目的........................................................................................................ 2

1.3. 实验架构........................................................................................................ 3

1.4. 实验准备....................................................................................................... 9

二. 实验步骤............................................................................................................... 12

步骤1：生成分析表.......................................................................................... 12

步骤2：进行DOD变化和YOY变化............................................................. 17

步骤3：导出分析表到本地............................................................................... 19

步骤4：删除分析表：....................................................................................... 19

三. 思考&讨论........................................................................................................... 20

 

一.   实验准备

1.1.         实验概述

使用商业提供的销售数据制作销售业绩日表是数据分析师的一个基本工作内容。本实验详细示范在HIVE实验环境下，整个制作销售业绩日表的步骤与流程。

 

1.2.         实验目的

·        熟悉销售业绩日表的模式,了解报表的基本形式。样表：

	销售额	毛利	交易次数	平均购货篮	顾客人数	人均消费
昨天	¥     49,000.78	¥    19,355.31	149	¥   328.86	147	¥ 333.34
今天	¥     43,782.91	¥    17,600.73	131	¥   334.22	130	¥ 336.79
DOD变化	-10.65%	-9.07%	-12.08%	1.63%	-11.56%	1.04%
去年的今天为止	¥ 17,467,000.63	¥ 6,899,465.25	55673	¥   313.74	34567	¥ 505.31
今年的今天为止	¥ 14,983,000.32	¥ 6,023,166.13	54673	¥   274.05	31980	¥ 468.51
YOY变化	-14.22%	-12.70%	-1.80%	-12.65%	-7.48%	-7.28%

要求时间段：

1.    以天为计算周期，“昨天”：假设昨天是2016年5月31日

2.    以天为计算周期，“今天”：假设今天是2016年6月1日

3.    从今年1月1日到“今天”的计算周期，“今年的今天为止”：2016年1月1日至2016年6月1日。

4.    从去年1月1日到去年的“今天”的计算周期，“去年的今天为止”：2015年1月1日至2015年6月1日。

主要的Metrics：

销售额 (Revenue)：按以上要求的4个时间段，计算全部合格销售的总销售额。

毛利 (Margin)：按以上要求的4个时间段，计算全部合格销售的毛利。毛利为总销售额减去总商品原进价(Cost)。

交易次数 (Number of Transactions)：按要求的4个时间段，计算合格销售的交易次数，一次交易只计算一次。

平均购货蓝 (Average Basket Size)：按以上要求的4个时间段，计算平均每笔合格销售的交易额。

顾客人数 (Number of Customers)：按以上要求的4个时间段，计算共有几位顾客进行了合格销售的交易，一位顾客只计算一次。

人均消费 (Average Spend per Customer)：按以上要求的4个时间段，计算平均每一位顾客的交易额。

注释：

DOD变化：Day over Day Change，以天为单位，比较“昨天”与“今天”的Metrics变化。

YOY变化：Year over Year Change，以今年和去年同期为单位，比较“去年的今天为止”和“今年的今天为止”的Metrics变化。

不合格的销售：退货等。

l  熟练在hive环境下使用SQL语言对数据表进行操作并制作分析表。

l  熟练在hive环境下使用SQL语言对制作的分析表进行相关Metrics的计算

 

1.1.  实验准备

背景知识

Hadoop是用于在分布式环境中存储和处理大数据的开源框架。它包含两个模块，一个是MapReduce，另一个是Hadoop分布式文件系统（HDFS），Hadoop2.0还包括YARN，这个在前面的实验中已经描述。下图为Hadoop的生态系统：

MapReduce：它是一种并行编程模型，用于处理大量结构化，半结构化和非结构化数据。

HDFS：用于分布式存储和处理数据集。

 

Hadoop生态系统包含许多子项目（工具），如Sqoop，Pig和Hive。

Sqoop：用于在HDFS和RDBMS（关系数据库管理系统）之间导入和导出数据。

Pig：用于开发MapReduce操作脚本的过程语言平台。

Hive：用类似SQL类型脚本以进行MapReduce操作的工具。

有多种方法可以执行MapReduce操作：

1.     使用Java MapReduce程序进行结构化，半结构化和非结构化数据的方法（前面的MapReduce实验）。

2.     使用Pig处理结构化和半结构化数据的脚本方法。

3.     使用Hive处理结构化数据的Hive查询语言（HiveQL或HQL）。

Hive是一个用于处理Hadoop中结构化数据的数据仓库基础设施。它位于Hadoop之上，HIVE的官网上说，HIVE2.0以后，其运行的基础将逐步从MapReduce迁移到SPARK，官方文档说HIVE2.0还未可靠，不建议部署。

Hive的架构

 

组成单元	描述
用户界面 （User interfac）	Hive支持的用户界面是Hive Web UI，Hive命令行和Hive HD Insight（在Windows服务器中）。
元数据 Meta Store	选择相应的数据库服务器（默认是Derby，也可以选择其他数据库），将表格，数据库，列的模式或元数据存储在表中，其数据类型和HDFS映射。
HiveQL处理引擎 （HiveQL process engine）	HiveQL类似于SQL，用于在Metastore上查询模式信息。它可以替换用Java编写MapReduce程序，可以为MapReduce作业编写一个查询并处理它。
执行引擎 （Excute engine）	HiveQL处理引擎和MapReduce的结合部分是Hive执行引擎。执行引擎处理查询并生成与用Java编写MapReduce程序相同的结果。
HDFS或HBASE	Hadoop分布式文件系统或HBASE是将数据存储到文件系统中的数据存储技术。

HIVE的大部分命令和SQL类似，常用的命令很容易通过网上搜索找到。

一.   实验步骤

步骤1：生成分析表

以下的操作都是在Hive中运行的，所以先输入命令：hive

然后在提示符“hive>”后输入下面的代码，如图。

1.  创建分析表ad_hoc_report：

1	CREATE TABLE IF NOT EXISTS ad_hoc_report (
2	ID BIGINT,
3	Period STRING,
4	Revenue BIGINT,
5	Margin BIGINT,
6	Number_of_Transactions BIGINT,
7	Average_Basket_Size BIGINT,
8	Number_of_Customers BIGINT,
9	Average_Spend_per_Customer BIGINT
10	);

其中，各主要字段的含义如下：

字段	含义
Period	时间段
Revenue	销售额
Margin	毛利
Number_of_Transactions	交易次数
Average_Basket_Size	平均购物篮
Number_of_Customers	购物人数
Average_Spend_per_Customer	人均消费

2.  使用ecommerce.ecom_fact_sales表格计算“今天”的Metrics，同时将计算出的结果添加进分析表ad_hoc_report:

INSERT INTO TABLE ad_hoc_report SELECT 1 AS ID, 'Today' AS Period, SUM(line_item_total_amount) AS Revenue, SUM((line_item_product_price - line_item_product_cost) * line_item_quantity) AS Margin, COUNT(DISTINCT sales_key) AS Number_of_Transactions, SUM(line_item_total_amount)/COUNT(DISTINCT sales_key) AS Average_Basket_Size, COUNT(DISTINCT customer_key) AS Number_of_Customers, SUM(line_item_total_amount)/COUNT(DISTINCT customer_key) AS Average_Spend_per_Customer FROM (select * from ecom_fact_sales where line_item_quantity > 0) a Left join (select * from ecom_dim_date WHERE calendar_date = "2016-06-01") b On a.date_key = b.date_key Where b.date_key is not null ;

3.  使用ecommerce.ecom_fact_sales表格计算“昨天”的Metrics，同时将计算出的结果添加进分析表ad_hoc_report:

INSERT INTO TABLE ad_hoc_report SELECT 1 AS ID, 'Yesterday' AS Period, SUM(line_item_total_amount) AS Revenue, SUM((line_item_product_price - line_item_product_cost) * line_item_quantity) AS Margin, COUNT(DISTINCT sales_key) AS Number_of_Transactions, SUM(line_item_total_amount)/COUNT(DISTINCT sales_key) AS Average_Basket_Size, COUNT(DISTINCT customer_key) AS Number_of_Customers, SUM(line_item_total_amount)/COUNT(DISTINCT customer_key) AS Average_Spend_per_Customer FROM (select * from ecom_fact_sales where line_item_quantity > 0) a Left join (select * from ecom_dim_date WHERE calendar_date = "2016-05-31") b On a.date_key = b.date_key Where b.date_key is not null ;

4.  使用ecom_fact_sales表格计算“今年的今天为止”的Metrics，同时将计算出的结果添加进分析表ad_hoc_report:

INSERT INTO TABLE ad_hoc_report SELECT 2 AS ID, 'This Year' AS Period, SUM(line_item_total_amount) AS Revenue, SUM((line_item_product_price - line_item_product_cost) * line_item_quantity) AS Margin, COUNT(DISTINCT sales_key) AS Number_of_Transactions, SUM(line_item_total_amount)/COUNT(DISTINCT sales_key) AS Average_Basket_Size, COUNT(DISTINCT customer_key) AS Number_of_Customers, SUM(line_item_total_amount)/COUNT(DISTINCT customer_key) AS Average_Spend_per_Customer FROM (select * from ecom_fact_sales where line_item_quantity > 0) a Left join (select * from ecom_dim_date WHERE calendar_date >= "2016-01-01" And calendar_date <="2016-06-01" ) b On a.date_key = b.date_key Where b.date_key is not null ;

5.  使用ecom_fact_sales表格计算“去年的今天为止”的Metrics，同时将计算出的结果添加进分析表ad_hoc_report:

INSERT INTO TABLE ad_hoc_report SELECT 2 AS ID, 'Last Year' AS Period, SUM(line_item_total_amount) AS Revenue, SUM((line_item_product_price - line_item_product_cost) * line_item_quantity) AS Margin, COUNT(DISTINCT sales_key) AS Number_of_Transactions, SUM(line_item_total_amount)/COUNT(DISTINCT sales_key) AS Average_Basket_Size, COUNT(DISTINCT customer_key) AS Number_of_Customers, SUM(line_item_total_amount)/COUNT(DISTINCT customer_key) AS Average_Spend_per_Customer FROM (select * from ecom_fact_sales where line_item_quantity > 0) a Left join (select * from ecom_dim_date WHERE calendar_date >= "2015-01-01" And calendar_date <="2015-06-01" ) b On a.date_key = b.date_key Where b.date_key is not null ;

通过以上四段代码运行，我们已经在分析表“ad_hoc_report”中收集到“今天”，“昨天”，“今年的今天为止”，“去年的今天为止”这四个时间段的Metrics。

接下来，在步骤2，我们将利用分析表“ad_hoc_report”来计算DOD变化和YOY变化。

 

 

 

步骤2：进行DOD变化和YOY变化

1.    计算DOD变化，使用JOIN来进行计算：

1	SELECT
2	(a.Revenue - b.Revenue)/b.Revenue AS DOD_Revenue,
3	(a.Margin-b.Margin)/b.Margin as DOD_Margin,
4	(a.Number_of_Transactions-b.Number_of_Transactions)/b.Number_of_Transactions AS DOD_Number_of_Transactions,
5	(a.Average_Basket_Size-b.Average_Basket_Size)/b.Average_Basket_Size AS DOD_Average_Basket_Size,
6	(a.Number_of_Customers-b.Number_of_Customers)/b.Number_of_Customers AS DOD_Number_of_Customers,
7	(a.Average_Spend_per_Customer-b.Average_Spend_per_Customer)/b.Average_Spend_per_Customer
8	AS DOD_Average_Spend_per_Customer
9	FROM
10	(SELECT* FROM ad_hoc_report where period = 'Today') a
11	LEFT OUTER JOIN
12	(SELECT* FROM ad_hoc_report where period = 'Yesterday') b
13	ON a.id = b.id
16	;

 

 

 

 

 

2.   计算YOY变化，使用JOIN来进行计算：

1	SELECT
2	(a.Revenue - b.Revenue)/b.Revenue AS YOY_Revenue,
3	(a.Margin-b.Margin)/b.Margin as YOY_Margin,
4	(a.Number_of_Transactions-b.Number_of_Transactions)/b.Number_of_Transactions AS YOY_Number_of_Transactions,
5	(a.Average_Basket_Size-b.Average_Basket_Size)/b.Average_Basket_Size AS YOY_Average_Basket_Size,
6	(a.Number_of_Customers-b.Number_of_Customers)/b.Number_of_Customers AS YOY_Number_of_Customers,
7	(a.Average_Spend_per_Customer-b.Average_Spend_per_Customer)/b.Average_Spend_per_Customer
8	AS YOY_Average_Spend_per_Customer
9	FROM
10	(SELECT* FROM ad_hoc_report where period = 'This Year')a
11	LEFT OUTER JOIN
12	(SELECT* FROM ad_hoc_report where period = 'Last Year')b
13	ON a.id = b.id
16	;

 

通过上面的两段两段运行 ，我们已经收集到DOD和YOY的数据。至此，制作销售业绩日表所需的数据已全部收集完成。有兴趣的同学可以按照实验目的里的表格标准制作表格。

 

步骤3：导出分析表到本地

在Hive中输入命令：exit; 退出

然后输入下面的命令，将结果输出到虚拟机：

hive -e "select * from ad_hoc_report" >> /home/cloudera/sql/report.txt

 

 

 

步骤4：删除分析表：

通过以上操作， 所需数据已经全部收集完成。因为分析表会占用一定的服务器空间，为了释放和节省空间，所以我们需要把分析表“ad_hoc_report”删除：

删除分析表“ad_hoc_report”命令:  Drop Table ad_hoc_report；

本实验已经向您讲解了数据分析师制作销售业绩日表的具体步骤和方法。通过上述实验过程，我们已经了解了如何使用hive在hadoop中执行分析表生成、从销售表中获取数据、计算Metrics、插入新的记录进分析表、以及使用分析表进行进一步DOD及YOY的计算。

 

 

 

二.     思考&讨论

 

Hive和关系型数据库相比有什么区别？

 

参考答案：

1.   Hive和关系型数据库的文件储存系统不同。Hive使用HDFS，而关系型数据库则使用本地文件系统；

2.   Hive和关系型数据库的计算模型不同。Hive使用MapReduce进行运算，而关系型数据库使用自己设计的模型；

3.   Hive和关系型数据库的主要应用场景不同。hive是为海量数据做数据挖掘设计的，实时性差；而关系型数据库是为实时查询的业务进行设计的，实时性好；

4.   Hive和关系型数据库的拓展能力不同。Hive搭建在集群上，可以通过增加集群节点的数量来对存储与计算能力进行拓展，而关系型数据库搭建在单机服务器上，不宜进行拓展；

5.   Hive和关系型数据库的运算能力不同。当数据量较小时，由于Hive需要调用MapReduce框架，查询速度与关系型数据库相比会有一定的延迟，而当数据量较大时Hive的并行计算速度会比关系型数据库快很多。