REFRESH MATERIALIZED VIEW mv_fact_insights_market_product_daily_poc;

information_schema.task_runs

query_id

sql
SELECT 
    TASK_NAME,
    CREATE_TIME,
    FINISH_TIME,
    STATE,
    PROGRESS,
    ERROR_MESSAGE
FROM information_schema.task_runs 
WHERE QUERY_ID = '2fb1ac42-7c35-11f1-9595-8a5cd1361ab8';

RUNNING

SUCCESS

FAILED

STATE

SUCCESS

sql
-- This will only work if the profile has been captured (profile_level >= 1)
SHOW PROFILE FOR '2fb1ac42-7c35-11f1-9595-8a5cd1361ab8';

ERROR_MESSAGE

task_runs

fe.log

query_id

<http://be.INFO|be.INFO>

SUCCESS

task_runs

sql
SHOW MATERIALIZED VIEWS WHERE NAME = 'mv_fact_insights_market_product_daily_poc';

last_refresh_time

is_active

mv_fact_insights_market_product_daily_poc

date

market_product_id

master_product_id

marketplace_id

customer_id

vendor_id

customer_type

customer_state_id

estimated_total_page_views

estimated_total_page_views_non_china

estimated_total_sold_units

estimated_page_views_lost_to_suppression

estimated_lost_suppression_sales

estimated_lost_suppression_sales_aed

estimated_lost_suppression_sales_ars

estimated_lost_suppression_sales_aud

estimated_lost_suppression_sales_cad

estimated_lost_suppression_sales_cny

estimated_lost_suppression_sales_eur

estimated_lost_suppression_sales_gbp

estimated_lost_suppression_sales_jpy

estimated_lost_suppression_sales_usd

avg_rating

reviews_count

estimated_lost_suppression_sales_krw

estimated_lost_suppression_sales_pln

estimated_lost_suppression_sales_sek

estimated_total_sales

estimated_total_sales_aed

estimated_total_sales_ars

estimated_total_sales_aud

estimated_total_sales_cad

estimated_total_sales_cny

estimated_total_sales_eur

estimated_total_sales_gbp

estimated_total_sales_jpy

estimated_total_sales_usd

estimated_total_sales_krw

estimated_total_sales_pln

estimated_total_sales_sek

customer_id

fact_insights_market_product_daily_poc

date

fact_insights_market_product_daily_poc

market_product_id

fact_insights_market_product_daily_poc

master_product_id

fact_insights_market_product_daily_poc

marketplace_id

fact_insights_market_product_daily_poc

customer_id

fact_insights_market_product_daily_poc

vendor_id

fact_insights_market_product_daily_poc

customer_type

fact_insights_market_product_daily_poc

customer_state_id

fact_insights_market_product_daily_poc

estimated_total_page_views

fact_insights_market_product_daily_poc

estimated_total_page_views_non_china

fact_insights_market_product_daily_poc

estimated_total_sold_units

fact_insights_market_product_daily_poc

estimated_page_views_lost_to_suppression

fact_insights_market_product_daily_poc

estimated_lost_suppression_sales

fact_insights_market_product_daily_poc

estimated_lost_suppression_sales_aed

fact_insights_market_product_daily_poc

estimated_lost_suppression_sales_ars

fact_insights_market_product_daily_poc

estimated_lost_suppression_sales_aud

fact_insights_market_product_daily_poc

estimated_lost_suppression_sales_cad

fact_insights_market_product_daily_poc

estimated_lost_suppression_sales_cny

fact_insights_market_product_daily_poc

estimated_lost_suppression_sales_eur

fact_insights_market_product_daily_poc

estimated_lost_suppression_sales_gbp

fact_insights_market_product_daily_poc

estimated_lost_suppression_sales_jpy

fact_insights_market_product_daily_poc

estimated_lost_suppression_sales_usd

fact_insights_market_product_daily_poc

avg_rating

fact_insights_market_product_daily_poc

reviews_count

fact_insights_market_product_daily_poc

estimated_lost_suppression_sales_krw

fact_insights_market_product_daily_poc

estimated_lost_suppression_sales_pln

fact_insights_market_product_daily_poc

estimated_lost_suppression_sales_sek

fact_insights_market_product_daily_poc

estimated_total_sales

fact_insights_market_product_daily_poc

estimated_total_sales_aed

fact_insights_market_product_daily_poc

estimated_total_sales_ars

fact_insights_market_product_daily_poc

estimated_total_sales_aud

fact_insights_market_product_daily_poc

estimated_total_sales_cad

fact_insights_market_product_daily_poc

estimated_total_sales_cny

fact_insights_market_product_daily_poc

estimated_total_sales_eur

fact_insights_market_product_daily_poc

estimated_total_sales_gbp

fact_insights_market_product_daily_poc

estimated_total_sales_jpy

fact_insights_market_product_daily_poc

estimated_total_sales_usd

fact_insights_market_product_daily_poc

estimated_total_sales_krw

fact_insights_market_product_daily_poc

estimated_total_sales_pln

fact_insights_market_product_daily_poc

estimated_total_sales_sek

iceberg_adhoc

adhoc

[E1014]Got EOF of Socket... [E112]Not connected to 10.97.47.228:8060 yet

10.97.47.228

8060

EOF

UNPARTITIONED

SHOW BACKENDS;

10.97.47.228

Alive

dmesg -T | grep -i oom

storage/log/be.INFO

be.out

16:00:00

No viable statement for input 'SHOW PROFILE'

sql
    SHOW PROFILELIST;

sql
    ANALYZE PROFILE FROM '2fb1ac42-7c35-11f1-9595-8a5cd1361ab8';

information_schema.task_runs

STATE

SUCCESS

FAILED

0%

UNPARTITIONED

PARTITION BY date

mem_limit

be_process_mem_limit

be.conf

REFRESH MATERIALIZED VIEW ... PARTITION START ("...") END ("...")

SELECT *

date

sql
CREATE MATERIALIZED VIEW mv_fact_insights_market_product_daily_poc
PARTITION BY `date`  -- Use the same partition column as your Iceberg table
DISTRIBUTED BY HASH(customer_id)
REFRESH MANUAL
PROPERTIES (
    "session.enable_spill" = "true",      -- Enables spilling to disk if memory is low
    "session.insert_timeout" = "28800",   -- Increases timeout to 8 hours for large initial load
    "replication_num" = "1"
)
AS 
SELECT * FROM iceberg_adhoc.adhoc.fact_insights_market_product_daily_poc;

REFRESH

sql
-- Refresh only a specific range of dates
REFRESH MATERIALIZED VIEW mv_fact_insights_market_product_daily_poc 
PARTITION START ("2024-01-01") END ("2024-01-31");

"session.enable_spill" = "true"

default_mv_wg

sql
SHOW RESOURCE GROUPS;

sql
ALTER MATERIALIZED VIEW mv_fact_insights_market_product_daily_poc SET ("resource_group" = "your_large_job_rg");

Got EOF of Socket

10.97.47.228

customer_id

customer_id

sql
CREATE MATERIALIZED VIEW mv_fact_insights_market_product_daily_poc
-- 1. Partition by the same column as Iceberg to enable incremental sync
PARTITION BY (customer_id)
-- 2. Distribute by the same key for optimized joins/aggregation
DISTRIBUTED BY HASH(customer_id)
REFRESH MANUAL
PROPERTIES (
    -- 3. Safety: only refresh 5-10 partitions at a time to prevent OOM
    "partition_refresh_number" = "10",
    -- 4. Set a high timeout for the initial massive load (8 hours)
    "session.insert_timeout" = "28800",
    -- 5. Enable spill to disk to handle memory pressure
    "session.enable_spill" = "true",
    "replication_num" = "1"
)
AS 
SELECT 
    `date`, `market_product_id`, `master_product_id`, `marketplace_id`, 
    `customer_id`, `vendor_id`, `customer_type`, `customer_state_id`,
    -- ... (other columns)
FROM iceberg_adhoc.adhoc.fact_insights_market_product_daily_poc;

partition_refresh_number

10

REFRESH...

sql
REFRESH MATERIALIZED VIEW mv_fact_insights_market_product_daily_poc;

"partition_refresh_number" = "10"

sql
REFRESH MATERIALIZED VIEW mv_fact_insights_market_product_daily_poc 
PARTITIONS (customer_id_val1, customer_id_val2, ...);

sql
    SELECT * FROM information_schema.task_runs
    WHERE task_name = 'mv-29457' -- Use your task ID
    ORDER BY create_time DESC;

PROGRESS

sql
    SELECT count(*) FROM mv_fact_insights_market_product_daily_poc;

customer_id

customer_id

customer_id

date

INSERT OVERWRITE

"partition_refresh_number" = "N"

N

5

10

"session.enable_spill" = "true"

PARTITIONED BY (customer_id)

customer_id

customer_id

customer_id

customer_id

date

sql
CREATE MATERIALIZED VIEW mv_fact_insights_market_product_daily_poc
-- Partition by date to manage the 500M rows in time-chunks
PARTITION BY (`date`) 
DISTRIBUTED BY HASH(customer_id)
REFRESH ASYNC EVERY (INTERVAL 1 HOUR) -- Matches your source refresh rate
PROPERTIES (
    "partition_refresh_number" = "1",      -- Refresh 1 day at a time
    "session.enable_spill" = "true",       -- Safety net for OOM
    "session.insert_timeout" = "3600",     -- 1 hour timeout per batch
    "replication_num" = "1"
)
AS 
SELECT * FROM iceberg_adhoc.adhoc.fact_insights_market_product_daily_poc;

Duplicate Key

INSERT INTO buffer_table SELECT * FROM iceberg_table

sql
    ALTER TABLE main_table SWAP WITH buffer_table;

partition_refresh_number = 1

SELECT * FROM information_schema.task_runs WHERE task_name = '...'

sql
SELECT 
    TASK_NAME,
    CREATE_TIME,
    FINISH_TIME,
    -- Total duration in seconds
    timestampdiff(SECOND, CREATE_TIME, FINISH_TIME) as duration_sec,
    STATE,
    PROGRESS, -- This will show 0-100%
    ERROR_MESSAGE
FROM information_schema.task_runs
WHERE TASK_NAME LIKE (
    SELECT concat('mv-', TABLE_ID) 
    FROM information_schema.tables 
    WHERE table_name = 'mv_fact_insights_market_product_daily_poc'
)
ORDER BY CREATE_TIME DESC;

sql
    SHOW MATERIALIZED VIEWS WHERE NAME = 'mv_fact_insights_market_product_daily_poc';

is_active

last_refresh_status

date

customer_id

task_runs

sql
DROP MATERIALIZED VIEW IF EXISTS mv_fact_insights_market_product_daily_poc;

sql
CREATE MATERIALIZED VIEW mv_fact_insights_market_product_daily_poc
-- 1. Partition by date to enable batched processing of the 500M rows
PARTITION BY (`date`)
DISTRIBUTED BY HASH(customer_id)
-- 2. Schedule to match your Iceberg refresh cycle
REFRESH ASYNC EVERY (INTERVAL 1 HOUR)
PROPERTIES (
    -- 3. Safety: Process only 1 partition (day) at a time to stay under RAM limits
    "partition_refresh_number" = "1",
    -- 4. Safety: Spill to S3 if memory usage gets too high
    "session.enable_spill" = "true",
    -- 5. Increase timeout for the massive initial load (8 hours)
    "session.insert_timeout" = "28800",
    "replication_num" = "1",
    -- 6. Important for shared-data: Data is stored in your S3 bucket
    "storage_volume" = "builtin_storage_volume" 
)
AS 
SELECT 
    `date`, `market_product_id`, `master_product_id`, `marketplace_id`, 
    `customer_id`, `vendor_id`, `customer_type`, `customer_state_id`,
    `estimated_total_page_views`, `estimated_total_page_views_non_china`,
    `estimated_total_sold_units`, `estimated_page_views_lost_to_suppression`,
    `estimated_lost_suppression_sales`, `estimated_lost_suppression_sales_aed`,
    `estimated_lost_suppression_sales_ars`, `estimated_lost_suppression_sales_aud`,
    `estimated_lost_suppression_sales_cad`, `estimated_lost_suppression_sales_cny`,
    `estimated_lost_suppression_sales_eur`, `estimated_lost_suppression_sales_gbp`,
    `estimated_lost_suppression_sales_jpy`, `estimated_lost_suppression_sales_usd`,
    `avg_rating`, `reviews_count`, `estimated_lost_suppression_sales_krw`,
    `estimated_lost_suppression_sales_pln`, `estimated_lost_suppression_sales_sek`,
    `estimated_total_sales`, `estimated_total_sales_aed`, `estimated_total_sales_ars`,
    `estimated_total_sales_aud`, `estimated_total_sales_cad`, `estimated_total_sales_cny`,
    `estimated_total_sales_eur`, `estimated_total_sales_gbp`, `estimated_total_sales_jpy`,
    `estimated_total_sales_usd`, `estimated_total_sales_krw`, `estimated_total_sales_pln`,
    `estimated_total_sales_sek`
FROM iceberg_adhoc.adhoc.fact_insights_market_product_daily_poc;

sql
SELECT 
    TASK_NAME,
    CREATE_TIME,
    STATE,
    PROGRESS,
    ERROR_MESSAGE
FROM information_schema.task_runs
WHERE TASK_NAME LIKE (
    SELECT concat('mv-', TABLE_ID) 
    FROM information_schema.tables 
    WHERE table_name = 'mv_fact_insights_market_product_daily_poc'
)
ORDER BY CREATE_TIME DESC 
LIMIT 5;

sql
SELECT 
    TASK_NAME,
    CREATE_TIME,
    FINISH_TIME,
    -- Duration in minutes
    timestampdiff(MINUTE, CREATE_TIME, FINISH_TIME) as duration_minutes,
    STATE
FROM information_schema.task_runs
WHERE STATE = 'SUCCESS'
AND TASK_NAME LIKE 'mv-%'
ORDER BY CREATE_TIME DESC;

customer_id

customer_id

customer_id

customer_id

partition_refresh_number

sql
CREATE MATERIALIZED VIEW mv_fact_insights_market_product_daily_poc
PARTITION BY (customer_id) -- Must match Iceberg partition column
DISTRIBUTED BY HASH(customer_id)
REFRESH ASYNC EVERY (INTERVAL 1 HOUR)
PROPERTIES (
    "partition_refresh_number" = "10", -- Refreshes 10 customers at a time
    "session.enable_spill" = "true",
    "replication_num" = "1"
)
AS 
SELECT * FROM iceberg_catalog.adhoc.fact_insights_market_product_daily_poc;

customer_id

date

sql
CREATE TABLE fact_insights_market_product_daily_poc (
    `date` DATE,
    `customer_id` BIGINT,
    ... -- other columns
)
PARTITION BY (`date`) -- You ARE allowed to partition by date here!
DISTRIBUTED BY HASH(customer_id)
PROPERTIES ("replication_num" = "1");

sql
-- This task clears and reloads the data every hour
CREATE TASK refresh_iceberg_data
WAREHOUSE = 'your_warehouse_name'
SCHEDULE = '0 * * * *' -- Every hour
AS
INSERT OVERWRITE fact_insights_market_product_daily_poc 
SELECT * FROM iceberg_catalog.adhoc.fact_insights_market_product_daily_poc;

customer_id

customer_id

date

INSERT OVERWRITE

sql
SELECT 
    TASK_NAME,
    CREATE_TIME,
    FINISH_TIME,
    timestampdiff(MINUTE, CREATE_TIME, FINISH_TIME) as duration_minutes,
    STATE,
    ERROR_MESSAGE
FROM information_schema.task_runs
ORDER BY CREATE_TIME DESC LIMIT 10;

TABLE_ID

information_schema.tables

information_schema.tables_config

information_schema.materialized_views

TASK_NAME

sql
SELECT 
    tr.TASK_NAME,
    tr.CREATE_TIME,
    tr.FINISH_TIME,
    tr.STATE,
    tr.PROGRESS,
    tr.ERROR_MESSAGE
FROM information_schema.task_runs tr
JOIN information_schema.materialized_views mv 
  ON tr.TASK_NAME = mv.TASK_NAME
WHERE mv.TABLE_NAME = 'mv_fact_insights_market_product_daily_poc'
ORDER BY tr.CREATE_TIME DESC
LIMIT 5;

sql
SELECT 
    TABLE_NAME,
    IS_ACTIVE,
    LAST_REFRESH_STATE,
    LAST_REFRESH_START_TIME,
    LAST_REFRESH_FINISHED_TIME,
    LAST_REFRESH_DURATION as duration_seconds,
    LAST_REFRESH_ERROR_MESSAGE
FROM information_schema.materialized_views
WHERE TABLE_NAME = 'mv_fact_insights_market_product_daily_poc';

information_schema.materialized_views

TASK_NAME

LAST_REFRESH_...

RUNNING

IS_ACTIVE

task_runs

sql
SELECT 
    TASK_NAME,
    CREATE_TIME,
    STATE,
    PROGRESS, -- This will show percentage or status like 'RUNNING'
    EXTRA_MESSAGE -- Often contains specific info about the current partition being processed
FROM information_schema.task_runs
WHERE STATE = 'RUNNING'
  AND TASK_NAME = (
      SELECT TASK_NAME 
      FROM information_schema.materialized_views 
      WHERE TABLE_NAME = 'mv_fact_insights_market_product_daily_poc'
  )
ORDER BY CREATE_TIME DESC;

PROGRESS

PROGRESS

partition_refresh_number

EXTRA_MESSAGE

sql
-- Replace 'mv-12345' with the actual TASK_NAME from the query in Step 1
CANCEL TASK RUN FOR mv-xxxxxx;

p44

p36

p46

"partition_refresh_number" = "10"

customer_id

planBuilderMessage

customer_id >= 30 AND customer_id < 31

planBuilderMessage

customer_id

default_warehouse

p30

p32

customer_id

RUNNING

FAILED

partition_refresh_number

10

5

2

PROGRESS

mvPartitionsToRefresh