Hive个人心得笔记之表结构,数据类型,join
目录
2.Full outer 全外连接 6 四条重复+2条没提交的
一.连接-join
1.建表:
create external table order_t (id string,time string,pid string,amount int) row format delimited fields terminated by ' ' location '/order';
create external table product_t (pid string,name string,price int) row format delimited fields terminated by ' ' location '/product';
2.查询:
select * from product_t join order_t on product_t.pid=order_t.pid;
3.inner join
select * from product_t inner join order_t on product_t.pid=order_t.pid;
4.left join
select * from product_t left join order_t on product_t.pid=order_t.pid;
5.right join
select * from product_t right join order_t on product_t.pid=order_t.pid;
6.Full outer join
select * from product_t full outer join order_t on product_t.pid=order_t.pid;
7.left semi join
select * from product_t left semi join order_t on product_t.pid=order_t.pid;
1.创建两个外部表
2.创建语句
3.不指定默认内连接 inner join
1.假设 五条数据 成功提交四条 5 4 5
左外连接,右外连接,全外连接 LEFT OUTER JOIN RIGHT OUTER JOIN FULL OUTER JOIN
1.inner join 内连接 4 提交为准
2.Full outer 全外连接 6 四条重复+2条没提交的
3.Left/right 以左表,或者右表为基准
4.笛卡尔积 25 相乘
二.内部表和外部表
一、内部表的概念
- 先在hive里建一张表,然后向这个表插入数据(用insert可以插入数据,也可以通过加载外部文件方式来插入数据),这样的表称之为hive的内部表
二、外部表的概念
- HDFS里已经有数据了,然后,通过hive创建一张表来管理这个文件数据。则这样表称之为外部表
- 注意,hive外部表管理的是HDFS里的某一个目录下的文件数据
三、外部表创建命令
- 进入hive,执行:create external table stu (id int,name string) row format delimited fields terminated by ' ' location '/目录路径'
四、内部表和外部表的区别
- 对于内部表,在删除该表的时候,HDFS对应的目录节点会被删除
- 对于外部表,在删除该表的时候,HDFS对应的目录节点不会删除
三.分区表
一、概述
- 分区表可以通过添加指定的字段来提高Hive的查询效率
- 在数据量较大的情况下,往往会添加分区表来避免全表查询
二、分区表指令
指令 | 作用 | 额外说明 |
create table book (id int, name string) partitioned by (category string) row format delimited fields terminated by '\t'; | 创建book表,以category作为分区 | 在创建分区表时,partitioned字段可以不在字段列表中。生成的表中自动就会具有该字段。 |
load data local inpath '/home/cn.txt' overwrite into table book partition (category='cn'); | 将本地文件cn.txt添加到book表中,分区字段为cn | 在HDFS下生成category=cn目录 |
select * from book where category='cn'; | 查看分区为cn的数据 |
|
ALTER TABLE book add PARTITION (category = 'jp') location '/user/hive/warehouse/park.db/book/category=jp'; | 将指定的目录添加为分区字段 |
|
show partitions iteblog; | 查看分区 |
|
msck repair table book; | 修复分区 |
|
alter table book drop partition(category='cn'); | 删除分区 |
|
alter table book partition(category='french') rename to partition (category='hh'); | 修改分区的名字 |
|
四.分桶表
一、概述
- 分桶表是一种更细粒度的数据分配方式
- 一个表既可以分区也可以分桶
- 分桶的主要作用是实现数据的抽样,方便进行数据测试
- 分桶表通过hash分桶算法,将数据分放在不同的桶(hdfs中的文件)中,方便后续获取
- 分桶表机制默认是不开启的,需要手动开启:set hive.enforce.bucketing=true;
- 分桶表不允许以外部文件方式导入数据,只能从另外一张表数据导入
二、分桶表语法
指令 | 作用 | 额外说明 |
create table teacher(name string) clustered by (name) into 3 buckets row format delimited fields terminated by ' '; | 创建teacher表,以name作为分桶机制,分为3个桶 |
|
insert overwrite table teacher select * from tmp; | 将tmp表中的数据添加到teacher表中 | 实际上是产生了3个文件用于存储不分桶的数据 |
select * from teacher tablesample(bucket 1 out of 3 on name); | 进行抽样 | 抽样格式为:bucket x out of y on XXX
|
创建一个表,分桶为6,计算name类型的hashcode的取模随机分配一个桶中
在分桶结构中无法直接导入外部文件,需要重新创建一个表
生成数据
写其他不认,写*
1.单词统计
拆分数据
把外部文件加载进来
以空格才分
切成单独的字段
将切分数据放入一个临时表,分组计数聚合
2.去掉非数字函数 打成JAR包
打成jar
添加jar
五.基本类型
Hive中的类型 | Java中的类型 |
tinyint | byte |
smallint | short |
int | int |
bigint | long |
boolean | boolean |
float | float |
double | double |
string | String |
timestamp | TimeStamp |
binary | byte[] |
四.复杂类型
一、数组类型 array
1.建表语句:
- create external table ex(vals array<int>) row format delimited fields terminated by '\t' collection items terminated by ',' location '/ex';
2.查询每行数组的个数,查询语句:
- select size(vals) from ex;
注:hive 内置函数不具备查询某个具体行的数组元素。需要自定义函数来实现,但这样的需求在实际开发里很少,所以不需要在意。
二、map类型
1.建表语句:
- create external table m1 (vals map<string,int>) row format delimited fields terminated by '\t' map keys terminated by ',' location '/map';
2.查询语句:
- select vals['tom'] from m1;
- 查询数据和非空查询
注意:map类型,列的分割符必须是\t
3.查询语句
- select vals['tom'] from ex where vals['tom'] is not null;
4.如果想做去重工作,可以调用distinct内置函数
- select distinct(ip) from (select vals['tom'] ip from ex where vals['tom'] is not null)ex1;
- 或者select distinct(vals['tom']) from m2 where vals['tom'] is not null;
三、struct 类型
1.建表语句:
-
create external table ex (vals struct<name:string,age:int>)row format delimited collection items terminated by ' ' location '/ex';
2.查询语句:
- select vals.age from ex where vals.name='tom';
- 单个查询
执行