1、大數(shù)據(jù)競(jìng)賽培訓(xùn)之?dāng)?shù)據(jù)分析,2018,講師：趙利平 日期：2018年11月11日,目錄,CONTENTS,原理介紹,01,原理介紹,本次數(shù)據(jù)分析是采用Hive進(jìn)行的，Hive是建立在 Hadoop 上的數(shù)據(jù)倉(cāng)庫(kù)基礎(chǔ)構(gòu)架。它提供了一系列的工具，可以用來(lái)進(jìn)行數(shù)據(jù)提取轉(zhuǎn)化加載（ETL），這是一種可以存儲(chǔ)、查詢和分析存儲(chǔ)在Hadoop 中的大規(guī)模數(shù)據(jù)的機(jī)制。 Hive 定義了簡(jiǎn)單的類 SQL 查詢語(yǔ)言，稱為 HQL，它允許熟悉 SQL 的用戶查詢數(shù)據(jù)。同時(shí)，這個(gè)語(yǔ)言也允許熟悉 MapReduce 開(kāi)發(fā)者的開(kāi)發(fā)自定義的 mapper 和 reducer 來(lái)處理內(nèi)建的 mapper 和 reducer 無(wú)法

2、完成的復(fù)雜的分析工作。,Hive的Sql語(yǔ)句,HDFS,解釋為mapreduce,學(xué)習(xí)目標(biāo),02,掌握將本地文件上傳至hdfs指定路徑技能,掌握創(chuàng)建hive表，并將本地?cái)?shù)據(jù)信息導(dǎo)入技能,掌握創(chuàng)建表獲取指定格式、指定信息技能,掌握轉(zhuǎn)化率計(jì)算等函數(shù),學(xué)習(xí)目標(biāo),學(xué)習(xí)目標(biāo),前期準(zhǔn)備,03,3.1hive基本操作,進(jìn)入hive安裝目錄 cd /usr/hive/apache-hive-2.1.1-bin/ 啟動(dòng)hive client(master上) bin/hive 查看數(shù)據(jù)庫(kù)列表 show databases;,3.1hive基本操作,建數(shù)據(jù)庫(kù) create database hongya; /創(chuàng)建

3、數(shù)據(jù)庫(kù)hongya show databases; /查看數(shù)據(jù)庫(kù)，發(fā)現(xiàn)有庫(kù)hongya use hongya; /使用hongya數(shù)據(jù)庫(kù),3.2查看樣例數(shù)據(jù),原始數(shù)據(jù)表：train_format2.csv數(shù)據(jù),中間表1：初步創(chuàng)建表將activity_log中以#分割的數(shù)據(jù)拆成多行數(shù)據(jù),中間表2:將拆成行的數(shù)據(jù)activity_log中以：分割的數(shù)據(jù)拆分成元素,3.3hive操作-創(chuàng)建表,創(chuàng)建比賽數(shù)據(jù)表match_data，要求表結(jié)構(gòu)與提供的數(shù)據(jù)結(jié)構(gòu)一樣，信息包含用戶iduser_id、用戶性別gender、商家唯一id merchant_id、購(gòu)物者標(biāo)簽label，均為為int類型，用戶與商家

4、交互信息activity_log為varchar類型。,create table match_data( user_id int, age_range int, gender int, merchant_id int, label int, activity_log varchar(1000) ) row format delimited fields terminated by ,;,3.4hive基本操作導(dǎo)入數(shù)據(jù),將root下的train_format2.csv數(shù)據(jù)導(dǎo)入到創(chuàng)建的match_data表中,load data local inpath /root/train_format2.c

5、sv overwrite into table match_data;,查看match_data數(shù)據(jù),select * from match_data limit 100;,數(shù)據(jù)分析,04,4.1數(shù)據(jù)分析中間表,中間表是數(shù)據(jù)庫(kù)中專門存放中間計(jì)算結(jié)果的數(shù)據(jù)表。報(bào)表系統(tǒng)中的中間表是普遍存在的。分析接下來(lái)的四個(gè)問(wèn)題： 創(chuàng)建click表，寫入商品點(diǎn)擊次數(shù)top100數(shù)據(jù) 創(chuàng)建add_to_cart表，寫入商品被加入購(gòu)物車次數(shù)top100數(shù)據(jù) 創(chuàng)建collect表，寫入商品被收藏次數(shù)top100數(shù)據(jù) 創(chuàng)建emption表，寫入商品被購(gòu)買次數(shù)top100數(shù)據(jù),4.1數(shù)據(jù)分析中間表,CREATE TABLE

6、 RESULT AS /創(chuàng)建RESULT表并獲取match_data的USER_ID, ITEM_ID, BRAND_ID, ATIION_TYPE SELECT USER_ID, SPLIT(LOG_SPLIT,:)0 AS ITEM_ID, /將拆成行的數(shù)據(jù)以：為分隔符篩選字符串第0位 SPLIT(LOG_SPLIT,:)2 AS BRAND_ID, /將拆成行的數(shù)據(jù)以：為分隔符篩選字符串第2位 SPLIT(LOG_SPLIT,:)4 AS ATIION_TYPE /將拆成行的數(shù)據(jù)以：為分隔符篩選字符串第4位 FROM (SELECT USER_ID,LOG_SPLIT FROM matc

7、h_data LATERAL VIEW EXPLODE(SPLIT(ACTIVITY_LOG,#) ACTIVITY_LOG AS LOG_SPLIT ) T1; /lateral view和split, explode一起使用，以#為分隔符將一列數(shù)據(jù)拆成多行數(shù)據(jù),4.1數(shù)據(jù)分析中間表,select * from RESULT limit 100;/查看前100行數(shù)據(jù),查看表RESULT中前100行數(shù)據(jù),4.2.1數(shù)據(jù)分析創(chuàng)建點(diǎn)擊量表,創(chuàng)建click表，寫入商品點(diǎn)擊次數(shù)top100數(shù)據(jù) 分析：商品id、點(diǎn)擊、top100 表：商品id、點(diǎn)擊量統(tǒng)計(jì)、100條數(shù)據(jù),CREATE TABLE CLI

8、CK AS /創(chuàng)建表click，代表點(diǎn)擊量 SELECT ITEM_ID,COUNT(1) COUNT_1/對(duì)所有的行ITEM_ID相同的進(jìn)行統(tǒng)計(jì) FROM RESULT WHERE ATIION_TYPE = 0/限定條件ATIION_TYPE = 0 GROUP BY ITEM_ID/ group by操作表示按照ITEM_ID字段的值進(jìn)行分組，有相同的ITEM_ID值放到一起 ORDER BY COUNT_1 DESC/按照統(tǒng)計(jì)結(jié)果全局降序排序 LIMIT 100;/限制數(shù)據(jù)100行,4.2.1數(shù)據(jù)分析創(chuàng)建點(diǎn)擊量表,select * from click;/ 查看click表中所有數(shù)據(jù),

9、查看click表中所有數(shù)據(jù)，第一列代表商品id，第二列代表點(diǎn)擊量,4.2.2數(shù)據(jù)分析創(chuàng)建加入購(gòu)物車表,創(chuàng)建ADD_TO_CART表，寫入商品點(diǎn)擊次數(shù)top100數(shù)據(jù) 分析：商品id、加入購(gòu)物車、top100 表：商品id、加入購(gòu)物車量統(tǒng)計(jì)、100條數(shù)據(jù),CREATE TABLE ADD_TO_CART AS /創(chuàng)建表ADD_TO_CART，代表加入購(gòu)物車量 SELECT ITEM_ID,COUNT(1) COUNT_1/對(duì)所有的行ITEM_ID相同的進(jìn)行統(tǒng)計(jì) FROM RESULT WHERE ATIION_TYPE = 1 /限定條件ATIION_TYPE = 1 GROUP BY ITEM

10、_ID / group by操作表示按照ITEM_ID字段的值進(jìn)行分組，有相同的ITEM_ID值放到一起 ORDER BY COUNT_1 DESC /按照統(tǒng)計(jì)結(jié)果全局降序排序 LIMIT 100 ; /限制數(shù)據(jù)100行,4.2.2數(shù)據(jù)分析創(chuàng)建加入購(gòu)物車表,查看ADD_TO_CART表中所有數(shù)據(jù)，第一列代表商品id，第二列代表統(tǒng)計(jì)量,select * from ADD_TO_CART；,4.2.3數(shù)據(jù)分析創(chuàng)建收藏量表,創(chuàng)建click表，寫入商品收藏次數(shù)top100數(shù)據(jù) 分析：商品id、收藏、top100 表：商品id、收藏量統(tǒng)計(jì)、100條數(shù)據(jù),CREATE TABLE COLLECT AS /

11、創(chuàng)建表COLLECT，代表收藏量 SELECT ITEM_ID,COUNT(1) COUNT_1 /對(duì)所有的行ITEM_ID相同的進(jìn)行統(tǒng)計(jì) FROM RESULT WHERE ATIION_TYPE = 3 /限定條件ATIION_TYPE = 3 GROUP BY ITEM_ID / group by操作表示按照ITEM_ID字段的值進(jìn)行分組，有相同的ITEM_ID值放到一起 ORDER BY COUNT_1 DESC /按照統(tǒng)計(jì)結(jié)果全局降序排序 LIMIT 100; /限制數(shù)據(jù)100行,4.2.4數(shù)據(jù)分析創(chuàng)建購(gòu)買量表,創(chuàng)建EMPTION表，寫入商品購(gòu)買次數(shù)top100數(shù)據(jù) 分析：商品id、

12、購(gòu)買、top100 表：商品id、購(gòu)買量統(tǒng)計(jì)、100條數(shù)據(jù),CREATE TABLE EMPTION AS /創(chuàng)建表EMPTION，代表購(gòu)買量 SELECT ITEM_ID,COUNT(1) COUNT_1 /對(duì)所有的行ITEM_ID相同的進(jìn)行統(tǒng)計(jì) FROM RESULT WHERE ATIION_TYPE = 2 /限定條件ATIION_TYPE = 2 GROUP BY ITEM_ID / group by操作表示按照ITEM_ID字段的值進(jìn)行分組，有相同的ITEM_ID值放到一起 ORDER BY COUNT_1 DESC /按照統(tǒng)計(jì)結(jié)果全局降序排序 LIMIT 100; /限制數(shù)據(jù)10

13、0行,4.2.4數(shù)據(jù)分析創(chuàng)建購(gòu)買量表,查看表EMPTION中所有數(shù)據(jù)，第一列表示商品id，第二列代表購(gòu)買量,select * from EMPTION;,4.3.1數(shù)據(jù)分析點(diǎn)擊購(gòu)買轉(zhuǎn)化率計(jì)算,CREATE TABLE CLICK_EMP AS /創(chuàng)建CLICK_EMP表 SELECT ITEM_ID,/商品id SUM(IF(ATIION_TYPE = 0,1,0)/COUNT(1) CLICK_EMP_RATE/點(diǎn)擊總和除以該ITEM_ID的購(gòu)買總和 FROM RESULT T1 GROUP BY ITEM_ID / group by操作表示按照ITEM_ID字段的值進(jìn)行分組，有相同的ITE

14、M_ID值放到一起 ORDER BY CLICK_EMP_RATE DESC;/按照點(diǎn)擊購(gòu)買轉(zhuǎn)化率降序排序,創(chuàng)建商品點(diǎn)擊購(gòu)買轉(zhuǎn)化率表，并按照降序排列 分析：商品id、點(diǎn)擊購(gòu)買轉(zhuǎn)化率、降序排列 表：商品id、轉(zhuǎn)化率、降序,4.3.1數(shù)據(jù)分析點(diǎn)擊購(gòu)買轉(zhuǎn)化率計(jì)算,查看表CLICK_EMP中前100行數(shù)據(jù)，第一列數(shù)據(jù)為item_id，第二列數(shù)據(jù)為點(diǎn)擊購(gòu)買轉(zhuǎn)化率。,select * from CLICK_EMP limit 100,4.3.2數(shù)據(jù)分析加入購(gòu)物車轉(zhuǎn)化率計(jì)算,CREATE TABLE ADD_EMP AS /創(chuàng)建ADD_EMP P表 SELECT ITEM_ID,SUM(IF(ATIION_

15、TYPE = 1,1,0)/COUNT(1) CLICK_EMP_RATE /加入購(gòu)物車總和除以該ITEM_ID的購(gòu)買總和 FROM RESULT T1 GROUP BY ITEM_ID / group by操作表示按照ITEM_ID字段的值進(jìn)行分組，有相同的ITEM_ID值放到一起 ORDER BY CLICK_EMP_RATE DESC; /按照點(diǎn)擊購(gòu)買轉(zhuǎn)化率降序排序,創(chuàng)建商品加入購(gòu)物車購(gòu)買轉(zhuǎn)化率表，并按照降序排列 分析：商品id、加入購(gòu)物車購(gòu)買轉(zhuǎn)化率、降序排列 表：商品id、轉(zhuǎn)化率、降序,4.3.2數(shù)據(jù)分析加入購(gòu)物車轉(zhuǎn)化率計(jì)算,查看表ADD_EMP中前100行數(shù)據(jù)， 第一列數(shù)據(jù)為item_id，第二列數(shù)據(jù)為加入 購(gòu)物車購(gòu)買轉(zhuǎn)化率。,select * from ADD_EMP limit 100;,4.3.3數(shù)據(jù)分析收藏購(gòu)買轉(zhuǎn)化率計(jì)算,CREATE TABLE COLLECT_EMP AS /創(chuàng)建COLLECT_EMP表 SELECT ITEM_ID,SUM(IF(ATII