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

1. SQL中设置常量

1. set var:pi_sysdate = 20241114;
2. Variable PI_SYSDATE set to 20241114

复制代码

2. CDP中impala 创建内外表

1. #hive3.0 默认不创建事务表的配置参数
2. set default_transactional_type=none;
3. create external table stg.hd_aml_mac_ip_ext (
4. machinedate string,
5. vc_fundacco string,
6. ip string
7. )
8. stored as textfile
9. tblproperties ('objcapabilities'='extread,extwrite');
11. create external table stg.hd_aml_mac_ip (
12. machinedate string,
13. vc_fundacco string,
14. ip string
15. )
16. stored as parquet
17. tblproperties ("parquet.compression"="snappy");

复制代码

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

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

复制代码

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

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

复制代码

5. MYSQL hive元数据

1. set session group_concat_max_len = 20480;
2. select concat_ws('',
3.                  a.create_body_str,
4.                  CHAR(10),
5.                  c.tbl_comment,
6.                  CHAR(10),
7.                  b.partition_str,
8.                  CHAR(10),
9.                  a.stored_format,
10.                  CHAR(10),
11.                  d.compress_str,
12.                  ';') AS create_sql
13. FROM (
14. select t.TBL_ID,
15.        t.TBL_NAME,
16.        case when k.INPUT_FORMAT like '%.parquet%' then 'STORED AS PARQUET'
17.              when k.INPUT_FORMAT like '%.SequenceFile%' then 'STORED AS SEQUENCEFILE'
18.              when k.INPUT_FORMAT like '%.Text%' then ''
19.              else 'STORED AS NULL'
20.         end AS stored_format,
21.         concat_ws('',
22.                   'CREATE',
23.                   CASE t.TBL_TYPE
24.                     WHEN 'EXTERNAL_TABLE' THEN ' EXTERNAL'
25.                     ELSE '' END,
26.                     ' TABLE IF NOT EXISTS ${schema}.',
27.                     t.TBL_NAME,
28.                     '(',
29.                     CHAR(10),
30.                     group_concat(concat_ws('',
31.                                            g.COLUMN_NAME,
32.                                            ' ',
33.                                            g.TYPE_NAME,
34.                                            ' COMMENT ',
35.                                            '''',
36.                                            REPLACE(REPLACE(g.COMMENT,';',' '),'; ',' '),
37.                                            '''',
38.                                            CHAR(10)) ORDER BY g.INTEGER_IDX separator ','),
39.                                            ')'
40.                                           ) AS create_body_str
41. from hive.TBLS t,hive.SDS k,hive.COLUMNS_V2 g,hive.DBS s
42. where t.SD_ID = k.SD_ID
43. and k.CD_ID = g.CD_ID
44. and s.DB_ID = t.DB_ID
45. and k.INPUT_FORMAT not like '%.kudu%'
46. and s.NAME = 'stg' -- 限制数据库
47. group by t.TBL_ID
48. -- limit 100
49. ) a
50. left join (select t.TBL_ID,
51.                   concat_ws('','COMMENT ','''',t.param_value,'''') AS tbl_comment
52.              from hive.TABLE_PARAMS t
53.              where t.param_key = 'comment'
54.              group by t.TBL_ID
55.              ) c
56.    on c.tbl_id = a.tbl_id
57. left join (select t.TBL_ID,concat_ws('','PARTITIONED BY (',group_concat(concat_ws('',t.pkey_name,' ',t.pkey_type,' ','COMMENT ','''',t.pkey_comment,'''')
58.                                                                        order by t.integer_idx separator ','),')') AS partition_str
59.              from hive.PARTITION_KEYS t
60.              group by t.TBL_ID) b
61.        ON b.tbl_id = a.tbl_id
62. left join (select t.TBL_ID,
63.                   concat_ws('',
64.                           'TBLPROPERTIES (',
65.                           '''',
66.                           t.PARAM_KEY,
67.                           '''',
68.                           '=',
69.                           '''',
70.                           t.PARAM_VALUE,
71.                           ''')') as compress_str
72.               from hive.TABLE_PARAMS t
73.               where t.param_key like '%compression%'
74.               group by t.TBL_ID,t.param_key,t.param_value
75.               -- limit 100
76.               ) d
77.        on d.tbl_id = a.tbl_id
78.   order by a.tbl_name;

复制代码

6.修复数据

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

复制代码

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

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

复制代码

8. 使用UDF函数

1. --查看使用函数
2. use default;
3. show functions;
4. --查看函数所用jar&主类
5. show create function default.genseq;
6. --将jar包上传到新集群以及修改权限
7. hdfs dfs -put /home/app_adm/etl/udf/udf_0608.jar /user/hive/warehouse/udf_0608.jar
8. hdfs dfs -chown hive:hive /user/hive/warehouse/udf_0608.jar
9. hdfs dfs -chmod 777 /user/hive/warehouse/udf_0608.jar
10. --删除UDF函数，先在impala删除，再在hive中删除;
11. --1.impala执行
12. DROP FUNCTION DEFAULT.udf10(STRING, STRING);
13. --2.hive执行
14. drop function default.udf10;
15. --创建UDF函数 hive创建，impala刷新元数据同步。
16. create function default.clnseq as 'cn.com.businessmatrix.udf.HLSequenceCleaner' using jar 'hdfs:///user/hive/warehouse/udf_0608';
17. create function default.genseq as 'cn.com.businessmatrix.udf.HLSequenceGenerator' using jar 'hdfs:///user/hive/warehouse/udf_0608';
18. --将本地的文件强制推送到hdfs上面，如果文件已存在覆盖
19. hdfs dfs -put -f /home/file/ylb_trade_transfer_ext_out /tmp/hive/stg/ylb_trade_transfer_ext_out
20. --对HDFS目录进行用户赋权-用于执行hive命令
21. sudo -u hdfs hadoop fs -chown -R hive:supergroup /tmp/hive/stg/ylb_trade_transfer_ext

复制代码

9. impala更新KUDU表 指定主键

1. upsert into ${var:schema_ods}.mdm_ip_cust(
2.      sk_invpty_of_cust
3.         ,gp_flag
4. )
5. select t.sk_invpty_of_cust,
6.        0 as gp_flag
7.   from ods.mdm_ip_cust t
8. where t.gp_flag is null;

复制代码

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

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

复制代码

11.HBASE基本操作

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

复制代码

12.hive脱敏 中文不会脱敏

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

复制代码

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

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

复制代码

14.SQL脚本需在代船埠部添加阐明注释

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

复制代码

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

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

复制代码

16.hive中的排序

1. ORDER BY 全局排序，只有一个Reduce任务
2. SORT BY 只在本机做排序

复制代码

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

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

复制代码

18. hive 列传行

1. --hive 列传行
2. select new_fundaccount,new_bk_tradeaccount,bk_product from stg.tt0liquidateschema_tmp01
3. LATERAL VIEW explode(split(fundaccount,','))fundaccount as new_fundaccount
4. LATERAL VIEW explode(split(bk_tradeaccount,','))bk_tradeaccount as new_bk_tradeaccount;
6. --例子：
7. create table tmp_dz as                  
8. select '000855' as bk_product,
9. '372402834320,37345435345435,37345343434' as fundaccount,
10. '982342242322342,9842423424,98345333' as tradeaccount from dual;
12. insert into tmp_dz
13. select '000845' as bk_product,
14. '37345343454' as fundaccount,
15. '98345333433' as tradeaccount from dual;
18. select nvl(new_fundaccount,fundaccount) as fundaccount,
19.        nvl(new_tradeaccount,tradeaccount) as tradeaccount,
20.        bk_product
21. from (
22. SELECT REGEXP_SUBSTR(fundaccount, '[^,]+', 1, ROWNUM) as new_fundaccount,
23.        REGEXP_SUBSTR(tradeaccount, '[^,]+', 1, ROWNUM) as new_tradeaccount,
24.        t.*
25.   FROM tmp_dz t
26. CONNECT BY ROWNUM <= regexp_count(fundaccount, ',') + 1
27. ) t;

复制代码

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

1. -- 测试数据
2. with temp as
3. (select '1,2,3' as id,
4.          'a,b,c' as name union select '4,5,6' as id,
5.          'd,e,f' as name)
6. -- 添加where限制
7. select id, name, s_id, s_name
8.   from temp   lateral view posexplode(split(id, ','  )) t as s_id_index,
9.        s_id   lateral view posexplode(split(name, ','  )) t as s_name_index,
10.        s_name
11. where s_id_index = s_name_index

复制代码

20.CDPhive支持变乱，增编削查

1. --默认是支持update\delete操作，创建表不用指定 transactional=true，创建表并尝试插入数据。
2. create table cdhadmin_table_hive (col1 int ) ;
3. --插入操作
4. insert into table cdhadmin_table_hive values (1);
5. insert into table cdhadmin_table_hive values (51);
6. insert into table cdhadmin_table_hive values (2);
7. insert into table cdhadmin_table_hive values (3);
8. select * from  cdhadmin_table_hive;
10. --删除操作
11. delete from cdhadmin_table_hive where col1 = 51;
12. select * from  cdhadmin_table_hive;
13. --更新操作
14. update  cdhadmin_table_hive set col1=300 where col1=3;
15. select * from  cdhadmin_table_hive;
16. --使用hive用户，给应用用户赋权，使其可以访问default库。
17. grant select on database default to user cdhadmin;

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。