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

阳光穿透心脏的1/2处 2021-09-11 06:52 344阅读 0赞

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

# 一、Hive数据类型 #

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

## 1、基本类型 ##

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

## 2、复杂类型 ##

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

# 二、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