hive表操作

4、hive表操作

4.1、alter操作

--新增字段

alter table pipeloan.pipeloan_f_order_info add COLUMNS(fnd_int_rat decimal(18,6) comment "资方年华利率") cascade;

hive分区表新增字段时末尾加上 cascade， cascade的中文翻译为“级联”，也就是不仅变更新分区的表结构（metadata），同时也变更旧分区的表结构

--hive不支持drop字段，可以用replace来实现删除字段的目的

alter table table_name replace columns(column_1 string); --column_2不写，即删除column_2，保留column_1

--修改表名

alter table ods_loan.ods_loan_dscreditdata_creditcore_credit_data_collect_log_new rename to ods_loan_dscreditdata_creditcore_credit_data_collect_log;

alter table ods_loan.ods_loan_dscreditdata_creditcore_credit_data_query_log_new rename to ods_loan_dscreditdata_creditcore_credit_data_query_log;

--表添加注释

alter table ods_loan.ods_loan_dscreditdata_creditcore_credit_data_collect_log set TBLPROPERTIES('comment' = '采集记录表');

alter table ods_loan.ods_loan_dscreditdata_creditcore_credit_data_query_log set comment = '查询记录表';

--表字段添加注释

alter table ods_loan.ods_loan_dscreditdata_creditcore_credit_data_collect_log change column scene_id scene_id string COMMENT '场景id';

alter table ods_loan.ods_loan_dscreditdata_creditcore_credit_data_query_log change column scene_id scene_id string COMMENT '场景id';

--删除分区数据

alter table dwd_loan.dwd_loan_f_dscrm_customersystem_faq_search_logs_all drop partition (day<'2021-11-04');

4.2插入数据（insert into & insert overwrite）

insert into 与insert overwrite 之间的异同，两者都可以向 hive 表中插入数据，但 insert into 操作是以追加的方式向 hive 表尾部追加数据，而 insert overwrite 操作则是直接重写数据，即先删除 hive 表的数据，再执行写入操作。注意，如果 hive 表是分区表的话，insert overwrite 操作只会重写当前分区的数据，不会重写其他分区数据。

------------------必须严格保证insert语句中的字段和建表语句中的字段的顺序一致！！！------------------

4.3

--查看建表语句:show create table tbl

show create table ods_loan.ods_loan_dsmisc02_inneroa_pmp_score;

--查看hive表分区:show partitions tbl

show partitions ods_loan.ods_loan_dsmisc02_inneroa_pmp_score;

--查看建表相关的各种配置属性以及默认属性。（包含表存储路径）

desc formatted ods_loan.ods_loan_dsriskbiz_riskbizcore_risk_biz_order_grey

show functions; -- 查看有哪些udf

desc function context_ngrams; -- 查看某个udf的介绍

hive非分区表改造为分区表:

非分区表改造为分区表：例如表名-tb

首先建立一张表结构相同的新表 tb_new->旧表 tb数据导入到新表tb_new中->老表重命名tb_bak,tb_new重命名为tb -> 并行观察两天tb和tb_bak数据，核对无问题后drop掉tb_bak，tb改造完成。

4.4 常用函数

在正常解析一个有值的字符串时，用lateral view explode是完全ok的，但是，当遇到该字符串为空时，如果在使用该函数，就会导致该条记录消失。

lateral view explode这个UDTF转换的Array为空的记录，自动被过滤掉了，如果想要保留记录，需要加上outer关键字。lateral view outer explode

get_json_object --解析单个字段

json_tuple --解析多个字段

regexp_replace(input,'[0-9]+','*') --字段中所有的数字替换为*

select regexp_replace('foobar', 'oo|ar', 'A') --返回：fAbA

regexp_extract 返回被解析字符串，匹配到正则表达式中index指定组的结果

用法：regexp_extract('foothebar', 'foo(.*?)(bar)', 2)第一个参数为被解析字符串，第二参数为正则表达式，第三参数为指定组索引index。注意：第三参数index，0代表返回全部，默认为1，索引数不能大于正则表达中括号，大于会报错。

select regexp_extract('foothebar', 'foo(.*?)(bar)', 0) --返回：foothebar

select regexp_extract('foothebar', 'foo(.*?)(bar)', 1) --返回：the

select regexp_extract('foothebar', 'foo(.*?)(bar)', 2) --返回：bar

特殊符号 转义字符：';' 用 '\073' -- 分号用 \073 替换

用法：regexp_extract('foothebar', 'foo(.*?)(bar)', 2)第一个参数为被解析字符串，第二参数为正则表达式，第三参数为指定组索引index。注意：第三参数index，0代表返回全部，默认为1，索引数不能大于正则表达中括号，大于会报错。