数据仓库——hive的相关配置和操作

Hive的相关资料和配置文档

点击下载
链接：https://pan.baidu.com/s/1Z4VG7mPBpmW6mWpR_WcyPQ
提取码：7afc

Hive基本操作命令

1. 创建数据库

* create database db_name;
* create database if not exists db_name;//创建一个不存在的数据库final

2. 查看数据库

* show databases;
* show databases like 'f.*'; //选择性查看数据库
* describe database db_name; //查看某一个数据库的详细信息

3. 使用自己已经存在的数据库
   use db_name;
4. 创建表

* create table t_name (....)
* create table t_name as select ... //以查询结果创建表
* create tabale t_name like t_old_name  //克隆表
* create table gds_goods(id int,g_name string,price int,desc string)row format delimited fields terminated by '\t';     //以分隔符创建数据表

5. 查看当前数据库下的所有表
   show tables;
6. 删除一个已经存在的表
   drop table employee;
7. 修改一个表名，重命名
   alter table old_name rename to new_name;
8. 加载数据（在表中导入数据）

* insert into table t_name values(.....)    //插入数据
* insesrt into table t_Name select .... //查询的方式插入数据
* load data local inpath '....' into table t_name;  //导入Linux本地的数据（不覆盖表数据）
* load data local inpath '....' overwrite into table t_name;    //导入Linux本地的数据并且覆盖表数据
* load data inpath '....' into table t_name     //导入hdfs的数据（不覆盖表数据）
* load data inpath '....' overwrite into table t_name   //导入hdfs的数据并且覆盖表数据
* load data inpath  '/gds_goods' INTO TABLE gds_goods;（例子）

9. 导出数据(从表中导出数据，文本)

* into data local direction '' from t_name      //导出到Linux本地
* into data direction '' from t_name        //导出到hdfs
* insert overwrite local directory "/home/hadoop/hive_data/sale.txt" row format delimited fields terminated by '\t' select * from kongtao.sale;（例子）

10. 建立分区表

* create table t_name (....) partitioned by (....)
* create table sale(id int,name String,price int)partitioned by (date int) row format delimited fields terminated by '\t';（例子）
* 分别向分区导入数据
* load data local inpath '/home/hadoop/day1' into table kongtao.sale partition (udate='20200401’);
* load data local inpath '/home/hadoop/day2' into table kongtao.sale partition (udate='20200402’);

11. 在 hive 环境中，查看当前 linux 所在目录
    !pwd
12. 在 hive 环境中，查看 hdfs 的根目录的内容
    dfs -ls /
13. 使用 linux 命令，运行一种 hive 的 sql 语句
    hive -e 'select * from db_name.tb_name'
14. 使用 linux 命令，运行一个存放 hive 的 sql 语句文件
    hive -f hive.sh //hive.sh中放了hql语句，并且一行只放一句
15. 设置严格模式
    set hive.mapred.mode=strict;
16. 查询按部门名称汇总，统计每个部门名称的员工的最高工资，工资总额，员工数 同时，只列出平均工资大于 3000 的部门 按部门名称反序显示
    select max(money),sum(money),count(*),demp from emp group by demp having avg(money)>3000 order by demp desc;

查询操作可以参考MySql，HQL语句和MySql类似

记一次电商数据指标计算作业

用到的数据表（数据量很大，几十万条一张表）

点击下载
链接：https://pan.baidu.com/s/1beYFeiuj_cAh-3i9PIGeKg
提取码：lf75

指标计算

建立访客表
create table fangke(ip string,ftime string,secondTime string,uid string,tel string,aid string,link string,event string,resource string,dfree float)row format delimited fields terminated by ',';
load data local inpath '/home/hadoop/桌面/CSV/访客表.csv' overwrite into table fangke;

建立订单表
create table dingdan(id string,dtime string,mid string,bookid string,mip string,address string,tel string,clickres string,dfree float,overtime string,orderStatus string)row format delimited fields terminated by ',';

load data local inpath '/home/hadoop/桌面/CSV/订单表.csv' overwrite into table dingdan;

建立商品类目表
create table shop(sid string,sname string,author string,editor string,etime string,idbm string,sellPrice float,price float,sCount int,fAddress string)row format delimited fields terminated by ',';

load data local inpath '/home/hadoop/桌面/CSV/商品类目表.csv' overwrite into table shop;

1.独立访客数(派生表必须加别名）
  指访问电商网站的不重复用户。指访问某个站点的不同IP地址的人数，多次进入不重复统计。

select count(*) from (select count(*) from fangke group by ip) as ip;
select count(*) from (select count(*) from fangke group by ip) group by resource;


2.页面访问数
  页面浏览量，用户每一次对电商网站或着移动电商应用中的每个网页访问均被记录一次，用户对同一页面的多次访问，访问量累计
select count(*) from fangke;
select count(*) from fangke group by resource;



3.人均页面访问数
  页面访问数（PV）／独立访客数，指平均每个用户浏览的页面数，该指标反映的是网站访问粘性，要对每一个网站分开计算
select resource,count(resource),round(count(ip)/count(distinct ip),2) from fangke group by resource;



4.总订单数量
  完成下单操作的订单总量
select count(*) dingdan from fangke where event = 'chargeRequest';


5.访问到下单转化率
  下单数与网站访问次数之比，总下单数/页面访问数。
with c1
as (select count(*) pv from fangke),
c2
as (select count(*) dingdan from fangke where event=’chargeRequest’)
select b.dingdan/a.pv from c1 a,c2 b;


6.成交金额(GMV)
生成订单号的所有订单总和，包括付款和未付款两部分
select sum(price) from (select * from dingdan where orderStatus='支付成功' or orderStatus='待支付') a join shop on a.bookid=sid;


7.客单价
订单金额与订单数量的比值。反映平均每一件商品的平均单价
商品销售额(GMV)/顾客量  22.54
with c1
as (select count(*) uv from dingdan where orderStatus='支付成功'),
c2
as (select sum(price) zong from (select * from dingdan where orderStatus='支付成功') a join shop on a.bookid=sid)
select b.zong/a.uv from c1 a,c2 b;


8.销售毛利（毛利最高的时间商品） 计算规则：默认商品的成本默认是商品定价的40%（仅为指标实现，不具备现实普适性）
销售收入减去成本的差值，并没有减去运营成本（比如管理费用，营业费用）
商品售价总额-生产成本
with c1 
as (select sum(price) zong,sum(sellPrice) ss from (select * from dingdan where orderStatus='支付成功') a join shop on a.bookid=sid)
select a.zong-(a.ss*0.4) from c1 a;

9.毛利率（默认规则：(商品售价-商品定价*40%-渠道单点费用)）
衡量电商企业盈利能力的指标，是销售毛利与销售收入的比值。
with c1 
as (select sum(price) zong,sum(sellPrice) ss from (select * from dingdan where orderStatus='支付成功') a join shop on a.bookid=sid),
c2
as (select sum(dfree) df from fangke)
select (a.zong-(a.ss*0.4)-b.df)/a.zong from c1 a,c2 b;