How to check if a request located in JDBC_SESSION_INIT_STATEMENT is working? DataframeReader

Question

I am trying to connect to sql server with spark-jdbc, using JDBC_SESSION_INIT_STATEMENT to create a temporary table and then download data from the temporary table in the main query.

I have the following code:

//df is org.apache.spark.sql.DataFrameReader
val s = """select * into #tmp_table from ( SELECT op.ID,
          |       op.Date,
          |       op.DocumentID,
          |       op.Amount,
          |       op.AmountCurr,
          |       op.CurrencyID,
          |       operson.ObjectTypeId AS PersonOT,
          |       op.PersonID,
          |       ocontract.ObjectTypeId AS ContractOT,
          |       op.ContractID,
          |       op.DocNum,
          |       op.MomentCreate,
          |       op.ObjectTypeID,
          |       op.OwnerObjectID
          |FROM dbo.Operation op With (Index = IX_Operation_Date) --Без хинта временами уходит в скан всей таблицы
          |LEFT JOIN dbo.Object ocontract ON op.ContractID = ocontract.ID
          |LEFT JOIN dbo.Object operson ON op.PersonID = operson.ID
          |WHERE op.Date>='2019-01-01' and op.Date<'2020-01-01' AND 1=1
          |) wrap_for_single_connect
          |OPTION (LOOP JOIN, FORCE ORDER, MAX_GRANT_PERCENT=25)""".stripMargin

df
  .option(JDBCOptions.JDBC_SESSION_INIT_STATEMENT, s)
  .jdbc(
    jdbcUrl,
    "(select * from tempdb.#tmp_table) sub",
    connectionProps)

i get com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name '#tmp_table'. And I have a feeling that JDBC_SESSION_INIT_STATEMENT is not working, because I deliberately tried to mess up the request and still got the Invalid object error.

How can I check if the request is working in JDBC_SESSION_INIT_STATEMENT?

Answer 1

One way to know whether your JDBCOptions.JDBC_SESSION_INIT_STATEMENT is executed is to enable INFO logging level for org.apache.spark.sql.execution.datasources.jdbc logger.

That should trigger this line and print out the following message to the logs:

Executing sessionInitStatement: [sql]

Given the comment I don't think you should use it to create a source table to load records from:

// This executes a generic SQL statement (or PL/SQL block) before reading
// the table/query via JDBC. Use this feature to initialize the database
// session environment, e.g. for optimizations and/or troubleshooting.

You should use dbtable or query parameter instead.