目录
1 分区表
2 外部表
3 ARRAY类型
4 MAP类型
5 STRUCT类型
6 动态分区
7 表的生命周期管理
7.1创建临时表
7.2 设置表的生存时间(TTL)
8 存储格式优化
8.1 ORC格式
8.2 Parquet格式
9 实战案例
如果觉得本文对你有帮助,想进一步学习SQL语言这门艺术的,那么不妨也可以选择去看看我的博客专栏 ,部分内容如下:
数字化建设通关指南
专栏 原价99,现在活动价59.9,按照阶梯式增长,直到恢复原价。
1 分区表
分区表可以提高查询效率
CREATE TABLE partition_table (id INT,name STRING
)
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',';
2 外部表
外部表适用于数据已存在于HDFS的情况:
CREATE EXTERNAL TABLE external_table (id INT,name STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LOCATION '/user/hive/warehouse/external_table';
3 ARRAY类型
CREATE TABLE employees (name STRING,skills ARRAY<STRING>
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY ':';
4 MAP类型
CREATE TABLE user_attributes (user_id INT,attributes MAP<STRING, STRING>
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY ':'
MAP KEYS TERMINATED BY '#';
5 STRUCT类型
CREATE TABLE complex_types (id INT,contact STRUCT<phone:STRING, email:STRING>
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY ':';
6 动态分区
动态分区允许在插入数据时自动创建分区,非常适合处理大量分区的场景。
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;CREATE TABLE sales (id INT,amount DOUBLE,date STRING
)
PARTITIONED BY (year INT, month INT);INSERT OVERWRITE TABLE sales
PARTITION(year, month)
SELECT id, amount, date, YEAR(date) as year, MONTH(date) as month
FROM raw_sales;
7 表的生命周期管理
7.1创建临时表
临时表只在当前会话中有效,会话结束后自动删除。
CREATE TEMPORARY TABLE temp_stats (metric STRING,value DOUBLE
);
7.2 设置表的生存时间(TTL)
CREATE TABLE expiring_logs (log_time TIMESTAMP,event STRING
)
TBLPROPERTIES ('transient_lastDdlTime'='1635724800');ALTER TABLE expiring_logs
SET TBLPROPERTIES ('lifetime'='30d');
8 存储格式优化
8.1 ORC格式
CREATE TABLE orc_table (id INT,name STRING
)
STORED AS ORC
TBLPROPERTIES ("orc.compress"="SNAPPY");
8.2 Parquet格式
CREATE TABLE parquet_table (id INT,name STRING
)
STORED AS PARQUET;
9 实战案例
假设我们需要设计一个高效的日志分析系统,可以这样构建表结构:
-- 创建原始日志表
CREATE EXTERNAL TABLE raw_logs (log_time TIMESTAMP,user_id STRING,ip STRING,action STRING,details STRING
)
PARTITIONED BY (date STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE
LOCATION '/data/raw_logs';-- 创建优化后的分析表
CREATE TABLE analyzed_logs (log_hour TIMESTAMP,user_id STRING,action STRING,action_count INT
)
PARTITIONED BY (date STRING)
CLUSTERED BY (user_id) INTO 32 BUCKETS
STORED AS ORC
TBLPROPERTIES ("orc.compress"="SNAPPY");-- 使用动态分区插入数据
INSERT OVERWRITE TABLE analyzed_logs
PARTITION (date)
SELECT FLOOR(log_time TO HOUR) as log_hour,user_id,action,COUNT(*) as action_count,TO_DATE(log_time) as date
FROM raw_logs
GROUP BY FLOOR(log_time TO HOUR),user_id,action,TO_DATE(log_time);
这个设计充分利用了分区、分桶和列式存储的优势,可以高效地支持各种分析查询。
如果觉得本文对你有帮助,想进一步学习SQL语言这门艺术的,那么不妨也可以选择去看看我的博客专栏 ,部分内容如下:
数字化建设通关指南
专栏 原价99,现在活动价59.9,按照阶梯式增长,直到恢复原价。
具体专栏链接如下:
数字化建设通关指南_莫叫石榴姐的博客-CSDN博客