SYNC command fails with a mismatched input error

Unity Catalog must be enabled on your cluster before you can use SYNC to migrate a legacy Hive table to Unity Catalog.

Written by John.Lourdu

Last published at: April 11th, 2023

Problem

The SYNC command can be used to migrate legacy external Apache Hive tables to Unity Catalog.

When you run SYNC in a Databricks notebook, it fails with a mismatched input 'schema' expecting 'MATERIALIZED' error.

SYNC schema <target UC catalog>.<target UC schema> from hive_metastore.<hive schema> DRY RUN

com.databricks.backend.common.rpc.DatabricksExceptions$SQLExecutionException: org.apache.spark.sql.catalyst.parser.ParseException: mismatched input 'schema' expecting 'MATERIALIZED'(line 1, pos 5)

Cause

This error occurs if Unity Catalog is not enabled on your cluster.

Solution

Verify that Unity Catalog is enabled in your workspace and on your clusters.

Unity Catalog is supported with Single user and Shared access mode on clusters running Databricks Runtime 11.3 LTS and above. Unity Catalog is not supported on No isolation shared clusters.