Dremio doesn't pushdown LEFT() to Snowflake

  • I am trying to migrate data from snowflake to Minio/iceberg/dremio using Dremio
  • In Dremio, I query snowflake directly with a dremio database connection and attempt to create the table.
  • I pass it the following query and it says that: Field with index 15 exceeds the size limit of 32000 bytes.
    which I believe is line_items.
CREATE TABLE minio.data.stitch_landing.shopify_the_chosen."abandoned_checkouts" ("ABANDONED_CHECKOUT_URL","BILLING_ADDRESS","BUYER_ACCEPTS_MARKETING","CART_TOKEN","COMPLETED_AT","CREATED_AT","CURRENCY","CUSTOMER","CUSTOMER_LOCALE","DISCOUNT_CODES","EMAIL","GATEWAY","ID","LANDING_SITE","LINE_ITEMS","NAME","NOTE","NOTE_ATTRIBUTES","PHONE","PRESENTMENT_CURRENCY","REFERRING_SITE","SHIPPING_ADDRESS","SHIPPING_LINES","SOURCE_NAME","SUBTOTAL_PRICE","TAXES_INCLUDED","TAX_LINES","TOKEN","TOTAL_DISCOUNTS","TOTAL_LINE_ITEMS_PRICE","TOTAL_PRICE","TOTAL_TAX","TOTAL_WEIGHT","UPDATED_AT","_SDC_BATCHED_AT","_SDC_EXTRACTED_AT","_SDC_RECEIVED_AT","_SDC_SEQUENCE","_SDC_SHOP_ID","_SDC_SHOP_MYSHOPIFY_DOMAIN","_SDC_SHOP_NAME","_SDC_TABLE_VERSION","USER_ID","BUYER_ACCEPTS_SMS_MARKETING","LOCATION_ID","SOURCE_IDENTIFIER","DEVICE_ID","TOTAL_DUTIES","CLOSED_AT") 
    AS
    SELECT "ABANDONED_CHECKOUT_URL","BILLING_ADDRESS","BUYER_ACCEPTS_MARKETING","CART_TOKEN",case when length("COMPLETED_AT") > 11 then "COMPLETED_AT" else to_timestamp("COMPLETED_AT",'YYYY-MM-DD HH24:MI:SS.FFF TZO') end,case when length("CREATED_AT") > 11 then "CREATED_AT" else to_timestamp("CREATED_AT",'YYYY-MM-DD HH24:MI:SS.FFF TZO') end,"CURRENCY","CUSTOMER","CUSTOMER_LOCALE","DISCOUNT_CODES","EMAIL","GATEWAY","ID","LANDING_SITE","LINE_ITEMS","NAME","NOTE","NOTE_ATTRIBUTES","PHONE","PRESENTMENT_CURRENCY","REFERRING_SITE","SHIPPING_ADDRESS","SHIPPING_LINES","SOURCE_NAME","SUBTOTAL_PRICE","TAXES_INCLUDED","TAX_LINES","TOKEN","TOTAL_DISCOUNTS","TOTAL_LINE_ITEMS_PRICE","TOTAL_PRICE","TOTAL_TAX","TOTAL_WEIGHT",case when length("UPDATED_AT") > 11 then "UPDATED_AT" else to_timestamp("UPDATED_AT",'YYYY-MM-DD HH24:MI:SS.FFF TZO') end,case when length("_SDC_BATCHED_AT") > 11 then "_SDC_BATCHED_AT" else to_timestamp("_SDC_BATCHED_AT",'YYYY-MM-DD HH24:MI:SS.FFF TZO') end,case when length("_SDC_EXTRACTED_AT") > 11 then "_SDC_EXTRACTED_AT" else to_timestamp("_SDC_EXTRACTED_AT",'YYYY-MM-DD HH24:MI:SS.FFF TZO') end,case when length("_SDC_RECEIVED_AT") > 11 then "_SDC_RECEIVED_AT" else to_timestamp("_SDC_RECEIVED_AT",'YYYY-MM-DD HH24:MI:SS.FFF TZO') end,"_SDC_SEQUENCE","_SDC_SHOP_ID","_SDC_SHOP_MYSHOPIFY_DOMAIN","_SDC_SHOP_NAME","_SDC_TABLE_VERSION","USER_ID","BUYER_ACCEPTS_SMS_MARKETING","LOCATION_ID","SOURCE_IDENTIFIER","DEVICE_ID","TOTAL_DUTIES",case when length("CLOSED_AT") > 11 then "CLOSED_AT" else to_timestamp("CLOSED_AT",'YYYY-MM-DD HH24:MI:SS.FFF TZO') end from snowflake_stitch.stitch_landing.shopify_the_chosen."abandoned_checkouts";
  • After I got this error the first time, I then added in left(“LINE_ITEMS”,8000) in an attempt to truncate the table, but get the same error. I look at what was sent to snowflake and the query is basically just a select * from the table, which means the left() wasn’t pushed down to snowflake.
  • How can I make the pushdown happen? According to the documentaiton it’s supposed to happen.
  • Here is the full query profile attached.

0d5d0d15-6d25-4627-b0ee-739b2a2874a2.zip (23.7 KB)

@alex_a, welcome to the Dremio Community.

One alternative is to use external queries to push the entire query as-is to Snowflake. Something like,

CREATE TABLE minio_source.path.to.your.iceberg.table as SELECT * FROM table(snowflake_source.external_query(‘SELECT <your columns> FROM path.to.snowflake.table’))

That would probably work! But the documentation says that external queries are only supported for these databases, which does not appear to include snowflake.

Sounds like a doc bug. But, if I’m not mistaken, all the source connectors except for the noSQL ones are built on ARP.

Have you tried the external query on Snowflake? Let us know.

This has worked for us, thank you so much for the help!

1 Like