Hive Basic

-- 查看最新的函数使用方法
SHOW FUNCTIONS;
-- 方法介绍
DESCRIBE FUNCTION <function_name>;
-- 方法扩展功能介绍，有例子，比上面的更详细
DESCRIBE FUNCTION EXTENDED <function_name>;

-- 建表语法
CREATE TABLE page_view( -- 字段
                viewTime INT, 
                userid BIGINT,
                page_url STRING, 
                referrer_url STRING,
                friends ARRAY<BIGINT>, 
                properties MAP<STRING, STRING>,
                ip STRING COMMENT '字段注释')
COMMENT '表注释'
PARTITIONED BY(dt STRING, country STRING) -- 分区字段
CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS -- 分桶
ROW FORMAT DELIMITED -- 指定行分隔符
        FIELDS TERMINATED BY '1' -- 字段之间用字符`1`分割
        COLLECTION ITEMS TERMINATED BY '2' -- 集合之间用字符`2`分割
        MAP KEYS TERMINATED BY '3' -- Map key value 用字符`3`分割
STORED AS SEQUENCEFILE; -- 存储类型

-- 举个例子
CREATE TABLE `public_opinion_article`(
  `docid` string, 
  `field` string, 
  `termid` int, 
  `term` string, 
  `tfidf` double)
PARTITIONED BY ( 
  `dt` date)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.orc.OrcSerde' 
WITH SERDEPROPERTIES ( 
  'path'='hdfs://hadoop3:8020/apps/hive/warehouse/tfidf.db/public_opinion_article') 
STORED AS 
  INPUTFORMAT 
    'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat' 
  OUTPUTFORMAT 
    'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
LOCATION
  'hdfs://hadoop3:8020/apps/hive/warehouse/tfidf.db/public_opinion_article'
TBLPROPERTIES (
  'spark.sql.create.version'='2.4.0', 
  'spark.sql.partitionProvider'='catalog', 
  'spark.sql.sources.provider'='orc', 
  'spark.sql.sources.schema.numPartCols'='1', 
  'spark.sql.sources.schema.numParts'='1', 
  'spark.sql.sources.schema.part.0'='{\"type\":\"struct\",\"fields\":[{\"name\":\"docId\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"field\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"termId\",\"type\":\"integer\",\"nullable\":true,\"metadata\":{}},{\"name\":\"term\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"tfIdf\",\"type\":\"double\",\"nullable\":true,\"metadata\":{}},{\"name\":\"dt\",\"type\":\"date\",\"nullable\":true,\"metadata\":{}}]}', 
  'spark.sql.sources.schema.partCol.0'='dt', 
  'transient_lastDdlTime'='1555657655')

-- 查看所有表
SHOW TABLES;

-- 可以使用正则表达式
SHOW TABLES 'page.*';

-- 查看表的分区字段，如果表没有被分区则抛出错误信息
SHOW PARTITIONS page_view;

-- 查看表的所有字段和字段类型
DESCRIBE page_view;
DESC page_view;

-- 额外显示表的其他属性，常用于debugging
DESCRIBE EXTENDED page_view;

-- 查看表的分区字段和字段类型，以及分区的所有其他属性信息
DESCRIBE EXTENDED page_view PARTITION (ds='2008-08-08');

-- 查看表的创建语句，常用于查看表 SerDe 等信息
SHOW CREATE TABLE page_view;

-- 合并分区小文件为更大的文件
ALTER TABLE table_name [PARTITION partition_spec] CONCATENATE;

-- 删表
DROP TABLE pv_users;
-- 删除指定分区数据
ALTER TABLE pv_users DROP PARTITION (ds='2008-08-08')

CREATE TABLE IF NOT EXISTS test_zq.full_dimension
(
    id varchar(20),
    name varchar(20),
    age int,
    birthday date,
    farther_id string
)
PARTITIONED BY (dt string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS textfile;

LOAD DATA INPATH 'hdfs:///zq_test/full_dimensions_test_data'
INTO TABLE test_zq.full_dimension
PARTITION (dt = '2018-11-22');

-- 导出到本地，注意 insert into 后面只能跟表名，所以需要使用 insert overwrite
-- 字段分割符默认是 ^A(ascii=\00001)
INSERT OVERWRITE LOCAL DIRECTORY '/home/zhangqiang/hive'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
SELECT * FROM temp;

-- 导出到 hdfs
INSERT OVERWRITE DIRECTORY '/home/zhangqiang/hive'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
SELECT * FROM temp;

-- Usage: 按照COLUMN1进行分组，按照COLUMN2在分组内排序，然后分组内按顺序从1开始为每条数据编号
ROW_NUMBER() OVER(PARTITION BY COLUMN1 ORDER BY COLUMN2)

-- Example: 按照 DocID 进行去重(分组)，只保留最新版本的 Document
SELECT * 
FROM(
  SELECT *, ROW_NUMBER() OVER (PARTITION BY doc_id ORDER BY insert_time DESC) num 
  FROM my_table
  ) t 
WHERE t.num = 1;