How to use SQL query to define table in dbtable?

Question

In JDBC To Other Databases I found the following explanation of dbtable parameter:

The JDBC table that should be read. Note that anything that is valid in a FROM clause of a SQL query can be used. For example, instead of a full table you could also use a subquery in parentheses.

When I use the code:

CREATE TEMPORARY TABLE jdbcTable USING org.apache.spark.sql.jdbc OPTIONS ( url "jdbc:postgresql:dbserver", dbtable "mytable" )

everything works great, but the following:

 dbtable "SELECT * FROM mytable"

leads to the error:

What is wrong?

zero323 · Accepted Answer · 2015-09-17 13:47:46Z

Since dbtable is used as a source for the SELECT statement it has be in a form which would be valid for normal SQL query. If you want to use subquery you should pass a query in parentheses and provide an alias:

CREATE TEMPORARY TABLE jdbcTable USING org.apache.spark.sql.jdbc OPTIONS ( url "jdbc:postgresql:dbserver", dbtable "(SELECT * FROM mytable) tmp" );

It will be passed to the database as:

SELECT * FROM (SELECT * FROM mytable) tmp WHERE 1=0

Soumyadip Ghosh · Accepted Answer · 2018-05-11 06:37:09Z

Code In Scala

val checkQuery = "(SELECT * FROM " + inputTableName + " ORDER BY " + columnName + " DESC LIMIT 1) AS timetable" val timeStampDf = spark.read.format("jdbc").option("url", url).option("dbtable", checkQuery).load()

Adding an alias is also necessary after the query in parenthesis.

Collectives™ on Stack Overflow

How to use SQL query to define table in dbtable?

2 Answers 2

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Linked

Related