Setting up Azure SQL External Metastore for Azure Databricks — Invalid column name ‘IS_REWRITE_ENABLED’

Question

I’m attempting to set up an external Hive metastore for Azure Databricks. The Metastore is in Azure SQL and the Hive version is 1.2.1 (included with azure HdInsight 3.6).

I have followed the setup instructions on the “External Apache Hive metastore” page in Azure documentation.

I can see all of the databases and tables in the metastore but if I look at a specific table I get the following.

Caused by: javax.jdo.JDOException: Exception thrown when executing query : SELECT DISTINCT 'org.apache.hadoop.hive.metastore.model.MTable' AS NUCLEUS_TYPE,A0.CREATE_TIME,A0.LAST_ACCESS_TIME,A0.OWNER,A0.RETENTION,A0.IS_REWRITE_ENABLED,A0.TBL_NAME,A0.TBL_TYPE,A0.TBL_ID FROM TBLS A0 LEFT OUTER JOIN DBS B0 ON A0.DB_ID = B0.DB_ID WHERE A0.TBL_NAME = ? AND B0."NAME" = ?
NestedThrowables:
com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'IS_REWRITE_ENABLED'.

I was expecting to see errors relating to the underlying storage but this appears to be a problem with the metastore.

Anybody have any idea what’s wrong?

Answer 1

The error message seems to suggest that the column IS_REWRITE_ENABLED doesn't exist on table TBLS with alias A0.

Looking at the script for the hive-schema for the derby db, it can help guide you in seeing that the column in question does exist here.

metastore script definition

If you've got admin access to the Azure SQL db, you can alter the table and add the column:

ALTER TABLE TBLS
ADD IS_REWRITE_ENABLED char(1) NOT NULL DEFAULT 'N';

I don't believe this is the actual fix but it does work around the error.