Search code examples
sqlrdatabase-schemarodbc

RODBC: can't specify schema with ALTER TABLE command

I can specify schema with CREATE TABLE or DROP TABLE:

sqlQuery(odbcConnect("Hive"), "DROP TABLE schema.table;")

but not with ALTER TABLE:

sqlQuery(odbcConnect("Hive"), "ALTER TABLE schema.table RENAME TO schema.new_table;")

Error message: ...mismatched input 'RENAME' expecting KW_EXCHANGE near 'table' in alter exchange partition

This didn't work either:

sqlQuery(odbcConnect("Hive"), "USE schema; ALTER TABLE table RENAME TO new_table;")

Error message: ...missing EOF at ';' near 'schema'

P.S. In the end, I worked around this problem using INSERT INTO TABLE. I'd still like to know the answer to the original question, though.

Solution

  • If possible, you might try upgrading to at least Hive 0.14.0.

    The issue with Hive not handling a schema in ALTER TABLE schema.table statements was resolved in Hive 0.14.0 See the following JIRA on this issue:

    https://issues.apache.org/jira/browse/HIVE-9180

    If you can upgrade, the original "ALTER TABLE schema.table" command will work.