Hadoop Hive sql语法详解

最新推荐文章于 2023-05-29 08:05:52 发布

盛装吾步

最新推荐文章于 2023-05-29 08:05:52 发布

阅读量1.3k

点赞数

分类专栏： hive 文章标签： hadoop hive sql 大数据 mapreduce

hive 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

Hadoop知识库

Hive 是基于Hadoop 构建的一套数据仓库分析系统，它提供了丰富的SQL查询方式来分析存储在Hadoop 分布式文件系统中的数据，可以将结构

化的数据文件映射为一张数据库表，并提供完整的SQL查询功能，可以将SQL语句转换为MapReduce任务进行运行，通过自己的SQL 去查询分析需

要的内容，这套SQL 简称Hive SQL，使不熟悉mapreduce 的用户很方便的利用SQL 语言查询，汇总，分析数据。而mapreduce开发人员可以把

己写的mapper 和reducer 作为插件来支持Hive 做更复杂的数据分析。

它与关系型数据库的SQL 略有不同，但支持了绝大多数的语句如DDL、DML 以及常见的聚合函数、连接查询、条件查询。HIVE不适合用于联机

online)事务处理，也不提供实时查询功能。它最适合应用在基于大量不可变数据的批处理作业。

HIVE的特点：可伸缩（在Hadoop的集群上动态的添加设备），可扩展，容错，输入格式的松散耦合。

Hive 的官方文档中对查询语言有了很详细的描述，请参考：http://wiki.apache.org/hadoop/Hive/LanguageManual ，本文的内容大部分翻译自该页面，期间加入了一些在使用过程中需要注意到的事项。

1. DDL 操作

DDL

•建表

•删除表

•修改表结构

•创建／删除视图

•创建数据库

•显示命令

建表：

CREATE[EXTERNAL] TABLE [IF NOT EXISTS] table_name
[(col_name data_type [COMMENT col_comment], ...)]
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
[CLUSTERED BY (col_name, col_name, ...)
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
[ROW FORMAT row_format]
[STORED AS file_format]
[LOCATION hdfs_path]

•CREATE TABLE创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXIST 选项来忽略这个异常

•EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION）

•LIKE 允许用户复制现有的表结构，但是不复制数据

•COMMENT可以为表与字段增加描述

•ROW FORMAT

DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]

[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]

| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value,property_name=property_value, ...)]

用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive 通过 SerDe 确定表的具体的列的数据。

•STORED AS

SEQUENCEFILE

| TEXTFILE

| RCFILE

| INPUTFORMAT input_format_classnameOUTPUTFORMAT output_format_classname

如果文件数据是纯文本，可以使用 STORED ASTEXTFILE。如果数据需要压缩，使用 STORED ASSEQUENCE 。

创建简单表：

hive> CREATETABLE pokes (foo INT, bar STRING);

创建外部表：

CREATEEXTERNAL TABLE page_view(viewTime INT, userid BIGINT,

page_url STRING, referrer_url STRING,

ip STRING COMMENT 'IP Address of the User',

country STRING COMMENT 'country of origination')

COMMENT'This is the staging page view table'

ROWFORMAT DELIMITED FIELDS TERMINATED BY '\054'

STOREDAS TEXTFILE

LOCATION'<hdfs_location>';

建分区表

CREATE TABLEpar_table(viewTime INT, userid BIGINT,

page_url STRING, referrer_url STRING,

ip STRING COMMENT 'IP Address of the User')

COMMENT'This is the page view table'

PARTITIONEDBY(date STRING, pos STRING)

ROW FORMATDELIMITED ‘\t’

FIELDS TERMINATED BY '\n'

STORED ASSEQUENCEFILE;

建Bucket表

CREATE TABLEpar_table(viewTime INT, userid BIGINT,

page_url STRING, referrer_url STRING,

ip STRING COMMENT 'IP Address of the User')

COMMENT'This is the page view table'

PARTITIONEDBY(date STRING, pos STRING)

CLUSTEREDBY(userid) SORTED BY(viewTime) INTO 32 BUCKETS

ROWFORMAT DELIMITED ‘\t’

FIELDS TERMINATED BY '\n'

STORED ASSEQUENCEFILE;

创建表并创建索引字段ds

hive> CREATETABLE invites (foo INT, bar STRING) PARTITIONED BY (ds STRING);

复制一个空表

CREATE TABLEempty_key_value_store

LIKE key_value_store;

例子

create table user_info (user_id int, cid string, ckid string, username string)

row formatdelimited

fieldsterminated by '\t'

linesterminated by '\n';

导入数据表的数据格式是：字段之间是tab键分割，行之间是断行。

及要我们的文件内容格式：

100636 100890 c5c86f4cddc15eb7 yyyvybtvt
100612 100865 97cc70d411c18b6f gyvcycy
100078 100087 ecd6026a15ffddf5 qa000100

显示所有表：

hive> SHOW TABLES;

按正条件（正则表达式）显示表，

hive> SHOW TABLES '.*s';

删除表

当从Hive Metastore删除表，它删除了表/列的数据及其元数据。它可以是一个正常的表(存储在Metastore)或外部表(存储在本地文件系统); 不论什么类型Hive对待的方式相同。

hive> DROP TABLE IF EXISTS employee;

truncate 不能删除外部表！因为外部表里的数据并不是存放在Hive Meta store中

hive> truncate table table_name;

修改表结构

•增加分区、删除分区

•重命名表

•修改列的名字、类型、位置、注释

•增加/更新列

•增加表的元数据信息

表添加一列：

hive> ALTERTABLE pokes ADD COLUMNS (new_col INT);

添加一列并增加列字段注释

hive> ALTERTABLE invites ADD COLUMNS (new_col2 INT COMMENT 'a comment');

更改表名：

hive> ALTERTABLE events RENAME TO 3koobecaf;

删除列：

hive> DROPTABLE pokes;

增加、删除分区

•增加

ALTER TABLEtable_name ADD [IF NOT EXISTS] partition_spec [ LOCATION 'location1' ]partition_spec [ LOCATION 'location2' ] ...

partition_spec:

:PARTITION (partition_col = partition_col_value, partition_col =partiton_col_value, ...)

•删除

ALTER TABLEtable_name DROP partition_spec, partition_spec,...

重命名表

•ALTER TABLEtable_name RENAME TO new_table_name

修改列的名字、类型、位置、注释：

•ALTER TABLEtable_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENTcol_comment] [FIRST|AFTER column_name]

•这个命令可以允许改变列名、数据类型、注释、列位置或者它们的任意组合

表添加一列：

hive> ALTER TABLE pokes ADDCOLUMNS (new_col INT);

添加一列并增加列字段注释

hive> ALTER TABLE invites ADDCOLUMNS (new_col2 INT COMMENT 'a comment');

增加/更新列

•ALTER TABLEtable_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)

• ADD是代表新增一字段，字段位置在所有列后面(partition列前)

REPLACE则是表示替换表中所有字段。

增加表的元数据信息

•ALTER TABLEtable_name SET TBLPROPERTIES table_properties table_properties:

:[property_name = property_value…..]

•用户可以用这个命令向表中增加metadata

改变表文件格式与组织

•ALTER TABLEtable_name SET FILEFORMAT file_format

•ALTER TABLEtable_name CLUSTERED BY(userid) SORTED BY(viewTime) INTO num_buckets BUCKETS

•这个命令修改了表的物理存储属性

创建／删除视图

•CREATE VIEW[IF NOT EXISTS] view_name [ (column_name [COMMENT column_comment], ...)][COMMENT view_comment][TBLPROPERTIES (property_name = property_value,...)] AS SELECT

•增加视图

•如果没有提供表名，视图列的名字将由定义的SELECT表达式自动生成

•如果修改基本表的属性，视图中不会体现，无效查询将会失败

•视图是只读的，不能用LOAD/INSERT/ALTER

•DROP VIEWview_name

•删除视图

创建数据库

•CREATEDATABASE name

显示命令

•show tables;

•showdatabases;

•showpartitions ;

•showfunctions

•describeextended table_name dot col_name

2. DML 操作:元数据存储

hive不支持用insert语句一条一条的进行插入操作，也不支持update操作。数据是以load的方式加载到建立好的表中。数据一旦导入就不可以修改。

DML包括：INSERT插入、UPDATE更新、DELETE删除

•向数据表内加载文件

•将查询结果插入到Hive表中

•0.8新特性 insert into

向数据表内加载文件

•LOAD DATA[LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION(partcol1=val1, partcol2=val2 ...)]

•Load 操作只是单纯的复制/移动操作，将数据文件移动到 Hive 表对应的位置。

•filepath

•相对路径，例如：project/data1

•绝对路径，例如：/user/hive/project/data1

•包含模式的完整 URI，例如：hdfs://namenode:9000/user/hive/project/data1

例如：

hive> LOAD DATA LOCAL INPATH'./examples/files/kv1.txt' OVERWRITE INTO TABLE pokes;

加载本地数据，同时给定分区信息

•加载的目标可以是一个表或者分区。如果表包含分区，必须指定每一个分区的分区名

•filepath 可以引用一个文件（这种情况下，Hive 会将文件移动到表所对应的目录中）或者是一个目录（在这种情况下，Hive 会将目录中的所有文件移动至表所对应的目录中）

LOCAL关键字

•指定了LOCAL，即本地

•load 命令会去查找本地文件系统中的 filepath。如果发现是相对路径，则路径会被解释为相对于当前用户的当前路径。用户也可以为本地文件指定一个完整的 URI，比如：file:///user/hive/project/data1.

•load 命令会将 filepath 中的文件复制到目标文件系统中。目标文件系统由表的位置属性决定。被复制的数据文件移动到表的数据对应的位置

例如：加载本地数据，同时给定分区信息：

hive> LOAD DATA LOCAL INPATH './examples/files/kv2.txt' OVERWRITE INTOTABLE invites PARTITION (ds='2008-08-15');

• 没有指定LOCAL

如果 filepath 指向的是一个完整的 URI，hive 会直接使用这个 URI。否则

•如果没有指定 schema 或者 authority，Hive 会使用在 hadoop 配置文件中定义的 schema 和 authority，fs.default.name 指定了 Namenode 的 URI

•如果路径不是绝对的，Hive 相对于 /user/ 进行解释。 Hive 会将 filepath 中指定的文件内容移动到 table （或者 partition）所指定的路径中

加载DFS数据，同时给定分区信息：

hive> LOAD DATA INPATH'/user/myname/kv2.txt' OVERWRITE INTO TABLE invites PARTITION(ds='2008-08-15');
The above command will load data from an HDFS file/directory to the table. Notethat loading data from HDFS will result in moving the file/directory. As aresult, the operation is almost instantaneous.

OVERWRITE

•指定了OVERWRITE

•目标表（或者分区）中的内容（如果有）会被删除，然后再将 filepath 指向的文件/目录中的内容添加到表/分区中。

•如果目标表（分区）已经有一个文件，并且文件名和 filepath 中的文件名冲突，那么现有的文件会被新文件所替代。

将查询结果插入Hive表

•将查询结果插入Hive表

•将查询结果写入HDFS文件系统

•基本模式

INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2...)] select_statement1 FROM from_statement

•多插入模式

FROMfrom_statement

INSERTOVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)]select_statement1

[INSERTOVERWRITE TABLE tablename2 [PARTITION ...] select_statement2] ...

•自动分区模式

INSERTOVERWRITE TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...)select_statement FROM from_statement

将查询结果写入HDFS文件系统

•INSERTOVERWRITE [LOCAL] DIRECTORY directory1 SELECT ... FROM ...

FROM from_statement

INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1

[INSERT OVERWRITE [LOCAL] DIRECTORY directory2 select_statement2]

•

•数据写入文件系统时进行文本序列化，且每列用^A 来区分，\n换行

INSERT INTO

•INSERTINTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)]select_statement1 FROM from_statement

3. DQL 操作:数据查询SQL

SQL操作

•基本的Select 操作

•基于Partition的查询

•Join

3.1 基本的Select 操作

SELECT [ALL |DISTINCT] select_expr, select_expr, ...

FROMtable_reference

[WHEREwhere_condition]

[GROUP BYcol_list [HAVING condition]]

[ CLUSTER BY col_list

|[DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list]

]

[LIMITnumber]

•使用ALL和DISTINCT选项区分对重复记录的处理。默认是ALL，表示查询所有记录。DISTINCT表示去掉重复的记录

•

•Where 条件

•类似我们传统SQL的where 条件

•目前支持 AND,OR ,0.9版本支持between

•IN, NOT IN

•不支持EXIST ,NOT EXIST

ORDER BY与SORT BY的不同

•ORDER BY 全局排序，只有一个Reduce任务

•SORT BY 只在本机做排序

Limit

•Limit 可以限制查询的记录数

SELECT * FROMt1 LIMIT 5

•实现Top k 查询

•下面的查询语句查询销售记录最大的 5 个销售代表。

SETmapred.reduce.tasks = 1
SELECT * FROM test SORT BY amount DESC LIMIT 5

•REGEX ColumnSpecification

SELECT 语句可以使用正则表达式做列选择，下面的语句查询除了 ds 和 hr 之外的所有列：

SELECT`(ds|hr)?+.+` FROM test

例如

按先件查询

hive> SELECTa.foo FROM invites a WHERE a.ds='<DATE>';

将查询数据输出至目录：

hive> INSERTOVERWRITE DIRECTORY '/tmp/hdfs_out' SELECT a.* FROM invites a WHEREa.ds='<DATE>';

将查询结果输出至本地目录：

hive> INSERTOVERWRITE LOCAL DIRECTORY '/tmp/local_out' SELECT a.* FROM pokes a;

选择所有列到本地目录：

hive> INSERTOVERWRITE TABLE events SELECT a.* FROM profiles a;
hive> INSERT OVERWRITE TABLE events SELECT a.* FROM profiles a WHERE a.key< 100;
hive> INSERT OVERWRITE LOCAL DIRECTORY '/tmp/reg_3' SELECT a.* FROM eventsa;
hive> INSERT OVERWRITE DIRECTORY '/tmp/reg_4' select a.invites, a.pokes FROMprofiles a;
hive> INSERT OVERWRITE DIRECTORY '/tmp/reg_5' SELECT COUNT(1) FROM invites aWHERE a.ds='<DATE>';
hive> INSERT OVERWRITE DIRECTORY '/tmp/reg_5' SELECT a.foo, a.bar FROMinvites a;
hive> INSERT OVERWRITE LOCAL DIRECTORY '/tmp/sum' SELECT SUM(a.pc) FROM pc1a;

将一个表的统计结果插入另一个表中：

hive> FROMinvites a INSERT OVERWRITE TABLE events SELECT a.bar, count(1) WHERE a.foo >0 GROUP BY a.bar;
hive> INSERT OVERWRITE TABLE events SELECT a.bar, count(1) FROM invites aWHERE a.foo > 0 GROUP BY a.bar;
JOIN
hive> FROM pokes t1 JOIN invites t2 ON (t1.bar = t2.bar) INSERT OVERWRITETABLE events SELECT t1.bar, t1.foo, t2.foo;

将多表数据插入到同一表中：

FROM src
INSERT OVERWRITE TABLE dest1 SELECT src.* WHERE src.key < 100
INSERT OVERWRITE TABLE dest2 SELECT src.key, src.value WHERE src.key >= 100and src.key < 200
INSERT OVERWRITE TABLE dest3 PARTITION(ds='2008-04-08', hr='12') SELECT src.keyWHERE src.key >= 200 and src.key < 300
INSERT OVERWRITE LOCAL DIRECTORY '/tmp/dest4.out' SELECT src.value WHEREsrc.key >= 300;

将文件流直接插入文件：

hive> FROMinvites a INSERT OVERWRITE TABLE events SELECT TRANSFORM(a.foo, a.bar) AS (oof,rab) USING '/bin/cat' WHERE a.ds > '2008-08-09';
This streams the data in the map phase through the script /bin/cat (like hadoopstreaming). Similarly - streaming can be used on the reduce side (please seethe Hive Tutorial or examples)

3.2 基于Partition的查询

•一般 SELECT 查询会扫描整个表，使用 PARTITIONED BY 子句建表，查询就可以利用分区剪枝（input pruning）的特性

•Hive 当前的实现是，只有分区断言出现在离 FROM 子句最近的那个WHERE 子句中，才会启用分区剪枝

3.3 Join

Syntax

join_table:
   table_reference JOIN table_factor [join_condition]
| table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_referencejoin_condition
| table_reference LEFT SEMI JOIN table_reference join_condition

table_reference:
    table_factor
| join_table

table_factor:
    tbl_name [alias]
| table_subquery alias
| ( table_references )

join_condition:
    ON equality_expression ( AND equality_expression )*

equality_expression:
    expression = expression

•Hive 只支持等值连接（equality joins）、外连接（outer joins）和（left semi joins）。Hive 不支持所有非等值的连接，因为非等值连接非常难转化到 map/reduce 任务

•LEFT，RIGHT和FULL OUTER关键字用于处理join中空记录的情况

•LEFT SEMIJOIN 是 IN/EXISTS 子查询的一种更高效的实现

•join 时，每次 map/reduce 任务的逻辑是这样的：reducer 会缓存 join 序列中除了最后一个表的所有表的记录，再通过最后一个表将结果序列化到文件系统

•实践中，应该把最大的那个表写在最后

join 查询时，需要注意几个关键点

•只支持等值join

•SELECT a.*FROM a JOIN b ON (a.id = b.id)

•SELECT a.*FROM a JOIN b
ON (a.id = b.id AND a.department = b.department)

•可以 join 多于 2 个表，例如

SELECTa.val, b.val, c.val FROM a JOIN b
ON (a.key = b.key1) JOIN c ON (c.key = b.key2)

•如果join中多个表的 join key 是同一个，则 join 会被转化为单个 map/reduce 任务

LEFT，RIGHT和FULL OUTER

•例子

•SELECTa.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key)

•如果你想限制 join 的输出，应该在 WHERE 子句中写过滤条件——或是在 join 子句中写

•

•容易混淆的问题是表分区的情况

• SELECTc.val, d.val FROM c LEFT OUTER JOIN d ON (c.key=d.key)
WHERE a.ds='2010-07-07' AND b.ds='2010-07-07‘

•如果 d 表中找不到对应 c 表的记录，d 表的所有列都会列出 NULL，包括 ds 列。也就是说，join 会过滤 d 表中不能找到匹配 c 表 join key 的所有记录。这样的话，LEFT OUTER 就使得查询结果与 WHERE 子句无关

•解决办法

•SELECTc.val, d.val FROM c LEFT OUTER JOIN d
ON (c.key=d.key AND d.ds='2009-07-07' AND c.ds='2009-07-07')

LEFT SEMIJOIN

•LEFT SEMIJOIN 的限制是， JOIN 子句中右边的表只能在 ON 子句中设置过滤条件，在 WHERE 子句、SELECT 子句或其他地方过滤都不行

•

•SELECTa.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)

UNION ALL

•用来合并多个select的查询结果，需要保证select中字段须一致

•select_statementUNION ALL select_statement UNION ALL select_statement ...

4. 从SQL到HiveQL应转变的习惯

1、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 WHEREt1.a2 = t2.b2

2、分号字符

•分号是SQL语句结束标记，在HiveQL中也是，但是在HiveQL中，对分号的识别没有那么智慧，例如：

•selectconcat(key,concat(';',key)) from dual;

•但HiveQL在解析语句时提示：

FAILED: Parse Error: line 0:-1 mismatched input '<EOF>' expecting ) infunction specification

•解决的办法是，使用分号的八进制的ASCII码进行转义，那么上述语句应写成：

•selectconcat(key,concat('\073',key)) from dual;

3、IS [NOT] NULL

•SQL中null代表空值, 值得警惕的是, 在HiveQL中String类型的字段若是空(empty)字符串, 即长度为0, 那么对它进行IS NULL的判断结果是False.

4、Hive不支持将数据插入现有的表或分区中，

仅支持覆盖重写整个表，示例如下：

[sql] view plain copy

print ?

1. INSERT OVERWRITE TABLE t1

2. SELECT * FROM t2;

4、hive不支持INSERT INTO, UPDATE, DELETE操作

这样的话，就不要很复杂的锁机制来读写数据。
INSERT INTO syntax is only available starting in version0.8。INSERT INTO就是在表或分区中追加数据。

5、hive支持嵌入mapreduce程序，来处理复杂的逻辑

如：

[sql] view plain copy

print ?

1. FROM (

2. MAP doctext USING 'python wc_mapper.py' AS (word, cnt)

3. FROM docs

4. CLUSTER BY word

5. ) a

6. REDUCE word, cnt USING 'python wc_reduce.py';

--doctext: 是输入

--word, cnt: 是map程序的输出

--CLUSTER BY: 将wordhash后，又作为reduce程序的输入

并且map程序、reduce程序可以单独使用，如：

[sql] view plain copy

print ?

1. FROM (

2. FROM session_table

3. SELECT sessionid, tstamp, data

4. DISTRIBUTE BY sessionid SORT BY tstamp

5. ) a

6. REDUCE sessionid, tstamp, data USING 'session_reducer.sh';

--DISTRIBUTE BY:用于给reduce程序分配行数据

6、hive支持将转换后的数据直接写入不同的表，还能写入分区、hdfs和本地目录。

这样能免除多次扫描输入表的开销。

[sql] view plain copy

print ?

1. FROM t1

3. INSERT OVERWRITE TABLE t2

4. SELECT t3.c2, count(1)

5. FROM t3

6. WHERE t3.c1 <= 20

7. GROUP BY t3.c2

9. INSERT OVERWRITE DIRECTORY '/output_dir'

10.SELECT t3.c2, avg(t3.c1)

11.FROM t3

12.WHERE t3.c1 > 20 AND t3.c1 <= 30

13.GROUP BY t3.c2

14.

15.INSERT OVERWRITE LOCAL DIRECTORY '/home/dir'

16.SELECT t3.c2, sum(t3.c1)

17.FROM t3

18.WHERE t3.c1 > 30

19.GROUP BY t3.c2;

5. 实际示例

创建一个表

CREATE TABLEu_data (
userid INT,
movieid INT,
rating INT,
unixtime STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '/t'
STORED AS TEXTFILE;

下载示例数据文件，并解压缩
wget http://www.grouplens.org/system/files/ml-data.tar__0.gz
tar xvzf ml-data.tar__0.gz

加载数据到表中:

LOAD DATA LOCALINPATH 'ml-data/u.data'
OVERWRITE INTO TABLE u_data;

统计数据总量:

SELECT COUNT(1)FROM u_data;

现在做一些复杂的数据分析:

创建一个 weekday_mapper.py: 文件，作为数据按周进行分割
import sys
import datetime

for line insys.stdin:
line = line.strip()
userid, movieid, rating, unixtime = line.split('/t')

生成数据的周信息

weekday =datetime.datetime.fromtimestamp(float(unixtime)).isoweekday()
print '/t'.join([userid, movieid, rating, str(weekday)])

使用映射脚本

//创建表，按分割符分割行中的字段值
CREATE TABLE u_data_new (
userid INT,
movieid INT,
rating INT,
weekday INT)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '/t';
//将Python文件加载到系统
add FILE weekday_mapper.py;

将数据按周进行分割

INSERT OVERWRITETABLE u_data_new
SELECT
TRANSFORM (userid, movieid, rating, unixtime)
USING 'python weekday_mapper.py'
AS (userid, movieid, rating, weekday)
FROM u_data;

SELECT weekday,COUNT(1)
FROM u_data_new
GROUP BY weekday;

处理Apache Weblog 数据

将WEB日志先用正则表达式进行组合，再按需要的条件进行组合输入到表中
add jar ../build/contrib/hive_contrib.jar;

CREATE TABLEapachelog (
host STRING,
identity STRING,
user STRING,
time STRING,
request STRING,
status STRING,
size STRING,
referer STRING,
agent STRING)
ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
"input.regex" = "([^ ]*) ([^ ]*) ([^ ]*) (-|//[[^//]]*//]) ([^/"]*|/"[^/"]*/") (-|[0-9]*) (-|[0-9]*)(?: ([^/"]*|/"[^/"]*/") ([^/"]*|/"[^/"]*/"))?",
"output.format.string" = "%1$s %2$s %3$s %4$s %5$s %6$s %7$s%8$s %9$s"
)
STORED AS TEXTFILE;

一、关系运算：... 4

1. 等值比较: =. 4

2. 不等值比较: <>. 4

3. 小于比较: <. 4

4. 小于等于比较: <=. 4

5. 大于比较: >. 5

6. 大于等于比较: >=. 5

7. 空值判断: IS NULL. 5

8. 非空判断: IS NOT NULL. 6

9. LIKE比较: LIKE. 6

10. JAVA的LIKE操作: RLIKE. 6

11. REGEXP操作: REGEXP. 7

二、数学运算：... 7

1. 加法操作: +. 7

2. 减法操作: - 7

3. 乘法操作: *. 8

4. 除法操作: /. 8

5. 取余操作: %.. 8

6. 位与操作: &.. 9

7. 位或操作: |. 9

8. 位异或操作: ^. 9

9．位取反操作: ~. 10

三、逻辑运算：... 10

1. 逻辑与操作: AND.. 10

2. 逻辑或操作: OR. 10

3. 逻辑非操作: NOT. 10

四、数值计算... 11

1. 取整函数: round. 11

2. 指定精度取整函数: round. 11

3. 向下取整函数: floor. 11

4. 向上取整函数: ceil 12

5. 向上取整函数: ceiling. 12

6. 取随机数函数: rand. 12

7. 自然指数函数: exp. 13

8. 以10为底对数函数: log10. 13

9. 以2为底对数函数: log2. 13

10. 对数函数: log. 13

11. 幂运算函数: pow.. 14

12. 幂运算函数: power. 14

13. 开平方函数: sqrt. 14

14. 二进制函数: bin. 14

15. 十六进制函数: hex. 15

16. 反转十六进制函数: unhex. 15

17. 进制转换函数: conv. 15

18. 绝对值函数: abs. 16

19. 正取余函数: pmod. 16

20. 正弦函数: sin. 16

21. 反正弦函数: asin. 16

22. 余弦函数: cos. 17

23. 反余弦函数: acos. 17

24. positive函数: positive. 17

25. negative函数: negative. 17

五、日期函数... 18

1. UNIX时间戳转日期函数: from_unixtime.18

2. 获取当前UNIX时间戳函数: unix_timestamp. 18

3. 日期转UNIX时间戳函数: unix_timestamp. 18

4. 指定格式日期转UNIX时间戳函数: unix_timestamp. 18

5. 日期时间转日期函数: to_date. 19

6. 日期转年函数: year. 19

7. 日期转月函数: month. 19

8. 日期转天函数: day. 19

9. 日期转小时函数: hour. 20

10. 日期转分钟函数: minute. 20

11. 日期转秒函数: second. 20

12. 日期转周函数: weekofyear. 20

13. 日期比较函数: datediff 21

14. 日期增加函数: date_add. 21

15. 日期减少函数: date_sub. 21

六、条件函数... 21

1. If函数: if 21

2. 非空查找函数: COALESCE. 22

3. 条件判断函数：CASE. 22

4. 条件判断函数：CASE. 22

七、字符串函数... 23

1. 字符串长度函数：length. 23

2. 字符串反转函数：reverse. 23

3. 字符串连接函数：concat. 23

4. 带分隔符字符串连接函数：concat_ws. 23

5. 字符串截取函数：substr,substring.24

6. 字符串截取函数：substr,substring.24

7. 字符串转大写函数：upper,ucase. 24

8. 字符串转小写函数：lower,lcase. 25

9. 去空格函数：trim.. 25

10. 左边去空格函数：ltrim.. 25

11. 右边去空格函数：rtrim.. 25

12. 正则表达式替换函数：regexp_replace.26

13. 正则表达式解析函数：regexp_extract.26

14. URL解析函数：parse_url 26

15. json解析函数：get_json_object.27

16. 空格字符串函数：space. 27

17. 重复字符串函数：repeat. 27

18. 首字符ascii函数：ascii 28

19. 左补足函数：lpad. 28

20. 右补足函数：rpad. 28

21. 分割字符串函数: split. 28

22. 集合查找函数: find_in_set.29

八、集合统计函数... 29

1. 个数统计函数: count. 29

2. 总和统计函数: sum.. 29

3. 平均值统计函数: avg. 30

4. 最小值统计函数: min. 30

5. 最大值统计函数: max. 30

6. 非空集合总体变量函数: var_pop. 30

7. 非空集合样本变量函数: var_samp. 31

8. 总体标准偏离函数: stddev_pop. 31

9. 样本标准偏离函数: stddev_samp.31

10．中位数函数: percentile. 31

11. 中位数函数: percentile. 31

12. 近似中位数函数: percentile_approx.32

13. 近似中位数函数:percentile_approx. 32

14. 直方图:histogram_numeric. 32

九、复合类型构建操作... 32

1. Map类型构建: map. 32

2. Struct类型构建: struct. 33

3. array类型构建: array. 33

十、复杂类型访问操作... 33

1. array类型访问: A[n] 33

2. map类型访问: M[key] 34

3. struct类型访问: S.x. 34

十一、复杂类型长度统计函数... 34

1. Map类型长度函数: size(Map<K.V>) 34

2. array类型长度函数: size(Array<T>) 34

3. 类型转换函数... 35

一、关系运算：1. 等值比较: =

语法：A=B

操作类型：所有基本类型

描述: 如果表达式A与表达式B相等，则为TRUE；否则为FALSE

举例：

hive>select 1 from lxw_dual where 1=1;

2. 不等值比较: <>

语法: A <> B

操作类型: 所有基本类型

描述: 如果表达式A为NULL，或者表达式B为NULL，返回NULL；如果表达式A与表达式B不相等，则为TRUE；否则为FALSE

举例：

hive> select1 from lxw_dual where 1<> 2;

3. 小于比较: <

语法: A < B

操作类型: 所有基本类型

描述: 如果表达式A为NULL，或者表达式B为NULL，返回NULL；如果表达式A小于表达式B，则为TRUE；否则为FALSE

举例：

hive> select1 from lxw_dual where 1< 2;

4. 小于等于比较: <=

语法: A <= B

操作类型: 所有基本类型

描述: 如果表达式A为NULL，或者表达式B为NULL，返回NULL；如果表达式A小于或者等于表达式B，则为TRUE；否则为FALSE

举例：

hive> select1 from lxw_dual where 1<= 1;

5. 大于比较: >

语法: A > B

操作类型: 所有基本类型

描述: 如果表达式A为NULL，或者表达式B为NULL，返回NULL；如果表达式A大于表达式B，则为TRUE；否则为FALSE

举例：

hive> select1 from lxw_dual where 2> 1;

6. 大于等于比较: >=

语法: A >= B

操作类型: 所有基本类型

描述: 如果表达式A为NULL，或者表达式B为NULL，返回NULL；如果表达式A大于或者等于表达式B，则为TRUE；否则为FALSE

举例：

hive> select1 from lxw_dual where 1>= 1;

注意：String的比较要注意(常用的时间比较可以先to_date之后再比较)

hive> select* from lxw_dual;

201111120900:00:00 2011111209

hive> selecta,b,a<b,a>b,a=bfrom lxw_dual;

201111120900:00:00 2011111209 false true false

7. 空值判断: IS NULL

语法: A IS NULL

操作类型: 所有类型

描述: 如果表达式A的值为NULL，则为TRUE；否则为FALSE

举例：

hive> select1 from lxw_dual wherenull is null;

8. 非空判断: IS NOTNULL

语法: A IS NOT NULL

操作类型: 所有类型

描述: 如果表达式A的值为NULL，则为FALSE；否则为TRUE

举例：

hive> select1 from lxw_dual where 1is not null;

9. LIKE比较: LIKE

语法: A LIKE B

操作类型: strings

描述: 如果字符串A或者字符串B为NULL，则返回NULL；如果字符串A符合表达式B 的正则语法，则为TRUE；否则为FALSE。B中字符”_”表示任意单个字符，而字符”%”表示任意数量的字符。

举例：

hive> select1 from lxw_dual where'football' like 'foot%';

hive> select1 from lxw_dual where'football' like 'foot____';

注意：否定比较时候用NOT ALIKE B

hive> select1 from lxw_dual where NOT'football' like 'fff%';

10. JAVA的LIKE操作: RLIKE

语法: A RLIKE B

操作类型: strings

描述: 如果字符串A或者字符串B为NULL，则返回NULL；如果字符串A符合JAVA正则表达式B的正则语法，则为TRUE；否则为FALSE。

举例：

hive> select1 from lxw_dual where'footbar’ rlike '^f.*r$’;

注意：判断一个字符串是否全为数字：

hive>select 1from lxw_dual where'123456' rlike '^\\d+$';

hive> select1 from lxw_dual where'123456aa' rlike '^\\d+$';

11. REGEXP操作: REGEXP

语法: A REGEXP B

操作类型: strings

描述: 功能与RLIKE相同

举例：

hive> select1 from lxw_dual where'footbar' REGEXP '^f.*r$';

二、数学运算：1. 加法操作: +

语法: A + B

操作类型：所有数值类型

说明：返回A与B相加的结果。结果的数值类型等于A的类型和B的类型的最小父类型（详见数据类型的继承关系）。比如，int + int 一般结果为int类型，而int + double 一般结果为double类型

举例：

hive> select1 + 9 from lxw_dual;

hive> createtable lxw_dual as select1 + 1.2 from lxw_dual;

hive>describe lxw_dual;

_c0 double

2. 减法操作: -

语法: A – B

操作类型：所有数值类型

说明：返回A与B相减的结果。结果的数值类型等于A的类型和B的类型的最小父类型（详见数据类型的继承关系）。比如，int – int 一般结果为int类型，而int – double 一般结果为double类型

举例：

hive> select10 – 5 from lxw_dual;

hive> createtable lxw_dual as select5.6 – 4 from lxw_dual;

hive>describe lxw_dual;

_c0 double

3. 乘法操作: *

语法: A * B

操作类型：所有数值类型

说明：返回A与B相乘的结果。结果的数值类型等于A的类型和B的类型的最小父类型（详见数据类型的继承关系）。注意，如果A乘以B的结果超过默认结果类型的数值范围，则需要通过cast将结果转换成范围更大的数值类型

举例：

hive> select40 * 5 from lxw_dual;

200

4. 除法操作: /

语法: A / B

操作类型：所有数值类型

说明：返回A除以B的结果。结果的数值类型为double

举例：

hive> select40 / 5 from lxw_dual;

8.0

注意：hive中最高精度的数据类型是double,只精确到小数点后16位，在做除法运算的时候要特别注意

hive>selectceil(28.0/6.999999999999999999999) from lxw_duallimit 1;

结果为4

hive>selectceil(28.0/6.99999999999999) from lxw_dual limit1;

结果为5

5. 取余操作: %

语法: A % B

操作类型：所有数值类型

说明：返回A除以B的余数。结果的数值类型等于A的类型和B的类型的最小父类型（详见数据类型的继承关系）。

举例：

hive> select 41 % 5 from lxw_dual;

hive> select 8.4 % 4 from lxw_dual;

0.40000000000000036

注意：精度在hive中是个很大的问题，类似这样的操作最好通过round指定精度

hive> select round(8.4 % 4 , 2) fromlxw_dual;

0.4

6. 位与操作: &

语法: A & B

操作类型：所有数值类型

说明：返回A和B按位进行与操作的结果。结果的数值类型等于A的类型和B的类型的最小父类型（详见数据类型的继承关系）。

举例：

hive> select 4 & 8 from lxw_dual;

hive> select 6 & 4 from lxw_dual;

7. 位或操作: |

语法: A | B

操作类型：所有数值类型

说明：返回A和B按位进行或操作的结果。结果的数值类型等于A的类型和B的类型的最小父类型（详见数据类型的继承关系）。

举例：

hive> select 4 | 8 from lxw_dual;

hive> select 6 | 8 from lxw_dual;

8. 位异或操作: ^

语法: A ^ B

操作类型：所有数值类型

说明：返回A和B按位进行异或操作的结果。结果的数值类型等于A的类型和B的类型的最小父类型（详见数据类型的继承关系）。

举例：

hive> select 4 ^ 8 from lxw_dual;

hive> select 6 ^ 4 from lxw_dual;

9．位取反操作: ~

语法: ~A

操作类型：所有数值类型

说明：返回A按位取反操作的结果。结果的数值类型等于A的类型。

举例：

hive> select ~6 from lxw_dual;

-7

hive> select ~4 from lxw_dual;

-5

三、逻辑运算：1. 逻辑与操作: AND

语法: A AND B

操作类型：boolean

说明：如果A和B均为TRUE，则为TRUE；否则为FALSE。如果A为NULL或B为NULL，则为NULL

举例：

hive> select 1 from lxw_dual where1=1 and 2=2;

2. 逻辑或操作: OR

语法: A OR B

操作类型：boolean

说明：如果A为TRUE，或者B为TRUE，或者A和B均为TRUE，则为TRUE；否则为FALSE

举例：

hive> select 1 from lxw_dual where1=2 or 2=2;

3. 逻辑非操作: NOT

语法: NOT A

操作类型：boolean

说明：如果A为FALSE，或者A为NULL，则为TRUE；否则为FALSE

举例：

hive> select 1 from lxw_dual wherenot 1=2;

四、数值计算1. 取整函数: round

语法: round(doublea)

返回值: BIGINT

说明: 返回double类型的整数值部分（遵循四舍五入）

举例：

hive> select round(3.1415926) fromlxw_dual;

hive> select round(3.5) fromlxw_dual;

hive> create table lxw_dual as selectround(9542.158) fromlxw_dual;

hive> describe lxw_dual;

_c0 bigint

2. 指定精度取整函数: round

语法: round(doublea, int d)

返回值: DOUBLE

说明: 返回指定精度d的double类型

举例：

hive> select round(3.1415926,4) fromlxw_dual;

3.1416

3. 向下取整函数: floor

语法: floor(doublea)

返回值: BIGINT

说明: 返回等于或者小于该double变量的最大的整数

举例：

hive> select floor(3.1415926) fromlxw_dual;

hive> select floor(25) from lxw_dual;

4. 向上取整函数: ceil

语法: ceil(double a)

返回值: BIGINT

说明: 返回等于或者大于该double变量的最小的整数

举例：

hive> select ceil(3.1415926) fromlxw_dual;

hive> select ceil(46) from lxw_dual;

5. 向上取整函数: ceiling

语法: ceiling(doublea)

返回值: BIGINT

说明: 与ceil功能相同

举例：

hive> select ceiling(3.1415926) fromlxw_dual;

hive> select ceiling(46) fromlxw_dual;

6. 取随机数函数: rand

语法:rand(),rand(int seed)

返回值: double

说明: 返回一个0到1范围内的随机数。如果指定种子seed，则会等到一个稳定的随机数序列

举例：

hive> select rand() from lxw_dual;

0.5577432776034763

hive> select rand() from lxw_dual;

0.6638336467363424

hive> select rand(100) from lxw_dual;

0.7220096548596434

hive> select rand(100) from lxw_dual;

0.7220096548596434

7. 自然指数函数: exp

语法: exp(double a)

返回值: double

说明: 返回自然对数e的a次方

举例：

hive> select exp(2) from lxw_dual;

7.38905609893065

自然对数函数: ln

语法: ln(double a)

返回值: double

说明: 返回a的自然对数

举例：

hive> select ln(7.38905609893065)from lxw_dual;

2.0

8. 以10为底对数函数: log10

语法: log10(doublea)

返回值: double

说明: 返回以10为底的a的对数

举例：

hive> select log10(100) fromlxw_dual;

2.0

9. 以2为底对数函数: log2

语法: log2(double a)

返回值: double

说明: 返回以2为底的a的对数

举例：

hive> select log2(8) from lxw_dual;

3.0

10. 对数函数: log

语法: log(doublebase, double a)

返回值: double

说明: 返回以base为底的a的对数

举例：

hive> select log(4,256) fromlxw_dual;

4.0

11. 幂运算函数: pow

语法: pow(double a, doublep)

返回值: double

说明: 返回a的p次幂

举例：

hive> select pow(2,4) from lxw_dual;

16.0

12. 幂运算函数: power

语法: power(doublea, double p)

返回值: double

说明: 返回a的p次幂,与pow功能相同

举例：

hive> select power(2,4) fromlxw_dual;

16.0

13. 开平方函数: sqrt

语法: sqrt(double a)

返回值: double

说明: 返回a的平方根

举例：

hive> select sqrt(16) from lxw_dual;

4.0

14. 二进制函数: bin

语法: bin(BIGINT a)

返回值: string

说明: 返回a的二进制代码表示

举例：

hive> select bin(7) from lxw_dual;

111

15. 十六进制函数: hex

语法: hex(BIGINT a)

返回值: string

说明: 如果变量是int类型，那么返回a的十六进制表示；如果变量是string类型，则返回该字符串的十六进制表示

举例：

hive> select hex(17) from lxw_dual;

hive> select hex(‘abc’) fromlxw_dual;

616263

16. 反转十六进制函数: unhex

语法: unhex(stringa)

返回值: string

说明: 返回该十六进制字符串所代码的字符串

举例：

hive> select unhex(‘616263’)fromlxw_dual;

abc

hive> select unhex(‘11’)fromlxw_dual;

hive> select unhex(616263) fromlxw_dual;

abc

17. 进制转换函数: conv

语法: conv(BIGINTnum, int from_base, int to_base)

返回值: string

说明: 将数值num从from_base进制转化到to_base进制

举例：

hive> select conv(17,10,16) fromlxw_dual;

hive> select conv(17,10,2) fromlxw_dual;

10001

18. 绝对值函数: abs

语法: abs(doublea) abs(int a)

返回值: double int

说明: 返回数值a的绝对值

举例：

hive> select abs(-3.9) from lxw_dual;

3.9

hive> select abs(10.9) from lxw_dual;

10.9

19. 正取余函数: pmod

语法: pmod(int a,int b),pmod(double a, double b)

返回值: int double

说明: 返回正的a除以b的余数

举例：

hive> select pmod(9,4) from lxw_dual;

hive> select pmod(-9,4) fromlxw_dual;

20. 正弦函数: sin

语法: sin(double a)

返回值: double

说明: 返回a的正弦值

举例：

hive> select sin(0.8) from lxw_dual;

0.7173560908995228

21. 反正弦函数: asin

语法: asin(double a)

返回值: double

说明: 返回a的反正弦值

举例：

hive> select asin(0.7173560908995228)from lxw_dual;

0.8

22. 余弦函数: cos

语法: cos(double a)

返回值: double

说明: 返回a的余弦值

举例：

hive> select cos(0.9) from lxw_dual;

0.6216099682706644

23. 反余弦函数: acos

语法: acos(double a)

返回值: double

说明: 返回a的反余弦值

举例：

hive> select acos(0.6216099682706644)from lxw_dual;

0.9

24. positive函数: positive

语法: positive(inta), positive(double a)

返回值: int double

说明: 返回a

举例：

hive> select positive(-10) fromlxw_dual;

-10

hive> select positive(12) fromlxw_dual;

25. negative函数: negative

语法: negative(inta), negative(double a)

返回值: int double

说明: 返回-a

举例：

hive> select negative(-5) fromlxw_dual;

hive> select negative(8) fromlxw_dual;

-8

五、日期函数1. UNIX时间戳转日期函数:from_unixtime

语法: from_unixtime(bigintunixtime[, string format])

返回值: string

说明: 转化UNIX时间戳（从1970-01-0100:00:00 UTC到指定时间的秒数）到当前时区的时间格式

举例：

hive> selectfrom_unixtime(1323308943,'yyyyMMdd') from lxw_dual;

20111208

2. 获取当前UNIX时间戳函数:unix_timestamp

语法:unix_timestamp()

返回值: bigint

说明: 获得当前时区的UNIX时间戳

举例：

hive> select unix_timestamp() fromlxw_dual;

1323309615

3. 日期转UNIX时间戳函数:unix_timestamp

语法:unix_timestamp(string date)

返回值: bigint

说明: 转换格式为"yyyy-MM-ddHH:mm:ss"的日期到UNIX时间戳。如果转化失败，则返回0。

举例：

hive> selectunix_timestamp('2011-12-07 13:01:03') from lxw_dual;

1323234063

4. 指定格式日期转UNIX时间戳函数:unix_timestamp

语法:unix_timestamp(string date, string pattern)

返回值: bigint

说明: 转换pattern格式的日期到UNIX时间戳。如果转化失败，则返回0。

举例：

hive> select unix_timestamp('2011120713:01:03','yyyyMMddHH:mm:ss') from lxw_dual;

1323234063

5. 日期时间转日期函数:to_date

语法: to_date(stringtimestamp)

返回值: string

说明: 返回日期时间字段中的日期部分。

举例：

hive> select to_date('2011-12-0810:03:01') from lxw_dual;

2011-12-08

6. 日期转年函数: year

语法: year(stringdate)

返回值: int

说明: 返回日期中的年。

举例：

hive> select year('2011-12-0810:03:01') from lxw_dual;

2011

hive> select year('2012-12-08')fromlxw_dual;

2012

7. 日期转月函数: month

语法: month (stringdate)

返回值: int

说明: 返回日期中的月份。

举例：

hive> select month('2011-12-0810:03:01') from lxw_dual;

hive> select month('2011-08-08')fromlxw_dual;

8. 日期转天函数: day

语法: day (stringdate)

返回值: int

说明: 返回日期中的天。

举例：

hive> select day('2011-12-0810:03:01') from lxw_dual;

hive> select day('2011-12-24')fromlxw_dual;

9. 日期转小时函数: hour

语法: hour (stringdate)

返回值: int

说明: 返回日期中的小时。

举例：

hive> select hour('2011-12-0810:03:01') from lxw_dual;

10. 日期转分钟函数: minute

语法: minute (stringdate)

返回值: int

说明: 返回日期中的分钟。

举例：

hive> select minute('2011-12-0810:03:01') from lxw_dual;

11. 日期转秒函数: second

语法: second (stringdate)

返回值: int

说明: 返回日期中的秒。

举例：

hive> select second('2011-12-0810:03:01') from lxw_dual;

12. 日期转周函数:weekofyear

语法: weekofyear(string date)

返回值: int

说明: 返回日期在当前的周数。

举例：

hive> select weekofyear('2011-12-0810:03:01') from lxw_dual;

13. 日期比较函数: datediff

语法:datediff(string enddate, string startdate)

返回值: int

说明: 返回结束日期减去开始日期的天数。

举例：

hive> selectdatediff('2012-12-08','2012-05-09')from lxw_dual;

213

14. 日期增加函数: date_add

语法:date_add(string startdate, int days)

返回值: string

说明: 返回开始日期startdate增加days天后的日期。

举例：

hive> selectdate_add('2012-12-08',10)from lxw_dual;

2012-12-18

15. 日期减少函数: date_sub

语法: date_sub(string startdate, int days)

返回值: string

说明: 返回开始日期startdate减少days天后的日期。

举例：

hive> selectdate_sub('2012-12-08',10)from lxw_dual;

2012-11-28

六、条件函数1. If函数: if

语法: if(booleantestCondition, T valueTrue, T valueFalseOrNull)

返回值: T

说明: 当条件testCondition为TRUE时，返回valueTrue；否则返回valueFalseOrNull

举例：

hive> select if(1=2,100,200) fromlxw_dual;

200

hive> select if(1=1,100,200) fromlxw_dual;

100

2. 非空查找函数: COALESCE

语法: COALESCE(T v1,T v2, …)

返回值: T

说明: 返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL

举例：

hive> selectCOALESCE(null,'100','50′) from lxw_dual;

100

3. 条件判断函数：CASE

语法: CASE a WHEN bTHEN c [WHEN d THEN e]* [ELSE f] END

返回值: T

说明：如果a等于b，那么返回c；如果a等于d，那么返回e；否则返回f

举例：

hive> Select case 100 when 50 then'tom' when 100 then 'mary'else 'tim' end from lxw_dual;

mary

hive> Select case 200 when 50 then'tom' when 100 then 'mary'else 'tim' end from lxw_dual;

tim

4. 条件判断函数：CASE

语法: CASE WHEN aTHEN b [WHEN c THEN d]* [ELSE e] END

返回值: T

说明：如果a为TRUE,则返回b；如果c为TRUE，则返回d；否则返回e

举例：

hive> select case when 1=2 then 'tom'when 2=2 then 'mary' else'tim' end from lxw_dual;

mary

hive> select case when 1=1 then 'tom'when 2=2 then 'mary' else'tim' end from lxw_dual;

tom

七、字符串函数1. 字符串长度函数：length

语法: length(stringA)

返回值: int

说明：返回字符串A的长度

举例：

hive> select length('abcedfg') fromlxw_dual;

2. 字符串反转函数：reverse

语法: reverse(stringA)

返回值: string

说明：返回字符串A的反转结果

举例：

hive> select reverse(abcedfg’) fromlxw_dual;

gfdecba

3. 字符串连接函数：concat

语法: concat(stringA, string B…)

返回值: string

说明：返回输入字符串连接后的结果，支持任意个输入字符串

举例：

hive> select concat(‘abc’,'def’,'gh’)from lxw_dual;

abcdefgh

4. 带分隔符字符串连接函数：concat_ws

语法:concat_ws(string SEP, string A, string B…)

返回值: string

说明：返回输入字符串连接后的结果，SEP表示各个字符串间的分隔符

举例：

hive> selectconcat_ws(',','abc','def','gh') from lxw_dual;

abc,def,gh

5. 字符串截取函数：substr,substring

语法: substr(stringA, int start),substring(string A, int start)

返回值: string

说明：返回字符串A从start位置到结尾的字符串

举例：

hive> select substr('abcde',3) fromlxw_dual;

cde

hive> select substring('abcde',3)from lxw_dual;

cde

hive> selectsubstr('abcde',-1)from lxw_dual; （和ORACLE相同）

6. 字符串截取函数：substr,substring

语法: substr(stringA, int start, int len),substring(string A, intstart, int len)

返回值: string

说明：返回字符串A从start位置开始，长度为len的字符串

举例：

hive> select substr('abcde',3,2) fromlxw_dual;

hive> select substring('abcde',3,2)from lxw_dual;

hive>select substring('abcde',-2,2)from lxw_dual;

7. 字符串转大写函数：upper,ucase

语法: upper(stringA) ucase(string A)

返回值: string

说明：返回字符串A的大写格式

举例：

hive> select upper('abSEd') fromlxw_dual;

ABSED

hive> select ucase('abSEd') fromlxw_dual;

ABSED

8. 字符串转小写函数：lower,lcase

语法: lower(stringA) lcase(string A)

返回值: string

说明：返回字符串A的小写格式

举例：

hive> select lower('abSEd') fromlxw_dual;

absed

hive> select lcase('abSEd') fromlxw_dual;

absed

9. 去空格函数：trim

语法: trim(string A)

返回值: string

说明：去除字符串两边的空格

举例：

hive> select trim(' abc ') fromlxw_dual;

abc

10. 左边去空格函数：ltrim

语法: ltrim(stringA)

返回值: string

说明：去除字符串左边的空格

举例：

hive> select ltrim(' abc ') fromlxw_dual;

abc

11. 右边去空格函数：rtrim

语法: rtrim(stringA)

返回值: string

说明：去除字符串右边的空格

举例：

hive> select rtrim(' abc ') fromlxw_dual;

abc

12. 正则表达式替换函数：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 lxw_dual;

13. 正则表达式解析函数：regexp_extract

语法:regexp_extract(string subject, string pattern, int index)

返回值: string

说明：将字符串subject按照pattern正则表达式的规则拆分，返回index指定的字符。

举例：

hive> selectregexp_extract('foothebar', 'foo(.*?)(bar)', 1) fromlxw_dual;

the

hive> selectregexp_extract('foothebar', 'foo(.*?)(bar)', 2) fromlxw_dual;

bar

hive> selectregexp_extract('foothebar', 'foo(.*?)(bar)', 0) fromlxw_dual;

foothebar

注意，在有些情况下要使用转义字符，下面的等号要用双竖线转义，这是java正则表达式的规则。

select data_field,

regexp_extract(data_field,'.*?bgStart\\=([^&]+)',1) asaaa,

regexp_extract(data_field,'.*?contentLoaded_headStart\\=([^&]+)',1)as bbb,

regexp_extract(data_field,'.*?AppLoad2Req\\=([^&]+)',1)as ccc

frompt_nginx_loginlog_st

where pt ='2012-03-26'limit 2;

14. URL解析函数：parse_url

语法:parse_url(string urlString, string partToExtract [, stringkeyToExtract])

返回值: string

说明：返回URL中指定的部分。partToExtract的有效值为：HOST, PATH, QUERY,REF, PROTOCOL, AUTHORITY, FILE, and USERINFO.

举例：

hive>selectparse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST')fromlxw_dual;

facebook.com

hive>selectparse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1','QUERY','k1') from lxw_dual;

15. json解析函数：get_json_object

语法:get_json_object(string json_string, string path)

返回值: string

说明：解析json的字符串json_string,返回path指定的内容。如果输入的json字符串无效，那么返回NULL。

举例：

hive> selectget_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 lxw_dual;

amy

16. 空格字符串函数：space

语法: space(int n)

返回值: string

说明：返回长度为n的字符串

举例：

hive> select space(10) from lxw_dual;

hive> select length(space(10)) fromlxw_dual;

17. 重复字符串函数：repeat

语法: repeat(stringstr, int n)

返回值: string

说明：返回重复n次后的str字符串

举例：

hive> select repeat('abc',5) fromlxw_dual;

abcabcabcabcabc

18. 首字符ascii函数：ascii

语法: ascii(stringstr)

返回值: int

说明：返回字符串str第一个字符的ascii码

举例：

hive> select ascii('abcde') fromlxw_dual;

19. 左补足函数：lpad

语法: lpad(stringstr, int len, string pad)

返回值: string

说明：将str进行用pad进行左补足到len位

举例：

hive> select lpad('abc',10,'td') fromlxw_dual;

tdtdtdtabc

注意：与GP，ORACLE不同，pad 不能默认

20. 右补足函数：rpad

语法: rpad(stringstr, int len, string pad)

返回值: string

说明：将str进行用pad进行右补足到len位

举例：

hive> select rpad('abc',10,'td') fromlxw_dual;

abctdtdtdt

21. 分割字符串函数: split

语法: split(stringstr, stringpat)

返回值: array

说明: 按照pat字符串分割str，会返回分割后的字符串数组

举例：

hive> select split('abtcdtef','t')from lxw_dual;

["ab","cd","ef"]

22. 集合查找函数: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 lxw_dual;

hive> selectfind_in_set('at','ef,ab,de') from lxw_dual;

八、集合统计函数1. 个数统计函数: count

语法: count(*),count(expr), count(DISTINCT expr[, expr_.])

返回值: int

说明: count(*)统计检索出的行的个数，包括NULL值的行；count(expr)返回指定字段的非空值的个数；count(DISTINCTexpr[, expr_.])返回指定字段的不同的非空值的个数

举例：

hive> select count(*) from lxw_dual;

hive> select count(distinct t) fromlxw_dual;

2. 总和统计函数: sum

语法: sum(col),sum(DISTINCT col)

返回值: double

说明: sum(col)统计结果集中col的相加的结果；sum(DISTINCTcol)统计结果中col不同值相加的结果

举例：

hive> select sum(t) from lxw_dual;

100

hive> select sum(distinct t) fromlxw_dual;

3. 平均值统计函数: avg

语法: avg(col),avg(DISTINCT col)

返回值: double

说明: avg(col)统计结果集中col的平均值；avg(DISTINCTcol)统计结果中col不同值相加的平均值

举例：

hive> select avg(t) from lxw_dual;

hive> select avg (distinct t) fromlxw_dual;

4. 最小值统计函数: min

语法: min(col)

返回值: double

说明: 统计结果集中col字段的最小值

举例：

hive> select min(t) from lxw_dual;

5. 最大值统计函数: max

语法: maxcol)

返回值: double

说明: 统计结果集中col字段的最大值

举例：

hive> select max(t) from lxw_dual;

120

6. 非空集合总体变量函数:var_pop

语法: var_pop(col)

返回值: double

说明: 统计结果集中col非空集合的总体变量（忽略null）

举例：

7. 非空集合样本变量函数:var_samp

语法: var_samp (col)

返回值: double

说明: 统计结果集中col非空集合的样本变量（忽略null）

举例：

8. 总体标准偏离函数:stddev_pop

语法:stddev_pop(col)

返回值: double

说明: 该函数计算总体标准偏离，并返回总体变量的平方根，其返回值与VAR_POP函数的平方根相同

举例：

9. 样本标准偏离函数:stddev_samp

语法: stddev_samp(col)

返回值: double

说明: 该函数计算样本标准偏离

举例：

10．中位数函数:percentile

语法:percentile(BIGINT col, p)

返回值: double

说明: 求准确的第pth个百分位数，p必须介于0和1之间，但是col字段目前只支持整数，不支持浮点数类型

举例：

11. 中位数函数:percentile

语法:percentile(BIGINT col, array(p1 [, p2]…))

返回值:array<double>

说明: 功能和上述类似，之后后面可以输入多个百分位数，返回类型也为array<double>，其中为对应的百分位数。

举例：

select percentile(score,<0.2,0.4>)from lxw_dual；取0.2，0.4位置的数据

12. 近似中位数函数:percentile_approx

语法: percentile_approx(DOUBLEcol, p [, B])

返回值: double

说明: 求近似的第pth个百分位数，p必须介于0和1之间，返回类型为double，但是col字段支持浮点类型。参数B控制内存消耗的近似精度，B越大，结果的准确度越高。默认为10,000。当col字段中的distinct值的个数小于B时，结果为准确的百分位数

举例：

13. 近似中位数函数:percentile_approx

语法:percentile_approx(DOUBLE col, array(p1 [, p2]…) [, B])

返回值:array<double>

说明: 功能和上述类似，之后后面可以输入多个百分位数，返回类型也为array<double>，其中为对应的百分位数。

举例：

14. 直方图:histogram_numeric

语法:histogram_numeric(col, b)

返回值:array<struct {‘x’,‘y’}>

说明: 以b为基准计算col的直方图信息。

举例：

hive> select histogram_numeric(100,5)from lxw_dual;

[{"x":100.0,"y":1.0}]

九、复合类型构建操作1. Map类型构建: map

语法: map (key1,value1, key2, value2, …)

说明：根据输入的key和value对构建map类型

举例：

hive> Create table lxw_test as selectmap('100','tom','200','mary')as t from lxw_dual;

hive> describe lxw_test;

t map<string,string>

hive> select t from lxw_test;

{"100":"tom","200":"mary"}

2. Struct类型构建: struct

语法: struct(val1,val2, val3, …)

说明：根据输入的参数构建结构体struct类型

举例：

hive> create table lxw_test as selectstruct('tom','mary','tim')as t from lxw_dual;

hive> describe lxw_test;

t struct<col1:string,col2:string,col3:string>

hive> select t from lxw_test;

{"col1":"tom","col2":"mary","col3":"tim"}

3. array类型构建: array

语法: array(val1,val2, …)

说明：根据输入的参数构建数组array类型

举例：

hive> create table lxw_test asselectarray("tom","mary","tim") as t fromlxw_dual;

hive> describe lxw_test;

t array<string>

hive> select t from lxw_test;

["tom","mary","tim"]

十、复杂类型访问操作1. array类型访问: A[n]

语法: A[n]

操作类型: A为array类型，n为int类型

说明：返回数组A中的第n个变量值。数组的起始下标为0。比如，A是个值为['foo', 'bar']的数组类型，那么A[0]将返回'foo',而A[1]将返回'bar'

举例：

hive> create table lxw_test asselectarray("tom","mary","tim") as t fromlxw_dual;

hive> select t[0],t[1],t[2] fromlxw_test;

tom mary tim

2. map类型访问: M[key]

语法: M[key]

操作类型: M为map类型，key为map中的key值

说明：返回map类型M中，key值为指定值的value值。比如，M是值为{'f' -> 'foo', 'b'-> 'bar', 'all' -> 'foobar'}的map类型，那么M['all']将会返回'foobar'

举例：

hive> Create table lxw_test asselectmap('100','tom','200','mary') as t from lxw_dual;

hive> select t['200'],t['100'] fromlxw_test;

mary tom

3. struct类型访问: S.x

语法: S.x

操作类型: S为struct类型

说明：返回结构体S中的x字段。比如，对于结构体struct foobar{int foo, int bar}，foobar.foo返回结构体中的foo字段

举例：

hive> create table lxw_test as selectstruct('tom','mary','tim')as t from lxw_dual;

hive> describe lxw_test;

t struct<col1:string,col2:string,col3:string>

hive> select t.col1,t.col3 fromlxw_test;

tom tim

十一、复杂类型长度统计函数1. Map类型长度函数: size(Map<K.V>)

语法:size(Map<K.V>)

返回值: int

说明: 返回map类型的长度

举例：

hive> selectsize(map('100','tom','101','mary')) from lxw_dual;

2. array类型长度函数: size(Array<T>)

语法: size(Array<T>)

返回值: int

说明: 返回array类型的长度

举例：

hive> selectsize(array('100','101','102','103')) from lxw_dual;

3. 类型转换函数

类型转换函数: cast

语法: cast(expr as<type>)

返回值: Expected"=" to follow "type"

说明: 返回array类型的长度

举例：

hive> select cast(1 as bigint) from lxw_dual;

盛装吾步

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录