topic Re: Cannot create a table having a column whose name contains commas in Hive metastore. in Data Governance

Cannot create a table having a column whose name contains commas in Hive metastore.

Shafi — Fri, 18 Nov 2022 07:39:24 GMT

Tried to create a delta table from spark data frame using below command:

destination_path = "/dbfs/mnt/kidneycaredevstore/delta/df_corr_feats_spark_4"

df_corr_feats_spark.write.format("delta").option("delta.columnMapping.mode", "name").option("path",destination_path).saveAsTable("CKD_Features_4")

Getting below error:

AnalysisException: Cannot create a table having a column whose name contains commas in Hive metastore. Table: `default`.`abc_features_4`; Column: Adverse, abc initial encounter

Please note that there are around 6k columns in this data frame and it is developed by data scientist generate feature. So, we cannot rename columns.

How to fix this error. Any help will be appreciated.

Re: Cannot create a table having a column whose name contains commas in Hive metastore.

Pat — Fri, 18 Nov 2022 13:07:33 GMT

Hi @Shafiul Alam ,

who gave those names to columns? 🙂

you can rename you columns, replace spaces / special characters, for example:

%python
import re
list_of_columns = df_corr_feats_spark.colums
renamed_list_of_columns = [ re.sub(r'[^0-9a-zA-Z]+', "_", col) for col in list_of_columns]
df_corr_feats_spark.toDF(*new_column_name_list)

thanks,

Pat

Re: Cannot create a table having a column whose name contains commas in Hive metastore.

Shafi — Sat, 19 Nov 2022 07:29:51 GMT

@Pat Sienkiewicz , Thanks for responding.

So, does this mean that the delta table column cannot contain any non-ascii characters? I thought option("delta.columnMapping.mode", "name") handles columns with non-ascii characters which is a feature from DBR > 10.2. But, looks like Metastore is not supporting such column naming.

Thanks again for your help.

Re: Cannot create a table having a column whose name contains commas in Hive metastore.

Pat — Sat, 19 Nov 2022 08:24:12 GMT

Hi @Shafiul Alam ,

yeah it was what I would do old days. Rename the column, I used this as an example: re.sub(r'[^0-9a-zA-Z]+', "_", col)

The issue is here that hive_metastore doesn't allow names with commas you are right. The documentation must be related to the Databricks implementation of metastore - it's confusing in the documentation sometimes

It was fine for tables in Unity Catalog:

but for hive_metastore it throws an error:

Re: Cannot create a table having a column whose name contains commas in Hive metastore.

Shafi — Mon, 21 Nov 2022 18:23:43 GMT

@Pat Sienkiewicz , thanks a lot for sharing this suggestion