Uploaded image for project: 'Spark'
  1. Spark
  2. SPARK-28834

JDBC option "sessionInitStatement" does not executes sql statement when write data into database from dataframe

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 2.4.0
    • Fix Version/s: None
    • Component/s: Spark Core
    • Labels:
      None

      Description

      I need to write dataframe data into database, after database session is opened to the remote DB and before starting to write data, I need to execute a custom SQL statement with option "sessionInitStatement", all i know is that "sessionInitStatement" will always executes the configured sql statement before operation.

      When write data with code below:

       

      df.toDF(columns: _*).write.format("jdbc").options(
       Map(
       "url" -> db.url,
       "user" -> db.user,
       "dbtable" -> table,
       "password" -> db.password,
       JDBC_BATCH_INSERT_SIZE -> batchSize.toString,
       "sessionInitStatement" -> "set @@session.tidb_batch_insert=1;"
       )).mode(Append).save()
      

       

      the sql statement "set @@session.tidb_batch_insert=1;" is never executed.

       

      If i read data from database:

      reader.format("jdbc").options(
        Map(
          "url" -> dbProperties.dbUrl,
          "dbtable" -> alias(queryStatement),
          "user" -> dbProperties.userName,
          "sessionInitStatement" -> "set @@session.tidb_batch_insert=1;",
          "password" -> dbProperties.password
        )).load
      
      

      the sql statement "set @@session.tidb_batch_insert=1;" is executed correctly.

      In my opinion, "sessionInitStatement" option should execute sql statement after database session is opened to the remote DB, no matter you read data or write data.

       

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              raoxunrong raoxunrong
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated: