Inner  joins:

hive> SELECT * FROM sales;

Joe 2

Hank 4

Ali 0

Eve 3

Hank 2

hive> SELECT * FROM things;

2 Tie

4 Coat

3 Hat

1 Scarf

两个表Inner joins:

hive> SELECT sales.*, things.*

> FROM sales JOIN things ON (sales.id =things.id);

Joe 2 2 Tie

Hank 4 4 Coat

Eve 3 3 Hat

Hank 2 2 Tie

只显示匹配id的记录

另一种表达方式：

SELECT sales.*, things.*

FROM sales, things

WHERE sales.id = things.id;

查看SQL查询分配多少个MapReduce job,使用关键字EXPLAIN

EXPLAIN

SELECT sales.*, things.*

FROM sales JOIN things ON (sales.id =things.id);

一个join 分配一个MapReduce job

Outer  joins:

SELECT sales.*, things.*

> FROM sales LEFT OUTER JOIN things ON(sales.id = things.id);

Joe 2 2 Tie

Hank 4 4 Coat

Ali 0 NULL NULL

Eve 3 3 Hat

Hank 2 2 Tie

LEFT OUTER JOIN显示左表的列，右表的列只显示匹配的，不匹配的用null显示。

hive> SELECT sales.*, things.*

> FROM sales RIGHT OUTER JOIN things ON(sales.id = things.id);

Joe 2 2 Tie

Hank 2 2 Tie

Hank 4 4 Coat

Eve 3 3 Hat

NULL NULL 1 Scarf

RIGHT OUTER JOIN显示右表的列，左表的列只显示匹配的，不匹配的用null显示。

hive> SELECT sales.*, things.*

> FROM sales FULL OUTER JOIN things ON(sales.id = things.id);

Ali 0 NULL NULL

NULL NULL 1 Scarf

Hank 2 2 Tie

Joe 2 2 Tie

Eve 3 3 Hat

Hank 4 4 Coat          

两个表的列都显示，不匹配的以null填充

Semi  joins：

SELECT *

FROM things

WHERE things.id IN (SELECT id from sales);

也可以替换用下列表达式：

hive> SELECT *

> FROM things LEFT SEMI JOIN sales ON(sales.id = things.id);

2 Tie

4 Coat

3 Hat

LEFT SEMI JOIN有限制右表sales 只能显示在on里，不能再select 表达式里引用sales表。

Map  joins

看下面join：

SELECT sales.*, things.*

FROM sales JOIN things ON (sales.id =things.id);

如果一个表足够小可以存在内存里，hive可以加载该表到内存里执每个map 里的join，这就是map join. Map joins 没有reducer,不适合RIGHT、FULL OUTER JOIN

Map joins 可以利用分桶表的好处，使用需要启动优化属性：

SET hive.optimize.bucketmapjoin=true;

子查询：

SELECT station, year, AVG(max_temperature)

FROM (

SELECT station, year, MAX(temperature) ASmax_temperature

FROM records2

WHERE temperature != 9999 AND quality IN(0, 1, 4, 5, 9)

GROUP BY station, year

) mt

GROUP BY station, year;

上面的语句就是子查询，在from后面又一个查询。

视图：

视图是一个虚拟表通过一个select语句实现。

视图定义：

CREATE VIEW valid_records

AS

SELECT *

FROM records2

WHERE temperature <> 9999 AND qualityIN (0, 1, 4, 5, 9);

DESCRIBE EXTENDED view_name 查看视图的详细信息

在第一个视图基础上创建一个视图：求每个位置每年的最大气温。

CREATE VIEW max_temperatures (station,year, max_temperature)

AS

SELECT station, year, MAX(temperature)

FROM valid_records

GROUP BY station, year;

执行查询求平均最大气温：

SELECT station, year, AVG(max_temperature)

FROM max_temperatures

GROUP BY station, year;

视图只能读，不可以通过视图加载或插入数据到基本表

用户自定义函数：

  UDF：操作单个数据行，产生单个数据行。

  UDAF：操作多个数据行，产生一个数据行。

  UDTF：操作一个数据行，产生多个数据行一个表作为输出。

下面是一个列子使用UDTF：

CREATE TABLE arrays (x ARRAY<STRING>)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY '\001'

COLLECTION ITEMS TERMINATED BY '\002';

数据：

a^Bb

c^Bd^Be

通过数据加载命令可以得到：

hive> SELECT * FROM arrays;

["a","b"]

["c","d","e"]

然后将上述的每行数组数据转换成单行String类型数据，如下：

hive> SELECT explode(x) AS y FROM arrays;

a

b

c

d

e

UDTF有一些限制，它们不能使用额外列表达式。

UDF:

一个UDF必须满足以下两个属性：

•一个UDF必须是org.apache.hadoop.hive.ql.exec.UDF的子类。

•一个UDF必须实现至少一个evaluate（）方法。

Evaluate不受接口定义，它可能接受任意个参数和任意类型，返回任意类型的值。

使用步骤：

1、打包编写好的UDF  2、注册功能到元数据中并给起个名字

UDF程序：除去字符串两端的空格或者两端指定的字符

package com.hadoop2app.hive;

import org.apache.commons.lang.StringUtils;
import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.io.Text;

/**
 * 除去字符串两端的空格或者两端指定的字符
 * 
 * */

public class Strip extends UDF {

private Text result  = new Text();

public Text evaluate(Text str) {
if (str == null) {
return null;
}
result.set(StringUtils.strip(str.toString()));
return result;
}

public Text evaluate(Text str, String stripChars) {
if (str == null) {
return null;
}
result.set(StringUtils.strip(str.toString(), stripChars));
    return result;
}
}

打包：mvn package 或者 eclipse 选项导出 jar 包并将 jar 上传至服务器指定目录：

注册 FUNCTION 在元数据中并起个名字，操作如下 ：

添加jar:

hive> add jar /home/jar/Strip.jar;

CREATE FUNCTION strip AS'com.hadoop2app.hive.Strip';

效果图：

                                 一个参数

                                   两个参数

CREATE FUNCTION strip AS'com.hadoopbook.hive.Strip'

USING JAR '/path/to/hive-examples.jar';

在集群中，我们需要将打包的jar上传至HDFS中，USING JAR 后是HDFS的URI。

移除function:

DROP FUNCTION strip;

创建一个在Hive会话期间的FUNCTION，它不持久化到metastore，使用TEMPORARY关键字

ADD JAR /path/to/hive-examples.jar;

CREATE TEMPORARY FUNCTION strip AS'com.hadoopbook.hive.Strip';

在定义UDFs的目录里创建一个.hiverc，在hive  session开始的时候将自动运行。

UDAF：

1.必须是org.apache.hadoop.hive.ql.exec.UDAF的子类

2.必须包含一个或者多个实现了org.apache.hadoop.hive.ql.exec.UDAFEvaluator的静态类

3.一个evaluator，必须实现5个方法

init()：初始化

iterate()：

terminatePartial()：返回中间聚合的结果

merge()：

terminate()：聚合的结果显示，调用terminate()。

UDAF 程序: 求温度大值 

package com.hadoop2app.hive;

import org.apache.hadoop.hive.ql.exec.UDAF;
import org.apache.hadoop.hive.ql.exec.UDAFEvaluator;
import org.apache.hadoop.io.IntWritable;
/**                                                                        
 *  UDAF:求最大值
 * 
 * /

@SuppressWarnings("deprecation")
public class Maximum  extends UDAF {

public static class MaximumIntUDAFEvaluator implements UDAFEvaluator{
    
private IntWritable result;
@Override
public void init() {
result = null;
}

public boolean iterate(IntWritable value){
if(value==null){
return true;
}
if(result==null){
result = new IntWritable(value.get());
}else{
result.set(Math.max(result.get(),value.get()));
}

return true;
}

public IntWritable terminatePartial(){
return result;
}

public boolean merge(IntWritable other){
return iterate(other);
}

    public IntWritable terminate(){
       return result;
    }
    }

} 

创建FUNCTION：

hive> add jar /home/jar/Maximum.jar;

Added [/home/jar/Maximum.jar] to class path

Added resources: [/home/jar/Maximum.jar]

hive> CREATE TEMPORARY FUNCTION maximum AS 'com.hadoop2app.hive.Maximum'; 

hive> SELECT maximum(temperature) FROM records;  

                          UDAF处理流程图