Hive HiveQL基础知识及常用语句总结
来源:互联网 发布:js中获取父节点 编辑:程序博客网 时间:2024/06/05 16:35
基础语句
CREATE DROP
建表、删表
- 建表
---------------------------------------- 1. 直接建表---------------------------------------- 创建非分区表时,省去后半部分即可create table if not exists table_name(id string comment 'id ',num string comment '数值' )partitioned by (ym string comment '月份 ' );---------------------------------------- 2. 复制其他表的表结构--------------------------------------create table if not exists new_table like old_table;---------------------------------------- 3. 从其他表选取数据创建并插入新表--------------------------------------create table if not exists new_table asselect * from old_table;
- 删表
drop table table_name ;
ALTER
更改表结构
分区
- 添加分区
alter table table_name add if not exists partition (y='2016',m='12');
- 删除分区
ALTER TABLE table_name DROP IF EXISTS PARTITION (ym='201706');
- 重命名分区
ALTER TABLE table_name PARTITION (y='2017',m='05') RENAME TO PARTITION (y='2017',m='06');
列
- 删除列
ALTER TABLE table_name DROP COLUMN id;
- 增加列
Alter table table_name add COLUMNS (id string comment '代号');
- 修改列 (此处可用于 修改字段注释)
ALTER TABLE table_name CHANGE id level string comment '层级代号';
- 替换列
ALTET TABLE table_name REPLACE COLUMNS(id_new string COMMENT '新字段1', level_new string COMMENT '新字段2');
表
- 重命名表名
ALTER TABLE old_table RENAME TO new_table;
INSERT
插入
- 插入单条数据 (Hive 已支持单条插入)
insert into table_name values(201705,'lol');
- 插入分区表
insert overwrite table table_name PARTITION (y='2017',m='01') select * from table_name_2 where pt = concat('2017','01');
LOAD
载入
- 重写载入分区表 (非分区表同理)
LOAD DATA LOCAL INPATH 'total.txt' overwrite into table table_name partition (y='2016',m='12');
其他语句
- 其他基础SQL类似的语句不再赘述,此处再多列举几个常用语句:
-- 列举库或表SHOW DATABASES/TABLES;-- 根据关键字查找库或表SHOW DATABASES/TABLES LIKE "*keyword*";-- 列举所有函数SHOW FUNCTIONS;-- 查看分区SHOW PARTITIONS test_table;-- 查看建表语句SHOW CREATE TABLE table_name;-- 详细描述,包括建表时间、最后更新时间、行数统计等。DESC FORMATTED test_table;-- 解释语句EXPLAIN select * from dual;-- 清空表truncate table table_name;
函数、技巧、方法
- 执行顺序理解
根据 explain 语句的使用发现,在执行以下语句时:
在TableScan
步骤,where条件中若有分区筛选条件且目标分区确实存在、且判断方式为=
时,自动进行过滤, 然后再在Filter Operator
进行其他条件的筛选:
predicate: (latitude is not null and (UDFToDouble(longitude) > 100.0) AND (UDFToDouble(ym) > 201701.0)) (type: boolean)
predicate: (lat_avg is not null and lng_avg is not null) (type: boolean)
所以, 先join在where和先where在join是等等价的
select b.*, a.name,a.price,a.city from table_name_a a join table_name_b b on round(b.lng_avg,2) = round(a.longitude,2) and round(b.lat_avg,2) = round(a.latitude,2) where a.longitude >100
shell内通过
hive -v " "
调用执行HiveQL语句时,如需要注释,请注意转义问题- 注释的那条语句不能含有分号
;
- 注释语句后接语句尽量避免导jar包的语句
- 避免使用 /* */
- 注释的那条语句不能含有分号
选择除某几个字段外的所有字段
当某个表的字段特别多,若想选取除某几个字段外的所有字段时,这时语句写起来就会很麻烦,比如有时两个表join的条件的字段名相同时,只能保留一个字段。但有一个方法可以解决这个问题,如下:
set hive.support.quoted.identifiers=none;select `(y|m|d)?+.+` from dual;
- 需注意:
- 括号内不能有空格
- 在shell里调用时需要对这个反引号进行转义
- 通过
concat_ws
、collect_set
和str_to_map
的使用,实现字符串转map
str_to_map(concat_ws(',',collect_set(concat_ws(':',key, cast(value as string)))))
avg()
函数会自动剔除NULL,总数除以非NULL个数- 字符串截取函数
substr
、substring
是等价的
substr(string A, int start, int len)
substring(string A, int start, int len) x between a and b
等同于a<= x <=b
子查询加最好要加别称
报错:Failed rule: ‘identifier’ in subquery source
解决:子查询加 别称设置队列问题,
mapred.job.queue.name
与mapreduce.job.queuename
set mapred.job.queue.name=queue_xx;
MRv2重新命名了MRv1中的所有配置参数,但兼容MRv1中的旧参数,只不过会打印一条警告日志提示用户参数过期。详见此篇博文。
- 查询表的实际更新情况
desc formatted 的结果往往只有CreateTime
,LastAccessTime
经常为UNKNOWN
的状态,可以用hadoop 指令去查询数据文件的真实更新时间:hadoop fs ‐ls truePath/databaseName.db/tableName/
调优
占个坑,有时间再来填
Hive里的坑点
limit m,n
的问题
使用语句:create table a as select * from b limit m,n
时,会导致新建表a内无数据,解决办法:- 看似可以其实并不好使:
create table a as select * from (select * from b limit m,n ) t
- 解决: 使用
row_number()
加序号,根据序号选取
- 看似可以其实并不好使:
- NULL 值问题
length(NULL)
等于NULL
NULL
与空字符串
:
由下可见:NULL
不可以与字符串或数值比较,''
可与字符串比较但不可与数值比较
hive (xx_safe)> select * from dual;OKdual.lol10050112fangjialeNULL --(此处为'')hive (xx_safe)> select * from dual where lol !='1';OKdual.lolfangjiale100502 --(此处为'') hive (xx_safe)> select * from dual where lol !=1;OKdual.lol100502
阅读全文
0 0
- Hive HiveQL基础知识及常用语句总结
- Hive-2.HiveQL查询中JOIN语句
- Hive安装及基础知识总结
- hiveQL学习和hive常用操作
- Hive-2.HiveQL查询中常用函数
- 关于HiveQL的常用语法总结(四)——其它技巧(hive代码优化)
- hive hiveql
- Hive-2.HiveQL查询中where和group by语句
- Hive--HiveQL:查询
- Hive数据仓库--HiveQL查询
- Hive数据类型及常用操作总结(一)
- JavaScript基础知识及常用方法总结
- 关于HiveQL的常用语法总结(一)——DDL
- 关于HiveQL的常用语法总结(二)——DML
- Hive常用语句
- hive常用语句
- hive常用语句
- HIVE常用语句
- C++ primer:第四章备忘。
- 313. Super Ugly Number
- 自旋锁与读写锁
- pg中检查点的触发条件
- 为什么爬虫正则更多的用非贪婪匹配模式?
- Hive HiveQL基础知识及常用语句总结
- netty EventLoop write() 源码分析(二)
- ARM Cortex-A平台NEON指令的编译和优化
- 开启TCP BBR拥塞控制算法
- 查看是否关闭iphone版本通道(回退版本时)
- HBase Client客户端数据管理软件
- pg中删除表中重复记录的SQL
- blueZ-5.37 交叉编译可以在arm上运行
- HTTPS模拟表单发送参数和图片