hive知识点二

hive的查询

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list [HAVING condition]]
[CLUSTER BY col_list|[DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list]
ORDER BY col_list：全局排序 stu按照年龄排序 select * from stu order by age desc; 默认为升序
SORT BY：局部排序  select * from stu sort by age desc; 根据reducetask的局部进行排序但是当reduce的个数为1的时候
和order by 一样；最终的输出结果是随机的。
DISTRIBUTE BY col_list：类似分桶 按照指定的字段和你设置的reducetask的个数进行分桶但是并没有排序。
CLUSTER BY col_list: 分桶之后进行排序等于DISTRIBUTE BY+SORT BY 注意cluster by和sort by不可以同时使用适用于
两个字段一样

• set hive.exec.mode.local.auto=true;设置本地模式

• set mapreduce.job.reduces=3设置reducetask

• 建表语句

  • clustered by 分桶
  • sort by 排序
  • partitioned by 分区

• 查询语句

  • order by 全局排序
  • sort by 局部排序
  • distribute by 类似于分桶
  • cluster by 分桶加排序

• 分桶表的作用：

  • hive中的表都是大表，大表关联大表，大表1关联大表2就需要全部扫描大表2。提升效率需要将两个表进行分桶。
  • 提高join的效率
  • 提高抽样的性能散列每个桶中的数据足够散列。
    #hive的fetch过程 抓取过程

• 执行hql语句的时候 一旦要进行mr过程效率低

• 这个时候可以设置将一些hive语句不需要走mr程序尽量不走mr

• hive的2.0版本中查询支持spark和tez 默认为mr

  • Default Value: minimal in Hive 0.10.0 through 0.13.1, more in Hive 0.14.0 and later

  • Added In: Hive 0.10.0 with HIVE-2925; default changed in Hive 0.14.0 with HIVE-7397

      none:  Disable hive.fetch.task.conversion (value added in Hive 0.14.0 with HIVE-8389)表示所有的
      hql语句都需要转换为mr程序
      minimal:  SELECT *, FILTER on partition columns (WHERE and HAVING clauses), LIMIT only 
      查询所有语句 过滤条件在分区列 limit 三种语句不走mapreduce剩下的都走mapreduce
      more:  SELECT, FILTER, LIMIT only (including TABLESAMPLE, virtual columns)
      所有的select 和过滤 limit
    

• join

  • hive中的joinhive中的join注意
    • 等值连接不支持不等值连接 select a.,b. from a join b on a.id=b.id;
    • 支持支and不支持or select a.,b. from a join b on a.id=b.id and a.name=b.name; hive底层就是把hql语句转化为mr
    • join可以支持多表连接 select a.,b.,c.* from a join b on a.id=b.id join c on a.id=c.id;多表连接如果是同一个字段只需要一个mr，如果字段不是同一个字段则是需要多个mr。
  • 内连接 inner join 默认就是内连接的
  • 外连接 左外连接 left outer 全连接 左右表都显示 full outer左右表取并集
  • 半连接 left semi 左表中在右表中存在的数据 并且返回左表中的数据
    #hive的数据类型

• 数字类

  类型	长度	备注
  TINYINT	1字节	有符号整型
  SMALLINT	2字节	有符号整型
  SMALLINT	2字节	有符号整型
  INT	4字节	有符号整型
  BIGINT	8字节	有符号整型

• 日期时间类

  类型	长度	备注
  TIMESTAMP	–	时间戳，内容格式：yyyy-mm-dd hh:mm:ss[.f…]
  DATE	–	日期，内容格式：YYYY­MM­DD
  INTERVAL	–	–

• 字符串类

  类型	长度	备注
  STRING	–	字符串
  VARCHAR	字符数范围1 - 65535	长度不定字符串
  CHAR	最大的字符数：255	长度固定字符串

• Misc类

  类型	长度	备注
  OOLEAN	–	布尔类型 TRUE/FALSE
  BINARY	–	字节序列

• 复合类

  类型	长度	备注
  ARRAY	–	包含同类型元素的数组，索引从0开始 ARRAY
  MAP	–	字典 MAP<primitive_type, data_type>
  STRUCT	–	结构体 STRUCT<col_name : data_type [COMMENT col_comment], …>
  UNIONTYPE	–	联合体 UNIONTYPE<data_type, data_type, …>

• 表的创建

    create table union_testnew(foo uniontype<int, double, string, array<string>, map<string, string>>)
    row format delimited
    collection items terminated by ','
    map keys terminated by ':'
    lines terminated by '\n'
    stored as textfile; 
  

• hive的视图

  • 创建视图：create view my_view as select * from stu where age>=19;
    • 注意事项相当于存储了sql语句的快捷方式
    • hive中的视图只有逻辑视图没有物化视图
    • hive中的视图只支持查询不支持增删改
    • hive中的视图保存的值只保存sql语句不保存存储结果
  • 查看视图：show tables；既显示表信息有显示视图信息 show views;只显示视图信息 元数据存储在TBLS中
  • 删除视图：drop view my_view

hive的内置函数

1. 分为3类
   1. UDF：自定义函数一对一的
   2. UDAF:自定义聚合函数 多对一
   3. UDTF:自定义表函数 一对多
2. 查看内置函数：show functions;
3. 查看函数得描述信息:desc function 函数
4. 查看函数的详细信息：desc function extended 函数
5. floor 取不大于某个数的最大整数； select floor(4.5);
6. ceil 取不小于这个数的最小整数 select ceil(4.5);
7. substring字符串截取 select substring(“hello word”,5); 截取的时候从1开始 负数的时候从右向左
8. instr 返回第二个字符串在第一个字符串第一次所出现的下标 select instr(“nihao”,“ha”); 如果后面的字符串是没有的值则会返回0
9. concat 字符串拼接按照传参的顺序进行拼接
10. concat_ws 字符串拼接按照传参的顺序进行拼接 第一个传分隔符
11. array生成数组select array(1,2,3,5,4);
12. map生成map select map(“name”,“a”,“d”,“ds”); 奇数key value 偶数
13. nvl 采集数据可能有null值 如果参数1为null则返回参数2 select nvl(null,2); select nvl(1,2);
14. if 类似三目表达式 select if(表达式，参数1，参数2） select if(2>3,3,6);

---

UDTF:

1. explode 将数组分成多个行 获将 map分为多个行 2列 explode(参数为一个数组或map)select explode(array(1,2,3,4)); 当同时查询炸裂字段和普通字段需要使用横向虚拟视图。

    select tf.* from (select 0) t lateral view explode(array('A','B','C')) 横向视图的别名 
    as 字段的别名(map应该给两个);
   

---

自定义函数

• 建立一个工程 导入jar包
• 继承udf 重写evluat方法
• 使用 ：
  • 把写的工程导成jar包
  • 放入linux
  • 添加到hive的下add jar jar包的路径
  • 查看 list jars
  • 给自定义函数起别名 并在hive中注册这个函数 create temporary function my as ‘udf.MyUdf’;这种方式是临时函数客户端关闭就没有了生产中就使用这种方式
  • 查看函数库中有没有你的函数
  • 根据方法名和参数来确定用哪个
  • 可以变成永久函数 但是需要修改源码

hive解析json函数

• get_json_object(json_text,path)

• create table rating(line string)

• load data local inpath ‘/home/hadoop/json’ into table rating;

• select get_json_object(line,"$.firstName") from rating ; 取值json的值

     $   : Root object 根目录
    .   : Child operator 子对象 访问下一级节点的
    []  : Subscript operator for array 数组下标用于区数组的
    *   : Wildcard for []
  

• transform方式

  • 使用脚本来解析数据 python脚本

      #！/usr/bin/python
      import sys
      import datetime
      for line in sys.stdin:
      	line=line.strip()
      	movie,rate,unixtime,userid=line.split("\t")
      	weekday=datetime.datetime.formtimestamp(float(unixtime)).isweekday()
      	print '\t'.join([movie,rate,str(weekday),userid])	
    

• 脚本使用

  • 如果想要hive找到脚本需要将脚本放到claapath ； add file /home/hadoop/mypython.py;

  • select transform(需要解析原数据库中的字段) using ‘python mypython.py’ as (python解析出来的字段)from 表名;

  • row_number() over(分组排序规则 partition by order by) 分组加行号在每一组加从1开始顺序添加

      select id,name,gender,age,derpart,row_number() over(partition by derpart order by age)
       as index from stu; 
    

  • rank（）over(分组排序规则) 用于排名计数排名如果相同拍相同的名次

      select id,name,gender,age,derpart,rank() over(order by age) as index from stu;
    

  • dence_rank() over(分组排序规则)用于排名 不计数排名连续排名并列的显示一样。

  • 业务需求中会出现求topN需求 每个部门中年龄最大的员工

• 分隔符 hive中分隔符默认为单字节的 如果想要使用多字节分隔符 修改输入

    create table test(id int,name string)row format delimited fields terminated by '::';
    load data local inpath '/home/hadoop/test01' into table test; 
    1	
    2	
    3	
    4	
  

  • 修改这个类为正则表达式的类

      SerDe Library:      	org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe	 序列化库这个库只能解析单字节的如
      果想要解析多字节修改这个类
      InputFormat:        	org.apache.hadoop.mapred.TextInputFormat	 
      OutputFormat:       	org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOut
    
      serde 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe' 定义序列化的类库指定的是正则表达式的方式		
      'input.regex'='(.*)\\|\\|(.*):定义输入的正则表达式	
      'output.format.string'='%1$s %2$s %3$3' 定义输出的正则表达式怎么从你的正则表达式中抽取结果
      create table test_rg(id string,name string)row format 
      serde 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe' with 
      serdeproperties('input.regex'='(.*)::(.*)','output.format.string'='%1$s %2$s') 
      stored as textfile;
    

  • 修改底层源码 不建议

• hive的shell

  • 进入hive之前
    • hive -i 从文件初始化hql
    • -e 从命令行执行指定的hql hive -e ‘show databases’ 不进入hive就可以执行hql语句
    • -f 执行hql脚本 hive -f hive.conf
    • -v 输出执行的hql语句到控制台
    • -S 表示以不答应日志的形式执行操作
    • -p
    • -hiveconf hive -hiveconf mapred.reduce.tasks=3 设置属性值这种方式启动的时候这个参数已经被改变
  • 进入hive之后
    • quit 退出客户端
    • set mapred.reduce.tasks;查看reduce的个数
    • set mapred.reduce.tasks=10；给属性赋值
    • add file 添加脚本文件
    • add jar 添加jar 添加到class path
    • list jar（file） 显示所有的文件或jar
    • dfs 可以调用hdfs命令
    • source 执行sql的脚本需要对数据库批量执行sql语句