Sqoop导入时报错unknown column 'cities.city' in 'field list'求助

这个问题我之前处理过，咱们从核心原因到解决方案一步步捋清楚：

1. 问题根源：MySQL不支持FULL JOIN语法 #

你写的full join是标准SQL的全外连接语法，但MySQL原生不支持全外连接。当Sqoop把这条查询传递给MySQL执行时，数据库无法正确解析JOIN逻辑，进而抛出列找不到的错误（本质是JOIN语法错误导致的解析失败，并非真的没有cities.city列）。

2. 解决方案：用UNION ALL模拟全外连接 #

把原查询中的FULL JOIN替换成**左连接+右连接（排除重复记录）**的组合，同时要确保$CONDITIONS在两个查询分支中都存在（Sqoop需要这个参数来拆分任务）。修改后的完整命令如下：

sqoop import \
--connect jdbc:mysql://localhost/sivam_db \
--username root \
--password cloudera \
--query 'SELECT cities.city AS ccity, normcities.city AS ncity FROM cities LEFT JOIN normcities USING(id) WHERE $CONDITIONS UNION ALL SELECT cities.city AS ccity, normcities.city AS ncity FROM normcities LEFT JOIN cities USING(id) WHERE cities.id IS NULL AND $CONDITIONS' \
--split-by id \
--target-dir /user/duplicatecolumn \
--m 1 \
--boundary-query "SELECT min(id),max(id) FROM cities" \
--mapreduce-job-name fjoin \
--direct

3. 提前验证步骤（避免踩坑） #

在执行Sqoop命令前，先在MySQL客户端里测试查询逻辑是否正常，执行以下SQL：

SELECT cities.city AS ccity, normcities.city AS ncity FROM cities LEFT JOIN normcities USING(id) 
UNION ALL 
SELECT cities.city AS ccity, normcities.city AS ncity FROM normcities LEFT JOIN cities USING(id) WHERE cities.id IS NULL;

如果这条SQL能正常返回结果，说明逻辑没问题，Sqoop命令修改后就能正常运行了。

内容的提问来源于stack exchange，提问作者naga siva kumar