Snowflake Python连接器含括号JOIN查询无结果问题排查

安全阿岳

2026-7-28

Snowflake Python连接器带括号JOIN查询无结果问题排查

问题背景

使用snowflake-connector-python 3.0.1搭配Python 3.9.16执行含括号条件的JOIN查询时，Snowflake控制台能正常返回数据，但通过Python连接器执行却无结果。移除JOIN条件中OR语句的括号后，连接器可返回结果，但不符合业务需求。尝试多种含括号的查询改写方式（如将条件移至WHERE子句），均在控制台有效但连接器无结果。已确认应用与控制台使用相同凭据、仓库及数据库，且直接打印应用中的查询语句在控制台执行正常。

补充说明：因csd.gi_media_product有时为'N/A'，故使用OR运算符。

相关代码与数据

改写后的查询

SELECT
    *
FROM csd
LEFT JOIN mpm
    ON mpm.csd_media_product = csd.media_product
JOIN  mm
    ON mm.csd_market = csd.market
LEFT JOIN (
    SELECT dma_name, dma_id, media_name
    FROM gi 
) as gi
    ON gi.dma_name = mm.gi_market
    AND (gi.media_name = csd.gi_media_product OR gi.media_name = mpm.gi_media_product)
WHERE gi.media_name IS NOT NULL

csd表数据

ID,CONTRACT_ID,ADVERTISER,AE1USERID,AE2USERID,AE3USERID,PROGRAM,CAMPAIGN_ID,END_DATE,MARKET,MARKET_ID,MEDIA_PRODUCT,MEDIA_TYPE,QUANTITY,SALES_MARKET,START_DATE,STATUS,CAMPAIGN_SEGMENT_ID,GI_MEDIA_PRODUCT,DIGITAL_SCREENS
12345678-604707-Paid,12345678,some_business,some_user,,,some_program,123456,2023-07-02T04:00:00Z,NJ,7,2 Sheet,Rail,16,some_sales_market,2023-01-01T05:00:00Z,Paid,123456,N/A,

gi表数据

ID,GI_ID,SITE_ID,DIGITAL,CREATED_DTM,UPDATE_DTM,MEDIA_NAME,CLASSIFICATION_TYPE_NAME,MAX_FRAMES,MAX_HEIGHT,MAX_WIDTH,CONSTRUCTION_NAME,CONSTRUCTION_COUNT,CONSTRUCTION_TYPE_NAME,CONSTRUCTION_TYPE_DESCRIPTION,ILLUMINATION_TYPE_NAME,ILLUMINATION_TYPE_DESCRIPTION,MEDIA_TYPE_NAME,PLACEMENT_TYPE_NAME,ORIENTATION,LATITUDE,LONGITUDE,DMA_ID,DMA_NAME,CBSA_CODE,CBSA_NAME,COUNTY_ID,COUNTY_NAME,ZIP_CODE,ZIP_CODE_NAME,STATE,TIME_ZONE,PERIOD,BASE_SEGMENT,TARGET_SEGMENT,TARGET_GEO,MARKET,INDEX_COMP_TARGET,PCT_COMP_POP_TARGET_INMKT,PCT_COMP_IMP_TARGET,PCT_COMP_IMP_TARGET_INMKT,FREQ_AVG,IMP_TARGET,IMP,REACH_NET,REACH_PCT,TRP,EFF_FREQ_MIN,EFF_FREQ_AVG,EFF_REACH_NET,EFF_REACH_PCT,PRODUCT_NAME,ACCOUNT_NAME,PARENT_ACCOUNT_NAME,PLACE_NAME,PLACE_SHORT_NAME,SPOT_IMPRESSIONS,SPOT_MEASURES_IMP_INMKT,IMP_INMKT,SPOT_MEASURES_IMP_TARGET_INMKT,IMP_TARGET_INMKT
50088217-2035-2023,12345678,some_site_id,FALSE,2020-01-27T15:09:47.828Z,2023-02-24T21:19:51.592Z,2-Sheet,Place Based,1,46,60,some_construction_name,1,Interior Wall,the wall inside of a building,Artificial Ambient,Surface illuminated by ambient artificial light source,Panel,Platform,0,50.843869,-79.078795,123,"some_dma_name",123456,"some_cbsa_name",12345,some_county_name,123456,"some_zip_code_name",NJ,-5,1,9330,2035,Defaulted to GLOBAL,Defaulted to GLOBAL,121,1,1,1,1.23,123,123,123,123,123,,,,,2023,some_account_name,some_parent_account_name,some_place_name,,123,123,123,123,123.79658731

mm表数据

CSD_MARKET,GI_MARKET
NYO,"New York, NY"

mpm表数据

CSD_MEDIA_PRODUCT,GI_MEDIA_PRODUCT
2 Sheet,2-Sheet

Python执行脚本

import os
import snowflake


def get_csd_data(self, cursor):
    connection = self.acquire_connection()
    cursor = connection.cursor()
    cursor.execute("USE SCHEMA g")

    query = """
        SELECT
            *
        FROM csd
        LEFT JOIN mpm
            ON mpm.csd_media_product = csd.media_product
        JOIN  mm
            ON mm.csd_market = csd.market
        LEFT JOIN (
            SELECT dma_name, dma_id, media_name
            FROM gi 
        ) as gi
            ON gi.dma_name = mm.gi_market
            AND (gi.media_name = csd.gi_media_product OR gi.media_name = mpm.gi_media_product)
        WHERE gi.media_name IS NOT NULL
    """
    cursor.execute(query)
    items = cursor.fetchall()
    
    return items

def acquire_connection(self):
    try:
        connection = snowflake.connector.connect(
            user=os.environ["snowflake_db_username"],
            password=os.environ["snowflake_db_password"],
            account=os.environ["snowflake_db_account"],
            database=os.environ["snowflake_db_name"],
            warehouse=os.environ["snowflake_db_warehouse"],
            autocommit=False
        )
    except Exception as e:
        print(e)
        raise(e)
    else:
        return connection

问题分析

首先可以排除连接器的括号解析问题——因为你直接打印应用中的查询语句在控制台能正常执行，说明连接器传递给Snowflake的SQL语句是完全正确的，没有出现括号解析错误的情况。

问题大概率出在Python会话与控制台会话的参数差异上，以下是几个可能的方向：

会话参数不一致：比如字符串比较的大小写敏感性（QUOTED_IDENTIFIERS_IGNORE_CASE）、时区设置、NULL值处理规则等，这些参数会影响查询的匹配逻辑，导致相同SQL在不同会话中返回不同结果。
自动提交设置：你的Python连接设置了autocommit=False，而控制台默认是autocommit=True，虽然这个不影响查询结果，但如果有未提交的事务可能干扰会话状态。
隐形字符或格式问题：虽然你打印了查询，但可能存在控制台不可见的空白字符（比如换行、制表符），不过这种概率极低。

解决方案

1. 对齐会话参数

在Python脚本中执行查询前，先获取当前会话的参数，并与控制台对比：

cursor.execute("SHOW PARAMETERS LIKE '%CASE%'")
print(cursor.fetchall())
cursor.execute("SHOW PARAMETERS LIKE '%TIMEZONE%'")
print(cursor.fetchall())

找到差异后，在Python中显式设置一致的参数，比如：

cursor.execute("ALTER SESSION SET QUOTED_IDENTIFIERS_IGNORE_CASE = TRUE")
cursor.execute("ALTER SESSION SET TIMEZONE = 'UTC'")

2. 优化查询的条件逻辑

针对csd.gi_media_product为'N/A'的场景，可以将条件改写得更明确，避免潜在的NULL或字符串匹配问题：

SELECT
    *
FROM csd
LEFT JOIN mpm
    ON mpm.csd_media_product = csd.media_product
JOIN  mm
    ON mm.csd_market = csd.market
LEFT JOIN (
    SELECT dma_name, dma_id, media_name
    FROM gi 
) as gi
    ON gi.dma_name = mm.gi_market
    AND (
        (csd.gi_media_product != 'N/A' AND gi.media_name = csd.gi_media_product)
        OR (csd.gi_media_product = 'N/A' AND gi.media_name = mpm.gi_media_product)
    )
WHERE gi.media_name IS NOT NULL

3. 开启连接器日志排查

在创建连接时开启日志，验证执行的SQL语句是否与预期完全一致：

import logging
logging.basicConfig(level=logging.INFO)

connection = snowflake.connector.connect(
    user=os.environ["snowflake_db_username"],
    password=os.environ["snowflake_db_password"],
    account=os.environ["snowflake_db_account"],
    database=os.environ["snowflake_db_name"],
    warehouse=os.environ["snowflake_db_warehouse"],
    autocommit=False,
    logger=logging.getLogger("snowflake.connector")
)

查看日志中的SQL执行部分，确认没有被篡改或添加额外内容。

4. 调整自动提交设置

尝试将Python连接的autocommit设置为True，保持与控制台一致：

connection = snowflake.connector.connect(
    # ...其他参数
    autocommit=True
)

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

Snowflake Python连接器含括号JOIN查询无结果问题排查

问题背景 #

相关代码与数据 #