Hive(二)

• HQL执行优先级
  • from、where、group by、having、ordert by、join、select、limit
• where条件里不支持不等式子查询，实际上是支持in、exists、not exists
  • 列出与“SCOTT”从事相同工作的所有员工。

  • select t1.EMPNO
            , t1.ENAME
            ,  t1.JOB
    from emp t1
    where ti.ename !='SCOTT' and t1.job in (
            select job
            from emp
            where ename='SCOTT');
    
    select  t1.EMPNO
            ,t1.ENAME
            ,t1.JOB
    from emp t1
    where t1.ENAME != "SCOTT" and exists(
        select  job
        from emp t2
        where ENAME = "SCOTT"
        and t1.job = t2.job
    );

     

• hive中大小写不敏感
• 在Hive中，数据中如果有null字符串，加载到表中的时候会变成null(不是字符串)
  • 如果需要判断null,使用某个字段名 is null 这样的方式来判断
  • 或者使用 NVL(字段名，需要替换的值)，不能 直接 某个字段名==null\
  • NVL函数的功能是实现空值的转换，根据第一个表达式的值是否为空值来返回响应的列名或表达式，主要用于对数据列上的空值进行处理，语法格式如：NVL ( string1, replace_with) 如果第一个参数的值为空值，则返回第二个参数的值，否则返回第一个参数的值。. 如果两个参数的值都为空值，则返回空值。. 第一个参数和第二个参数可以是任何类型的数据，但两个参数的数据类型必须相同（或能够由Oracle隐式转换为相同的类型）。.
• 使用explain查看SQL执行计划

  • explain select  t1.EMPNO
            ,t1.ENAME
            ,t1.JOB
    from emp t1
    where t1.ENAME != "SCOTT" and t1.job in(
        select  job
        from emp
        where ENAME = "SCOTT");
        
    # 查看更加详细的执行计划，加上extended
    explain extended select  t1.EMPNO
            ,t1.ENAME
            ,t1.JOB
    from emp t1
    where t1.ENAME != "SCOTT" and t1.job in(
        select  job
        from emp
        where ENAME = "SCOTT");

     

• Hive数据类型
  •  整型：TINYINT、SMALLINT、INT、BIGINT
  • 浮点：FLOAT、DOUBLE
  • 布尔类型：BOOL （False/True）
  • 字符串：STRING
  • 时间类型：
    • 时间戳 timestamp
    • 日期 date

    • create table testDate(
          ts timestamp
          ,dt date
      ) row format delimited fields terminated by ',';
      
      // 2021-01-14 14:24:57.200,2021-01-11

       

  • 时间戳与时间字符串转换

    • // from_unixtime 传入一个时间戳以及pattern（yyyy-MM-dd） 可以将 时间戳转换成对应格式的字符串
      select from_unixtime(1630915221,'yyyy年MM月dd日 HH时mm分ss秒')

      // unix_timestamp 传入一个时间字符串以及pattern，可以将字符串按照pattern转换成时间戳
      select unix_timestamp('2021年09月06日 16时00分21秒','yyyy年MM月dd日 HH时mm分ss秒');
      select unix_timestamp('2021-01-14 14:24:57.200')

• 复杂数据类型
  • array

    • create table testArray(
          name string,
          weight array<string>
      )row format delimited 
      fields terminated by '\t'
      COLLECTION ITEMS terminated by ',';
      
      select name,weight[0] from testArray;

  • map
    •  key:value,key2:v2,k3:v3

    • create table scoreMap(
          name string,
          score map<string,int> 
      )ROW FORMAT DELIMITED
      FIELDS TERMINATED BY '\t'
      COLLECTION ITEMS TERMINATED BY ','
      MAP KEYS TERMINATED BY ':';
      
      select name,score['语文'] from scoreMap;

       

  • struct

    • create table scoreStruct(
          name string,
          score struct<course:string,score:int,course_id:int,tearcher:String> 
      )ROW FORMAT DELIMITED
      FIELDS TERMINATED BY '\t'
      COLLECTION ITEMS TERMINATED BY ',';
      
      select name,score.course,score.score from scoreStruct;