hive 全面学习

拼搏现实的明天。 2022-03-28 15:30 358阅读 0赞

1.Hive CLI（hive命令行 command line）  
hive命令行选项：  
\-d k=v (定义变量)  -e "" -f filename  -h host -p port -v (控制台显示执行的hql)  
  
hive交互模式：  
set;显示hive中的所有变量，例如set mapred.reduce.tasks=32;  
set k=v :如果k不存在，不会报错  
! shell command :hive交互模式执行shell，例如 ： ! echo aa  
dfs command  ：hive交互模式执行hadoop fs 的命令，和hadoop fs 命令相同  
  
set:输出hive设置的变量  
  
数据类型：  
Integers  
    TINYINT - 1 byte integer  
    SMALLINT - 2 byte integer  
    INT - 4 byte integer  
    BIGINT - 8 byte integer  
Boolean type  
    BOOLEAN - TRUE/FALSE  
Floating point numbers  
    FLOAT - single precision  
    DOUBLE - Double precision  
String type  
    STRING - sequence of characters in a specified character set  
Complex Types  
Structs  STRUCT \{a INT; b INT\} c.a          struct\_type : STRUCT < col\_name : data\_type \[COMMENT col\_comment\], ...>  
Maps M\['group'\]  
Arrays The elements in the array have to be in the same type \['a', 'b', 'c'\], A\[1\] retruns 'b'.  
union: UNIONTYPE<data\_type, data\_type, ...>  
SELECT create\_union(0, key), create\_union(if(key<100, 0, 1), 2.0, value), create\_union(1, "a", struct(2, "b")) FROM src LIMIT 2;  
union\_type  
   : UNIONTYPE < data\_type, data\_type, ... >  
  
TIMESTAMP  
Note: Only available starting with Hive 0.8.0  
BINARY  
BINARY (Note: Only available starting with Hive 0.8.0)  
操作符（Built in operators）：  
Relational Operators  
A LIKE B  \_%，sql中的相同  
A RLIKE B ：正则表达式like，例如  'foo' rlike 'f.\*' 返回true，与A REGEXP B 相同  
  
Arithmetic Operators  
A % B A & B（按位与）、A | B （按位或）、A ^ B （按位异或）、~A （按位非）  
Logical Operators  
A AND B A OR B !A NOT A  
  
Operators on Complex Types  
A\[n\] A is an Array and n is an int  
M\[key\] M is a Map<K, V> and key has type K  
S.x S is a struct  
函数：  
round(double a)BIGINT  
floor(double a) BIGINT  
ceil(double a) BIGINT  
rand(), rand(int seed) double  
concat(string A, string B,...)  
substr(string A, int start, int length)  
upper(string A)  
lower(string A)  
trim(string A)  
ltrim(string A)  
rtrim(string A)  
regexp\_replace(string A, string B, string C)  
size(Map<K.V>) returns the number of elements in the map type  
size(Array<T>)  
cast(<expr> as <type>)  和mysql中的相同  
from\_unixtime(int unixtime)  
to\_date(string timestamp) to\_date("1970-01-01 00:00:00") = "1970-01-01"  
year(string date) year("1970-01-01") = 1970  
month(string date)  month("1970-11-01") = 11  
day(string date) day("1970-11-01") = 1 ,相当于dayofmonth()  
hour()/minute()/second()  
weekofyear(string date)  
  
get\_json\_object(string json\_string, string path)  
  
aggregate functions  
count(\*), count(expr), count(DISTINCT expr\[, expr\_.\])  
sum(col), sum(DISTINCT col)  
avg(col), avg(DISTINCT col)  
min(col)  
max(col)  
  
创建表：  
    CREATE \[EXTERNAL\] TABLE \[if not exists\] page\_view(viewTime INT comment '', userid BIGINT,  
                    page\_url STRING, referrer\_url STRING,  
                    friends ARRAY<BIGINT>, properties MAP<STRING, STRING>  
                    ip STRING COMMENT 'IP Address of the User')  
    COMMENT 'This is the page view table'  
    PARTITIONED BY(dt STRING comment '', country STRING)  
    CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS  表按userid分为32个桶，每个桶中，数据按viewTime进行排序  
    ROW FORMAT DELIMITED  
            FIELDS TERMINATED BY '1'  
            COLLECTION ITEMS TERMINATED BY '2'  
            MAP KEYS TERMINATED BY '3'  
   STORED AS SEQUENCEFILE/TEXTFILE/RCFILE/INPUTFORMAT input\_format\_classname OUTPUTFORMAT output\_format\_classname  
   \[LOCATION hdfs\_path\]  
   \[AS select\_statement\];  
    
  
   注意：  
1.The EXTERNAL keyword lets you create a table and provide a LOCATION so that Hive does not use a default location for this table.  When dropping an EXTERNAL table, data in the table is NOT deleted from the file system.  
2.tables or partitions can be bucketed using CLUSTERED BY columns, and data can be sorted within that bucket via SORT BY columns. This can improve performance on certain kinds of queries.  
3.Table names and column names are case insensitive but SerDe and property names are case sensitive.  
4.The CLUSTERED BY and SORTED BY creation commands do not affect how data is inserted into a table – only how it is read. This means that users must be careful to insert data correctly by specifying the number of reducers to be equal to the number of buckets, and using CLUSTER BY and SORT BY commands in their query.  
如何往分桶的表中插入数据：  
set hive.enforce.bucketing = true;   
FROM user\_id  
INSERT OVERWRITE TABLE user\_info\_bucketed  
PARTITION (ds='2009-02-25')  
SELECT userid, firstname, lastname WHERE ds='2009-02-25';  
  
修改表：Alter Table/Partition Statements  
修改分区：  
ALTER TABLE table\_name ADD \[IF NOT EXISTS\] PARTITION partition\_spec \[LOCATION 'location1'\] partition\_spec \[LOCATION 'location2'\] ...，该语法1次不能添加多个分区  
partition\_spec: (partition\_col = partition\_col\_value, partition\_col = partiton\_col\_value, ...)  
ALTER TABLE table\_name DROP \[IF EXISTS\] partition\_spec, partition\_spec,...  
ALTER TABLE table\_name \[PARTITION partitionSpec\] SET LOCATION "new location"  
Alter Table/Partition Protections  
ALTER TABLE table\_name \[PARTITION partition\_spec\] ENABLE|DISABLE NO\_DROP;  
ALTER TABLE table\_name \[PARTITION partition\_spec\] ENABLE|DISABLE OFFLINE;  
修改表字段：  
ALTER TABLE table\_name CHANGE \[COLUMN\] col\_old\_name col\_new\_name column\_type \[COMMENT col\_comment\] \[FIRST|AFTER column\_name\]  
ALTER TABLE table\_name ADD|REPLACE COLUMNS (col\_name data\_type \[COMMENT col\_comment\], ...)  
Alter Table Properties(给表中增加自己的元数据)  
ALTER TABLE table\_name SET TBLPROPERTIES table\_properties  
Alter Table (Un)Archive  
ALTER TABLE table\_name \[PARTITION partition\_spec\] ENABLE|DISABLE NO\_DROP;  
ALTER TABLE table\_name \[PARTITION partition\_spec\] ENABLE|DISABLE OFFLINE;  
ALTER TABLE table\_name PARTITION partition\_spec RENAME TO PARTITION partition\_spec;  
视图：  
CREATE VIEW \[IF NOT EXISTS\] view\_name \[(column\_name \[COMMENT column\_comment\], ...) \]  
\[COMMENT view\_comment\]  
\[TBLPROPERTIES (property\_name = property\_value, ...)\]  
AS SELECT ...  
  
DROP VIEW \[IF EXISTS\] view\_name  
ALTER VIEW view\_name SET TBLPROPERTIES table\_properties  
注意：  
1.此视图只是逻辑上的，目前不支持物化视图  
2.如果视图的基表被删除，视图的schema不会被改变，使用视图时会出错。  
3.视图是只读的。  
函数：  
add files ...添加jar到hive的classpath  
CREATE TEMPORARY FUNCTION function\_name AS class\_name  （使用的类必须包含在classpath中）  
DROP TEMPORARY FUNCTION \[IF EXISTS\] function\_name  
索引：  
CREATE INDEX index\_name  
ON TABLE base\_table\_name (col\_name, ...)  
AS index\_type  
\[WITH DEFERRED REBUILD\]  
\[IDXPROPERTIES (property\_name=property\_value, ...)\]  
\[IN TABLE index\_table\_name\]  
\[  
   \[ ROW FORMAT ...\] STORED AS ...  
   | STORED BY ...  
\]  
\[LOCATION hdfs\_path\]  
\[TBLPROPERTIES (...)\]  
\[COMMENT "index comment"\]  
  
DROP INDEX \[IF EXISTS\] index\_name ON table\_name  
  
详情见：https://cwiki.apache.org/confluence/display/Hive/IndexDev\#CREATE\_INDEX  
Show/Describe Statements  
show databases/tables \[like 'RegExp'\]  ，此处为正则表达式  
show partitions tableName \[PARTITION(ds='2010-03-03')\]  
show tblproperties tableName Hive 0.10.0  
SHOW FUNCTIONS "a.\*"  
SHOW \[FORMATTED\] (INDEX|INDEXES) ON table\_with\_index \[(FROM|IN) db\_name\]  
SHOW COLUMNS (FROM|IN) table\_name \[(FROM|IN) db\_name\],输出所有列，包括分区列 Version information As of Hive 0.10  
desc database xl\_netdisk\_ods;  
加载数据：  
     1:数据加载到表：  
Standard syntax:  
   LOAD DATA \[LOCAL\] INPATH 'filepath' \[OVERWRITE\] INTO TABLE tablename \[PARTITION (partcol1=val1, partcol2=val2 ...)\]，如果不使用overwrite，文件名不冲突的情况下原先数据依然存在，否则将被替换  
   INSERT OVERWRITE TABLE tablename1 \[PARTITION (partcol1=val1, partcol2=val2 ...) \[IF NOT EXISTS\]\] select\_statement1 FROM from\_statement;  
   INSERT INTO TABLE tablename1 \[PARTITION (partcol1=val1, partcol2=val2 ...)\] select\_statement1 FROM from\_statement;  
Hive extension (multiple inserts):  
FROM from\_statement  
INSERT OVERWRITE TABLE tablename1 \[PARTITION (partcol1=val1, partcol2=val2 ...) \[IF NOT EXISTS\]\] select\_statement1  
\[INSERT OVERWRITE TABLE tablename2 \[PARTITION ... \[IF NOT EXISTS\]\] select\_statement2\]  
\[INSERT INTO TABLE tablename2 \[PARTITION ...\] select\_statement2\] ...;  
Hive extension (dynamic partition inserts):  
INSERT OVERWRITE TABLE tablename PARTITION (partcol1\[=val1\], partcol2\[=val2\] ...) select\_statement FROM from\_statement;  
INSERT INTO TABLE tablename PARTITION (partcol1\[=val1\], partcol2\[=val2\] ...) select\_statement FROM from\_statement;  
  
注意：  
1.INSERT OVERWRITE will overwrite any existing data in the table or partition unless IF NOT EXISTS is provided for a partition (as of Hive 0.9.0)  
INSERT INTO will append to the table or partition keeping the existing data in tact. (Note: INSERT INTO syntax is only available starting in version 0.8)  
     2:数据加载到目录：  
Standard syntax:  
INSERT OVERWRITE \[LOCAL\] DIRECTORY directory1 SELECT ... FROM ...  
  
Hive extension (multiple inserts):  
FROM from\_statement  
INSERT OVERWRITE \[LOCAL\] DIRECTORY directory1 select\_statement1  
  
     3.例子：  
\[INSERT OVERWRITE \[LOCAL\] DIRECTORY directory2 select\_statement2\] ...  
   LOAD DATA LOCAL INPATH /tmp/pv\_2008-06-08\_us.txt INTO TABLE page\_view PARTITION(date='2008-06-08', country='US')  
   LOAD DATA INPATH '/user/data/pv\_2008-06-08\_us.txt' INTO TABLE page\_view PARTITION(date='2008-06-08', country='US')  
   INSERT OVERWRITE TABLE user\_active  SELECT user.\*FROM user WHERE user.active = 1;  
  
In order check the existence of a key in another table, the user can use LEFT SEMI JOIN as illustrated by the following example.  
  
    INSERT OVERWRITE TABLE pv\_users  
    SELECT u.\*  
    FROM user u LEFT SEMI JOIN page\_view pv ON (pv.userid = u.id)  
    WHERE pv.date = '2008-03-03';  
  
however, the following query is not allowed  
  
    INSERT OVERWRITE TABLE pv\_gender\_agg  
    SELECT pv\_users.gender, count(DISTINCT pv\_users.userid), count(DISTINCT pv\_users.ip) 一个select中只能有一个distinct  
    FROM pv\_users  
    GROUP BY pv\_users.gender;  
  
hive中in，exists子查询的替代：  
SELECT a.key, a.value FROM a WHERE a.key in (SELECT b.key FROM B);  
可以被重写为：  
SELECT a.key, a.val FROM a LEFT SEMI JOIN b on (a.key = b.key)  
Left Semi join(左半连接) 当第二个（底端）输入中有匹配行时，Left Semi Join 逻辑运算符返回第一个（顶端）输入中的每行。如果Argument列内不存在任何联接谓词，则每行都是一个匹配行。  
Multi Table/File Inserts  
    FROM pv\_users  
    INSERT OVERWRITE TABLE pv\_gender\_sum  
SELECT pv\_users.gender, count\_distinct(pv\_users.userid)  
GROUP BY pv\_users.gender  
  
    INSERT OVERWRITE DIRECTORY '/user/data/tmp/pv\_age\_sum'  
SELECT pv\_users.age, count\_distinct(pv\_users.userid)  
GROUP BY pv\_users.age;  
  
    FROM page\_view\_stg pvs  
    INSERT OVERWRITE TABLE page\_view PARTITION(dt='2008-06-08', country='US')  
   SELECT pvs.viewTime, pvs.userid, pvs.page\_url, pvs.referrer\_url, null, null, pvs.ip WHERE pvs.country = 'US'  
    INSERT OVERWRITE TABLE page\_view PARTITION(dt='2008-06-08', country='CA')  
   SELECT pvs.viewTime, pvs.userid, pvs.page\_url, pvs.referrer\_url, null, null, pvs.ip WHERE pvs.country = 'CA'  
    INSERT OVERWRITE TABLE page\_view PARTITION(dt='2008-06-08', country='UK')  
   SELECT pvs.viewTime, pvs.userid, pvs.page\_url, pvs.referrer\_url, null, null, pvs.ip WHERE pvs.country = 'UK';  
Dynamic-partition Insert（动态分区插入，自动创建分区，解决了需要预先知晓分区的问题，往多个分区插入数据，不需要多个job作业，0.6之后版本的功能）  
FROM page\_view\_stg pvs  
    INSERT OVERWRITE TABLE page\_view PARTITION(dt='2008-06-08', country) 注意此处没有写明country的值，会自动创建分区并插入值  
   SELECT pvs.viewTime, pvs.userid, pvs.page\_url, pvs.referrer\_url, null, null, pvs.ip, pvs.country  
动态分区，只能为分区的最后一个列，只能是最后一个子分区，不能是这样（dt, country='US') ,动态分区需要在select指定分区列，静态分区不需要。如果被插入的分区已经存在，数据被重写  
否则不被重写。如果分区列的值为null或‘’，数据会被插入到默认分区\_\_HIVE\_DEFAULT\_PARTITION\_\_，此数据被认为是坏数据。  
注意：每一个节点(mapper or reducer)创建的分区数不能超过100个，通过DML创建的总的分区数不能超过1000个，文件数不能超过10w个，都可以通过参数配置而改变。hive默认不允许所有的分区  
都是动态的，可以通过改变hive.exec.dynamic.partition.mode=nonstrict来改变这种状况  
  
hive> set hive.exec.dynamic.partition.mode=nonstrict;  
hive> FROM page\_view\_stg pvs  
  INSERT OVERWRITE TABLE page\_view PARTITION(dt, country)  
SELECT pvs.viewTime, pvs.userid, pvs.page\_url, pvs.referrer\_url, null, null, pvs.ip,  
from\_unixtimestamp(pvs.viewTime, 'yyyy-MM-dd') ds, pvs.country  
DISTRIBUTE BY ds, country;  每个map或reduce产生的分区数超过100，可以将分区均衡，映射到不同的reduce，使用 distribute by  
This query will generate a MapReduce job rather than Map-only job. The SELECT-clause will be converted to a plan to the mappers and the output will be distributed to the reducers based on the value of (ds, country) pairs. The INSERT-clause will be converted to the plan in the reducer which writes to the dynamic partitions.  
查询：  
注意：  
1.sort by，order by区别，sort by只保证单个reduce中有序，order by保证整体有序，整体有序是在牺牲性能的情况下保证，reduce的数量为1  
2.sort按照字段的类型进行排序，如果用字符串存储数值，则需要将其转换为非字符串类型之后再排序  
3.如果想所有的相同的key在同一个reduce中，使用 cluster by ..,或使用 distribute by .. sort by ..,前者是后者的缩写形式，一般distribute by后面的列为sort by的前缀  
4.分区的使用和mysql一样，自动选择分区。分区的选择，where中和join之后的on中可以使用  
  
连接：  
1.多个join转为1个map/reduce作业。Hive converts joins over multiple tables into a single map/reduce job if for every table the same column is used in the join clauses e.g.SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)  
2.在join中的每一个map/reduce阶段，缓存前面的表，后面的表的数据依次流入，如果有多个作业，缓存之前的结果，后面的表数据依次流入reduce，如果想要改变缓存的表，给一个hint，  
例如：SELECT /\*+ STREAMTABLE(a) \*/ a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)，本来b表中数据依次流入，改为a表中数据依次流入  
3.Joins occur BEFORE WHERE CLAUSES，注意左、右、外连接的问题，条件放在on中和where中结果不相同，只要知道join和where的顺序，以及外连接的含义，结果很容易理解  
先进行on条件筛选，再连接，之后where，on后的筛选条件主要是针对从表，对主表不起作用，因为是外关联，主表数据都会输出，对于主表的筛选条件应该放在where后面，如果  
觉得主表不需要关联的数据太多，可以使用子查询，先过滤主表中无用数据  
4.只是用一个map完成join，注意有限制。 SELECT /\*+ MAPJOIN(b) \*/ a.key, a.value FROM a join b on a.key = b.key，对于a的每一个map，b完全读入内存，只需要map就可以完成join操作。 The restriction is that a FULL/RIGHT OUTER JOIN b cannot be performed  
如果join的表都很大，但是在join的字段上进行了分桶，而且一个的桶数是另一个的倍数，则也可以进行mapjoin  
侧视图(lateral view)：用来列转行  
lateralView: LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias)\*  
fromClause: FROM baseTable (lateralView)\*  
取出样例数据:tablesample  
table\_sample: TABLESAMPLE (BUCKET x OUT OF y \[ON colname\]),将数据分为y桶，取出第x桶，如果建表时表没被分桶，则会扫描全表，进行分桶，colname可以为rand()  
block\_sample: TABLESAMPLE (n PERCENT) 取出n%的数据量，不是%n行  
SELECT \* FROM source TABLESAMPLE(BUCKET 3 OUT OF 32 ON rand()) s;  
SELECT \* FROM source TABLESAMPLE(0.1 PERCENT) s;  
虚拟列：  
INPUT\_\_FILE\_\_NAME，BLOCK\_\_OFFSET\_\_INSIDE\_\_FILE  
查看语句执行过程  
explain \[extended\] query  
  
  
生成采样数据：  
INSERT OVERWRITE TABLE pv\_gender\_sum\_sample  
    SELECT pv\_gender\_sum.\*  
    FROM pv\_gender\_sum TABLESAMPLE(BUCKET 3 OUT OF 32 \[ON userid\]);   
Union all 略  
Array Operations  
Array columns in tables can only be created programmatically currently.  
SELECT pv.friends\[2\] FROM page\_views pv; SELECT pv.userid, size(pv.friends)FROM page\_view pv;  
Map(Associative Arrays) Operations  
Maps provide collections similar to associative arrays. Such structures can only be created programmatically currently.  
INSERT OVERWRITE page\_views\_map  
SELECT pv.userid, pv.properties\['page type'\] FROM page\_views pv;SELECT size(pv.properties) FROM page\_view pv;  
  
distribute by 和 cluster by 的区别：  
Distribute By and Sort By: Instead of specifying "cluster by", the user can specify "distribute by" and "sort by", so the partition columns and sort columns can be different  
Altering Tables  
ALTER TABLE tab1 ADD COLUMNS (c1 INT COMMENT 'a new int column', c2 STRING DEFAULT 'def val');  
Dropping Tables and Partitions  
ALTER TABLE pv\_users DROP PARTITION (ds='2008-08-08')  
  
  
  
  
  
  
Hive内置函数：  
查看函数：show functions;desc function \[extended\] fname;  
  
运算符：  
A \[NOT\] BETWEEN B AND C (as of version 0.9.0)  
函数  
1.数学函数：  
round(double a\[, int d\]):int/double ，pow(a,b),sqrt(),bin(a):返回2进制形式，hex():16进制形式，conv(BIGINT num, int from\_base, int to\_base)  
abs(), pmod(int a, int b),返回正余数，a%b如果余数为负，返回负余数，degrees(double a) 弧度转为度，radians(double a)，e(),pi(),sign()符号函数  
std(),stddev()  
2.Collection Functions  
size(Map<K.V>),size(Array<T>),map\_keys(Map<K.V>),map\_values(Map<K.V>),array\_contains(Array<T>, value),sort\_array(Array<T>)：按自然顺序(as of version 0.9.0)  
array(n0, n1...) - Creates an array with the given elements  
3.Type Conversion Functions  
cast(expr as <type>)  
4.Date Functions  
from\_unixtime(bigint unixtime\[, string format\])  
unix\_timestamp(string date, string pattern)  
weekofyear(string date),  
datediff(string enddate, string startdate),  
date\_add(string startdate, int days),  
date\_sub(string startdate, int days)  
from\_utc\_timestamp(timestamp, string timezone)  
to\_utc\_timestamp(timestamp, string timezone)  
date\_format  
  
5.Conditional Functions  
if(boolean testCondition, T valueTrue, T valueFalseOrNull)  
COALESCE(a1, a2, ...) - Returns the first non-null argument，如果参数全为null，返回null。D.J.\[kəʊəles\] 可以用来替代ifnull，  
CASE a WHEN b THEN c \[WHEN d THEN e\]\* \[ELSE f\] END When a = b, returns c; when a = d, return e; else return f  
CASE WHEN a THEN b \[WHEN c THEN d\]\* \[ELSE e\] END When a = true, returns b; when c = true, return d; else return e  
  
6.String Functions  
concat\_ws(string SEP, string A, string B...) ，可以使用自定义分隔符  
find\_in\_set(string str, string strList)  find\_in\_set('ab', 'abc,b,ab,c,def') returns 3  
format\_number(number x, int d) Formats the number X to a format like '\#,\#\#\#,\#\#\#.\#\#', rounded to D decimal places(as of Hive 0.10.0)  
get\_json\_object(string json\_string, string path) ，跟对象的名字为$  
json\_tuple(jsonStr, p1, p2, ..., pn) - like get\_json\_object, but it takes multiple names and return a tuple. All the input parameters and output column types are string.  
in\_file(string str, string filename)  
instr(string str, string substr) ,locate(string substr, string str\[, int pos\])  
lpad(string str, int len, string pad)  lpad('a',3,'b'):bba，rpad(),ltrim(),rtrim(),trim()  
ngrams(array<array<string>>, int N, int K, int pf) =================  
parse\_url(string urlString, string partToExtract \[, string keyToExtract\])  Returns the specified part from the URL. Valid values for partToExtract include HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO.  
parse\_url('http://facebook.com/path1/p.php?k1=v1&k2=v2\#Ref1', 'HOST') : 'facebook.com',parse\_url('http://facebook.com/path1/p.php?k1=v1&k2=v2\#Ref1', 'QUERY', 'k1') returns 'v1'.  
parse\_url\_tuple(url, partname1, partname2, ..., partnameN) - extracts N (N>=1) parts from a URL.  
SELECT b.\* FROM src LATERAL VIEW parse\_url\_tuple(fullurl, 'HOST', 'PATH', 'QUERY', 'QUERY:id') b as host, path, query, query\_id LIMIT 1;  
SELECT parse\_url\_tuple(a.fullurl, 'HOST', 'PATH', 'QUERY', 'REF', 'PROTOCOL', 'FILE',  'AUTHORITY', 'USERINFO', 'QUERY:k1') as (ho, pa, qu, re, pr, fi, au, us, qk1) from src a;  
printf(String format, Obj... args) (as of Hive 0.9.0)  
regexp\_extract('foothebar', 'foo(.\*?)(bar)', n)  抽取第n组的数据，例如regexp\_extract('foothebar', 'foo(.\*?)(bar)', 2) returns 'bar.'  
regexp\_replace(string INITIAL\_STRING, string PATTERN, string REPLACEMENT)  
repeat(string str, int n)  Repeat str n times  
reverse(string A)  
sentences(string str, string lang, string locale) :array<array<string>>  
space(int n)  
split(string str, string pat)：array Split str around pat (pat is a regular expression)  
str\_to\_map(text\[, delimiter1, delimiter2\]) Splits text into key-value pairs using two delimiters. Delimiter1 separates text into K-V pairs, and Delimiter2 splits each K-V pair. Default delimiters are ',' for delimiter1 and '=' for delimiter2.  
substr(string|binary A, int start, int len)或substring(string|binary A, int start, int len)  
translate(string input, string from, string to)  
upper(),lower()  
groupconcat()  
map\_keys()  
map\_values()  
  
Misc. Functions  
varies java\_method(class, method\[, arg1\[, arg2..\]\])Synonym for reflect (as of Hive 0.9.0)  
varies reflect(class, method\[, arg1\[, arg2..\]\]) Use this UDF to call Java methods by matching the argument signature (uses reflection). (as of Hive 0.7.0)  
  
XPath Functions（从xml格式中获取数据）  
xpath, xpath\_short, xpath\_int, xpath\_long, xpath\_float, xpath\_double, xpath\_number, xpath\_string  
  
Built-in Aggregate Functions (UDAF)  
max(),min(),count(),avg(),sum()  
double variance(col), var\_pop(col) Returns the variance of a numeric column in the group  方差  
double var\_samp(col) Returns the unbiased sample variance of a numeric column in the group 样本方差  
double stddev\_pop(col) Returns the standard deviation of a numeric column in the group 标准差  
double stddev\_samp(col) Returns the unbiased sample standard deviation of a numeric column in the group 样本标准差  
double covar\_pop(col1, col2) Returns the population covariance of a pair of numeric columns in the group 协方差  
double covar\_samp(col1, col2) Returns the sample covariance of a pair of a numeric columns in the group 样本协方差  
  
  
Built-in Table-Generating Functions (UDTF)  
Array Type explode(array<TYPE> a) For each element in a, explode() generates a row containing that element  
    No other expressions are allowed in SELECT  
SELECT pageid, explode(adid\_list) AS myCol... is not supported  
    UDTF's can't be nested  
SELECT explode(explode(adid\_list)) AS myCol... is not supported  
    GROUP BY / CLUSTER BY / DISTRIBUTE BY / SORT BY is not supported  
SELECT explode(adid\_list) AS myCol ... GROUP BY myCol is not supported  
  stack(INT n, v\_1, v\_2, ..., v\_k) Breaks up v\_1, ..., v\_k into n rows. Each row will have k/n columns. n must be constant.