Spark SQL just load the query result as a new source (via JDBC), so DO NOT confused with the
Spark SQL tables. They are totally independent database systems.
From: Yi Zhang [mailto:zhangyi76@yahoo.com.INVALID]
Sent: Friday, May 15, 2015 1:59 PM
To: Cheng, Hao; Dev
Subject: Re: Does Spark SQL (JDBC) support nest select with current version
@Hao,
Because the querying joined more than one table, if I register data frame as temp table, Spark
can't disguise which table is correct. I don't how to set dbtable and register temp table.
Any suggestion?
On Friday, May 15, 2015 1:38 PM, "Cheng, Hao" <hao.cheng@intel.com<mailto:hao.cheng@intel.com>>
wrote:
You need to register the “dataFrame” as a table first and then do queries on it? Do you
mean that also failed?
From: Yi Zhang [mailto:zhangyi76@yahoo.com.INVALID]
Sent: Friday, May 15, 2015 1:10 PM
To: Yi Zhang; Dev
Subject: Re: Does Spark SQL (JDBC) support nest select with current version
If I pass the whole statement as dbtable to sqlContext.load() method as below:
val query =
"""
(select t1._salory as salory,
|t1._name as employeeName,
|(select _name from mock_locations t3 where t3._id = t1._location_id ) as locationName
|from mock_employees t1
|inner join mock_locations t2
|on t1._location_id = t2._id
|where t1._salory > t2._max_price) EMP
""".stripMargin
val dataFrame = sqlContext.load("jdbc", Map(
"url" -> url,
"driver" -> "com.mysql.jdbc.Driver",
"dbtable" -> query
))
It works. However, I can't invoke sql() method to solve this problem. And why?
On Friday, May 15, 2015 11:33 AM, Yi Zhang <zhangyi76@yahoo.com.INVALID<mailto:zhangyi76@yahoo.com.INVALID>>
wrote:
The sql statement is like this:
select t1._salory as salory,
t1._name as employeeName,
(select _name from mock_locations t3 where t3._id = t1._location_id ) as locationName
from mock_employees t1
inner join mock_locations t2
on t1._location_id = t2._id
where t1._salory > t2._max_price
I noticed the issue [SPARK-4226] SparkSQL - Add support for subqueries in predicates - ASF
JIRA<https://issues.apache.org/jira/browse/SPARK-4226> is still in the progress. And
somebody commented it that Spark 1.3 would support it. So I don't know current status for
this feature. Thanks.
Regards,
Yi
[SPARK-4226] SparkSQL - Add support for subqueries in predicates - ASF JIRA<https://issues.apache.org/jira/browse/SPARK-4226>
java.lang.RuntimeException: Unsupported language features in query: select customerid from
sparkbug where customerid in (select customerid from sparkbug where customerid in (2,3)) TOK_QUERY
TOK_FROM TOK_TABREF TOK_TABNAME sparkbug TOK_INSERT TOK_DE...
View on issues.apache.org<https://issues.apache.org/jira/browse/SPARK-4226>
Preview by Yahoo
|