Memsql Spark Connector DDL operation

Off-Topic
1

Hi ,

We are working on Spark Memsql Connector to load/write data from Hadoop to Memsql Database. We have a requirement to implement a table rename using spark SQL in memsql db.

We tried the below option to submit ALTER query in below format, which didnt work for us.

sparkSession.read.format(“memsql”).option(“ddlEndpoint”,“xxxx”).option(“username”,“xxx”).option(“password”,“xxx”).options(Map(“query”->“ALTER TABLE TEST RENAME TO CHECK”,“database”->“dbname”)).load().

kindly suggest us on the same.

Thanks,
Harsha.

2

Hi harshakiranmca,

Thank you for reaching out and welcome to our community!

For DDL and other operational queries (e.g., update/delete), there is no concept of those in SparkSQL and you must use a direct JDBC connection (or prepared statement) to achieve them. For example:

try {
    con = DriverManager.getConnection("mysqlurl", "userName", "password");
    try {
         Statement st = con.createStatement();
         } finally {
               if (st != null) close st
               }
   String sql = "ALTER TABLE TEST RENAME TO CHECK";
   st.execute(sql);
   st.close()
   con.close();
} finally {
  if con != null close con
}

Thank you for bringing this question to our attention. We will be updating our MemSQL documentation to make this point clearer, and we are considering releasing future functionality which will make it easier to connect to MemSQL directly via JDBC for such operations that cannot be performed natively through Spark.

Best,
Roxanna

3

Got it, thanks Roxanna for the inputs.