运维开发网

Hive HiveQL基础知识及常用语句总结

运维开发网 https://www.qedev.com 2020-04-28 17:01 出处:网络
基础语句 CREATE DROP 建表、删表 建表 -------------------------------------- -- 1. 直接建表 -------------------------------------- -- 创建非分区表时,省去后半部分即可 create table if not exists table_name( id string comment ‘id ‘, n

基础语句

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 as select * 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语句时,如需要注释,请注意转义问题

    1. 注释的那条语句不能含有分号;
    2. 注释语句后接语句尽量避免导jar包的语句
    3. 避免使用 /* */
  • 选择除某几个字段外的所有字段 

    当某个表的字段特别多,若想选取除某几个字段外的所有字段时,这时语句写起来就会很麻烦,比如有时两个表join的条件的字段名相同时,只能保留一个字段。但有一个方法可以解决这个问题,如下:

set hive.support.quoted.identifiers=none; select `(y|m|d)?+.+` from dual;
需注意:
括号内不能有空格
在shell里调用时需要对这个反引号进行转义
  • 通过concat_wscollect_setstr_to_map的使用,实现字符串转map
str_to_map(concat_ws(‘,‘,collect_set(concat_ws(‘:‘,key, cast(value as string)))))
  • avg()函数会自动剔除NULL,总数除以非NULL个数
  • 字符串截取函数substrsubstring是等价的 

    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.namemapreduce.job.queuename

set mapred.job.queue.name=queue_xx;

MRv2重新命名了MRv1中的所有配置参数,但兼容MRv1中的旧参数,只不过会打印一条警告日志提示用户参数过期。详见此篇博文。

  • 查询表的实际更新情况 

    desc formatted 的结果往往只有CreateTimeLastAccessTime经常为UNKNOWN的状态,可以用hadoop 指令去查询数据文件的真实更新时间:hadoop fs ‐ls truePath/databaseName.db/tableName/

调优

占个坑,有时间再来填

Hive里的坑点

  • limit m,n 的问题 

    使用语句:create table a as select * from b limit m,n时,会导致新建表a内无数据,解决办法: 

    1. 看似可以其实并不好使: create table a as select * from (select * from b limit m,n ) t
    2. 解决: 使用row_number() 加序号,根据序号选取
  • NULL 值问题 

    1. length(NULL) 等于NULL
    2. NULL空字符串

      由下可见:NULL不可以与字符串或数值比较,‘‘可与字符串比较但不可与数值比较

hive (xx_safe)> select * from dual; OK dual.lol 100 50 1 1 2 fangjiale NULL --(此处为‘‘) hive (xx_safe)> select * from dual where lol !=‘1‘; OK dual.lol fangjiale 100 50 2 --(此处为‘‘) hive (xx_safe)> select * from dual where lol !=1; OK dual.lol 100 50 2
0

精彩评论

暂无评论...
验证码 换一张
取 消