2.Hive数据仓库

以下全部是在Zeppelin上操作

一、内部表：

1.向内部表插入数据

%hive
--insert  into mydemo.xxx values(1,'zhangsan',25),(2,'wangwu',27)
select * from mydemo.xxx

2.创建1个压缩格式为orc的内部表

%hive
create table mydemo.yyy(
    id string,
    name string
)
row format delimited fields terminated by ','
stored as orc

3.向表中插入数据

%hive
create table mydemo.yyy(
    id string,
    name string
)
row format delimited fields terminated by ','
stored as orc

4.补充：

hive建表语法中的分隔符设定
ROW FORMAT DELIMITED 分隔符设置开始语句
FIELDS TERMINATED BY：设置字段与字段之间的分隔符
COLLECTION ITEMS TERMINATED BY：设置一个复杂类型（array,struct)字段的各个item之间的分隔符
MAP KEYS TERMINATED BY：设置一个复杂类型(Map)字段的key value之间的分隔符
LINES TERMINATED BY：设置行与行之间的分隔符

二、外部表

1.创建一个外部表

首先在Hadoop上创建一个数据目录，后面将数据文件上传到此，再创建外部表映射到这里

-- 在Hadoop上创建一个数据目录
%sh
hdfs dfs -mkdir /tab


-- 上传数据文件到外部表的文件夹
%sh
hdfs dfs -put /opt/data/data.txt /tab



-- 创建一个外部表，映射到Hadoop上的数据文件位置

　%hive
　create external table mydemo.uss(
　id string,
　name string,
　job ARRAY<string>,
  sex_age STRUCT<sex:int,age:string>, -- 对象类型
  skill MAP<string,string>
  )
  row format delimited fields terminated by ' '   -- 列于列之间用空格隔开
  collection items terminated by ','　　　　　　　　 -- 集合之间用，隔开
  map keys terminated by ':'　　　　　　　　　　　　　 -- map的键值对之间用：隔开
  location '/tab'

 

  -- 查看数据表

　%hive
　select id,job[0],sex_age.age,skill['skill1'] from mydemo.uss

2.csv文件作为数据源

%sh
hdfs dfs -mkdir -p /tab1


%sh
hdfs dfs -put /opt/data/data.csv /tab1


%hive
create external table mydemo.usi(
    id string,
    name string,
    birth string,
    hoby string
)
-- row format delimited fields terminated by ','
-- location '/tab1'
-- tblproperties('skip.header.line.count'='1') --去除首行
row format serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
with serdeproperties 
(
    'separatorChar' = ',',
    'quoteChar'     = '\"',
    'escapeChar'    = '\\'
)
location '/tab1'
tblproperties('skip.header.line.count'='1')

 

三、根据已有的表创建新表，只能建内部表

%hive
--create table mydemo.usi1 as select * from mydemo.usi  --连数据带结构

--create table mydemo.usi2 like mydemo.usi              --只复制表结构

--创建表不推荐  但查询使用非常方便
create table mydemo.usi3 as
with
r1 as (select userid id,username name from mydemo.userinfos),
r2 as (select id,name from mydemo.usi)
select * from r1 union all select * from r2

 

四、使用load向表中传数据

分别创建一张内部表和一张外部表：

-- 内部表
%hive
-- drop table mydemo.test1
create table mydemo.test1(
    id string,
    name string
)
row format delimited fields terminated by ','
stored as textfile

-- 外部表
-- create external table mydemo.test2(
--     id string,
--     name string
-- )
-- row format delimited fields terminated by ','
-- location '/tab2'

 

2.用load加载hive中的数据  ---> ETL 数据格式不能转换

%hive
load data local inpath '/opt/data/dd.txt' into table mydemo.test1
-- local 是指从Linux本地加载  不加local就是指从Hadoop上加载
-- load data local inpath '/opt/data/dd.txt' overwrite into table mydemo.test2
-- overwrite是全量表（用一次覆盖一次）| 不写是增量表写法（往后面追加，前面已有数据不动）

 

五、创建分区表

　　分区表操作：实际上就是建立一个个的文件夹，将数据按照你的分区约定，分别存放进去。分为静态分区和动态分区

　　创建一个分区表，id，name，birthmonth三列，其中按birthmonth分区：

%hive
create table mydemo.my_part(
    id string,
    name string
)
partitioned by (birthmonth string)  -- 分区的那个列写在外面
row format delimited fields terminated by ','

 

1.手工创建一个静态分区

%hive
alter table mydemo.my_part add partition(birthmonth='01') -- 实质上就是手工添加一个文件夹

　　此时去你的50070端口，你会发现在/hive/warehouse/mydemo.db/my_part路径下，为你生成好了一个 ‘birthmonth=01’ 文件夹

 

2.向指定分区里面插入Linux本地的数据文件进去（两种方法 ： 静态 和 动态）

一：静态将数据以全量表（不覆盖）的方式插入
%hive
load data local inpath '/opt/data/dd.txt' overwrite into table mydemo.my_part partition(birthmonth='01')

二：动态插入一些数据
%hive
insert into mydemo.my_part partition(birthmonth='01') select id,name from mydemo.usi

 

3.再创建一个动态分区，并向其中插入数据

%hive
-- 动态分区是根据查询分区列的有几种变化就有几个分区（列值基数）
-- set hive.exec.dynamic.partition=true;
-- set hive.exec.dynamic.partition.mode=nonstrict;

-- 放入数据
insert into mydemo.my_part1 partition(birthmonth) select id,name,month(regexp_replace(birth,'/','-')) birthmonth from mydemo.usi

 

六、分桶表

1.先创建一个临时表

%hive
-- 创建一个临时表存放数据
-- create temporary table mydemo.tmp(
--     id string,
--     name string,
--     gender string
-- )
-- row format delimited fields terminated by ','
load data local inpath '/opt/data/ddd.csv' overwrite into table mydemo.tmp

2.创建分桶表

%hive
-- create table mydemo.bck_user(
--     id string,
--     name string,
--     gender string
-- )
-- clustered by (gender)   -- 按照什么分桶
-- into 2 buckets          -- 分多少个桶
-- row format delimited fields terminated by ','

-- set hive.enforce.bucketing=true
-- set mapreduce.job.reduces=2

insert into table mydemo.bck_user select * from mydemo.tmp

3.查看分桶表

%hive
select * from mydemo.tmp tablesample(bucket 2 out of 2 on gender)
-- (1)n = 总桶数  (2)x = 抽桶开始的位置  (3)y 2^z = 抽取总桶数的比例 n/y=2/2=1

 

七、创建视图

%hive
create view mydemo.view_user as
select id,name,if(gender='男',1,0) sex from mydemo.bck_user

%hive
select * from mydemo.view_user

八、Hive中的集合函数

size()针对列值有多个，底层其实为数组 

map_keys(字段) 当一个列是map类型，取出所有的key

map_values(字段) 当一个列是map类型，取出所有的value

array_contain(字段，'值') 针对array类型的集合  判断该列中是否包含有‘值’

sort_array(字段) 针对array类型的集合  进行排序

cast( 字段  as  类型) 将某一字段类型强转为后面指定的类型

 

九、日期函数

%hive
-- 1.from_unixtime()将一个时间戳转为指定格式的日期

-- 2.unix_timestamp()获取当下时间戳--->"2021-09-23 16:05:47"规定格式

-- 3.如果不用这种格式返回值为null,此时要在后面给定格式
-- select from_unixtime(1632384347,'yyyy-MM-dd HH:mm:ss'),unix_timestamp(),unix_timestamp("2021-09-23 16:05:47"),unix_timestamp("2021-09-23","yyyy-MM-dd")

-- 1.to_date()将字符串中的日期部分提取出来,year将日期中的年份提取出来
-- 2.dayofmonth()一个月中的第几天，如果给定日期超出，自动跳到下个月
-- 3.datediff()计算两个日期差多少天（前-后）
-- 4.date_sub('xxxx',int days)给指定日期减去days天*-
-- 5.current_date()获取当前时间
-- 6.next_Day(给定日期,'星期几') 找到从给定日期起，下一个该星期几是几号
-- 7.date_format("给定日期","给定格式") 将给定日期按照给定格式转换


-- select to_date("2021-09-23 16:05:47"),year("2021-1-1"),dayofmonth("2021-5-23"),weekofyear("2021-4-19"),datediff('2021-5-6','2021-5-23'),date_sub('2021-5-19',1),current_date(),current_timestamp(),add_months(current_date(),6),last_day(current_date())
select next_day(current_date(),'WE'),date_format(current_date(),"yyyy-MM")

 

十、hive 的数学函数

%hive
-- round（）函数,默认四舍五入，除非你在后面指定精确到小数点后几位。
select round(10.5),round(10.35468,3),floor(-10.2),ceil(10.9),rand(100)
select exp(2),ln(10),pow(2,3),sqrt(25)

 

十一、explode函数和posexplode函数

%hive
-- select * from mydemo.usi
-- 某一字段可能包含多个值，是个数组  explode函数将这些值全部拿出来  只拿值 注意必须起别名
-- select id,name,birth,ind from mydemo.usi lateral view explode(split(hoby,',')) b as ind

-- 在上面的基础上，将数组的下标和值对应着一一拿出 对应下标和值全部拿出来  注意必须起别名
select id,name,birth,ind,schem from mydemo.usi lateral view posexplode(split(hoby,',')) b as ind,schem