Hadoop生态系统-Hive

DML

数据导入：将HDFS数据与hive的表映射

• 数据导入：将HDFS数据与hive的表映射

  1. load本地映射表
  • 准备数据：本地
    
    
    
  • 追加数据：再导入
    
  • 覆盖数据
    
  • 从本地映射表，顺便也将数据上传到了HDFS
  2. loadHDFS映射表
  • 准备数据
    
    
    
  • 在HDFS中load相当于做了一个剪切的操作
    

3. insert
   • 会跑一个MR程序，速度十分慢。新插入的数据在最上边，原因：HDFS中排列顺序如此，select此表时按照这个顺序查询。
     
     
   • 从一个表中查出数据插入到新表中
     
     
     
   • 直接在建表时从别处表查询数据插入as select
     
   • location
     
     
     

数据导出：将统计或者是查询的结果导出到文件

• 数据导出：将统计或者是查询的结果导出到文件

1. insert

   • 将数据导出到本地。通过跑一个MR程序
     
     
   • 按照指定格式导出数据到本地
     
   • 指定格式导出数据到HDFS
   • 对于表中null数据的导出（本地）
     
     
     
     
     
     

2. hive -e

   • 不用登录，直接执行SQL，完成后退出
     

3. export

   • 不仅导出数据还会导出表的元数据。常用于集群间的数据迁移
     
   • 结合import，直接建表插入数据
     
     

清空表数据

• 清空表数据

1. 管理表（内部表）
   
   
   

2. 不能清除外部表数据
   
   
   
   

DQL

查询语法

SELECT [ALL | DISTINCT] select_expr, select_expr, ...  -- 查哪些
  FROM table_reference  -- 从哪查
  [WHERE where_condition]  -- 过滤条件
  [GROUP BY col_list] -- 分组
  [HAVING having_contiditon] -- 分组后过滤条件
  [ORDER BY col_list] -- 全局排序
  [CLUSTER BY col_list  -- 分区排序
    |
   [DISTRIBUTE BY col_list]  -- 分区
   [SORT BY col_list] -- 区内排序
  ]
 [LIMIT number] -- 限制返回的条数

数据准备

• 创建表导入数据
  
  
  
  
  
  

基本查询

• 全表或特定列的查询：*或表字段名以逗号隔开。SQL 语言大小写不敏感
• 列别名：AS 可以省略
• 常用函数：count（），max（），min（），avg（），sum（）。
• limit和where
  • 常用的where条件：between 400 and 800（400~800）范围过滤
    is/not null是否为空
    或in（100，200）
• 模糊匹配：like或rlike(可以通过java的正则表达式匹配)
  • 比如：where 字段 like/rlike … ；
    ​匹配以字母A开头的：like ‘A%’
    匹配第二个字母为A的：like ‘_A’
    ​匹配含有字母A的：rlike ‘[A]’
• 逻辑运算符或与非：or and not
• 算数运算符和常用函数最大最小平均总和
  • 加减乘除取余，取反：~A
    max,min,count,avg（）

分组：常结合聚合函数一起使用

• 计算emp每个部门的平均工资

  • 也要跑一个MR的，速度很慢
  • 
    
  • 开启本地模式报错，原因是默认分给jvm的内存只有256mb，内存溢出了
    
    
    
  • 更改为本地模式后，速度明显变快。set hive.exec.mode.local.auto=true;只对本窗口生效
    
    
    
    
    

• 计算emp每个部门中每个岗位的最高薪水

• 计算emp中每个部门中最高薪水的那个人

  • 子查询
    
    此处容易出错
    select deptno,max(sal) max_sal,depname from emp group by deptno;
    原因就是：结果不匹配。分组之后每一组的deptno一样，所以查出来的deptno每组只有一个，最高工资也只有一个，但是每组的人可不止一个，所以错误。
    分组之后，select后边只能是分组字段或聚合函数

• 分组之后，select后面只能跟组标识(分组字段) 和 聚合函数(分组函数)
  

• 计算emp中除了CLERK岗位之外的剩余员工的每个部门的平均工资大于2000的部门和平均工资

  • 分组之前的过滤用where，分组之后的过滤用having

join查询

• 基本概念
  • join的方式

  内连接   A inner join B  on ....
     内连接的结果集取交集
  外连接  
    主表(驱动表) 和  从表(匹配表)
    外连接的结果集主表的所有数据 +  从表中与主表匹配的数据. 
    左外连接  A left outer join B  on ....    A 主 B 从
             B right outer Join A  on.... 
    右外连接  A right outer join B  on ....   A 从 B 主
             B left outer join  A  on ....
  自连接  满外连接(full outer join )

• join图

  • 

• 准备表的数据

  • emp
  • dept

• emp 和 dept共有的数据(内连接)
  
  

• emp所有的数据 和 dept中与emp匹配的数据

  • 左外连接和右外连接都可以实现。主要是分清楚谁是主表谁是从表
    
    
    

• dept中所有的数据 和 emp中与dept匹配的数据

  • 左外
    
  • 右外
    

• emp表独有的数据

  • emp所有的数据 和 dept中与emp匹配的数据中dept分组字段为空的数据
    

• dept表独有的数据

  • dept所有的数据 和 emp中与dept匹配的数据中emp分组字段为空的数据
    

• emp 和 dept 所有的数据(全外连接，满外连接)

  • emp所有的数据 和 dept中与emp匹配的数据+dept中所有的数据 和 emp中与dept匹配的数据（union all不去重）
    
  • emp所有的数据 和 dept中与emp匹配的数据+dept中所有的数据 和 emp中与dept匹配的数据（union 去重）
    
  • full outer join
    

• emp 和 dept 独有的数据

  • emp 和 dept 所有的数据(全外连接，满外连接)中过滤出emp分组字段为空，或dept分组字段为空的数据
    

• 多表连接

  • 表数据准备
    
    
    
  • 查询 员工名 部门名 位置名
    

• 笛卡尔积

  • 省略连接条件 连接条件无效 所有表中的所有行互相连接都会造成笛卡尔积。
    

排序

• 全局排序：order by（只有一个Reducer）

  • 本地模式
    
  • 集群模式
    
    
  • 设置Reducer数量后再去集群模式下全局排序，Reducer个数还是一个
    
    

• 别名排序

  • 按照员工薪水的2倍排序

• 多个列排序

  • 按照部门和工资升序排序 按照工资和部门升序排序
    

• 每个Reducer内部排序：sort by

  • 设置Reducer个数
  • 由部门编号降序查看员工信息，分区排序，如何分区：随机。计算机中没有真正的随机，都是假随机。只要随机算法不变，数据不变，随机结果也就不变。所以sort by不会单独使用，使用前提是要先有分区
    • 分区：distribute by。分区：分区字段的数据（10，20，30，50）对Reducer个数（3）取余
    • 分区将数据写到本地
      

• cluster by

  • 当distribute by和sort by字段相同，排序是升序排序时，可以用cluster by代替