站浏览量次 站访问人数

hive
笔记

By

目录
  1. 1. 建表
  2. 2. 数据导入
  3. 3. json串
  4. 4. 中文
  5. 5. 正则
  6. 6. 时间操作
  7. 7. 自定义UDF

hive一些操作常用集合命令.

建表

构建一个以时间分区的表,

1
2
3
4
5
6
7
8
9
10
use 库名;
drop table if exists 表名;
create table 表名
(
   a string,
   b string,
   c int
)
partitioned by (cal_dt string)
row format delimited fields terminated by '\t';

注意分割符是\t,在读取hive时,分割字符存在两种,如果文件是压缩过的.lzo形式文件,则以\001分割,如果是part-00000形式文件,则以\t形式分割.

数据导入

当数据存储在hdfs上,数据加载进表,

1
2
use 库名;
load data inpath '/**/****/part-*' into table 表名;

当数据存储在本地(指hive的服务器上),数据加载进表,需在hive shell中执行,

1
2
use 库名;
load data local inpath '/**/****/part-*' into 表名;

构建一个按时间的分区表,从原有表中导入数据,不需要有分割符指定.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
use 库名;
drop table if exists 表名;
create table 表名
(
   a string,
   b string,
   c int
)
partitioned by (cal_dt string)
;

insert overwrite table 库名.表名 partition (cal_dt = 时间日期)
select *
from *
;

不是分区表的操作类似,只是不用加分区字段.

json串

需加载brickhouse-0.7.1.jar的包,使用旗下的函数,

1
2
CREATE TEMPORARY FUNCTION to_json AS 'brickhouse.udf.json.ToJsonUDF';
CREATE TEMPORARY FUNCTION collect AS 'brickhouse.udf.collect.CollectUDAF';

具体步骤如下:

1,构建结构体,给定name和value,

1
named_struct('a',a,'b',b,'c',c) as temp_item

2,多行转成json,

1
to_json(collect(c.temp_item)) as new_tr

中文

先提取替换掉非中文串,判断程度是否大于0.

1
select length(regexp_replace('25','([^\\u4e00-\\u9fa5]+)',''))

编码转换

1
2
3
4
5
6
7
存储数据时，有时以其他编码存储中文，比如unicode编码，需要对其进行转换，
如果是unicode编码，可以使用如下函数进行解码转换成中文：

select reflect("java.net.URLDecoder", "decode",desc) as otherdesc

当然也存在编码函数java.net.URLEncoder encode
select reflect("java.net.URLEncoder", "encode",'你好') as otherdesc

正则

正则使用的常用字符，

1
2
3
4
5
6
7
8
9
10
11
12
13
^ 开始       $ 结束     . 任意单个字符          * 匹配0或多个前面元素
\ 转义       [] 匹配集合中的一个   () 组或其内容当成一个元素
{} 重复      + 一个或多个与前面字符    | 或    ? 匹配0或1个前面元素
- 特殊字符   

一下是一些简单例子：
(a|b) a或b   [abc] 单个字符是abc其中一个即可    [^abc] 不是abc的其他字符
[a-z] a-z的字符   [A-Z] A-Z的字符   [0-7]数字0-7,\\(.*\\) 匹配(任意个字符)模式

特殊字符转义表示：
\s 空格 \S 不是空格的其他字符  \d 数字  \D 不是数字的其他字符 \w 单词  \W 标点符号
$A1 访问A中的第1个
{x} 重复x，{x,}重复x，

有些文本含这些字符,在hive表中显示时会跳行,需剔除掉，

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
替换掉文本的换行符,tab符等,
regexp_replace(comm_speciality,'\\\\n|\\\\r|\\\\t|\\t|\\r|\\n','') as new_comm_speciality

去除html格式的编码，
regexp_replace(collect_set(original_content)[0],'<[^<]*>|&[a-z]+\073','')

regexp_extract('/ask/jh-100000050006','-(\\w+)',1)
返回100000050006

regexp_extract('/cd/qalist/category/10','\\/(\\d+)',1)
返回10

url解析，HOST,PATH,QUERY,REF,PROTOCOL,FILE,AUTHORITY,USERINFO

parse_url('http://beijing.***.com/ask/SE_%E5%B9%B3%E6%88%BF/p2/','PATH')

parse_url('http://m.anjuke.com/bj/qalist/search?q=%E7%A8%8E%E8%B4%B9','QUERY','q')
q=%E7%A8%8E%E8%B4%B9  是?之后的参数

parse_url('http://m.anjuke.com/cd/qalist/category/0?','PATH')
/cd/qalist/category/0,host之后与?之前的一段,

正则提取，第二个参数是正则,注意转义使用\,第三个参数是正则的第几个小括号,0是全部匹配,1是代表匹配到第一个小括号的值,

1
2
3
4
5
regexp_extract(parse_url(url,'PATH'),'-(\\w+)',1)

regexp_extract(desc,'(\\d+\\.\\d+)(平.)|(\\d+)(平.)',0) /*提取带平或平米的面积*/                     
length(regexp_extract(desc,'(\\d+\\.\\d+)(m.)|(\\d+)(m.)',0) /*提取带m或m2的面积*/
但是像12min这种需要再过滤，采取同样原理过滤；整体匹配的放在前，部分放在后，即先匹配小数的。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
当提取多个符合串时，regexp_extract只能提取一个，这时采取的策略是先按照匹配的分割，分割后进行替换，得到全部的匹配串。

select concat_ws(',',split(regexp_replace('朱辛庄地铁站肯特公寓，40平米两居室20万，24平13万'
       ,concat_ws('|',split('朱辛庄地铁站肯特公寓，40平米两居室20万，24平米13万'
                ,'((\\d+\\.\\d+)(平米)|(\\d+)(平米)|(\\d+\\.\\d+)(平)|(\\d+)(平)|(\\d+\\.\\d+)(m2)|(\\d+)(m2))'
                ))
       ,'')
       ,'平米|平|m2'))
~~~bash


hive下的map,

~~~bash
select str_to_map('/ask/SE_dsfdsf/p2/','_','/')
    得到  {"":"ask/SE","dsfdsf":"p2/"}
         参数含义 (串,分割符,字串的key-value分割符)

得到一个map的keys,map_keys(map),map是key-value类型的map,

1
2
select map_keys(str_to_map('/ask/SE_dsfdsf/p2/','_','/'))[1]
   返回dsfdsf,

concat(每个字段接着隔开的字符)

concat_ws(‘:’,collect_set(mid)),把mid字段进行合并,多条记录合并成一个;
concat(a1,’:’,a2),把多行并列成一行,值用:连接,

不重复排序记录
row_number() over(partition by * order by )

json串和行转列

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
 select *
      from
      (
        select  *
               ,regexp_replace(get_json_object(base_info,'$.a'),'\\[|"|\\]','')   as a
               ,regexp_replace(get_json_object(base_info,'$.b'),'\\[|"|\\]','')   as b
        from 表名
      )a
      lateral view explode(split(a,',')) b as a
;

json串解析
get_json_object(base_info,'$.a')

一行中多个值转成多行,即多值变成列值,
lateral view explode(split(as,',')) b as a

brickhouse-*.*.*.jar包解析json串
create temporary function from_json as 'brickhouse.udf.json.FromJsonUDF';
from_json(items,'array<map<string,int>>')，解析出[{},{},...,{}]类型数据，访问则与map一致，map[keyname].

集合操作，把数据聚集，collect_set是对数据进行了去重操作后的聚集；如果需要不去重，则使用brickhouse-0.7.1.jar中的collect函数，
ADD JAR ****/brickhouse-0.7.1.jar;
create temporary function collect as 'brickhouse.udf.collect.CollectUDAF';

数组切分出

from tableName
lateral view explode(
  from_json(para, 'array<map<string,string>>')
) x as item
where 条件

时间操作

时间戳转日期,
from_unixtime(unix_timestamp(),’yyyy-MM-dd HH:mm:ss’)

日期转时间戳，到秒，1494567967，
unix_timestamp(‘2017-05-12 13:45:67’)

转成日期,2017-01-01形式,
to_date(‘2017-01-01 00:07:59.0’)

时间减,当前固定天数
date_sub(${dealDate},59)

时间加，在原有日期上加上固定天数
date_add

时间减,前面时间减去后面时间,得到的是相差天数
datediff(‘2016-12-07’,’2016-12-08’)

取日期的year，month，day，hour，minute，second，
year(‘2017-05-12 13:45:67’)

自定义UDF

在操作表时，需要多一些数据进行特殊处理，而hive自带的函数却没有能解决到的，这种情况出现在特定业务场景下，以及需要操作特殊数据得到自定义结果，以下是几个例子，仅供参考学习，

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
自定义一些map<key,value>，虽然可以在hive中指定，但是出现hive中需要大量的语句，即赋值语句，对操作不太美观。
比如建一张额外表，存储key-value关系，

create table tableName as
select 1 as map_id
       , map('1','label1',
             '2','label2',
             '3','label3'
            )
        as catalog_encode
;

因此需要做一个黑盒的函数，供使用者，是一个透明的；定义如下函数，

package 包名;

import java.util.HashMap;
import java.util.Map;

import org.apache.hadoop.hive.ql.exec.UDF;

public class CatalogDedoce extends UDF{

	public Map<String,Object> initMap(){
		Map<String,Object> result = new HashMap<>();
		map.put('1','label1');
                map.put('2','label2');
                map.put('3','label3');
		return result;
	}

	public String evaluate(final String key)
    {  
        if(key == null)
            return null;
        Map<String,Object> catalog_code = initMap();
        return catalog_code.get(key).toString();
    }  
}

然后，对其打包，在eclipse里直接export即可。然后，在hive时add包即可。当有多个自定义操作函数时，可放在一个包下，export出包即可。

对数据的操作还会对array<任意类型>数据类型操作，这时在hive里自带一些通用的方法，但是需要对其中数据进行特定的判断则需要自己定义方法实现，下面是判断一个判断数组中函数个数，以及连续数字的操作。

bash
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

public class 类名 extends UDF{

	public int evaluate(List<Long> arguments,int spaceMinute,int hasNum,int totalNum){  

		if (arguments!=null && !arguments.isEmpty()){
			int len = arguments.size();

			if (len>totalNum){
				return 3;
			}
			if (len>hasNum){
				int start = 0;
				int end = start+hasNum;
				while (end<len){
					if ((arguments.get(end)-arguments.get(start))/60<=spaceMinute){
						return 2;
					}
					start = start + 1;
					end = start+hasNum;
				}
			}
		}
	     return 0;
	}  
}

在hive中array<>类型对应java的List。

使用自定义的函数方式如下：

1
2
3
4
ADD JAR *****.jar;
CREATE TEMPORARY FUNCTION 函数名 AS '包.类';

函数名(字段,[参数])