SqlExecutor2 does not handle Snowflake ARRAY data type * Bug?
![info-rchitect](https://us.v-cdn.net/6038231/uploads/lithium_attachments/n9089iA7C08C68990A5113.png)
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
Answers
-
Alexandru Dataiker, Dataiku DSS Core Designer, Dataiku DSS ML Practitioner, Dataiku DSS Adv Designer, Registered Posts: 1,211 Dataiker
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 -
thx @AlexT. Does the backlog also have better handling of VARIANT data type, which also currently gets converted to TEXT?
-
Alexandru Dataiker, Dataiku DSS Core Designer, Dataiku DSS ML Practitioner, Dataiku DSS Adv Designer, Registered Posts: 1,211 Dataiker
Support for VARIANT type Snowflake is also in backlog.
-
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?