hive大数据笔记

hive创建表

   create table t1(
      a string COMMENT '号段代码',
      b string COMMENT '号段类型代码'
    ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS TEXTFILE ;
    load data inpath '/user/000000/upload/1.txt' into table ods.t1;

取某条数据的最新记录

SELECT
  COUNT(1)
FROM
  (
    SELECT
      r.*,
      ROW_NUMBER () OVER (
        PARTITION BY r.ns_code
        ORDER BY
          r.ns_code DESC
      ) num
    FROM
      ods.t1 r
  ) m where num = 1 ;

SELECT
  COUNT(1)
FROM
  (
    SELECT
      r.*,
     rank() OVER (
        PARTITION BY r.ns_code
        ORDER BY
          r.ns_code DESC
      ) num
    FROM
      ods.tm_ns_cfg r
  ) m where num = 1 ;

grouping sets 使用

select
    ns_code,
    ns_type_code,
    number_length
from
    ods.tm_ns_cfg
group by 
     ns_code,
    ns_type_code,
    number_length
grouping sets
(
   (ns_code,number_length),
   (ns_code,ns_type_code),
   (ns_type_code,number_length),
   (number_length)
)

hive行转多列LATERAL

源表（table1）数据{A:string B:array C:string}
A B C
190 [1030,1031,1032,1033,1190] select id
191 [1030,1031,1032,1033,1190] select id

希望的结果是：
190 1030 select id
190 1031 select id
190 1032 select id
190 1033 select id
190 1190 select id
191 1030 select id
191 1031 select id
191 1032 select id
191 1033 select id
191 1190 select id

select A,B,C from table_1 LATERAL VIEW explode(B) table1 as B