Hive----hive数据的导入导出及分区.greatest.case.with as

数据的导入
1 location
2 load
3 insert
4 create
5 put
6 import导入指定export的数据
create table tb_log2 like tb_log ; – 根据已有的表结构建表

在shell客户端导入hdfs上的数据:
export table tb_log to
‘/user/hive/warehouse/export/tb_log’ ;

import table tb_log2 from
‘/user/hive/warehouse/export/tb_log’;

数据的导出
1 export tb_name to HDFS_PATH --在客户端导出数据到hdfs
2 如果数据是普通的文本数据 在shell客户端使用get下载数据
hdfs dfs -get /tb_dir/file
hive> dfs -get /文件
3 1) hive -e “use default ; select * from tb_log;” – 不开启终端 执行SQL语句
4 sqoop dataX 数据迁移工具

5 insert
insert overwrite local directory ‘/user_data/’
select * from tb_user; – 将查询的结构数据保存在本地的目录中

insert overwrite directory ‘/user_data/’
select * from tb_user; – 将查询的结构数据保存在HDFS目录中

6 insert into res_table
select count(1) , avg(rate) from tb_name2 --普通表导入数据

分区表
将表的数据以查询维度为依据分文件夹管理 , 当根据这个维度查询的时候减少数据的检索范围
比如有一个log表 所有的日志数据在log表目录下 ,假如想查20201130日的数据 , 只能遍历所有的数据
有了分区表以后 数据就可以以日期为维度 为文件夹存储对应日期的数据 ,
假如想查20201130日的数据直接从对应的文件夹下读取数据

1.静态分区
文件中存储的是指定规则的数据 比如 a.log中存储的就是20201130的数据
直接创建一个分区叫20201130 将数据直接load到目录下
– 静态分区
1)前提有静态数据
2)创建分区表
3)将静态数据导入到指定的分区中

创建一级分区表
create table tb_partition_log(
log_id string ,
url string ,
ct string
)
partitioned  by(dt string) -- 指定分区字段
row format delimited fields terminated by ',' ;
将静态数据导入到指定的分区中
load  data  local  inpath  "/data/log/20201128.log"  into  table tb_partition_log partition(dt='20201128') ;
load  data  local  inpath  "/data/log/20201129.log"  into  table tb_partition_log partition(dt='20201129') ;
load  data  local  inpath  "/data/log/20201130.log"  into  table tb_partition_log partition(dt='20201130') ;

创建二级分区表
create table tb_partition_log2(
log_id string ,
url string ,
ct string
)
partitioned  by(m string , d string) -- 指定分区字段  2个
row format delimited fields terminated by ',' ;
load  data  local  inpath  "/doit19/20201128.log" into  table tb_partition_log2 partition(m='202011' , d='28') ;
load  data  local  inpath  "/doit19/20201129.log" into  table tb_partition_log2 partition(m='202011' , d='29') ;
load  data  local  inpath  "/doit19/20201130.log" into  table tb_partition_log2 partition(m='202011' , d='30') ;
load  data  local  inpath  "/doit19/20201010.log" into  table tb_partition_log2 partition(m='202010' , d='10') ;
load  data  local  inpath  "/doit19/20201011.log" into  table tb_partition_log2 partition(m='202010' , d='11') ;

2.动态分区
比如a.log中既有20201130数据 又有20201129数据 还有20191111的数据
只能根据日期字段的值创建分区 将对应的数据分配指定的分区

1)建普通表
create  table  tb_user_log(
uid int ,
name string ,
city string 
)
row format delimited fields terminated by ',' ;
load data local inpath "/doit19/user_log/" into  table tb_user_log ;

2) 导入数据
user.log
1,lisi,Shanghai
2,ycy,Shanghai
3,ym,Beijing
4,Yangzi,Beijing
5,Yangguo,shenzhen
6,mayun,shenzhen
7,mbg,Shanghai
8,marong,shenzhen

3) 分区表
CREATE  TABLE tb_dynamic_partition_user_log(
id int ,
name string ,
city string 
)
partitioned by(p_city string) ;

4) 开启动态分区支持
set hive.exec.dynamic.partition=true ;
set hive.exec.dynamic.partition.mode=nonstrick;

5) 通过select insert的方式导入数据
insert into  tb_dynamic_partition_user_log  partition(p_city)
select uid , name  , city , city as p_city   from tb_user_log ;

修改分区表

删除分区
alter table tb_partition_log drop partition(dt='20201111');
添加分区
alter table tb_partition_log add partition(dt='20201111');
添加多个分区
alter table tb_partition_log add partition(dt='20201112')  partition(dt='20201113')
展示表的所有分区
show partitions tb_partition_log

数据库的操作

show database: 查看所有数据库名
user db_name: 使用db_name数据库
drop database db_name cascade: 强制删除数据库
select current_database(): 查看当前正在使用的表
select current_date: 查看当前日期
select current_timestamp: 查看当前时间戳
show functions: 查看系统支持的函数
create database db_name: 创建数据库
alter database db_name set dbproperties('auther'='tdz','age'='18'): 设置表的属性
desc database extended db_name: 查看数据库的详细属性

with as语法(主逻辑比较清晰)

--部门名称和总薪资
with t as(
select
deptno ,
sum(sal+nvl(comm,0)) as he    --nvl(不为null返回comm,为null返回0)
from
emp
group by
deptno
)
select
dept.deptno,
dept.dname,
t.he
from
t
right join
dept
on
t.deptno=dept.deptno;

求每个部门中每个岗位薪资最高的员工姓名.工资,部门地址
with m as(
select
a.*,
dept.dname,
tb_local.loc_name
from

(select
deptno,
job,
max(sal+nvl(comm,0)) as sal_max
from
emp
group by deptno,job) a

join
dept
join
tb_local
on
a.deptno=dept.deptno
and
dept.loc=tb_local.loc )

select
m.*,
emp.ename
from
emp
join
m
on
m.deptno=emp.deptno
and
m.job=emp.job
and
m.sal_max=emp.sal;

if(a,b,c): 如果表达式a成立执行b,不成立执行c
greatest(a,b,c): 返回abc中值最大的一个(行中最大的字段)

求出公司中每个员工的姓名 和三类收入中最高的那种收入的类型
select
a.name,
greatest(jb,jj,tc) max_sal,
if(jb=greatest(jj,jb,tc) , 'jb',if(jj=greatest(jj,jb,tc)  , 'jj' , 'tc')) typt
from
(select
yg.name,
yg.uid,
gz.jb,
gz.jj,
gz.tc
from
gz
join
yg
on
gz.uid=yg.uid) a

case when then else end :
case
when 条件表达式1 then 成立返回对应的值
when 条件表达式2 then 成立返回对应的值
else 返回条件表达式12不成立的值
end

求出公司中每个员工的姓名 和三类收入中最高的那种收入的类型
select
a.name,
greatest(jj,jb,tc) sal_max,
case 
when greatest(jj,jb,tc)=jj then 'jj'
when greatest(jj,jb,tc)=jb then 'jb'
else 'tc'
end as typt
from
(select
yg.name,
gz.jj,
gz.tc,
gz.jb
from
gz
join
yg
on
gz.uid=yg.uid) a