HiveSQL电商分析案例30题

 

问题导读：

1、如何通过UDF自定义 MD5加密函数 ，对地址、邮箱等信息进行加密？

2、如何对表内容进行检查？

3、如何进行数据分析？

 

一、前言

Hive 学习过程中的一个练习项目，如果不妥的地方或者更好的建议，欢迎指出！我们主要进行一下一些练习：

•     数据结构

•     数据清洗

•     基于Hive的数据分析

二、项目需求

首先和大家讲一下这个项目的需求：

 

「对某零售企业最近1年门店收集的数据进行数据分析」

•     潜在客户画像

•     用户消费统计

•     门店的资源利用率

•     消费的特征人群定位

•     数据的可视化展现

三、数据结构

本次练习一共用到四张表，如下：

Customer表

 

Transaction表

 

Store表

 

Review表

 

 

四、项目实战

 

「Create HDFS Folder」

1. hdfs dfs -mkdir -p /tmp/shopping/data/customer

2. hdfs dfs -mkdir -p /tmp/shopping/data/transaction

3. hdfs dfs -mkdir -p /tmp/shopping/data/store

4. hdfs dfs -mkdir -p /tmp/shopping/data/review

「Upload the file to HDFS」

1. hdfs dfs -put /opt/soft/data/customer_details.csv /tmp/shopping/data/customer/

2. hdfs dfs -put /opt/soft/data/transaction_details.csv /tmp/shopping/data/transaction/

3. hdfs dfs -put /opt/soft/data/store_details.csv /tmp/shopping/data/store/

4. hdfs dfs -put /opt/soft/data/store_review.csv /tmp/shopping/data/review/

「Create database」

1. drop database if exists shopping cascade
   
   create database shopping

    

「Use database」

1. use shopping

「Create external table」

 

「创建四张对应的外部表，也就是本次项目中的近源表。」

1. create external table if not exists ext_customer_details(
   
   customer_id string,
   
   first_name string,
   
   last_name string,
   
   email string,
   
   gender string,
   
   address string,
   
   country string,
   
   language string,
   
   job string,
   
   credit_type string,
   
   credit_no string
   
   )
   
   row format delimited fields terminated by ','
   
   location '/tmp/shopping/data/customer/'
   
   tblproperties('skip.header.line.count'='1')
   
   create external table if not exists ext_transaction_details(
   
   transaction_id string,
   
   customer_id string,
   
   store_id string,
   
   price double,
   
   product string,
   
   buydate string,
   
   buytime string
   
   )
   
   row format delimited fields terminated by ','
   
   location '/tmp/shopping/data/transaction'
   
   tblproperties('skip.header.line.count'='1')
   
   create external table if not exists ext_store_details(
   
   store_id string,
   
   store_name string,
   
   employee_number int
   
   )
   
   row format delimited fields terminated by ','
   
   location '/tmp/shopping/data/store/'
   
   tblproperties('skip.header.line.count'='1')
   
   create external table if not exists ext_store_review(
   
   transaction_id string,
   
   store_id string,
   
   review_score int
   
   )
   
   row format delimited fields terminated by ','
   
   location '/tmp/shopping/data/review'
   
   tblproperties('skip.header.line.count'='1')

    

2.  

 

通过UDF自定义 MD5加密函数

 

「Create MD5 encryption function」

 

这里通过UDF自定义 MD5加密函数 ，对地址、邮箱等信息进行加密。

1. -- md5 udf自定义加密函数
   
   --add jar /opt/soft/data/md5.jar
   
   --create temporary function md5 as 'com.shopping.services.Encryption'
   
   --select md5('abc')
   
   --drop temporary function encrymd5

    

「Clean and Mask customer_details 创建明细表」

1. create table if not exists customer_details
   
   as select customer_id,first_name,last_name,md5(email) email,gender,md5(address) address,country,job,credit_type,md5(credit_no)
   
   from ext_customer_details

    

对表内容进行检查，为数据清洗做准备

 

「Check ext_transaction_d