保姆级教程：用Hive SQL搞定数据从MySQL到HDFS的完整ETL流程（含建库建表）

企业级数据仓库实战基于Hive SQL的MySQL到HDFS全链路ETL设计在数据驱动的商业决策时代将传统关系型数据库中的数据迁移到大数据平台进行分析已成为企业数字化转型的关键环节。本文将深入探讨如何利用Hive构建完整的ETL管道实现从MySQL到HDFS的高效数据流转涵盖从环境准备到最终数据交付的全流程技术细节。1. 环境准备与数据抽取1.1 基础设施配置在开始ETL流程前需要确保以下组件已正确部署并配置Hadoop集群版本建议2.7已配置YARN资源管理Hive环境版本3.1.0Metastore服务正常运行Sqoop/DataX数据抽取工具本文以Sqoop 1.4.7为例MySQL Connector确保Java连接器jar包已放入Sqoop的lib目录关键检查点测试HDFS的写入权限和MySQL的网络连通性避免后续步骤因权限问题中断。1.2 数据抽取策略设计针对不同数据规模采用差异化的抽取方案数据特征抽取方案适用场景全量小表(10G)直接全量导入维度表、配置表全量大表分批导入并行度调整事实表、日志表增量数据基于时间戳/水位的CDC订单、交易类流水典型Sqoop全量抽取命令示例sqoop import \ --connect jdbc:mysql://mysql-server:3306/source_db \ --username dbuser \ --password dbpass \ --table customer \ --target-dir /data/raw/customer \ --fields-terminated-by \t \ --lines-terminated-by \n \ --null-string \\N \ --null-non-string \\N \ --compress \ --compression-codec org.apache.hadoop.io.compress.SnappyCodec \ --num-mappers 42. Hive数据模型设计2.1 数据库与表结构规划在Hive中创建与源系统对应的数据库建议采用业务域划分的命名规范CREATE DATABASE IF NOT EXISTS ods COMMENT Operational Data Store for source systems LOCATION /data/warehouse/ods; CREATE DATABASE IF NOT EXISTS dw COMMENT Dimensional warehouse layer LOCATION /data/warehouse/dw;2.2 表类型选择策略根据数据使用场景合理选择表类型外部表适用于原始数据层(ODS)保留数据所有权CREATE EXTERNAL TABLE ods.customer_external ( id BIGINT COMMENT 客户ID, name STRING COMMENT 客户名称, create_time TIMESTAMP COMMENT 创建时间 ) PARTITIONED BY (dt STRING COMMENT 业务日期) ROW FORMAT DELIMITED FIELDS TERMINATED BY \t STORED AS TEXTFILE LOCATION /data/raw/customer;内部表适用于数据仓库层(DW)Hive全权管理CREATE TABLE dw.dim_customer ( customer_sk BIGINT COMMENT 代理键, natural_key STRING COMMENT 业务键, name STRING COMMENT 客户名称, scd_start DATE COMMENT 生效日期, scd_end DATE COMMENT 失效日期 ) STORED AS ORC TBLPROPERTIES (orc.compressSNAPPY);2.3 高级表特性应用分区设计示例按日期和业务单元双重分区CREATE TABLE ods.sales_detail ( order_id STRING, product_id STRING, amount DECIMAL(18,2) ) PARTITIONED BY ( dt STRING COMMENT 交易日期, region STRING COMMENT 大区编码 ) STORED AS PARQUET;分桶表示例优化JOIN性能CREATE TABLE dw.fact_order ( order_id STRING, customer_id STRING, order_date DATE ) CLUSTERED BY (customer_id) INTO 32 BUCKETS STORED AS ORC;3. 数据转换与质量管控3.1 数据清洗技术处理常见数据质量问题-- 空值处理 INSERT OVERWRITE TABLE dw.dim_product SELECT COALESCE(product_id, N/A) AS product_id, NULLIF(TRIM(product_name), ) AS product_name, CASE WHEN price 0 THEN 0 ELSE ROUND(price, 2) END AS price FROM ods.product_stage;3.2 缓慢变化维(SCD)处理Type2 SCD实现方案-- 新增记录 INSERT INTO dw.dim_customer SELECT ROW_NUMBER() OVER() max_sk.max_val AS customer_sk, s.customer_id AS natural_key, s.customer_name, CURRENT_DATE AS scd_start, CAST(9999-12-31 AS DATE) AS scd_end FROM ods.customer_new s CROSS JOIN (SELECT COALESCE(MAX(customer_sk),0) AS max_val FROM dw.dim_customer) max_sk LEFT JOIN dw.dim_customer t ON s.customer_id t.natural_key WHERE t.customer_sk IS NULL; -- 更新记录 INSERT INTO dw.dim_customer SELECT t.customer_sk, t.natural_key, s.customer_name, CURRENT_DATE AS scd_start, CAST(9999-12-31 AS DATE) AS scd_end FROM ods.customer_new s JOIN dw.dim_customer t ON s.customer_id t.natural_key WHERE t.scd_end CAST(9999-12-31 AS DATE) AND (s.customer_name t.name OR (s.customer_name IS NULL AND t.name IS NOT NULL)); UPDATE dw.dim_customer SET scd_end DATE_SUB(CURRENT_DATE, 1) WHERE natural_key IN (SELECT customer_id FROM ods.customer_new) AND scd_end CAST(9999-12-31 AS DATE) AND customer_sk NOT IN ( SELECT customer_sk FROM dw.dim_customer WHERE scd_start CURRENT_DATE );4. 性能优化与运维实践4.1 执行效率提升技巧分区裁剪确保查询条件包含分区字段向量化执行set hive.vectorized.execution.enabledtrueCBO优化set hive.cbo.enabletrue并行执行set hive.exec.paralleltrue4.2 数据加载最佳实践动态分区加载示例SET hive.exec.dynamic.partitiontrue; SET hive.exec.dynamic.partition.modenonstrict; INSERT OVERWRITE TABLE ods.sales_detail PARTITION(dt, region) SELECT order_id, product_id, amount, order_date AS dt, region_code AS region FROM staging.sales_trans;ACID事务支持Hive 3.0CREATE TABLE dw.fact_order_acid ( order_id STRING, order_date DATE, amount DECIMAL(18,2) ) STORED AS ORC TBLPROPERTIES (transactionaltrue); BEGIN; INSERT INTO dw.fact_order_acid VALUES (1001, 2023-06-01, 199.99); INSERT INTO dw.fact_order_acid VALUES (1002, 2023-06-01, 299.99); COMMIT;4.3 元数据管理与数据血缘通过Hive Hook记录数据转换关系-- 创建操作日志表 CREATE TABLE meta.etl_audit ( job_name STRING, source_tables ARRAYSTRING, target_table STRING, start_time TIMESTAMP, end_time TIMESTAMP, rows_affected BIGINT ) PARTITIONED BY (dt STRING);在实际项目中我们发现合理设置ORC文件的stripe大小(默认64MB)和row index间隔(默认10,000行)能显著提升查询性能特别是在有高选择性过滤条件的场景下。对于频繁更新的维度表采用Merge-On-Read策略的ACID表比传统的SCD方式维护成本更低。