-- Create the entire table as a cold table.
CREATE TABLE archive_log (
  id BIGINT PRIMARY KEY,
  log_time DATETIME,
  payload TEXT
) STORAGE_TIER = OBJECT_STORAGE;

-- Explicitly declare the use of local storage (equivalent to not specifying it).
CREATE TABLE hot_order (
  id BIGINT PRIMARY KEY,
  amount DECIMAL(10,2)
) STORAGE_TIER = LOCAL_STORAGE;

CREATE TABLE order_history (
  id BIGINT,
  order_date DATE,
  amount DECIMAL(10,2),
  PRIMARY KEY (id, order_date)
)
PARTITION BY RANGE COLUMNS (order_date) (
  PARTITION p_2023 VALUES LESS THAN ('2024-01-01') STORAGE_TIER = OBJECT_STORAGE,
  PARTITION p_2024 VALUES LESS THAN ('2025-01-01') STORAGE_TIER = OBJECT_STORAGE,
  PARTITION p_2025 VALUES LESS THAN ('2026-01-01') STORAGE_TIER = LOCAL_STORAGE
);

ALTER TABLE archive_log STORAGE_TIER = OBJECT_STORAGE;

ALTER TABLE order_history
  MODIFY PARTITION (p_2024) STORAGE_TIER = OBJECT_STORAGE;

SELECT
  node_name,
  rep_group_id,
  stage,
  uploaded_file_num,
  total_file_num,
  progress_pct,
  elapsed_time_ms,
  err_msg
FROM information_schema.TDSTORE_TIERING_COLD_RG_JOB_PROGRESS
ORDER BY rep_group_id;

-- View the approximate data volume for each cold table index/partition
SELECT
  TABLE_SCHEMA,
  TABLE_NAME,
  PARTITION_NAME,
  INDEX_NAME,
  CACHE_DB_SIZE,
  DURABLE_DB_SIZE,
  TOTAL_SIZE
FROM information_schema.TDSTORE_TIERED_STORAGE_USAGE;

-- Aggregate by table
SELECT
  TABLE_SCHEMA,
  TABLE_NAME,
  SUM(CACHE_DB_SIZE)   AS cache_total,
  SUM(DURABLE_DB_SIZE) AS durable_total
FROM information_schema.TDSTORE_TIERED_STORAGE_USAGE
GROUP BY TABLE_SCHEMA, TABLE_NAME;