ALTER TABLE xxx RENAME TO issue with memsql spark connector

[samminen]

Hello Team, We are trying to rename a table present in memsql database using memsql spark connector as shown below. But we are not successful. Can you please verify below memsql spark connector code and advise ?

Code:

sparksession .read .format("memsql") .option("ddlEndpoint", connectionURL) .option("user",username) .option("Password", password) .options(Map("query" -> ("ALTER TABLE Table1 RENAME TO Table2"),"database" -> "MyDB")) .load()

ERROR:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'alter table MyDB.Table1 rename to MyDb.Table2) AS q WHERE 1=0' at line 1

[rpourzand]

Hi,

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 Table1 RENAME TO Table2";
   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

[blinov-ivan]

Hi, currently you can use our new feature for running DDL queries directly to MemSQL. Here's an example:

import com.memsql.spark.SQLHelper.QueryMethods
spark.executeMemsqlQuery("MyDB", "ALTER TABLE Table1 RENAME TO Table2")

Here's the first argument to executeMemsqlQuery is the Database name, and the second argument is the query to execute. You can also run this method with only one argument (without database name), in this case, the query will be executed against the database specified in SparkSession options. Here's the doc about this feature.