azure-databricksdatabricks-sqldatabricks-unity-catalog

Databricks Unity Catalog Error: [UNSUPPORTED_DATA_TYPE_FOR_DATASOURCE]

I’m migrating objects from hive_metastore to Unity Catalog on Databricks.

Some of my legacy tables are stored textfile. When I try to sync these tables into the Unity Catalog, I get the following error: [UNSUPPORTED_DATA_TYPE_FOR_DATASOURCE] The Text datasource doesn't support the column column_name of the type xyz. SQLSTATE: 0A000

I had problems with the following types: double, VARCHAR(128), INT, DECIMAL(28,0)...

I tried CTAS (Create Table As Select), like this:

1.

DROP TABLE IF EXISTS <UC>.<SCHEMA_NAME>.<TABLE_NAME>;
CREATE TABLE IF NOT EXISTS <UC>.<SCHEMA_NAME>.<TABLE_NAME> USING TEXT LOCATION 
'LOCATION_IN_AZURE' AS SELECT * FROM <HIVE>.<SCHEMA_NAME>.<TABLE_NAME>

When I did this, I got the following error: 'Text data source supports only a single column, and you have 2 columns.'

2.

DROP TABLE IF EXISTS <UC>.<SCHEMA_NAME>.<TABLE_NAME>;
CREATE TABLE IF NOT EXISTS <UC>.<SCHEMA_NAME>.<TABLE_NAME> USING TEXT LOCATION 
'LOCATION_IN_AZURE' AS SELECT * FROM TEXT.`<LOCATION_IN_AZURE>`

I get zero results (from what I can see, the TEXT format in UC is looking for ',' delimiter, and the delimiter I have is ';'), so when I execute the query SELECT * FROM TEXT.`<LOCATION_IN_AZURE> I get one column with the values separated by ';'.

3.

SYNC TABLE <UC>.<SCHEMA_NAME>.<TABLE_NAME> 
FROM <HIVE>.<SCHEMA_NAME>.<TABLE_NAME>

But I got this message: [UPGRADE_NOT_SUPPORTED.HIVE_SERDE] Table is not eligible for upgrade from Hive Metastore to Unity Catalog. Reason: Hive SerDe table. SQLSTATE: 0AKUC

Thank you!

Solution

  • Unity Catalog's TEXT format supports only single-column string data and assumes a comma delimiter, making it incompatible with multi-column, semicolon-delimited, or typed tables like VARCHAR or DECIMAL.

    Hive SerDe based tables cannot be synced directly into Unity Catalog. TEXT is non-schema aware and lacks type support, so migration requires transforming data into supported formats like Delta or Parquet.

    Here is the below approach you can follow to get it resolved:

    Use CTAS with correct configuration defining options.

    Firstly if you have tables saved in hive_metastore as text,

    enter image description here

    You can directly use below query:

    CREATE  OR  REPLACE  TABLE my_catalog.my_schema.employees_uc
AS  SELECT  *  FROM hive_metastore.default.legacy_text_table;

    To get the output :

    SELECT  *  FROM  my_catalog.my_schema.employees_uc;

    Again as you were using

    SELECT  *
FROM  text.`azure_location`;

    It will give output as:

    enter image description here

    Now, if the text file is stored in azure storage with delimiter , you need to do the following below:

    Though your file is stored as text still use csv with your desired delimiter ',', for you it is ';'.

    CREATE OR REPLACE  TABLE  my_catalog.my_schema.employees_uc_new
AS  SELECT  *
FROM csv.`<azure_location>`
WITH (
`delimiter`  =  ',',
`inferSchema`  =  'true'
)

    Then to check use query:

    SELECT  *  FROM  my_catalog.my_schema.employees_uc_new

    Output:

    enter image description here

    For more methods to migrate to Unity Catalog Table follow the below resource:

    Upgrade Hive tables and views to Unity Catalog