topic Re: A void column was created after connecting to cosmos in Data Engineering

A void column was created after connecting to cosmos

joel_iemma — Thu, 12 May 2022 12:55:38 GMT

Hi everyone, I have connected to Cosmos using this tutorial https://github.com/Azure/azure-sdk-for-java/tree/main/sdk/cosmos/azure-cosmos-spark_3_2-12/Samples/DatabricksLiveContainerMigration

After creating a table using a simple SQL command:

CREATE TABLE mydb.cosmos_table AS

SELECT *

FROM cosmosCatalog.mycosmosdb.mycosmoscontainer

LIMIT 100

After this statement finishes successfully, when querying or checking the table in the "Data" pane on the left, I receive this error. Apparently, the column "BankErrorDescription" is of type void, and then it can't be found according to the following error.

How to turn this void column into string for example? One approach would be to unload to storage and then load again but I'd prefer a more direct solution?

Re: A void column was created after connecting to cosmos

Atanu — Thu, 26 May 2022 06:19:32 GMT

https://stackoverflow.com/questions/11563732/change-a-value-in-a-column-in-sqlite may be this also you can look for. and the above you mentioned https://github.com/Azure/azure-sdk-for-java/tree/main/sdk/cosmos/azure-cosmos-spark_3_2-12/Samples/DatabricksLiveContainerMigration , I am not the expert but may be this can help. @Joel iemma

Re: A void column was created after connecting to cosmos

Prabakar — Thu, 26 May 2022 15:02:08 GMT

Have you tried alter column?

ALTER TABLE [tbl_name] 
ALTER COLUMN [col_name_1] [DATA_TYPE]

Re: A void column was created after connecting to cosmos

joel_iemma — Fri, 27 May 2022 09:56:43 GMT

The way I solved it with more dynamically python was:

df = ...
cols = [(col[0], str(col[1])) for col in df.dtypes]
    void_cols = [x[0] for x in cols if x[1] == 'void']
    print(void_cols)
    
    for col in void_cols:
        df = df.withColumn(col, lit(None).cast('string'))

Re: A void column was created after connecting to cosmos

Prabakar — Mon, 30 May 2022 18:43:42 GMT

Thats good to know that you were able to solve this.

Re: A void column was created after connecting to cosmos

Anonymous — Thu, 07 Jul 2022 16:14:26 GMT

Hey there @Joel iemma

Hope all is well! Just wanted to check in if you would be happy to mark an answer as best for us, please? It would be really helpful for the other members too.

Cheers!