AWS Athena中CPF列设为BIGINT无法读取的解决方法咨询

问题原因 #

直接将带格式的CPF（如372.088.989-03）设为BIGINT类型会读取失败，因为原始字符串包含.和-等非数字字符，Athena无法自动将这类带符号的字符串解析为数值类型。

解决方案 #

方案一：通过CTAS创建目标表（推荐）

先创建临时外部表以STRING类型读取全量数据，再通过转换生成带数值类型CPF的正式表：

1. 创建临时外部表（适配原始CSV格式）：

CREATE EXTERNAL TABLE temp_cpf_records (
    cpf STRING,
    name STRING,
    cell BIGINT
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LOCATION 's3://你的存储桶路径/数据目录/'
TBLPROPERTIES ('skip.header.line.count'='1'); -- 若CSV包含表头则添加此参数

2. 用CTAS语句生成目标表（转换CPF为数值类型）：

CREATE TABLE cpf_records
WITH (
    format = 'Parquet', -- 推荐使用列式存储提升查询性能，也可保留CSV格式
    external_location = 's3://你的存储桶路径/目标表目录/'
) AS
SELECT
    CAST(REGEXP_REPLACE(cpf, '[^0-9]', '') AS BIGINT) AS cpf,
    name,
    cell
FROM temp_cpf_records;

此方法会将原始CPF中的非数字字符全部清除，再转换为BIGINT类型，同时完成数据的存储格式优化。

方案二：查询时实时转换（无需重建表）

如果仅需在查询时获取数值类型的CPF，可直接在查询语句中处理：

SELECT
    CAST(REGEXP_REPLACE(cpf, '[^0-9]', '') AS BIGINT) AS cpf_numeric,
    name,
    cell
FROM 你的原始字符串类型CPF表;

注意事项 #

• 不要尝试直接在外部表定义中将带格式的CPF映射为BIGINT，这种方式会因格式不匹配导致数据读取失败。
• 若需保留原始CPF格式，可在目标表中同时存储STRING类型的原始值和BIGINT类型的转换值。

内容的提问来源于stack exchange，提问作者William Soares