HIVE详解（三）：hive操作

1. hive数据类型
   1.1 原子数据类型：TINYINT,SMALINT,INT,BIGINT,BOOLEAN,FLOAT,DOUBLE,STRING,TIMESTAMP,BINARY。
   常用的有INT,BOOLEAN,FLOAT,DOUBLE,STRING,其中string对应数据库的varchar类型，是一个可变的字符串，不能生命最多存储多少个字符，理论上可以存储2GB的字符数。
   2.2 集合数据类型
   ARRAY,MAP,STRUCT.
   2.3 案例操作

   1）假设某表有如下一行，我们用 JSON 格式来表示其数据结构
   {
   "name": "songsong",
   "friends": ["bingbing" , "lili"] , //列表 Array,
   "children": { //键值 Map,
   "xiao song": 18 ,
   "xiaoxiao song": 19
   }
   "address": { //结构 Struct,
   "street": "hui long guan" ,
   "city": "beijing"
   }
   }
   2) 基于上述数据结构，我们在 Hive 里创建对应的表，并导入数据。
   创建本地测试文件 test.txt
   songsong,bingbing_lili,xiao song:18_xiaoxiao song:19,hui long guan_beijing
   yangyang,caicai_susu,xiao yang:18_xiaoxiao yang:19,chao yang_beijing
   注意，MAP，STRUCT 和 ARRAY 里的元素间关系都可以用同一个字符表示，这里用“_”。
   3) hive上创建测试表test
   create table test(
   name string,
   friends array<string>,
   children map<string, int>,
   address struct<street:string, city:string>
   )
   row format delimited fields terminated by ','
   collection items terminated by '_'
   map keys terminated by ':'
   lines terminated by '\n';
   字段解释：
   row format delimited fields terminated by ',' -- 列分隔符
   collection items terminated by '_' --MAP STRUCT 和 ARRAY 的分隔符(数据分割符号)
   map keys terminated by ':' -- MAP 中的 key 与 value 的分隔符
   lines terminated by '\n'; -- 行分隔符
   4）导入文本数据到test表
   hive (default)> load data local inpath '/root/test.txt' into table test;
   5) 访问三种集合列里的数据，以下分别是 ARRAY，MAP，STRUCT 的访问方式
   hive (default)> select friends[1],children['xiao song'],address.city from test where
   name="songsong";
   OK
   _c0 _c1 city
   lili 18 beijing
   Time taken: 0.076 seconds, Fetched: 1 row(s)
   

2. DDL数据定义
   2.1 数据库操作
   1）创建数据库
   i) 数据库在 HDFS 上的默认存储路径是/user/hive/warehouse/*.db
   hive (default)> create database db_hive;
   ii)避免要创建的数据库已经存在错误，增加 if not exists 判断。（标准写法）
   hive (default)> create database if not exists db_hive;
   iii) 创建一个数据库，指定数据库在 HDFS 上存放的位置
   hive (default)> create database db_hive2 location ‘/db_hive2.db’;
   2）修改数据库
   用户可以使用 ALTER DATABASE 命令为某个数据库的 DBPROPERTIES 设置键-值对
   属性值，来描述这个数据库的属性信息。数据库的其他元数据信息都是不可更改的，包括数
   据库名和数据库所在的目录位置。
   hive (default)> alter database db_hive set dbproperties(‘createtime’=‘20170830’);
   在mysql中查看修改结果 hive> desc database extended db_hive;
   db_name comment location owner_name owner_type parameters
   default Default Hive database hdfs://ns1/hive/warehouse public ROLE {createtime=20181205}
   3）查询数据库
   i）显示数据库 show databases;
   ii）过滤显示查询的数据库show databases like ‘db_hive’;
   ii）查看数据库详情 desc database extended db_hive;
   4）切换当前数据库 use db_hive;
   5）删除数据库
   i）删除空数据库 drop database db_hive2；
   ii）如果数据库不为空，可以采用cascade命令，强制删除 drop database db_hive cascade；
   2.2 表操作
   1）创建表
   i）建表语法
   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]
   ii）字段解释说明
   CREATE TABLE： 创建一个指定名字的表。如果相同名字的表已经存在，则抛出
   异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常；
   EXTERNAL： 关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际
   数据的路径（LOCATION），Hive 创建内部表时，会将数据移动到数据仓库指向的路
   径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的
   时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据；
   COMMENT：为表和列添加注释；
   PARTITIONED BY： 创建分区表；
   CLUSTERED BY： 创建分桶表；
   SORTED BY：排序，不常用；
   ROW FORMAT：指定分隔符，DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char] [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]；
   STORED AS：指定存储文件类型；
   LOCATION ：指定表在 HDFS 上的存储位置；
   LIKE：允许用户复制现有的表结构，但是不复制数据。
   2）管理表(内部表)
   默认创建的表都是管理表，又叫内部表，不适合和其他工具共享数据。
   案例操作：

   （1）普通创建表
   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';
   （2）根据查询结果创建表（查询的结果会添加到新创建的表中）
   create table if not exists student3 
   as select id,name from student;
   （3）根据已经存在的表结构创建表
   create table if not exists student4 like student;
   （4）查询表的类型
   desc formatted student2;
   （5）加载数据
   load data local inpath '/root/test.txt' into table student;
   （6）查询表
   select * from student；
   （6）删除表 删除hive的元数据和hdfs上的数据
   drop table student；
   

   3）外部表
   外部表指向已经在hdfs上存在的数据，删除外部表不会删除hdfs上的数据，仅删除该链接。
   4）分区表
   分区表实际上对应一个hdfs上一个独立的文件夹，该文件夹下是该分区所有的数据文件。hive分区就是分目录，把要给大的数据集根据业务需求分割成小的数据集，在查询时通过where子句中的表达式选择查询所需要的指定分区，这样查询效率会提高很多
   案例：

   （1）创建分区表语法
   create table dept_partition(
   deptno int, dname string, loc string
   )
   partitioned by (month string)
   row format delimited fields terminated by '\t';
   （2）加载数据到分区表中
   load data local inpath '/root/dept.txt' into table
   default.dept_partition partition(month='201809');
   （3）查询分区表中数据
   单分区查询
   select * from dept_partition where month='201809';
   多分区联合查询
   select * from dept_partition where month='201809'
   union
   select * from dept_partition where month='201808';
   （4）增加分区
   创建单个分区alter table dept add partition(month='201810');
   同时创建多规格分区alter table dept add partition(month='201811') partition(month='201812');
   （5）删除分区
   删除单个分区alter table dept drop partition(month='201809');
   删除多个分区alter table dept drop partition(month='201810'),partition(month='201811');
   （6）查看分区表有多少分区
   show partitions dept_partition;
   （7）查看分区表结构desc formatted dept_partition;
   

   （1）创建二级分区表
    create table dept_partition2(
    deptno int, dname string, loc string
    )
    partitioned by (month string, day string)
    row format delimited fields terminated by '\t';
    （2）加载数据到二级分区表中
    load data local inpath 'root/dept.txt' into table dept_partition2 partition(month='201809',day='13');
    （3）查询分区数据
    select * from dept_partition2 where month='201809' and day='13';
    （4）把数据上传到分区目录上，让给分区表和数据产生过关联的三种方式
    i）上传数据后修复
    上传数据dfs -mkdir -p /hive/warehouse/dept_partition/month=201809/day=12;
    dfs -put /root/dept.txt /hive/warehouse/dept_partition/month=201809/day=12;
    此时select查询不到刚上传的数据
    执行修复命令 msck repair table dept_partition;
    再次select可以查到数据
    ii）上传数据后添加分区
    执行添加分区alter table dept_partition add partition(month='201809',day='12');
   

   5）修改表
   （1）重命名表 alter table dept rename to dept2;
   （2）增加/修改替换 列信息
   alter table dept add columns(deptdesc string);增加一列
   alter table dept change column deptdesc desc int;更新列
   alter table dept replace columns(deptno string,dname string,loc string);替换列
   6）删除表 drop table dept;

3. DML数据操作

   1）数据导入
   （1）向表中装在数据（load）
   load data [local] inpath '/root/test.txt' [overwrite] into table dept [partition...];
   local:表示从本地加载数据到hive表，默认从高hdfs加载数据到hive表；
   overwrite：表示覆盖表中已有数据，否则表示追加
   2）通过查询语句向表中插入数据（Insert）
   i）基本插入数据
   insert into table dept partition(month='201809') values('10010','lisi');
   ii）基本模式插入（根据单张表查询结果）
   insert overwrite table dept parition(month='201809') select id,name from dept where month='201808';
   iii）多模式插入（根据多张表的查询结果）
   from dept 
   insert overwrite table dept partition(month='201807') select id,name where month='201809'
   insert overwrite table dept partition(month='201806') select id,name where month='201809'
   （3）查询语句中创建表并加载数据（as select）
   create table dept2 as select id,name from dept;
   （4）创建表时通过location指定加载数据路径
   create table dept3(id int,name string) row format delimited fields terminated by '\t' location '/hive/warehouse/dept3';
   （5）import数据到指定hive表中
   import table dept 2 partition(month='201809') from '/hive/warehouse/dept3'
   2）数据导出
   （1）insert 导出将查询结果导出到本地/格式化导出到本地/导出到hdfs
   insert overwrite [local] directory '/root/dept' [row format delimited fields terminated by '\t'] select * from dept;
   （2）hadoop命令导出到本地
   dfs -get /hive/warehouse/dept/month=201809/000000_0 /root/dept.txt;
   （3）hive shell命令导出
   hive -e 'select *  from dept;' > /root/dept.txt;
   （4）export 导出到hdfs上
   export table dept to '/hive/warehouse/dept';
   （5）sqoop 导出
   3）清除表中的数据
   truncate table dept;    truncate只能删除内部表，不能删除外部表中数据
   

4. 查询
   1）基本查询（select … from）
   （1) 全表和特定列查询
   全表查询：select * from dept;
   特定列查询：select deptNo,deptName from dept;0
   （2）列别名 as 可有可无
   select deptName [as] name from dept;
   （3）常用函数 count max min sum avg
   （4）limit语句 用于限制返回的行数
   2）条件过滤查询
   （1）where语句 过滤条件
   （2）条件查询 between and，is null，in()，like，rlike
   rlike后可以跟正则表达式
   （3）逻辑运算符 and/or/not
   3）分组
   （1）group by语句 通常会和聚合函数一起使用，按照一个或多个队列结果进行分组，然后每个组执行聚合操作
   计算 empt 表每个部门的平均工资
   select deptno,avg(t.sal) avg_sal from empt t group by t.deptno;
   计算 emp 每个部门中每个岗位的最高薪水
   select deptno, job, max(sal) from emp group by deptno,job;
   （2）having 针对查询结果中的列发挥作用，后面可以跟分组函数，只用于group by分组统计语句
   求每个部门的平均薪水大于 2000 的部门
   select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal >2000;
   4）join语句
   （1）等值join 只支持等值连接，不支持非等值连接
   根据员工表和部门表中的部门编号相等，查询员工编号、员工名称和部门编号；
   select e.empno, e.ename, d.deptno, d.dname from emp e join dept d on e.deptno = d.deptno;
   （2）内连接
   只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。
   select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno;
   （3）左外连接
   join操作符左边表中符合where子句的所有记录会被返回
   select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno =d.deptno;
   （4）右外连接
   join操作符右边表中符合where子句的所有记录会被返回
   select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno =d.deptno;
   （5）满外连接
   返回所有表中符合where语句条件的所有记录，如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值代替
   select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno =d.deptno;
   （6）多表连接
   连接n个表至少需要n-1个连接条件。
   SELECT e.ename, d.deptno, l. loc_name
   FROM emp e JOIN dept d ON d.deptno = e.deptno
   JOIN location l ON d.loc = l.loc;
   大多数情况，hive会对每对join连接对象启动一个MR任务。本例中会先启动一个MRjob对表e和d进行连接操作，然后再启动一个MRjob将第一个MRjob的输出和表l进行连接操作。
   （7）笛卡儿积
   条件：省略连接条件或连接条件无效，所有表的所有行相互连接
   5）排序
   （1）全局排序（order by）一个MR
   i）ASC升序（默认），DESC降序
   ii）可以按照别名排序
   按照员工薪水的 2 倍排序
   select ename, sal*2 twosal from emp order by twosal;
   iii）可以按多个列排序
   按照部门和工资升序排序
   select ename, deptno, sal from emp order by deptno, sal ;
   （2）每个MR内部排序（sort by）
   每个MR内部进行排序，对全局结果来说不是排序
   设置 reduce 个数 set mapreduce.job.reduces=3;
   查看设置 reduce 个数 set mapreduce.job.reduces;
   根据部门编号降序查看员工信息
   select * from emp sort by empno desc;
   将查询结果导入到文件中（按照部门编号降序排序）
   insert overwrite local directory ‘/root/sortby-result’ select * from emp sort by deptno desc;
   （3）分区排序 distribute by
   类似MR中的partition，进行分区，结合sort by使用，要求写在 sort by语句之前，对于distribute by测试，一定要配多个reduce
   先按照部门编号分区，再按照员工编号降序排序。
   set mapreduce.job.reduces=3;
   insert overwrite local directory ‘/root/distribute-result’ select * from emp distribute by deptno sort by empno desc;
   （4）cluster by
   当distribute by 和sort by字段相同时，等价于使用cluster by方式
   但是只能是倒序排序，不能指定ASC/DESC
   6）分桶及抽样调查
   （1）分桶表数据存储
   分区针对的是数据的存储路径，分桶针对的是数据文件。
   分区提供一个隔离数据和优化查询的便利方式，但是并非所有的数据集都可以形成合理的分区，而分桶是将数据集分解成更容易管理的若干部分的另一个技术。
   创建分桶表
   create table stu_buck(id int, name string)
   clustered by(id) into 4 buckets
   row format delimited fields terminated by ‘\t’;
   导入数据到分桶表中(需要设置set hive.enforce.bucketing=true;)
   load data local inpath ‘/root/student.txt’ into table stu_buck;
   （2）分桶抽样查询
   select * from stu_buck tablesample(bucket 1 out of 4 on id);
   注：tablesample 是抽样语句，语法：TABLESAMPLE(BUCKET x OUT OF y) 。y必须是table总bucket数的倍数或因子。hive根据y的大小决定给抽样的比例，本例总共4个bucket，y=4，所以抽取总bucket数/y =1，所以抽取一个bucket的数据，x代表从哪个bucket开始抽取，x的值必须小于或等于y的值，否则报错。
   （3）数据块抽样
   hive提供了另外一种按照百分比进行抽样，这种是基于行数的，按照输入路径下的数据块的百分比进行的抽样
   select * from stu tablesample(0.1 percent) ;
   这种抽样方式不一定适用于所有的文件格式，另外最小的抽样单元是一个hdfs数据块，因此，如果表的数据大小小于128M的话，那么将返回所有行。

5. 函数
   5.1 系统自带的函数
   1）查看系统自带的函数 show functions;
   2）显示自带函数用法/并带例子
   desc function [extended] upper;
   5.2 自定义函数UDF
   1）UDF分为三种
   （1）UDF 一进一出
   （2）UDAF聚集函数，多进一出
   （3）UDTF一进多出
   2）编程步骤
   （1）继承org.apache.hadoop.hive.ql.UDF
   （2）需要实现evaluate函数，evaluate函数支持重载；
   （3）在hive的命令行窗口创建函数
   添加jar包 add jar linux_jar_path;
   创建function create[temporary] function function_name as clsss_name;
   （4）在hive的命令行窗口删除函数
   drop [temporary] function function_name;
   注意：UDF必须要有返回类型，可以为null，但是不能为void。
   3）自定义UDF函数开发案例

   （1）将hive lib下的jar包到拷到java工程中
   （2）创建一个类
   	package com.atguigu.hive;
   	import org.apache.hadoop.hive.ql.exec.UDF;
   	public class Lower extends UDF {
   		public String evaluate (final String s) {
   			if (s == null) {
   				return null;
   			}
   				return s.toString().toLowerCase();
   		}
   	}
   （3）打成jar包上传到/root/udf.jar
   （4）将jar包添加到hive的classpath add jar /root/udf.jar
   （5）创建临时函数与开发好的java class关联
      create temporary function my_lower as "com.atguigu.hive.Lower"
   （6）可以在hql中使用自定义函数
   select enamel, my_lower(ename) lowername from emp;