用于记录Hive的原理说明、常用命令、以及优化，以便日后查询。
1. Hive概述
a. Hive是一个数据仓库的软件，用来管理大量的数据集，对其进行数据统计分析
b. Hive并不是数据库，它和数据库的应用场景不同，数据库适用于OLTP[online transaction process 在线事务处理]，而Hive适合于OLAP[online analysis process 在线分析处理]
2. 知识点说明
a. Hive可以用类SQL语言[HiveQL/HQL]进行操纵,但是并不是操作的真实的表
Hive中的数据库和表都是HDFS上的文件夹，表中的数据实际上是put在HDFS上的文本文件
b. Hive将数据库、表、字段的描述信息存放在关系型数据库中，默认是Derby，可以存放MySQL
c. Hive用HQL对数据库进行操作时，将HQL语句中的信息和存放在MySQL中的描述信息进行比对，从而映射到HDFS上的文件上去，找到数据存放的文件，从而操作数据
d. MySQL中存放的是数据库和表的结构，而数据则是HDFS上的文本，只需要文本的格式符合表行和列的分割符要求，将该文本手动put到HDFS上之后，用HQL查询表
中数据的时候，也会将该文本中的数据查询出来，若是不符合分割要求，查询出来则会显示NULL
e. Hive不支持更新，删除，也不支持事务
f. set指令可以显示所有的环境变量信息
g. hive命令行下，可以通过"!+Linux指令"的方式执行Linux指令，例如：!clear
3. Hive数据类型
TINYINT[1 byte] ARRAY SMALLINT[2 bytes] MAP INT[4 bytes] STRUCT BIGINT[8 bytes] NAMED STRUCT FLOAT[4 bytes] UNION DOUBLE[8 bytes] DECIMAL[38 digits] BINARY BOOLEAN STRING CHAR VARCHAR DATE TIMESTAMP
3. 常用命令
在Hive中可以使用hdfs命令，即dfs命令
创建数据库，MySQL记录数据库的描述信息，对应HDFS上的数据库文件夹
create database <basename>; e.g. CREATE DATABASE IF NOT EXISTS myhive COMMENT 'hive database demo' LOCATION '/hdfs/directory' // 指定数据库在HDFS上的存放位置 WITH DBPROPERTIES ('creator'='xw','date'='2017-04-10');
常看所有数据库
show databases;
查看数据库结构
desc database <databasename> ;
查看数据库结构及扩展信息
desc database extended <databasename>;
修改数据库结构,增加数据库属性
alter database <databasename> set dbproperties('created'='xw'); [属性即是键值对，可以自行添加]
显示所有的表
show tables;
查看表结构
desc <tablename>;
查看表结构及扩展信息
desc extended <tablename>; desc formatted <tablename>; // 友好显示
创建表，MySQL记录表的描述信息，对应HDFS上的表文件夹，不加修饰则是内部表[托管表 managed_table 即由Hive管理]
create table <tablename>(id int, name string, age int,...) comment 'table description' // 表的描述 row format delimited // 定义分隔符 fields terminated by ',' // 列分隔符，用于HQL查询某一字段时，对文本进行分割 lines terminated by ' ' // 行分隔符 stored as textfile; // 以文本形式存储，也可以是序列文件或RC文件 [e.g. 1. create table test(id int, name string, age int) comment 'table description' row format delimited fields terminated by ',' lines terminated by ' ' stored as textfile; 2. CREATE TABLE if not exists test (name string, str_arr ARRAY<string>, t_struct STRUCT<sex:string,age:int>, t_map MAP<string,int>, t_map_arr MAP<string,ARRAY<string>> ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' COLLECTION ITEMS TERMINATED BY ',' MAP KEYS TERMINATED BY ':'; ] 注：分隔符不能为分号
从本地加载数据，即插入数据，即将数据put到HDFS上的对应文件夹中
LOAD DATA LOCAL INPATH '...[本地路径]' OVERWRITE INTO TABLE <tablename>; // 省去local则是加载集群上的数据，该加载时移动[剪切] // overwrite 会覆盖原来的数据
重命名表名
alter table <tablename> rename to <new tablename>;
添加列
alter table <tablename> add columns(...)
复制表
create table default.test1 like mybase.test2; // 复制mybase数据库中的test2到default中的test1，只复制表结构，不复制数据
创建表的时候复制数据，该功能不能用于外部表
create table <tablename> as select ... from <tablename> where ...
复制表数据，批量插入
insert into <tablename> select ... from <tablename> where ...
导出数据，即把hive[hdfs]中的数据导出到本地
insert overwrite local directory '...[local path]' select * from test where ...
连接join
Hive默认的是mapjoin 属性设置为hive.auto.convert.join，默认是true 动态设置mapjoin SELECT /*+ MAPJOIN(table_name) */ ... mapjoin不支持以下操作： 在UNION ALL, LATERAL VIEW, GROUP BY/JOIN/SORT BY/CLUSTER BY/DISTRIBUTE BY之后使用 在UNION, JOIN, and 另一个 MAPJOIN之前使用
类型转换
cast(value as type); select cast('100' as int) from xxx;
修改分隔符
alter table <tablename> set serdeproperties ('field.delim' = ',');
修改表位置
alter table <tablename> set location '...[path]';
保护表[不能被删除]
alter table <tablename> enable no_drop; alter table <tablename> disable no_drop; // 取消保护
离线表[不能查询]
alter table <tablename> enable offline; alter table <tablename> disable offline; // 取消离线
导出表到hdfs
EXPORT TABLE employee TO '/home/xw/tmp'; 从hdfs上导入数据到一张新表中 IMPORT TABLE empolyee_imported FROM '/home/xw/tmp';
排序
1. order by 全局排序 2. sort by 每个reducer排序，并不整体排序 3. DISTRIBUTE BY 类似于分组 4. CLUSTER BY 先DISTRIBUTE BY后sort by，即先分组[reducer]，后组内排序