Hive 数据仓库工具(最全整理)_hive仓库工具都有那些

工作时，数据统计分析、挖掘的时候用到很多Hive方面的内容，就做了一个完整的整理文档。

·       hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供简单的sql查询功能，可以将sql语句转换为MapReduce任务进行运行。其优点是学习成本低，可以通过类SQL语句快速实现简单的MapReduce统计，不必开发专门的MapReduce应用，十分适合数据仓库的统计分析。

·       Hive 定义了简单的类 SQL查询语言，称为 HQL，它允许熟悉 SQL的用户查询数据。

·       Hive 将用户的HiveQL语句通过解释器转换为MapReduce作业提交到Hadoop集群上，Hadoop 监控作业执行过程，然后返回作业执行结果给用户。Hive并非为联机事务处理而设计，Hive并不提供实时的查询和基于行级的数据更新操作。Hive的最佳使用场合是大数据集的批处理作业，例如，网络日志分析。

Tip1： Hadoop

是一个开源框架来存储和处理大型数据在分布式环境中。它包含两个模块，一个是MapReduce，另外一个是Hadoop分布式文件系统（HDFS）。

•MapReduce：一种并行编程模型在大型集群普通硬件可用于处理大型结构化，半结构化和非结构化数据。

•HDFS：Hadoop分布式文件系统是Hadoop的框架的一部分，用于存储和处理数据集。它提供了一个容错文件系统在普通硬件上运行。

Hadoop生态系统包含了用于协助Hadoop的不同的子项目（工具）模块，如Sqoop, Pig和 Hive。

•Sqoop: 它是用来在HDFS和RDBMS之间来回导入和导出数据。

•Pig: 它是用于开发MapReduce操作的脚本程序语言的平台。

•Hive: 它是用来开发SQL类型脚本用于做MapReduce操作的平台。

注：有多种方法来执行MapReduce作业：

•传统的方法是使用Java MapReduce程序结构化，半结构化和非结构化数据。

•针对MapReduce的脚本的方式，使用Pig来处理结构化和半结构化数据。

•Hive查询语言（HiveQL或HQL）采用Hive为MapReduce的处理结构化数据。

 

1、用户接口

用户接口主要有三个：CLI，Client和 WUI。其中最常用的是 CLI，Cli启动的时候，会同时启动一个 Hive副本。Client 是 Hive 的客户端，用户连接至 Hive Server。在启动 Client模式的时候，需要指出 Hive Server所在节点，并且在该节点启动 Hive Server。 WUI是通过浏览器访问Hive。

2、元数据存储

Hive 将元数据存储在数据库中，如 mysql、derby。Hive中的元数据包括表的名字，表的列和分区及其属性，表的属性（是否为外部表等），表的数据所在目录等。

3、解释器、编译器、优化器、执行器

解释器、编译器、优化器完成 HQL查询语句从词法分析、语法分析、编译、优化以及查询计划的生成。生成的查询计划存储在 HDFS中，并在随后由 MapReduce调用执行。

4、Hadoop

Hive 的数据存储在 HDFS中，大部分的查询由 MapReduce完成。

 

首先Hive没有专门的数据存储格式，也没有为数据建立索引，用户可以非常自由的组织 Hive中的表，只需要在创建表的时候告诉 Hive数据中的列分隔符和行分隔符，Hive就可以解析数据。

其次，Hive中所有的数据都存储在 HDFS 中，Hive中包含以下数据模型：表(Table)，外部表(External Table)，分区(Partition)，桶(Bucket)。

Hive 中的 Table和数据库中的 Table在概念上是类似的，每一个 Table在 Hive 中都有一个相应的目录存储数据。例如，一个表 pvs，它在 HDFS中的路径为：/wh/pvs，其中，wh是在 hive-site.xml中由 ${hive.metastore.warehouse.dir}指定的数据仓库的目录，所有的 Table数据（不包括 External Table）都保存在这个目录中。

Partition 对应于数据库中的 Partition列的密集索引，但是 Hive中 Partition的组织方式和数据库中的很不相同。在 Hive中，表中的一个 Partition对应于表下的一个目录，所有的 Partition的数据都存储在对应的目录中。例如：pvs表中包含 ds 和 city 两个 Partition，则对应于 ds = 20090801, ctry = US的 HDFS 子目录为：/wh/pvs/ds=20090801/ctry=US；对应于 ds = 20090801, ctry = CA的 HDFS 子目录为；/wh/pvs/ds=20090801/ctry=CA

Buckets 对指定列计算 hash，根据 hash值切分数据，目的是为了并行，每一个 Bucket对应一个文件。将 user列分散至 32 个 bucket，首先对 user列的值计算 hash，对应 hash值为 0 的 HDFS 目录为：/wh/pvs/ds=20090801/ctry=US/part-00000；hash值为 20 的 HDFS 目录为：/wh/pvs/ds=20090801/ctry=US/part-00020

External Table 指向已经在 HDFS 中存在的数据，可以创建 Partition。它和 Table在元数据的组织上是相同的，而实际数据的存储则有较大的差异。

Table 的创建过程和数据加载过程（这两个过程可以在同一个语句中完成），在加载数据的过程中，实际数据会被移动到数据仓库目录中；之后对数据对访问将会直接在数据仓库目录中完成。删除表时，表中的数据和元数据将会被同时删除。

 

CREATE DATABASE语句

创建数据库是用来创建数据库在Hive中语句。在Hive数据库是一个命名空间或表的集合。此语法声明如下：

CREATEDATABASE|SCHEMA[IF NOT EXISTS]<database name>

IF NOT EXISTS是一个可选子句，通知用户已经存在相同名称的数据库。

可以使用SCHEMA在DATABASE的这个命令。下面的查询执行创建一个名为userdb数据库：

hive> CREATE DATABASE[IF NOT EXISTS] userdb;

或，hive> CREATE SCHEMA userdb;

以下的查询用于验证数据库列表：

hive>SHOW DATABASES;

default

userdb

JDBC 程序

在JDBC程序（HiveCreateDb.java文件）来创建数据库如下。

import java.sql.SQLException;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.Statement;

import java.sql.DriverManager;

 

publicclassHiveCreateDb{

   privatestaticStringdriverName="org.apache.hadoop.hive.jdbc.HiveDriver";

  

   publicstaticvoid main(String[] args)throwsSQLException{

      // Register driver and createdriver instance

  

      Class.forName(driverName);

      // get connection

     

      Connection con=DriverManager.getConnection("jdbc:hive://localhost:10000/default","","");

      Statement stmt= con.createStatement();

     

      stmt.executeQuery("CREATEDATABASE userdb");

      System.out.println(“Database userdbcreated successfully.”);

     

      con.close();

   }

}

JDBC ：数据库连接，一个JAVA API可访问任何类型表列数据，用于连接到数据库；创建SQL或MySQL语句；在数据库中执行SQL或MySQL查询；查看和修改数据库中的数据记录。

 

DROP DATABASE语句

DROP DATABASE是删除所有的表并删除数据库的语句。它的语法如下：

DROP DATABASEStatementDROP (DATABASE|SCHEMA) [IF EXISTS] database_name

[RESTRICT|CASCADE];

删除名称为userb的数据库；

hive> DROP DATABASE IF EXISTS userdb;

使用CASCADE查询删除数据库。这意味着要全部删除相应的表在删除数据库之前；

hive> DROP DATABASE IF EXISTS userdb CASCADE;

使用SCHEMA查询删除数据库；

hive> DROP SCHEMA userdb;

另外也可通过JDBC程序删除数据库。

 

Create Table是用于在Hive中创建表的语句。语法和示例如下：

语法：

CREATE [TEMPORARY][EXTERNAL] TABLE [IF NOTEXISTS][db_name.] table_name

 

[(col_namedata_type[COMMENT col_comment],...)]

[COMMENTtable_comment]

[ROWFORMAT row_format]

[STOREDAS file_format]

示例：需使用CREATE TABLE语句创建一个名为employee表。employee表中的字段和数据类型如下：

下面的数据是一个注释，行格式字段，如字段终止符，行终止符，并保存的文件类型。

COMMENT ‘Employee details’

FIELDSTERMINATED BY ‘\t’

LINES TERMINATEDBY ‘\n’

STORED IN TEXTFILE

下面的查询创建使用上述数据的表名为 employee。

hive>CREATE TABLE IF NOT EXISTS employee( eidint,nameString,

>salaryString, destinationString)

> COMMENT‘Employee details’

> ROWFORMAT DELIMITED

> FIELDSTERMINATED BY‘\t’

> LINESTERMINATED BY‘\n’

> STOREDAS TEXTFILE;

如果添加选项IF NOT EXISTS，Hive忽略大小写，万一表已经存在的声明。

成功创建表后，能看到以下回应：

OK

Time taken:5.905 seconds

hive>

LOAD DATA语句

一般来说，在SQL创建表后，可以使用INSERT语句插入数据。但在Hive中，可使用LOAD DATA语句插入数据。

同时将数据插入到Hive，最好是使用LOAD DATA来存储大量记录。有两种方法用来加载数据：一种是从本地文件系统，另一种是从Hadoop文件系统。

加载数据：

LOAD DATA [LOCAL] INPATH'filepath'[OVERWRITE] INTOTABLE tablename

[PARTITION(partcol1=val1,partcol2=val2...)]

•  LOCAL是标识符指定本地路径。它是可选的。

•  OVERWRITE是可选的，覆盖表中的数据。

•  PARTITION这是可选的

例如；插入在/home/user目录中名为sample.txt文件的数据。

hive> LOADDATA LOCAL INPATH'/home/user/sample.txt'

> OVERWRITE INTO TABLE employee;

 

修改表的属性，如，修改表名，修改列名，添加列，删除或替换列。

Alter Table 语句

语法：声明接受任意属性，我们希望在一个表中修改以下语法。

ALTER TABLE name RENAME TOnew_name

ALTER TABLE name ADDCOLUMNS (col_spec[, col_spec...])

ALTER TABLE name DROP [COLUMN]column_name

ALTER TABLE name CHANGEcolumn_name new_name new_type

ALTER TABLE name REPLACECOLUMNS(col_spec[, col_spec...])

    Rename To… 查询重命名表，把 employee修改为 emp

hive> ALTER TABLE employee RENAME TO emp;

Change …修改列名和列数据类型：

下表为例，显示employee表中字段要被更改（粗体）。

语句；

hive>ALTER TABLE employee CHANGE name enameString;

hive> ALTER TABLE employeeCHANGE salary salaryDouble;

添加列语句

下面的查询增加了一个列名dept在employee表。

hive>ALTER TABLE employee ADD COLUMNS(

   > dept STRING COMMENT'Department name');

REPLACE语句

以下从employee表中查询使用empid代替eid列，name代替ename列

hive>ALTER TABLE employee REPLACE COLUMNS(

   >eid INT empidInt,

   > ename STRING nameString);

 

Drop Table语句

语法：

DROP TABLE[IFEXISTS] table_name;

以下查询删除一个名为 employee的表:

hive> DROP TABLE IF EXISTSemployee;

查询验证表的列表

hive>SHOW TABLES;

emp

ok

Time taken:2.1 seconds

 

分区含义；组织表到分区。它是将一个表到基于分区列，如日期，城市和部门的值相关方式。使用分区，很容易对数据进行部分查询。

表或分区是细分成桶，以提供额外的结构，可以使用更高效的查询的数据。桶的工作是基于表的一些列的散列函数值。

例如，一个Tab1表包含雇员数据，如 id, name, dept和yoj (即加盟年份)，如果用年份分区雇员数据并将其存储在一个单独的文件，它减少了查询处理时间。下例表示分区的文件和数据；

employee 数据表中，

/tab1/employeedata/file1

id, name, dept, yoj

1, gopal, TP, 2012

2, kiran, HR, 2012

3, kaleel,SC, 2013

4, Prasanth, SC, 2013

上面的数据被划分成使用年两个文件。

/tab1/employeedata/2012/file2

1, gopal, TP, 2012

2, kiran, HR, 2012

/tab1/employeedata/2013/file3

3, kaleel,SC, 2013

4, Prasanth, SC,2013

添加分区

可以通过添加分区表改变所述表。假设我们有一个表叫employee，拥有如 Id, Name, Salary,Designation, Dept,和 yoj等字段。

语法：

ALTER TABLE table_name ADD[IF NOT EXISTS] PARTITION partition_spec

[LOCATION'location1'] partition_spec[LOCATION'location2']...;

 

partition_spec:

:(p_column= p_col_value, p_column= p_col_value,...)

以下查询用于将分区添加到employee表。

hive>ALTER TABLE employee

>ADD PARTITION(year=’2013’)

> location'/2013/part2013';

重命名分区

此命令的语法如下。

ALTER TABLE table_namePARTITION partition_spec RENAME TO PARTITION partition_spec;

hive>ALTER TABLE employee PARTITION(year=’1203’)

  > RENAME TO PARTITION(Yoj=’1203’);

删除分区

下面语法用于删除分区：

ALTER TABLE table_name DROP[IF EXISTS] PARTITION partition_spec, PARTITIONpartition_spec,...;

以下查询是用来删除分区：

hive>ALTER TABLE employee DROP[IF EXISTS]

  >PARTITION(year=’1203’);

 

在Hive有四种类型的运算符：

•  关系运算符

•  算术运算符

•  逻辑运算符

•  复杂运算符

关系运算符；用来比较两个操作数

下表描述Hive中可用的关系运算符：

示例

假设employee表由字段：Id, Name,Salary, Designation,和Dept组成，如下图所示。生成一个查询检索员工详细信息 - ID为1205。

+-----+--------------+--------+-----------------------+---

| Id  | Name        | Salary | Designation           |Dept |

+-----+--------------+------------------------------------+---

|1201 |Gopal        | 45000  | Technical manager     | TP  |

|1202 |Manisha      | 45000  | Proofreader           | PR   |

|1203 |Masthanvali  | 40000  | Technical writer      | TP  |

|1204 |Krian        | 40000  | Hr Admin              | HR   |

|1205 |Kranthi      | 30000  | Op Admin              | Admin|

+-----+--------------+--------+-----------------------+---

以下查询执行检索使用上述表中的雇员的详细信息：

hive>SELECT* FROM employee WHEREId=1205;

成功执行的查询，有以下结果：

+-----+-----------+-----------+----------------------+

| ID  | Name     | Salary    | Designation          | Dept  |

+-----+---------------+-------+----------------------+

|1205 | Kranthi   | 30000    | Op Admin             | Admin |

+-----+-----------+-----------+----------------------+

下面的查询执行以检索薪水大于或等于40000卢比的雇员的详细信息。

hive>SELECT* FROM employee WHERESalary>=40000;

成功执行的查询，有以下回应：

+-----+------------+--------+------------------------+------+

| ID  | Name      | Salary | Designation           | Dept |

+-----+------------+--------+------------------------+------+

|1201 |Gopal      | 45000  | Technical manager      | TP  |

|1202 | Manisha    | 45000 | Proofreader            | PR  

|1203 |Masthanvali| 40000  | Technicalwriter       | TP   |

|1204 |Krian      | 40000  | Hr Admin               | HR   |

+-----+------------+--------+------------------------+------+

算术运算符：支持的操作数各种常见的算术运算。返回数字类型。下表描述了在Hive中可用的算术运算符：

示例

下面的查询相加两个数字，20和30。

hive>SELECT20+30 ADD FROM temp;

在成功执行查询后，有以下回应：

+--------+

|   ADD  |

+--------+

|   50   |

+--------+

逻辑运算符

运算符是逻辑表达式。所有这些返回TRUE或FALSE。

示例

下面的查询用于检索部门是TP并且工资超过40000卢比的员工详细信息。

hive>SELECT* FROM employee WHERESalary>40000&&Dept=TP;

成功执行查询后，能看到以下回应：

+------+--------------+-----------+---------------—--+-------+

| ID   | Name         | Salary    | Designation      | Dept  |

+------+--------------+-----------+------------------+-------+

|1201  | Gopal       | 45000     | Technical manager|TP    |

+------+--------------+-----------+------------------+-------+

复杂的运算符

这些运算符提供一个表达式来接入复杂类型的元素。

 

Hive支持以下内置函数：

 

示例；

round() 函数

hive>SELECT round(2.6)from temp;

成功执行的查询，能看到以下回应：

2.0

floor() 函数

hive>SELECT floor(2.6)from temp;

2．0

 

聚合函数

Hive支持以下内置聚合函数。这些函数的用法类似SQL聚合函数。

 

和SQL类似，可根据用户的需求创建视图，将任何结果集数据保存为一个视图。

创建一个视图

可以创建一个视图，在执行SELECT语句的时候。语法如下：

CREATE VIEW [IFNOT EXISTS] view_name [(column_name [COMMENT column_comment], ...) ]

[COMMENTtable_comment]

AS SELECT ...

示例

设employee表拥有如下字段：Id, Name, Salary, Designation和 Dept。生成一个查询检索工资超过30000卢比的员工详细信息，我们把结果存储在一个名为视图 emp_30000.

+------+--------------+-----------+------------------+-------+

| ID   | Name         | Salary    | Designation      | Dept |

+------+--------------+-----------+------------------+-------+

|1201  | Gopal       | 45000     |Technical manager |TP    |

|1202  | Manisha     | 45000     | Proofreader      | PR   |

|1203  | Masthanvali | 40000     | Technical writer |TP    |

|1204  | Krian       | 40000     | Hr Admin         | HR   |

|1205  | Kranthi     | 30000     | Op Admin         | Admin

+------+--------------+-----------+------------------+-------+

下面使用上述业务情景查询检索员的工详细信息：

hive> CREATE VIEWemp_30000 AS

   > SELECT * FROM employee

   > WHERE salary>30000;

删除一个视图

语法：DROP VIEW view_name

创建索引

索引是一个表上的一个特定列的指针。创建索引意味着创建表上的一个特定列的指针。语法如下：

CREATE INDEXindex_name

ON TABLEbase_table_name (col_name, ...)

AS'index.handler.class.name'

[WITH DEFERREDREBUILD]

[IDXPROPERTIES(property_name=property_value, ...)]

[IN TABLEindex_table_name]

[PARTITIONED BY(col_name, ...)]

[

   [ ROW FORMAT ...] STORED AS ...

   | STORED BY ...

]

[LOCATIONhdfs_path]

[TBLPROPERTIES(...)]

示例，使用之前的字段 Id, Name, Salary, Designation,和 Dept创建一个名为index_salary的索引，对employee表的salary列索引。

下面的查询创建一个索引：

hive> CREATE INDEXinedx_salary ON TABLE employee(salary)

   > AS'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler';

这是一个指向salary列。如果列被修改，变更使用的索引值存储。

删除索引

用来删除索引的语法：

DROP INDEX <index_name> ON<table_name>

下面的查询删除名为index_salary索引：

hive> DROP INDEXindex_salary ON employee;

 

SELECT语句用来从表中检索的数据。 WHERE子句中的工作原理类似于一个条件。它使用这个条件过滤数据，并返回给出一个有限的结果。内置运算符和函数产生一个表达式，满足该条件并从表中select出来。

SELECT查询的语法；

查询检索薪水大于30000的员工信息；

hive> SELECT * FROMemployee WHERE salary>30000;

 

ORDER BY子句用于检索基于一列的细节并设置排序结果按升序或降序排列。

ORDER BY子句的语法；

例如，有员工表；

+------+--------------+----------+------------------+--------+

| ID   | Name         | Salary   | Designation      | Dept  |

+------+--------------+----------+------------------+--------+

|1201  | Gopal       | 45000    |Technical manager |TP     |

|1202  | Manisha     | 45000    | Proofreader      | PR    |

|1203  | Masthanvali | 40000    | Technical writer |TP     |

|1204  | Krian       | 40000    | Hr Admin         | HR     |

|1205  | Kranthi     | 30000    | Op Admin         | Admin  |

+------+--------------+----------+------------------+--------+

hive> SELECTId,Name,Dept FROM employee ORDER BY DEPT;

得到查询结果

+------+--------------+----------+-----------------+--------+

| ID   | Name         | Salary   | Designation     | Dept  |

+------+--------------+----------+-----------------+--------+

|1205  | Kranthi     | 30000    | Op Admin        | Admin |

|1204  | Krian       | 40000    | Hr Admin        | HR    |

|1202  | Manisha     | 45000    | Proofreader     | PR    |

|1201  | Gopal       | 45000    |Technical manager|TP     |

|1203  | Masthanvali | 40000    | Technical writer|TP     |

+------+--------------+----------+-----------------+--------+

 

GROUP BY子句用于分类所有记录结果的特定集合列，用来查询一组记录。

GROUP BY子句的语法：

同样的，上述员工表，

hive> SELECTDept,count(*) FROM employee GROUP BYDEPT;

查询以检索每个部门的员工数量。

+------+--------------+

| Dept |Count(*)     |

+------+--------------+

|Admin |    1        |

|PR    |   2         |

|TP    |   3         |

+------+--------------+

 

JOIN子句通过共同值组合来自两个表的特定字段。它是从数据库中的两个或更多的表组合的记录。

语法；

join_table:

 

   table_reference JOIN table_factor[join_condition]

   | table_reference{LEFT|RIGHT|FULL}[OUTER] JOINtable_reference

   join_condition

   | table_referenceLEFT SEMI JOIN table_reference join_condition

   | table_referenceCROSS JOIN table_reference[join_condition]

示例，CUSTOMERS表

+----+----------+-----+-----------+----------+

| ID | NAME     | AGE | ADDRESS   | SALARY  |

+----+----------+-----+-----------+----------+

| 1  | Ramesh  | 32  | Ahmedabad | 2000.00  | 

| 2  | Khilan  | 25  | Delhi     | 1500.00 | 

| 3  | kaushik | 23  | Kota      | 2000.00 |

| 4  | Chaitali | 25  | Mumbai   | 6500.00  |

| 5  | Hardik   | 27  |Bhopal    | 8500.00  |

| 6  | Komal   | 22  | MP        | 4500.00  |

| 7  | Muffy   | 24  | Indore    | 10000.00 |

+----+----------+-----+-----------+----------+

另一个ORDERS表

+-----+---------------------+-------------+--------+

|OID  | DATE               | CUSTOMER_ID |AMOUNT |

+-----+---------------------+-------------+--------+

| 102 |2009-10-08 00:00:00 |           3 |3000   |

| 100 |2009-10-08 00:00:00 |           3 |1500   |

| 101 |2009-11-20 00:00:00 |           2 |1560   |

| 103 |2008-05-20 00:00:00 |           4 |2060   |

+-----+---------------------+-------------+--------+

其中有不同类型的联接；JOIN、LEFT OUTER JOIN、RIGHT OUTER JOIN、FULL OUTER JOIN。

JOIN：

JOIN子句用于合并和检索来自多个表中的记录。 JOIN和SQL OUTER JOIN类似。连接条件是使用主键和表的外键。

hive> SELECT c.ID, c.NAME, c.AGE, o.AMOUNT

  > FROM CUSTOMERS c JOINORDERS o

  > ON(c.ID= o.CUSTOMER_ID);

查询结果；

+----+----------+-----+--------+

| ID | NAME     | AGE | AMOUNT |

+----+----------+-----+--------+

| 3  | kaushik | 23  | 3000   |

| 3  | kaushik | 23  | 1500   |

| 2  | Khilan  | 25  | 1560   |

| 4  | Chaitali | 25  | 2060  |

+----+----------+-----+--------+

 

LEFT OUTER JOIN

HiveQL LEFT OUTER JOIN返回所有行左表，即使是在正确的表中没有匹配。这意味着，如果ON子句匹配的右表0（零）记录，JOIN还是返回结果行，但在右表中的每一列为NULL。

例如以下LEFT OUTER JOIN用法

hive> SELECT c.ID, c.NAME, o.AMOUNT, o.DATE

  > FROM CUSTOMERS c

  > LEFT OUTER JOIN ORDERS o

  > ON(c.ID= o.CUSTOMER_ID);

查询结果；

+----+----------+--------+---------------------+

| ID | NAME     | AMOUNT | DATE                |

+----+----------+--------+---------------------+

| 1  | Ramesh  | NULL   | NULL                |

| 2  | Khilan  | 1560   | 2009-11-20 00:00:00 |

| 3  | kaushik | 3000   | 2009-10-08 00:00:00 |

| 3  | kaushik | 1500   | 2009-10-08 00:00:00 |

| 4  | Chaitali | 2060   | 2008-05-20 00:00:00 |

| 5  | Hardik  | NULL   | NULL                |

| 6  | Komal   | NULL   | NULL                |

| 7  | Muffy   | NULL   | NULL                |

+----+----------+--------+---------------------+

 

RIGHT OUTER JOIN

HiveQL RIGHT OUTER JOIN返回右边表的所有行，即使有在左表中没有匹配。如果ON子句的左表匹配0（零）的记录，JOIN结果返回一行，但在左表中的每一列为NULL。

hive> SELECT c.ID, c.NAME, o.AMOUNT, o.DATE

  > FROM CUSTOMERS c

  > RIGHT OUTER JOIN ORDERS o

  > ON(c.ID= o.CUSTOMER_ID);

查询结果：

+------+----------+--------+---------------------+

| ID   | NAME    | AMOUNT | DATE                |

+------+----------+--------+---------------------+

| 3    | kaushik | 3000   | 2009-10-08 00:00:00 |

| 3    | kaushik | 1500   | 2009-10-08 00:00:00 |

| 2    | Khilan  | 1560   | 2009-11-20 00:00:00 |

| 4    | Chaitali | 2060   | 2008-05-20 00:00:00 |

+------+----------+--------+---------------------+

 

FULL OUTER JOIN

HiveQL FULL OUTER JOIN结合了左边，并且满足JOIN条件合适外部表的记录。连接表包含两个表的所有记录，或两侧缺少匹配结果那么使用NULL值填补。

+------+----------+--------+---------------------+

| ID   | NAME    | AMOUNT | DATE                |

+------+----------+--------+---------------------+

| 1    | Ramesh  | NULL   | NULL                |

| 2    | Khilan  | 1560   | 2009-11-20 00:00:00 |

| 3    | kaushik | 3000   | 2009-10-08 00:00:00 |

| 3    | kaushik | 1500   | 2009-10-08 00:00:00 |

| 4    | Chaitali | 2060   | 2008-05-20 00:00:00 |

| 5    | Hardik  | NULL   | NULL                |

| 6    | Komal   | NULL   | NULL                |

| 7    | Muffy   | NULL   | NULL                | 

| 3    | kaushik | 3000   | 2009-10-08 00:00:00 |

| 3    | kaushik | 1500   | 2009-10-08 00:00:00 |

| 2    | Khilan  | 1560   | 2009-11-20 00:00:00 |

| 4    | Chaitali | 2060   | 2008-05-20 00:00:00 |

+------+----------+--------+---------------------+

 

1、Hive不支持等值连接

• SQL中对两表内联可以写成：

•select * from dual a,dual bwhere a.key = b.key;

•Hive中应为

•select * from dual a joindual b on a.key = b.key; 

 

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,而对它进行ISNULL的判断结果是False.