Hive原理与Hive建表实例（内部表与外部表，静态与动态分区表，分桶）

最新推荐文章于 2024-06-14 14:10:08 发布
最新推荐文章于 2024-06-14 14:10:08 发布
阅读量3.7k 收藏 12
点赞数 1
分类专栏： Hive 文章标签： hive建表 hive静态与动态分区表 Hive Hive分桶
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/image_fzx/article/details/82084947
版权

1.  Hive的原理

HiveQL语句会转化成MapReduce，提交任务到Hadoop中，用HDFS存储数据，MapReduce查询数据。

1.  用户接口主有三个：CLI、JDBC/ODBC和WebGUI。

   CLI为shell命令行；JDBC/ODBC是Hive的JAVA实现，与传统数据库JDBC类似；WebGUI是通过浏览器访问Hive。

2.  元数据存储：Hive 将元数据存储在数据库mysql , derby中。

   Hive 中的元数据包括表的名字，表的列和分区及其属性，表的属性（是否为外部表等），表的数据所在目录等。

3.  Hive的数据存储

Hive中所有的数据都存储在 HDFS 中，没有专门的数据存储格式（可支持Text，SequenceFile，ParquetFile，RCFILE等）

Hive 中包含以下数据模型：DB、Table，External Table，Partition，Bucket

  • db：在hdfs中表现为${hive.metastore.warehouse.dir}目录下一个文件夹
  • table：在hdfs中表现所属db目录下一个文件夹
  • external table：与table类似，不过其数据存放位置可以在任意指定路径
  • partition：在hdfs中表现为table目录下的子目录
  • bucket：在hdfs中表现为同一个表目录下根据hash散列之后的多个文件

Hive的数据类型

基础数据类型包括：TINYINT, SMALLINT, INT, BIGINT, BOOLEAN, FLOAT, DOUBLE, STRING, BINARY, TIMESTAMP, DECIMAL, CHAR, VARCHAR, DATE。

复杂类型包括： ARRAY, MAP, STRUCT, UNION。

CREATE 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 (country STRING, state STRING);

内部表也称为managed_table，默认存储在/user/hive/warehouse下，也可以通过location指定（一般不指定），删除表时，会删除表数据以及元数据；
外部表称之为external_table，在创建表时可以自己指定目录位置location（一般都要指定）；删除表时，只会删除元数据不会删除表数据。

2.  创建内部表mytable

create table if not exists mytable(sid int, sname string)   \
row format delimited fields terminated by '\005'    \
stored as textfile;

查看HDFS： http://100.168.1.182:50070/explorer.html#/user/hive/warehouse/mytable，有/user/hive/warehouse/mytable目录

创建同另一张表格式一样的表，但数据为空

create table if not exists mytable_2
Like mytable;

2.  创建外部表stocks

我的本地文件 /home/hadoop/Data/stocks/stocks.txt ， 如下：

1,RMB,2018-08-01,1,1,1,
2,HKD,2018-08-02,1.18,1.19,1.20,
3,USD,2018-08-03,0.14,0.15,0.16,
4,GBP,2018-08-04,0.11,0.12,0.13,
5,TWD,2018-08-05,4.48,4.49,4.50,
6,AUD,2018-08-06,0.19,0.20,0.21,

上传文件

hadoop fs -mkdir /user/hive/external/data
hadoop fs -mkdir /user/hive/external/data/stocks
hadoop fs -put stocks.txt /user/hive/external/data/stocks/

这是HDFS中有这份数据，此时该创建外部表指向这份数据。

create external table if not exists stocks(
stock_id  int,
money string,
ymd  string,
price_open  float,
price_mid  float,
price_close  float)
row format delimited  fields  terminated by ','
location '/user/hive/external/data/stocks';

刚开始我用exchange   string， 一直报错，出现NoViableAltException问题

NoViableAltException(109@[2238:1: columnNameTypeOrPKOrFK : ( ( foreignKeyWithName ) | ( primaryKeyWithName ) | ( primaryKeyWithoutName ) | ( foreignKeyWithoutName ) | ( columnNameType ) );])
。。。。。。
FAILED: ParseException line 1:58 cannot recognize input near 'exchange' 'STRING' ',' in column name or primary key or foreign key

后来我将exchange   string改成money string,  就可以了，在数据库中 exchange是更改的意思，不要使用！！

hive> select * from stocks;
OK
1	RMB	2018-08-01	1.0	1.0	1.0
2	HKD	2018-08-02	1.18	1.19	1.2
3	USD	2018-08-03	0.14	0.15	0.16
4	GBP	2018-08-04	0.11	0.12	0.13
5	TWD	2018-08-05	4.48	4.49	4.5
6	AUD	2018-08-06	0.19	0.2	0.21
Time taken: 1.366 seconds, Fetched: 6 row(s)

3.  分区表

分区代表了数据的仓库，也就是文件夹目录。每个文件夹下面可以放不同的数据文件。通过文件夹可以查询里面存放的文件。但文件夹本身和数据的内容毫无关系。

本地有 /home/hadoop/Data/order.txt 如下：

1	2017-06-20 00:01:02.123456789
2	2017-09-21 00:03:04.234567890
3	2018-02-22 05:06:07.345678901
4	2018-05-23 06:07:08.456789012
5	2018-07-24 07:08:09.567890123
6	2018-08-25 08:09:10.678901234

创建静态分区表

create table order_par(order_number string, event_time string)
partitioned by (event_month string)
row format delimited fields terminated by '\t';

加载数据到Hive分区表中：

方法一： 通过load方式加载
load data local inpath  "/home/hadoop/Data/order.txt" overwrite into table order_par  
partition (event_month='2017-09');

方法二： 查询装入
insert overwrite table order_par partition(event_month='2017-09')
select order_number,event_time from order_par where event_month='2018-05';

方法三：  手工创建hdfs目录和上传文件，从而达到添加分区的目的
静态分区表如果手工创建对应的hdfs目录上传文件，分区表中无法查到该分区信息，需要刷新，这种添加分区的途径是不合法

手动在HDFS上创建了分区目录，并手动上传了数据文件，之后：
select * from order_par where event_month='2018-05';

此时是查不到该分区的， 修复表信息之后可以查询
MSCK REPAIR TABLE order_par;

查看数据文件：select * from order_par ;     或者     select * from order_par where event_month='2017-09';

hive> select * from order_par where event_month='2017-09';
OK
1	2017-06-20 00:01:02.123456789	2017-09
2	2017-09-21 00:03:04.234567890	2017-09
3	2018-02-22 05:06:07.345678901	2017-09
4	2018-05-23 06:07:08.456789012	2017-09
5	2018-07-24 07:08:09.567890123	2017-09
6	2018-08-25 08:09:10.678901234	2017-09
Time taken: 0.572 seconds, Fetched: 6 row(s)

查看HDFS 中的数据文件：    /user/hive/warehouse/srt.db/order_par/event_month=2017-09/order.txt

hadoop@Master:~/Data$ hadoop fs -cat /user/hive/warehouse/srt.db/order_par/event_month=2017-09/order.txt
1	2017-06-20 00:01:02.123456789
2	2017-09-21 00:03:04.234567890
3	2018-02-22 05:06:07.345678901
4	2018-05-23 06:07:08.456789012
5	2018-07-24 07:08:09.567890123
6	2018-08-25 08:09:10.678901234

使用where子句，过滤分区字段，遍历某个分区； 以上两个SQL可以查到列event_month信息，而使用hdfs dfs -cat看不到该列，说明Hive分区表的分区列是伪列

添加分区，两个分区有相同的数据

添加新的分区：
alter table order_par add partition(event_month='2018-05');

加载数据：
load data local inpath "/home/hadoop/Data/order.txt" overwrite into table order_par  
partition(event_month='2018-05');

查看分区记录：
hive> select * from order_par where event_month='2018-05';
OK
1	2017-06-20 00:01:02.123456789	2018-05
2	2017-09-21 00:03:04.234567890	2018-05
3	2018-02-22 05:06:07.345678901	2018-05
4	2018-05-23 06:07:08.456789012	2018-05
5	2018-07-24 07:08:09.567890123	2018-05
6	2018-08-25 08:09:10.678901234	2018-05
Time taken: 0.153 seconds, Fetched: 6 row(s)

查看表的分区数：
hive> show partitions order_par;
OK
event_month=2017-09
event_month=2018-05
Time taken: 0.067 seconds, Fetched: 1 row(s)

两个分区的数据合并
select * from order_par where event_month='2018-05'  
union 
select * from order_par where event_month='2017-09';

查看合并之后的结果：
hive> select * from order_par;
OK
1	2017-06-20 00:01:02.123456789	2017-09
2	2017-09-21 00:03:04.234567890	2017-09
3	2018-02-22 05:06:07.345678901	2017-09
4	2018-05-23 06:07:08.456789012	2017-09
5	2018-07-24 07:08:09.567890123	2017-09
6	2018-08-25 08:09:10.678901234	2017-09
1	2017-06-20 00:01:02.123456789	2018-05
2	2017-09-21 00:03:04.234567890	2018-05
3	2018-02-22 05:06:07.345678901	2018-05
4	2018-05-23 06:07:08.456789012	2018-05
5	2018-07-24 07:08:09.567890123	2018-05
6	2018-08-25 08:09:10.678901234	2018-05
Time taken: 0.12 seconds, Fetched: 12 row(s)

删除分区：
alter table order_par drop partition(event_month='2018-05');

动态分区：分区的值是非确定的，由输入数据来确定

如果用上述的静态分区，插入的时候必须首先要知道有什么分区类型，而且每个分区写一个load data，太烦人。使用动态分区可解决以上问题，其可以根据查询得到的数据动态分配到分区里。其实动态分区就是不指定分区目录，由系统自己选择。

首先Hive 有一张表 person_par，如下：

hive> select * from person_par;
OK
lily	china	man	2013-03-28
nancy	china	woman	2013-03-28
hanmei	america	man	2013-03-28
jan	china	woman	2013-03-29
mary	america	man	2013-03-29
lilei	china	man	2013-03-29
  1. 动态分区的字段，需要写在select语句中所有字段的最后
  2. hive需要设置set hive.exec.dynamic.partition=true;(默认值是false，表示是否开启动态分区)
  3. [可选]hive需要设置set hive.exec.dynamic.partition.mode=nonstrict;(默认是strict模式，表示至少需要指定一个静态分区；nonstrict模式表示不需要指定静态分区)
设置动态分区
hive> set hive.exec.dynamic.partition=true;

创建新表person_par_dnm
hive> create table person_par_dnm ( name  string, nation string) partitioned by (sex  string, dt string)
    > row format delimited fields terminated by ',';
OK
Time taken: 0.334 seconds

现在查询分区，并没有
hive> show partitions person_par_dnm;
OK
Time taken: 0.073 seconds

从旧表person_par导入数据到新表中person_par_dnm，自动实现分区sex="man",dt，按照最后的dt分区
hive> insert overwrite table person_par_dnm partition(sex="man",dt) select name, nation, dt  from person_par;

现在查询分区，有以下分区sex=man/dt=2013-03-28和sex=man/dt=2013-03-29
hive> show partitions person_par_dnm;
OK
sex=man/dt=2013-03-28
sex=man/dt=2013-03-29

查看HDFS上面的目录，有   /user/hive/warehouse/person_par_dnm/sex=man，说明系统按照时间自动分区了

4.  分桶表

Hive采用对列值哈希来组织数据的方式, 称之为分桶, 适合采样和map-join.    使用用户ID来确定如何划分桶(Hive使用对值进行哈希并将结果除 以桶的个数取余数。这样，任何一桶里都会有一个随机的用户集合（PS：其实也能说是随机）

桶则是按照数据内容的某个值进行分桶，把一个大文件散列称为一个个小文件

建立原表person_srt
hive> create table person_srt (srtid int, name string, nation string, sex string, dt string)
    > row format delimited fields terminated by ',';

装入数据
hive>load data local inpath '/home/hadoop/Data/person_srt.txt' overwrite into table person_srt;

查看数据
hive> select * from person_srt;
OK
1	lily	china	man	2013-03-28
2	nancy	china	woman	2013-03-28
3	hanmei	america	man	2013-03-28
4	jan	china	woman	2013-03-29
5	mary	america	man	2013-03-29
6	lilei	china	man	2013-03-29

*****************************************************************************
建立新的分桶表person_srt2，要求：
1.指定根据哪一列来划分桶： clustered by (srtid)
2. 以srtid降序排列：sorted by(srtid desc)
3. 指定划分几个桶：  into 2 buckets
distribute by 类似于mapreduce中分区partition，对数据进行分区，结合sort by进行使用
cluster by   当distribute by和sort by字段相同时 可以用cluster by代替
**********************************************************************************
hive> create table person_srt2( srtid int, name string, nation string, sex string, dt string) 
    > clustered by (srtid) sorted by(srtid desc) into 2 buckets
    > row format delimited fields terminated by ',';

设置相关参数
hive> set hive.enforce.bucketing=true;
hive> set mapreduce.job.reduces=2;

把旧表person_srt的数据装入分桶表person_srt2中
hive> insert into table person_srt2 select srtid,name,nation,sex,dt from person_srt 
distribute by(srtid) sort by(srtid asc);

查询分桶表，以降序排列
hive> select * from person_srt2;
OK
6	lilei	china	man	2013-03-29
4	jan	china	woman	2013-03-29
2	nancy	china	woman	2013-03-28
5	mary	america	man	2013-03-29
3	hanmei	america	man	2013-03-28
1	lily	china	man	2013-03-28

对桶中的数据进行采样
从2个桶的第1个中获取所有的用户
hive> select * from person_srt2 tablesample(bucket 1 out of 2);
OK
6	lilei	china	man	2013-03-29
4	jan	china	woman	2013-03-29
2	nancy	china	woman	2013-03-28
从2个桶的第2个中获取所有的用户
hive> select * from person_srt2 tablesample(bucket 2 out of 2);
OK
5	mary	america	man	2013-03-29
3	hanmei	america	man	2013-03-28
1	lily	china	man	2013-03-28

创建分桶表成功，HDFS有如下目录：    /user/hive/warehouse/srt.db/person_srt2

查看HDFS上面的两个文件的数据：

hadoop@Master:~/Data$ hadoop fs -cat /user/hive/warehouse/srt.db/person_srt2/000000_0
6,lilei,china,man,2013-03-29
4,jan,china,woman,2013-03-29
2,nancy,china,woman,2013-03-28
hadoop@Master:~/Data$ hadoop fs -cat /user/hive/warehouse/srt.db/person_srt2/000001_0
5,mary,america,man,2013-03-29
3,hanmei,america,man,2013-03-28
1,lily,china,man,2013-03-28

 

fuzuxian
关注
  • 1
    点赞
  • 踩
  • 12
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Hive编程指南-表创建、内部表、外部表
Evankaka的专栏
02-10 5168
摘要：本文主要介绍了Hive创建表的一些知识，包含内部表、外部表、分区和分桶等。
Hive的分区详解
酒馆的黑喵的博客
04-24 1084
一、分区     hive表就是hdfs的上的一个目录     hive表中的数据，其实就是对应了HDFS上的一个目录下的数据     概念：对hive表的数据做分区管理 创建分区表     create table student_ptn(id int, name string) par...
【Hive中篇： 一篇文章带你了解表的静态分区，动态分区! 分桶！Hive sql的内置函数！复杂数据类型！hive的简单查询语句！】
最新发布
书生的技术分享
06-14 1241
表的静态分区，动态分区，分桶！Hive sql的内置函数！复杂数据类型！以及hive的简单查询语句！在Hive中，分区是一种优化查询性能的技术，它允许用户将数据分成多个子目录，每个子目录称为一个分区。分桶是Hive中另一种优化查询性能的技术，它将数据分成多个文件（称为桶），每个桶中的数据是原始数据的一个子集。分桶通常与抽样、连接操作等一起使用，以提高查询效率。
伪列（ROWNUM）的使用
JustinNeil的博客
05-19 1643
ROWNUM是在查询过程中动态生成的一个列，该列不会在数据库中真实添加，该列必须从1开始 使用ROWNUM查询第一条数据 SELECT ROWNUM,emp.* FROM emp WHERE ROWNUM =1; 查询前5条数据 SELECT ROWNUM,emp.* FROM emp WHERE ROWNUM <=5; 查询第6-10条数据 SELECT * FROM ( SELE...
Hive的基本操作、创建内部表、创建外部表、创建分桶表、创建分区表 04
啊策策大数据社区
03-22 722
1. 创建数据库 创建数据库不指定位置 create database if not exists myhive; use myhive; hive的表存放位置模式是由hive-site.xml当中的一个属性指定的 <name>hive.metastore.warehouse.dir</name> <value>/user/hive/warehouse...
hive分区表分通表建表语句详解和例子
11-01
详细描述了hive分桶表，分区表的创建，附带详细建表语句，包含一级静态分区，二级静态分区，一级动态分区，二级动态分区，分区的查询，删除，添加，数据的导入
HIVE-分桶表的详解和创建实例.docx
04-29
HIVE-分桶表的详解和创建实例.docx
Hive内部表和外部表
01-07
删除表时(drop table)内部表会删除hdfs对应路径，而外部表不会删除hdfs对应的路径, 删除表无论是内部表和外部表都会删除元数据(metastore.TBLS、metastore.COLUMNS_V2) 二：location关键字 用于指定hdfs路径，如果不...
Hive分区表
01-07
一：简介 分区表就是根据指定字段的值进行分类、分组，字段值相同的分为一类然后存储在一个单独的HDFS文件中，多个类就存储在多个文件中。原本存储在一个文件中的数据现在存储在多个文件中，查询...动态分区：创建表
Hive的分区表
01-20
Hive分区表对应的是HDFS上独立的文件夹，该文件夹存放的是该分区的所有数据，其实分区就是分目录。Hive通过分区把一个大的数据集根据业务需要分割成小的数据集，在查询时能够通过where关键词选择指定分区，从而提高...
hive建表以及测试
火羽的博客
03-03 3276
内部表建表（manaaged_table）内部表定义1、由create table命令直接创建，没有加入关键字的表 2、内部表默认存储/user/hive/warehouse下，也可以由location指定、 3、drop表时会将表数据与元数据同时删除 根据以上两种建表方式，引出，hive常用的三种建表的方法内部表常规建表根据需求，分别列出表中的列，创建表应用场景场景：在库中没有相关的表，根据
hivesql建表语句_Hive SQL语法总结
weixin_31662303的博客
11-22 2049
Hive是一个数据仓库基础的应用工具，在Hadoop中用来处理结构化数据，它架构在Hadoop之上，通过SQL来对数据进行操作，了解SQL的人，学起来毫不费力。Hive 查询操作过程严格遵守Hadoop MapReduce 的作业执行模型，Hive 将用户的Hive SQL 语句通过解释器转换为MapReduce 作业提交到Hadoop 集群上，Hadoop 监控作业执行过程，然后返回作...
Hive简介
一度凡尘的专栏
08-19 585
转自 http://www.yiibai.com/hive/ 教程笔记 hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供简单的sql查询功能，可以将sql语句转换为MapReduce任务进行运行。 其优点是学习成本低，可以通过类SQL语句快速实现简单的MapReduce统计，不必开发专门的MapReduce应用，十分适合数据仓库的统计分析。 https
2.4 hive创建表实例讲解
weixin_30384031的博客
04-22 331
一、create table## 员工表 create table IF NOT EXISTS default.emp( empno int, ename string, job string, mgr int, hiredate string, sal double, comm double, deptno int ) ROW FORMAT DELIMITED FIELDS TERMINATED...
HIVE的三种建表方式
热门推荐
weixin_44988131的博客
11-01 2万+
目录一、直接建表二、as （直接使用查询结果插入到一张新表）三、like（复制表结构） 一、直接建表 中括号里面的均为可选项 CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name 　　[(col_name data_type [COMMENT col_comment], ...)] 　　[COMMENT table_comment] 　　[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...
Hive编程实践
wr456wr的博客
06-09 4977
Hive基础编程实践
大数据开发之Hive篇3-Hive数据定义语言
只是甲的博客
12-22 985
备注: Hive 版本 2.1.1 文章目录一.Hive关系模型概述1.1.Database1.2 Table1.2.1 管理表和外部表1.2.2 永久表和临时表1.3 Partition1.4 Bucket二.数据定义语言(DDL)2.1 HiveQL保留关键字2.2 Database相关DDL操作2.2.1 SHOW 命令2.2.2 DESCRIBE命令2.2.3 CREATE命令2.2.4 DROP命令2.2.5 ALTER 命令2.2.6 Use命令2.3 Table相关DDL操作2.3.1 Cre
Hive基础04、Hive建表语句详解
李知恩从零开始学Java
07-14 865
1、CREATE TABLE 创建一个指定名字的表，如果库中已有相同名的表，则抛出异常；用户可以使用 IF NOT EXISTS 选项来忽略此异常。 2、EXTERNAL 关键字可以让用户创建一个外部表（默认创建内部表）。外部表在建表的同时必须指定一个指向实际数据的路径（LOCATION），Hive在创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。...
hive 分区表与分桶表的区别
06-06
Hive是建立在Hadoop之上的数据仓库工具，支持SQL查询，用于处理大规模数据。在Hive中，分区表和分桶表是两种不同的表类型，它们的主要区别如下： 1. 存储方式不同：分区表会将数据按照指定的分区方式存储在不同的目录中，而分桶表则会将数据按照指定的桶数和桶列存储在同一个目录中。 2. 查询效率不同：由于分区表将数据划分到不同的目录中，查询时只需要扫描相关分区的数据，因此查询效率相对较高；而分桶表则需要根据桶数和桶列进行哈希计算，查询时需要读取整个桶，因此查询效率相对较低。 3. 数据倾斜问题不同：分桶表可以有效地处理数据倾斜问题，将数据均匀地分布在不同的桶中，而分区表则无法处理数据倾斜问题。 综上所述，分区表和分桶表都有各自的优缺点，需要根据具体的场景选择合适的表类型。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值