Hive个人心得笔记之表结构，数据类型，join

叁歲伎倆 2021-12-11 22:55 397阅读 0赞

# **                 Hive个人心得笔记之表结构，数据类型，join** #

一.连接-join

1.建表：

2.查询：

3.inner join

4.left join

5.right join

6.Full outer join

7.left semi join

1.创建两个外部表

2.创建语句

3.不指定默认内连接  inner join

1.假设 五条数据 成功提交四条   5  4  5

1.inner join     内连接       4      提交为准

2.Full  outer  全外连接     6    四条重复+2条没提交的

3.Left/right     以左表，或者右表为基准

4.笛卡尔积        25  相乘

二.内部表和外部表

一、内部表的概念

二、外部表的概念

三、外部表创建命令

四、内部表和外部表的区别

三.分区表

一、概述

二、分区表指令

四.分桶表

一、概述

二、分桶表语法

1.单词统计

2.去掉非数字函数 打成JAR包

四.基本类型

五.复杂类型

一、数组类型 array

1.建表语句：

2.查询每行数组的个数，查询语句：

二、map类型

1.建表语句：

2.查询语句：

3.查询语句

4.如果想做去重工作，可以调用distinct内置函数

三、struct 类型

1.建表语句：

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.创建两个外部表** ##

![20190709184328658.png][]

## 2.创建语句 ##

![20190709184328665.png][]

## 3.不指定默认内连接  inner join  ##

![20190709184328681.png][]

### 1.假设 五条数据 成功提交四条   5  4  5 ###

**左外连接，右外连接，全外连接        LEFT OUTER JOIN    RIGHT OUTER JOIN    FULL OUTER JOIN**

### 1.inner join     内连接       4      提交为准 ###

![20190709184328686.png][]

### 2.Full  outer  **全外连接   **  6    四条重复+2条没提交的   ###

![20190709184328698.png][]

### 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的查询效率
 *  在数据量较大的情况下，往往会添加分区表来避免全表查询

## 二、分区表指令 ##

<table> 
 <tbody> 
 <tr> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:5.0562in;"> 指令 </td> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:2.3256in;"> 作用 </td> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:3.9388in;"> 额外说明 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:5.0562in;"> create table book (id int, name string) partitioned by (category string) row format delimited fields terminated by '\t'; </td> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:2.3256in;"> 创建book表，以category作为分区 </td> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:3.9388in;"> 在创建分区表时，partitioned字段可以不在字段列表中。生成的表中自动就会具有该字段。 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:5.0562in;"> load data local inpath '/home/cn.txt' overwrite into table book partition (category='cn'); </td> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:2.3256in;"> 将本地文件cn.txt添加到book表中，分区字段为cn </td> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:3.9388in;"> 在HDFS下生成category=cn目录 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:5.0562in;"> select * from book where category='cn'; </td> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:2.3256in;"> 查看分区为cn的数据 </td> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:3.9388in;"> &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:5.0562in;"> ALTER TABLE book add&nbsp; PARTITION (category = 'jp') location '/user/hive/warehouse/park.db/book/category=jp'; </td> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:2.3256in;"> 将指定的目录添加为分区字段 </td> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:3.9388in;"> &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:5.0562in;"> show partitions iteblog; </td> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:2.3256in;"> 查看分区 </td> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:3.9388in;"> &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:5.0562in;"> msck repair table book; </td> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:2.3256in;"> 修复分区 </td> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:3.9388in;"> &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:5.0562in;"> alter table book drop partition(category='cn'); </td> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:2.3256in;"> 删除分区 </td> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:3.9388in;"> &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:5.0562in;"> alter table book partition(category='french') rename to partition (category='hh'); </td> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:2.3256in;"> 修改分区的名字 </td> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:3.9388in;"> &nbsp; </td> 
 </tr> 
 </tbody> 
</table>

# 四.分桶表 #

## 一、概述 ##

1.  分桶表是一种更细粒度的数据分配方式
2.  一个表既可以分区也可以分桶
3.  分桶的主要作用是实现数据的抽样，方便进行数据测试
4.  分桶表通过hash分桶算法，将数据分放在不同的桶(hdfs中的文件)中，方便后续获取
5.  分桶表机制默认是不开启的，需要手动开启：set hive.enforce.bucketing=true;
6.  分桶表不允许以外部文件方式导入数据，只能从另外一张表数据导入

## 二、分桶表语法 ##

<table> 
 <tbody> 
 <tr> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:4.2347in;"> 指令 </td> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:2.0444in;"> 作用 </td> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:4.9687in;"> 额外说明 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:4.2347in;"> create table teacher(name string) clustered by (name) into 3 buckets row format delimited fields terminated by ' '; </td> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:2.0444in;"> 创建teacher表，以name作为分桶机制，分为3个桶 </td> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:4.9687in;"> &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:4.2347in;"> insert overwrite table teacher select * from tmp; </td> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:2.0444in;"> 将tmp表中的数据添加到teacher表中 </td> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:4.9687in;"> 实际上是产生了3个文件用于存储不分桶的数据 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:4.2347in;"> select * from teacher tablesample(bucket 1 out of 3 on name); </td> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:2.0444in;"> 进行抽样 </td> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:4.9687in;"> 抽样格式为：bucket x out of y on XXX 
 <ol style="margin-left:.375in;"> 
 <li>x表示抽样的起始桶，例如bucket 1 out of 3表示从第1 个桶开始抽取数据</li> 
 <li>y决定抽样的比例，要求必须是桶数的因子或者整数倍 
 <ol style="margin-left:.375in;"> 
 <li>如果桶数为6，y为2，则表示抽取6/2=3个桶中的数据</li> 
 <li>如果桶数为6，y为3，则表示抽取6/3=2个桶中的数据</li> 
 <li>如果桶数为6，y为12，则表示抽取6/12=0.5个桶中的数据</li> 
 </ol></li> 
 <li>如果桶数为6，抽样为bucket 1 out of 3 on id表示从第1个桶开始抽样，抽取2个桶的数据，所以抽取的样本为1和4桶中的数据</li> 
 </ol></td> 
 </tr> 
 </tbody> 
</table>

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70][]

**创建一个表，分桶为6，计算name类型的hashcode的取模随机分配一个桶中**

**在分桶结构中无法直接导入外部文件，需要重新创建一个表**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70 1][]

**生成数据**

![20190709184328597.png][]

写其他不认，写\*

![20190709184328705.png][]

### 1.单词统计 ###

拆分数据

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70 2][]

把外部文件加载进来

![20190709184328724.png][]

以空格才分

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70 3][]

切成单独的字段

![20190709184328743.png][]

将切分数据放入一个临时表，分组计数聚合

![20190709184328759.png][]

### 2.去掉非数字函数 打成JAR包 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70 4][]

**打成jar**

![20190709184328765.png][]

**添加jar**

![20190709184328777.png][]

# 五.基本类型 #

<table> 
 <tbody> 
 <tr> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:2.5416in;"> Hive中的类型 </td> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:4.4625in;"> Java中的类型 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:2.5416in;"> tinyint </td> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:4.4625in;"> byte </td> 
 </tr> 
 <tr> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:2.5416in;"> smallint </td> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:4.4625in;"> short </td> 
 </tr> 
 <tr> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:2.5416in;"> int </td> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:4.4625in;"> int </td> 
 </tr> 
 <tr> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:2.5416in;"> bigint </td> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:4.4625in;"> long </td> 
 </tr> 
 <tr> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:2.5416in;"> boolean </td> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:4.4625in;"> boolean </td> 
 </tr> 
 <tr> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:2.5416in;"> float </td> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:4.4625in;"> float </td> 
 </tr> 
 <tr> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:2.5416in;"> double </td> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:4.4625in;"> double </td> 
 </tr> 
 <tr> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:2.5416in;"> string </td> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:4.4625in;"> String </td> 
 </tr> 
 <tr> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:2.5416in;"> timestamp </td> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:4.4625in;"> TimeStamp </td> 
 </tr> 
 <tr> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:2.5416in;"> binary </td> 
 <td style="border-color:#a3a3a3;vertical-align:top;width:4.4625in;"> byte[] </td> 
 </tr> 
 </tbody> 
</table>

# 四.复杂类型 #

## 一、数组类型 array ##

### **1.建表语句：** ###

* create external table ex(vals array<int>) row format delimited fields terminated by '\\t' collection items terminated by ',' location '/ex';

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70 5][]

### 2.查询每行数组的个数，查询语句： ###

*  select  size(vals) from ex;
 *  ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70 6][]

**注：hive 内置函数不具备查询某个具体行的数组元素。需要自定义函数来实现，但这样的需求在实际开发里很少，所以不需要在意。**

## 二、map类型 ##

### **1.建表语句：** ###

* create external table m1 (vals map<string,int>) row format delimited fields terminated by '\\t' map keys terminated by ',' location '/map';

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70 7][]

### 2.查询语句： ###

*  select vals\['tom'\] from m1;
 *  查询数据和非空查询

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70 8][]

**注意：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';

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70 9][]

### 2.查询语句： ###

*  select vals.age from ex where vals.name='tom';
 *  单个查询

![20190709184328649.png][]

执行

[20190709184328658.png]: /images/20211211/8bf0da423ff04506a5885dbedb0b7f08.png
[20190709184328665.png]: /images/20211211/3a76f7d2421b481ba5d4e183ab6542e8.png
[20190709184328681.png]: /images/20211211/7eb4a602b71446aabf71624f03019fe2.png
[20190709184328686.png]: /images/20211211/af4c2b16503e47a3a6793758ff051427.png
[20190709184328698.png]: /images/20211211/182eb408c8eb46ec8abd98f84eeb4dd5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70]: /images/20211211/123c259b336a4d7381e41ea7fd9a7650.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70 1]: /images/20211211/cd5abda85d664957a788d2e82a6e798d.png
[20190709184328597.png]: /images/20211211/eee4d002984f472aaf203acbe0f841c3.png
[20190709184328705.png]: /images/20211211/7d7d70d6262b487cb5b4c1e7228a5e06.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70 2]: /images/20211211/521bd0b3f46f4707a0081915f61f1138.png
[20190709184328724.png]: /images/20211211/c7c3870a1c8d4f3ab544810e41641d83.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70 3]: /images/20211211/0cfdde4ce02a4885b6596f5060ebca37.png
[20190709184328743.png]: /images/20211211/70b7a1d566c24b5dbaa888f18fe6696f.png
[20190709184328759.png]: /images/20211211/89897febf68346f18985aed2037d6279.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70 4]: /images/20211211/bf0b97d26a73447fb5391214922eda2b.png
[20190709184328765.png]: /images/20211211/3a88a2d0a60148c08fe738ccc548762e.png
[20190709184328777.png]: /images/20211211/fd6aa9fea5204979af21c00a7d52189f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70 5]: /images/20211211/d124330065194ffca358d7f361533a7f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70 6]: /images/20211211/b6af66f6729b42d88dde3b6c834a5482.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70 7]: /images/20211211/49eefe515d0f49deb0313bd9c2c046e4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70 8]: /images/20211211/468d87a7be584fd9ae2f09a6c6c030ee.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70 9]: /images/20211211/8626b8a0867449819ccfd3bda4799185.png
[20190709184328649.png]: /images/20211211/055e5059c58f4d3aaafceb1dbaab423c.png