1,hive分桶表
1,在hive中桶表是比表或者分区更加明显的数据范围划分,针对某一列进行桶的组织,队列哈希值,然后除以桶的个数求余:分桶时间数据集分解成更容易管理若干个部分的另一种技术
建桶表的格式: 1,create table mybuckt(id int ,name string ,age int) clustered by (id) into 100 buckets; 分析: clusterd by (id) 是指明我们以id分桶 in to 1000 buckets 是分100个桶 2,create table mybuckt(id int ,name string ,age int) partition by (ndate string) clustered by (id) into 100 buckets; 我们还可以指定先分区在分桶就是说每个ndate分区都有100个桶
好处:1,获得查询的优化
2,可以采取一些抽样查询
2,给桶表添加数据:
物理上,在hdfs上一个桶表就是文件,桶表对于mapreduce的输出文件分区:一个作业产生的桶(输出文件)和reduce任务个数是一样的:
我这里使用的hive2.3 在当前版本中说要开启桶表强制分桶 开启分桶 set hive.enforce.bucketing =true; set mapreduce.job.reduces=20; insert into table bucket1 select * from zxz_5;(zxz_5是我一个表里面的数据添加到bucket1表中) 注意:上面代码中的set mapreduce.job.reduces的数量要和分桶数量一样,这样会加快执行效率 如果以上不设置不会影响分桶,但会很慢,很慢。
分完桶,会是这样:
我们可以看到我们分的是20个桶,因为太长的原因我就显示12条,里面就是我们根据id映射的hash值来分的桶数据
3,桶表的采样:
1,随机抽样基于整行数据 select * from bucket1 tablesample(bucket 3 out of 40 on rand())s;//s是别名可以不加 2,随机抽样基于指定列(使用分桶列更高效) select * from bucket1 tablesmaple(bucket 3 out of 40 in rand())s; 3,随机抽样基于block size select * from bucket1 tablesmple(10 percent)s;(随机抽取桶表的百分之10的数据) select * from bucket1 tablesmple(1m)s;(随机抽取1m的桶表数据) select * from bucket1 tablesmple(10 rows)s;(随机抽取桶表的10行数据)
注:tablesample是抽样语句,语法:TABLESAMPLE(BUCKET x OUT OF y)
y必须是table总bucket数的倍数或者因子。hive根据y的大小,决定抽样的比例。例如,table总共分了64份,当y=32时,抽取(64/32=)2个bucket的数据,当y=128时,抽取(64/128=)1/2个bucket的数据。x表示从哪个bucket开始抽取。例如,table总bucket数为32,tablesample(bucket 3 out of 16),表示总共抽取(32/16=)2个bucket的数据,分别为第3个bucket和第(3+16=)19个bucket的数据。
2,视图
视图我们可以成为伪表,就是把我们查询的结构返回给一个视图,这个查询一定是一个复杂的查询,因为我们如果经常使写复杂的查可能头会很大,这样就用到视图了,我们到时候直接查询视图就可以看到我们之前查询的数据了
select a.name as anem,t1.name from office a join (select * from employee e where e.salary in ((select max(b.salary) as salary from employee b group by b.oid))) t1 on a.oid=t1.oid; 上面是一个统计每个部门的最高工资的人,看着这么长的sql语句我们就可以简写成: create view employ_max as select a.name as aname,t1.name as bname from office a join (select * from employee e where e.salary in ((select max(b.salary) as salary from employee b group by b.oid))) t1 on a.oid=t1.oid; 下次我们只要执行 select * from employ_max; 同我们上面的查询
查看视图: show view employ_max; 可查看视图的字段
删除视图: drop view employ_max;
3,补充
1,cast(value as type) //类型转换函数
select cast(nid as decimal(10,2)) ,name ,phone from zxz_5 ;
2,concat(value,value,value) //字符连接函数
select cast(nid as decimal(10,2)) ,name ,concat(phone,#) from zxz_5 ;
3,union all(将多个结果合并输出)
select id,name from customers union all select id,orderno from orders ;
4,explode 表生成函数
select explode(array(1,2,3)) ;