当前位置： 首页 > article >正文

大数据CDP集群中ImpalaHive常见使用语法

article 2024/11/18 13:20:57

1. SQL中设置常量

set var: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元数据

set session 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,
       case when 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'
        end AS 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)) ORDER BY 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 not like '%.kudu%'
and s.NAME = 'stg' -- 限制数据库
group by t.TBL_ID
-- limit 100
) a
left join (select t.TBL_ID,
                  concat_ws('','COMMENT ','''',t.param_value,'''') AS tbl_comment
             from hive.TABLE_PARAMS t
             where t.param_key = 'comment'
             group by t.TBL_ID
             ) c 
   on c.tbl_id = a.tbl_id
left join (select t.TBL_ID,concat_ws('','PARTITIONED BY (',group_concat(concat_ws('',t.pkey_name,' ',t.pkey_type,' ','COMMENT ','''',t.pkey_comment,'''')
                                                                       order by t.integer_idx separator ','),')') AS partition_str
             from hive.PARTITION_KEYS t 
             group by t.TBL_ID) b 
       ON b.tbl_id = a.tbl_id
 left join (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%'
              group by t.TBL_ID,t.param_key,t.param_value
              -- limit 100
              ) d 
       on d.tbl_id = a.tbl_id 
  order by 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-->string
SELECT to_timestamp('20230710','yyyyMMdd')  --string->timestamp
select from_timestamp(date_add(to_timestamp('20231201','yyyyMMdd'),1),'yyyyMMdd')
select date_add(now(),interval -1 years); --获取去年years\months\days\

8. 使用UDF函数

--查看使用函数
use default;
show functions; 
--查看函数所用jar&主类
show create function default.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执行
DROP FUNCTION DEFAULT.udf10(STRING, STRING);
--2.hive执行
drop function default.udf10;
--创建UDF函数 hive创建，impala刷新元数据同步。
create function default.clnseq as 'cn.com.businessmatrix.udf.HLSequenceCleaner' using jar 'hdfs:///user/hive/warehouse/udf_0608';
create function default.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,
       0 as gp_flag
  from ods.mdm_ip_cust t
 where t.gp_flag is null;

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

--用来控制归档是否可用
set hive.archive.enabled=true;
--通知Hive在创建归档时是否可以设置父目录
set hive.archive.har.parentdir.settable=true;
--控制需要归档文件的大小
set har.partfile.size=1099511627776;
--使用以下命令进行归档
ALTER TABLE A ARCHIVE PARTITION(dt='2020-12-24', hr='12');
--对已归档的分区恢复为原文件
ALTER TABLE 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
2	Partial mask: show last 4	仅显示最后四个字符，其他用 x 代替	mask_show_last_n
3	Partial mask: show first 4	仅显示前四个字符，其他用 x 代替	mask_show_first_n
4	Hash	用值的哈希值替换原值	mask_hash
5	Nullify	用 NULL 值替换原值	Ranger 自身实现
6	Unmasked	原样显示	Ranger 自身实现
7	Date: 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 之后。
ALTER TABLE test_change CHANGE a a1 STRING AFTER b;

16.hive中的排序

ORDER BY 全局排序，只有一个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;

--例子：
create table tmp_dz as                   
select '000855' as bk_product,
'372402834320,37345435345435,37345343434' as fundaccount,
'982342242322342,9842423424,98345333' as tradeaccount from dual;

insert into 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
CONNECT BY ROWNUM <= regexp_count(fundaccount, ',') + 1
) t;

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

-- 测试数据
with temp as
 (select '1,2,3' as id,
         'a,b,c' as name union select '4,5,6' as id,
         'd,e,f' as name)
-- 添加where限制
select id, name, s_id, s_name
  from temp   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，创建表并尝试插入数据。
create table cdhadmin_table_hive (col1 int ) ;
--插入操作
insert into table cdhadmin_table_hive values (1);
insert into table cdhadmin_table_hive values (51);
insert into table cdhadmin_table_hive values (2);
insert into table cdhadmin_table_hive values (3);
select * from  cdhadmin_table_hive;

--删除操作
delete from cdhadmin_table_hive where col1 = 51;
select * from  cdhadmin_table_hive;
--更新操作
update  cdhadmin_table_hive set col1=300 where col1=3;
select * from  cdhadmin_table_hive;
--使用hive用户，给应用用户赋权，使其可以访问default库。
grant select on database default to user cdhadmin;

http://www.kler.cn/a/398735.html

相关文章：

  • ARM 汇编指令
  • VMWare虚拟机安装华为欧拉系统
  • 基于Python深度学习的【垃圾识别系统】实现~TensorFlow+人工智能+算法网络
  • React中组件通信的几种方式
  • 【动手学深度学习Pytorch】1. 线性回归代码
  • git简介和本地仓库创建，并提交修改。git config init status add commit
  • 【Excel】身份证号最后一位“X”怎么计算
  • calico网络原理、组网方式
  • 开源科学工程技术软件介绍 – EDA工具KLayout
  • 计算机网络：运输层 —— TCP的流量控制
  • Django5 2024全栈开发指南（二）：Django项目配置详解
  • 【C++进阶篇】——string类的使用
  • AntFlow 0.11.0版发布,增加springboot starter模块,一款设计上借鉴钉钉工作流的免费企业级审批流平台
  • aws(学习笔记第十三课) 向AWS的RDS导入既存数据
  • 递归基础斐波那契数（LeetCode——509.斐波那契数）
  • 刘艳兵-DBA043-什么是“虚拟列索引”？
  • 如何查看电脑支持的最大内存
  • 【Linux内核剖析】深入分析inet_init的处理机制
  • 自动驾驶系列—深入解析自动驾驶车联网技术及其应用场景
  • 说说TCP传输的三次握手四次挥手策略
  • [369]基于springboot的高校教师教研信息填报系统
  • Infisical开源密钥管理平台实战指南
  • 《Python 网络爬虫》
  • ‌DNN（深度神经网络）和CNN（卷积神经网络）区别
  • Cursor安装Windows / Ubuntu
  • 新160个crackme - 098-DueList.4