MySQL以及HIVE

发表于 | 浏览 次

一、使用基础

二、hive tips

  1. 如何转换数据类型
    Hive内置数据类型由基本数据类型和复杂数据类型组成，例如int，float，double，string，date等。常常需要在数据类型之间进行转换。
    转换方法：
    cast(str_column as "你要转换的类型")

  2. 分割符问题
    hive存储数据时默认分隔符是^A,对应”\001”,download下来的文本分隔符是”\x01”
    hive设置存储（或者读取）分割符语句：

    1
    2
    insert overwrite directory 路径
    row format delimited fields terminated by '\t'
  1. 分组排序问题
    对group by 的多个字段取 top N的时候常常用到
    新版本的hive是有自带的函数的
    1
    2
    3
    4
    row_number() over ([partition col1] [order by col2])
    rank() over ([partition col1] [order by col2])
    dense_rank() over ([partition col1] [order by col2])
    percent_rank over ([partition col1] [order by col2])

但是公司内部的hive版本不支持，目前的解决方案是UDF,具体参考 bigdata的作业组内的具体实现
这篇博客也有介绍

  1. 字段切分问题
    split("116:151:1", '\\:')[0]
    有两个参数，第一个参数是字符串，第二个参数是分割符，尽量转义一下避免报错；切割完是一个array，可以索引

  2. order by, sort by, distribute by 问题

    • order by 全局排序，一个reducer进行排序
    • sort by 会在每个reducer内进行局部排序，但是同样的值可能出现在不同的分区
    • distribute by 控制map的输出在reducer中是如何划分的，把相同的某个字段的记录分配到同一个reducer中去，然后使用sort by来进行排序
    • cluster by
      cluster by = distribute by + sort by

  3. 关于join

    • join 中需要将大表写在靠右的位置；hive的jion会从左往右执行，且会缓存最右边的表，所以建议表的大小由小到大排列。当然也可以显示地声明哪张表是大表；
    • join 只返回两个表连接条件满足的记录
    • left outer join（在需要保留左表全部记录时使用）
      会保留左表所有的记录，右表没有符合连接条件的记录，选择的列的值会是NULL。满足where条件的记录都会返回，但是outer join 会先执行join语句，再通过where语句进行过滤。
      right outer join和left outer join 类似。
      left out join不能连续3个以上使用，必须2个一组，2个一组包装起来使用
      left join在MySQL里和left outer join的功能是一致的，但是有些版本的hive是没有left join的，很可能不能用
      备注：hive里left join和left outer join的功能也是一致的，但是只能显示的使用left outer join，用left join会报错

    • left semi join
      会返回左表的记录，前提是记录右表也满足on语句的判定条件。但此时，select和where语句不能应用到右表的字段。semi-join对于左表一条指定的记录，在右表一旦找到匹配记录，hive就会停止扫描。
      semi join 最常见的功能是实现 exist in 的功能
      举个例子（要实现如下的功能）：

      1
      2
      3
      4
      5
      SELECT a.key, a.value
      FROM a
      WHERE a.key in
      (SELECT b.key
      FROM B);

      用left out join是这样写

      1
      2
      3
      SELECT a.key, a.value
      FROM a LEFT OUTER JOIN b ON (a.key = b.key)
      WHERE b.key <> NULL;

      用left semi join是这样写

      1
      2
      SELECT a.key, a.val
      FROM a LEFT SEMI JOIN b on (a.key = b.key);

    • 笛卡尔积join CROSS JOIN 待补充

    • map-side join
      如果所有表中的只有一张表是小表，那么可以在最大的表通过mapper的时候将小表完全放到内存中，hive可以在map端执行连接过程。从而可以减少reduce过程。使用/*+ MAPJOIN(--).
    hive.auto.convert.join
    1
    set hive.mapjoin.smalltable.fillesize=250000000
    • INNER JOIN 仅对满足连接条件的cross中的列

  4. 字符串的匹配
    一个模糊匹配多个query的例子
    `normalized_query RLIKE '%糯米|贴吧|小说|新闻|网址|视频%'

  5. hive reduce阶段内存不足
    尤其是使用python脚本最容易出现，比较快的方法是增加内存
    set mapred.reduce.memory.limit=1600;

  6. hive 小文件合并
    HIVE将运算好的数据写回hdfs，有时候会产生大量的小文件，会带来很多问题。所以hive在正常job执行完之后，会起一个conditional task，来判断是否需要合并小文件，如果满足要求就会另外启动一个map-only job 或者mapred job来完成合并。
    输出阶段

    1
    2
    3
    4
    5
    6
    7
    8
    hive.merge.mapfiles(默认为true)
    正常的map-only job后，是否启动merge job来合并map端输出的结果
    hive.merge.mapredfiles(默认为false) 其实reduce数据量不大，但是给你分了200个区，就比较蛋疼了，所以需要限制一下
    正常的map-reduce job后，是否启动merge job来合并reduce端输出的结果，建议开启
      
    set hive.merge.size.per.task = 256*1000*1000 #合并文件的大小
    set hive.merge.smallfiles.avgsize=16000000 #当输出文件的平均大小小于该值时，启动一个独立的map-reduce任务进行文件merge

    输入阶段

    1
    2
    3
    4
    mapred.max.split.size(默认256MB)256000000
    mapred.min.split.size.per.node(默认1 byte)100000000
    mapred.min.split.size.per.rack(默认1 byte)100000000
    set hive.input.format=org.apache.Hadoop.hive.ql.io.CombineHiveInputFormat;  #执行Map前进行小文件合并

  7. 如果要跑大量的数，尽量不要使用带Python脚本的任务，将会非常非常慢
    例子：从insight_hour表诸如浏览器或者手机品牌时，要使用ua识别的脚本来解析，但是一旦使用这个脚本，4000个map将跑3个小时左右，非常消耗资源

MySQL基础

  • mysql补位
    MySQL LPAD() function
    LPAD(str, len, padstr)

学习MySQL函数的好地方
MySQL FUNCTIONS and OPERATORS

  • mysql日期的diff
    Difference between two dates in MySQL
    -

    1
    2
    3
    4
    SELECT TIMEDIFF('2007-12-31 10:02:00','2007-12-30 12:01:01');
    -- result: 22:00:59.
    SELECT TIMESTAMPDIFF(SECOND,'2007-12-30 12:01:01','2007-12-31 10:02:00'); 
    -- result: 79259  the difference in seconds with the time.
    • SELECT DATEDIFF('2010-10-08 18:23:13', '2010-09-21 21:40:36') AS days;