大数据CDP集群中Impala&Hive常见使用语法

1. SQL中设置常量

setvar:pi_sysdate = 20241114;
Variable PI_SYSDATE set to 20241114

2. CDP中impala 创建内外表

#hive3.0 默认不创建事务表的配置参数set default_transactional_type=none;create external table stg.hd_aml_mac_ip_ext (
 machinedate string,
 vc_fundacco string,
 ip string
)
stored as textfile
tblproperties ('objcapabilities'='extread,extwrite');create external table stg.hd_aml_mac_ip (
 machinedate string,
 vc_fundacco string,
 ip string
)
stored as parquet
tblproperties ("parquet.compression"="snappy");

3. hive导出逗号分隔文件到本地

hive -e "SELECT * from student"| sed 's/\t/,/g' > /tmp/student.csv

4. hive on mr 的参数设置 开启动态分区

set hive.exec.dynamic.partition=true;set hive.exec.dynamic.partition.mode=nonstrict;set hive.exec.max.dynamic.partitions=500000;set hive.exec.max.dynamic.partitions.pernode=100000;set mapreduce.reduce.memory.mb=4096;

5. MYSQL hive元数据

setsession group_concat_max_len =20480;select concat_ws('',
                 a.create_body_str,CHAR(10),
                 c.tbl_comment,CHAR(10),
                 b.partition_str,CHAR(10),
                 a.stored_format,CHAR(10),
                 d.compress_str,';')AS create_sql
FROM(select t.TBL_ID,
       t.TBL_NAME,casewhen k.INPUT_FORMAT like'%.parquet%'then'STORED AS PARQUET'when k.INPUT_FORMAT like'%.SequenceFile%'then'STORED AS SEQUENCEFILE'when k.INPUT_FORMAT like'%.Text%'then''else'STORED AS NULL'endAS stored_format,
        concat_ws('','CREATE',CASE t.TBL_TYPE 
                    WHEN'EXTERNAL_TABLE'THEN' EXTERNAL'ELSE''END,' TABLE IF NOT EXISTS ${schema}.',
                    t.TBL_NAME,'(',CHAR(10),
                    group_concat(concat_ws('',
                                           g.COLUMN_NAME,' ',
                                           g.TYPE_NAME,' COMMENT ','''',REPLACE(REPLACE(g.COMMENT,';',' '),'; ',' '),'''',CHAR(10))ORDERBY g.INTEGER_IDX separator ','),')')AS create_body_str
from hive.TBLS t,hive.SDS k,hive.COLUMNS_V2 g,hive.DBS s 
where t.SD_ID = k.SD_ID
and k.CD_ID = g.CD_ID
and s.DB_ID = t.DB_ID
and k.INPUT_FORMAT notlike'%.kudu%'and s.NAME ='stg'-- 限制数据库groupby t.TBL_ID
-- limit 100) a
leftjoin(select t.TBL_ID,
                  concat_ws('','COMMENT ','''',t.param_value,'''')AS tbl_comment
             from hive.TABLE_PARAMS t
             where t.param_key ='comment'groupby t.TBL_ID
             ) c 
   on c.tbl_id = a.tbl_id
leftjoin(select t.TBL_ID,concat_ws('','PARTITIONED BY (',group_concat(concat_ws('',t.pkey_name,' ',t.pkey_type,' ','COMMENT ','''',t.pkey_comment,'''')orderby t.integer_idx separator ','),')')AS partition_str
             from hive.PARTITION_KEYS t 
             groupby t.TBL_ID) b 
       ON b.tbl_id = a.tbl_id
 leftjoin(select t.TBL_ID,
                  concat_ws('','TBLPROPERTIES (','''',
                          t.PARAM_KEY,'''','=','''',
                          t.PARAM_VALUE,''')')as compress_str
              from hive.TABLE_PARAMS t 
              where t.param_key like'%compression%'groupby t.TBL_ID,t.param_key,t.param_value
              -- limit 100) d 
       on d.tbl_id = a.tbl_id 
  orderby a.tbl_name;

6.修复数据

#impala刷新元数据
 invalidate metadata ods.tablename;#hive修复磁盘数据
 msck repair table ods.tablename;

7. impala中时间戳转(DATE)指定格式的字符串

SELECT from_timestamp(now(),'yyyyMMdd');--timestamp\date-->stringSELECT to_timestamp('20230710','yyyyMMdd')--string->timestampselect from_timestamp(date_add(to_timestamp('20231201','yyyyMMdd'),1),'yyyyMMdd')select date_add(now(),interval-1 years);--获取去年years\months\days\

8. 使用UDF函数

--查看使用函数usedefault;show functions;--查看函数所用jar&主类showcreatefunctiondefault.genseq;--将jar包上传到新集群以及修改权限
hdfs dfs -put /home/app_adm/etl/udf/udf_0608.jar /user/hive/warehouse/udf_0608.jar
hdfs dfs -chown hive:hive /user/hive/warehouse/udf_0608.jar
hdfs dfs -chmod 777/user/hive/warehouse/udf_0608.jar
--删除UDF函数，先在impala删除，再在hive中删除;--1.impala执行DROPFUNCTIONDEFAULT.udf10(STRING, STRING);--2.hive执行dropfunctiondefault.udf10;--创建UDF函数 hive创建，impala刷新元数据同步。createfunctiondefault.clnseq as'cn.com.businessmatrix.udf.HLSequenceCleaner'using jar 'hdfs:///user/hive/warehouse/udf_0608';createfunctiondefault.genseq as'cn.com.businessmatrix.udf.HLSequenceGenerator'using jar 'hdfs:///user/hive/warehouse/udf_0608';--将本地的文件强制推送到hdfs上面，如果文件已存在覆盖
hdfs dfs -put -f /home/file/ylb_trade_transfer_ext_out /tmp/hive/stg/ylb_trade_transfer_ext_out
--对HDFS目录进行用户赋权-用于执行hive命令
sudo -u hdfs hadoop fs -chown -R hive:supergroup /tmp/hive/stg/ylb_trade_transfer_ext

9. impala更新KUDU表 指定主键

upsert into ${var:schema_ods}.mdm_ip_cust(
     sk_invpty_of_cust
    ,gp_flag
)select t.sk_invpty_of_cust,0as gp_flag
  from ods.mdm_ip_cust t
 where t.gp_flag isnull;

10.使用hadoop的archive将小文件归档

--用来控制归档是否可用set hive.archive.enabled=true;--通知Hive在创建归档时是否可以设置父目录set hive.archive.har.parentdir.settable=true;--控制需要归档文件的大小set har.partfile.size=1099511627776;--使用以下命令进行归档ALTERTABLE A ARCHIVE PARTITION(dt='2020-12-24', hr='12');--对已归档的分区恢复为原文件ALTERTABLE A UNARCHIVE PARTITION(dt='2020-12-24', hr='12');

11.HBASE基本操作

--1.进入
hbase shell
--2.创建表create'student','info'--3.插入数据
put 'student','1001','info:sex','male'--4.扫描查看表数据
scan 'student'
scan 'student',{STARTROW =>'1001', STOPROW  =>'1001'}
scan 'student',{STARTROW =>'1001'}
--5.查看表结构describe'student'--6.更新指定字段的数据
put 'student','1001','info:name','Nick'--7.查看 “指定行” 或 “指定列族:列” 的数据
get 'student','1001'
get 'student','1001','info:name'--8.统计表数据行数
count 'student'--9.变更表信息alter'student',{NAME=>'info',VERSIONS=>3}
get 'student','1001',{COLUMN=>'info:name',VERSIONS=>3}
--10.删除数据--删除某 rowkey 的全部数据
deleteall 'student','1001'-- 删除某 rowkey 的某一列数据delete'student','1002','info:sex'--11.清空表数据truncate'student'--12.清空表数据truncate'student'drop'student'--提示：清空表的操作顺序为先 disable，然后再 truncate。--13.查看命名空间
list_namespace
--创建命名空间
create_namespace 'bigdata'--在新的命名空间中创建表create'bigdata:student','info'--只能删除空的命名空间，如果不为空，需要先删除该命名空间下的所有表
drop_namespace 'bigdata'

12.hive脱敏 中文不会脱敏

select mask('不不不bbb123');--不不不xxxnnn
序号    策略名    策略说明    Hive 系统函数
1    Redact    用 x 屏蔽字母字符，用 n 屏蔽数字字符    mask
2Partial mask: showlast4    仅显示最后四个字符，其他用 x 代替    mask_show_last_n
3Partial mask: showfirst4    仅显示前四个字符，其他用 x 代替    mask_show_first_n
4Hash    用值的哈希值替换原值    mask_hash
5    Nullify    用 NULL 值替换原值    Ranger 自身实现
6    Unmasked    原样显示    Ranger 自身实现
7Date: show only year    仅显示日期字符串的年份    mask
8    Custom    Hive UDF 来自定义策略     

13.基于CDH5升级到CDH6.3.x造成的语法兼容

1.传入参数使用STRING
2.'''||value||'''->"'||value||'"3.""->""4. 调整成collect_set()函数的使用 hive on mr ,其他使用impala跑进临时表

14.SQL脚本需在代码头部添加说明注释

[示例]-- ** 所属主题: 交易-- ** 功能描述: 交易退款分析-- ** 创建者 : xxx-- ** 创建日期: 20170616 -- ** 修改日志:-- ** v1.0.0.0 20200118 xxx 创建基线-- ** v1.0.0.1 20200118 xxx 修改大小写规范 [示例]-- ** modify 20200118 xxx 添加质押比例字段取值 start
nvl(c.en_ratio,0)as plg_rati,--质押比例-- ** modify 20200118 xxx 添加质押比例字段取值 end

15.hive 修改字段并指定位置

--将 a 列的名字改为 a1，a 列的数据类型改为 string，并将它放置在列 b 之后。ALTERTABLE test_change CHANGE a a1 STRING AFTER b;

16.hive中的排序

ORDERBY 全局排序，只有一个Reduce任务
SORT BY 只在本机做排序

17. sqoop将hive中的数据导出到Oracle

sqoop export
#oracle数据库连接--connect jdbc:oracle:jdbc:oracle:thin:@locallhost:1521/testdb
--username test
--password 123456
--table t_test
# hive表数据文件在hdfs上的路径--export-dir'/apps/hive/warehouse/dbi.db/t_test/pdt=20191229'# 指定表的列名，不写会默认全部列--columns ID,data_date,data_type,c1,c2,c3 
# 列分隔符(根据hive的表结构定义指定分隔符)--input-fields-terminated-by '\001'# 行分隔符--input-lines-terminated-by '\n'# 如果hive表中存在null字段，则需要添加参数，否则无法导入--input-null-string '\\N'--input-null-non-string '\\N'  > sqoop_oracle.log 2>&1 &

18. hive 列传行

--hive 列传行select new_fundaccount,new_bk_tradeaccount,bk_product from stg.tt0liquidateschema_tmp01 
LATERAL VIEW explode(split(fundaccount,','))fundaccount as new_fundaccount
LATERAL VIEW explode(split(bk_tradeaccount,','))bk_tradeaccount as new_bk_tradeaccount;--例子：createtable tmp_dz asselect'000855'as bk_product,'372402834320,37345435345435,37345343434'as fundaccount,'982342242322342,9842423424,98345333'as tradeaccount from dual;insertinto tmp_dz
select'000845'as bk_product,'37345343454'as fundaccount,'98345333433'as tradeaccount from dual;select nvl(new_fundaccount,fundaccount)as fundaccount,
       nvl(new_tradeaccount,tradeaccount)as tradeaccount,
       bk_product
 from(SELECT REGEXP_SUBSTR(fundaccount,'[^,]+',1, ROWNUM)as new_fundaccount,
       REGEXP_SUBSTR(tradeaccount,'[^,]+',1, ROWNUM)as new_tradeaccount,
       t.*FROM tmp_dz t
CONNECTBY ROWNUM <= regexp_count(fundaccount,',')+1) t;

19.hive 列传行 多列逗号分割的字段

-- 测试数据withtempas(select'1,2,3'as id,'a,b,c'as name unionselect'4,5,6'as id,'d,e,f'as name)-- 添加where限制select id, name, s_id, s_name
  fromtemp   lateral view posexplode(split(id,',')) t as s_id_index,
       s_id   lateral view posexplode(split(name,',')) t as s_name_index,
       s_name
 where s_id_index = s_name_index

20.CDPhive支持事务，增删改查

--默认是支持update\delete操作，创建表不用指定 transactional=true，创建表并尝试插入数据。createtable cdhadmin_table_hive (col1 int);--插入操作insertintotable cdhadmin_table_hive values(1);insertintotable cdhadmin_table_hive values(51);insertintotable cdhadmin_table_hive values(2);insertintotable cdhadmin_table_hive values(3);select*from  cdhadmin_table_hive;--删除操作deletefrom cdhadmin_table_hive where col1 =51;select*from  cdhadmin_table_hive;--更新操作update  cdhadmin_table_hive set col1=300where col1=3;select*from  cdhadmin_table_hive;--使用hive用户，给应用用户赋权，使其可以访问default库。grantselectondatabasedefaulttouser cdhadmin;