如何将无表头的.dat文件加载至含额外字段的Hive外部表？

1. 定义外部表结构 #

先创建包含原始数据字段+额外字段的外部表，假设你的.dat文件包含col1(字符串)、col2(整数)、col3(浮点数)三个字段，根据实际字段类型和数量调整：

CREATE EXTERNAL TABLE IF NOT EXISTS customer_external (
    col1 STRING,
    col2 INT,
    col3 DOUBLE,
    cob_date STRING,
    region STRING,
    file_name STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t' -- 替换为你的.dat文件实际分隔符（如',' '|'）
LOCATION '/user/hive/warehouse/customer_datasets/' -- 数据文件所在的HDFS路径
TBLPROPERTIES (
    'skip.header.line.count' = '0' -- 无表头，设置为0
);

2. 插入数据并填充额外字段 #

利用Hive内置函数提取文件名中的日期，同时硬编码region和file_name，有两种实现方式：

方式一：直接读取文件并插入 #

适合一次性处理所有.dat文件：

INSERT OVERWRITE TABLE customer_external
SELECT
    split(line, '\t')[0] AS col1,
    CAST(split(line, '\t')[1] AS INT) AS col2,
    CAST(split(line, '\t')[2] AS DOUBLE) AS col3,
    -- 从文件名提取日期：匹配customer_data_YYYYMMDD.dat格式
    regexp_extract(input_file_name(), 'customer_data_(\\d{8})\\.dat', 1) AS cob_date,
    'APAC' AS region, -- 替换为你需要的硬编码region值
    input_file_name() AS file_name -- 获取完整文件路径，也可硬编码如'customer_data_20240708.dat'
FROM textfile('/user/hive/warehouse/customer_datasets/*.dat');

方式二：通过临时表中转 #

如果原始数据格式复杂，先创建临时表读取原始数据：

-- 创建临时表读取无表头.dat文件
CREATE TEMPORARY TABLE IF NOT EXISTS customer_temp (
    col1 STRING,
    col2 INT,
    col3 DOUBLE
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
LOCATION '/user/hive/warehouse/customer_datasets/';

-- 插入外部表并填充额外字段
INSERT OVERWRITE TABLE customer_external
SELECT
    col1,
    col2,
    col3,
    regexp_extract(input_file_name(), 'customer_data_(\\d{8})\\.dat', 1) AS cob_date,
    'EMEA' AS region, -- 自定义硬编码值
    input_file_name() AS file_name
FROM customer_temp;

关键注意事项 #

• 正则表达式调整：如果你的文件名格式不是customer_data_YYYYMMDD.dat，需要修改regexp_extract中的正则，比如文件名是20240708_customer.dat，正则改为(\\d{8})_customer.dat。
• 分隔符一致性：确保FIELDS TERMINATED BY与.dat文件的实际分隔符完全匹配，否则会导致字段解析错误。
• 字段类型转换：根据.dat文件中字段的实际类型，调整CAST函数的目标类型，避免数据转换异常。

内容的提问来源于stack exchange，提问作者Big data Pyspark