Hive编程指南-数据类型、DDL、DML、常用字符串函数

      摘要：本文主要讲了Hive的数据基本，及一些学用的DDL,DML语句，常用字符串函数。

一、Hive数据类型

Hive的数据类型主要如下几种：

1、基本类型

Hive表基本类型

类型	描述	示例
BOOLEAN	true/false	TRUE
TINYINT	1字节的有符号整数 -128~127	1
SMALLINT	2个字节的有符号整数，-32768~32767	18
INT	2个字节的有符号整数，-32768~32767	1
BIGINT	8字节带符号整数	23
FLOAT	4字节单精度浮点数	1.0
DOUBLE	8字节双精度浮点数	1.0
DEICIMAL	意精度的带符号小数（从Hive0.11.0开始支持）	1.0
STRING	字符串，变长	“a”,’b’
VARCHAR	变长字符串（从Hive0.12.0开始支持）	“a”,’b’
CHAR	固定长度字符串（从Hive0.13.0开始支持）	“a”,’b’
BINARY	字节数组
TIMESTAMP	时间戳，纳秒精度 (Hive 0.8.0以上才可用)	122327493795
DATE	日期 (Hive 0.8.0以上才可用)	‘2016-03-29’

2、复杂类型

Hive表复杂类型

类型	描述	示例
ARRAY	有序的的同类型的集合	array(1,2)
MAP	key-value,key必须为原始类型，value可以任意类型	map(‘a’,1,’b’,2)
STRUCT	字段集合,类型可以不同	struct(‘1’,1,1.0), named_stract(‘col1’,’1’,’col2’,1,’clo3’,1.0)
UNION	在有限取值范围内的一个值	create_union(1,’a’,63)

二、Hive常用SQL

1、复制表结构

CREATE TABLE IF NOT EXISTS ods_uba.linlin LIKE ods_uba.lin_test; 

2、查看表结构

DESCRIBE TABLE ods_uba.linlin;

或者使用

SHOW CREATE TABLLE  ods_uba.linlin

3、删除表：

DROP TABLE ods_uba.linlin

4、修改列名

方法：

ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]

示例

alter table bus_req_prc change fr_evt_type fr_evt_name string;

5、增加一列

方法：

ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [CONMMENT col_comment], ...);

示例

ALTER TABLE visit_log add COLUMNS (url string)
alter table bus_req_prc change session_id session_id string after openid;

6、修改表名

ALTER TABLE bus_req_prc RENAME TO bus_req_prc_before

7、创建视图：
Hive中的视图(view)的概念和传统数据库中的表是相同的，是只读的，目前还不支持不支持物化视图。
如果在创建试图后，再将基本表中被视图引用的列修改，那么修改后的数据列将不会体现在视图中；如果基本表被删除或以不兼容的方式被修改，则查询该视图时会失败。

CREATE VIEW IF NOT EXISTS view_students (user_no,name,score) 
AS 
 SELECT user_no,name,score FROM students; 

8、删除视图：

DROP VIEW view_students; 

9、改变表的数据存储格式

 ALTER TABLE test SET FILEFORMAT SEQUENCEFILE; 

10、添加分区Partition

ALTER TABLE students ADD PARTITION(ds = '2013-05-07',country = 'china') LOCATION '/usr/test/data/test.txt'; 

11、删除分区Partition

 ALTER TABLE students DROP PARTITION(ds = '2013-05-07',country = 'china'); 

12、创建函数

CREATE TEMPORARY FIUNCTION aFunc as class_name; 

13、删除函数

DROP TEMPORARY FIUNCTION aFunc; 

14、显示分区

 SHOW PARTITIONS students; 

三、Hive常用字符串函数

字符串函数

•字符串长度函数：length
•字符串反转函数：reverse
•字符串连接函数：concat
• 带分隔符字符串连接函数：concat_ws
• 字符串截取函数：substr,substring
• 字符串截取函数：substr,substring
• 字符串转大写函数：upper,ucase
• 字符串转小写函数：lower,lcase
• 去空格函数：trim
• 左边去空格函数：ltrim
• 右边去空格函数：rtrim
•正则表达式替换函数：regexp_replace
•正则表达式解析函数：regexp_extract
•URL解析函数：parse_url
•json解析函数：get_json_object
•空格字符串函数：space
•重复字符串函数：repeat
•首字符ascii函数：ascii
•左补足函数：lpad
•右补足函数：rpad
•分割字符串函数: split
•集合查找函数: find_in_set
• 字符串长度函数： length
语法: length(string A)
返回值: int
说明：返回字符串A的长度
举例：
hive> select length('abcedfg') from dual;
7
• 字符串反转函数： reverse
语法: reverse(string A)
返回值: string
说明：返回字符串A的反转结果
举例：
hive> select reverse(abcedfg’) from dual;
gfdecba
• 字符串连接函数： concat
语法: concat(string A, string B…)
返回值: string
说明：返回输入字符串连接后的结果，支持任意个输入字符串
举例：
hive> select concat(‘abc’,'def’,'gh’) from dual;
abcdefgh
• 带分隔符字符串连接函数： concat_ws
语法: concat_ws(string SEP, string A, string B…)
返回值: string
说明：返回输入字符串连接后的结果，SEP表示各个字符串间的分隔符
举例：
hive> select concat_ws(',','abc','def','gh') from dual;
abc,def,gh
• 字符串截取函数： substr,substring
语法: substr(string A, int start),substring(string A, int start)
返回值: string
说明：返回字符串A从start位置到结尾的字符串
举例：
hive> select substr('abcde',3) from dual;
cde
hive> select substring('abcde',3) from dual;
cde
hive> select substr('abcde',-1) from dual; （和ORACLE相同）
e
• 字符串截取函数： substr,substring
语法: substr(string A, int start, int len),substring(string A, int start, int len)
返回值: string
说明：返回字符串A从start位置开始，长度为len的字符串
举例：
hive> select substr('abcde',3,2) from dual;
cd
hive> select substring('abcde',3,2) from dual;
cd
hive>select substring('abcde',-2,2) from dual;
de
• 字符串转大写函数： upper,ucase
语法: upper(string A) ucase(string A)
返回值: string
说明：返回字符串A的大写格式
举例：
hive> select upper('abSEd') from dual;
ABSED
hive> select ucase('abSEd') from dual;
ABSED
• 字符串转小写函数： lower,lcase
语法: lower(string A) lcase(string A)
返回值: string
说明：返回字符串A的小写格式
举例：
hive> select lower('abSEd') from dual;
absed
hive> select lcase('abSEd') from dual;
absed
• 去空格函数： trim
语法: trim(string A)
返回值: string
说明：去除字符串两边的空格
举例：
hive> select trim(' abc ') from dual;
abc
• 左边去空格函数： ltrim
语法: ltrim(string A)
返回值: string
说明：去除字符串左边的空格
举例：
hive> select ltrim(' abc ') from dual;
abc
• 右边去空格函数： rtrim
语法: rtrim(string A)
返回值: string
说明：去除字符串右边的空格
举例：
hive> select rtrim(' abc ') from dual;
abc
• 正则表达式替换函数： regexp_replace
语法: regexp_replace(string A, string B, string C)
返回值: string
说明：将字符串A中的符合java正则表达式B的部分替换为C。注意，在有些情况下要使用转义字符,类似oracle中的regexp_replace函数。
举例：
hive> select regexp_replace('foobar', 'oo|ar', '') from dual;
fb
• 正则表达式解析函数： regexp_extract
语法: regexp_extract(string subject, string pattern, int index)
返回值: string
说明：将字符串subject按照pattern正则表达式的规则拆分，返回index指定的字符。
举例：
hive> select regexp_extract('foothebar', 'foo(.*?)(bar)', 1) from dual;
the
hive> select regexp_extract('foothebar', 'foo(.*?)(bar)', 2) from dual;
bar
hive> select regexp_extract('foothebar', 'foo(.*?)(bar)', 0) from dual;
foothebar
注意，在有些情况下要使用转义字符，下面的等号要用双竖线转 义，这是 java 正则表达式的规则。
select data_field,
regexp_extract(data_field,'.*?bgStart\\=([^&]+)',1) as aaa,
regexp_extract(data_field,'.*?contentLoaded_headStart\\=([^&]+)',1) as bbb,
regexp_extract(data_field,'.*?AppLoad2Req\\=([^&]+)',1) as ccc
from pt_nginx_loginlog_st
where pt = '2012-03-26' limit 2;
• URL 解析函数： parse_url
语法: parse_url(string urlString, string partToExtract [, string keyToExtract])
返回值: string
说明：返回URL中指定的部分。partToExtract的有效值为：HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO.
举例：
hive> select parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST') from dual;
facebook.com
hive> select parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'QUERY', 'k1') from dual;
v1
• json 解析函数： get_json_object
语法: get_json_object(string json_string, string path)
返回值: string
说明：解析json的字符串json_string,返回path指定的内容。如果输入的json字符串无效，那么返回NULL。
举例：
hive> select get_json_object('{"store":
> {"fruit":\[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}],
> "bicycle":{"price":19.95,"color":"red"}
> },
> "email":"amy@only_for_json_udf_test.net",
> "owner":"amy"
> }
> ','$.owner') from dual;
amy
• 空格字符串函数： space
语法: space(int n)
返回值: string
说明：返回长度为n的字符串
举例：
hive> select space(10) from dual;
hive> select length(space(10)) from dual;
10
• 重复字符串函数： repeat
语法: repeat(string str, int n)
返回值: string
说明：返回重复n次后的str字符串
举例：
hive> select repeat('abc',5) from dual;
abcabcabcabcabc
• 首字符 ascii 函数： ascii
语法: ascii(string str)
返回值: int
说明：返回字符串str第一个字符的ascii码
举例：
hive> select ascii('abcde') from dual;
97
• 左补足函数： lpad
语法: lpad(string str, int len, string pad)
返回值: string
说明：将str进行用pad进行左补足到len位
举例：
hive> select lpad('abc',10,'td') from dual;
tdtdtdtabc
注意：与 GP ， ORACLE 不同， pad 不能默认
• 右补足函数： rpad
语法: rpad(string str, int len, string pad)
返回值: string
说明：将str进行用pad进行右补足到len位
举例：
hive> select rpad('abc',10,'td') from dual;
abctdtdtdt
• 分割字符串函数 : split
语法: split(string str, string pat)
返回值: array
说明: 按照pat字符串分割str，会返回分割后的字符串数组
举例：
hive> select split('abtcdtef','t') from dual;
["ab","cd","ef"]
• 集合查找函数 : find_in_set
语法: find_in_set(string str, string strList)
返回值: int
说明: 返回str在strlist第一次出现的位置，strlist是用逗号分割的字符串。如果没有找该str字符，则返回0
举例：
hive> select find_in_set('ab','ef,ab,de') from dual;
2
hive> select find_in_set('at','ef,ab,de') from dual;
0