hive的查询
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list [HAVING condition]]
[CLUSTER BY col_list|[DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list]
ORDER BY col_list:全局排序 stu按照年龄排序 select * from stu order by age desc; 默认为升序
SORT BY:局部排序 select * from stu sort by age desc; 根据reducetask的局部进行排序但是当reduce的个数为1的时候
和order by 一样;最终的输出结果是随机的。
DISTRIBUTE BY col_list:类似分桶 按照指定的字段和你设置的reducetask的个数进行分桶但是并没有排序。
CLUSTER BY col_list: 分桶之后进行排序等于DISTRIBUTE BY+SORT BY 注意cluster by和sort by不可以同时使用适用于
两个字段一样
-
set hive.exec.mode.local.auto=true;设置本地模式
-
set mapreduce.job.reduces=3设置reducetask
-
建表语句
- clustered by 分桶
- sort by 排序
- partitioned by 分区
-
查询语句
- order by 全局排序
- sort by 局部排序
- distribute by 类似于分桶
- cluster by 分桶加排序
-
分桶表的作用:
- hive中的表都是大表,大表关联大表,大表1关联大表2就需要全部扫描大表2。提升效率需要将两个表进行分桶。
- 提高join的效率
- 提高抽样的性能散列每个桶中的数据足够散列。
#hive的fetch过程 抓取过程
-
执行hql语句的时候 一旦要进行mr过程效率低
-
这个时候可以设置将一些hive语句不需要走mr程序尽量不走mr
-
hive的2.0版本中查询支持spark和tez 默认为mr
-
Default Value: minimal in Hive 0.10.0 through 0.13.1, more in Hive 0.14.0 and later
-
Added In: Hive 0.10.0 with HIVE-2925; default changed in Hive 0.14.0 with HIVE-7397
none: Disable hive.fetch.task.conversion (value added in Hive 0.14.0 with HIVE-8389)表示所有的 hql语句都需要转换为mr程序 minimal: SELECT *, FILTER on partition columns (WHERE and HAVING clauses), LIMIT only 查询所有语句 过滤条件在分区列 limit 三种语句不走mapreduce剩下的都走mapreduce more: SELECT, FILTER, LIMIT only (including TABLESAMPLE, virtual columns) 所有的select 和过滤 limit
-
-
join
- hive中的joinhive中的join注意
- 等值连接不支持不等值连接 select a.,b. from a join b on a.id=b.id;
- 支持支and不支持or select a.,b. from a join b on a.id=b.id and a.name=b.name; hive底层就是把hql语句转化为mr
- join可以支持多表连接 select a.,b.,c.* from a join b on a.id=b.id join c on a.id=c.id;多表连接如果是同一个字段只需要一个mr,如果字段不是同一个字段则是需要多个mr。
- 内连接 inner join 默认就是内连接的
- 外连接 左外连接 left outer 全连接 左右表都显示 full outer左右表取并集
- 半连接 left semi 左表中在右表中存在的数据 并且返回左表中的数据
#hive的数据类型
- hive中的joinhive中的join注意
-
数字类
类型 长度 备注 TINYINT 1字节 有符号整型 SMALLINT 2字节 有符号整型 SMALLINT 2字节 有符号整型 INT 4字节 有符号整型 BIGINT 8字节 有符号整型 -
日期时间类
类型 长度 备注 TIMESTAMP – 时间戳,内容格式:yyyy-mm-dd hh:mm:ss[.f…] DATE – 日期,内容格式:YYYYMMDD INTERVAL – – -
字符串类
类型 长度 备注 STRING – 字符串 VARCHAR 字符数范围1 - 65535 长度不定字符串 CHAR 最大的字符数:255 长度固定字符串 -
Misc类
类型 长度 备注 OOLEAN – 布尔类型 TRUE/FALSE BINARY – 字节序列 -
复合类
类型 长度 备注 ARRAY – 包含同类型元素的数组,索引从0开始 ARRAY MAP – 字典 MAP<primitive_type, data_type> STRUCT – 结构体 STRUCT<col_name : data_type [COMMENT col_comment], …> UNIONTYPE – 联合体 UNIONTYPE<data_type, data_type, …> -
表的创建
create table union_testnew(foo uniontype<int, double, string, array<string>, map<string, string>>) row format delimited collection items terminated by ',' map keys terminated by ':' lines terminated by '\n' stored as textfile;
-
hive的视图
- 创建视图:create view my_view as select * from stu where age>=19;
- 注意事项相当于存储了sql语句的快捷方式
- hive中的视图只有逻辑视图没有物化视图
- hive中的视图只支持查询不支持增删改
- hive中的视图保存的值只保存sql语句不保存存储结果
- 查看视图:show tables;既显示表信息有显示视图信息 show views;只显示视图信息 元数据存储在TBLS中
- 删除视图:drop view my_view
- 创建视图:create view my_view as select * from stu where age>=19;
hive的内置函数
- 分为3类
- UDF:自定义函数一对一的
- UDAF:自定义聚合函数 多对一
- UDTF:自定义表函数 一对多
- 查看内置函数:show functions;
- 查看函数得描述信息:desc function 函数
- 查看函数的详细信息:desc function extended 函数
- floor 取不大于某个数的最大整数; select floor(4.5);
- ceil 取不小于这个数的最小整数 select ceil(4.5);
- substring字符串截取 select substring(“hello word”,5); 截取的时候从1开始 负数的时候从右向左
- instr 返回第二个字符串在第一个字符串第一次所出现的下标 select instr(“nihao”,“ha”); 如果后面的字符串是没有的值则会返回0
- concat 字符串拼接按照传参的顺序进行拼接
- concat_ws 字符串拼接按照传参的顺序进行拼接 第一个传分隔符
- array生成数组select array(1,2,3,5,4);
- map生成map select map(“name”,“a”,“d”,“ds”); 奇数key value 偶数
- nvl 采集数据可能有null值 如果参数1为null则返回参数2 select nvl(null,2); select nvl(1,2);
- if 类似三目表达式 select if(表达式,参数1,参数2) select if(2>3,3,6);
UDTF:
-
explode 将数组分成多个行 获将 map分为多个行 2列 explode(参数为一个数组或map)select explode(array(1,2,3,4)); 当同时查询炸裂字段和普通字段需要使用横向虚拟视图。
select tf.* from (select 0) t lateral view explode(array('A','B','C')) 横向视图的别名 as 字段的别名(map应该给两个);
自定义函数
- 建立一个工程 导入jar包
- 继承udf 重写evluat方法
- 使用 :
- 把写的工程导成jar包
- 放入linux
- 添加到hive的下add jar jar包的路径
- 查看 list jars
- 给自定义函数起别名 并在hive中注册这个函数 create temporary function my as ‘udf.MyUdf’;这种方式是临时函数客户端关闭就没有了生产中就使用这种方式
- 查看函数库中有没有你的函数
- 根据方法名和参数来确定用哪个
- 可以变成永久函数 但是需要修改源码
hive解析json函数
-
get_json_object(json_text,path)
-
create table rating(line string)
-
load data local inpath ‘/home/hadoop/json’ into table rating;
-
select get_json_object(line,"$.firstName") from rating ; 取值json的值
$ : Root object 根目录 . : Child operator 子对象 访问下一级节点的 [] : Subscript operator for array 数组下标用于区数组的 * : Wildcard for []
-
transform方式
-
使用脚本来解析数据 python脚本
#!/usr/bin/python import sys import datetime for line in sys.stdin: line=line.strip() movie,rate,unixtime,userid=line.split("\t") weekday=datetime.datetime.formtimestamp(float(unixtime)).isweekday() print '\t'.join([movie,rate,str(weekday),userid])
-
-
脚本使用
-
如果想要hive找到脚本需要将脚本放到claapath ; add file /home/hadoop/mypython.py;
-
select transform(需要解析原数据库中的字段) using ‘python mypython.py’ as (python解析出来的字段)from 表名;
-
row_number() over(分组排序规则 partition by order by) 分组加行号在每一组加从1开始顺序添加
select id,name,gender,age,derpart,row_number() over(partition by derpart order by age) as index from stu;
-
rank()over(分组排序规则) 用于排名计数排名如果相同拍相同的名次
select id,name,gender,age,derpart,rank() over(order by age) as index from stu;
-
dence_rank() over(分组排序规则)用于排名 不计数排名连续排名并列的显示一样。
-
业务需求中会出现求topN需求 每个部门中年龄最大的员工
-
-
分隔符 hive中分隔符默认为单字节的 如果想要使用多字节分隔符 修改输入
create table test(id int,name string)row format delimited fields terminated by '::'; load data local inpath '/home/hadoop/test01' into table test; 1 2 3 4
-
修改这个类为正则表达式的类
SerDe Library: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe 序列化库这个库只能解析单字节的如 果想要解析多字节修改这个类 InputFormat: org.apache.hadoop.mapred.TextInputFormat OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOut serde 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe' 定义序列化的类库指定的是正则表达式的方式 'input.regex'='(.*)\\|\\|(.*):定义输入的正则表达式 'output.format.string'='%1$s %2$s %3$3' 定义输出的正则表达式怎么从你的正则表达式中抽取结果 create table test_rg(id string,name string)row format serde 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe' with serdeproperties('input.regex'='(.*)::(.*)','output.format.string'='%1$s %2$s') stored as textfile;
-
修改底层源码 不建议
-
-
hive的shell
- 进入hive之前
- hive -i 从文件初始化hql
- -e 从命令行执行指定的hql hive -e ‘show databases’ 不进入hive就可以执行hql语句
- -f 执行hql脚本 hive -f hive.conf
- -v 输出执行的hql语句到控制台
- -S 表示以不答应日志的形式执行操作
- -p
- -hiveconf hive -hiveconf mapred.reduce.tasks=3 设置属性值这种方式启动的时候这个参数已经被改变
- 进入hive之后
- quit 退出客户端
- set mapred.reduce.tasks;查看reduce的个数
- set mapred.reduce.tasks=10;给属性赋值
- add file 添加脚本文件
- add jar 添加jar 添加到class path
- list jar(file) 显示所有的文件或jar
- dfs 可以调用hdfs命令
- source 执行sql的脚本需要对数据库批量执行sql语句
- 进入hive之前