NULL instead no value

marbys · February 21

Hi,

I'm trying to write dataframe into Snowflake table with function write_with_schema. Data comes from excel file, where we have some columns with no value and we want to keep it that way. I used parameters with read_excel and also fillna('') and dataset looks ok (no nulls, only no values/empty), but after using wrIte_with_schema we are getting nulls in those columns. Is there a way to insert data with no value/string lenght 0 instead of nulls?

Turribeach · February 21

Can you check the data type of the column in the data frame and the column in the Snowflake table? Are they the same type?

marbys · February 21

Object type for dataframe and varchar in Snowflake

Turribeach · February 21

What version of pandas are you using?

Turribeach · February 21

Are you sure you are applying fillna() correctly?

https://stackoverflow.com/a/67356585/10491951

Can you you do a count and group by that column to see all possible values after the fillna()?

marbys · February 21

Yes, dataframe looks ok. I just tried different option to have no value instead on null in dataframe, but it seems, that write_with_schema still converts that to null.

Even if there is '' as a value, it goes as null into Snowflake.

marbys · February 21

Raw is my expected output

Turribeach · February 21

Your Python code doesn't look right to me. This is how you usually write to a Dataset from a Python recipe:

# Write recipe outputs
customers_out = dataiku.Dataset("some_poutput_dataset")
customers_out.write_with_schema(df)

I don't see where you got OUTPUT from in your code.

marbys · February 21

I have OUTPUT declared in a different cell.

OUTPUT = dataiku.Dataset("OUTPUT_DATAIKU")

Everything works, except no values(''), that somehow are converted to null when using write_with_schema/write_from_dataframe

Turribeach · February 21

Can I suggest you restart your kernel and run from top to bottom again? Or run the code in a recipe to make sure there are now left overs from you playing in the Notebook.

marbys · February 21

Done that, I even checked e.g. fillna('5') and it goes to Snowflake as 5. I don't think it's an issue with python/pandas/dataframe, but specifically with writing to Snowflake and using built-in (write_with_schema/writre_from_dataframe) function to do that. Maybe there is a parameter or different function to use?

Turribeach · February 21

Try write_with_schema(df, drop_and_create=True) and with write_dataframe(df, infer_schema=False)

marbys · February 21

That's not a solution, everything goes to Snowflake as it should, except '' or "" or ' '- still as null

NULL instead no value

Answers

Categories

Setup Info

Tags