HIVE
踩坑
本来hive运行正常,但是由于运行试验spark sql期间把mysql中的所有root相关用户都删除了,只剩一个,且把hive-site.xml配置文件中的连接MySQL用户名和密码都改成了root,所以这次在使用hive时,运行show databases等命令报错
MetaException(message:Hive Schema version 2.3.0 does not match metastore's schema version 1.2.0 Metastore is no
。
通过博客1和博客2可知,
原因为: spark应用创建表时,指定的schema版本为1.2.0,而hive的schema版本为2.3.0,版本不兼容导致。
解决为: 在hvie-site.xml中将hive.metastore.schema.verification参数设置为false
<property>
<name>hive.metastore.schema.verification</name>
<value>false</value>
</property>
成功
理论知识
- 分区作用。
由博客2如果没有分区的存在,那么每次查询Hive将会进行全表扫描。对于小数据量的表来说,全表扫描并不会慢到无法忍受,但是对于大数据量来讲,比如几年的数据,每次查询都要扫描几年的所有数据,除了浪费时间之外,还浪费集群资源。为了改进这一问题,分区的价值就体现出来了。- 静态分区
Hive的静态分区,实际上就是手动指定分区的值为静态值,这种对于小批量的分区插入比较友好,语句中partition(year=“2020”, month=“04”, day=“2020-04-10”, hour=“22”) 的年月日小时手动指定了具体的值,这样的分区就叫静态分区了。 - 动态分区
Hive的动态分区,其实就是把静态分区中的分区值设置为动态的值,就可以了
语句中partition(year=year, month=month, day=day, hour=hour)会根据具体值的变化而变化,无需手动指定,这对于大批量的分区插入是一个很方便的用法,但需要根据业务需求衡量分区数量是否合理的问题。毕竟分区会占用IO资源,数量越多,IO资源消耗越大,查询时间和性能都是有所损耗的。insert overwrite table demo_dynamic_partition partition(year=year, month=month, day=day, hour=hour) select user_id, user_name, trade_year as year , trade_month as month, trade_day as day, trade_hour as hour from user_demo
2. 结合使用
Hive静态分区和动态分区结合使用
Hive的动态分区,其实就是把静态分区中的分区值设置为动态的值,就可以了partition(year="2020", month="04", day=day, hour=hour) select user_id, user_name, trade_year as year , trade_month as month, trade_day as day, trade_hour as hour from user_demo where trade_year="2020" and trade_month="04"
- 静态分区
不难看出,Hive分区,主要是以缩小数据查询范围,提高查询速度和性能的。
- 分桶表创建后,直接load data不会有分桶的效果,这样和不分桶一样,在HDFS上只有一个文件。于是需要创建分桶表时,设置参数set hive.enforce.bucketing=true,并且数据通过子查询的方式导入,这样就会在hdfs上分成几个文件。
博客3中解释抽样查询
SELECT avg(viewTime)
FROM page_view TABLESAMPLE(BUCKET 1 OUT OF 32);
当您使用clustered子句创建表,并将其存储到32个桶中时(作为示例),hive使用确定性散列函数将您的数据存储到32个桶中。然后当你使用TABLESAMPLE(BUCKET x OUT OF y)时,hive将你的桶分成y个桶组(每组有y个桶),然后选择每个组的第x个桶,也就是整体的第x + y个桶。例如:如果您使用 TABLESAMPLE ( BUCKET 6 OUT OF 8 ) ,蜂巢会将您的32个桶分成8个桶组,产生4组8个桶,然后挑选每组的第6个桶,因此挑选桶6,14,22 ,30。如果您使用TABLESAMPLE ( BUCKET 23 OUT of 32 ) ,hive会将您的32个桶分成32个组,只产生1组32个桶,然后选择第23个桶作为结果。如果你使用TABLESAMPLE ( BUCKET 3 out of 64 ) ,hive会将你的32个桶分成64个桶组,产生一组64个“半桶”,然后选择对应于第3个满桶的半桶-桶。
-
HiveQL与SQL区别
从博客4可知- Hive不支持等值连接
SQL中对两表内联可以写成:
select * from dual a,dual b where a.key = b.key;
Hive中应为
select * from dual a join dual b on a.key = b.key;
而不是传统的格式:
SELECT t1.a1 as c1, t2.b1 as c2FROM t1, t2
WHERE t1.a2 = t2.b2 - IS [NOT] NULL
SQL中null代表空值, 值得警惕的是, 在HiveQL中String类型的字段若是空(empty)字符串, 即长度为0, 那么对它进行IS NULL的判断结果是False. - hive支持嵌入mapreduce程序,来处理复杂的逻辑
- hive支持将转换后的数据直接写入不同的表,还能写入分区、hdfs和本地目录
这样能免除多次扫描输入表的开销。
- Hive不支持等值连接
-
SQL 必学函数
- concat(str1,SEP,str2,SEP,str3,……) 和 concat_ws(SEP,str1,str2,str3, ……) 字符串连接函数,需要是 string型字段
- from_unixtime(unix_timestamp(),‘yyyy-MM-dd HH:mm:ss’) 时间转换函数博客5
1、unix_timestamp() 得到当前时间戳
如果参数date满足yyyy-MM-dd HH:mm:ss形式,则可以直接unix_timestamp(string date) 得到参数对应的时间戳
如果参数date不满足yyyy-MM-dd HH:mm:ss形式,则我们需要指定date的形式,在进行转换select unix_timestamp(‘2009-03-20’) --1237507200
select unix_timestamp(‘2009-03-20 00:00:00’, ‘yyyy-MM-dd HH:mm:ss’) --1237507200
select unix_timestamp(‘2009-03-20 00:00:01’, ‘yyyy-MM-dd HH:mm:ss’) --1237507201
select unix_timestamp(‘2009/03/20 00:00:01’, ‘yyyy-MM-dd HH:mm:ss’) --NULL
2.语法:from_unixtime(t1,’yyyy-MM-dd HH:mm:ss’)
其中t1是10位的时间戳值,即1970-1-1至今的秒,而13位的所谓毫秒的是不可以的。
对于13位时间戳,需要截取,然后转换成bigint类型,因为from_unixtime类第一个参数只接受bigint类型。例如:
select from_unixtime(1237507201,‘yyyy-MM-dd HH:mm:ss’) – 2009-03-20 00:00:01
select from_unixtime(1237507200,‘yyyy-MM-dd HH:mm:ss’) – 2009-03-20 00:00:00
常用的插入时间为当前系统时间 转换成距离1970的时间戳,再转换成当前时间
select FROM_UNIXTIME(UNIX_TIMESTAMP() ,‘yyyy-MM-dd HH:mm:ss’) AS W_INSERT_DT - regexp_replace(string A, string B, string C) 字符串替换函数
按照Java正则表达式PATTERN将字符串INTIAL_STRING中符合条件的部分成REPLACEMENT所指定的字符串,如里REPLACEMENT这空的话,抽符合正则的部分将被去掉 如:regexp_replace(“foobar”, “oo|ar”, “”) = ‘fb.’ 注意些预定义字符的使用,如第二个参数如果使用’\s’将被匹配到s,’\s’才是匹配空格 - to_date(string timestamp) 将时间戳转换成日期型字符串
- date_add(string startdate, int days) 日期加减
hive (default)> select date_add('2020-07-20',7); OK _c0 2020-07-27 Time taken: 6.524 seconds, Fetched: 1 row(s) hive (default)> select date_sub('2020-07-20',7); OK _c0 2020-07-13 Time taken: 0.342 seconds, Fetched: 1 row(s)
- date_format(date/timestamp/string ts, string fmt) 日期格式
返回日期时间字段中的日期部分hive (default)> select date_format('2020-07-20','yyyy/MM/dd'); OK _c0 2020/07/20 Time taken: 0.11 seconds, Fetched: 1 row(s) hive (default)> select date_format('2020-07-20','yyyyMMdd'); OK _c0 20200720
- last_day(string date) 返回 当前时间的月末日期
shell hive (default)> select last_day('2020-07-22 10:03:01'); OK _c0 2020-07-31
- if(boolean testCondition, T valueTrue, T valueFalseOrNull) ,根据条件返回不同的值
说明: 当条件testCondition为TRUE时,返回valueTrue;否则返回valueFalseOrNull`
hive> select if(a=a,’bbbb’,111) fromlxw_dual;
bbbb
hive> select if(1<2,100,200) fromlxw_dual;
200 - nvl(T value, T default_value) 如果T is null ,返回默认值
shell hive (default)> select nvl(1,23); OK _c0 1 Time taken: 0.073 seconds, Fetched: 1 row(s) hive (default)> select nvl(null,23); OK _c0 23
- length(string A) 返回字符串A的长度
- split(str, regex) ,安装规则截取字符串,返回数组
hive (default)> select split('帅哥|$美女','\\|\\$')[0]; OK _c0 帅哥 Time taken: 0.097 seconds, Fetched: 1 row(s) hive (default)> select split('ab-cd','-'); OK _c0 ["ab","cd"]
- 常用窗口函数
row_number():从1开始,按照顺序,生成分组内记录的序列,row_number()的值不会存在重复,当排序的值相同时,按照表中记录的顺序进行排列;通常用于获取分组内排序第一的记录;获取一个session中的第一条refer等。
rank():生成数据项在分组中的排名,排名相等会在名次中留下空位。
dense_rank():生成数据项在分组中的排名,排名相等会在名次中不会留下空位