Hive 教程

目录

• Hive 安装
  • 第1步：验证JAVA安装
  • 安装 Java
    • 第(1)步：
    • 第(2)步：
    • 第(3)步：
    • 第（4）步：
  • 第2步：验证Hadoop的安装
  • 下载Hadoop
  • 在伪分布式模式安装Hadoop
    • 步骤I：设置Hadoop
    • 步骤II：Hadoop配置
  • 验证Hadoop的安装
    • 步骤I：名称节点设置
    • 步骤 II: 验证Hadoop dfs
    • 步骤 III : 验证Yarn脚本
    • 步骤 IV:在浏览器访问Hadoop
    • 步骤 V: 验证集群的所有应用程序
    • 第3步：下载Hive
  • 第4步：安装Hive
    • 提取和验证Hive存档
    • 将文件复制到/usr/local/hive目录
    • 设置Hive环境
  • 第5步：配置Hive
  • 第6步：下载并安装Apache Derby
    • 下载 Apache Derby
    • 提取和验证Derby存档
    • 将文件复制到/usr/local/derby 目录
    • 设置Derby环境
    • 创建一个目录来存放Metastore
  • 步骤7：配置Hive的Metastore
  • 第8步：验证Hive安装
• Hive 数据类型
  • 列类型
    • 整型
    • 字符串类型
    • 时间戳
    • 日期
    • 小数点
    • 联合类型
  • 文字
    • 浮点类型
    • 十进制类型
  • Null 值
  • 复杂类型
    • 数组
    • 映射
    • 结构体
• Hive 创建数据库
  • CREATE DATABASE语句
    • JDBC 程序
    • 输出：
• Hive 删除数据库
  • DROP DATABASE语句
    • JDBC Program
    • Output:
• Hive 创建表
  • CREATE TABLE语句
    • 语法
    • 示例
    • JDBC 程序
    • 输出
  • LOAD DATA语句
    • 语法
    • 示例
    • JDBC 程序
    • 输出：
• Hive 修改表
  • Alter Table 语句
    • 语法
  • Rename To… 语句
    • JDBC 程序
    • 输出
  • Change 语句
    • JDBC 程序
    • 输出
  • 添加列语句
  • JDBC 程序
    • 输出
  • REPLACE语句
  • JDBC 程序
    • 输出：
• Hive 删除表
  • Drop Table语句
    • JDBC 程序
    • 输出
• Hive 分区
  • 添加分区
    • 语法：
  • 重命名分区
  • 删除分区
• Hive 内置运算符
  • 关系运算符
    • 示例
  • 算术运算符
    • 示例
  • 逻辑运算符
    • 示例
  • 复杂的运算符
• Hive 内置函数
  • 内置函数
    • 示例
    • round() 函数
    • floor() 函数
    • floor() 函数
  • 聚合函数
• Hive 视图和索引
  • 创建一个视图
  • 示例
  • 删除一个视图
  • 创建索引
  • 例子
  • 删除索引
• HiveQL Select语句与Where子句
  • 语法
  • 示例
    • JDBC 程序
    • 输出：
• HiveQL Select语句与Order By子句
  • 语法
  • 示例
    • JDBC 程序
    • 输出
• HiveQL Select语句与Group By子句
  • 语法
  • 示例
    • JDBC 程序
    • 输出：
• HiveQL 连接
  • 语法
  • 示例
  • JOIN
  • LEFT OUTER JOIN
  • RIGHT OUTER JOIN
  • FULL OUTER JOIN

Hive 安装

所有Hadoop的子项目，如Hive, Pig,和HBase，都需要Linux的操作系统。因此，需要安装Linux OS。以下是为Hive的安装执行的简单步骤：

第1步：验证JAVA安装

在Hive安装之前，Java必须在系统上已经安装。使用下面的命令来验证是否已经安装Java：

$ java –version

如果Java已经安装在系统上，就可以看到如下回应：

java version "1.7.0_71" 
Java(TM) SE Runtime Environment (build 1.7.0_71-b13) 
Java HotSpot(TM) Client VM (build 25.0-b02, mixed mode)

如果在系统中尚未安装java，可以按照下面给出的安装Java的步骤进行安装。

安装 Java

第(1)步：

下载Java（JDK<最新版> - X64.tar.gz）通过访问以下链接 http://www.oracle.com/technetwork/java/javase/downloads/jdk7-downloads-1880260.html

JDK-7u71-linux-x64.tar.gz将被下载到系统中。

第(2)步：

一般来说，下载文件夹中下载的java文件。使用下面的命令提取jdk-7u71-linux-x64.gz文件。

$ cd Downloads/
$ ls
jdk-7u71-linux-x64.gz
$ tar zxf jdk-7u71-linux-x64.gz
$ ls
jdk1.7.0_71 jdk-7u71-linux-x64.gz

第(3)步：

为了使所有用户都能使用Java，将它移动到/usr/local/。打开root用户，键入以下命令。

$ su
password:
# mv jdk1.7.0_71 /usr/local/
# exit

第（4）步：

设置PATH和JAVA_HOME变量，添加以下命令到〜/.bashrc文件。

export JAVA_HOME=/usr/local/jdk1.7.0_71
export PATH=PATH:$JAVA_HOME/bin

现在使用java-version命令从终端验证如上所述的安装。

第2步：验证Hadoop的安装

Hadoop必须在安装Hive之前安装。使用下面的命令来验证Hadoop的安装：

$ hadoop version

如果Hadoop已经安装在系统上，那么会得到以下回应：

Hadoop 2.4.1 Subversion https://svn.apache.org/repos/asf/hadoop/common -r 1529768 
Compiled by hortonmu on 2013-10-07T06:28Z 
Compiled with protoc 2.5.0 
From source with checksum 79e53ce7994d1628b240f09af91e1af4

如果在系统上还未安装Hadoop，然后继续进行下面的安装步骤：

下载Hadoop

从Apache软件基金会下载并使用下面的命令提取Hadoop2.4.1。

$ su
password:
# cd /usr/local
# wget http://apache.claz.org/hadoop/common/hadoop-2.4.1/
hadoop-2.4.1.tar.gz
# tar xzf hadoop-2.4.1.tar.gz
# mv hadoop-2.4.1/* to hadoop/
# exit

在伪分布式模式安装Hadoop

下列步骤用于在伪分布式模式下安装Hadoop2.4.1。

步骤I：设置Hadoop

可以通过附加下面的命令来设置Hadoop环境变量在〜/ .bashrc文件中。

export HADOOP_HOME=/usr/local/hadoop 
export HADOOP_MAPRED_HOME=$HADOOP_HOME 
export HADOOP_COMMON_HOME=$HADOOP_HOME 
export HADOOP_HDFS_HOME=$HADOOP_HOME 
export YARN_HOME=$HADOOP_HOME
export HADOOP_COMMON_LIB_NATIVE_DIR=$HADOOP_HOME/lib/native export
PATH=$PATH:$HADOOP_HOME/sbin:$HADOOP_HOME/bin

现在，应用所有更改到当前正在运行的系统。

$ source ~/.bashrc

步骤II：Hadoop配置

可以找到位置“$HADOOP_HOME/etc/hadoop”所有Hadoop配置文件。需要根据Hadoop基础架构作出适当的修改这些配置文件。

$ cd $HADOOP_HOME/etc/hadoop

为了使用java开发Hadoop的项目，必须用java在系统的位置替换JAVA_HOME值重置hadoop-env.sh文件中的java环境变量。

export JAVA_HOME=/usr/local/jdk1.7.0_71

下面给出的是编辑配置Hadoop的文件列表。

core-site.xml

core-site.xml文件中包含的信息，如使用Hadoop实例分配给文件系统的存储器，用于存储数据的内存限制的端口号，以及读/写缓冲器的大小。

打开core-site.xml文件并在,标签之间添加以下属性。

<configuration>

   <property> 
      <name>fs.default.name</name> 
      <value>hdfs://localhost:9000</value> 
   </property>
   
</configuration>

hdfs-site.xml

hdfs-site.xml 文件中包含的信息，如复制数据的值，名称节点的路径，本地文件系统的数据节点的路径。

我们假定有以下数据。

dfs.replication (data replication value) = 1

(In the following path /hadoop/ is the user name.
hadoopinfra/hdfs/namenode is the directory created by hdfs file system.)

namenode path = //home/hadoop/hadoopinfra/hdfs/namenode

(hadoopinfra/hdfs/datanode is the directory created by hdfs file system.)
datanode path = //home/hadoop/hadoopinfra/hdfs/datanode

打开这个文件，并在此文件中的标签之间添加以下属性。

<configuration>

   <property> 
      <name>dfs.replication</name> 
      <value>1</value> 
   </property> 
   <property> 
      <name>dfs.name.dir</name> 
      <value>file:///home/hadoop/hadoopinfra/hdfs/namenode </value> 
   </property> 
   <property> 
      <name>dfs.data.dir</name>
      <value>file:///home/hadoop/hadoopinfra/hdfs/datanode </value > 
   </property>
   
</configuration>

注：在上面的文件，所有的属性值是用户定义的，可以根据自己的Hadoop基础架构进行更改。

yarn-site.xml

此文件用于配置yarn到Hadoop。打开yarn-site.xml文件，并在此文件中的标签之间添加以下属性。

<configuration>

   <property> 
      <name>yarn.nodemanager.aux-services</name> 
      <value>mapreduce_shuffle</value> 
   </property>
   
</configuration>

mapred-site.xml

此文件用于指定我们正在使用的MapReduce框架。缺省情况下，包含 yarn-site.xml模板。缺省情况下，包含yarn-site.xml模板。首先，需要将文件从mapred-site.xml复制。模板mapred-site.xml文件使用以下命令。

$ cp mapred-site.xml.template mapred-site.xml

打开mapred-site.xml文件，并在在此文件中的标签之间添加以下属性。

<configuration>

   <property> 
      <name>mapreduce.framework.name</name> 
      <value>yarn</value> 
   </property>

</configuration>

验证Hadoop的安装

下面的步骤被用来验证Hadoop的安装。

步骤I：名称节点设置

使用命令“hdfs namenode -format”设置名称节点如下。

$ cd ~
$ hdfs namenode -format

预期的结果如下。

10/24/14 21:30:55 INFO namenode.NameNode: STARTUP_MSG: 
/************************************************************ 
STARTUP_MSG: Starting NameNode 
STARTUP_MSG: host = localhost/192.168.1.11 
STARTUP_MSG: args = [-format] 
STARTUP_MSG: version = 2.4.1 
... 
... 
10/24/14 21:30:56 INFO common.Storage: Storage directory 
/home/hadoop/hadoopinfra/hdfs/namenode has been successfully formatted. 
10/24/14 21:30:56 INFO namenode.NNStorageRetentionManager: Going to 
retain 1 images with txid >= 0 
10/24/14 21:30:56 INFO util.ExitUtil: Exiting with status 0
10/24/14 21:30:56 INFO namenode.NameNode: SHUTDOWN_MSG: 
/************************************************************ 
SHUTDOWN_MSG: Shutting down NameNode at localhost/192.168.1.11
 ************************************************************/

步骤 II: 验证Hadoop dfs

下面的命令用来启动dfs。执行这个命令将开始启动Hadoop文件系统。

$ start-dfs.sh

期望的输出如下所示：

10/24/14 21:37:56 
Starting namenodes on [localhost] 
localhost: starting namenode, logging to /home/hadoop/hadoop-2.4.1/logs/hadoop-hadoop-namenode-localhost.out 
localhost: starting datanode, logging to /home/hadoop/hadoop-2.4.1/logs/hadoop-hadoop-datanode-localhost.out 
Starting secondary namenodes [0.0.0.0]

步骤 III : 验证Yarn脚本

下面的命令用来启动yarn脚本。执行此命令将启动yarn守护进程。

$ start-yarn.sh

期望的输出如下所示：

starting yarn daemons 
starting resourcemanager, logging to /home/hadoop/hadoop-2.4.1/logs/yarn-hadoop-resourcemanager-localhost.out 
localhost: starting nodemanager, logging to /home/hadoop/hadoop-2.4.1/logs/yarn-hadoop-nodemanager-localhost.out

步骤 IV:在浏览器访问Hadoop

访问Hadoop的默认端口号为50070.使用以下网址，以获取浏览器Hadoop服务。

http://localhost:50070/

步骤 V: 验证集群的所有应用程序

访问集群中的所有应用程序的默认端口号为8088。使用以下URL访问该服务。

http://localhost:8088/

第3步：下载Hive

我们在本教程中使用hive-0.14.0。可以通过访问以下链接下载 http://apache.petsads.us/hive/hive-0.14.0/. 假设它下载到/Downloads目录。在这里，我们下载一个名为“apache-hive-0.14.0-bin.tar.gz”的Hive存档。下面的命令用来验证的下载：

$ cd Downloads
$ ls

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

apache-hive-0.14.0-bin.tar.gz

第4步：安装Hive

需要执行以下步骤在系统上安装配置单元。假设Hive存档下载到/Downloads目录。

提取和验证Hive存档

下面的命令来验证下载并解压hive存档：

$ tar zxvf apache-hive-0.14.0-bin.tar.gz
$ ls

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

apache-hive-0.14.0-bin apache-hive-0.14.0-bin.tar.gz

将文件复制到/usr/local/hive目录

我们需要将文件从超级用户“su -”复制。下面的命令用于从提取目录中的文件复制到"/usr/local/hive“目录。

$ su -
passwd:

# cd /home/user/Download
# mv apache-hive-0.14.0-bin /usr/local/hive
# exit

设置Hive环境

可以设置Hive环境，通过附加以下行到〜/.bashrc文件中：

export HIVE_HOME=/usr/local/hive
export PATH=$PATH:$HIVE_HOME/bin
export CLASSPATH=$CLASSPATH:/usr/local/Hadoop/lib/*:.
export CLASSPATH=$CLASSPATH:/usr/local/hive/lib/*:.

下面的命令是用来执行〜/.bashrc文件。

$ source ~/.bashrc

第5步：配置Hive

配置Hive用于Hadoop环境中，需要编辑hive-env.sh文件，该文件放置在 $HIVE_HOME/conf目录。下面的命令重定向到Hive config文件夹并复制模板文件：

$ cd $HIVE_HOME/conf
$ cp hive-env.sh.template hive-env.sh

通过编辑hive-env.sh文件添加以下行：

export HADOOP_HOME=/usr/local/hadoop

Hive安装成功完成。现在，需要一个外部数据库服务器配置Metastore。我们使用Apache Derby数据库。

第6步：下载并安装Apache Derby

按照下面的步骤来下载和安装Apache Derby：

下载 Apache Derby

下面的命令用于下载Apache Derby。它下载需要一定的时间。

$ cd ~
$ wget http://archive.apache.org/dist/db/derby/db-derby-10.4.2.0/db-derby-10.4.2.0-bin.tar.gz

下面的命令用来验证下载文件：

$ ls

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

db-derby-10.4.2.0-bin.tar.gz

提取和验证Derby存档

下面的命令用于提取和验证Derby存档：

$ tar zxvf db-derby-10.4.2.0-bin.tar.gz
$ ls

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

db-derby-10.4.2.0-bin db-derby-10.4.2.0-bin.tar.gz

将文件复制到/usr/local/derby 目录

我们需要超级用户“su- ”复制。下面的命令用于从提取目录中的文件复制到/usr/local/derby目录：

$ su -
passwd:
# cd /home/user
# mv db-derby-10.4.2.0-bin /usr/local/derby
# exit

设置Derby环境

可以通过附加以下行到〜/.bashrc文件设置Derby环境：

export DERBY_HOME=/usr/local/derby
export PATH=$PATH:$DERBY_HOME/bin
Apache Hive
18
export CLASSPATH=$CLASSPATH:$DERBY_HOME/lib/derby.jar:$DERBY_HOME/lib/derbytools.jar

下面的命令是用来执行〜/.bashrc文件：

$ source ~/.bashrc

创建一个目录来存放Metastore

创建一个名为data目录在$DERBY_HOME目录中，用于存储Metastore数据。

$ mkdir $DERBY_HOME/data

Derby安装和环境设置完成。

步骤7：配置Hive的Metastore

配置Metastore意味着，指定要Hive的数据库存储。可以通过编辑hive-site.xml 文件，在$HIVE_HOME/conf目录下可以做到这一点。首先，使用以下命令复制模板文件：

$ cd $HIVE_HOME/conf
$ cp hive-default.xml.template hive-site.xml

编辑hive-site.xml并在和标记之间追加以下行：

<property>
   <name>javax.jdo.option.ConnectionURL</name>
   <value>jdbc:derby://localhost:1527/metastore_db;create=true </value>
   <description>JDBC connect string for a JDBC metastore </description>
</property>

创建一个文件名为 jpox.properties 并添加以下行：

javax.jdo.PersistenceManagerFactoryClass =

org.jpox.PersistenceManagerFactoryImpl
org.jpox.autoCreateSchema = false
org.jpox.validateTables = false
org.jpox.validateColumns = false
org.jpox.validateConstraints = false
org.jpox.storeManagerType = rdbms
org.jpox.autoCreateSchema = true
org.jpox.autoStartMechanismMode = checked
org.jpox.transactionIsolation = read_committed
javax.jdo.option.DetachAllOnCommit = true
javax.jdo.option.NontransactionalRead = true
javax.jdo.option.ConnectionDriverName = org.apache.derby.jdbc.ClientDriver
javax.jdo.option.ConnectionURL = jdbc:derby://hadoop1:1527/metastore_db;create = true
javax.jdo.option.ConnectionUserName = APP
javax.jdo.option.ConnectionPassword = mine

第8步：验证Hive安装

运行Hive之前，需要创建/tmp文件夹在HDFS独立的Hive文件夹。在这里使用/user/hive/warehouse文件夹。需要给这些新创建的文件夹写权限，如下图所示：

chmod g+w

现在，设置它们在HDFS验证Hive之前。使用下面的命令：

$ $HADOOP_HOME/bin/hadoop fs -mkdir /tmp 
$ $HADOOP_HOME/bin/hadoop fs -mkdir /user/hive/warehouse
$ $HADOOP_HOME/bin/hadoop fs -chmod g+w /tmp 
$ $HADOOP_HOME/bin/hadoop fs -chmod g+w /user/hive/warehouse

下面的命令来验证配置单元安装：

$ cd $HIVE_HOME
$ bin/hive

在成功安装Hive后，能看到以下回应：

Logging initialized using configuration in jar:file:/home/hadoop/hive-0.9.0/lib/hive-common-0.9.0.jar!/hive-log4j.properties 
Hive history file=/tmp/hadoop/hive_job_log_hadoop_201312121621_1494929084.txt
………………….
hive>

下面的示例命令以显示所有表：

hive> show tables; 
OK 
Time taken: 2.798 seconds 
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<int, double, array<string>, struct<a:int,b:string>>

{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

308

.

Null 值

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

复杂类型

Hive复杂数据类型如下：

数组

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

Syntax: ARRAY<data_type>

映射

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

Syntax: MAP<primitive_type, data_type>

结构体

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

Syntax: STRUCT<col_name : data_type [COMMENT col_comment], ...>

Hive 创建数据库

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

CREATE DATABASE语句

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

CREATE DATABASE|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程序来创建数据库如下。

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 创建表

本章将介绍如何创建一个表以及如何将数据插入。创造表的约定在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 修改表

本章将介绍如何修改表的属性，如，修改表名，修改列名，添加列，并删除或替换列。

Alter Table 语句

它是在Hive中用来修改的表。

语法

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

ALTER TABLE name RENAME TO new_name
ALTER TABLE name ADD COLUMNS (col_spec[, col_spec ...])
ALTER TABLE name DROP [COLUMN] column_name
ALTER TABLE name CHANGE column_name new_name new_type
ALTER TABLE name REPLACE COLUMNS (col_spec[, col_spec ...])

Rename To… 语句

下面是查询重命名表，把 employee 修改为 emp。

hive> ALTER TABLE employee RENAME TO emp;

JDBC 程序

在JDBC程序重命名表如下。

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

public class HiveAlterRenameTo {
   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("ALTER TABLE employee RENAME TO emp;");
      System.out.println("Table Renamed Successfully");
      con.close();
   }
}

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

$ javac HiveAlterRenameTo.java
$ java HiveAlterRenameTo

输出

Table renamed successfully.

Change 语句

下表包含employee表的字段，它显示的字段要被更改（粗体）。

字段名	从数据类型转换	更改字段名称	转换为数据类型
eid	int	eid	int
name	String	ename	String
salary	Float	salary	Double
designation	String	designation	String

下面查询重命名使用上述数据的列名和列数据类型：

hive> ALTER TABLE employee CHANGE name ename String;
hive> ALTER TABLE employee CHANGE salary salary Double;

JDBC 程序

下面给出的是使用JDBC程序来更改列。

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

public class HiveAlterChangeColumn {
   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("ALTER TABLE employee CHANGE name ename String;");
      stmt.executeQuery("ALTER TABLE employee CHANGE salary salary Double;");
      
      System.out.println("Change column successful.");
      con.close();
   }
}

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

$ javac HiveAlterChangeColumn.java
$ java HiveAlterChangeColumn

输出

Change column successful.

添加列语句

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

hive> ALTER TABLE employee ADD COLUMNS ( 
   > dept STRING COMMENT 'Department name');

JDBC 程序

JDBC程序添加列到表如下。

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

public class HiveAlterAddColumn {
   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("ALTER TABLE employee ADD COLUMNS " + " (dept STRING COMMENT 'Department name');");
      System.out.prinln("Add column successful.");
      
      con.close();
   }
}

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

$ javac HiveAlterAddColumn.java
$ java HiveAlterAddColumn

输出

Add column successful.

REPLACE语句

以下从employee表中查询删除的所有列，并使用emp替换列：

hive> ALTER TABLE employee REPLACE COLUMNS ( 
   > eid INT empid Int, 
   > ename STRING name String);

JDBC 程序

下面给出的是JDBC程序使用empid代替eid列，name代替ename列。

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

public class HiveAlterReplaceColumn {

   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("ALTER TABLE employee REPLACE COLUMNS "
         +" (eid INT empid Int,"
         +" ename STRING name String);");
         
      System.out.println(" Replace column successful");
      con.close();
   }
}

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

$ javac HiveAlterReplaceColumn.java
$ java HiveAlterReplaceColumn

输出：

Replace column successful.

Hive 删除表

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

Drop Table语句

语法如下：

DROP TABLE [IF EXISTS] table_name;

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

hive> DROP TABLE IF EXISTS employee;

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

OK
Time taken: 5.3 seconds
hive>

JDBC 程序

下面JDBC程序删除employee表。

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

public class HiveDropTable {

   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("DROP TABLE IF EXISTS employee;");
      System.out.println("Drop table successful.");
      
      con.close();
   }
}

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

$ javac HiveDropTable.java
$ java HiveDropTable

输出

Drop table successful

以下查询被用来验证表的列表：

hive> SHOW TABLES;
emp
ok
Time taken: 2.1 seconds
hive>

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中可用的关系运算符：

运算符	操作	描述
A = B	所有基本类型	如果表达A等于表达B，结果TRUE ，否则FALSE。
A != B	所有基本类型	如果A不等于表达式B表达返回TRUE ，否则FALSE。
A < B	所有基本类型	TRUE，如果表达式A小于表达式B，否则FALSE。
A <= B	所有基本类型	TRUE，如果表达式A小于或等于表达式B，否则FALSE。
A > B	所有基本类型	TRUE，如果表达式A大于表达式B，否则FALSE。
A >= B	所有基本类型	TRUE，如果表达式A大于或等于表达式B，否则FALSE。
A IS NULL	所有类型	TRUE，如果表达式的计算结果为NULL，否则FALSE。
A IS NOT NULL	所有类型	FALSE，如果表达式A的计算结果为NULL，否则TRUE。
A LIKE B	字符串	TRUE，如果字符串模式A匹配到B，否则FALSE。
A RLIKE B	字符串	NULL，如果A或B为NULL；TRUE，如果A任何子字符串匹配Java正则表达式B；否则FALSE。
A REGEXP B	字符串	等同于RLIKE.

示例

让我们假设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 WHERE Id=1205;

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

+-----+-----------+-----------+----------------------------------+
| ID  | Name      | Salary    | Designation              | Dept  |
+-----+---------------+-------+----------------------------------+
|1205 | Kranthi   | 30000     | Op Admin                 | Admin |
+-----+-----------+-----------+----------------------------------+

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

hive> SELECT * FROM employee WHERE Salary>=40000;

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

+-----+------------+--------+----------------------------+------+
| 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   |
+-----+------------+--------+----------------------------+------+

算术运算符

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

运算符	操作	描述
A + B	所有数字类型	A加B的结果
A - B	所有数字类型	A减去B的结果
A * B	所有数字类型	A乘以B的结果
A / B	所有数字类型	A除以B的结果
A % B	所有数字类型	A除以B.产生的余数
A & B	所有数字类型	A和B的按位与结果
A | B	所有数字类型	A和B的按位或结果
A ^ B	所有数字类型	A和B的按位异或结果
~A	所有数字类型	A按位非的结果

示例

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

hive> SELECT 20+30 ADD FROM temp;

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

+--------+
|   ADD  |
+--------+
|   50   |
+--------+

逻辑运算符

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

运算符	操作	描述
A AND B	boolean	TRUE，如果A和B都是TRUE，否则FALSE。
A && B	boolean	类似于 A AND B.
A OR B	boolean	TRUE，如果A或B或两者都是TRUE，否则FALSE。
A || B	boolean	类似于 A OR B.
NOT A	boolean	TRUE，如果A是FALSE，否则FALSE。
!A	boolean	类似于 NOT A.

示例

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

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

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

+------+--------------+-------------+-------------------+--------+
| ID   | Name         | Salary      | Designation       | Dept   |
+------+--------------+-------------+-------------------+--------+
|1201  | Gopal        | 45000       | Technical manager | TP     |
+------+--------------+-------------+-------------------+--------+

复杂的运算符

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

运算符	操作	描述
A[n]	A是一个数组，n是一个int	它返回数组A的第n个元素，第一个元素的索引0。
M[key]	M 是一个 Map<K, V> 并 key 的类型为K	它返回对应于映射中关键字的值。
S.x	S 是一个结构	它返回S的s字段

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<K.V>)	它返回在映射类型的元素的数量。
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

floor() 函数

hive> SELECT ceil(2.6) from temp;

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

3.0

聚合函数

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

返回类型	签名	描述
BIGINT	count(*), count(expr),	count(*) - 返回检索行的总数。
DOUBLE	sum(col), sum(DISTINCT col)	返回该组或该组中的列的不同值的分组和所有元素的总和。
DOUBLE	avg(col), avg(DISTINCT col)	返回上述组或该组中的列的不同值的元素的平均值。
DOUBLE	min(col)	返回该组中的列的最小值。
DOUBLE	max(col)	返回该组中的列的最大值。

Hive 视图和索引

本章介绍如何创建和管理视图。根据用户的需求创建视图。可以将任何结果集数据保存为一个视图。视图在Hive的用法和SQL视图用法相同。它是一个标准的RDBMS概念。我们可以在视图上执行所有DML操作。

创建一个视图

可以创建一个视图，在执行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 <index_name> ON <table_name>

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

hive> DROP INDEX index_salary ON employee;

HiveQL Select语句与Where子句

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 Select语句与Order By子句

本章介绍了如何使用SELECT语句的ORDER BY子句。ORDER BY子句用于检索基于一列的细节并设置排序结果按升序或降序排列。

语法

下面给出的是ORDER BY子句的语法：

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

示例

让我们举个SELECT ... ORDER BY子句的例子。假设员工表，如下Id, Name, Salary, Designation, 和 Dept 的字段，生成一个查询用于检索员工的详细信息。

+------+--------------+-------------+-------------------+--------+
| 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 Id, 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     |
+------+--------------+-------------+-------------------+--------+

JDBC 程序

下面是JDBC程序应用给定Order By子句的例子。

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

public class HiveQLOrderBy {
   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 ORDER BY DEPT;");
      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();
   }
}

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

$ javac HiveQLOrderBy.java
$ java HiveQLOrderBy

输出

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
1204     Krian          40000       Hr Admin             HR

HiveQL Select语句与Group By子句

本章介绍了SELECT语句的GROUP BY子句。GROUP BY子句用于分类所有记录结果的特定集合列。它被用来查询一组记录。

语法

GROUP BY子句的语法如下：

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

示例

让我们以SELECT... GROUP BY子句为例。假设员工表有如下Id, Name, Salary, Designation, 和 Dept字段。产生一个查询以检索每个部门的员工数量。

+------+--------------+-------------+-------------------+--------+ 
| ID   | Name         | Salary      | Designation       | Dept   |
+------+--------------+-------------+-------------------+--------+ 
|1201  | Gopal        | 45000       | Technical manager | TP     | 
|1202  | Manisha      | 45000       | Proofreader       | PR     | 
|1203  | Masthanvali  | 40000       | Technical writer  | TP     | 
|1204  | Krian        | 45000       | Proofreader       | PR     | 
|1205  | Kranthi      | 30000       | Op Admin          | Admin  |
+------+--------------+-------------+-------------------+--------+

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

hive> SELECT Dept,count(*) FROM employee GROUP BY DEPT;

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

+------+--------------+ 
| Dept | Count(*)     | 
+------+--------------+ 
|Admin |    1         | 
|PR    |    2         | 
|TP    |    3         | 
+------+--------------+

JDBC 程序

下面给出的是JDBC程序应用对给定的GROUP BY子句例子。

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

public class HiveQLGroupBy {
   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 Dept,count(*) ” + “FROM employee GROUP BY DEPT; ”);
      System.out.println(" Dept \t count(*)");
      
      while (res.next()) {
         System.out.println(res.getString(1) + " " + res.getInt(2)); 
      }
      con.close();
   }
}

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

$ javac HiveQLGroupBy.java
$ java HiveQLGroupBy

输出：

 Dept     Count(*)
 Admin       1
 PR          2
 TP          3

HiveQL 连接

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 | 
+------+----------+--------+---------------------+