首页 > 其他分享 > hive(四)

hive(四)

时间:2023-05-21 18:55:08浏览次数:40  
标签:-- 分区 hive grade table txt 数据

内部表和外部表

默认情况下创建的表就是内部表,Hive拥有该表的结构和文件。换句话说,Hive完全管理表(元数据和数据)的生命周期,类似于RDBMS中的表。当你删除内部表时,它会删除数据以及表的元数据。可以使用DESCRIBE FORMATTED tablename,来获取表的元数据描述信息,从中可以看出表的类型。

image
外部表(External table )中的数据不是Hive拥有或管理的,只管理表元数据的生命周期。要创建一个外部表,需要使用EXTERNAL语法关键字。删除外部表只会删除元数据,而不会删除实际数据。在Hive外部仍然可以访问实际数据。实际场景中,外部表搭配location语法指定数据的路径,可以让数据更安全。

主要差异

  • 无论内部表还是外部表,Hive都在Hive Metastore中管理表定义、字段类型等元数据信息。
  • 删除内部表时,除了会从Metastore中删除表元数据,还会从HDFS中删除其所有数据文件。
  • 删除外部表时,只会从Metastore中删除表的元数据,并保持HDFS位置中的实际数据不变。
-- 创建内部表 加载数据
create table t_user_inner(
    id int,
    uname string,
    pwd string,
    sex string,
    age int
)row format delimited fields terminated by ',';
load data local inpath '/root/user.txt' into table t_user_inner;
-- 查看表信息
desc formatted t_user_inner ;
-- 创建外部表 加载数据
create external table t_user_ext(
         id int,
         uname string,
         pwd string,
         sex string,
         age int
)row format delimited fields terminated by ',';
load data local inpath '/root/user.txt' into table t_user_ext;
-- 查看表信息
desc formatted table t_user_ext;


-- 删除内部表  数据被删除了
drop  table t_user_inner;
-- 删除外部表  数据并没有被删除
drop  table t_user_ext;

-- 再次重新创建 t_user_ext 可以直接查询数据
select * from  t_user_ext;

-- 将t_user_ext 转换为内部表
alter table t_user_ext set tblproperties('EXTERNAL'='FALSE'); -- 要求KV的大小写
-- 查询表信息发现 Table Type: MANAGED_TABLE
desc formatted t_user_ext; 
--将t_user_ext 转换为外部表
alter table t_user_ext set tblproperties('EXTERNAL'='true');
-- 查询表信息发现 Table Type:EXTERNAL_TABLE
desc formatted t_user_ext;

分区表

分区表实际上就是将表中的数据以某种维度进行划分文件夹管理 ,当要查询数据的时候,根据维度直接加载对应文件夹下的数据! 不用加载整张表所有的数据再进行过滤, 从而提升处理数据的效率!

比如在一个学生表中想查询某一个年级的学生,如果不分区,需要在整个数据文件中全表扫描,但是分区后只需要查询对应分区的文件即可.

image

静态分区

所谓静态分区指的是分区的属性值是由用户在加载数据的时候手动指定的

1.创建单分区表:

-- 创建学生表 分区字段为年级grade
CREATE TABLE  t_student (
    sid int,
    sname string
 ) partitioned by(grade int)   -- 指定分区字段
row format delimited fields terminated by ',';
-- 注意∶分区字段不能是表中已经存在的字段,因为分区字段最终也会以虚拟字段的形式显示在表结构上。
select * from t_student;
+----------------+------------------+------------------+
| t_student.sid  | t_student.sname  | t_student.grade  |
+----------------+------------------+------------------+
+----------------+------------------+------------------+
stu01.txt
1,zhangsan,1
2,lisi,1
3,wangwu,1

stu02.txt
4,zhaoliu,2
5,lvqi,2
6,maba,2

stu03.txt
7,liuyan,3
8,tangyan,3
9,jinlian,3
-- 静态分区需要用户手动加载数据 并指定分区
load  data local  inpath '/root/stu01.txt' into table t_student partition(grade=1);
load  data local  inpath '/root/stu02.txt' into table t_student partition(grade=2);
load  data local  inpath '/root/stu03.txt' into table t_student partition(grade=3);
-- 查询
select * from t_student where grade=1;
+----------------+------------------+------------------+
| t_student.sid  | t_student.sname  | t_student.grade  |
+----------------+------------------+------------------+
| 1              | zhangsan         | 1                |
| 2              | lisi             | 1                |
| 3              | wangwu           | 1                |
+----------------+------------------+------------------+

image

注意:文件中的数据放入到哪个分区下就属于当前分区的数据,即使数据有误,也会按照当前分区处理

stu03.txt
7,liuyan,3
8,tangyan,3
9,jinlian,3
10.aaa,4

load  data local  inpath '/root/stu03.txt' overwrite into table t_student partition(grade=3);

select * from t_student where grade=3;
-- 最后一条记录虽然写的是4 但是 放到了年级3分区下 效果也是年级3
+----------------+------------------+------------------+
| t_student.sid  | t_student.sname  | t_student.grade  |
+----------------+------------------+------------------+
| 7              | liuyan           | 3                |
| 8              | tangyan          | 3                |
| 9              | jinlian          | 3                |
| 10             | aaa              | 3                |
+----------------+------------------+------------------+

2.创建多分区表

-- 创建学生表 分区字段为年级grade 班级clazz
CREATE TABLE  t_student02 (
    sid int,
    sname string
 ) partitioned by(grade int,clazz int)   -- 指定分区字段
row format delimited fields terminated by ',';
1年级1班
stu0101.txt  
1,zhangsan,1,1
2,lisi,1,1

1年级2班
stu0102.txt
3,wangwu,1,2

2年级1班
stu0201.txt
4,zhangsan,2,1
5,lisi,2,1
6,maba,2,1

3年级1班
stu0301.txt
7,liuyan,3,1
8,tangyan,3,1
3年级2班
9,dalang,3,2
10,jinlian,3,2
load  data local  inpath '/root/stu0101.txt' into table t_student02 partition(grade=1,clazz=1);
load  data local  inpath '/root/stu0102.txt' into table t_student02 partition(grade=1,clazz=2);
load  data local  inpath '/root/stu0201.txt' into table t_student02 partition(grade=2,clazz=1);
load  data local  inpath '/root/stu0301.txt' into table t_student02 partition(grade=3,clazz=1);
load  data local  inpath '/root/stu0302.txt' into table t_student02 partition(grade=3,clazz=2);

select * from t_student02 where grade=1 and clazz=2;
+------------------+--------------------+--------------------+--------------------+
| t_student02.sid  | t_student02.sname  | t_student02.grade  | t_student02.clazz  |
+------------------+--------------------+--------------------+--------------------+
| 7                | liuyan             | 3                  | 1                  |
| 8                | tangyan            | 3                  | 1                  |
+------------------+--------------------+--------------------+--------------------+

image

注意:我们既然建立了分区,就要保证分区查询的的命中率,查询尽量使用设置的分区字段去查询.分区虽然避免了全表扫描,但是也可能会产生大量的小文件,有利有弊.

3.分区其他操作(了解)

-- 查看分区 
show partitions t_student02;
-- 添加分区
alter table t_student02 add  partition (grade=4,clazz=1);
-- 删除分区
alter table t_student02 drop  partition (grade=4,clazz=1);

动态分区

静态分区与动态分区的主要区别在于静态分区是手动指定,而动态分区是通过数据来进行判断.

详细来说:静态分区需要我们自己手动load并指定分区,如果数据很多,那么是麻烦了.而动态分区指的是分区的字段值是基于查询结果(参数位置)自动推断出来的。核心语法就是insert+seclect。

开启动态分区首先要在hive会话中设置如下的参数

-- 临时设置 重新连接需要重新设置
set hive.exec.dynamic.partition=true; 
set hive.exec.dynamic.partition.mode=nonstrict;

其余参数配置如下:

设置为true表示开启动态分区的功能(默认为false) 
--hive.exec.dynamic.partition=true; 

设置为nonstrict,表示允许所有分区都是动态的(默认为strict) 严格模式至少有一个静态分区
-- hive.exec.dynamic.partition.mode=nonstrict; 

每个mapper或reducer可以创建的最大动态分区个数(默认为100) 
比如:源数据中包含了一年的数据,即day字段有365个值,那么该参数就需要设置成大于365,
如果使用默认 值100,则会报错 
--hive.exec.max.dynamic.partition.pernode=100; 

一个动态分区创建可以创建的最大动态分区个数(默认值1000) 
--hive.exec.max.dynamic.partitions=1000; 

全局可以创建的最大文件个数(默认值100000) 
--hive.exec.max.created.files=100000; 

当有空分区产生时,是否抛出异常(默认false) 
-- hive.error.on.empty.partition=false;

操作步骤

  1. 创建文件并上传
  2. 创建外部表指向文件(相当于临时表)
  3. 创建动态分区表
  4. 查询外部表将数据动态存入分区表中
创建文件并上传

student.txt  

1,zhangsan,1,1
2,lisi,1,1
3,wangwu,1,2
4,zhangsan,2,1
5,lisi,2,1
6,maba,2,1
7,liuyan,3,1
8,tangyan,3,1
9,dalang,3,2
10,jinlian,3,2

-- 将文件上传到hdfs根目录
hdfs dfs -put student.txt  /stu
创建外部表指向文件(相当于临时表)  
create external table t_stu_e(
    sid int,
    sname string,
    grade int,
    clazz int
)row format delimited fields terminated by ","
location "/stu";
创建动态分区表
create  table  t_stu_d(
    sid int,
    sname string
)partitioned by (grade int,clazz int)
row format delimited fields terminated by ",";

查询外部表将数据动态存入分区表中
insert overwrite table t_stu_d partition (grade,clazz) select  *  from t_stu_e ;

select * from t_stu_d;

image

分桶表

概述

分桶表也叫做桶表,叫法源自建表语法中bucket单词,是一种用于优化查询而设计的表类型。

分区提供一个隔离数据和优化查询的便利方式。不过,并非所有的数据集都可形成合理的分区。不合理的数据分区划分方式可能导致有的分区数据过多,而某些分区没有什么数据的尴尬情况 。分桶是将数据集分解为更容易管理的若干部分的另一种技术。
image
对Hive(Inceptor)表分桶可以将表中记录按分桶键(字段)的哈希值分散进多个文件中,这些小文件称为桶。桶以文件为单位管理数据!分区针对的是数据的存储路径;分桶针对的是数据文件。

分桶的原理

Hive采用对列值哈希,然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。

bucket num = hash_function(bucketing_column) mod   num_buckets
 分隔编号      哈希方法(分桶字段)              取模   分桶的个数

image

分桶的好处

  1. 基于分桶字段查询时,减少全表扫描.

  2. 根据join的字段对表进行分桶操作,join时可以提高MR程序效率,减少笛卡尔积数量.

image

  1. 分桶表数据进行高效抽样.数据量大时,使用抽样数据估计和推断整体特性.

分桶表的创建

1.准备person.txt上传到hdfs
2.创建外部表指向person.txt
3.创建分桶表
4.查询外部表将数据加载到分桶表中
person.txt 
public class Test02 {
    public static void main(String[] args) {
        for (int i = 1; i <= 10000; i++) {
            System.out.println(i + "," + "liuyan" + (new Random().nextInt(10000) + 10000));
        }
    }
}

 hdfs dfs -mkdir /person
hdfs dfs -put person.txt /person
2.创建外部表指向person.txt
create external table  t_person_e(
     id int,
     pname string
) row format delimited fields terminated by ","
    location "/person";

select  * from t_person_e;
create table  t_person(
    id int,
    pname string
)clustered by(id) sorted by (pname) into 24 buckets
row format delimited fields terminated by ",";
insert overwrite table t_person select * from t_person_e ;

image

桶表抽样

-- tablesample是抽样语句,语法:TABLESAMPLE(BUCKET x OUT OF y) 
-- x表示从哪个bucket开始抽取。
   例如,table总bucket数为32,tablesample(bucket 3 out of 16)
   32 / 16  = 2  代表16桶为一组  抽取 第一组的第3桶  抽取第二组的第3桶 也就是第19桶
-- y必须是table总bucket数的倍数或者因子。hive根据y的大小,决定抽样的比例。
    tablesample(bucket 3 out of 64)
    32/64 = 2分之一      64桶为一组  不够一组 取第三桶的 前百分之50
    
    select * from t_person tablesample(bucket 4 out of 12); 
    24/12 抽取2桶数据      12桶一组 抽取 第一组第4桶 第二组 第4桶 4+12 =16桶

标签:--,分区,hive,grade,table,txt,数据
From: https://www.cnblogs.com/paopaoT/p/17418986.html

相关文章

  • sqoop导数到hive任务状态一直是Accepted或Running
    昨天晚上装了sqoop准备将数据从pg库导入Hive库备用,写了个sqoop脚本,运行脚本本后从yarnui上看任务状态一直Accepted,卡了三四个小时,最后发现是yarn-site.xml配置问题,给的资源太少,无法运行任务。在yarn-site.xml中添加下面的内容:<property><name>ya......
  • hive(三)
    HQL基础语法Hive中的语句叫做HQL语句,是一种类似SQL的语句,基本上和SQL相同但是某些地方也是有很大的区别.数据库操作创建数据库1.创建一个数据库,数据库在HDFS上的默认存储路径是/hive/warehouse/*.db。createdatabasehive01;避免要创建的数据库已经存在错误,增加ifn......
  • 二、Hive平台安装
    解压文件:tar-zxvfapache-hive-2.0.0-bin.tar.gz重命名:mv/usr/local/src/apache-hive-2.0.0-bin/usr/local/src/hive卸载MariaDB数据库:rpm-qa|grepmariadbrpm-e--nodepsmariadb-libs-5.5.56-2.el7.x86_64安装mysql数据库:rpm-ivhmysql-community-common-5.7.......
  • Hive学习之抽样(Sampling)
    当数据量特别大时,对全体数据进行处理存在困难时,抽样就显得尤其重要了。抽样可以从被抽取的数据中估计和推断出整体的特性,是科学实验、质量检验、社会调查普遍采用的一种经济有效的工作和研究方法。    Hive支持桶表抽样和块抽样,下面分别学习。所谓桶表指的是在创建表时使用......
  • 大数据Spark “蘑菇云”行动第93课:Hive中的内置函数、UDF、UDAF实战
     大数据Spark“蘑菇云”行动第93课:Hive中的内置函数、UDF、UDAF实战selectsum_all(age)from...hive>usedefault;showtables;select*fromemployeeforhaving;一:udf编码importorg.apache.hadoop.hive.ql.exec.UDF;importorg.apache.hadoop.io.Text;pub......
  • 第92课作业,通过SerDes的方式对一下数据进行Hive的存储和查询操作
     第92课作业,通过SerDes的方式对一下数据进行Hive的存储和查询操作: 0^^Hadoop^^America^^5000|8000|12000|level8^^male1^^Spark^^America^^8000|10000|15000|level9^^famale2^^Flink^^America^^7000|8000|13000|level10^^male3^^Hadoop^^America^^9000|11000|12000|level10^^f......
  • hive(一)
    数据仓库数据仓库,英文名称为DataWarehouse,可简写为DW。是一个用于存储,分析,报告的数据系统.数据仓库的目的是构建面向分析的集成化数据环境,分析结果为企业提供决策支持.数据库和数据仓库区别数据库和数据仓库的区别实际就是OLTP和OLAP的区别OLTP系统的典型应用就是RDBMS,也......
  • HIVE跨集群迁移
    查看mysql使用端口ps-ef|grepmysqlss-antp|grep[ps查出来的pid]停止HIVE写入服务创建备份路径mkdir-p/root/hivebackup/执行备份数据库命令:(在目标集群执行)mysqldump-uroot-pPassword-h1.1.1.1-P3306--databaseshive_prode>/root/jws/hiveba......
  • impala jdbc导出hive数据字典
    业务需求太多了,给完整导出为html文件,以及之前搞的publicstaticvoidmain(String[]args)throwsException{kerberos();}publicstaticvoidkerberos(){URLresource=Thread.currentThread().getContextClassLoader().getResource("");......
  • 1、通过亿级数据量在hive和impala中查询比较text、orc和parquet性能表现(二)
    文章目录9、分别在hive和impala中查询验证结果(比較HDFS存儲三種格式文件的查詢性能textfile、orc、parquet)1)、查詢總條數2)、隨便找一條信息,按照name查詢3)、按照多条件查询4)、按照時間區間查詢5)、兩張表join6)、總結1、文件存儲2、hive查詢與impala查詢速度3、不同查詢類型的查詢......