Hive SQL汇总-阿里云开发者社区

开发者社区> 大数据> 正文

Hive SQL汇总

简介: 创建数据库create database if not exists sopdmcomment 'this is test database'with dbproperties('creator'='gxw','date'='2014-11-12') --数据库键值对属性信息location '/my/preferred/directory';显示所有表show tables ;显示表的描述


创建数据库
create database if not exists sopdm
comment 'this is test database'
with dbproperties('creator'='gxw','date'='2014-11-12') 
--数据库键值对属性信息
location '/my/preferred/directory';


显示所有表
show tables ;
显示表的描述信息
desc [extended,formatted] tablename;
显示建表语句
show create table tablename;
删除表
drop table tablename;

由一个表创建另一个表,相当于复制,表结构复制,数据没复制
create table test3 like test2;

由其他表查询创建表
create table test4 as select name,addr from test5;


stored as textfile
可以直接查看
stored as sequencefile
必须用hadoop fs -text查看
stored as rcfile
hive -service rcfilecat path 查看

stored as inputformat 'class'(自定义的)


加载jar包
shell窗口add jar path(作用范围本shell)
加载到分布式缓存中供各个节点使用

或者直接拷贝到hive安装目录下的lib目录


SerDe(hive使用SerDe读、写表的行)

读写顺序:
HDFS文件-->InputFileFormat--> <key,value>-->Deserializer-->Row对象(供hive使用)
Row对象-->Serializer--> <key,value>-->OutputFileFormat-->HDFS文件

hive自带RegexSerDe.class 正则表达式匹配每一行的数据

create table apachelog(
host STRING, 
identity STRING,
user STRING,
time STRING,
request STRING,
status STRING,
size STRING,
refer STRING,
agent STRING) 
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe' 
WITH SERDEPROPERTIES (
"input.regex="([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([0-9]*) ([0-9]*) ([^ ]*) ([^ ]*)"
) stored AS TEXTFILE;


分区表(相当于表的子目录)

create table tablename (name string) partitioned by (key type,...)


create external table employees(
name string,
salary float,
subordinates array<string>,
deductions map<string,float>,
address struct<street:string,city:string,state:string,zip:int)
)
partitioned by (dt string,type string)
row format delimited fields terminated by '\t'
collection items terminated by ','
map keys terminated by ':'
lines terminated by '\n'
stored as textfile
location '/data';
数据格式:
wang  123  a1,a2,a3  k1:1,k2:2,k3:3  s1,s2,s3,4

查看分区:
show partitions employees
增加分区

alter table employees add if not exists partition(country='xxx'[,state='yyy'])
删除分区
alter table employees drop if  exists partition(country='xxx'[,state='yyy'])

动态分区:
1.不需要为不同的分区添加不同的插入语句
2.分区不确定,需要从数据中获取
参数:(动态分区前两个必须开启)
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrick;
//无限制模式,如果是strict,则必须有一个静态分区,且放在最前面
set hive.exec.max.dynamic.partitions.pernode=10000;
//每个节点生成动态分区的最大个数
set hive.exec.max.dynamic.partitions=100000;
//每次sql查询生成动态分区的最大个数
set hive.exec.max.created.files=150000;
//一个任务最多可以创建的文件数目
set dfs.datanode.max.xcievers=8182;
//限定一次最多打开的文件数
1. 创建分区表
create table d_part (
name string
)
partitioned by (value string)
row format delimited fields terminated by '\t'
lines terminated by '\n'
stored as textfile;
2. 插入动态分区
insert overwrite table d_part partition(value)
select name,addr as value 
from testtext;


分桶
set hive.enforce.bucketing=true;

按id分桶
create table bucketed_user
(id string,
name string)
clustered by (id) sorted by(name)  into 4 buckets
row format delimited fields terminated by '\t'
lines terminated by '\n'
stored as textfile;
分桶抽样
select * from bucketed_user tablesample(bucket 1 out of 2 on id )
取一半的桶
优化
set hive.optimize.bucketmapjoin=true;
set hive.optimize.bucketmapjoin.sortedmerge=true;
set hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;


wKiom1YvQG_wH6PQAAI1XC7HQsM313.jpg

beeline底层使用的jdbc,命令行使用jdbc(可以远程访问)

hive -help
hive --help
hive --service -help

查询结果写到文件
hive -V -e "select name from testtext" > /home/data/result


hive命令行输入list jar
显示当前分布式缓存有哪些jar包也就是add jar命令加载的jar包)

命令行执行hql文件(类似于hive -f),常用于设置初始化参数
source /home/data/hql/select_hql

配置变量
set val='';
hql使用hive变量
${hiveconf:val}
select * from testtext where name ='${hiveconf:val}';
env查看linux环境变量
HOME=/root
hql使用linux环境变量
select '${env:HOME}' from testtext;



1.内表数据加载(overwrite和into不能同时存在,只能存在一个)
(1) 创建表时加载
create table newtable as select col1,col2 from oldtable;
(2)创建表时指定数据位置(对location下的数据具有拥有权,删除内表时也会删除数据)

create table tablename() location '';
(3)本地数据加载
load data local inpath 'localpath' [overwrite] into table tablename;
(4)加载hdfs数据(移动数据,原来的数据移动到表的位置下)
load data  inpath 'hdfspath' [overwrite] into table tablename;

hive命令行执行linux shell命令在前面加个!
!ls /home/data

(5)通过查询语句加载数据

insert  into table test_m select name,addr from testtext where name ='wer';
或者
from testtext insert  into table test_m select name,addr  where name ='wer';
或者
select name,addr from testtext  where name ='wer' insert  into table test_m ;

2.外部表数据加载
(1)创建表时指定数据位置
create external table tablename() location '';
(2)查询插入,同内表
(3)使用hadoop命令拷贝数据到指定位置(hive的shell中执行和linux的shell执行)

3.分区表数据加载
(1)内部分区表数据加载类似于内表

(2)外部分区表数据加载方式类似于外表
注意:数据存放的路径层次要和表的分区一致,并且表要增加相应分区才能查到数据

load data local inpath 'localpath' [overwrite] into table tablename partition(dt='20140905');

4.数据类型对应问题
Load数据,字段类型不能相互转化时,查询返回NULL;
select查询输入,字段类型不能相互转化时,插入数据为NULL(文件保存是 \N);
select查询输入数据,字段名称可不一致,数据加载不做检查,查询时检查



数据导出

导出到本地,默认分隔符^A
insert overwrite local directory '/home/data3' 
row format delimited fields terminated by '\t'
select name,addr from testtext; 

导出到hdfs,不支持row format delimited fields terminated by '\t',只能采用默认分隔符是I
insert overwrite directory '/home/data3' 
select name,addr from testtext; 

表属性操作

1.修改表名
alter table table_name rename to new_table_name;

2.修改列名
alter table tablename change column c1 c2 int comment 'xxx' after severity;
c1旧列,c2新列,int代表新列数据类型
after severity;可以把该列放到指定列的后面,或者使用'first'放到第一位

3.增加列(默认新增列放到最末尾)
alter  table tablename add column (c1 string comment 'xxxx',c2 string comment 'yyyy');

4.修改tblproperties
alter table test set tblproperties('comment'='xxxx');

5.修改分隔符(分区表比较特殊)
方式一
alter table city set serdeproperties('field.delim'='\t');(对分区表原始数据无效,对新加分区有效)

方式二 对分区表原始数据也使用最新的分隔符
alter table city partitin(dt='20140908') set serdeproperties('field.delim'='\t');

6.修改location
alter table city [partition(...)] set location 'hdfs://master:9000/location' ;

7.内部表和外部表转换
alter table test set tblproperties('EXTERNAL'='TRUE');内部表转外部表
alter table test set tblproperties('EXTERNAL'='FALSE');外部表转内部表

聚合操作
1.count计数
count(*)所有的字段不全为null,全为null不加1
count(1)不管记录是啥,只要有这条记录都加1
count(col)列不为空加1

2.sum求和
sum(可转成数字的值)  返回bigint
sum(col)+cast(1 as bigint)

3.avg
avg(可转成数字的值) 返回double

where条件在map端执行

group by是在reduce端执行 分组的列组合为key

having字句聚合操作之后执行判断,也是在reduce端执行


groupby数据倾斜优化
hive.groupby.skewindata=true;(多起一个job)


join操作(普通join不支持不等值链接)
优化参数set hive.optimize.skewjoin=true;
样例
select m.col as col,m.col2 as col2,n.col3 as col3
from 
(select col,col2
from test
where ...(map端执行)
) m
[left outer|right outer|left semi] join
n (右表)
on m.col=n.col
where condition (reduce端执行)



LEFT SEMI JOIN 是 IN/EXISTS 子查询的一种更高效的实现

Hive 当前没有实现 IN/EXISTS 子查询,所以你可以用 LEFT SEMI JOIN 重写你的子查询语句。LEFT SEMI JOIN 的限制是, JOIN 子句中右边的表只能在 

ON 子句中设置过滤条件,在 WHERE 子句、SELECT 子句或其他地方过滤都不行。

 SELECT a.key, a.value

 FROM a

 WHERE a.key in

  (SELECT b.key

  FROM b);

可以被重写为:

  SELECT a.key, a.val

  FROM a LEFT SEMI JOIN b on (a.key = b.key)




order by全局排序,只有一个reduce
distribute by col分散数据,按col分散到不同的reduce
和sort by结合保证每个reduce输出是有序的
 

union all(不去重)和union(去重)

hive只支持union all,子查询不允许起别名
select col from 
(select a as col from t1 
union all
select b as col from t2
) tmp;

要求:
1.字段名字一样
2.字段类型一样
3.字段个数一样
4.子表不能有别名
5.如果需要从合并之后的表中查询数据,那么合并之后的表必须要有别名


hive创建索引表

hive> create index user_index on table user(id)     
        > as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler'    
        > with deferred rebuild    
        > IN TABLE user_index_table;    
    hive> alter index user_index on user rebuild;    
    hive> select * from user_index_table limit 5;



直接用hadoop命令复制删除hive存储数据后,需要add partition或alter来同步源数据信息,否则drop表等操作时会查询元数据metastore,查到metastore信息和hdfs信息不一致,会报错。这个也可算是hive的bug,尚未修复,但也可以理解为初衷不建议直接操作hdfs数据。
目前,可以采用命令: 

  1. MSCK REPAIR TABLE table_name;  

该命令会把没添加进partition的数据,都增加对应的partition。同步源数据信息metadata。

Recover Partitions

Hive在metastore中存储每个表的分区列表,如果新的分区加入HDFS后,metastore不会注意这些分区,除非

ALTER TABLE table_name ADD PARTITION

当然可以通过

MSCK REPAIR TABLE table_name;

类似EMR版本中的如下命令

ALTER TABLE table_name RECOVER PARTITIONS;


本文出自 “点滴积累” 博客,请务必保留此出处http://tianxingzhe.blog.51cto.com/3390077/1706872

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
大数据
使用钉钉扫一扫加入圈子
+ 订阅

大数据计算实践乐园,近距离学习前沿技术

其他文章