hive的基本简介及安装、配置、使用（一）

-- 查看数据库(默认的数据库为default)
show databases;
show databases like "db*";
desc database db_hive_01;

-- 创建和使用数据库
CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
		  [COMMENT database_comment]
		  [LOCATION hdfs_path]
		  [WITH DBPROPERTIES (property_name=property_value, ...)];

create database db_hive_01;
create database if not exists db_hive_01;
create database if not exists db_hive_01 comment 'Hive数据库01';
create database if not exists db_hive_01 comment 'Hive数据库01' location '/user/ubuntu/hive/warehouse/db_hive_01.db';
use db_hive_01;


-- 修改数据库
ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role;   -- (Note: Hive 0.13.0 and later; SCHEMA added in Hive 0.14.0)

ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...);   -- (Note: SCHEMA added in Hive 0.14.0)

ALTER (DATABASE|SCHEMA) database_name SET LOCATION hdfs_path; -- (Note: Hive 2.2.1, 2.4.0 and later)


-- 删除数据库
DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];
drop database if exists db_hive_01 cascade; --如果数据库中存在表和数据，则删除需要cascade关键字，这样数据库的目录及其子目录也将被删掉

-- 创建表
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name -- (Note: TEMPORARY available in Hive 0.14.0 and later)
  [(col_name data_type [COMMENT col_comment], ... [constraint_specification])]
  [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]
  [SKEWED BY (col_name, col_name, ...)  -- (Note: Available in Hive 0.10.0 and later)]
     ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
     [STORED AS DIRECTORIES]
  [
   [ROW FORMAT row_format] 
   [STORED AS file_format]
     | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]  -- (Note: Available in Hive 0.6.0 and later)
  ]
  [LOCATION hdfs_path]
  [TBLPROPERTIES (property_name=property_value, ...)]   -- (Note: Available in Hive 0.6.0 and later)
  [AS select_statement];  -- (Note: Available in Hive 0.5.0 and later; not supported for external tables)
 
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
  LIKE existing_table_or_view_name
  [LOCATION hdfs_path];
 
data_type
  : primitive_type
  | array_type
  | map_type
  | struct_type
  | union_type  -- (Note: Available in Hive 0.7.0 and later)
 
primitive_type
  : TINYINT
  | SMALLINT
  | INT
  | BIGINT
  | BOOLEAN
  | FLOAT
  | DOUBLE
  | DOUBLE PRECISION -- (Note: Available in Hive 2.2.0 and later)
  | STRING
  | BINARY      -- (Note: Available in Hive 0.8.0 and later)
  | TIMESTAMP   -- (Note: Available in Hive 0.8.0 and later)
  | DECIMAL     -- (Note: Available in Hive 0.11.0 and later)
  | DECIMAL(precision, scale)  -- (Note: Available in Hive 0.13.0 and later)
  | DATE        -- (Note: Available in Hive 0.12.0 and later)
  | VARCHAR     -- (Note: Available in Hive 0.12.0 and later)
  | CHAR        -- (Note: Available in Hive 0.13.0 and later)
 
array_type
  : ARRAY < data_type >
 
map_type
  : MAP < primitive_type, data_type >
 
struct_type
  : STRUCT < col_name : data_type [COMMENT col_comment], ...>
 
union_type
   : UNIONTYPE < data_type, data_type, ... >  -- (Note: Available in Hive 0.7.0 and later)
 
row_format
  : DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]
        [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
        [NULL DEFINED AS char]   -- (Note: Available in Hive 0.13 and later)
  | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]
 
file_format:
  : SEQUENCEFILE
  | TEXTFILE    -- (Default, depending on hive.default.fileformat configuration)
  | RCFILE      -- (Note: Available in Hive 0.6.0 and later)
  | ORC         -- (Note: Available in Hive 0.11.0 and later)
  | PARQUET     -- (Note: Available in Hive 0.13.0 and later)
  | AVRO        -- (Note: Available in Hive 0.14.0 and later)
  | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname
 
constraint_specification:
  : [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE ]
    [, CONSTRAINT constraint_name FOREIGN KEY (col_name, ...) REFERENCES table_name(col_name, ...) DISABLE NOVALIDATE
     

     
-- 创建普通表示例(用location来指定数据文件的位置，这样创建表的时候也加载了数据)
create table if not exists db_hive.student(
  id int comment '学生id', 
  name string comment '学生姓名'
) comment '学生表'
ROW FORMAT DELIMITED 
	FIELDS TERMINATED BY '\t'
    COLLECTION ITEMS TERMINATED BY '\n'
	STORED AS TEXTFILE
    LOCATION '/user/ubuntu/hive/warehouse/student'; 

-- 创建一个外部表示例(企业中大部分应用场景下使用外部表，创建外部表时必须要指定location参数)
create external table if not exists db_hive.student(
  id int comment '学生id', 
  name string comment '学生姓名'
) comment '学生表'
ROW FORMAT DELIMITED 
	FIELDS TERMINATED BY '\t'
    COLLECTION ITEMS TERMINATED BY '\n'
	STORED AS TEXTFILE
    LOCATION '/user/ubuntu/datas/student';

-- 普通表与外部表(external table)的区别：
1）创建表时：创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。
2）删除表时：在删除表的时候，内部表的元数据和数据会被一起删除， 而外部表只删除元数据，不删除数据。这样外部表相对来说更加安全些，数据组织也更加灵活，方便共享源数据。 
3）对于n个外部表指向HDFS同一个数据文件夹，当更改这个文件夹中的数据时，这n个外部表都会受到影响。 
    
     

-- 分区表
分区表实际上就是对应一个hdfs文件系统上的一个独立的文件夹，该文件夹下是该分区所有的数据文件。hive中的分区就是分目录，把一个大的数据集根据业务的需要划分成更小的数据集。在查询时通过where子句中的表达式来选择查询所需要的指定的分区，这样的查询效率会体改很多。
分区表的注意事项：对于普通表，先上传(hdfs put)表相关数据文件到HDFS相对应的文件目录再创建表的方式 与 先创建表再加载(hive load)表相关的数据文件到HDFS 这两种方式的效果是相同的；对于分区表，先上传(hdfs put)表相关数据文件到HDFS相对应的文件目录再创建表之后，select查询该表格，结果是查不到刚才上传的数据的，因为分区表除了在mysql数据库存放与普通表相同的信息，还在mysql存储分区的相关信息（可以查询mysql-metastore：select * from patitions），这时候HIVE端分区表需要采取修复的方法，以在mysql端增加新创建分区表的metastore-patition信息。
修复方法一：（一次性修复，msck：metastore check）
msck repair table db_hive_01.dept_patition;
修复方法二：（企业中常用）
alter table db_hive_01.dept_partition add patition(month='20170204',day='01');

     
     
-- 创建分区表
-- 先创建分区表，后向分区表中加载数据
create external table if not exists dept(deptno int, dname string, loc string)
partitioned by(month string, day string) --拥有两级分区
row format delimited fields terminated by '\t'
location '/user/ubuntu/datas/dept';
     
load data local inpath '/opt/datas/dept01.txt' into table db_hive_01.dept partition(month='201708', day='01');
load data local inpath '/opt/datas/dept02.txt' into table db_hive_01.dept partition(month='201708', day='02');     

-- 先存存在数据文件，后创建分区表
create external table if not exists dept(deptno int, dname string, loc string)
partitioned by(month string, day string) --拥有两级分区
row format delimited fields terminated by '\t'
location '/user/ubuntu/datas/dept';
     
alter table dept add partition(month='201708', day='01');
alter table dept add partition(month='201708', day='02');
或
msck repair table `db_hive_01.dept`;
     
     
-- 以as的方式创建表(CATS, create table as select)
create table if not exists db_hive_01.student_ctas
as select id,name from db_hive_01.student_01_ext; --会生成内部表的数据文件student_ctas/000000_0，包含了select查到的一些数据，删除内部表时也只删除内部表的数据文件
     

-- 以like的方式创建表(数据非常大，可以每天的数据放在新创建的一张表中)
create external table if not exsist db_hive_01.student_ext_03
like db_hive_01.student_ext_02 location '/user/ubuntu/datas/student02';
 

     
-- 查看表信息
desc db_hive.student;
desc extended db_hive.student;
desc formatted db_hive.student;
     
     
--查询某个分区的数据：
select * from db_hive_01.dept where month='201708';
-- 查看某张分区表的分区
show partitions db_hive_01.dept_partition;     
-- 合并分区的数据：
$vi xxx.sql
	select dname from db_hive_01.dept_partition where month='201702' and day='01'                 
	union all
	select dname from db_hive_01.dept_partition where event_month='201703' and day='01';
$bin/hive -f xxx.sql > xxx/result.txt;


-- 修改表名(如果是内部表，则相应的数据存储文件路径名也会被修改，注意修改的时候前面不能加数据库名)
alter table student rename to student01;
     
     
-- 清空表数据(只能清空内部表的数据)
truncate table db_hive_01.student;
     
    
-- group by聚合查询
-- 查询每个部门的平均工资示例：
select e.deptno, avg(e.sal) from db_hibve_01.emp e group by e.deptno;  
-- 查询每个部门中每个岗位的最高薪水示例：
select e.deptno,e.job,max(e.sal) maxsal from db_hibve_01.emp e group by e.deptno,e.job;
     
     
-- having过滤
-- having与where的区别：
-- where 是针对单条记录进行筛选，having 是针对分组的结果进行筛选
-- 求部门平均薪水大于2000的部门示例：
select e.deptno,avg(sal) avgsal from db_hive_01.emp e group by e.deptno having avgsal>2000; 
     
     
-- 多表查询join...on:
select e.empno,e.ename,d.deptno,d.dname from db_hive_01.emp e join db_hive_01.dept d on e.deptno=d.deptno;
     
 
     
-- Hive对查询结果的排序
-- 1. order by，对全局数据的排序，只在一台节点运行，仅仅只有一个reduce，使用需谨慎！
select * from db_hive_01.emp order by empno;

-- 2. sort by，对每个reduce内部数据进行排序（即对每个分区的数据排序，默认的分区是hash-key对reduce个数取模，执行之前先设置reduce的个数），不是全局排序，对全局结果集来说是没有排序的
set mapreduce.job.reduces = 3; --默认值为-1，一个reduce
insert overwrite local directory '/opt/datas/test/results/' 
select * from db_hive_01.emp sort by empno asc; -- 会生成mapreduce.job.reduces个结果文件
     
-- 3. distribute by，设置MR分区，对数据进行分区，结合sort by使用
-- 注意：distribute by 必须要在sort by 之前！
-- 像下面的例子，一般有多少个部门编号(deptno)，就使用多少个reducer执行reduce任务(这样每个结果文件存储了一个分区的数据)
insert overwrite local directory '/opt/datas/test/results/' 
select * from db_hive_01.emp e distribute by e.deptno sort by e.empno;
     
-- 4. cluster by
-- 当distribute和sort字段相同时，使用该方式
insert overwrite local directory '/opt/datas/test/results/' 
select * from db_hive_01.dept d cluster by d.deptno;

-- hive导入源数据常见的九种形式：
-- 1. 加载本地文件到hive表：
load data local inpath 'local_path' into table db_hive_01.dept;

-- 2. 加载dfs文件档hive表（加载完成后dfs文件会被删除）
load data inpath 'dfs_path' into table db_hive_01.dept;

-- 3. 覆盖表中已有的数据
load data inpath 'dfs_path' overwrite into table db_hive_01.dept;

-- 4. 加载数据到分区表
load data [local] inpath ‘filepath’ [overwrite] db_hive_01.dept partition(month='201708', day='02',..);

-- 5. 创建表时通过select加载：
create [external] table if not exists db_hive_01.dept_ctas
as select * from db_hive_01.dept;

-- 6. 创建表时通过insert加载：
create [external] table if not exists db_hive_01.dept_like
like db_hive_01.dept；
insert into db_hive_01.dept_like select * from b_hive_01.dept;

-- 7. 创建表时通过location指定加载：
create [external] table if not exists db_hive_01.dept(
	deptno int,
	dname string,
	loc string
) 
row format delimited fields terminated by '\t'
location 'pathxxx';

-- 8. 修复表时加载：
msck repair table db_hive_01.dept_patition;
alert table db_hive_01.dept_partition add patition(month='201702', day='01'); 

-- 9. import，将外部数据导入到hive表中去：
import [external] table db_hive_01.dept_partition [patition(month='201702', day='01')] from 'xxx/db_hive_01_dept_export' [location 'import_target_path'];



-- hive导出结果数据的四种常见的方式：
-- 1. 将结果数据插入到本地磁盘：
hive(db_hive_01)> insert [overwrite] [local] directory 'filepath'
							row format delimited
								fields terminated by '\t'
								collection items terminited by '\n'
							select * from db_hive_01.dept;

-- 2. 直接在本地将执行结果重定向到指定文件
bin/hive -e "select * from db_hive_01.dept;" > xxx/result.txt

-- 3. export方式将hive表中的数据导出到外部
-- 导出的路径是hdfs上的文件路径，导出操作会拷贝数据文件夹和文件到目标路径，而且会在目标路径下生成_metadata文件存储表的元数据信息
export table db_hive_01.dept_partition [partition(month='201702',day='01')] to 'distpath';

-- 4. sqoop工具导入导出hive数据到传统关系数据库 或 Hbase
略

-- hive交互窗口执行的命令历史记录位于
~/.hivehistory

-- 查看函数
show functions;
desc function upper;
desc function extended upper;