SqlExecutor2 does not handle Snowflake ARRAY data type * Bug?

info-rchitect
info-rchitect Registered Posts: 188 ✭✭✭✭✭✭
edited July 16 in Using Dataiku

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
    Alexandru Dataiker, Dataiku DSS Core Designer, Dataiku DSS ML Practitioner, Dataiku DSS Adv Designer, Registered Posts: 1,226 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

  • info-rchitect
    info-rchitect Registered Posts: 188 ✭✭✭✭✭✭
    thx @AlexT. Does the backlog also have better handling of VARIANT data type, which also currently gets converted to TEXT?
  • Alexandru
    Alexandru Dataiker, Dataiku DSS Core Designer, Dataiku DSS ML Practitioner, Dataiku DSS Adv Designer, Registered Posts: 1,226 Dataiker

    Support for VARIANT type Snowflake is also in backlog.

  • Kanyenewmerch9
    Kanyenewmerch9 Registered Posts: 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?

Setup Info
    Tags
      Help me…