ODPS SQL排除字段：五条路线、性能账与避坑指南

ODPS SQL 查询除某字段之外的其他字段这个问题在技术群里的出现频率高得有点反常但翻十篇答案有八篇都是让人把列名一个个手敲出来。真到宽表上一百多列的表让你敲一百多个字段名敲错一个就白跑一次跑一次还可能占掉不少计算资源。更麻烦的是那些存着原始日志、完整 JSON、二进制序列化结果的字段单行几 KB 到几十 KB查询里只要带出来扫描量立刻上一个台阶。这篇东西不打算只给一句手写列名就行而是把这件事背后的几条路线、各自的适用边界、版本差异、性能账和踩过的坑一次说清楚。适合天天写 ODPS SQL 的数仓开发、要做数据导出和结果表同步的同学也适合刚接手一张陌生宽表、还不知道从哪下手的新人。读完你至少能判断手上这个场景该用手工列清单、该用元数据自动拼、还是该换个思路直接建视图。1. 为什么“少查一列”值得单独拿出来说1.1 从一张 120 列的宽表说起数仓里那种用户画像表、行为明细宽表、埋点全量表动辄一百多列是常态。里面通常混着三类字段一类是业务真正要用的维度与指标二十来个一类是预留的扩展字段建表时拍脑袋加的长期空着还有一类是大字段比如一整条原始日志的 JSON 串、一次请求的完整响应体、序列化后的特征向量。你要做一次临时取数或者给下游供一张结果表往往只关心第一类后两类纯属累赘。这时候SELECT *是最省事也最危险的写法。省事在于不用想列名危险在于它把大字段一起拖了出来还把你和这张表的结构彻底绑死。我见过太多人图快写SELECT *然后在一个几亿行的表上跑了四十分钟最后发现只要五个字段。也见过INSERT OVERWRITE TABLE t2 SELECT * FROM t1这种写法上游在中间插了一列t2 的数据整体错位等到报表数字对不上才回头查中间隔了三天。所以“排除某字段”这个需求表面是语法问题底下其实是三个更实在的诉求少读数据、少踩变更、少暴露。把这三个诉求想明白方案选择自然就清楚了。1.2 SELECT * 的三笔隐性成本第一笔是 IO 与计算成本。ODPS 的表底层是列式存储优化器有个能力叫列裁剪只有 SQL 里真正引用到的列才会被读进来。你写SELECT a, b FROM tc 到 z 这些列根本不会被碰你写SELECT *所有列都参与读取包括那个平均 8KB 的日志字段。这个差别在窄表上无所谓在宽表大字段上就是几倍甚至几十倍的扫描量差异。第二笔是变更传导成本。INSERT INTO ... SELECT *是按位置对齐的不是按列名对齐。上游表在中间加一列、删一列、调一次顺序下游写入的数据就整体偏移。这种错误不会报错只会静默地写进去一堆错位数据排查成本极高。显式列清单虽然啰嗦但它是一份契约上游动结构时你能立刻收到“列数不匹配”的报错而不是三个月后发现指标全错。第三笔是数据暴露面。有些字段是手机号、证件号、精确地址、内部备注本来不该出现在任何临时结果集和导出文件里。SELECT *把它们带出来再顺手tunnel download成一个 CSV 传到某个群里这事儿的风险不用我多说。显式排除本质上是一种最小必要原则的落地。注意这三笔成本里第二笔最容易被忽略因为它不报错。凡是跨表同步、落结果表、给下游供数的场景都建议把SELECT *换成显式列清单哪怕多写几十个字段名。2. 五条路线横向对比与选型2.1 路线一手写列名最土但最稳最直接的办法就是把需要的字段一个个列出来。表结构用DESC 表名或者SHOW COLUMNS看一眼把不要的那个字段剔掉剩下的拼成一行。这条路线没有任何版本依赖不挑引擎不挑客户端复制到哪儿都能跑。它的缺点也明显列多的时候纯体力活而且是一次性的表结构一变就得重来。但我还是建议把它作为兜底方案——不管你最终用哪条自动化路线最终产出的 SQL 本质上就是一份手写列清单只不过这份清单是机器帮你拼的。理解这一点后面几条路线就都好理解了。实操上有个省力技巧在 Studio 或者命令行里先跑一次SELECT * FROM 表名 LIMIT 1结果集的表头会按顺序列出所有列名直接复制表头把不要的那个删掉比对着DESC的输出手动敲快得多。列名多的表用这招能省一半时间。2.2 路线二EXCEPT 语法能用就先验证有些引擎版本支持SELECT * EXCEPT (col_name)这种写法一行搞定非常舒服。但 ODPS 的语法支持跟具体版本、具体执行模式有关不同项目、不同区域、不同时间开通的环境可能表现不一样。所以我的建议是先花三十秒验证别默认它有。验证方法很简单找一张小表跑一句SELECT * EXCEPT (raw_log) FROM demo_project.dw_user_profile LIMIT 10;返回结果正常、列里确实没有raw_log那就说明你的环境支持直接用最省事。如果报Syntax error或者提示except附近有语法问题别纠结也别去猜是不是括号写错了直接转路线三。我在不同项目里遇到过两种结果都出现的情况同一个账号换个项目就不认了所以这事儿没有统一答案只能现场试。提示如果你的 SQL 要沉淀成调度任务长期跑即便当前环境支持 EXCEPT我也建议改成显式列清单。原因很简单——调度任务的生命周期比环境版本长今天能跑的语法半年后换个项目就不一定了。2.3 路线三information_schema 拼 SQLODPS 提供了元数据视图information_schema.columns里面存着项目下所有表的列信息所属项目、表名、列名、数据类型、注释、列序号。用它加一个WHERE column_name 要排除的字段再用聚合函数把列名拼成字符串就能自动生成一条完整的 SELECT 语句。这条路线的好处是完全在 SQL 里完成不需要装客户端、不需要写脚本一条语句产出一条可执行的 SQL。适合在 Studio 里临时用也适合做成一个“生成器”查询固化成视图。缺点是对元数据的实时性有依赖而且拼出来的列顺序不保证跟你预期一致具体细节放到第 3 章展开。2.4 路线四DESC 加脚本生成如果手上正好有命令行客户端或者 Python 环境用DESC 表名拿到结构再用 awk 或 Python 过滤拼接也是非常顺手的一条路。它的优势是顺序完全可控你想按建表顺序、想按字母序、想按类型分组都能自己定。而且脚本可以带参数封装成一个“传表名和排除字段吐出 SQL”的小工具长期用下来效率最高。代价是需要一个能连上 ODPS 的执行环境纯网页端操作的同学用不了。所以这条路线更适合有本地开发环境、经常批量处理表的人。2.5 路线五视图封装一次定义长期复用如果这张表你会反复查而且每次都排除同一个字段那最好的办法不是每次拼 SQL而是建一个视图把排除逻辑固化下来CREATE VIEW demo_project.v_user_profile_clean AS SELECT user_id, device_id, city_code, register_dt -- 这里按实际列清单写全排除 raw_log FROM demo_project.dw_user_profile;之后所有查询都对着视图写SELECT * FROM v_user_profile_clean也不会带出那个大字段。这条路线把“排除”这件事从每次查询的动作变成了一次性的建模动作长期收益最高。但要清楚视图的两个特性一是逻辑视图不落数据查视图还是回表性能上和直接查表没区别二是视图定义里的列清单是静态的原表后续新增的列不会自动出现在视图里。后者既是缺点也是优点——对下游来说上游加列不影响你这恰恰是稳定性。2.6 一张表看清怎么选路线适用场景优点主要限制手写列名临时取数、列数不多零依赖最稳纯体力易敲错EXCEPT 语法快速验证、临时查询一行搞定版本相关不保证可用information_schema纯 SQL 环境、临时生成无需外部工具列顺序不保证元数据有延迟DESC 加脚本批量处理、需要固定顺序顺序可控可封装需要执行环境视图封装长期复用、供下游一次定义永久生效新增列不自动同步选型逻辑其实就一句话一次性的用手写或 EXCEPT反复用的建视图需要批量生成的写脚本。中间那条 information_schema 路线适合“我想自动化但不想装环境”的中间态。3. 元数据驱动的完整实操3.1 第一步把表结构摸清楚不管走哪条路线第一步都是看清楚这张表到底有哪些列。三种手段按场景选DESC 表名最直接输出列名、类型、注释人眼可读。缺点是要一条条看列多的时候刷屏。information_schema.columns适合做程序化处理一条 SQL 就能把列清单拉成结果集SELECT ordinal_position, column_name, data_type, comment FROM information_schema.columns WHERE table_schema demo_project AND table_name dw_user_profile ORDER BY ordinal_position;这里有两个细节要注意。table_schema填的是项目名不是库名而且一般用小写table_name也是小写。如果你写了大写混着来很可能查出来是空结果然后误以为是权限问题。第三个手段是SELECT * FROM 表名 LIMIT 0或者LIMIT 1从结果集表头看列。这个办法最直观但拿不到类型和注释信息只适合快速看列名。注意information_schema里的元数据不是实时的表结构变更之后通常要等一段时间才能同步过来。刚加完列就去查很可能查不到。这种时候用DESC更靠谱。3.2 第二步用聚合函数把列清单拼成 SQL拿到列清单之后核心动作就是“过滤 拼接”。ODPS 里可以用WM_CONCAT做字符串聚合SELECT CONCAT( SELECT , WM_CONCAT(,, CONCAT(, column_name, )), FROM demo_project.dw_user_profile WHERE dt 2024-05-01; ) AS generated_sql FROM information_schema.columns WHERE table_schema demo_project AND table_name dw_user_profile AND column_name raw_log;跑出来的结果就是一整条可以直接复制的 SQL。这里WM_CONCAT的第一个参数是分隔符第二个是待拼接的表达式中间用CONCAT给每个列名套上反引号。如果当前环境不认WM_CONCAT这个写法换成集合函数版本SELECT CONCAT( SELECT , CONCAT_WS(,, COLLECT_LIST(CONCAT(, column_name, ))), FROM demo_project.dw_user_profile; ) AS generated_sql FROM information_schema.columns WHERE table_schema demo_project AND table_name dw_user_profile AND column_name raw_log;两种写法的差异在于聚合函数的选择实际效果一样。遇到函数报错不要死磕换另一种就行这是 ODPS 里很常见的情况——同名函数在不同版本里参数签名会变。这里必须提醒一个坑聚合函数拼接出来的列顺序是不保证的。虽然实践中通常按扫描顺序出但你不能把正确性寄托在“通常”上。如果这条 SQL 要用来做INSERT ... SELECT并写入另一张表列顺序错位会直接导致数据错乱。解决办法有两个一是拼完之后人工核对一遍顺序二是干脆用脚本按ordinal_position排序拼把顺序这件事从数据库手里拿回来。3.3 第三步处理反引号、大小写和关键字这一步是最容易翻车的地方很多人拼出来的 SQL 长得很漂亮一执行就报错问题八成出在列名的转义上。ODPS 的列名默认不区分大小写但一旦列名里包含特殊字符、或者列名本身是 SQL 关键字比如comment、desc、order、date、level不加反引号就必然报错。所以拼列名的时候统一加反引号是最省心的做法不用去判断哪个是关键字。反引号本身也有个坑如果列名里真的有反引号极少但存在需要转义。这种情况我一般直接放弃自动化手工写那一列。还有一类坑来自列名里的空格和中文。ODPS 允许列名带中文但拼 SQL 的时候如果没加反引号、或者反引号被编辑器自动转成了中文引号就会报“无法识别”的错误。复制生成的 SQL 到客户端时留意一下引号是不是被输入法改了——这个错误我踩过不止一次排查半天最后发现是引号形状不对。3.4 第四步补上分区条件与全表扫描开关分区表是另一个必须处理的细节。ODPS 默认不允许对分区表做全表扫描你生成的 SQL 里如果没有分区过滤条件执行时会直接报错提示表是分区表但缺少分区条件。正规做法当然是在生成 SQL 的时候就把分区条件带进去比如上面示例里的WHERE dt 2024-05-01。生成器里可以用变量替换或者干脆生成带占位符的模板SELECT user_id, device_id, city_code FROM demo_project.dw_user_profile WHERE dt ${bizdate};这样落到调度里替换变量就行不用每次改 SQL。如果确实需要临时全表扫一次比如统计历史总量可以临时打开开关SET odps.sql.allow.fullscan true;但这句话要慎用。宽表全表扫的代价可能是几十倍于分区扫描一次手滑可能就是一笔不小的计算开销。我的习惯是只在小表或者明确算过量的情况下才开这个开关并且用完立刻在同一个会话里关掉。3.5 第五步固化成视图或调度任务SQL 生成出来、验证通过之后如果这个查询会反复出现就别每次都跑一遍生成器了直接固化成视图。建视图的时候有个细节值得注意把排除逻辑写进视图定义比在视图外面加过滤条件更彻底因为使用视图的人通常不会再去关心原表有哪些列。CREATE VIEW IF NOT EXISTS demo_project.v_user_profile_clean AS SELECT user_id, device_id, city_code, register_dt FROM demo_project.dw_user_profile;如果这张表本身有分区视图里不需要写死分区条件把分区字段作为普通列暴露出来让调用方自己加过滤就行。这样视图的适用范围更广也不会因为写死了某个日期而导致数据看起来“不更新”。固化成调度任务的话建议在任务里加一段自检逻辑跑之前先比对一下原表列数和视图列数不一致就告警。上游加列导致下游视图“少了一列”这件事不会报错只会静默地少数据早发现早处理。4. 宽表与大字段场景的性能账4.1 列裁剪到底发生在哪一层理解列裁剪发生的位置才能理解为什么“少查一列”真的能省钱。ODPS 的表是列式存储每个列的数据在物理上分开存放。执行引擎在生成执行计划时会做一次列裁剪优化扫描算子只读取 SQL 中被引用到的列未被引用的列在 IO 层就被跳过了不会进入内存也不参与后续计算。打个比方表像一排 120 个抽屉的柜子列式存储意味着每个抽屉是独立的一格。你要取 5 个抽屉里的东西列裁剪就是只拉开这 5 个抽屉。SELECT *的意思是“把 120 个抽屉全拉出来摊在桌上”哪怕你只从里面挑走 5 样东西剩下的 115 个抽屉也已经拉开过了这个动作本身就是成本。所以“排除某个字段”带来的收益本质上是把这个字段对应的那一格抽屉彻底不拉开。字段越宽这一格越大省得越多。4.2 一个大字段能吃掉多少 IO算一笔粗账感受会更直观。假设一张表 20 列其中raw_log这一列平均每行 8KB其余 19 列加起来平均每行 500 字节。那么整行数据量大约是 8.5KB其中 94% 都在这一列上。如果这张表有 1 亿行全列扫描的数据量约 850GB 量级排除raw_log之后只剩约 50GB差了十几倍。即便列式存储加压缩能把实际读取量压下来这个比例关系基本不会变。在按扫描数据量计费的模式下这直接对应成本在按资源占用的模式下这直接对应任务耗时进而影响排队和并发。这也是为什么我特别强调“排除大字段”和“随便排除一个字段”不是一回事。排除一个 INT 类型的城市编码收益几乎为零排除一个平均 8KB 的 JSON 大字段收益可能是数量级的。所以拿到“排除某字段”这个需求时第一件事是问这个字段有多大。用DESC看类型不够最好抽样算一下平均值SELECT COUNT(1) AS row_cnt, AVG(LENGTH(raw_log)) AS avg_bytes, MAX(LENGTH(raw_log)) AS max_bytes FROM demo_project.dw_user_profile WHERE dt 2024-05-01;这个查询本身也要读那一列所以建议先在一个分区上跑别全表跑。算出来的平均值乘以行数就是你每次SELECT *白读的量。4.3 分区裁剪和数据倾斜的连带影响排除字段这件事经常和分区裁剪一起出现。原因是宽表往往是分区表你在生成 SQL 的时候如果只顾着拼列名忘了带分区条件最后要么报错要么被强制全表扫。这两个优化的效果是叠加的分区裁剪砍掉不相关的时间片列裁剪砍掉不相关的字段两刀下去扫描量可能只剩原来的百分之几。还有一个容易被忽略的连带问题是数据倾斜。大字段列在某些行上可能特别大比如某几条异常日志有几十 KB如果这一列参与了GROUP BY或者作为 join 的 key会导致个别 task 特别慢整个任务被拖到最后一个 task 结束。排除大字段之后这种倾斜往往自然消失。所以有时候“查询变快了”不是因为扫描量少了而是因为某个拖后腿的 task 没了。提示判断一个任务慢是不是大字段导致的最直接的办法是看执行计划里的 task 耗时分布。如果少数几个 task 明显比其他的慢几倍而表本身分区均匀那大概率是数据倾斜先怀疑大字段。5. 报错与排查速查5.1 语法与解析类问题最常见的是EXCEPT报语法错误。表现是 SQL 编辑器里看着没问题一执行就提示except附近有语法问题或者干脆提示*后面不能跟东西。这种情况下别怀疑自己括号写错了就是环境不支持直接转元数据拼接路线。第二常见的是拼出来的 SQL 里列名没转义导致的报错提示通常是“列名无法识别”或者“语法错误”。排查方法很简单把生成的 SQL 拿去跟DESC的输出逐字比对重点看三处——反引号有没有丢、引号是不是被输入法转成了中文、列名中间有没有多出空格。这三个原因占了这类报错的九成。第三类是函数参数不匹配。WM_CONCAT在不同版本里参数个数不一样有的是一个参数有的是两个。报错信息一般会说参数数量不对这时候换CONCAT_WS加COLLECT_LIST的组合或者干脆换脚本方案。5.2 元数据查询不到的问题information_schema查出来是空结果先检查三件事项目名是不是写成了库名、大小写是不是一致、当前项目有没有开通这个功能。项目名这块特别容易错很多人习惯性把项目名当成数据库名来写实际上 ODPS 里项目和库的概念跟传统数据库不一样。还有一个隐蔽的原因是缓存延迟。刚ALTER TABLE ADD COLUMNS加完列立刻去查information_schema很可能查不到新列。等一会儿再查或者直接用DESC看DESC是实时读元数据的不会有这个延迟。权限问题也会表现成“查不到”。如果当前账号对目标表没有读权限某些元数据视图会直接返回空而不是报权限错误。这种情况换一个有权限的账号跑一次对比一下能快速定位。5.3 结果集和导出的问题生成的 SQL 执行成功了但列数不对、列顺序不对这两类问题要分开看。列数不对通常是过滤条件写错了比如column_name raw_log里的字段名大小写和实际不一致导致这一列没被过滤掉。列顺序不对则是聚合函数无序导致的前面说过要么人工核对要么换脚本按ordinal_position拼。导出环节的坑更多。用tunnel download导出 CSV 的时候如果某个字段内容里包含换行符或者分隔符导出的文件会错行、错列。排除大字段之后这个问题通常会缓解因为大字段尤其是 JSON 和日志最容易含换行。如果还是要导出含特殊字符的字段建议指定一个业务数据里绝不会出现的分隔符比如\u0001这种控制字符。5.4 问题速查表现象最可能的原因处理方式EXCEPT附近语法错误当前环境不支持该语法改用元数据拼接或手写列名生成的 SQL 列名无法识别缺反引号、引号被转成中文统一加反引号并检查引号形状information_schema返回空项目名写错、大小写不符、元数据未同步用DESC兜底确认分区表报缺少分区条件SQL 里没带分区过滤补WHERE dt ...或临时开全表扫描列数比预期多一列排除条件的字段名写错核对字段名大小写列顺序和表结构不一致聚合函数拼接无序改用脚本按列序号拼导出文件错行字段内含换行或分隔符更换分隔符或先排除大字段任务个别 task 特别慢大字段导致数据倾斜排除该字段后重跑对比6. 几条从踩坑里换来的经验6.1 别在生产库上直接跑拼出来的 SQL自动生成的 SQL 有个特点它看起来是对的但你没法一眼确认它真的对。我踩过最典型的一次是生成的 SQL 里少了一个字段原因是那个字段的列名里带了一个特殊字符拼接时被截断了。SQL 执行成功结果集正常返回只是少了一列直到下游反馈“这个指标怎么没了”才发现。从那以后我形成了一个习惯拼出来的 SQL 先在 LIMIT 10 上跑一次把结果集的列名和DESC的输出做一次集合比对。列数对得上、列名一个不差再放到全量上跑。这个检查花不了两分钟但能挡掉绝大多数低级错误。另外一条如果生成的 SQL 要写进另一张表务必在写入之前DESC一下目标表确认列数和类型都对得上。INSERT按位置对齐这件事不会给你任何提示。6.2 在命名阶段就把“排除”这件事解决掉最好的排除是不需要排除。建表的时候如果能把大字段单独拆到一张扩展表里主表只保留常用维度和小字段那么日常查询根本不需要考虑排除的问题——SELECT *也是安全的。这个思路在数仓分层里很常见主表瘦身宽表按需 join。另一个命名层面的技巧是给大字段加统一前缀比如ext_raw_log、ext_full_json这样在生成器里可以直接用column_name NOT LIKE ext_%一次排除掉一类字段不用每次改 SQL。这个约定需要在建模阶段就定下来事后补很麻烦但一旦定下来长期收益非常大。还有一种情况是字段本身就不该让所有人看到。这时候不该靠查询时排除而应该用列级别的权限控制从元数据层面就限制访问。查询时排除只是“我不查”权限控制才是“你查不到”两者不是一回事。6.3 一个可以反复用的检查清单下面这份清单是我每次处理“排除字段”需求时会过一遍的贴在备忘录里用久了确实少踩很多坑确认要排除的字段名大小写和实际元数据一致不要凭记忆写确认这个字段的类型和平均长度判断值不值得为它单独处理确认表是不是分区表生成的 SQL 里有没有带分区条件确认生成 SQL 的列顺序尤其是要写入其他表的场景确认列名都加了反引号关键字列名和特殊字符列名单独检查先在LIMIT 10上验证列数和列名再上全量如果是长期需求评估是否直接建视图把逻辑固化下来如果上游表结构可能变化在调度里加列数比对的自检这套流程跑下来一个中等复杂度的表大概多花五到十分钟但能省掉的返工时间远不止这些。尤其是宽表一次错位排查可能要花半天而且往往是在下游发现异常之后才回溯中间的损失已经发生了。我个人的体会是ODPS 里“排除一个字段”这件事语法层面从来没复杂过复杂的是搞清楚你到底在优化什么——是省扫描量、是防变更、还是控暴露面。三个目标的解法不完全一样为省扫描量重点在识别大字段为防变更重点在显式列清单和视图为控暴露面重点其实在权限而不在 SQL。想清楚是哪一种方案自然就定了不用在几条路线之间反复纠结。