使用TABLESAMPLE查询AWS Redshift时出现KeyError及语法错误

问题场景 #

在Jupyter Notebook中通过Python连接AWS Redshift集群，尝试用TABLESAMPLE实现查询结果随机采样，却触发KeyError，最终报错指向TABLESAMPLE附近存在语法错误。

原代码 #

import pandas as pd
import redshift_connector

conn = redshift_connector.connect(
    host=os.environ['host'],
    database=os.environ['database'],
    port=int(os.environ['port']),
    user=os.environ['user'],
    password=os.environ['password']
)

print(conn)
cursor = conn.cursor()
print(cursor)

cursor.execute(
    """
    SELECT * 
    FROM tbl1 
    WHERE type = 'xxx'
    AND created_at >= '2022-01-01 00:00:00' 
    AND created_at <= '2022-12-31 00:00:00'
    TABLESAMPLE BERNOULLI (10);
    """
)

df = DataFrame(cursor.fetchall())
field_names = [i[0] for i in cursor.description]
df.columns = field_names

报错信息 #

---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
File ~/anaconda3/lib/python3.10/site-packages/redshift_connector/core.py:1631, in Connection.execute(self, cursor, operation, vals)
   1630 try:
-> 1631     ps = cache["ps"][key]
   1632     cursor.ps = ps

KeyError: ("\n               SELECT * \n               FROM pmf  \n               WHERE event_type = 'load'\n               AND created_at >= '2022-01-01 00:00:00' \n               AND created_at <= '2022-12-31 00:00:00'\n               TABLESAMPLE BERNOULLI (10);\n               ", ())

File ~/anaconda3/lib/python3.10/site-packages/redshift_connector/core.py:1701, in Connection.execute(self, cursor, operation, vals)
   1698     else:
   1699         raise e
-> 1701 self.handle_messages(cursor)

File ~/anaconda3/lib/python3.10/site-packages/redshift_connector/core.py:1969, in Connection.handle_messages(self, cursor)
   1968 if self.error is not None:
-> 1969     raise self.error

ProgrammingError: {'S': 'ERROR', 'C': '42601', 'M': 'syntax error at or near "TABLESAMPLE"', 'P': '220', 'F': '/home/ec2-user/padb/src/pg/src/backend/parser/parser_scan.l', 'L': '732', 'R': 'yyerror'}

问题原因 #

Redshift的TABLESAMPLE语法规则为：必须紧跟在表名之后，WHERE条件之前。原SQL将其放在WHERE语句末尾，属于语法错误，导致数据库解析失败；而KeyError是redshift_connector内部缓存机制抛出的前置错误，根本问题还是SQL语法不符合规范。

修正后的代码 #

import pandas as pd
import redshift_connector
import os  # 补充原代码遗漏的os模块导入

conn = redshift_connector.connect(
    host=os.environ['host'],
    database=os.environ['database'],
    port=int(os.environ['port']),
    user=os.environ['user'],
    password=os.environ['password']
)

cursor = conn.cursor()

# 调整TABLESAMPLE位置到表名后
cursor.execute(
    """
    SELECT * 
    FROM tbl1 TABLESAMPLE BERNOULLI (10)
    WHERE type = 'xxx'
    AND created_at >= '2022-01-01 00:00:00' 
    AND created_at <= '2022-12-31 00:00:00';
    """
)

df = pd.DataFrame(cursor.fetchall())  # 补充pd.前缀，修复DataFrame引用问题
field_names = [i[0] for i in cursor.description]
df.columns = field_names

额外说明 #

• 原代码还有两处小问题：遗漏os模块导入、创建DataFrame时未加pd.前缀，修正时一并处理。
• Redshift支持两种采样方式：BERNOULLI（逐行随机采样，扫描全表，随机性高）、SYSTEM（按数据块采样，速度更快但随机性稍弱），可按需选择。

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