Hive 是一个构建在Hadoop之上的数据仓库和分析工具,她提供了一种类SQL的查询语言--HiveQL, 用于将结构化的查询SQL 转换为MapReduce 任务和Tez任务, 通过Hadoop的分布式计算能力来执行查询任务。同步hive 可以将hdfs文件中的数据,同步到外表中,进行数据分析。
hive表结构定义:
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name (
column1 data_type,
column2 data_type,
...
)
[COMMENT 'table_comment']
[PARTITIONED BY (partition_column data_type, ...)]
[CLUSTERED BY (col_name, col_name, ...)
[SORTED BY (col_name [ASC|DESC], ...)]
INTO num_buckets BUCKETS]
[ROW FORMAT row_format]
[STORED AS file_format]
[TBLPROPERTIES (property_name=property_value, ...)]
[LOCATION 'hdfs_path'];
Hive 使用HiveQL的建表语句,DDL 来定义表结构。DDL可以用于创建、修改、删除Hive表的元数据,包括表名、列定义、分区定义、存储格式等。 这里的发部分语法与标准的SQL建表语句还是相同的。
- EXTERNAL:可选项, 用于创建外表
- IF NOT EXISTS : 可选项,如果表已存在,就不创建。 与标准SQL用法相同
- table_name :指定要创建的表名称
- column1, column2 : 表的列名,以及列的数据类型
- COMMENT "table_commend":可选项,用于注释表的含义
- PARTITIONED BY (partition_column data_type, ......): 可选项,定义分区列和对应的数据类型
- CLUSTERED BY (column1, column2, ......) : 可选项,指定聚簇列
- STORTED BY(olumn1 [ASC] [DESC] , .....) : 可选项, 指定排序规则
- INTO num_buckets BUCKETS: 可选项,指定聚簇表的分桶数,更细粒度的划分。
- ROW FROM row_format:可选项, 定义行格式( delimited、serde)
- DELIMITED:表示使用默认的LazySimpleSerDe类来处理数据,一般用于用分隔符分隔的文本文,默认使用native Serde
- SERDE: Serializer/Deserializer的简写,用于对象的序列化和反序列化。Hive使用SERDE读取和写入行对象,读取就是hdfs文件反序列化成对象,写入就是对象序列化存储hdfs
- STORED AS file_format:可选项,定义存储格式。底层数据格式的转换是依赖SERDE
- TextFile: 默认格式,以文本格式存储
- SequenceFile: 存储为压缩的二进制序列化文件
- RcFile:存储为二进制压缩文件,效率高、占用空间小、查询快,但不能可视化数据、导入数据耗时长、不能使用load加载数据
- ORC: 是RcFile的改进,在压缩编码、查询性能上做了改进
- Parquet:相比于ORC,支持对嵌套结构的存储,如JSON结构,但不支持update、ACID、数据可视化
- LOCATION 'hdfs_path' : 可选项, 指定表的hdfs路径
- TBLPROPERTIES( property_name=property_value): 可选项, 定义表级属性 。可以通过它来定义上面我们提到的所有属性, 如 TBLPROPERTIES('EXTERNAL'='TRUE', 'LOCATION'='/data/hdfs/data',......);
hive数据类型:
- 基本数据类型
- 整数类型: TINYINT、SMALLINT、INT、BIGINT
- 浮点类型:FLOAT、DOUBLE
- 布尔类型:BOOLEAN
- 字符串类型:STRING
- 日期类型:DATE、TIMESTAMP
- 复杂数据类型
- 数组类型:ARRAY<data_type>
- 结构体类型:STRUCT< col_name : data_type [ comment 'col_comment' ] , ........ >
- Map类型:MAP<key_type, value_type>
- 其它特殊类型
- NULL 类型: NULL
- 二进制类型:BINARY
- Decimal类型:DECIMAL(precision, scale)
- VARCHAR类型: VARCHAR (length)
- CHAR 类型: CHAR (length)
hive外部表、管理表:
外部表(External Table):
它和数据存储(如HDFS)中的文件并没有直接的关联。Hive仅通过指定文件的位置来了解数据的结构。
- 外部表的数据文件可以在HDFS或本地文件系统中。
- 删除外部表不会删除底层数据文件。
- 外部表的数据可以由外部系统进行加载,这意味着可以直接在外部系统中更新数据文件,而Hive可以直接读取最新的数据。
- 外部表可以跨多个Hive实例共享。
管理表(Managed Table):
管理表是在Hive中定义的表,它的数据和表定义都由Hive管理。
- 管理表的数据文件默认存储在Hive的数据仓库中。
- 删除管理表会连同底层数据一起删除,因为数据是由Hive管理的。
- 管理表的数据只能由Hive进行加载和更新。
对于外部表,可以使用LOAD DATA
语句来加载数据,而对于管理表,可以使用常规的INSERT INTO
语句来插入数据。
hive分区、分桶:
分区:
分区是把一个表的数据以分区字段的值作为目录去存储。可以缩小磁盘数据扫描范围,减少IO; 将数据存储在多个分区目录下,便于地利管理数据。
- 静态分区:根据PARTITIONED BY 创建分区表,指定分区字段,分区字段不存放实际的数据,仅仅是分区的表示。
- 单分区: 按照一个列的值进行分区, 管理和查询相对简单,适用于简单数据模型
- 多分区: 按照多个列的值进行分区,可以提供更灵活的查询选项
- 动态分区: 执行时才知道分区的值,相比于静态分区,可以一次写入多个分区数据,而不用在每次分区写入的时候执行多次insert。
- 在INSERT … SELECT …查询中,必须在SELECT语句中的列中最后指定动态分区列,并按PARTITION()子句中出现的顺序进行排列
- 如果动态分区和静态分区一起使用,必须是静态分区的字段在前,动态分区的字段在后
静态分区语法:
#分区表创建
#单分区表创建
CREATE TABLE tmp_xx(id int,name String) partitioned by (d string);
#多分区表创建
CREATE TABLE tmp_xx(id int,name String) partitioned by (d String,h String);
#添加分区
ALTER TABLE tmp_partition ADD IF NOT EXISTS PARTITION (d='20220628')
#删除分区
ALTER TABLE tmp_partition DROP IF EXISTS PARTITION (d='20220628')
#数据写入
#单分区数据写入
INSERT OVERWRITE TABLE tmp_xx PARTITION (d='20220629')
SELECT id, name FROM tmp_yy limit 10;
#多分区数据写入
INSERT OVERWRITE TABLE tmp_xx PARTITION (d='20220629',h='15')
SELECT id, name FROM tmp_yy limit 10;
#查看分区数据
#单分区
select * from tmp_xx where d='20220629'
#多分区
select * from tmp_xx where d='20220629' and h='15'
#查看表分区
show partitions table;
#查看目录
hadoop dfs -du -h /user/hive/warehouse/tmp_xxx
动态分区语法:
1、使用动态分区需要hive开启动态分区:
-- 开启动态分区 默认为false,不开启
set hive.exec.dynamic.partition=true;
-- 指定动态分区模式,默认为strict
set hive.exec.dynamic.partition.mode=nonstrict;
# 下面参数可选
SET hive.exec.max.dynamic.partitions=2048;
SET hive.exec.max.dynamic.partitions.pernode=256;
SET hive.exec.max.created.files=10000;
SET hive.error.on.empty.partition=true;
2、语法:
#写入数据
INSERT overwrite TABLE tmp_partition PARTITION(d)
SELECT id,NAME,d FROM tmp_xxx
#写入多分区数据
INSERT overwrite TABLE tmp_partition PARTITION(d,h)
SELECT id,NAME,d,h FROM tmp_xxx
#混合分区使用,使用动态分区和静态分区,静态分区必须在前
INSERT overwrite TABLE tmp_partition PARTITION(d='20220629',h)
SELECT id,NAME,h FROM tmp_xxx
分桶:
相比于分区表, 分桶表是将数据进行更加细粒度的划分, 一般使用较少,在数据量不大的情况下, 使用分桶甚至会使性能更差。
分桶表将整个数据内容按照分桶字段的哈希值进行区分,使用该哈希值除以桶的个数得到取余数,bucket_id = column.hashcode % bucket.num,余数决定了该条记录会被分在哪个桶中。余数相同的记录会分在一个桶里。需要注意的是,在物理结构上,一个桶对应一个文件,而分区表只是一个目录,至于目录下有多少数据是不确定的。
创建分桶表:
CREATE TABLE tmp_bucket(id INT,NAME STRING) clustered BY (id) INTO 4 buckets
抽样:
#建表
select columns from table tablesample(bucket x out of y on column);
-- x:表示从第几个分桶进行抽样
-- y:表示每隔几个分桶取一个分桶,y必须为表bucket的整数倍或者因子
#从分桶表的建表语句中可知,我们一共分了4个桶,所以我们这里x取1,y取2
一共抽取2(4/2)个桶,从第一个桶开始,每隔2个桶抽取一次,即第一个桶和
第三个桶。
SELECT id,NAME FROM tmp_bucket tablesample(bucket 1 OUT of 2 ON id) LIMIT 10
hive 索引
Hive中的索引是基于Hadoop分布式文件系统(HDFS)的存储布局来实现的。它在HDFS上创建了元数据文件来跟踪数据文件的位置和分区信息。
hive索引原理:
- 在指定列上创建索引,生成一张索引表(hive的物理表),记录索引列的值、该值对应的hdfs文件路径、该值对应的文件中的偏移量
- 在执行索引字段查询时,首先二外生成一个MapReduce job,根据索引列的过滤条件,从索引表中过滤出索引列的值对应的hdfs文件路径和偏移量,输出到hdfs的一个文件中,然后根据这个文件中的hdfs路径和偏移量,筛选原始input文件,生成新的split作为整个job的split,这样就避免了全表扫描。
为了方便理解,我们将Hive的索引和MySQL的索引做一下对比:
-
存储方式:
- Hive索引:Hive中的索引是基于Hadoop分布式文件系统(HDFS)的存储布局来实现的。它在HDFS上创建了元数据文件来跟踪数据文件的位置和分区信息。
- MySQL索引:MySQL的索引是基于B+树或哈希表等数据结构来实现的,用于在存储引擎层对数据进行索引操作。
-
数据类型支持:
- Hive索引:Hive的索引支持基本数据类型以及复杂数据类型的部分操作,例如数组和结构体。但是在使用复杂数据类型时,索引可能会受到限制。
- MySQL索引:MySQL的索引支持广泛的数据类型,包括整数、浮点数、字符串、日期等。
-
数据一致性:
- Hive索引:Hive的索引通常是延迟维护的,特别是在动态分区中。这意味着索引可能不会立即反映数据的变化,而是在某个时间点进行更新。
- MySQL索引:MySQL的索引在数据修改操作时会实时更新,保持数据的一致性。这使得在执行读取操作时可以获得最新的数据。
-
查询性能:
- Hive索引:Hive的索引主要用于过滤和筛选数据,可以帮助减少查询的数据量,但不能实现像MySQL那样的高效定位。
- MySQL索引:MySQL的索引可以快速定位所需数据的位置,从而提高查询的性能。
-
管理和维护:
- Hive索引:Hive的索引是由Hive自身管理和维护的。创建和删除索引需要使用Hive的DDL语句。Hive索引的创建和维护通常需要额外的操作,而且对于大型数据集和动态分区的表可能需要较长的时间。
- MySQL索引:MySQL的索引可以由开发者自己定义和管理。创建、删除和修改索引的操作相对比较简单和直接。
1、hive创建索引:
-- 创建索引
CREATE [UNIQUE] INDEX index_name
ON TABLE table_name
[PARTITIONED BY (col_name, ...)] -- 指定索引列
[index_type]
[AS index_properties]
[WITH DEFERRED REBUILD]
[STORED AS index_storage_handler]
[TBLPROPERTIES (property_name=property_value, ...)]
[COMMENT 'index_comment'];
UNIQUE
: 可选关键字,用于指定索引是否是唯一索引。PARTITIONED BY (col_name, ...): 指定索引列
index_type
: 索引类型,例如BTREE或BITMAP。这是可选的,如果未指定,默认是BTREE类型。AS index_properties
: 可选参数,用于指定索引的配置属性。这些属性通常与底层存储引擎相关,一般使用 org.apache.hadoop.hive.ql.index.compact.CompactIndexHandlerWITH DEFERRED REBUILD
: 可选参数,用于启用延迟重建索引,即当前是个空索引,还没有实际创建爱你索引。当此选项被使用时,需要手动进行重建,索引将在创建后被标记为“DEFERRED_REBUILD” 。STORED AS index_storage_handler
: 可选参数,用于指定存储索引的处理程序。例如,可以指定STORED AS RCFILE来存储索引为RCFile格式。TBLPROPERTIES (property_name=property_value, ...)
: 可选参数,用于指定额外的表级属性,这些属性通常与底层存储引擎相关。COMMENT 'index_comment'
: 可选参数,用于提供索引的注释信息。
2、生成索引
刚创建完的Hive索引表是没有数据的,需要生成索引数据
alter index 索引名称 on 表名 rebuild;
生成索引的过程可能会对系统资源产生一定的影响,特别是对于大型数据集和庞大的索引来说。因此,在生成索引时需要考虑系统的负载和可用资源。
3、查看索引
SHOW FORMATTED INDEX ON table_name;
SHOW FORMATTED INDEXES ON table_name;