数仓分区策略与粒度设计

-- 创建分桶表CREATE TABLE user_behavior_bucketed (    user_id STRING,    event_type STRING,    event_time TIMESTAMP,    page_url STRING,    session_id STRING)PARTITIONED BY (dt STRING)CLUSTERED BY (user_id) INTO 32 BUCKETSSTORED AS ORC;
-- 启用分桶SET hive.enforce.bucketing = true;SET hive.exec.dynamic.partition.mode = nonstrict;
-- 插入数据到分桶表INSERT INTO user_behavior_bucketed PARTITION(dt='2024-01-15')SELECT user_id, event_type, event_time, page_url, session_idFROM raw_user_behaviorWHERE to_date(event_time) = '2024-01-15';

-- Hive表创建示例CREATE TABLE user_behavior_daily (    user_id STRING,    event_type STRING,    event_time TIMESTAMP,    page_url STRING,    session_id STRING)PARTITIONED BY (dt STRING)STORED AS ORCLOCATION '/warehouse/user_behavior_daily/';
-- 数据插入INSERT INTO user_behavior_daily PARTITION(dt='2024-01-15')SELECT user_id, event_type, event_time, page_url, session_idFROM raw_user_behaviorWHERE DATE(event_time) = '2024-01-15';

-- 月分区表设计CREATE TABLE sales_monthly (    order_id STRING,    customer_id STRING,    product_id STRING,    amount DECIMAL(10,2),    order_date DATE)PARTITIONED BY (year_month STRING)STORED AS ORC;
-- 数据处理逻辑INSERT INTO sales_monthly PARTITION(year_month='2024-01')SELECT order_id, customer_id, product_id, amount, order_dateFROM raw_ordersWHERE date_format(order_date, 'yyyy-MM') = '2024-01';

-- 年分区表设计CREATE TABLE customer_archive_yearly (    customer_id STRING,    registration_date DATE,    last_login_date DATE,    total_orders INT,    total_amount DECIMAL(12,2))PARTITIONED BY (year STRING)STORED AS ORC;

-- 增量数据处理CREATE TABLE order_incremental (    order_id STRING,    customer_id STRING,    order_amount DECIMAL(10,2),    create_time TIMESTAMP,    update_time TIMESTAMP)PARTITIONED BY (dt STRING)STORED AS ORC;
-- ETL处理逻辑INSERT INTO order_incremental PARTITION(dt='${today}')SELECT order_id, customer_id, order_amount, create_time, update_timeFROM source_ordersWHERE to_date(create_time) = '${today}'   OR to_date(update_time) = '${today}';

-- 全量快照表CREATE TABLE customer_snapshot (    customer_id STRING,    customer_name STRING,    registration_date DATE,    status STRING,    total_orders INT,    total_amount DECIMAL(12,2),    last_order_date DATE)PARTITIONED BY (snapshot_date STRING)STORED AS ORC;
-- 全量数据生成INSERT OVERWRITE TABLE customer_snapshot PARTITION(snapshot_date='2024-01-15')SELECT     customer_id,    customer_name,    registration_date,    status,    COUNT(order_id) as total_orders,    SUM(order_amount) as total_amount,    MAX(order_date) as last_order_dateFROM customer_base cLEFT JOIN order_history o ON c.customer_id = o.customer_idWHERE o.order_date <= '2024-01-15' OR o.order_date IS NULLGROUP BY customer_id, customer_name, registration_date, status;

-- 拉链表结构设计CREATE TABLE customer_zipper (    customer_id STRING,    customer_name STRING,    phone STRING,    email STRING,    address STRING,    start_date DATE,    end_date DATE,    is_current BOOLEAN)PARTITIONED BY (dt STRING)STORED AS ORC;

-- 拉链表更新逻辑-- 1. 处理变更数据WITH changed_customers AS (    SELECT         customer_id,        customer_name,        phone,        email,        address,        '2024-01-15' as start_date,        '9999-12-31' as end_date,        true as is_current    FROM source_customer_changes    WHERE change_date = '2024-01-15'),
-- 2. 关闭历史记录updated_history AS (    SELECT         customer_id,        customer_name,        phone,        email,        address,        start_date,        CASE             WHEN customer_id IN (SELECT customer_id FROM changed_customers)            THEN '2024-01-14'            ELSE end_date        END as end_date,        CASE             WHEN customer_id IN (SELECT customer_id FROM changed_customers)            THEN false            ELSE is_current        END as is_current    FROM customer_zipper    WHERE dt = '2024-01-14')
-- 3. 合并数据INSERT INTO customer_zipper PARTITION(dt='2024-01-15')SELECT * FROM updated_historyUNION ALLSELECT * FROM changed_customers;

-- 好的分区键设计CREATE TABLE sales_optimized (    order_id STRING,    customer_id STRING,    product_id STRING,    amount DECIMAL(10,2),    order_date DATE,    region STRING)PARTITIONED BY (    year STRING,    month STRING,    region STRING)CLUSTERED BY (customer_id) INTO 32 BUCKETSSTORED AS ORC;

-- 利用分区剪枝的查询SELECT customer_id, SUM(amount) as total_amountFROM sales_optimizedWHERE year = '2024'   AND month IN ('01', '02', '03')  AND region = 'North'GROUP BY customer_id;

-- 优化1：使用动态分区减少DDL操作SET hive.exec.dynamic.partition = true;SET hive.exec.dynamic.partition.mode = nonstrict;
-- 优化2：调整并发参数SET hive.exec.parallel = true;SET hive.exec.parallel.thread.number = 8;
-- 优化3：使用小文件合并SET hive.merge.mapfiles = true;SET hive.merge.mapredfiles = true;SET hive.merge.size.per.task = 256000000;SET hive.merge.smallfiles.avgsize = 16000000;
-- 优化4：启用向量化执行SET hive.vectorized.execution.enabled = true;SET hive.vectorized.execution.reduce.enabled = true;

-- ORC格式优化CREATE TABLE user_behavior_optimized (    user_id STRING,    event_type STRING,    event_time TIMESTAMP,    page_url STRING,    session_id STRING,    device_type STRING,    browser STRING)PARTITIONED BY (dt STRING)STORED AS ORCTBLPROPERTIES (    'orc.compress'='SNAPPY',    'orc.stripe.size'='134217728');