ODPS SQL 排除指定字段的4种方案与生产避坑

在 ODPS（MaxCompute）里写 SQL 的人，几乎都会在某个时刻卡在同一个地方：一张几百列的宽表，我只想排除掉其中一两个字段，剩下的全要。于是你下意识地敲下SELECT *，然后停住——SQL 的列选择是白名单语义，没有原生的“取反”。这不是新手才会遇到的问题，做数据开发三五年的人，照样会在深夜对着一个SELECT *发呆，因为下游表结构一改，第二天凌晨的调度就红了。这篇东西聊的就是“ODPS SQL 查询除某字段之外的其他字段”这一件事：它到底有几种解法、每种解法的边界在哪、哪种能上生产、哪种只能临时排查用，以及我自己在宽表和敏感字段场景下踩过的那些坑。不管你是刚接手数仓任务的新人，还是天天和调度告警打交道的老手，下面这些内容应该都能直接拿去用。

1. 这个需求为什么在 ODPS 里特别别扭

1.1 SQL 的选列天生是白名单，没有反选语义

标准 SQL 的SELECT子句本质是一个投影列表，你写什么就返回什么，编译器不需要知道表里还有什么。这种设计在关系代数里非常自然：投影是一个明确的正向操作。问题在于，实际工作中大量的需求是“减法”而不是“加法”——这张宽表 380 列，我只要去掉一个超大的 JSON 字段和两个敏感字段，剩下 377 列全要。用白名单的思路去做减法，就等于让你把 377 个列名手打一遍。

为什么会这样？因为 SQL 在编译期并不静态展开*，*的展开发生在语义分析阶段，由元数据驱动。多数引擎早期并没有把“列集合的差集”做成语法糖，直到后面才陆续补上。不同产品的补法还不一样，有的叫EXCEPT，有的叫EXCLUDE，有的干脆不支持，让你自己想办法。ODPS 属于“给了语法糖，但有版本门槛和适用边界”的那一类，这就意味着你不能只记一种写法，得知道它什么时候会失效。

还有一个容易忽略的点：SELECT *在 ODPS 里返回的列顺序是普通列在前、分区列在后，跟你在 DDL 里写的顺序一致。这个细节在后面讲字段顺序错位的时候会反复出现，先记住它。

1.2 宽表时代，手写列清单的隐性成本远超想象

很多人第一反应是“手写就手写呗，复制粘贴一下”。我一开始也这么想，直到遇到一张埋点宽表。那张表 400 多列，列名长得像event_param_ext_attr_37，手写一遍大概要花五分钟，而这五分钟里你至少会犯两类错误：拼错一个字母（编译期报错，还算幸运），或者漏掉一列（编译期不报错，下游默默少了一个字段，半个月后才被发现）。

更麻烦的是表结构会变。上游团队加字段是家常便饭，你的手写清单不会自动跟着变。如果这个 SQL 是每天跑的调度任务，那么上游加字段那天，你的任务照常成功，只是新字段永远进不了下游表——这种“静默正确”比报错可怕得多，因为它不会触发任何告警。

再叠加一个成本：如果你要排除的字段只有一个，而保留的有 377 个，代码评审时没人会去逐行核对这 377 个列名有没有错。评审形同虚设，风险全部压在执行者一个人身上。所以手写列清单不是不能做，而是不该由人来手工维护，应该由脚本生成。

1.3 排除字段的三个真实动机

搞清楚“为什么要排除”，才能选对方案。我遇到的场景基本归为三类：

这三个动机对方案的要求并不一样。成本诉求看的是列裁剪有没有真的生效；合规诉求看的是代码里能不能一眼看到“我排除了什么”；兼容诉求看的是列顺序和列数量对不对得上。后面每一节我都会回到这张表上来对照。

2. SELECT * EXCEPT：一条语句搞定，但边界比你想的多

2.1 语法形态与返回列的排列规则

ODPS 里最省事的写法是这个：

SELECT * EXCEPT (raw_json, id_card_no) FROM my_project.dwd_user_wide WHERE ds = '20240101' LIMIT 100;

括号里可以放多个列名，用逗号分隔。返回的结果集是原表列去掉这些列，剩下的列保持原表的 DDL 顺序，不会被打乱。这一点很关键：它意味着SELECT * EXCEPT (a)的结果列顺序，恰好等于“原表顺序删掉 a”，这跟手写列清单的结果通常是一致的，但不绝对——如果你手写时把顺序写乱了，两者就对不上。

支持是随版本推进的，不是所有环境都能跑。我这边能跑通的写法就是上面这样，你在自己环境里验证的方法很简单：找一张小表，跑一句SELECT * EXCEPT (随便一个真实列名) FROM 小表 LIMIT 1;，能出结果就说明支持，报语法错误就说明版本不够，直接跳到第 3 节的脚本方案。

几个我实测过的限制，建议你也测一遍确认：

• EXCEPT后面必须跟括号，SELECT * EXCEPT col这种不写括号的形式不行。
• 括号里的列名必须是真实存在的列，写错一个会直接报错，不会静默忽略（这点反而是好事）。
• 带表别名限定的写法，比如SELECT t.* EXCEPT (col) FROM tbl t，在部分环境里解析会出问题，我一般直接写* EXCEPT (...)，不挂别名前缀。
• 分区列也可以被排除，SELECT * EXCEPT (ds)是合法的，虽然这么写的实际意义不大。

提示：如果你的 SQL 是交给调度每天跑的，别把版本差异当成“应该没问题”。上线前一定在目标项目空间里实测一次，不同项目空间、不同执行模式（比如是否走加速引擎）都可能表现不一致。

2.2 EXCEPT 关键字撞车：集合差集和列排除怎么区分

这是我认为最值得单独拿出来说的一点。EXCEPT在 SQL 里本来是个集合操作符，表示差集：

-- 集合差集：在 t1 里但不在 t2 里的 id SELECT id FROM t1 EXCEPT SELECT id FROM t2;

而列排除长这样：

-- 列排除：t1 里除 id 之外的所有列 SELECT * EXCEPT (id) FROM t1;

两个语义完全不同的东西共用一个关键字，靠的是上下文区分：紧跟在*后面并且带括号的，是列排除；出现在两个完整SELECT之间的，是集合差集。解析器能分清，但读代码的人不一定能一眼分清，尤其在长 SQL 里EXCEPT正好落在换行处的时候。

我见过的真实误读是这样的：某同学接手一段代码，看到SELECT * EXCEPT (dt) FROM a EXCEPT SELECT * FROM b，以为第一行的EXCEPT (dt)是集合运算的一部分，改了半天没改对。实际上它是两个东西叠在一起——第一个EXCEPT (dt)排除列，第二个EXCEPT做差集，虽然这个 SQL 本身写得就很可疑。

我的习惯是：只要用了列排除，就在那一行上方加一行注释说明意图，并且尽量避免在同一个查询里同时出现列排除和集合差集。如果非要同时用，把列排除的结果先落到 CTE 里，再做差集：

WITH base AS ( SELECT * EXCEPT (raw_json) FROM my_project.dwd_user_wide WHERE ds = '20240101' ) SELECT * FROM base EXCEPT SELECT * FROM my_project.dim_user_blacklist;

这样两层语义物理隔离，谁读都不会晕。

2.3 哪些位置能用、哪些位置建议绕开

能用的位置比我预想的多：子查询、CTE、视图定义、INSERT ... SELECT的源端，基本都能写。我实际用得最多的三个位置是：

一是临时排查。想快速看一眼某张宽表的数据分布，又不想把大字段拉出来撑爆客户端，SELECT * EXCEPT (raw_json, ext_info) FROM t WHERE ds='...' LIMIT 100是最快的路径，不需要任何准备工作。

二是视图封装。把“去掉敏感字段的宽表”做成视图给分析同学用，语义固化在视图里，谁查都不会漏掉排除规则：

CREATE VIEW my_project.v_dwd_user_wide_safe AS SELECT * EXCEPT (id_card_no, phone_no, detail_addr) FROM my_project.dwd_user_wide;

三是已经确认过列顺序的 INSERT。这个用法我持保留态度，具体原因在第 6 节展开——简单说就是当源和目标列顺序不完全一致时，它不报错但会写错数据。

不建议用的位置：多层嵌套的复杂查询里。每嵌套一层就来一个* EXCEPT，最后没人说得清中间结果到底有哪些列。我的经验是，一个查询里最多出现一次列排除，且尽量放在最外层或者最内层的 CTE 里，中间层一律显式写列名。

3. 稳到不行的兜底方案：把列清单自动化生成

3.1 用 information_schema.columns 一次拿全字段

不管版本支不支持EXCEPT，元数据查询这条路永远走得通。ODPS 提供了information_schema，可以直接查列信息：

SELECT column_name, ordinal_position, data_type FROM information_schema.columns WHERE table_schema = 'my_project' AND table_name = 'dwd_user_wide' ORDER BY ordinal_position;

拿到的就是按 DDL 顺序排列的完整列清单，ordinal_position保证了顺序正确。这里有个前提：项目空间需要开启 Information Schema 相关能力，如果查出来是空结果，或者报对象不存在，说明没开，那就退回用DESC my_project.dwd_user_wide;看表结构——DESC的输出格式是文本，需要人工整理或者用脚本解析，会麻烦一些，但信息是全的。

information_schema.columns有一个需要交叉核对的点：分区列是否被包含在内。这个在不同环境里表现不完全一致，有的版本会额外给出标识字段，有的版本干脆不列。我的做法是永远用DESC的结果和information_schema的结果对一遍，重点看最后一个分区列在不在，避免生成出来的 SQL 少一列。

3.2 用 pyodps 脚本生成可直接粘贴的 SQL

查到列信息之后，生成 SQL 就是纯字符串拼接的事。我用得最顺的是 pyodps，因为它能直接拿到表结构对象：

from odps import ODPS o = ODPS( access_id='your_access_id', secret_access_key='your_access_key', project='my_project', endpoint='your_endpoint', ) tbl = o.get_table('dwd_user_wide') # 普通列，顺序与 DDL 一致 normal_cols = [c.name for c in tbl.table_schema.columns] # 分区列，单独取 part_cols = [p.name for p in tbl.table_schema.partitions] # 重要：ODPS 的 SELECT * 返回顺序是「普通列 + 分区列」 all_cols = normal_cols + part_cols exclude = {'raw_json', 'id_card_no', 'phone_no'} keep = [c for c in all_cols if c not in exclude] sql = ( "SELECT\n " + ",\n ".join(f"`{c}`" for c in keep) + "\nFROM my_project.dwd_user_wide\nWHERE ds = '${bizdate}';" ) print(sql)

打印出来的东西直接粘到编辑器里就能用。列名加反引号是个小习惯，遇到列名里带特殊字符或者和关键字重名的时候能省掉一次报错。生成出来的 SQL 是死的、显式的、可评审的，这正好对应第 1.3 节里“合规”和“兼容”两个诉求。

如果你不想装 pyodps，也可以纯用information_schema的查询结果导出成 CSV，再用几行 Python 处理，逻辑是一样的。

3.3 分区列漏掉这个坑，我踩过不止一次

第一次用 pyodps 生成 SQL 的时候，我直接用了tbl.table_schema.columns，生成出来的列清单里没有ds。当时觉得没什么问题，因为WHERE ds = '...'照样能过滤。后来在做一次数据迁移，目标表是分区表，INSERT INTO target SELECT ...按位置映射，源少了分区列，结果直接报列数不匹配。

更隐蔽的一种情况是：目标表不是分区表，而是把所有列平铺的一张普通表，分区列在源里算一列、在目标里也算一列。这时候如果源端漏了ds，列数就少一个，但因为类型可能兼容（STRING 和 STRING），有些环境下不会立刻报错，而是把后面的列整体往前挪一位，数据全错。这种错误不查数据分布根本发现不了。

所以我的脚本里永远有两行：一行取columns，一行取partitions，然后拼起来。如果你只用DESC看表结构，也要注意取完整——DESC的输出里分区列是在最后单独一段的，容易看漏。

4. 临时表与视图路线：把“排除”这件事固化下来

4.1 建中间表加 DROP COLUMN，适合一次性场景

还有一种思路是“先全拿过来，再删掉不要的”：

CREATE TABLE my_project.tmp_user_wide LIKE my_project.dwd_user_wide; INSERT INTO my_project.tmp_user_wide SELECT * FROM my_project.dwd_user_wide WHERE ds = '20240101'; ALTER TABLE my_project.tmp_user_wide DROP COLUMN raw_json;

这条路线的好处是彻底——数据落了盘，后面怎么查都不会再碰那个大字段，等于是把成本一次性付清。适合的场景很明确：某个宽表你要反复查十几次做分析，每次都读那个 200MB 的 JSON 字段实在浪费，不如花一次全量复制的代价。

代价也很明确：全量复制要花存储和计算资源，而且复制那一次本身还是读了所有列，成本一点没省。另外DROP COLUMN在分区表、外部表上限制比较多，尤其外部表基本不要想。我个人的判断标准是：这个表我会查三次以上，且数据量在百 GB 以内，才考虑这条路；否则宁可每次写显式列清单。

4.2 视图封装，让排除规则变成团队约定

如果“排除某几个字段”不是一次性需求，而是长期规则（比如敏感字段永远不能出现在分析层），那视图是最合适的载体。把规则写在视图定义里，所有人查视图就自动遵守，不需要每个人记住“不能 select 手机号”。

CREATE VIEW my_project.v_user_wide_analytics AS SELECT * EXCEPT (id_card_no, phone_no, detail_addr, raw_json) FROM my_project.dwd_user_wide;

不过视图有两个必须说清楚的短板。第一，视图不落数据，每次查视图还是要扫原表，列裁剪生效了但分区裁剪还得靠你自己的WHERE条件，别指望视图帮你加速。第二，视图嵌套深了排查很痛苦，尤其当视图 A 引用视图 B 引用视图 C，出了问题要一层层往上翻。

我的做法是：视图只做一层，不做嵌套；视图定义里尽量用* EXCEPT而不是几百行手写列清单，这样上游加字段时视图能自动跟上，不会变成“视图里永远缺最新字段”的尴尬局面。这一点上，* EXCEPT反而比显式列清单更适合做视图——因为它表达的是“排除规则”而不是“当下快照”。

5. 排除一个字段，到底省了多少

5.1 列式存储下的读取代价

ODPS 底层是列式存储，读一列只读那一列的数据块，不会把整行拉出来。所以“排除一个字段”能省多少，完全取决于那个字段占了多少数据量，跟字段个数没关系。

一张 120 列的宽表，如果里面塞了 3 个 JSON 大字段，这 3 个字段可能占了整表 70% 的存储。把这三个排除掉，扫描量能从 TB 级降到几百 GB，成本差异非常直观。反过来，如果你排除的是一个BIGINT类型的状态位字段，那基本省不出什么东西——8 字节乘以行数，聊胜于无。

所以“要不要费劲排除”这件事，先看字段类型和数据量。判断方法很简单：

-- 看看几个可疑大字段的平均长度 SELECT ds, COUNT(*) AS cnt, AVG(LENGTH(raw_json)) AS avg_json_len, AVG(LENGTH(ext_info)) AS avg_ext_len, MAX(LENGTH(raw_json)) AS max_json_len FROM my_project.dwd_user_wide WHERE ds = '20240101' GROUP BY ds;

如果avg_json_len到了几千字节甚至上万，那这个字段值得排除；如果只有几十字节，那排除它的主要理由应该是合规或者兼容，而不是省钱。

5.2 用 EXPLAIN 确认列裁剪真的生效

写完 SQL 别急着跑，先看一眼执行计划：

EXPLAIN SELECT * EXCEPT (raw_json) FROM my_project.dwd_user_wide WHERE ds = '20240101';

重点看两件事：一是执行计划里读的列有没有真的少掉raw_json，二是分区裁剪有没有按ds生效。我遇到过一种情况，EXCEPT语法能跑通，但因为写在了一个表达式外面，执行计划里还是把整表列都读了——这种时候语法糖只是让你少打字，成本一点没省。

对比一下SELECT *的计划，两个计划放在一起看，列裁剪的差异是很明显的。这个习惯我建议养成：凡是改动了列清单的 SQL，上线前都 EXPLAIN 一次，比事后看账单便宜多了。

还有一个容易混淆的点：列裁剪和分区裁剪是两件独立的事。排除字段解决的是“读多少列”，WHERE ds = '...'解决的是“读多少分区”。两者要同时做对，成本才是最优的。只排除字段不加分区条件，照样是全表扫描。

6. 那些踩过的坑和定下来的规矩

6.1 字段顺序错位：不报错，但数据全错行

这是我见过后果最严重的一类问题。场景是这样：源表src有列(id, name, phone, addr, ds)，目标表tgt有列(id, name, addr, ds)，目标表是当初为了去掉phone建的。然后有人写了：

INSERT INTO tgt SELECT * EXCEPT (phone) FROM src WHERE ds = '20240101';

看起来天衣无缝，src去掉phone之后正好是(id, name, addr, ds)，与目标表完全对齐。但只要上游哪天在addr前面插了一个新字段nick_name，这句话就变成了往addr列写nick_name的值，往ds列写addr的值——如果类型恰好兼容，一句报错都没有。

我亲身经历过一次类似的：手机号和证件号在两张表里的位置互换，跑了两周才发现，下游的触达名单全发错了人。修复成本极高，因为要追溯两周内所有下游产物。

根治办法只有一个：INSERT 时显式写目标列名。

INSERT INTO tgt (id, name, addr, ds) SELECT id, name, addr, ds FROM src WHERE ds = '20240101';

这样一来，源端怎么变都不影响映射关系，列数不对会直接报错，列名对不上也会报错，问题在编译期就暴露了。这条规矩我现在是无条件执行，不管 SQL 多短。

6.2 上游结构变更引发的连锁反应

SELECT *和SELECT * EXCEPT (...)有一个共同特点：它们会跟着上游结构自动变化。这既是优点也是缺点。

上游加字段，SELECT * EXCEPT (a)会自动带上新字段。如果你的下游是一张手写的固定结构表，INSERT立刻报列数不匹配——这是好事，至少报错了。但如果下游也是个SELECT *的链路，新字段会一路往下传，直到某个环节炸掉，而那个环节可能离你很远，排查时根本想不到是半个月前上游加的一列。

上游删字段，EXCEPT (a)里的a如果正好被删了，直接报错；如果删的是别的字段，SQL 能跑，但下游某个依赖那列的报表会缺数据。

我给自己定的处理方式是：生产调度任务里，源端用* EXCEPT只出现在最内层 CTE，往外一律显式列名。这样自动跟随和稳定输出各取所需，变更的影响范围被限制在一个 CTE 里，出问题一眼就能定位。

6.3 我给自己定下的几条硬规矩

踩了这么多坑之后，现在写 ODPS SQL 我基本遵守下面这几条，供你参考：

第一，临时排查随便用* EXCEPT，生产任务慎用。临时查数据图快，怎么方便怎么来；上了调度、有了下游依赖，就得考虑三个月后别人接手能不能看懂。

第二，排除敏感字段必须显式列出字段名，不能用变量拼。合规审计要的是“代码里能看见排除了什么”，用一个变量exclude_cols拼出来的 SQL，评审时谁也确认不了。

第三，排除超过三列时，反过来考虑。如果要排除的列比保留的还多，说明你其实是在做“白名单”，那就老老实实写保留的列，别硬套EXCEPT。

第四，每次用完EXCEPT都加一行注释。就一句话：-- 排除大字段 raw_json，避免全表扫描撑爆内存。这行注释救过我自己好几次，隔了两个月回来改，一眼就知道当初为什么这么写。

第五，定期核对视图定义和实际表结构。写个小脚本，把information_schema.columns的字段清单和视图定义里的字段清单 diff 一下，能提前发现“视图里少了新字段”这类静默问题。这个脚本我放在了日常巡检里，每周跑一次，比等下游来问强。

最后分享一个我最近才改掉的习惯：以前我判断“要不要排除某字段”全凭感觉，现在会先跑一句AVG(LENGTH(col))看看数据量。有次我以为某个ext_info字段不大，量了一下平均 12KB，一张百亿行的表，光这一列就是 TB 级。数据量这东西，猜是猜不准的，量一下最踏实。