Hive is supporting “Sub-Queries” functionality since version 0.13, as previously described in “Sub-Queries in Apache Hive.” We also discussed how the query syntax is similar to SQL. It’s no wonder, if you’ve encountered the following error message while working with sub-queries.
FAILED: SemanticException [Error 10249]: Line 1:24 Unsupported SubQuery Expression ‘ColName’: Correlating expression cannot contain unqualified column references.
Look at the below sub-query:
SELECT * FROM emp WHERE deptno IN(
SELECT deptno FROM Dept WHERE loc=’DALLAS’);
The query above works OK in SQL, however it fails in Hive. This is because the column name for which the parent query is seeking for its value in the child query must have a table reference.
The query should be written as below.
SELECT * FROM emp WHERE emp.deptno IN(
SELECT deptno FROM Dept WHERE loc=’DALLAS’);
Hope you find this article helpful.
Please subscribe for more interesting updates.
Big Data and SQL 