Spark JDBC SQL connector "SELECT INTO" Statement

user_3104078 0
Viewed 396

I am trying to read the data from SQL Server. Because of some requirements I need to create a temp table using SELECT INTO statement, that will be used further in the query. But when I run the query I get the following error

com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'INTO'

My question is, is the SELECT INTO statement allowed with Spark SQL Connector?

Here is a sample query and code

    drivers = {"mssql": "com.microsoft.sqlserver.jdbc.SQLServerDriver"}
    sparkDf = spark.read.format("jdbc") \
        .option("url", connectionString) \
        .option("query", "SELECT * INTO #TempTable FROM Table1") \
        .option("user", username) \
        .option("password", password) \
        .option("driver",  "com.microsoft.sqlserver.jdbc.SQLServerDriver") \
        .load()
mssql-jdbc pyspark apache-spark-sql apache-spark sql-server
1 Answers

It's because the driver is doing extra stuff which is getting in your way. More explicitly, the problem is documented here. Here's docs from that page for the "query" option you're using:

A query that will be used to read data into Spark. The specified query will be parenthesized and used as a subquery in the FROM clause. Spark will also assign an alias to the subquery clause. As an example, spark will issue a query of the following form to the JDBC Source.

SELECT FROM (<user_specified_query>) spark_gen_alias

Below are a couple of restrictions while using this option.

It is not allowed to specify dbtable and query options at the same time.
It is not allowed to specify query and partitionColumn options at the same time. When specifying partitionColumn option is required, the

subquery can be specified using dbtable option instead and partition columns can be qualified using the subquery alias provided as part of dbtable. Example: spark.read.format("jdbc") .option("url", jdbcUrl) .option("query", "select c1, c2 from t1") .load()

Essentially, that wrapper that the driver is putting around your code is causing the problem. And because their wrapper starts with SELECT * FROM ( and ends with ) spark_generated_alias, you're very limited in how you can "break out" of it and still execute the statements you want.

Here's how I do it.

I break it into 3 separate queries, because normal (#) and global (##) temp-tables don't work (the driver disconnects after each query).

Query 1:

SELECT 1 AS col) AS tbl; --terminates the "SELECT * FROM (" the driver prepends

--Write whatever Sql you want, then select into a new "real" table.
--E.g. here's your example, but with a "real" table.
SELECT * INTO _TempTable FROM Table1;

SELECT 1 FROM (SELECT 1 AS col --and the driver will append ") spark_generated_alias". The driver ignores all result-sets but the first.

Query 2:

SELECT * FROM _TempTable;

Query 3 (you cannot run this until after you're done with the DataFrame):

SELECT 1 AS col) AS tbl; --terminates the "SELECT * FROM (" the driver prepends

DROP TABLE IF EXISTS _TempTable;

SELECT 1 FROM (SELECT 1 AS col --and the driver will append ") spark_generated_alias". The driver ignores all result-sets but the first.
user_5305450
Related