Hive基本语句及函数用法汇总

1. Hive的基本概念

1.1 hive是什么？

Hive是基于Hadoop的数据仓库工具，相当于hadoop之上的一个客户端；
可对存储在 HDFS 上的文件中的数据集进行数据整理、特殊查询和分析处理；
是一种SQL解析引擎，能够将SQL转换成Map/Reduce中的Job在hadoop上执行。

1.2 hive的数据存储特点：

数据存储是基于hadoop的HDFS；
没有专门的数据存储格式；
存储结构主要有：数据库、文件（默认可以直接加载文本文件）、表、视图、索引；
hive中的表实质就是HDFS的目录，按表名将文件夹分开。

1.3 总结什么是Hive?

1）Hive 由 Facebook 实现并开源 ；
2）是基于 Hadoop 的一个数据仓库工具 ；
3）可以将结构化的数据映射为一张数据库表 ；
4）并提供 HQL(Hive SQL)查询功能 ；
5）底层数据是存储在 HDFS 上 ；
6）Hive的本质是将 SQL 语句转换为 MapReduce 任务运行 ；
7）使不熟悉 MapReduce 的用户很方便地利用 HQL 处理和计算 HDFS 上的结构化的数据，适用于离线的批量数据计算；
总结为一句话：hive是基于hadoop的数据仓库。

2. Hive基础sql语法

•DDL 操作
•DML 操作
•DQL 操作

2.1 DDL 操作

•建表
•删除表
•修改表结构
•创建／删除视图
•创建数据库
•显示命令

2.1.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 hdfs_path]

释义：

•CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；
              用户可以用 IF NOT EXIST 选项来忽略这个异常
•EXTERNAL 关键字可以让用户创建一个外部表
•COMMENT可以为表与字段增加描述
•PARTITIONED BY 分区
•CLUSTERED BY 数据汇总
•SORTED BY 按某列排序
•BUCKETS 分桶 #设置词句分桶才有效: set hive.enforce.bucketing=true;
•ROW FORMAT DELIMITED 按分割格式读取文件
	[fields terminated by char]           #每个列字段通过什么分割
	[collection items terminated by char] #array/struct每个键值之间分割符
	[map keys terminated by char]         #每个键值对分隔符
	[lines terminated by char]            #每行之间通过什么分割
	#用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。
	#Hive 通过指定自定义的 SerDe 确定表的具体的列的数据。
•STORED AS 存储为不同文件格式
	[textfile]     #查看：hadoop fs -text 
	[sequencefile] #序列化文件
	[rcfile]       #查看：hive -service rcfilecat path
	[INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname] #自定义的输入\输出流
•LOCATION 在建表的同时指定一个指向实际数据的路径

• 数据编码格式

hive默认通过^A(\001)、^B(\002)、^C(\003)分别对列、(array和struct)、map进行匹配；
# 行格式分隔的字段终止于
row format delimited fields terminated by '\001'
# 集合项终止于
collection items terminated by '\002'
# 映射键终止于
map keys terminated by '\003'
# 行终止于
lines terminated by '\n'	

2.1.2 创建简单表：

CREATE TABLE pokes (foo INT, bar STRING);

2.1.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 fields terminated by ‘\054’
STORED AS TEXTFILE
LOCATION ‘<hdfs_location>’;

2.1.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 ‘\n’
STORED AS sequencefile;

2.1.5 建分桶表

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)
CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS
ROW FORMAT DELIMITED ‘\t’
fields terminated by ‘\n’
STORED AS sequencefile;

2.1.6 创建表并创建索引字段ds

CREATE TABLE invites (foo INT, bar STRING) PARTITIONED BY (ds STRING);

2.1.7 复制一个空表

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

CREATE TABLE empty_key_value_store LIKE key_value_store;#只是复制表结构

CREATE TABLE empty_key_value_store AS SELECT NAME1 FROM key_value_store;#复制表结构及数据

2.1.8 简单示例：

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

2.1.9 查看所有数据表/分区/数据库：

SHOW table_name;
SHOW partitions ;
SHOW databases;
•按正条件（正则表达式）显示表
SHOW TABLES ‘.*s’;
•显示表的描述信息
DESC table_name；
DESC extented table_name; #查看扩展信息
DESC formatted table_name;#查看格式化信息
•显示建表的语句
SHOW CREATE TABLE tablename;

2.1.10 修改表结构

•增加分区、删除分区
•重命名表
•修改列的名字、类型、位置、注释
•增加/更新列
•增加表的元数据信息

2.1.11 添加/更新列

ALTER TABLE table_name ADD|REPLACE COLUMNS (new_col INT);
ALTER TABLE table_name ADD|REPLACE COLUMNS (new_col INT COMMENT ‘a comment’);
#增加列字段注释，REPLACE会表示替换表中所有字段。

2.1.12 修改列名/类型/注释/位置：

ALTER TABLE table_name CHANGE col_old_name col_new_name;

ALTER TABLE table_name CHANGE col_old_name col_new_name column_type;

ALTER TABLE table_name CHANGE col_old_name col_new_name column_type COMMENT col_comment;

ALTER TABLE table_name CHANGE col_old_name col_new_name column_type AFTER/FIRST column_name;

2.1.13 删除列:

ALTER TABLE table_name DROP col_name;

2.1.14 表重命名：

ALTER TABLE old_table_name RENAME TO new_table_name;

2.1.15 修改表属性/元数据信息:

ALTER TABLE table_name SET TBLPROPERTIES (‘EXTERNAL’=‘TRUE’); //内部表转外部表
ALTER TABLE table_name SET TBLPROPERTIES (‘EXTERNAL’=‘FALSE’); //外部表转内部表
ALTER TABLE table_name SET TBLPROPERTIES property_name = property_value……

2.1.16 修改文件格式与组织

ALTER TABLE table_name SET FILEFORMAT file_format;
ALTER TABLE table_name CLUSTERED BY(userid) SORTED BY(viewTime) INTO num_buckets BUCKETS;

2.1.17 删除/清空数据表：

DROP TABLE table_name;
TRUNCATE TABLE table_name;

2.1.18 添加/删除/修改分区：

ALTER TABLE table_name ADD IF NOT EXISTS
PARTITION(col_name=‘value1’) location ‘loc1’;//一次添加一个分区

ALTER TABLE page_view ADD IF NOT EXISTS
PARTITION (dt=‘2008-08-08’, country=‘us’) LOCATION ‘/path/to/us/part080808’
PARTITION (dt=‘2008-08-09’, country=‘us’) LOCATION ‘/path/to/us/part080809’; //一次添加多个分区