How to make a string column with numeric and alphabet values use as partition?

[irfanaziz]

So i have two partitions defined for this delta table, One is year('GJHAR') contains year values, and the other is a string column('BUKS') with around 124 unique values. However, there is one problem with the 2nd partition column('BUKS'),

The values in BUKS are all numeric(e.g. 151, 017,002) except one('XB'). So if i create the partition by first inserting the 'XB' values followed by the other values (numeric ones ) then it gets inserted. But after the 2nd insert, which did not have any 'XB' values in the column('BUKS'), if we insert or merge a bunch of rows with numeric and 'XB' rows it simply fails with the error :

"cannot resolve XB in search condition given columns base"

I dont understand why XB is considered as a column by Spark?

[-werners-]

@nafri A​ , So to make sure I understand correctly: if you partition the table with only numeric data in BUKS, new incoming data cannot be added if it contains a string; but the other way around it does work?

Could it be that spark has inferred the column as integer/numeric instead of string?

[Kaniz_Fatma]

Hi @nafri A​, We haven’t heard from you on the last response from @Werner Stinckens​ , and I was checking back to see if his suggestions helped you. Or else, If you have any solution, please do share that with the community as it can be helpful to others.

[jose_gonzalez]

Hi @nafri A​,

Just a friendly follow-up. Did any of the responses help you to resolve your question? if it did, please mark it as best. Otherwise, please let us know if you still need help.