查看数据库：
show databases;
查看数据库详情：
desc database extended dm_cr;
打开默认数据库：
use default;
显示数据库中的表：
show tables;
查看表的结构：
desc student;
查看表详情：
desc formatted dm_ma_d_t_wyd_bub_event;
修改内部表student2为外部表
alter table student2 set tblproperties('EXTERNAL'='TRUE');
修改外部表student2为内部表
(‘EXTERNAL’=’TRUE’)和(‘EXTERNAL’=’FALSE’)为固定写法，区分大小写！
alter table student2 set tblproperties('EXTERNAL'='FALSE');
刷新表分区：
MSCK REPAIR TABLE table_name;
查看表分区：
show partitions table_name;
退出hive：
quit;
exit;
hive交互命令
1.“-e”不进入hive的交互窗口执行sql语句
hive -e "select id from student;" >> ./hive_result.csv
2.“-f”执行脚本中sql语句
hive -f ./hivef.sql >> ./hive_result.csv
在shell中将csv文件进行分列符号替换
sed -i ‘s/,/;/g’ hive_result.csv
sed -i ‘s/\t/;/g’ hive_result.csv
创建数据库
CREATE DATABASE [IF NOT EXISTS] database_name
创建一个数据库，数据库在HDFS上的默认存储路径是/user/hive/warehouse/*.db。
删除数据库
drop database if exists db_hive2;
drop database db_hive cascade;
如果删除的数据库不存在，最好采用 if exists判断数据库是否存在；如果数据库不为空，可以采用cascade命令，强制删除
创建表
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]
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement]
字段解释说明
（1）CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。
（2）EXTERNAL关键字可以让用户创建一个外部表，在建表的同时可以指定一个指向实际数据的路径（LOCATION），在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。
（3）COMMENT：为表和列添加注释。
（4）PARTITIONED BY 创建分区表
（5）CLUSTERED BY创建分桶表
（6）SORTED BY不常用，对桶中的一个或多个列另外排序
(7）ROW FORMAT DELIMITED
[FIELDS TERMINATED BY char] – 列分隔符
[COLLECTION ITEMS TERMINATED BY char] –MAP STRUCT 和 ARRAY 的分隔符(数据分割符号)
[MAP KEYS TERMINATED BY char] – MAP中的key与value的分隔符
[LINES TERMINATED BY char] – 行分隔符
SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, …)]
SerDe是Serialize/Deserilize的简称， hive使用Serde进行行对象的序列与反序列化。
（8）STORED AS指定存储文件类型
在hive中，较常见的文件存储格式有：TextFile、SequenceFile、RcFile、ORC、Parquet、AVRO。
常用的存储文件类型：
SEQUENCEFILE（二进制序列文件）、
TEXTFILE（文本，默认格式，行存储）、
RCFILE（列式存储格式文件）
ORCFile（按行分块，每块按列存储，压缩率非常高）
Parquet（行式存储，压缩性能很好，可以减少大量的表扫描和反序列化的时间）
如果文件数据是纯文本，可以使用STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。
（9）LOCATION ：指定表在HDFS上的存储位置。
（10）AS：后跟查询语句，根据查询结果创建表。
（11）LIKE允许用户复制现有的表结构，但是不复制数据
普通创建表（自动指定的路径不能存在，创建表的时候自动创建）
create table if not exists student2(
id int,
name string
)
row format delimited fields terminated by '\t'
stored as textfile
location '/user/hive/warehouse/student2';
根据查询结果创建表
create table if not exists student3 as
select
id, name
from student;
创建分区表
create table if not exists dept_partition(
deptno int,
dname string,
loc string
)
partitioned by (month string)
row format delimited fields terminated by '\t';
增加分区
--增加单个分区
alter table dept_partition add partition(month='201706') ;
--增加多个分区
alter table dept_partition add partition(month='201705') partition(month='201704');
删除分区
--删除单个分区
alter table dept_partition drop partition (month='201704');
--删除多个分区
alter table dept_partition drop partition (month='201705'), partition (month='201706');
重命名表
ALTER TABLE table_name RENAME TO new_table_name
更新列
ALTER TABLE table_name CHANGE [COLUMN] old_name new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]
增加和替换列
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)
删除表
drop table dept_partition;
清空表数据
truncate table dept_partition;
查询语法
[WITH CommonTableExpression as (, CommonTableExpression)*]
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ORDER BY col_list]
[CLUSTER BY col_list
| [DISTRIBUTE BY col_list] [SORT BY col_list]
]
[LIMIT number]
hive是基于Hadoop的Map Reduce，总体顺序：
from ..on .. join .. .. where .. select .. group by .. having .. order by
在map阶段
- from 加载，进行表的查找和加载
- where过滤
- select查询
- group by 执行分组后的相关计算
- map端文件合并
在Reduce阶段
- group by :对map端发来的文件进行合并
- select : 过滤列用于输出结果
- limit排序
几条可以提升hive速度的方式：
1）分区一定要加。
2）连接表时使用相同的关键词，这样只会产生一个job。
3）减少每个阶段的数据量，只选出需要的，在join表前就进行过滤。
4）map端聚合。不影响业务前提下，在mapper和reducer之间加一个combinator,提前进行合并以减少通信成本。
hive.map.aggr=true; // 用于设定是否在 map 端进行聚合，默认值为真hive.groupby.mapaggr.checkinterval=100000; // 用于设定 map 端进行聚合操作的条目数