SqlExecutor2 does not handle Snowflake ARRAY data type * Bug?

info-rchitect
Level 6
SqlExecutor2 does not handle Snowflake ARRAY data type * Bug?

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:

 

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) 

 

 

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

 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

0 Kudos
4 Replies
AlexT
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

0 Kudos
info-rchitect
Level 6
Author
thx @AlexT. Does the backlog also have better handling of VARIANT data type, which also currently gets converted to TEXT?
0 Kudos
AlexT
Dataiker

Support for VARIANT type Snowflake is also in backlog. 

Kanyenewmerch9
Level 1

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? 

0 Kudos

Labels

?
Labels (2)

Setup info

?
A banner prompting to get Dataiku