引言

与关系型数据库的SQL略有不同，但支持了绝大多数的语句如DDL、DML以及常见的聚合函数、连接查询、条件查询。HIVE不适合用于联机事务处理，也不提供实时查询功能。它最适合应用在基于大量不可变数据的批处理作业上。

1. HIVE的特点

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

2. DDL

1）建表

Create [external] table [if not exists] table_name

[(col_name data_type [comment col_comment],...)]

[COMMENT table_comment]

[PARTITIONED BY(col_name data_type [COMMENT col_comment],...)]

[CLUSTERED BY(col_name, col_name,...)

[SORTED BY(col_name [ASC| DESC],...)] INTO num_buckets BUCKETS]

[ROW FORMAT row_format]

[STORED AS file_format]

[LOCATION hdfd_path]

-CREATE TABLE创建一个指定名字的表。如果一样名字的表已经存在，则抛出异常;用户可以用IF NOT EXISTS来忽略这个异常。

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

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

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

-ROW FORMAT

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

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

I SERDE serde_name [WITH SERDEPROPERTIES (property.name =property_value,

property_name=property_value,...)]

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

-STORED AS

SEQUENCEFILE

| TEXTFILE

| RCFILE

| INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname

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

2）创建简单表

hive>CREATE TABLE pokes (foo INT，bar STRING);

3）创建外部表

CREATE EXTERNAL TABLE page_view(viewTime INT,userid BIGINT, page_url STRING, referrer_url STRING, ip STRING COMMENT “IP Address of the User”, country STRING COMMENT “country of origination”)

COMMENT “This is the staging page view table”

ROW FORMAT DELIMITED FlELDS TERMINATED BY “ 54”

STORED AS TEXTFILE

LOCATION “<hdfs_ location>”;

4) 建分区表

CREATE TABLE par_table(viewTime INT, userid BIGINT, page_url STRING, referrer_url STRING, ip STRING COMMENT “IP Address of the User”)

COMMENT “This is the page view table”

PARTITIONED BY(date STRING, pos STRING)

ROW FORMAT DELIMITED “ ”

FIELDS TERMINATED BY“ ”

STORED AS SEQUENCEFILE;

-例子

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

row format delimited

fields terminated by ' '

lines terminated by ' '；

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

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

100636 100890 c5c86f4cddcl5eb7 yyyvybtvt

100612 100865 97cc70d411cl8b6f gyvcycy

100078 100087 ecd6026al5ffddf5 qa000l00

-显示所有表：

hive>SHOW TABLES;

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

hive>SHOW TABLES ‘.*s’;

5) 修改表结构

-表添加一列：

hive>ALTER TABLE pokes ADD COLUMNS(new_col INT);

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

hive> ALTER TABLE invites ADD COLUMNS (new_col2 INT COMMENT

‘a comment’);

-更改表名

hive> ALTER TABLE events RENAME TO 3koobecaf;

-删除列

hive> DROP TABLE pokes;

6) 增加、删除分区

-增加

ALTER TABLE table_name ADD [IF NOT EXISTS] partition_spec [ LOCATION ‘location’]

partition.spec [ LOCATION ‘location2’]...

partition_spec:

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

-删除

ALTER TABLE table_name DROP partition_spec, partition_spec,...

7) 重命名表

ALTER TABLE table_name RENAME TO new_table_name

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

ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST| AFTER column_name]

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

8）表添加一列

hive>ALTER TABLE pokes ADD COLUMNS(new_col INT)；

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

hive> ALTER TABLE invites ADD COLUMNS (new_col INT COMMENT "a comment");

10）增加/更新列

-ALTER TABLE table_name ADD| REPLACE COLUMNS (col_name data_type [COMMENT col_comment],...)

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

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

11) 增加表的元数据信息

-ALTER TABLE table_name SET TBLPROPERTIES table_properties:

:[property_name = property_value....]

-用户可以用这个命令由表中增加metadata

12) 改变表文件格式与组织

-ALTER TABLE table_name SET FlLEFORMAT file_format

-ALTER TABLE table_name CLUSTERED BY(userid) SORTED BY(viewTime) INTO num_buckets

BUCKETS

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

13) 创建/删除视图

-CREATE VIEW [IF NOT EXISTS] view_name [ (column_name [COMMENT

column_comment],...) ][COMMENT view_comment][TBLPROPERTIES

(property_name =property_value,...)] AS SELECT

-增加视图

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

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

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

-DROP VIEW view_name

-删除视图

创建数据库

-CREATE DATABASE name

显示命令

-show tables;

-show databases;

-show partitions;

-show functions

-describe extended table_name dot col_name

3. DML

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

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

1）向数据表内加载文件

-LOAD DATA [LOCAL] INPATH“filepath”[OVERWRITE] INTO TABLE tablename

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

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

-filepath

-相对路径，例如：project/datal

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

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

例如：

hive>LOAD DATA LOCAL INPATH

‘./examples/files/kv1.txt’

OVERWRITE INTO TABLE pokes;

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

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

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

LOCAL关键字

-指定了 LOCAL，即本地

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

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

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

hive>LOAD DATA LOCAL INPATH ‘./examples/files/kv2.txt’

OVERWRITE INTO TABLE

invites PARTlTION (ds=’2008-08-15’)；

-没有指定LOCAL

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

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

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

3）加载DF数据，同时给定分区信息

hive>LOAD DATA INPATH ‘/user/myname/kv2.txt’

OVERWRITE INTO TABLE invites

PARTTION (ds=’2008-08-15’)；

The above command will load data from an HDFS file/directory to the table. Note that loading data from HDFS will result in moving the file/directory. As a result, the operation is almost instantaneous.

-OVERWRITE

-指定了 OVERWRITE

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

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

4) 将查询结果插入Hive表

-将查询结果插入Hive表

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

-基本模式

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

Select_statement1 FROM from_statement

-多插入模式

FROM from_statement

INSERT OVERWRITE TABLE tablename1 [PARTTITION (partcol1=val1, partcol2=val2...)]

Select_statement1

[INSERT OVERWRITE TABLE tablename2 [PARTITION...] select_statement2]...

-自动分区模式

INSERT OVERWRITE TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2]...)

select_statement FROM from_statement

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

-INSERT OVERWRITE [LOCAL] DIRECTORY directory1 SELECT... FROM ...

FROM from_statement

INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1

[INSERT OVERWRITE [LOCAL] DIRECTORY directory2 select_statement2]

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

INSERTINTO

-INSERTINTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)]

Select_statement1 FROM from_statement

4. DQL操作

1) 数据查询SQL

-基本的Select操作

SELECT [ALL | DISTINCTI select_expr, select_expr,...

FROM table_reference

[WHERE where_ condition]

[GROUP BY col_list [HAVING condition]]

[CLUSTER BY col_list

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

]

[LIMIT number]

-使用ALL和DISTlNCT选项区分对重复记录的处理。默认是ALL,表明查询所有记录。DISTNCT表明去掉重复的记录

-Where条件

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

-目前支持AND,OR ,0.9版本支持between，IN, NOT IN

-不支持 EXIST,NOT EXIST

2）ORDER BY 与SORT BY的不同

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

-SORT BY只在本机做排序

3）Limit

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

SELECT* FROM t1 LIMIT 5

-实现Top k查询

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

SET mapred.reduce.tasks =1

SELECT* FROM test SORT BY amount DESC LIMIT 5

4）基于Partition的查询

-一般SELECT查询会扫描整个表，使用PARFFONED BY子句建表，查询就可以利用分区剪枝(input pruning)的特性

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

5) Join

-Hive 只支持等值连接(equality joins)、外连接(outer joins)和(left semi

joins)。Hive不支持所有非等值的连接，由于非等值连接超级难转化到

map/reduce 任务

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

-LEFT SEMI JOIN是IN/EXISTS子查询的一种更高效的实现

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

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

6) 从sot到Hivea需要注意的事项

-Hive不支持等值连接

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

-hive不支持INSERT INTO, UPDATE, DELETE操作

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

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