Apache Drill使用Table查询失败但View正常的问题求助

问题 #

在Apache Drill 1.21.0上进行TPC-H基准测试时出现如下异常：

• 用CREATE VIEW语句创建customer、orders、lineitem三个视图后，执行TPC-H Q3查询可得到正确结果。
• 将创建语句中的view替换为table创建表后，执行相同查询报错，提示Column 'c_mktsegment' is ambiguous。

创建视图/表的SQL语句 #

create view if not exists customer as select
    cast(columns[0] as bigint) c_custkey,
    cast(columns[1] as char(25)) c_name,
    cast(columns[2] as varchar(40)) c_address,
    cast(columns[3] as int) c_nationkey,
    cast(columns[4] as char(15)) c_phone,
    cast(columns[5] as double) c_acctbal,
    cast(columns[6] as char(10)) c_mktsegment,
    cast(columns[7] as varchar(101)) c_comment
from dfs.`/opt/tpch/data/customer.tbl`;

create view if not exists orders as select
    cast(columns[0] as bigint) o_orderkey,
    cast(columns[1] as bigint) o_custkey,
    cast(columns[2] as char(1)) o_orderstatus,
    cast(columns[3] as double) o_totalprice,
    cast(columns[4] as date) o_orderdate,
    cast(columns[5] as char(15)) o_orderpriority,
    cast(columns[6] as char(15)) o_clerk,
    cast(columns[7] as int) o_shippriority,
    cast(columns[8] as varchar(79)) o_comment
from dfs.`/opt/tpch/data/orders.tbl`;

create view if not exists lineitem as select
    cast(columns[0] as bigint) l_orderkey,
    cast(columns[1] as bigint) l_partkey,
    cast(columns[2] as bigint) l_suppkey,
    cast(columns[3] as int) l_linenumber,
    cast(columns[4] as double) l_quantity,
    cast(columns[5] as double) l_extendedprice,
    cast(columns[6] as double) l_discount,
    cast(columns[7] as double) l_tax,
    cast(columns[8] as char(1)) l_returnflag,
    cast(columns[9] as char(1)) l_linestatus,
    cast(columns[10] as date) l_shipdate,
    cast(columns[11] as date) l_commitdate,
    cast(columns[12] as date) l_receiptdate,
    cast(columns[13] as char(25)) l_shipinstruct,
    cast(columns[14] as char(10)) l_shipmode,
    cast(columns[15] as varchar(44)) l_comment
from dfs.`/opt/tpch/data/lineitem.tbl`;

执行查询的命令 #

$DRILL_HOME/bin/drill-conf -u "jdbc:drill:zk=node1;schema=dfs.tmp" -log "result.txt" -f "q3.sql"

TPC-H Q3查询SQL #

select 
    l_orderkey, 
    sum(l_extendedprice * (1 - l_discount)) as revenue, 
    o_orderdate, 
    o_shippriority 
from 
    customer, 
    orders, 
    lineitem 
where 
    c_mktsegment = 'BUILDING' 
    and c_custkey = o_custkey 
    and l_orderkey = o_orderkey 
    and o_orderdate < date '1995-03-15' 
    and l_shipdate > date '1995-03-15' 
group by 
    l_orderkey, 
    o_orderdate, 
    o_shippriority 
order by 
    revenue desc, 
    o_orderdate 
LIMIT 10;

错误信息 #

Error: VALIDATION ERROR: From line 11, column 2 to line 11, column 13: Column 'c_mktsegment' is ambiguous

[Error Id: c929bf25-df6b-40c0-9866-b606f769c410 ] (state=,code=0)
Aborting command set because "force" is false and command failed: "select 
    l_orderkey, 
    sum(l_extendedprice * (1 - l_discount)) as revenue, 
    o_orderdate, 
    o_shippriority 
from 
    customer, 
    orders, 
    lineitem 
where 
    c_mktsegment = 'BUILDING' 
    and c_custkey = o_custkey 
    and l_orderkey = o_orderkey 
    and o_orderdate < date '1995-03-15' 
    and l_shipdate > date '1995-03-15' 
group by 
    l_orderkey, 
    o_orderdate, 
    o_shippriority 
order by 
    revenue desc, 
    o_orderdate 
LIMIT 10;"

环境信息：

• Drill 1.21.0
• openjdk-17
• Ubuntu 22.04

问题原因 #

这是Apache Drill元数据解析机制的版本特性差异导致的：

1. 视图与外部表的元数据存储不同：创建视图时，Drill会完整保存视图定义中列的归属信息，查询时能明确识别c_mktsegment属于customer表；而创建的表本质是Drill外部表，元数据仅记录列名，未关联列的原始表归属。
2. Drill 1.21.0版本缺陷：该版本在处理多外部表查询时，优化器无法正确匹配无前缀列名到对应表，即使实际不存在同名列，也会误判为列名歧义。

解决方法 #

方法1：修改Drill配置关闭隐式列扩展 #

在drill-override.conf中添加以下配置，避免元数据解析混乱：

drill.exec: {
  planner: {
    enable_implicit_column_expansion: false
  }
}

修改后重启Drill服务，重新创建表并执行查询即可。

方法2：用表别名+列前缀简化查询 #

在FROM子句中为表添加别名，通过别名前缀引用列，既符合SQL规范也能避免歧义：

select 
    l.l_orderkey, 
    sum(l.l_extendedprice * (1 - l.l_discount)) as revenue, 
    o.o_orderdate, 
    o.o_shippriority 
from 
    customer c, 
    orders o, 
    lineitem l
where 
    c.c_mktsegment = 'BUILDING' 
    and c.c_custkey = o.o_custkey 
    and l.l_orderkey = o.o_orderkey 
    and o.o_orderdate < date '1995-03-15' 
    and l.l_shipdate > date '1995-03-15' 
group by 
    l.l_orderkey, 
    o.o_orderdate, 
    o.o_shippriority 
order by 
    revenue desc, 
    o.o_orderdate 
LIMIT 10;

方法3：升级Drill版本 #

该列歧义问题在Drill 1.22.0及后续版本中已被修复，升级到更高版本可直接解决，无需修改配置或查询。

内容的提问来源于stack exchange，提问作者G.M