Hive partition table

2024-07-12

1. Partition table:

It is a type of table in Hive. By dividing the data in the table into multiple subsets (partitions), each partition corresponds to a specific column value in the table, which can improve query performance and data management efficiency. Each partition of the partition table is stored in a separate directory, and the definition of the partition is based on one or more columns in the table. The main purpose of using a partition table is to reduce the amount of data scanned by the query, thereby improving query efficiency.

If the partitions are too fine, a large number of small files may be generated, affecting the performance of HDFS and the efficiency of MapReduce tasks. Small files need to be merged regularly.


CREATE TABLE customer_data (
  customer_id STRING,
  name STRING,
  age INT,
  email STRING
)
PARTITIONED BY (city STRING)
STORED AS ORC;
 
select *
from customer_data;
 
-- 插入 New York 的数据
INSERT INTO TABLE customer_data PARTITION (city='New York')
VALUES
('1', 'John Doe', 30, '[email protected]'),
('2', 'Jane Smith', 25, '[email protected]'),
('3', 'Bob Johnson', 40, '[email protected]');
 
-- 插入 Los Angeles 的数据
INSERT INTO TABLE customer_data PARTITION (city='Los Angeles')
VALUES
('4', 'Alice Brown', 32, '[email protected]'),
('5', 'Charlie Davis', 28, '[email protected]');
 
-- 插入 Chicago 的数据
INSERT INTO TABLE customer_data PARTITION (city='Chicago')
VALUES
('6', 'Eve White', 45, '[email protected]'),
('7', 'Frank Black', 37, '[email protected]');

You can see that three directories are created on HDFS, corresponding to the three partitions. When you use the select with where condition to query, you can directly search for data in the corresponding partition directory, thereby reducing the amount of data scanned by the query and improving performance.

SELECT * FROM customer_data WHERE city='New York';

2. Bucket table:

It is another table type in Hive. By hashing the data in the table, query performance can be further improved, especially when performing join and aggregation operations. Bucket tables divide data into a fixed number of buckets, each of which is stored in a separate file.
---------------------------------------------------Characteristics of bucket table---------------------------------------------------
Data partitioning: Distribute data into a fixed number of buckets based on the hash values of one or more columns.
File storage: The data of each bucket is stored in a separate file.
Uniform distribution: Ideally, data is evenly distributed across all buckets, which improves query performance.


CREATE TABLE customer_data2 (
  customer_id STRING,
  name STRING,
  age INT,
  email STRING
)
CLUSTERED BY (customer_id) INTO 4 BUCKETS
STORED AS ORC;
-- 插入数据到分桶表
--通过这些步骤，我们创建了一个按 customer_id 列进行分桶的 Hive 表 customer_data，并插入了具体的数据。
INSERT INTO TABLE customer_data2 VALUES
('1', 'John Doe', 30, '[email protected]'),
('2', 'Jane Smith', 25, '[email protected]'),
('3', 'Bob Johnson', 40, '[email protected]'),
('4', 'Alice Brown', 32, '[email protected]'),
('5', 'Charlie Davis', 28, '[email protected]'),
('6', 'Eve White', 45, '[email protected]'),
('7', 'Frank Black', 37, '[email protected]'),
('8', 'Grace Green', 22, '[email protected]');
 
select *
from customer_data2;

By looking at the path on hdfs, we can see that the data will be divided into different buckets according to the hash value of the corresponding column.

Technology Sharing