https://www.w3cschool.cn/hive_manual/

Hive是一个在Hadoop中用来处理结构化数据的数据仓库基础工具。它架构在Hadoop之上，用来进行数据提取、转化、加载，这是一种可以存储、查询和分析存储在Hadoop中的大规模数据的机制。hive数据仓库工具能将结构化的数据文件映射为一张数据库表，并提供SQL查询功能，能将SQL语句转变成MapReduce任务来执行。

术语“大数据”是大型数据集，其中包括体积庞大，高速，以及各种由与日俱增的数据的集合。使用传统的数据管理系统难以加工大型数据。因此，Apache软件基金会推出了一款名为Hadoop的解决大数据管理和处理难题的框架。

Hive 没有专门的数据格式。 Hive 可以很好的工作在 Thrift 之上，控制分隔符，也允许用户指定数据格式。

回到顶部

Hive驱动模块中的执行器执行最终的MR任务时，Hive本身不会生成MR算法程序。它通过一个表示“Job执行计划”的XML文件，来驱动内置的、原生的Mapper和Reducer模块。Hive通过和JobTracker通信来初始化MR任务，而不需直接部署在JobTracker所在管理节点上执行。通常在大型集群中，会有专门的网关机来部署Hive工具，这些网关机的作用主要是远程操作和管理节点上的JobTracker通信来执行任务。Hive要处理的数据文件常存储在HDFS上，HDFS由名称节点（NameNode）来管理。

Hadoop

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的处理结构化数据。

Hive是什么？

Hive是一个在Hadoop中用来处理结构化数据的数据仓库基础工具。它架构在Hadoop之上。

最初，Hive是由Facebook开发，后来由Apache软件基金会开发，并作为进一步将它作为名义下Apache Hive为一个开源项目。它用在好多不同的公司。例如，亚马逊在 Amazon Elastic MapReduce使用它。

Hive 不是

• 一个关系数据库
• 一个设计用于联机事务处理（OLTP）
• 实时查询和行级更新的语言

Hive特点

• 它存储架构在一个数据库中并处理数据到HDFS。
• 它是专为OLAP设计。
• 它提供SQL类型语言查询叫HiveQL或HQL。
• 它是低学习成本，快速和可扩展的。

Hive架构

下面的组件图描绘了Hive的结构：

该组件图包含不同的单元。下表描述每个单元：

单元名称	操作
用户接口/界面	Hive是一个数据仓库基础工具软件，可以创建用户和HDFS之间互动。用户界面，Hive支持是Hive的Web UI，Hive命令行，HiveHD洞察（在Windows服务器）。
元存储	Hive选择各自的数据库服务器，用以储存表，数据库，列模式或元数据表，它们的数据类型和HDFS映射。
HiveQL处理引擎	HiveQL的Metastore模式信息查询类似于SQL的查询上。这是传统的方式进行MapReduce程序的替代品之一。相反，使用Java编写的MapReduce程序，可以编写为MapReduce工作，并处理它的查询。
执行引擎	HiveQL处理引擎和MapReduce的结合部分是由Hive执行引擎。执行引擎处理查询并产生结果和MapReduce的结果一样。它采用MapReduce方法。
HDFS 或 HBASE	Hadoop的分布式文件系统或者HBASE数据存储技术是用于将数据存储到文件系统。

Hive工作原理

下图描述了Hive 和Hadoop之间的工作流程。

下表定义Hive和Hadoop框架的交互方式：

Step No.	操作
1	Execute Query Hive接口，如命令行或Web UI发送查询驱动程序（任何数据库驱动程序，如JDBC，ODBC等）来执行。
2	Get Plan 在驱动程序帮助下查询编译器，分析查询检查语法和查询计划或查询的要求。
3	Get Metadata 编译器发送元数据请求到Metastore（任何数据库）。
4	Send Metadata Metastore发送元数据，以编译器的响应。
5	Send Plan 编译器检查要求，并重新发送计划给驱动程序。到此为止，查询解析和编译完成。
6	Execute Plan 驱动程序发送的执行计划到执行引擎。
7	Execute Job 在内部，执行作业的过程是一个MapReduce工作。执行引擎发送作业给JobTracker，在名称节点并把它分配作业到TaskTracker，这是在数据节点。在这里，查询执行MapReduce工作。
7.1	Metadata Ops 与此同时，在执行时，执行引擎可以通过Metastore执行元数据操作。
8	Fetch Result 执行引擎接收来自数据节点的结果。
9	Send Results 执行引擎发送这些结果值给驱动程序。
10	Send Results 驱动程序将结果发送给Hive接口。

Hive 数据类型

Hive不同的数据类型，用于创建表。Hive所有数据类型分为四种类型，给出如下：

• 列类型
• 文字
• Null 值
• 复杂类型

列类型

列类型被用作Hive的列数据类型。它们如下：

整型

整型数据可以指定使用整型数据类型，INT。当数据范围超过INT的范围，需要使用BIGINT，如果数据范围比INT小，使用SMALLINT。 TINYINT比SMALLINT小。

下表描述了各种INT数据类型：

类型	后缀	示例
TINYINT	Y	10Y
SMALLINT	S	10S
INT	-	10
BIGINT	L	10L

字符串类型

字符串类型的数据类型可以使用单引号('')或双引号(“”)来指定。它包含两个数据类型：VARCHAR和CHAR。Hive遵循C-类型的转义字符。

下表描述了各种CHAR数据类型：

数据类型	长度
VARCHAR	1 to 65355
CHAR	255

时间戳

它支持传统的UNIX时间戳可选纳秒的精度。它支持的java.sql.Timestamp格式“YYYY-MM-DD HH:MM:SS.fffffffff”和格式“YYYY-MM-DD HH:MM:ss.ffffffffff”。

日期

DATE值在年/月/日的格式形式描述 {{YYYY-MM-DD}}.

小数点

在Hive 小数类型与Java大十进制格式相同。它是用于表示不可改变任意精度。语法和示例如下：

DECIMAL(precision, scale)
decimal(10,0)

联合类型

联合是异类的数据类型的集合。可以使用联合创建的一个实例。语法和示例如下：

UNIONTYPE, struct>

{0:1} 
{1:2.0} 
{2:["three","four"]} 
{3:{"a":5,"b":"five"}} 
{2:["six","seven"]} 
{3:{"a":8,"b":"eight"}} 
{0:9} 
{1:10.0}

文字

下面是Hive中使用的文字中：

浮点类型

浮点类型是只不过有小数点的数字。通常，这种类型的数据组成DOUBLE数据类型。

十进制类型

十进制数据类型是只不过浮点值范围比DOUBLE数据类型更大。十进制类型的范围大约是

 -10

^-308

 到 10

³⁰⁸

Null 值

缺少值通过特殊值 - NULL表示。

复杂类型

Hive复杂数据类型如下：

数组

在Hive 数组与在Java中使用的方法相同。

Syntax: ARRAY

映射

映射在Hive类似于Java的映射。

Syntax: MAP

结构体

在Hive结构体类似于使用复杂的数据。

Syntax: STRUCT

Hive 创建数据库

Hive是一种数据库技术，可以定义数据库和表来分析结构化数据。主题结构化数据分析是以表方式存储数据，并通过查询来分析。本章介绍如何创建Hive 数据库。配置单元包含一个名为 default 默认的数据库。

CREATE DATABASE语句

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

CREATE DATABASE|SCHEMA [IF NOT EXISTS] 

在这里，IF NOT EXISTS是一个可选子句，通知用户已经存在相同名称的数据库。可以使用SCHEMA 在DATABASE的这个命令。下面的查询执行创建一个名为userdb数据库：

hive> CREATE DATABASE [IF NOT EXISTS] userdb;

hive> CREATE SCHEMA userdb;

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

hive> SHOW DATABASES;
default
userdb

JDBC 程序

在JDBC程序来创建数据库如下。

import java.sql.SQLException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.DriverManager;

public class HiveCreateDb {
   private static String driverName = "org.apache.hadoop.hive.jdbc.HiveDriver";
   
   public static void main(String[] args) throws SQLException {
      // Register driver and create driver instance
   
      Class.forName(driverName);
      // get connection
      
      Connection con = DriverManager.getConnection("jdbc:hive://localhost:10000/default", "", "");
      Statement stmt = con.createStatement();
      
      stmt.executeQuery("CREATE DATABASE userdb");
      System.out.println(“Database userdb created successfully.”);
      
      con.close();
   }
}

保存程序在一个名为HiveCreateDb.java文件。下面的命令用于编译和执行这个程序。

$ javac HiveCreateDb.java
$ java HiveCreateDb

输出：

Database userdb created successfully.

Hive 删除数据库

本章介绍了如何在Hive删除数据库。模式和数据库的使用是一样的。

DROP DATABASE语句

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

DROP DATABASE StatementDROP (DATABASE|SCHEMA) [IF EXISTS] database_name 
[RESTRICT|CASCADE];

下面的查询用于删除数据库。假设要删除的数据库名称为userdb。

hive> DROP DATABASE IF EXISTS userdb;

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

hive> DROP DATABASE IF EXISTS userdb CASCADE;

以下使用SCHEMA查询删除数据库。

hive> DROP SCHEMA userdb;

此子句中添加在Hive0.6版本。

JDBC Program

在JDBC程序来删除数据库如下。

import java.sql.SQLException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.DriverManager;

public class HiveDropDb {
   private static String driverName = "org.apache.hadoop.hive.jdbc.HiveDriver";
   
   public static void main(String[] args) throws SQLException {
   
      // Register driver and create driver instance
      Class.forName(driverName);
      
      // get connection
      Connection con = DriverManager.getConnection("jdbc:hive://localhost:10000/default", "", "");
      Statement stmt = con.createStatement();
      stmt.executeQuery("DROP DATABASE userdb");
      
      System.out.println(“Drop userdb database successful.”);
      
      con.close();
   }
}

将该程序保存在一个名为HiveDropDb.java文件。下面给出的是编译和执行这个程序的命令。

$ javac HiveDropDb.java
$ java HiveDropDb

Output:

Drop userdb database successful.

Hive 创建表

2021-09-29 09:48 更新

本章将介绍如何创建一个表以及如何将数据插入。创造表的约定在Hive中非常类似于使用SQL创建表。

CREATE TABLE语句

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

语法

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.] table_name

[(col_name data_type [COMMENT col_comment], ...)]
[COMMENT table_comment]
[ROW FORMAT row_format]
[STORED AS file_format]

示例

假设需要使用CREATE TABLE语句创建一个名为employee表。下表列出了employee表中的字段和数据类型：

Sr.No	字段名称	数据类型
1	Eid	int
2	Name	String
3	Salary	Float
4	Designation	string

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

COMMENT ‘Employee details’
FIELDS TERMINATED BY ‘\t’
LINES TERMINATED BY ‘\n’
STORED IN TEXT FILE

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

hive> CREATE TABLE IF NOT EXISTS employee ( eid int, name String,
> salary String, destination String)
> COMMENT ‘Employee details’
> ROW FORMAT DELIMITED
> FIELDS TERMINATED BY ‘\t’
> LINES TERMINATED BY ‘\n’
> STORED AS TEXTFILE;

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

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

OK
Time taken: 5.905 seconds
hive>

JDBC 程序

以下是使用JDBC程序来创建表给出的一个例子。

import java.sql.SQLException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.DriverManager;

public class HiveCreateTable {
   private static String driverName = "org.apache.hadoop.hive.jdbc.HiveDriver";
   
   public static void main(String[] args) throws SQLException {
   
      // Register driver and create driver instance
      Class.forName(driverName);
      
      // get connection
      Connection con = DriverManager.getConnection("jdbc:hive://localhost:10000/userdb", "", "");
      
      // create statement
      Statement stmt = con.createStatement();
      
      // execute statement
      stmt.executeQuery("CREATE TABLE IF NOT EXISTS "
         +" employee ( eid int, name String, "
         +" salary String, destignation String)"
         +" COMMENT ‘Employee details’"
         +" ROW FORMAT DELIMITED"
         +" FIELDS TERMINATED BY ‘\t’"
         +" LINES TERMINATED BY ‘\n’"
         +" STORED AS TEXTFILE;");
         
      System.out.println(“ Table employee created.”);
      con.close();
   }
}

将该程序保存在一个名为HiveCreateDb.java文件。下面的命令用于编译和执行这个程序。

$ javac HiveCreateDb.java
$ java HiveCreateDb

输出

Table employee created.

LOAD DATA语句

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

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

语法

加载数据的语法如下：

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

• LOCAL是标识符指定本地路径。它是可选的。
• OVERWRITE 是可选的，覆盖表中的数据。
• PARTITION 这是可选的

示例

我们将插入下列数据到表中。在/home/user目录中名为sample.txt的文件。

1201  Gopal       45000    Technical manager
1202  Manisha     45000    Proof reader
1203  Masthanvali 40000    Technical writer
1204  Kiran       40000    Hr Admin
1205  Kranthi     30000    Op Admin

下面的查询加载给定文本插入表中。

hive> LOAD DATA LOCAL INPATH '/home/user/sample.txt'
> OVERWRITE INTO TABLE employee;

下载成功完成，能看到以下回应：

OK
Time taken: 15.905 seconds
hive>

JDBC 程序

下面给出的是JDBC程序将给定的数据加载到表中。

import java.sql.SQLException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.DriverManager;

public class HiveLoadData {

   private static String driverName = "org.apache.hadoop.hive.jdbc.HiveDriver";
   
   public static void main(String[] args) throws SQLException {
   
      // Register driver and create driver instance
      Class.forName(driverName);
      
      // get connection
      Connection con = DriverManager.getConnection("jdbc:hive://localhost:10000/userdb", "", "");
      
      // create statement
      Statement stmt = con.createStatement();
      
      // execute statement
      stmt.executeQuery("LOAD DATA LOCAL INPATH '/home/user/sample.txt'" + "OVERWRITE INTO TABLE employee;");
      System.out.println("Load Data into employee successful");
      
      con.close();
   }
}

将该程序保存在一个名为HiveLoadData.java文件。使用下面的命令来编译和执行这个程序。

$ javac HiveLoadData.java
$ java HiveLoadData

输出：

Load Data into employee successful

Hive 分区

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

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

例如，一个名为Tab1表包含雇员数据，如 id, name, dept 和yoj (即加盟年份)。假设需要检索所有在2012年加入，查询搜索整个表所需的信息员工的详细信息。但是，如果用年份分区雇员数据并将其存储在一个单独的文件，它减少了查询处理时间。下面的示例演示如何分区的文件和数据：

下面文件包含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 '/2012/part2012';

重命名分区

此命令的语法如下。

ALTER TABLE table_name PARTITION 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, PARTITION partition_spec,...;

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

hive> ALTER TABLE employee DROP [IF EXISTS]
   > PARTITION (year=’1203’);

Hive 内置运算符

本章介绍Hive的内置运算符。在Hive有四种类型的运算符：

• 关系运算符
• 算术运算符
• 逻辑运算符
• 复杂运算符

Hive 内置函数

本章介绍了在Hive中可用的内置函数。这些函数看起来非常类似于SQL的函数，除了他们的使用有点不一样。

内置函数

Hive支持以下内置函数：

返回类型	签名	描述
BIGINT	round(double a)	返回BIGINT最近的double值。
BIGINT	floor(double a)	返回最大BIGINT值等于或小于double。
BIGINT	ceil(double a)	它返回最小BIGINT值等于或大于double。
double	rand(), rand(int seed)	它返回一个随机数，从行改变到行。
string	concat(string A, string B,...)	它返回从A后串联B产生的字符串
string	substr(string A, int start)	它返回一个起始，从起始位置的子字符串，直到A.结束
string	substr(string A, int start, int length)	返回从给定长度的起始start位置开始的字符串。
string	upper(string A)	它返回从转换的所有字符为大写产生的字符串。
string	ucase(string A)	和上面的一样
string	lower(string A)	它返回转换B的所有字符为小写产生的字符串。
string	lcase(string A)	和上面的一样
string	trim(string A)	它返回字符串从A.两端修剪空格的结果
string	ltrim(string A)	它返回A从一开始修整空格产生的字符串(左手侧)
string	rtrim(string A)	rtrim(string A)，它返回A从结束修整空格产生的字符串(右侧)
string	regexp_replace(string A, string B, string C)	它返回从替换所有子在B结果配合C.在Java正则表达式语法的字符串
int	size(Map)	它返回在映射类型的元素的数量。
int	size(Array)	它返回在数组类型元素的数量。
value of	cast( as )	它把表达式的结果expr<类型>如cast('1'作为BIGINT)代表整体转换为字符串'1'。如果转换不成功，返回的是NULL。
string	from_unixtime(int unixtime)	转换的秒数从Unix纪元(1970-01-0100:00:00 UTC)代表那一刻，在当前系统时区的时间戳字符的串格式："1970-01-01 00:00:00"
string	to_date(string timestamp)	返回一个字符串时间戳的日期部分：to_date("1970-01-01 00:00:00") = "1970-01-01"
int	year(string date)	返回年份部分的日期或时间戳字符串：year("1970-01-01 00:00:00") = 1970, year("1970-01-01") = 1970
int	month(string date)	返回日期或时间戳记字符串月份部分：month("1970-11-01 00:00:00") = 11, month("1970-11-01") = 11
int	day(string date)	返回日期或时间戳记字符串当天部分：day("1970-11-01 00:00:00") = 1, day("1970-11-01") = 1
string	get_json_object(string json_string, string path)	提取从基于指定的JSON路径的JSON字符串JSON对象，并返回提取的JSON字符串的JSON对象。如果输入的JSON字符串无效，返回NULL。

示例

以下查询演示了一些内置函数：

round() 函数

hive> SELECT round(2.6) from temp;

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

2.0

floor() 函数

hive> SELECT floor(2.6) from temp;

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

2.0

Hive 视图和索引

创建一个视图

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

CREATE VIEW [IF NOT EXISTS] view_name [(column_name [COMMENT column_comment], ...) ]
[COMMENT table_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 VIEW emp_30000 AS
   > SELECT * FROM employee
   > WHERE salary>30000;

删除一个视图

使用下面的语法来删除视图：

DROP VIEW view_name

下面的查询删除一个名为emp_30000的视图：

hive> DROP VIEW emp_30000;

创建索引

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

CREATE INDEX index_name
ON TABLE base_table_name (col_name, ...)
AS 'index.handler.class.name'
[WITH DEFERRED REBUILD]
[IDXPROPERTIES (property_name=property_value, ...)]
[IN TABLE index_table_name]
[PARTITIONED BY (col_name, ...)]
[
   [ ROW FORMAT ...] STORED AS ...
   | STORED BY ...
]
[LOCATION hdfs_path]
[TBLPROPERTIES (...)]

例子

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

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

hive> CREATE INDEX inedx_salary ON TABLE employee(salary)
   > AS 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler';

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

删除索引

下面的语法用来删除索引：

DROP INDEX  ON 

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

hive> DROP INDEX index_salary ON employee;

HiveQL Select语句与Where子句

HiveQL Select语句与Where子句

2021-09-28 14:40 更新

Hive查询语言(HiveQL)是一种查询语言,Hive处理在Metastore分析结构化数据。本章介绍了如何使用SELECT语句的WHERE子句。

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

语法

下面给出的是SELECT查询的语法：

SELECT [ALL | DISTINCT] select_expr, select_expr, ... 
FROM table_reference 
[WHERE where_condition] 
[GROUP BY col_list] 
[HAVING having_condition] 
[CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list]] 
[LIMIT number];

示例

让我们举个例子SELECT ... WHERE子句。假设employee表有如下 Id, Name, Salary, Designation, 和 Dept等字段，生成一个查询检索超过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> SELECT * FROM employee WHERE salary>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     |
+------+--------------+-------------+-------------------+--------+

JDBC 程序

在JDBC程序应用，其中针对给定的例子如下子句。

import java.sql.SQLException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.DriverManager;

public class HiveQLWhere {
   private static String driverName = "org.apache.hadoop.hive.jdbc.HiveDriver";
   
   public static void main(String[] args) throws SQLException {
   
      // Register driver and create driver instance
      Class.forName(driverName);
      
      // get connection
      Connection con = DriverManager.getConnection("jdbc:hive://localhost:10000/userdb", "", "");
      
      // create statement
      Statement stmt = con.createStatement();
      
      // execute statement
      Resultset res = stmt.executeQuery("SELECT * FROM employee WHERE salary>30000;");
      
      System.out.println("Result:");
      System.out.println(" ID \t Name \t Salary \t Designation \t Dept ");
      
      while (res.next()) {
         System.out.println(res.getInt(1) + " " + res.getString(2) + " " + res.getDouble(3) + " " + res.getString(4) + " " + res.getString(5));
      }
      con.close();
   }
}

保存程序在一个名为HiveQLWhere.java文件。使用下面的命令来编译和执行这个程序。

$ javac HiveQLWhere.java
$ java HiveQLWhere

输出：

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

HiveQL 连接

2021-09-28 14:53 更新

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

语法

join_table:

   table_reference JOIN table_factor [join_condition]
   | table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference
   join_condition
   | table_reference LEFT SEMI JOIN table_reference join_condition
   | table_reference CROSS 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 | 
+----+----------+-----+-----------+----------+

考虑另一个表命令如下：

+-----+---------------------+-------------+--------+ 
|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和SQLOUTER JOIN 类似。连接条件是使用主键和表的外键。

下面的查询执行JOIN的CUSTOMER和ORDER表，并检索记录：

hive> SELECT c.ID, c.NAME, c.AGE, o.AMOUNT 
   > FROM CUSTOMERS c JOIN ORDERS 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 JOIN返回左表中的所有的值，加上右表，或JOIN子句没有匹配的情况下返回NULL。

下面的查询演示了CUSTOMER 和ORDER 表之间的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。

RIGHT JOIN返回右表中的所有值，加上左表，或者没有匹配的情况下返回NULL。

下面的查询演示了在CUSTOMER和ORDER表之间使用RIGHT OUTER JOIN。

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值填补

下面的查询演示了CUSTOMER 和ORDER 表之间使用的FULL OUTER JOIN：

hive> SELECT c.ID, c.NAME, o.AMOUNT, o.DATE 
   > FROM CUSTOMERS c 
   > FULL 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                |  
| 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 | 
+------+----------+--------+---------------------+