Kai Sasaki Kai Sasaki - 6 months ago 42
SQL Question

Hive must specify actual data source(FROM) with UNION ALL

I have a question about Hive

UNION ALL
specification.

This query is a valid Hive query.

SELECT
1 as id,
'Tom' as name


This job returns correct one record result as expected.

+----------+----------------+
| id | name |
+----------+----------------+
| 1 | Tom |
+----------+----------------+


But when this query is submitted with
UNION ALL
, I'm facing an issue.

SELECT
id,
name
FROM
members
UNION ALL
SELECT
1 as id,
'Tom' as name,


This SQL produces
Error: java.lang.IllegalArgumentException: Can not create a Path from an empty string
exception. I found all sub-queries of
UNION ALL
must have actual data source with
FROM
. For example, above query can work as expected after rewriting like below.

SELECT
id,
name
FROM
members
UNION ALL
SELECT
1 as id,
'Tom' as name
FROM (
SELECT COUNT(1) FROM members
) t


Is this a specification of SQL or Hive? Cannot we run the query without
FROM
clause? Or is this a bug of Hive itself?
The version of Hive I'm using is 0.13 with Hadoop 2.4.

Answer

All SELECT statements need FROM clause.

Even your first query SELECT 1 as id, 'Tom' as name gives error as shown below:enter image description here

Whereas, if we run SELECT 1 as id, 'Tom' as name FROM table1 where table1 is the table in my database, then we have result as below: enter image description here

So above two queries prove that FROM clause is needed in all SELECT statements.

It doesn't matter UNION ALL or whatever.

This is the feature of Hive database.

I hope this helps you!!!

Comments