Hi,
I am using Vertica DB and connected to Dremio. Running a simple select as “select count(*) from table where column1 is null;” this query is running for a while (waiting for 30 minutes and beyond) but not displaying any result hence need to cancel. The same query is working fine on dbeavur or any other clients. I am using Vertica 11.1.x and Dremio version 23.2.2-202307280452300417-9276e770. I created a view in space and created reflection which is working out. But i cannot use reflection for this purpose as there will be a latency in data. Please advice.
You should check out the Dremio query profile and see if the filter has been pushed down into Vertica.
It is stuck in the JDBC_SUB_SCAN and i need to cancel the query. I am running against Vertica.
Thanks.
@u.sundaram Does the query complete if you directly run on Vertica? If yes, kindly send the Dremio profile as (like @Benny_Chow pointed out) that will tell us what the pushdown to Vertica is
Hi, Any update for me is appreciated. Thanks.
@u.sundaram The pushdown created by Dremio, see planning tab under final physical transformation 02-02 is probably not efficient, cab you copy and paste that into Vertica and run? See if that helps. I am not sure if that query is sensitive and so not pasting here.
Can you not move that data to a lake in Iceberg format as push down to Vertica is not going to be performant
Thanks for your update.
This query is running fine when i executed it using dbeaver or vsql (vertica client). Only from Dremio it is not working. All i am doing is connecting to vertica and executing this query and not moving this to iceberg format. Do you see from profile? Thanks.
@u.sundaram I understand you are querying Vertica from Dremio,
- I am saying the push down might be inefficient so run the psh down in the planning tab on Vertica (it will be different from what you are running on Vertica client)
- Second point I am making is that querying Vertica via Dremio is going to be sub-optial instead think of moving this to Iceberg and should be significantly faster
How to push down? Is it any config change i need to do in Dremio? Please clarify. Thanks for your update.
@u.sundaram Please see my update 2 days back
To see the pushdown created by Dremio, see planning tab under final physical transformation 02-02 is probably not efficient, can you copy and paste that into Vertica and run? IF this does not complete then probably. I am pasting the push down below, copy and paste into Vertica (both the queries) and see if it runs, let us know if both run or one of them never completes. Based on that we can decide where the issue is
SELECT "fact_orders"."order_ts", "fact_orders"."is_active", "fact_orders"."version", "fact_orders"."country_cd", "fact_orders"."order_dt", "fact_orders"."order_id", "fact_orders"."po_id", "fact_orders"."order_ts_est", "fact_orders"."order_modified_ts", "fact_orders"."order_modified_ts_est", "fact_orders"."customer_id", "fact_orders"."user_id", "fact_orders"."os", "fact_orders"."device_type", "fact_orders"."client_type", "fact_orders"."os_platform", "fact_orders"."channel_id", "fact_orders"."channel_code", "fact_orders"."order_payment_email", "fact_orders"."order_payment_email_src", "fact_orders"."order_payment_email_enc", "fact_orders"."order_payment_first_name", "fact_orders"."order_payment_last_name", "fact_orders"."order_payment_line1", "fact_orders"."order_payment_line2", "fact_orders"."order_payment_phone", "fact_orders"."order_payment_postal_code", "fact_orders"."order_payment_city", "fact_orders"."order_payment_state_or_province", "fact_orders"."order_payment_country", "fact_orders"."order_shipping_email", "fact_orders"."order_shipping_email_src", "fact_orders"."order_shipping_email_enc", "fact_orders"."order_shipping_first_name", "fact_orders"."order_shipping_last_name", "fact_orders"."order_shipping_line1", "fact_orders"."order_shipping_line2", "fact_orders"."order_shipping_phone", "fact_orders"."order_shipping_postal_code", "fact_orders"."order_shipping_city", "fact_orders"."order_shipping_state_or_province", "fact_orders"."order_shipping_country", "fact_orders"."line_item_count", "fact_orders"."order_samsung_profile_profile_email", "fact_orders"."order_samsung_profile_profile_email_src", "fact_orders"."order_samsung_profile_profile_email_enc", "fact_orders"."private_store_id", "fact_orders"."submission_dt_ts", "fact_orders"."submission_ts_est", "fact_orders"."order_type", "fact_orders"."internal_detailed_fraud_status", "fact_orders"."epp_discount_amount", "fact_orders"."campaign_id", "fact_orders"."store_name", "fact_orders"."store_channel", "fact_orders"."discount_amount", "fact_orders"."order_subtotal_amt", "fact_orders"."tax", "fact_orders"."is_payment_failure_flag", "fact_orders"."is_payment_verified_flag", "fact_orders"."browser", "fact_orders"."STATUS", "fact_orders"."upgraded_from_order_id", "fact_orders"."eff_start_ts", "fact_orders"."eff_end_ts", "fact_orders"."etl_ts", "fact_orders"."job_id", "fact_orders"."fraud_failure_reason", "fact_orders"."order_total_amt", "fact_orders"."payment_option", "fact_orders"."payment_method", "fact_orders"."payment_gateway", "fact_orders"."card_details_card_type", "fact_orders"."card_status", "fact_orders"."cart_type", "fact_orders"."finance_plan_id", "fact_orders"."external_plan_id", "fact_orders"."finance_provider_type", "fact_orders"."first_monthly_payment", "fact_orders"."decrypt_user_id", "fact_orders"."aes_encrypt_user_id", "fact_orders"."finance_plan_provider", "fact_orders"."finance_flag", "fact_orders"."payment_methods", "fact_orders"."payment_method_group", "fact_orders"."epp_group", "fact_orders"."order_shipping_postal_code_decrypt", "fact_orders"."order_shipping_city_decrypt", "fact_orders"."order_shipping_state_or_province_decrypt", "fact_orders"."order_shipping_country_decrypt", "fact_orders"."order_sub_type", "fact_orders"."is_primary_payment", "fact_orders"."partner_name", "fact_orders"."partner_id", "fact_orders"."partner_order_id", "fact_orders"."partner_origin", "fact_orders"."is_multi_tradein_flag", "fact_orders"."order_payment_company_name", "fact_orders"."campaign_referral", "fact_orders"."adobe_visitor_id", "fact_orders"."is_skip_tax_exemption_flag", "fact_orders"."rewards_available_reward_amount", "fact_orders"."rewards_redeemed_reward_amount", "fact_orders"."plan_type", "fact_orders"."plan_provider", "fact_orders"."tier_expiration_date", "fact_orders"."tier_points_earned_up_to_date", "fact_orders"."tier_points_needed", "fact_orders"."tier_status", "fact_orders"."is_bby_nerp_so_do_enabled", "fact_orders"."in_store_purchase_flag", "fact_orders"."endless_aisle_purchase_flag", "fact_orders"."ea_store_id", "fact_orders"."cost_split_type", "fact_orders"."cost_before_tax_updation", "fact_orders"."cost_after_tax_updation", "fact_orders"."adjust_auth_status", "fact_orders"."fraud_manual_release", "fact_orders"."fraud_attributes_pre_auth", "fact_orders"."fraud_attributes_decision", "fact_orders"."in_store_payment", "fact_orders"."user_store_segment", "fact_orders"."pay_id", "fact_orders"."oe_warehouse_code", "fact_orders"."oe_category", "fact_orders"."oe_shipping_date", "fact_orders"."pickup_store_info_name", "fact_orders"."pay_mode_option", "fact_orders"."promotion_details", "fact_orders"."oe_promotion_details", "fact_orders"."tags", "fact_orders"."etl_indexed_at", "fact_orders"."indexing_delay_in_minutes", "fact_orders"."remorse_period_end_ts"
FROM "verticadb"."edw"."fact_orders"
SELECT "fact_td_credit_application"."id", "fact_td_credit_application"."cart_id", "fact_td_credit_application"."application_status", "fact_td_credit_application"."application_id", "fact_td_credit_application"."created_ts", "fact_td_credit_application"."created_dt", "fact_td_credit_application"."created_ts_est", "fact_td_credit_application"."date_time_sent_ts", "fact_td_credit_application"."date_time_sent_ts_est", "fact_td_credit_application"."etl_indexed_ts", "fact_td_credit_application"."_id", "fact_td_credit_application"."account_number", "fact_td_credit_application"."account_type", "fact_td_credit_application"."billing_info_city", "fact_td_credit_application"."billing_info_country", "fact_td_credit_application"."billing_info_email", "fact_td_credit_application"."billing_info_first_name", "fact_td_credit_application"."billing_info_phone", "fact_td_credit_application"."billing_info_postal_code", "fact_td_credit_application"."billing_info_state", "fact_td_credit_application"."billing_info_state_or_province", "fact_td_credit_application"."billing_record_id", "fact_td_credit_application"."credit_amount_requested", "fact_td_credit_application"."credit_limit", "fact_td_credit_application"."finance_program", "fact_td_credit_application"."identity_id", "fact_td_credit_application"."product_category", "fact_td_credit_application"."purchase_flow_id", "fact_td_credit_application"."purchase_flow_template", "fact_td_credit_application"."return_code", "fact_td_credit_application"."schema_version", "fact_td_credit_application"."transaction_link", "fact_td_credit_application"."eff_start_ts", "fact_td_credit_application"."eff_end_ts", "fact_td_credit_application"."etl_ts"
FROM "verticadb"."edw"."fact_td_credit_application"
WHERE "fact_td_credit_application"."is_active" = 1 AND CAST("fact_td_credit_application"."credit_limit" AS INT) > 49
Hi,
Both the query is working fine in Vertica. From Dremio the second query is working but the first one is not. Thanks.
Can you run the below query and see how many records are in this table?
SELECT count(*)
FROM "verticadb"."edw"."fact_orders"
Hi Balaji,
Any update for me? Thanks.
Hi Balaji,
Any update for me is appreciated. Thanks.