Airflow中Redshift Unload含Replace语句的语法报错解决求助

错误原因 #

Redshift的UNLOAD命令要求查询语句用单引号包裹，当SQL查询内部包含单引号时，会破坏外层单引号的结构，导致语法解析失败。你原本的replace(end_user_entity.full_name, 'z','t')中的单引号触发了这个问题。

解决方案（针对替换双引号为空的需求） #

修改SQL中的replace语句，直接指定双引号为替换目标，同时避免单引号嵌套冲突：

修改后的SQL查询 #

get_ns_inv_data = """
    select invoice_number, invoice_line_id, invoice_date,invoice_type, maintenance_start_date, maintenance_end_date, substring(sf_invoice,0,8) sf_invoice, sf_opportunity_no,
    sf_order_no, currency, account_id, department_id, billtocust_id, reseller_id, enduser_id, product_hierarchy_id, sku, product_internal_id, unit_price, quantity_invoiced,
    value_invoiced, value_invoiced_tx, cogs_amount, region_id, license_type,
    reg.territory as ns_territory, reg.region as ns_region,
    bill_entity.full_name as bill_to_full_name, reseller_entity.full_name as reseller_full_name, replace(end_user_entity.full_name, '"', '') as end_user_full_name
    from netsuite.invoices inv
    left join netsuite.region reg on inv.region_id=reg.region_hierarchy_id
    left join netsuite.entity bill_entity on inv.billtocust_id = bill_entity.entity_id --or inv.reseller_id = entity.entity_id --or inv.enduser_id = entity.entity_id
    left join netsuite.entity reseller_entity on inv.reseller_id = reseller_entity.entity_id
    left join netsuite.entity end_user_entity on inv.enduser_id = end_user_entity.entity_id;
"""

关键修改点 #

• 将replace(end_user_entity.full_name, 'z','t')改为replace(end_user_entity.full_name, '"', '')，直接把双引号替换为空字符串，满足实际需求。
• 利用Python三重双引号字符串的特性，内部的双引号无需转义，同时避免了单引号嵌套导致的UNLOAD语法解析错误。

额外注意事项（如果后续需要替换单引号） #

如果之后需要替换字符串中的单引号，需将SQL里的单引号转义为两个连续单引号，例如：

replace(col_name, ''', '')  -- 替换单引号为空

这样UNLOAD命令解析时，会将两个连续单引号识别为一个单引号字符，不会破坏外层的包裹结构。

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