hive
1、 /bin/hive
2、 /bin/beeline
beenline> ! connect jdbc:hive2://youduk3:10000
使用hive
1、与hdfs 文件映射。
1)创建映射文件
touch test.txt
vim test.txt
1,allen,34
2,tom,22
3,acat,45
4,ali,21
5,afe,55
2、上传文件到hdfs
//创建个临时目录
hadoop dfs -mkdir -p /test/
//上传文件到临时目录
hadoop dfs -put test.txt /test/
3、创建表
hive
//创建数据库
hive> create database t_hive;
//创建表
create table t_ta (id int,name string,age int);
表创建对应的hdfs 目录
4、把文件复制到/user/hive/warehouse/t_hive.db/t_ta下
hadoop dfs -cp /test/test.txt /user/hive/warehouse/t_hive.db/t_ta
hive> select * from t_ta;
OK
NULL NULL NULL
NULL NULL NULL
NULL NULL NULL
NULL NULL NULL
NULL NULL NULL
成功映射到了行。但是没映射到数据。
5、设定分隔符映射信息。
create table t_ta2 (id int,name string,age int) row format delimited fields terminated by ',';
hadoop dfs -cp /test/test.txt /user/hive/warehouse/t_hive.db/t_ta2
hive> select * from t_ta2;
OK
1 allen 34
2 tom 22
3 acat 45
4 ali 21
5 afe 55
hive 建立表 跟已经存在的结构化数据产生映射关系。映射成功后可以通过写sql分析数据,避免写mr程序的麻烦
6、创建表类型&分割符
ROW FORMAT DELIMITED
[FIELDS TERMINATED BY char]
[COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char]
[LINES TERMINATED BY char] | SERDE serde_name
[WITH SERDEPROPERTIES
(property_name=property_value,property_name=property_value)]
复杂类型的数据表指定分割符
1、内置分隔符
ROW FORMAT DELIMITED
[FIELDS TERMINATED BY ‘,’]
[COLLECTION ITEMS TERMINATED BY ‘-’]
[MAP KEYS TERMINATED BY ‘:’]
1、数据如下:
zhangsan wef,wef3,efw321,ff24,f124
lisi hhh11,wef121,wseg12323,bd124124,gh24125
建表语句
create table t_ta3 (name string,items array<string>) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' COLLECTION ITEMS TERMINATED BY ',' ;
touch ttt.txt
vim ttt.txt
zhangsan wef,wef3,efw321,ff24,f124
lisi hhh11,wef121,wseg12323,bd124124,gh24125
退出保存
hadoop dfs -put ttt.txt /test/
hadoop dfs -cp /test/ttt.txt /user/hive/warehouse/t_hive.db/t_ta3
hive>use t_hive;
hive> select * from t_ta3;
OK
zhangsan [“wef”,“wef3”,“efw321”,“ff24”,“f124”]
lisi [“hhh11”,“wef121”,“wseg12323”,“bd124124”,“gh24125”]
Time taken: 0.131 seconds, Fetched: 2 row(s)
2、数据如下:
zhangshang 唱歌:非常喜欢-跳舞:喜欢-游泳:不喜欢
lisi 打游戏:喜欢-篮球不喜欢
建表语句
create table t_map(name string,hobby map<string,string>) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' COLLECTION ITEMS TERMINATED BY '-' MAP KEYS TERMINATED BY ':' ;
touch t_map.txt
vim t_map.txt
zhangshang 唱歌:非常喜欢-跳舞:喜欢-游泳:不喜欢
lisi 打游戏:喜欢-篮球:不喜欢
hadoop dfs -put t_map.txt /test/
hadoop dfs -cp /test/t_map.txt /user/hive/warehouse/t_hive.db/t_map
hive> select * from t_map;
OK
zhangshang {“唱歌”:“非常喜欢”,“跳舞”:“喜欢”,“游泳”:“不喜欢”}
lisi {“打游戏”:“喜欢”,“篮球”:“不喜欢”}
Time taken: 0.122 seconds, Fetched: 2 row(s)
2、默认分割符
hive 建表语句时候默认分隔符是’\001’ ,若建表的时候没有指明分隔符,load文件的时候分割是需要的是’\001’;若文件分隔符不是’001’,程序会报错,但是查询会显示NULL
SerDe 是Serialize/Deserilize的简称,目的是用于序列化和反序列化。
用vi编辑器 ctrl+v 然后 ctrl + a 即可输入 ‘\001’ ----------------->^A
vi test1.txt
1AallenA34
2AtomA22
3AacatA45
4AaliA21
5AafeA55
hadoop dfs -put test1.txt /test/
hadoop dfs -cp /test/test1.txt /user/hive/warehouse/t_hive.db/t_ta
hive> select * from t_ta;
OK
NULL NULL NULL
NULL NULL NULL
NULL NULL NULL
NULL NULL NULL
NULL NULL NULL
1 allen 34
2 tom 22
3 acat 45
4 ali 21
5 afe 55
3、分区
hive 建表语法
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] 表名
(列名 data_type [COMMENT 列注释], ...)
[COMMENT 表注释]
[PARTITIONED BY (列名 data_type [COMMENT 列注释], ...) 分区]
[CLUSTERED BY (列名, 列名, ...)
[SORTED ,BY (列名 [ASC|DESC], ...)] INTO num_buckets BUCKETS]
[ROW FORMAT row_format]
[STORED AS file_format]
[LOCATION hdfs_path]
PARTITIONED BY (列名 data_type [COMMENT 列注释], …)
1、不能与表字段重复。
2、分区字段是虚拟字段,不存放任何数据。
3、分区字段是数据来自于装载分区表数据的时候指定的。
建表语句:
create table t_p_test(id int, name string) PARTITIONED BY (country string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;
touch test2.txt
vim test2.txt
1,allen
2,tom
3,acat
4,ali
5,afe
hadoop dfs -put test2.txt /test/
hadoop dfs -cp /test/test2.txt /user/hive/warehouse/t_hive.db/t_p_test
hive> select * from t_p_test;
OK
结果没有对应上分区。
这个时候我们需要换一种命令来实现。
导入数据
hive> load data local inpath ‘/home/bigdata/apache-hive-1.2.2-bin/hdfs/test2.txt’ into table t_p_test partition(country=‘zh’);
local 表示数据来自本地 如果没有说明数据来源于hdfs
hive> select * from t_p_test;
OK
1 allen zh
2 tom zh
3 acat zh
4 ali zh
5 afe zh
目录结构 count=zh 显示每个分区的文件。
2、双分区
create table t_p_test2 (id int, name string) PARTITIONED BY (country string,hour string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;
hive> load data local inpath ‘/home/bigdata/apache-hive-1.2.2-bin/hdfs/test2.txt’ into table t_p_test2 partition(country=‘zh’,hour=‘12’);
多分区
在第一层分区创建第二层分区
3、三分区或多分区
create table t_p_test3 (id int, name string) PARTITIONED BY (country string,hour string,minut string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’ ;
hive> load data local inpath ‘/home/bigdata/apache-hive-1.2.2-bin/hdfs/test2.txt’ into table t_p_test3 partition(country=‘zh’,hour=‘12’,minut=‘10’);
4、分桶
hive 建表语法
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] 表名
(列名 data_type [COMMENT 列注释], ...)
[COMMENT 表注释]
[PARTITIONED BY (列名 data_type [COMMENT 列注释], ...) 分区]
[CLUSTERED BY (列名, 列名, ...) 分桶
[SORTED ,BY (列名 [ASC|DESC], ...)] INTO num_buckets BUCKETS]
[ROW FORMAT row_format]
[STORED AS file_format]
[LOCATION hdfs_path]
分桶操作也叫分簇操作
1、指定开启分桶
set hive.enforce.bucketing= true 开启分桶
set mapreduce.jog.reduces=4 设置分桶数量。
2、分桶表字段一定是表作用已经存在的字段。
创建表语句
create table ss_buck(ssid int,ssname string,sssex string ,ssage int) clustered by (ssid) into 4 buckets ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;
创建分桶表数据
touch ss_buck_test.txt
1,wanisha,女,42
2,dave,男,22
3,merry,女,41
4,kebob,男,12
5,wanisha,女,42
6,dave,男,22
7,merry,女,41
8,kebob,男,12
9,wanisha,女,42
10,dave,男,22
11,merry,女,41
12,kebob,男,12
创建ss_buck_base表
create table ss_buck_base(ssid int,ssname string,sssex string ,ssage int) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
load data local inpath '/home/bigdata/apache-hive-1.2.2-bin/hdfs/ss_buck_test.txt' into table ss_buck_base;
hive> select * from ss_buck_base;
OK
1 wanisha 女 42
2 dave 男 22
3 merry 女 41
4 kebob 男 12
5 wanisha 女 42
6 dave 男 22
7 merry 女 41
8 kebob 男 12
9 wanisha 女 42
10 dave 男 22
11 merry 女 41
12 kebob 男 12
基础表组件完成。
查询基础表ss_buck_base ----> ss_buck
insert overwrite table ss_buck select * from ss_buck_base cluster by (ssid);
hive> select * from ss_buck;
OK
1 wanisha 女 42
2 dave 男 22
3 merry 女 41
4 kebob 男 12
5 wanisha 女 42
6 dave 男 22
7 merry 女 41
8 kebob 男 12
9 wanisha 女 42
10 dave 男 22
11 merry 女 41
12 kebob 男 12
Time taken: 0.151 seconds, Fetched: 12 row(s)
往桶表中加入一次数据。需要执行依次reduce操作。(每次操作都需要计算依次 hash值)
5、外部表
hive 建表语法
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] 表名
(列名 data_type [COMMENT 列注释], ...)
[COMMENT 表注释]
[PARTITIONED BY (列名 data_type [COMMENT 列注释], ...) 分区]
[CLUSTERED BY (列名, 列名, ...) 分桶
[SORTED ,BY (列名 [ASC|DESC], ...)] INTO num_buckets BUCKETS]
[ROW FORMAT row_format]
[STORED AS file_format]
[LOCATION hdfs_path]
外部表
使用 CREATE [EXTERNAL] TABLE 配置是外部表 [LOCATION hdfs_path] 配置hdfs 表目录。
1、使用配置目录外。
2、删除表。数据不会被删除掉。
创建表语句:
create EXTERNAL table out_test(id int,name string,age int) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;
准备数据
vi out_test
1,alla,21
1,alla,23
1,alla,24
1,alla,25
1,alla,26
6、修改表
增加分区:
alter table table_name add partition(dt=‘20200101’) location ‘/user/hive/warehouse/table_name/dt-20200101’ //添加一个分区
alter table table_name add partition(dt=‘20200101’,country=‘us’) location ‘/user/hive/warehouse/table_name/dt-20200101’ partition(dt=‘20200102’,country=‘us’) location ‘/user/hive/warehouse/table_name/dt-20200102’ //添加多个分区。
删除分区
alter table table_name drop if exists partition(dt=‘partitionname’);
alter table table_name drop if exists partition(dt=‘partitionname’,country=‘us’);
修改分区
alter table table_name partition(dt=‘2020-01-01’) rename to (dt=‘xxx’)
添加列
alter table table_name add|replace columns (col_name datatype);
注: add是代表新增一个字段, 新增字段的位置在所有列后面 partition列前
replace 则是表示替换表中所有字段。
修改列
test_change (a int,b int,c)
修改test_change
alter table test_change change a a1 int;//修改a为 a1
alter table test_change change a a1 string after b;修改a字段为a1 位置在 b字段后面。
alter table test_change change b b1 first 把b字段改b1 并把位置放到第一个位置。
表重命名
alter table table_name rename to new_table_name;
7、显示命令
show tables;
显示当前所有表
show databases|schemas;
显示所有数据库名称
show partitions table_name;
泗安事表分区信息,不是分区表会报错
show functions;
显示当前hive支持的所有方法;
desc extended table_name;
显示外部表信息
desc formatted table_name;
查看表信息(格式化美观)
describe database database_name;
查看数据库相关信息
8、load
在将数据加载到表中,hive 不会进行任何转换,加载操作是将数据文件移动到与hive表对应的位置纯复制/移动操作。
语法结构
load data [local] inpath ‘filepath’ [overwrite] into table table_name [patition(partcol1=val1,partcol2=val2)]
说明:
1、filepath
相对路径,例如:project/data1
绝对路径,例如:/usr/hive/project/data1
完整路径,例如:hdfs://hostname:9000/usr/hive/project/data1
filepath 可以引用一个文件(在这种情况下,hive将文件移动到表中),或者他是一个目录,(在这种情况,hive将把目录中的所有文件移动到表中)
2、local
如果指定local ,load 命令将在本地文件系统中查找文件路径。
1)load命令将filepath 中的文件复制到目标文件系统中。目标文件系统由表的位置属性决定。被赋值的数据文件移动到表的数据应对的位置。
2)如果没有指定local关键字,如果filepath指向的事一个完整url,hive会直接使用这个uri,否则:如果没有指定schema或者authority,hive会使用在hadoop配置文件中定义的schema和authority,fs.default.name指定了namenode的uri
3)overwrite
如果使用了overwrite 关键字,则目标表或者分区表中的内容会呗删除,然后再将filepath执行的文件/目录中内容添加到表/分区中。
如果目标表(分区)已经有了一个文件了。并且文件名和filepath中的文件名冲突,name现有的文件将会被新文件所替代。
9、insert
insert
hive中insert 主要结合select 查询表中数据使用个,将查询结果插入到表中,例如:
insert overwrite table ss_buck select * from ss_buck_base cluster by (ssid);
需要保证查询结果列的数据和需要插入数据表格的列数据一致。
如果查询的数据类型和插入表格对应的列数据类型不一致,将会进行数据转换,但不能保证转换成功,如果失败将会设置成NULL
可以江一个表查询出来的数据插入到原来表中,结果相当于自我复制一份数据。
multi inserts
多重插入
from source_table
insert overwrite table table_name1 [partition (partcol1=val1,partcol2=val2)]
select_statement1
insert overwrite table table_name2 [partition (partcol1=val1,partcol2=val2)]
select_statement2
例如 源表source_table 中有 a,b,c,d 列
把a,b 列插入到 table_name1 表 ,把c,d 插入到table_name2表中。
from source_table
insert overwrite table table_name1 [partition (partcol1=val1,partcol2=val2)]
select a,b
insert overwrite table table_name2 [partition (partcol1=val1,partcol2=val2)]
select c,d
动态分区插入
insert overwrite table table_name partition (partcol[=val1],partcol2[=val2]…) select statement from
动态分区是位置来对应分区值。原始表select 出来的值和输出partition的值得关系仅仅是通过位置来确定,和名字并没有关系。
1)打开分区功能
set hive.exec.dynamic.partition=true;//是否开启动态分区功能,默认是false
set hive.exec.dynamic.partition.mode=nonstrict;//动态分区模式。默认是strict,表示必须指定至少一个分区为静态分区
创建原始表
create tabel dynamic_partition_table(day string,ip) row format delimited fields terminated by “,”;
2020-05-10,ip1
2020-05-11,ip2
2020-05-12,ip3
2020-05-13,ip4
2020-05-14,ip5
load 加载 数据到dynamic_partition_table中。
创建目标表
create table dpt(ip string) partitioned by (month string,day string);
动态插入
insert overwrite table dpt partiton (month,day)
select ip,substr(day,1,7) as month, day from dynamic_partition_table;
10、导出表数据
语句结构
insert overwrite [local] directory directory1 select … from …
如果加local 导入到本地;不加 local 导入到hdfs 中。overwrite 覆盖掉目录中所有文件。谨慎使用;
11、查询操作
select [all | distinct] select_expr,select_expr…
from table_reference
join table_other on expr
[where where_condition]
[cluster by col_list | [distribute by col_list] [sort by | order by col_list] ]
[limit number]
说明:
1、order by 会对输入数据全局排序。因为只有一个reducer,会导致当数据规模较大时,需要较长时间的计算时间
2、sort by 不是全局排序,其在数据进入reducer 前完成排序,因此,如果使用sort by 进行排序,并且设置了mapred.reduce.tasks>1,则sort by 只保证每个reducer的输出有序,不保证全局有序。
3、distribute by (字段) 根据指定的字段将数据分到不同的reducer,分发算法的hash散列。
4、cluster by (字段) 除了具有distribute by 的功能外,还会对字段进行排序。
如果distribute 和sort 的字段是同一个, 此时的cluster by = distribute by + sort by
12、hive join
hive 除了支持传统 内关联,做关联,有关联,全关联,还支持 left semi join 和cross join ,但是这两种jion类型也可以用前面的代替。
hive 支持等值链接 (a.id = b.id) , 不支持非等值链接(a.id> b.id) 的链接,因为非等值链接非常难转换到map/reduce任务。另外,hive支持多2个以上表的jion
写join时,需要注意几个关键点:
1、join时,每次map/reduce任务的逻辑。
reducer 会缓存join 序列中除了最后一个表的所有记录,在通过最后一个表的结果序列化到文件系统中。这一实现有助于在reduce端减少内存的使用量。实践中,应该把最大的那个表下载最后(否则会因为缓存浪费大量内存)
2、left right 和full outer 关键字用于处理join 中空记录的情况
select a.val,b.val from a left outer join b on a.key = b.key
对应素有阿彪中记录都有一条记录输出。输出的结果应该是a.val,b.val,当a.key = b.key时,而当b.key中找不到等值的 a.key 记录也会输出。
a.val,NULL
3、left semi join 查询只反馈左表数据 right semi join 只返回右表数据。
4、corss join (##慎用 ----特别消耗性能,或者导致死机)
返回两个表的笛卡尔积结果,不需要指定关联键
5、设置本地模式(开发环境)
set hive.exec.mode.local.auto=true
测试只在本地跑sql 运行效率快。
13、hive 命令行
输入${HIVE_HOME}/bin/hive -H 或者 --help 可以显示帮助选项:
1、-i 初始化HQL文件
2、-e 从命令行执行指定的HQL
3、-f 执行HQL脚本
4、-v输出执行的HQL语句到控制台
5、-p connection hive server on port number
6、-hiveconf x=y user this to set hive/hadoop configuration vaiables
有三种设定参数方式
配置文件 (全局有效) 范围最大 优先级最小
命令行参数 (对hive 启动实例有效) 范围中 优先级最中
参数声明 (对hive 链接sesison 有效) 范围最小 优先级最高
14、函数
1、内置运算符
在hive 中四种类型运算符:
关系运算符;
算术运算符;
逻辑运算符;
复杂运算符;
2、内置函数
《hive 常用运算和函数》
https://blog.csdn.net/qq_45798620/article/details/110007995?ops_request_misc=&request_id=&biz_id=102&utm_term=hive%20%E5%B8%B8%E7%94%A8%E8%BF%90%E7%AE%97%E5%92%8C%E5%87%BD%E6%95%B0&utm_medium=distribute.pc_search_result.none-task-blog-2allsobaiduweb~default-0-110007995.first_rank_v2_pc_rank_v29&spm=1018.2226.3001.4187
创建dual 表
create table dual (id string);
load 一个文件(只有一行内容:内容为一个空格)到dual 表
select substr(‘wefwefaewfaef’,2,3) from dual;
3、自定义函数
UDF自定义函数实例
1、新建 java maven 项目
添加依赖 hive-exec-1.2.1.jar 和hadoop-common-2.7.4.jar 依赖
写一个java类,集成UDF,并重载evaluate方法
package xxx.xxx.xxx.xxx
import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.io.Text;
public class Lower extends UDF{
public String evaluate(String s) {
if (s == null) {return;}
return new Text(s.toString().toLowerCase());
}
}
2、maven 打成jar 上传服务器
3、将jar 包添加到hive 的classpath
hive>add JAR /home/hadoop/udf.jar
4、创建一个临时函数与开发好的java class 关联
create temporary function lower as 'xxx.xxx.xxx.xxx.Lower '
Transform实现
hive 的Transform 关键字提供了在sql中调用自己写的脚本的功能。
适合实现hive 中没有的功能有不想写UDF的情况。
使用示例1:下面这句sql就是借用了 weekday_mapper.py对数据进行了处理
add file weekday_mapper.py;
insert overwrite table u_data_new
select
transform(movieid,rate,timestring,uid)
using 'python weekday_mapper.py'
as (movieid,rating,weekday,userid)
from t_rating;
weekday_mapper.py 内容如下:
#!/bin/python
import sys
import datetime
for line in sys.stdin:
line = line.strip()
movieid,rate,timestring,uid = line.split('\t')
weekday = datetim,datetime.formtimestamp(float(unixtime)).isoweekday()
print '\t'.join([movieid,rating,str(weekday),userid])
15、hive特殊分隔符处理(扩展)
hive
hive 分割符如果使用||线等特殊符号分割。
01||张珊
02||李思
可以使用RegexserDe通过正则表达式方式来抽取字段。
建表语句
create tabel tt1 (id string,name string) row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe'
with serdeproperties(
'imput.regex'='(.*\\|\\|(.*)',
'output.format.string'='%1$s %2$s'
)