HIVE SQL相关操作

※直接上干货

        刚开始学数仓时候都被一直灌输HIVE数仓不是数据库而是数据仓库，既不产生数据也不存储数据，只分析数据，起初不是很理解，直到自己长久的使用了之后才慢慢的理解，HIVE基于Hadoop，这就给HIVE提供了可靠的数据来源，而分析的话，很早之前是基于MR的，但是现在MR几乎已经被淘汰了，改为了基于Spark来计算分析了。

        基于数仓说直白点就是写各种hive SQL，SQL语言之间都大同小异，在经过了长久的SQL学习使用之后，对于hive SQL也就很得心应手了，以下是我认为的数仓所特有的一些知识点：

1、SQL中各字段的执行顺序：

   from-where-group by-having -select-order by -limit
        (7)    SELECT
        (8)    DISTINCT <select_list>
        (1)    FROM <left_table>
        (3)    <join_type> JOIN <right_table>
        (2)    ON <join_condition>
        (4)    WHERE <where_condition>
        (5)    GROUP BY <group_by_list>
        (6)    HAVING <having_condition>
        (9)    ORDER BY <order_by_condition>
        (10)   LIMIT <limit_number>

  2、创建内部表的两种方式：

 -- 1、as
    create table copy_student1 as select * from student;
    select * from copy_student1;
    desc copy_student1;
  -- 2、like
    create table copy_student2 like student;
    select * from copy_student2;
    desc copy_student2; 

对于as来说，创建的内部表是原表的全部，对于like来说，创建的内部表只是原表的元数据

3、增加一个字段

alter table test_db.person
    add columns (gender string, height double);

4、修改字段

-- hive sql中没有modify操作,只有change

alter table test_db.person
    change gender sex string;

5、修改表名

-- 自定义位置的表名不会修改
alter table test_db.person
    rename to test_db.person_tb;

6、hive中表的复制

如果是将数据从文件上传到对应的数据表下，则只是简单的数据的上传操作。而在hive中对表的复制，操作完之后，原位置不再有数据，即数据都迁移到了复制的位置，此时为表的mv操作。

7、内部表与外部表

-- 进行删除操作时候，如果表为内部表，则会将元数据以及表内的
-- 数据都给删除，如果表为外部表，则只会删除元数据信息，
-- 根本上来说数据是由HDFS存储管理的

8、分区与分桶

分区就类似于按照层级将数据分开存储，一部分存储一块数据，分开存储。
分桶就类似于将数据分成若干块，采用哈希排序的方法进行分桶

分区：
        在建表时候添加一个字段： partitioned by (month int) #指定分区
        分区表的数据加载方式：  partition (year='2021', month='12',day='31') 在原来的加载方式上添加一个分区字段
        ※多级分区表必须多个层级间有从属关系,例如年月日, 省市区
        ※多级分区表的层级一般不超过三级,因为数据层级过多,就会产生大量的小文件.
    -- 增加分区
    load data local inpath '/root/hive_data/score.txt' into table score_parts partition (year='2021', month='12',day='30');
    -- 删除分区
    alter table score_parts drop partition (year='2021', month='12',day='30');
    -- 修改分区名称
    alter table score_parts partition (year='2021', month='12',day='30') rename to partition (year='2021', month='12',day='3');


分桶：
        桶内排序就是将一个桶内的数据按照一定规则排序，所以操作顺序也是先分桶再排序          
        -- 当我们按照同一个字段分桶且排序,并且进行升序排列时
        
        #不能指定分桶，只能指定MR的任务数来达到分桶的效果
         set mapreduce.job.reduces = 3;
-- cluster by = distribute by + sort by
        distribute by：仅分桶
        sort by：仅排序
        distribute by (仅分桶) + sort by (仅排序)
 ----- cluster by 与 distribute by + sort by比较：       
-- cluster by 具有局限性, 分桶和排序字段必须相同,且只能使用升序排列
-- 所以 distribute by + sort by 更加灵活 

    
分区表与分桶表的异同：
    异：1、分区表使用的是元数据信息，分桶使用的是实体字段
        2、分区表将数据拆分成不同的目录存放，分桶是将数据分组存放
        3、分区表主要是提高了查询效率，分桶主要是提高了连接效率
    同：1、分区表、分桶表都是对表的优化手段
        2、都提高了查询效率

9、concat_ws

-- concat_ws 按照指定分隔符进行字符串连接
-- 格式: concat_ws(分隔符, 字符串1, 字符串2.....)
select concat_ws('❤', '中国');    

10、trim

--trim 裁剪字符串左右两侧的空格,不清除字符串中间的空白
        select trim('   hello   world!   ');

11、split

-- split 将字符串拆分[拆分之后，字符串将变为一个数组]
-- 格式: split(被拆分的字符串, 拆分使用的分隔符)

12、get_json_object

-- json数据我们可以先理解为 map类型和array类型的互相嵌套
-- 格式: get_json_object(json数据, 数据提取规则)
-- $ 代表整条json数据
-- $.name 代表当前json数据中 key为name的键值对所对应的值
        select get_json_object('{"name":"杨过", "age":"18"}', '$.name');   -- 杨过,   $表示json对象
        -- $.[1].name 代表json数据中下标为1的元素中键name所对应的值
        select get_json_object('[{"name":"杨过", "age":"18"}, {"name":"小龙女", "age":"26"}]', '$.[1].name');
        select get_json_object('[{"name":"杨过", "age":"18"}, {"name":"小龙女", "age":"26"}]', '$.[*].name');    

13、nvl

-- nvl 函数,获取该数据,如果该数据为空则获取默认值
-- 格式 : nvl(目标字段, 默认字段);
        select nvl(12, 0);
        select nvl(null, 0); 

14、coalesce

-- 获取数据集中第一个不为null的数据
select coalesce(1, 2, 3, null);
-- 我们可以判断array类型数据,但是其会作为一个数据看待
select coalesce(`array`(null, 'chuanzhi', 'boke'));  

15、array_contains

-- 判断元素是否在数组中
        select array_contains(`array`(1, 4, 7, 12), 13);
-- 判断数据元素是否在map的值内部
        select array_contains(map_values(`map`('name', '小明', 'age', 18, 'gender', '男')),'男')