Spark over JDBC uses an alias not allowed

Asked Aug 18 '20 at 07:27

Active Aug 18 '20 at 07:27

Viewed 186 times

Using Spark 2.4.0 and Exasol 6.2.0, I want to create a DataFrame over jdbc from a simple query SELECT * FROM table_name over JDBC.

This is the code in scala:

      df = sparkSession.read
        .format("jdbc")
        .option("driver", sourceJDBCConn("jdbcDriver"))
        .option("url", sourceJDBCConn("jdbcUrl"))
        .option("user", sourceJDBCConn("jdbcUsername"))
        .option("password", sourceJDBCConn("jdbcPassword"))
        .option("query", "SELECT * FROM table_name")
        .load()

This works with PostgreSQL but not with Exasol. If I look into the session-audit I find the following SQL:

SELECT * FROM (SELECT * FROM table_name) __SPARK_GEN_JDBC_SUBQUERY_NAME_0 WHERE 1=0

the error message from Exasol is the folowing:

syntax error, unexpected $undefined, expecting UNION_ or EXCEPT_ or MINUS_ or INTERSECT_ [line 1, column 98]

While PostgreSQL looks to accept the alias with the 2 leading underscores, such an alias is not allowed in Exasol. Is there any workaround to change the name of the alias __SPARK_GEN_JDBC_SUBQUERY_NAME_0 with an identifier accepted by Exasol?

asked Aug 18 '20 at 07:27

nicolamarangoni

what if you add `, backticks? – Lamanus Aug 18 '20 at 07:37
It doesn't make any difference. I can only modify the query inside the parenthesis, everything outside them ist set by Spark: `SELECT * FROM (I_can_only_define_this_query) __SPARK_GEN_JDBC_SUBQUERY_NAME_0 WHERE 1=0` – nicolamarangoni Aug 18 '20 at 07:54
what if add a quote? `"__something"` – Lamanus Aug 18 '20 at 07:55
I cannot neither modify nor quote or whatever __SPARK_GEN_JDBC_SUBQUERY_NAME_0 this is set by Spark somewhere in its code. – nicolamarangoni Aug 18 '20 at 07:56
Ah, I see, that is not your option but spark did. hm... – Lamanus Aug 18 '20 at 07:59
Then, what if you change the query in a way that `(select * from table) a` that is, not query but try for dbtable option. – Lamanus Aug 18 '20 at 08:00

Spark over JDBC uses an alias not allowed

0 Answers0