Dremio doesn't pushdown LEFT() to Snowflake

1
  • 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)

2

@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ā€™))

3

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

4

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.

5

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

1 Like