Hive Basic

-- 创建一个分区表
CREATE TABLE `eps_volc_sla.ods_cloud_sla_sli_rds_hf`(
  `id` bigint COMMENT 'ID',
  `status` bigint COMMENT '状态',
  `sli_key` string COMMENT '唯一SLI名',
  `product` string COMMENT '售卖产品',
  `functions` string COMMENT '计算方式'
)
PARTITIONED BY (
  `date` string COMMENT '日期分区，yyyyMMdd',
  `hour` string COMMENT '小时分区，HH'
)
ROW FORMAT SERDE
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION
  'hdfs://westeros/user/tiger/warehouse/eps_volc_sla.db/ods_cloud_sla_sli_rds_hf'
TBLPROPERTIES (
  'is_core'='true',
  'is_starred'='false',
  'primary_key'='',
  'source_from'='coral_ng',
  'status'='3',
  'transient_lastDdlTime'='1633758742',
  'ttl'='0',
  'ttl_extension'='{\"type\":\"mtime\"}')

-- 其中 date, hour 都是动态分区
INSERT OVERWRITE TABLE eps_volc_sla.dim_sla_sli_hf PARTITION (date = '20211029', hour = '04')
SELECT  id, --	ID
        status, --	状态
        sli_key, --	产品唯一 SLI Key
        product, --	售卖产品
        `functions` --	计算方式
FROM    eps_volc_sla.ods_cloud_sla_sli_rds_hf
WHERE   date = '20211029'
AND     hour = '04';

-- 使用动态分区需要先打开动态分区的开关
hive> set hive.exec.dynamic.partition=true; 
-- 其中 date 是静态分区， hour 是动态分区，会根据 ods_cloud_sla_sli_rds_hf 表 hour 字段的值决定 dim_sla_sli_hf 的 hour 分区
INSERT OVERWRITE TABLE eps_volc_sla.dim_sla_sli_hf PARTITION (date = '20211029', hour)
SELECT  id, --	ID
        status, --	状态
        sli_key, --	唯一SLO名
        product, --	售卖产品
        `functions` --	计算方式
FROM    eps_volc_sla.ods_cloud_sla_sli_rds_hf
WHERE   date = '20211029';

-- 动态分区可以允许所有的分区列都是动态分区列，但是需要设置参数，默认是 strict
hive> set hive.exec.dynamic.partition.mode=nonstrict;
-- 在每个执行 MR 的节点上，最大可以创建多少个动态分区。
-- 例如源数据共有 24 个 hour 分区，共 2 个 MR 计算节点， hive.exec.max.dynamic.partitions.pernode=10 就会有问题
hive> set hive.exec.max.dynamic.partitions.pernode=100
-- 在所有执行 MR 的节点上，最大一共可以创建多少个动态分区。
hive> set hive.exec.max.dynamic.partitions=1000
-- 整个 MR Job 中，最大可以创建多少个 HDFS 文件
hive> set hive.exec.max.created.files=100000

-- 查看最新的函数使用方法
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')

-- 创建带分区的视图
CREATE VIEW IF NOT EXISTS (
    viewTime,
    userid,
    page_url,
    referrer_url,
    friends,
    properties,
    ip COMMENT '字段注释',
    date  COMMENT '字段注释',
    hour COMMENT '字段注释'
) PARTITIONED ON (date, hour) AS
SELECT
    viewTime,
    userid,
    page_url,
    referrer_url,
    friends,
    properties,
    ip,
    date,
    hour
FROM
    
WHERE
    

-- 查看所有表
SHOW TABLES;

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

-- 查看指定数据库中的表
SHOW VIEWS [in/from <dbName>] [<pattern>];

-- hive 2.0+ / spark-sql 支持单独查看视图
SHOW VIEWS;

-- 查看表的分区字段，如果表没有被分区则抛出错误信息
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')

-- 重新刷新 Hive 分区数据，比如通过 HDFS 文件直接写入 Hive Warehouse 的场景
ALTER TABLE
        eps_tech_test.ods_stable_trade_bill_center_split_bill_detail_rds_df
ADD     PARTITION (date = '20230208');

-- 当导入分区比较多的时候可以通过 MSCK 直接修复整个表
MSCK REPAIR TABLE eps_tech_test.ods_stable_trade_bill_center_split_bill_detail_rds_df;

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;