SqlExecutor2 does not handle Snowflake ARRAY data type * Bug?

info-rchitect

Hello,

I am using the SQLExecutor2 to read a temporary table and write to a Snowflake dataset in a Python recipe. Here is the column data type:

{"type":"ARRAY","length":16777216,"byteLength":16777216,"nullable":true,"fixed":false}

Here is my Python code:

Expression type does not match column data type, expecting VARCHAR(16777216) but got ARRAY for column DOWNED_CCD_ZONE_RADIAL_H_ARRAY

from dataiku.core.sql import SQLExecutor2

executor = SQLExecutor2(connection="MYCONNECTION")

# Create the temporary data table using internal API

mydataset = dataiku.Dataset("mydataset")
executor.exec_recipe_fragment(output_dataset=mydataset,
                              query=f"select * from {my_temp_table}",
                              overwrite_output_schema=True) 

Why would Dataiku expect a VARCHAR when the existing table it is reading from clearly states it is an ARRAY? I believe this to be a bug?

Is there a workaround where I can force Dataiku to cast certain columns with specific datatypes?

thx

---

Operating system used: Windows 10

---

Operating system used: Windows 10

Alexandru

Hi @info-rchitect
,

DSS does not currently handle ARRAY types on SQL datasets and downcasts them to STRING which is why you are seeing this error. Better handling of this is in the backlog.

To better understand your issue and possible workarounds for now, I would suggest you submit a support ticket with the job diagnostics from your python recipe.

Thanks

info-rchitect

thx @AlexT. Does the backlog also have better handling of VARIANT data type, which also currently gets converted to TEXT?

Alexandru

Support for VARIANT type Snowflake is also in backlog.

Kanyenewmerch9

Simply phrasing your question well can make the difference between receiving a helpful response to your query swiftly and being bogged down in clarifications and follow-ups. Here is a guide on how to ask your questions in a manner that increases the likelihood that you'll receive a useful response. The golden rule is to consider what would be required if you were to attempt to answer your own query. How can the individual asking the question give you the tools you need to be able to respond?