引言

与关系型数据库的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 “ \054”
STORED AS TEXTFILE
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 “\t”
FIELDS TERMINATED BY“\t”
STORED AS SEQUENCEFILE;

-例子

create table user_info (user_id int, cid string, ckid string, username string)
row format delimited
fields terminated by '\t'
lines terminated by '\n'；

-导入数据表的数据格式是：字段之间是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来区分，\n换行

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和本地目录。

HIVE SQL基础语法（hive sql语句大全）

引言

1. HIVE的特点

2. DDL

3. DML

4. DQL操作

相关推荐

前端Flex布局可视化布局工具介绍，vue和html5快速设计利器

HTML 简介（html简介及优缺点）

现在页面实时聊天都使用Websocket技术实现吗?

关于HTML5被简称做H5，你怎么看?（html5缩写）

在SpringBoot+Vue3项目中通过Nginx代理WebSocket实现跨域通信

利用webSocket实现消息的实时推送

谈谈websocket跨域

前端开发规范(一、HTML篇)

html5 工业web组态-开发自定义的组件 (炫酷按钮)

Python每日一库之Beautiful Soup（python mtime）