Vertica导出至S3时出现列错位问题求助

你遇到的问题是用Vertica的EXPORT语句将数据导出到S3时，极小部分行出现列错位（比如country列出现ip的值），核心原因几乎都是字段内容包含了导出时使用的分隔符（制表符\t）或者换行符，导致解析时字段边界被错误识别。以下是具体排查和解决办法：

1. 定位问题行 #

先查询找出包含特殊字符的异常行，重点检查url、referrer_domain、linked_profiles这类可能存储复杂内容的字段：

SELECT * FROM blabal
WHERE url LIKE '%'||E'\t'||'%' 
   OR referrer_domain LIKE '%'||E'\t'||'%'
   OR linked_profiles::VARCHAR LIKE '%'||E'\t'||'%'
   OR url LIKE '%'||E'\n'||'%'
   OR referrer_domain LIKE '%'||E'\n'||'%'
   OR linked_profiles::VARCHAR LIKE '%'||E'\n'||'%';

2. 清洗特殊字符 #

导出前对含特殊字符的字段做替换处理，把制表符和换行符换成空格或其他无冲突字符：

EXPORT TO DELIMITED(directory = 'bulbbl/',
delimiter = E'\t',
includeheader = 'false',
compression = 'GZip',
ifDirExists='append')
OVER() 
as  SELECT
        version,  
        user_id,
        REPLACE(REPLACE(url, E'\t', ' '), E'\n', ' ') AS url,
        REPLACE(REPLACE(referrer_domain, E'\t', ' '), E'\n', ' ') AS referrer_domain,
        ip,
        city,
        country,
        region,
        postal_code,
        zipcode,
        e_longitude,
        e_latitude,
        browser_type,
        device_manufacturer,
        device_model,
        device_type,
        os_type_id,
        os_type,
        os_version,
        "timestamp",
        d_id,
        et_app,
        ma_name,
        app_id,
        item_id,
        geo_data_type_id,
        a_id,
        d_id,
        5_id,
        l_id,
        p_id,
        REPLACE(REPLACE(to_json(linked_profiles), E'\t', ' '), E'\n', ' ') AS linked_profiles
FROM blabal;

3. 启用字段包裹（备选方案） #

如果不想修改字段内容，可以给EXPORT语句添加enclosure参数，用引号包裹所有字段，这样即使字段内包含分隔符，也不会被误解析：

EXPORT TO DELIMITED(directory = 'bulbbl/',
delimiter = E'\t',
enclosure = '"', -- 添加引号包裹字段
includeheader = 'false',
compression = 'GZip',
ifDirExists='append')
OVER() 
as  SELECT
        version,  
        user_id,
        url,
        referrer_domain,
        ip,
        city,
        country,
        region,
        postal_code,
        zipcode,
        e_longitude,
        e_latitude,
        browser_type,
        device_manufacturer,
        device_model,
        device_type,
        os_type_id,
        os_type,
        os_version,
        "timestamp",
        d_id,
        et_app,
        ma_name,
        app_id,
        item_id,
        geo_data_type_id,
        a_id,
        d_id,
        5_id,
        l_id,
        p_id,
        to_json(linked_profiles) as linked_profiles
FROM blabal;

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