使用Athena Start Query Execution存储CSV到动态S3路径报错排查

1. SQL模板未完成变量替换直接提交 #

你将Python字符串格式化方法.format()写在了SQL文本内部，且Lambda代码中直接把未做变量替换的原始SQL模板传给了Athena，模板里的{fyear}/{fmonth}等占位符带着{符号直接进入Athena解析流程，而Athena SQL语法不识别该占位符，直接触发语法报错。

2. 过滤条件缺少字符串引号 #

就算完成格式化，year = {fyear}的写法也不符合SQL规范，你提取的fyear/fmonth等都是字符串类型，Athena SQL里字符串值需要用单引号包裹，否则会被识别为字段名或非法数值。

3. 隐藏拼写错误 #

SQL中存在笔误：cast(sales_tax as varvchar)多写了一个字母v，正确写法为varchar。

4. 输出路径逻辑混淆 #

你已经给目标表配置了Glue的storage.location.template和分区投影，INSERT操作时Athena会自动根据SELECT返回的year/month/day/hour字段值，匹配模板生成对应S3存储路径，不需要你手动在OutputLocation拼接分区路径，OutputLocation仅需填写Athena查询元数据的存储路径即可。

1. 修正SQL模板，移除写在SQL内部的.format()方法，给字符串占位符增加单引号，修正拼写错误：

"""insert into olo_baja_insert_to_csv
select cast(first_name as varchar) first_name,
cast(contact_number as varchar) contact_number,
cast(membership_number as varchar) membership_number,
cast(olo_customer_id as varchar) olo_customer_id,
cast(login_providers as varchar) login_providers,
cast(external_reference as varchar) external_reference,
cast(olo_email_address as varchar) olo_email_address,
cast(last_name as varchar) last_name,
cast(loyalty_scheme as varchar) loyalty_scheme,
cast(product_id as varchar) product_id,
cast(modifier_detail['modifierid'] as varchar) modifier_detail_modifier_id,
cast(modifier_detail['description'] as varchar) modifier_detail_description,
cast(modifier_detail['vendorspecificmodifierid'] as varchar) modifier_detail_vendor_specific_modifier_id,
cast(modifier_detail['modifiers'] as varchar) modifier_detail_modifiers,
cast(modifier_detail['modifierquantity'] as varchar) 
modifier_detail_modifier_quantity,
cast(modifier_detail['customfields'] as varchar)  modifier_detail_custom_fields,
cast(modifier_quantity as varchar) modifier_quantity,
cast(modifier_custom_fields as varchar) modifier_custom_fields,
cast(delivery as varchar) delivery,
cast(total as varchar) total,
cast(subtotal as varchar) subtotal,
cast(discount as varchar) discount,
cast(tip as varchar) tip,
cast(sales_tax as varchar) sales_tax,
cast(customer_delivery as varchar) customer_delivery, 
cast(payment_amount as varchar) payment_amount, 
cast(payment_description as varchar) payment_description,
cast(payment_type as varchar) payment_type,
cast(location_lat as varchar) location_lat,
cast(location_long as varchar) location_long,
cast(location_name as varchar) location_name,
cast(location_logo as varchar) location_logo,
cast(ordering_provider_name as varchar) ordering_provider_name,
cast(ordering_provider_slug as varchar) ordering_provider_slug,year,month,day, hour
from( 
select cast(first_name as varchar) first_name,
cast(contact_number as varchar) contact_number, 
cast(membership_number as varchar) membership_number,
cast(olo_customer_id as varchar) olo_customer_id,
cast(login_providers as varchar) login_providers,
cast(external_reference as varchar) external_reference,
cast(olo_email_address as varchar) olo_email_address,
cast(last_name as varchar) last_name,
cast(loyalty_scheme as varchar) loyalty_scheme,
cast(product_id as varchar) product_id,
cast(special_instructions as varchar) special_instructions,
cast(quantity as varchar) quantity,
cast(recipient_name as varchar) recipient_name,
cast(custom_values as varchar) custom_values,
cast(item_description as varchar) item_description,
cast(item_selling_price as varchar) item_selling_price,
cast(modifier['sellingprice'] as varchar) pre_modifier_selling_price,
cast(modifier['modifierid'] as varchar) modifier_id,
cast(modifier['description'] as varchar) modifier_description,
cast(modifier['vendorspecificmodifierid'] as varchar) vendor_specific_modifierid,
cast(modifier['modifiers'] as varchar) modifier_details,
cast(modifier['modifierquantity'] as varchar) modifier_quantity,
cast(modifier['customfields'] as varchar) modifier_custom_fields,
cast(delivery as varchar),cast(total as varchar),cast(subtotal as varchar), 
cast(discount as varchar),cast(tip as varchar),cast(sales_tax as varchar),cast(customer_delivery as varchar), cast(payment_amount as varchar), cast(payment_description as varchar),
cast(payment_type as varchar),cast(location_lat as varchar),cast(location_long as varchar), cast(location_name as varchar), 
cast(location_logo as varchar),
cast(ordering_provider_name as varchar), cast(ordering_provider_slug as varchar), year,month,day,hour
from(
select
cast(json_extract(customer, '$.firstname') as varchar) as first_name,
cast(json_extract(customer, '$.contactnumber') as varchar) as contact_number,
cast(json_extract(customer, '$.membershipnumber') as varchar) as membership_number,
cast(json_extract(customer, '$.customerid') as varchar) as olo_customer_id,
cast(json_extract(customer, '$.loginproviders') as array<map<varchar,varchar>>) as login_providers,
cast(json_extract(customer, '$.externalreference') as varchar) as external_reference,
cast(json_extract(customer, '$.email') as varchar) as olo_email_address,
cast(json_extract(customer, '$.lastname') as varchar) as last_name,
cast(json_extract(customer, '$.loyaltyscheme') as varchar) as loyalty_scheme,
try_cast(json_extract("item", '$.productid') as varchar) product_id,
try_cast(json_extract("item", '$.specialinstructions') as varchar) special_instructions,
try_cast(json_extract("item", '$.quantity') as varchar) quantity,
try_cast(json_extract("item", '$.recipientname') as varchar) recipient_name,
try_cast(json_extract("item", '$.customvalues') as varchar) custom_values,
try_cast(json_extract("item", '$.description') as varchar) item_description,
try_cast(json_extract("item", '$.sellingprice') as varchar) item_selling_price,
cast(json_extract("item", '$.modifiers') as array<map<varchar,json>>) modifiers,
cast(json_extract(totals, '$.delivery') as varchar) delivery,
cast(json_extract(totals, '$.total') as varchar) total,
cast(json_extract(totals, '$.subtotal') as varchar) subtotal,
cast(json_extract(totals, '$.discount') as varchar) discount,
cast(json_extract(totals, '$.tip') as varchar) tip,
cast(json_extract(totals, '$.salestax') as varchar) sales_tax,
cast(json_extract(totals, '$.customerdelivery') as varchar) customer_delivery,
payment['amount'] payment_amount,
payment['description'] payment_description,
payment['type'] payment_type,
cast(json_extract("location", '$.latitude') as varchar) location_lat,
cast(json_extract("location", '$.longitude') as varchar) location_long,
cast(json_extract("location", '$.name') as varchar) location_name,
cast(json_extract("location", '$.logo') as varchar) location_logo,
cast(json_extract("orderingprovider", '$.name') as varchar) ordering_provider_name,
cast(json_extract("orderingprovider", '$.slug') as varchar) ordering_provider_slug,hour
from sandbox_twilliams.olo_baja_raw_john_testing
cross join unnest (payments, "items") as t (payment, item)
--cross join unnest ("items") as t (item)
--cross join unnest (payments) as t (payment)
where year = '{fyear}'
and month = '{fmonth}'
and day = '{fday}'
and hour = '{fhour}'
)  
CROSS JOIN UNNEST (modifiers) as t (modifier)
)
CROSS JOIN UNNEST (cast(modifier_details as array<map<varchar,json>>)) as t (modifier_detail)
group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45;"""

2. 修正Lambda代码的SQL提交逻辑，先完成变量替换再提交查询：

else:
    # 先完成变量替换再赋值给query
    query = kahala_baja.format(fyear=fyear, fmonth=fmonth, fday=fday, fhour=fhour)
    print('<<QUERY  >>', query)
    response = athena_client.start_query_execution( 
        QueryString=query,
        QueryExecutionContext={
          'Database': database
        },
        ResultConfiguration={
            # 此处填写独立的Athena查询元数据存储路径，不要和INSERT目标表路径重合
            'OutputLocation': 's3://你的Athena查询结果存储桶/athena-logs/',
        }
    )

3. 确认Glue表的分区投影配置已开启，对应字段的类型、范围配置正确即可，无需手动拼接INSERT的输出路径。

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