hive02

0x0 hive命令

启动

hive --service metastore #启动元数据库
hive --service hiveserver2 #启动hiveserver2
hiveservices.sh start #通过脚本启动
bin/beeline -u jdbc:hive2://hadoop102:10000 -n atguigu

hive客户端

bin/hive #启动hive客户端
#可以配置打印表头 见附录4
bin/hive -help
bin/hive -e "select id from student;" # -e 不进入客户端执行sql
bin/hive -f # -f 执行文件内sql 
	touch hivef.sql
		select *from student;
bin/hive -f /opt/module/hive/data/hivef.sql #执行文件
bin/hive -f /opt/module/hive/datas/hivef.sql  > /opt/module/datas/hive_result.txt #结果写入文件
hive> dfs -ls /; #查看dfs文件
bin/hive -hiveconf mapred.reduce.tasks=10; #启动带配置

hive配置

常见的配置设置—不赋值的话可以直接看当前值

set mapreduce.job.queuename;---得到default
alter table person set tblproperties("EXTERNAL"="TRUE")--修改内外部表
set mapreduce.job.reduces=3;--设置分区数用于cluster by---不用时设成-1
set hive.exec.mode.local.auto=true;--本地模式
set mapreduce.job.queuename=hive;--虽然名字是mapreduce，但是实际是你启动客户端时显示的计算引擎，非常好用。
set hive.exec.dynamic.partition.mode=nonstrict;--动态分区
set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;--项目里。

hive测试数据

成员表 person表 附录1

员工表 emp表 附录2

部门表 dpt表 附录3

地址表 location表 附录4

0x1 hiveSQL

SQL：结构化查询语言(Structure Query Language)

DDL：数据定义语言 (Data Definition Language) –库use增create删drop改alter查show desc、表增create删drop改alter insert查show desc

DML：数据操纵语言（Data Manipulation Language）–导入导出数据

DQL：数据查询语言（Data Query Language）–表内查询

DCL：数据控制语言 (Data Control Language)

DDL建库

==创建数据库=======================
create database [if not exists] database_name
[comment database_comment]
[location hdfs_path] --默认为配置路径 见#附录6
[with dbproperties (property_name=property_value, ...)];
	-- 已经创建就无法创建,if not exists是为了防止报错
	-- 默认会添加库名 /user/hive/warehouse/库名.db 
	-- 指定location会直接指定文件夹的名字,无库名
drop database database_test;
	-- mysql/metadata/DBS表 可以看见元数据数据库信息

==描述数据库=======================
desc database database_test;
desc database extended database_test; -- 可以看见参数信息

==显示数据库=======================
show databases like 'db_hive*';

==修改数据库=======================
alter database db_hive set dbproperties('createtime'='20170830');

--删除数据库
drop database if exists database_test; --删除元数据
drop database if exists database_test cascade; --删除源文件 

DDL建表

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] 表名 
[(col_name data_type [COMMENT col_comment], ...)] 
[COMMENT table_comment] 
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] 
[CLUSTERED BY (col_name, col_name, ...) 
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] 
[ROW FORMAT row_format] 
[STORED AS file_format] 
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement]

DDL示例

create external table if not exists DemoTable --外部表 DFS数据不会删除
(
    id   bigInt comment 'table primary key',
    name string comment 'table col name'
)
    comment 'table_comment'
    partitioned by (day bigint comment 'day',hour bigint comment 'hour')
    clustered by (id,name) into 3 buckets
    row format delimited fields terminated by ',' --列分隔符
    collection items terminated by "_" --map struct yu array的分割符
    map keys terminated by ':' --map与struct中的key与value的分隔符
    lines terminated by '\n' --行分隔符
    stored as parquet --指定存储格式
    tblproperties ('parquet.compression'='LZO') --指定压缩格式
    as select * from demoInfo; --指定选择语句数据
    --     like 表结构

DML导入到处数据

==[load数据链接到表]=======================
-- 本地数据
load data local inpath '/opt/module/hive-3.1.2/datas/student.txt' into table student;
-- hdfs数据 会挪移数据,挪移到建表文件夹
load data inpath '/opt/module/hive-3.1.2/datas/student.txt' into table student;

==[HDFS表数据上传与下载]===================
-- 上传hdfs
hadoop fs -put /opt/module/hive-3.1.2/datas/student.txt  /datas/
-- 直接链接数据生成表
create table student3(id int, name string)
	row format delimited fields terminated by '\t'
	location '/datas';
-- 将HDFS数据导入到本地 --linux客户端输入 --这种靠谱点
hadoop fs -get /user/hive/warehouse/mydb.db/student/student.txt   /opt/module/hive-3.1.2/datas/export

-- hive shell 命令导出本地 linux客户端输入 需带引号 --- 不靠谱
hive -e 'select * from mydb.student'  > /opt/module/result.txt

==[export输出表]===========================
-- 到处表数据到HDFS上[用于平台迁移]
export table student to '/export/student';
--这个表不用提前建,import后自动生成（必须换个表名）在datagrip测试时候不能加分号,不知道为什么
import table student_import from '/export/student'

==[insert将表导出为文件]========================
--导出到文件
insert overwrite local directory '/opt/module/hive/datas/export/student'
select * from student;
--格式化到处到文件 注意 路径中不能有空格 文件会自己创建 输出文件会分区 可以设置reduce数目调整
insert overwrite local directory '/opt/module/hive/student'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
select * from student;
--格式化导出到HDFS
insert overwrite directory '/user/atguigu/student2'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 
select * from student;

DML查改增删

create table person (id int, name string, salary double)

==查======================================
show tables   --show create table table_name显示表的详细创建信息
desc person              --好用
desc extended person
desc formatted person  ----好用 

==改======================================
--修改外部表为管理表
alter table person set tblproperties('EXTERNAL'='FALSE');
--重命名表 注意在hdfs的文件夹的名字不会变,所以改了不太好
alter table person rename to newperson;
--插入
insert into [table] person values(1003,'wangwu',30000.3),(1004,'zhaoliu',40000.4);
insert overwrite table student_par select id, name from student where month='201709';
--分区插入
insert into table DemoTable partition(day=20200102,hour=12) values (1001,'lisi');
--创建新表
create table student2 as select id, name from student1;
--更新列 注意修改类型,如果里面有数据会报错
alter table person change [column] id pid bigint;
--添加列
alter table person add columns (address string);
--替换列 注意这个是替换所有的列,变成新列,数据会删除
alter table dept replace columns(deptno string, dname
 string, loc string);
 
==分区操作======================================
--增加分区
alter table person add partition(day='20200404');
--同时创建多个分区
alter table dept_partition add partition(day='20200405') partition(day='20200406');
--删除单个分区
alter table person drop partition (day='20200406');
--同时删除多个分区
alter table dept_partition drop partition (day='20200404'), partition(day='20200405');
--查看分区表有多少分区 不是分区表会报错
show partitions person;
--查看分区表结构
desc formatted person;

==[删]===========================
truncate table student;
--管理表才能删除hdfs数据

DQL查看库表结构

mydql> metastore 
mysql> select * from DBS --HDFS路径 
mysql> select TBL_ID,TBL_NAME from TBLS;--表名 ID 
mysql> select * from SDS --ID 压缩格式
show databases;
desc database [extended] 库名;
show tables ; 
show create table 表名; 
desc [extended|formatted] 表名;
--查看表分区结构
show partitions person;
--查看方程函数
show functions; 
--解释方程函数
desc  function [extended] 方法名

DQL多分区插入

==[多分区查询]========================================
--多表(多分区)插入模式(根据一张表多分区查询结果)
insert overwrite table student partition(month='201707') 
select id, name 
where month='201709';
insert overwrite table student partition(month='201706') 
select id, name 
where month='201709';

==[多表联合查询]======================================
--union all会将查询数据从上到下插入临时表中
--向表分区插入数据和在分区表查询数据不一样(前者在表后+partition() 后者 只能在where里面选择)
select * from dept_partition where day='20200401'--只能用where这种形式查询分区
	union all--union all适合横向拼接,join为列向拼接
select * from dept_partition where day='20200402'
	union all
select * from dept_partition where day='20200403';
select * from dept_partition where day='20200401' 
	or day='20200402' 
	or day='20200403';

0x2 hive函数

显示函数

show functions; 
--解释方程函数
desc function [extended] 方法名

执行顺序数

from->join->where->group by->having->select->order by desc->limit

标点符号[待整理]

===[()]==================================================
--必须使用的少：子查询，创建表，函数
    正确： select (p.comm) c from emp p;
           select p.comm c from emp p; ----多表连接推荐使用
           select comm c from (emp) p; 
           select comm c from (select * from emp union all select * from emp)p; 
           --必须使用(),但别名不一定
在on、where、having 后面的条件如
            select emp.empno eno,d.loc dc,d.deptno dno
            from emp
            inner join dept d
            --用 (dept) d 报错
            --join后如果是子查询必须用()并加上别名。如果不是子查询必须不加上()别名随意
            on emp.deptno = d.deptno; 
            ----或on （emp.deptno） = （d.deptno）;
            ----或on （emp.deptno = d.deptno）;
            ----或on emp.deptno = d.deptno and d.deptno > 10 or d.deptno = 10;
            ----或on (emp.deptno = d.deptno) and (d.deptno > 10) or (d.deptno = 10);
            ----或on (emp.deptno = d.deptno) and (d.deptno > 10 or d.deptno = 10);
    还有其他地方暂时不提了
    --比如创建表()，指定分区表分桶表，函数(),values()，in()，等
    错误  select ((p.comm) c) from emp p;
==['' ""]============================================
sql中的'' ""
基本上字符串全是'' 
特别是shell脚本里sql=" "引号里面的sql用''
这样比较好 $hive -e "$sql"  
==[%_ *]=================================================
sql中的 % _ *
	A字段 [NOT] LIKE B值 
	----'%_'(一个或多个)和一个字符
	show databases like 'mydb*'   任意多个 ----这个其实用的非常长

collect_set

行转列函数

select collect_set(job) from emp;--去重
select collect_list(job) from emp;--不去重

select concat_ws('_',collect_set(job)) from emp;--拼接sql,seq->字符串

select collect_list(job) from emp group by job;--根据分组,把分组数据变成list

select job,collect_list(sal) from emp group by job;--[重]求当前的job所有工资集合
select job,sort_array(collect_list(sal)) from emp group by job;--排序集合 从低到高
select job,sort_array_by(collect_list(sal)) asc from emp group by job; --可以传字段
select job,avg(sal) from emp group by job;--[重]求当前的job所有工资平均工资

select collect_set(sal)[0] from emp; --选择调用
select collect_list(job)[0] from emp group by job;--选择调用
select collect_set(job)[0] from emp group by job;
select collect_list(distinct(job)) from emp; --去重效果
==[size]============
select size(collect_list(job)) from emp; --求集合大小
select size(collect_set(job)) from emp; --求集合大小

substring

-- 获取年份
select substring(hiredate,1,4) from emp;

union/union all

==================
-- 纵向拼接函数 union all不去重
select * from emp
    union all
select * from emp;
====================
-- union去重
select * from emp
    union 
select * from emp;

聚合函数

collect_list() / collect_set() 
--虽然不是聚合函数，但是重要性不比下面小。
select deptno,job,collect_list(sal) from emp group by job,deptno;
-- 本质：每个组数据一行一行的进行(map算子)，count计数器加1，sum += x ， 
-- avg = sum/count(*) , 
-- max = max(之前的max，x)
-- min = min(之前的min，x)
-- 基于这个本质sum(1) = count(*)
==[count(*) ]==================================
-- 组内行数的和(如果没分组就是全表一个组)
-- 里面是常数，查出来只能是分组后的折合行数(分组把原来多少行聚合了)
-- [重]count(字段)的时候为null的字段行不计数：
    比如
    select count(sal) from emp; --14  
    select count(mgr) from emp; --13
==[sum(字段)]====================================
-- 组内字段的和（如果没分组就是全表一个组）-----任意基本类型数据
-- 遇到null的字段直接跳过不计算如
    select sum(comm) from emp; --2200.0
    select sum(job) from emp;  --0.0
==[avg(字段)]====================================
-- 组内字段的平均值（如果没分组就是全表一个组）
-- 任意基本类型数据
-- 遇到null的字段直接跳过不计算如select avg(comm) from emp; 
--0.0
select avg(job) from emp;
==[max(字段)]====================================
-- 组内字段的最大值（如果没分组就是全表一个组）----任意基本类型数据
-- 遇到null的字段直接跳过不计算如
select sum(comm) from emp; 
-- 1400.0 comm是补贴,有的有,有的没有
select max(job) from emp;
-- SALESMAN
==[min(字段)]====================================
-- 组内字段的最小值(如果没分组就是全表一个组)
-- 跟max一样任意基本类型数据
-- 遇到null的字段直接跳过不计算如
select min(comm) from emp; 
-- 0.0
select min(job) from emp;----ANALYST

算数运算符

-- 算数运算符：少见
-- 一般是+ - * / % 
-- 一般用于数值类型数据
select sal+1 from emp;
-- 非数值string类+数值后变成null
select comm + "str" from emp; 
-- int+string=null,string为非数值型str
-- 非数值string类＋''字符串也变null
-- int数值类string + int 
select comm + "123" from emp; 
-- 变成double应该有自动提升
-- null不能进行算术运算
-- 所有有null参与的运算结果都为null。

比较运算符

比较运算符:一般用在on,where,having 使用
===============================================
-- A=B
select job from  emp where job='CLERK' --全是CLERK

-- A<=>B 一般为null 返回false 两边为null返回true 
-- 可以用于监控null值概率
select comm<=>null from emp;
select sum(`if`(comm<=>null=false,1,0)) from emp;
select round(sum(`if`(comm<=>null=true,1,0))/sum(1),2) from emp;

-- A<>B, A!=B A或者B有null就返回null 然后等于返回false 不等于返回true
select job != 'CLERK' from emp;
-- A<B A<=B A>B A>=B	

-- A [NOT] BETWEEN B AND C
select ename,sal from emp where sal between 2000 and 3000; --工资在2000-3000之间的员工
-- A IS NULL	A IS NOT NULL
select comm from emp where comm is not null; --判断comm不为空

-- IN(数值1, 数值2)	
select job from  emp where job in ('CLERK','MANAGER');--可以用字符串
select sal from emp where sal in (1600,2000,3000);--选择里面的数据
select sal from emp where sal in (select sal from emp where sal>1000);--可以接语句

-- A [NOT] LIKE B STRING 
-- ‘x%’表示结果必须以字母‘x’开头
select ename from emp where ename like 'A%';--ALLEN ADAMS
-- ‘%x’表示结果必须以字母’x’结尾
select ename from emp where ename like '%N';--ALLEN MARTIN
-- 而‘%x%’表示结果包含有字母’x’,可以位于开头，结尾或者字符串中间
select ename from emp where ename like '%A%';--ALLEN WARD ...
-- _表示单个字符
select ename from emp where ename like 'CLAR_';--CLARK

-- A RLIKE B, A REGEXP B	STRING 
 后续补充 正则表达式

==[nvl(字段，默认值)]===============
--如果字段为null就取默认值返回。
--函数整体会返回一个任意类型值。
select nvl(comm,1) from emp;

limit

主要用于限制返回的行数

--返回2行
select * from  emp order by empno desc limit 2;
--返回从第一行开始,返回2行,包含第一行
select * from  emp order by empno desc limit 1,2;

0x3 hive语法

hive的别名

别名的好处

1.能够简化查询

2.提高执行效率(减少系统寻字段时间)

join

--内连接
select
    e.empno, e.ename, d.deptno
from emp e
inner join dept d
    on
e.deptno = d.deptno;
--左外连接
select 
	e.empno, e.ename, d.deptno 
from emp e 
left join dept d 
on e.deptno = d.deptno;
--右外连接
select 
	e.empno, e.ename, d.deptno 
from emp e right join dept d 
on e.deptno = d.deptno;
--全join
select e.empno, e.ename, d.depart from emp e full join dept d on e.deptno = d.depart;

where与on的区别

-- map join 配置
set hive.auto.convert.join = true;-- 默认为false
--该参数为true时，Hive自动对左边的表统计量，如果是小表就加入内存，即对 小表使用Map join
set hive.mapjoin.smalltable.filesize;--大表小表的阀值：

==[inner join]=======================
两者效率相同
-- 按照部门进行过滤
select e.ename name,e.comm comm,d.id depart,e.deptno,d.deptno
from emp e
join dept d
on e.deptno = d.deptno;--14行
-- 按照部门进行过滤
select e.ename name,e.comm comm,d.id depart,e.deptno,d.deptno
from emp e
join dept d
where e.deptno = d.deptno;--14行
==[left join]=========================
-- left join
-- 存在on and和on where条件的区别
-- [重][本质] 在进行left join时,on条件是在生成临时表时使用的条件,其本质执行过程不管on中条件是否为真,都会在临时表中生成左表中的全部记录 然后根据on条件过滤右边临时表(即使左边也会有过滤条件)
-- where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义(必须返回左边表的记录)了，条件不为真的就全部过滤掉，on后的条件用来生成左右表关联的临时表，where后的条件对临时表中的记录进行过滤。
-- 单过滤条件考虑where中->谓词下推
-- 总结:小表在左,实现mapjoin

多表join优化

select
*
from emp
join dept
    on emp.deptno = dept.deptno
join location
    on  dept.deptno = location.loc
-- 优化：当对3个或者更多表进行join连接时，如果每个on子句都使用相同的连接键的话，那么只会产生一个MapReduce job。

hiving与where的不同点

-- where后面不能写分组函数，而having后面可以使用分组函数。
-- having只用于group by分组统计语句。
-- 只能是分区的字段，或者组函数
-- where 过滤条件可用子查询，单值用 = ,多值用in 
select ename,job 
from emp 
where sal = (select min(sal) from emp); 或 
in(select sal from emp); 
-- 但注意必须()，不能别名

select job,max(sal) max_sal from emp group by job having job = 'CLERK';
select job,max(sal) max_sal from emp group by job where job = 'CLERK';--语法错误


-- 关于limit索引问题这两个相同
select sal from emp order by sal limit 1,3;
select sal from emp order by sal limit 0,3;

排序四个by

• order by
• distribute by
• sort by
• cluster by

==[order by]=================================
-- order by 分组字段或分组函数值（其实也相当于新表的字段） 
-- asc（默认升序，从小到大）
-- desc (null会自动当做最小值，而且多个null怎么排序不合适)
-- 支持多个字段按前后顺序order by deptno desc,sal asc;
-- 可组合升序降序
-- 全局排序只有一个Reducer,缺点很慢
select deptno,sal from emp order by deptno desc,sal asc;

==[distribute by]=============================
-- 设置reducer数量 注意要在hive客户端里使用,beeline客户端好像不能设置
set mapreduce.job.reduces=3;
-- 不用时设置-1 默认值,动态调整
distribute by (不同分区进入不同的reducer)
-- 类似MR中partition(自定义分区)
-- 注意区分这个和分桶的关系
-- [分区规则]使用字段的hash码与reduce的个数进行模除
-- Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前
select * from emp distribute by deptno sort by empno desc;
insert overwrite local directory '/opt/module/hive/data/distribute-result' 
select * from emp distribute by deptno sort by empno desc;
-- 输出存在有三个文件就对应对应三分区

==[sort by]===================================
-- reducer内排序
-- 也少用
-- 对于大规模的数据集order by的效率非常低。在很多情况下，并不需要全局排序，此时可以使用sort by。
-- 每个Reducer内部进行排序，对全局结果集来说不是排序。
select * from emp sort by deptno desc;

==[cluster by]================================
-- 前两者所用字段相同时
-- 起到了分区内排序。
-- 只能是默认升序排列
select * from emp cluster by deptno;
-- 其实计算结果跟分桶表有点类似
-- 这个hash分区器比较粗(不能自定义分区器)。不能随意按自己的规则来排序。一个指定分区字段，一个指定排序字段   
-- 有个问题：分区排序的两个字段(必须原封不动)必须出现在select里面。不然报错(order by不会)
-- 不能自定义分区规则

分区

分区 就是分目录（将数据按分区字段存入不同文件夹）

where时会进入不同分区文件夹，减少查询量

partitioned by (dt string)

分区字段必须建表时指定，并且不是create()括号那些字段

--需要建表时先分区，才能执行后面的add
create table student(
    id int, 
    name  string, 
   )
partitioned by (day string) 
row format delimited fields terminated by '\t';
--不能在加载的数据中，只是用于select 后面where 过滤用的，用
select * from table_name
-- 会显示这个字段信息(但其实hdfs里数据没有)应该是mysql元数据partitions表给你显示的
-- 
insert into table DemoTable partition(day=20200102,hour=12) values (1002,'lis5');
insert into DemoTable values(1001,'lisi');--报错
-- 必须指定分区才能插入数据
-- 每次插入数据都会在hdfs上生成不一个新文件,不是追加文件
-- 一般来讲我们会使用下面方法加载数据
load data inpath '/input/person.txt' into table person_text partition(day='20200401',hour='12');

分区数据操作方式

==[插入数据]=================================================
-- 分区表两种方式加载数据时，必须指定分区
-- 分区文件夹不需要提前创建(加载后自动生成)、数据内部不需要分区字段。
-- 查询的时候会自动把分区目录名放到最后一列。
load data local inpath '/opt/module/hive/datas/dept_20200403.log' into table dept_partition partition(day='20200403');--(注意字段数量匹配)
insert overwrite table student partition(month='201706') select id, name from student where month='201709'; --(注意字段数量匹配)
insert overwrite table student partition (day='20200201') select empno,ename from emp;
select * from emp; --可以从其他表拿出数据,但是其中的数据类型要对应


==[分区数据日常]==============================
select * from dept_partition where day='20200403';
-- 查询必须用where过滤分区字段而不是partition

-- 创建分区目录并关联数据
1.创建分区目录，load数据(提前创建分区目录多此一举:后面这一步就够了)
2.创建分区目录，直接把文件放入分区目录下，而不用load。会查询不出来。
3.需msck repair table 表名; --修复表,关联表
4.alter table add partition(day='20200404');
-- 查看分区数
show partitions person ;
-- 删除添加分区数
alter table student add partition(day='2020-04-05') partition(day='2020-04-07');
alter table student drop partition(day='2020-04-04'),partition(day='2020-04-05');
-- 二级分区：同理比如partitioned by(dt string,ts double)  partition(dt = '',ts = xxx)

==[动态分区]==============================
-- 动态分区设置非严格方式：
set hive.exec.dynamic.partition.mode=nonstrict;
-- 设定可以一共设置多少动态分区: 
hive.exec.max.dynamic.partitions=1000
-- 设置每个节点可以拥有多少分区: 
hive.exec.max.dynamic.partitions.pernode=100
-- 整个MR Job中，最大可以创建多少个HDFS文件。默认100000
hive.exec.max.created.files=100000
-- 当有空分区生成时，是否抛出异常。一般不需要设置。默认false
hive.error.on.empty.partition=false

-- 创建动态分区表:和前面创建分区表一模一样
create table student_partition(
    id int,
    name string,
    class int --直接使用表中字段做分区,报错
   )
partitioned by (class int);

-- 正确建表与插入数据方法
set hive.exec.dynamic.partition.mode=nonstrict;
create table student_partition(
    id int,
    name string
)partitioned by (class int);
-- 查询插入 为则正常字段+分区字段(类型相同) (这个字段多了没事不符合不报错)
insert overwrite table  student_partition 
select 
	empno,ename,deptno 
from emp;
select * from student_partition;
show partitions student_partition;

-- 查询插入,也可以这样,直接给定
insert overwrite table dept_partition_dy partition(loc) select deptno, dname, '2020-04-05' from dept;

-- load文件数据经列分隔符列数 = 表正常字段 + 分区字段数。(这个不符合会报错)
load data local inpath '/opt/module/hive/datas/dept_20200403.log' into table dept_partition;

-- [注意]假如分区字段有null值(普通加载方式指定特定分区值所以不会。动态分区的话假如有null ，null就当做一个分区;
-- [筛选null值]在hdfs上显示
dt=__HIVE_DEFAULT_PARTITION_
select * from student where dt is null;

分桶

分桶是分文件

分桶 
-- 就是分文件，分桶字段必须是上面已经定义的
-- 与分区的另一个区别是字段的重用,必须使用建表语句中的字段

--查看num buckets
--用的很少
desc formatted 表名;

-- 创建分桶表
create table stu_buck(
    id int,
    name string
)
clustered by (id)
into 4 buckets 
row format delimited fields terminated by '\t';
-- 对分桶字段排序已经不玩了
-- 采用对分桶字段的值进行哈希，取模。
set mapreduce.job.reduces=-1;
-- job自行决定reduce数量
-- 不要使用本地模式 本地模式防止数据找不到
-- 往分桶表放数据
load data inpath '/datas/student.txt' into table stu_buck;

数据抽样查询

==[数据块抽样]=================
select * from emp tablesample(10 percent); -- 抽取比例
select * from emp tablesample(5 rows); -- 每个map抽取5行

==[分桶随机抽象]===============
select * from emp tablesample(bucket 1 out of 10 on rand());
select * from emp tablesample(bucket 1 out of 10 on empno);
-- bucket 1 表示从几号桶开始
-- of 10 表示一共抽象10个桶
-- rand() 函数
select rand(); --随机产生一个(0-1)内的数字

==[随机抽样]=========================
-- 随机抽样(妙用rand()函数)
select empno from emp distribute by rand() sort by rand() limit 3;
select empno from emp cluster by rand() limit 3;

附录

附录1

person表数据

songsong,bingbing_lili,xiao song:18_xiaoxiao song:19,hui long guan_beijing_1001
yangyang,caicai_susu,xiao yang:18_xiaoxiao yang:19,chao yang_beijing_1002

vim /opt/module/hive-3.1.2/datas/person.txt
#上传hdfs
hadoop fs -put /opt/module/hive-3.1.2/datas/person.txt  /input/

-- 建立text表,用于输入非格式化的数据
create table person_text(---建表
  name string ,
  friends array<string>,
  child  map<string,int>,
  address struct<street:string,city:string,mph:int>
)
partitioned by (day string, hour string)      --设置分区字段--对应导入时的分区条件的选择
clustered by (name) into 3 buckets 	          --分桶表：用的少
row format delimited fields terminated by ',' -- 列分隔符-不同字段的分隔符必须一致，一般是'/t'制表符
collection items terminated by '_'            --MAP STRUCT和ARRAY的分隔符(数据分割符号)--集合中不同元素分隔符必须统一处理
map keys terminated by ':'                    -- MAP与STRUCT中的key与value的分隔符
lines terminated by '\n'                      -- 行分隔符，\n可默认不写
stored as textfile;
load data inpath '/input/person.txt' into table person_text partition(day='20200401',hour='12');

-- 建立压缩表
create table if not exists person(---建表
  name string ,
  friends array<string>,
  child  map<string,int>,
  address struct<street:string,city:string,mph:int>
)
partitioned by (day string, hour string)      --设置分区字段--对应导入时的分区条件的选择
clustered by (name) into 3 buckets 	          --分桶表：用的少
row format delimited fields terminated by ',' -- 列分隔符-不同字段的分隔符必须一致，一般是'/t'制表符
collection items terminated by '_'            --MAP STRUCT和ARRAY的分隔符(数据分割符号)
-- --集合中不同元素分隔符必须统一处理
map keys terminated by ':'                    -- MAP与STRUCT中的key与value的分隔符
lines terminated by '\n'                      -- 行分隔符，\n可默认不写
stored as parquet                             -- 指定存储方式
tblproperties("parquet.compression"="LZO");   --指定压缩格式


show tables;
desc person;
desc formatted person;

--todo 插入数据
insert into person select * from person_text;

附录2

7369	SMITH	CLERK	7902	1980-12-17	800.00		20
7499	ALLEN	SALESMAN	7698	1981-2-20	1600.00	300.00	30
7521	WARD	SALESMAN	7698	1981-2-22	1250.00	500.00	30
7566	JONES	MANAGER	7839	1981-4-2	2975.00		20
7654	MARTIN	SALESMAN	7698	1981-9-28	1250.00	1400.00	30
7698	BLAKE	MANAGER	7839	1981-5-1	2850.00		30
7782	CLARK	MANAGER	7839	1981-6-9	2450.00		10
7788	SCOTT	ANALYST	7566	1987-4-19	3000.00		20
7839	KING	PRESIDENT		1981-11-17	5000.00		10
7844	TURNER	SALESMAN	7698	1981-9-8	1500.00	0.00	30
7876	ADAMS	CLERK	7788	1987-5-23	1100.00		20
7900	JAMES	CLERK	7698	1981-12-3	950.00		30
7902	FORD	ANALYST	7566	1981-12-3	3000.00		20
7934	MILLER	CLERK	7782	1982-1-23	1300.00		10

create table if not exists emp(
empno int,
ename string,
job string,
mgr int,
hiredate string, 
sal double, 
comm double,
deptno int)
row format delimited fields terminated by '\t';

load data local inpath '/opt/module/datas/emp.txt' into table emp;

附录3

10	ACCOUNTING	1700
20	RESEARCH	1800
30	SALES	1900
40	OPERATIONS	1700

create table if not exists dept(
deptno int,
dname string,
loc int
)
row format delimited fields terminated by '\t';

load data local inpath '/opt/module/datas/dept.txt' into table
dept;

附录4

1700	Beijing
1800	London
1900	Tokyo

create table if not exists location(
loc int,
loc_name string
)
row format delimited fields terminated by '\t';

load data local inpath '/opt/module/datas/location.txt' into table location;