Hologres:CREATE TABLE

SELECT hg_dump_script('public.dwd_trade_orders');

INSERT INTO public.dwd_trade_orders
    (order_id, shop_id, user_id, order_amount, payment, payment_type, is_delivered, dt, order_time)
VALUES
    (50001, 101, 'U678', 299.00, 280.00, 1, true,  '20231101', '2023-11-01 10:00:01+08'),
    (50002, 102, 'U992',  59.00,  59.00, 2, false, '20231101', '2023-11-01 10:05:12+08'),
    (50003, 101, 'U441', 150.00, 145.00, 1, true,  '20231101', '2023-11-01 10:10:45+08'),
    (50004, 105, 'U219', 888.00, 888.00, 3, true,  '20231101', '2023-11-01 10:20:11+08'),
    (50005, 102, 'U883',  35.00,  30.00, 1, false, '20231101', '2023-11-01 10:32:00+08'),
    (50006, 110, 'U007', 120.50, 120.50, 2, true,  '20231101', '2023-11-01 10:45:33+08'),
    (50007, 101, 'U321', 210.00, 210.00, 1, true,  '20231101', '2023-11-01 11:02:19+08'),
    (50008, 108, 'U556',  45.00,  45.00, 2, false, '20231101', '2023-11-01 11:15:04+08'),
    (50009, 101, 'U112', 300.00, 290.00, 3, true,  '20231101', '2023-11-01 11:25:55+08'),
    (50010, 105, 'U449',  99.90,  99.90, 1, true,  '20231101', '2023-11-01 11:40:22+08');

-- Calculate the total transaction amount per shop, sorted in descending order.
SELECT
    shop_id,
    COUNT(1)     AS total_orders,
    SUM(payment) AS total_payment
FROM public.dwd_trade_orders
GROUP BY shop_id
ORDER BY total_payment DESC;

shop_id  total_orders  total_payment
105      2             987.90
101      4             925.00
110      1             120.50
102      1              59.00
108      1              45.00

BEGIN;

CREATE TABLE [IF NOT EXISTS] [schema_name.]table_name (
    {
        column_name column_type [column_constraints, [...]]
        | table_constraints
        [,...]
    }
)
[WITH (
    property = 'value'
    [, ...]
)];

[COMMENT ON COLUMN <[schema_name.]tablename.column> IS '<value>';]
[COMMENT ON TABLE  <[schema_name.]tablename>        IS '<value>';]

COMMIT;

BEGIN;

CREATE TABLE [IF NOT EXISTS] [schema_name.]table_name (
    {
        column_name column_type [column_constraints, [...]]
        | table_constraints
        [,...]
    }
);

CALL set_table_property('[schema_name.]<table_name>', '<property>', '<value>');
COMMENT ON COLUMN <[schema_name.]tablename.column> IS '<value>';
COMMENT ON TABLE  <[schema_name.]tablename>        IS '<value>';

COMMIT;

Organização de dados (não modificável após a criação)

Aceleração por índice (modificável após a criação)

Tanto bitmap_columns quanto dictionary_encoding_columns podem ser modificados após a criação da tabela por meio do comando ALTER TABLE.

Sintaxe de dictionary_encoding_columns:

CALL set_table_property('table_name', 'dictionary_encoding_columns', '[columnName{:[on|off|auto]}[,...]]');

Ciclo de vida dos dados

Observações sobre time_to_live_in_seconds:

• O TTL não é aplicado em um horário exato. Após a expiração, os dados são excluídos dentro de uma janela de tempo, e não em um momento preciso.

• Apenas os dados são excluídos; a tabela permanece intacta.

• O uso de TTL pode causar chaves primárias duplicadas ou resultados de consulta inconsistentes após a exclusão.

• Para gerenciar o ciclo de vida de dados em produção, prefira tabelas particionadas. Consulte CREATE PARTITION TABLE.

• A partir do Hologres V4.2, para tabelas com chave primária, a configuração de TTL é restringida pelo parâmetro GUC hg_time_to_live_in_days_min_value. Este parâmetro é medido em dias, tem valor padrão de 36500 (100 anos) e só pode ser modificado por um Superusuário. Ao definir o TTL de uma tabela com chave primária via CREATE TABLE, ALTER TABLE, SET_TABLE_PROPERTY ou REBUILD, o sistema verifica se o valor atende ao requisito mínimo (deve ser maior ou igual ao número de dias correspondente a hg_time_to_live_in_days_min_value). Tabelas sem chave primária não sofrem essa restrição.

Se não definido, o TTL padrão é de 100 anos (efetivamente sem expiração).

Sintaxe de time_to_live_in_seconds:

CALL set_table_property('table_name', 'time_to_live_in_seconds', '<non_negative_literal>');

Sintaxe de storage_mode:

-- Set storage mode at table creation:
CREATE TABLE <table_name> (...) WITH (storage_mode = 'hot');
CREATE TABLE <table_name> (...) WITH (storage_mode = 'cold');

-- Set storage mode after table creation:
CALL set_table_property('table_name', 'storage_mode', 'hot');
CALL set_table_property('table_name', 'storage_mode', 'cold');

BEGIN;

-- Create the partitioned parent table.
-- The primary key includes both the business key (order_id) and the partition key (dt).
CREATE TABLE IF NOT EXISTS public.dwd_trade_orders_partitioned (
    order_id      BIGINT NOT NULL,
    shop_id       INT NOT NULL,
    user_id       TEXT NOT NULL,
    order_amount  NUMERIC(12, 2) DEFAULT 0.00,
    payment       NUMERIC(12, 2) DEFAULT 0.00,
    payment_type  INT DEFAULT 0,
    is_delivered  BOOLEAN DEFAULT false,
    dt            TEXT NOT NULL,
    order_time    TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (order_id, dt)
)
PARTITION BY LIST (dt)
WITH (
    orientation       = 'column',
    distribution_key  = 'order_id',
    event_time_column = 'order_time',
    clustering_key    = 'order_time:asc'
);

COMMIT;

-- Create a child table to provide physical storage for a specific date.
CREATE TABLE IF NOT EXISTS public.dwd_trade_orders_20231101
    PARTITION OF public.dwd_trade_orders_partitioned FOR VALUES IN ('20231101');

-- Insert data. The application logic is identical to the flat table.
-- Data is automatically routed to the correct child table.
INSERT INTO public.dwd_trade_orders_partitioned
    (order_id, shop_id, user_id, order_amount, payment, payment_type, is_delivered, dt, order_time)
VALUES
    (50001, 101, 'U678', 299.00, 280.00, 1, true,  '20231101', '2023-11-01 10:00:01+08'),
    (50002, 102, 'U992',  59.00,  59.00, 2, false, '20231101', '2023-11-01 10:05:12+08'),
    (50003, 101, 'U441', 150.00, 145.00, 1, true,  '20231101', '2023-11-01 10:10:45+08'),
    (50004, 105, 'U219', 888.00, 888.00, 3, true,  '20231101', '2023-11-01 10:20:11+08'),
    (50005, 102, 'U883',  35.00,  30.00, 1, false, '20231101', '2023-11-01 10:32:00+08'),
    (50006, 110, 'U007', 120.50, 120.50, 2, true,  '20231101', '2023-11-01 10:45:33+08'),
    (50007, 101, 'U321', 210.00, 210.00, 1, true,  '20231101', '2023-11-01 11:02:19+08'),
    (50008, 108, 'U556',  45.00,  45.00, 2, false, '20231101', '2023-11-01 11:15:04+08'),
    (50009, 101, 'U112', 300.00, 290.00, 3, true,  '20231101', '2023-11-01 11:25:55+08'),
    (50010, 105, 'U449',  99.90,  99.90, 1, true,  '20231101', '2023-11-01 11:40:22+08');

-- Query with a partition filter. Hologres scans only the matching child table.
SELECT COUNT(*) FROM public.dwd_trade_orders_partitioned WHERE dt = '20231101';

-- Drop a child table to reclaim space in seconds (much faster than DELETE).
-- DROP TABLE public.dwd_trade_orders_20231101;

BEGIN;

CREATE TABLE IF NOT EXISTS public.dwd_order_summary (
    order_id    BIGINT PRIMARY KEY,
    category_id INT NOT NULL,
    gmv         NUMERIC(15, 2),
    order_time  TIMESTAMPTZ NOT NULL
) WITH (
    orientation       = 'column',           -- Best for large-scale aggregation; high compression, efficient column scans
    distribution_key  = 'order_id',         -- Even distribution; enables local joins with other order tables
    event_time_column = 'order_time',       -- Enables file-level segment clipping for time range filters
    clustering_key    = 'order_time:asc'    -- Reduces disk I/O for "last hour" or "specific day" queries
);

COMMENT ON TABLE  public.dwd_order_summary            IS 'Fact table for order summary details.';
COMMENT ON COLUMN public.dwd_order_summary.order_id   IS 'The unique order ID.';
COMMENT ON COLUMN public.dwd_order_summary.category_id IS 'The category ID.';
COMMENT ON COLUMN public.dwd_order_summary.gmv        IS 'The gross merchandise volume.';
COMMENT ON COLUMN public.dwd_order_summary.order_time IS 'The time when the order was placed.';

COMMIT;

BEGIN;

CREATE TABLE IF NOT EXISTS public.dim_user_persona (
    user_id       TEXT PRIMARY KEY,
    user_level    INT,
    persona_jsonb JSONB
) WITH (
    orientation = 'row'
    -- For row-oriented tables, the primary key is automatically used as the distribution key
    -- and clustering key. No additional settings are required.
);

COMMENT ON TABLE  public.dim_user_persona              IS 'Dimension table for user profiles.';
COMMENT ON COLUMN public.dim_user_persona.user_id      IS 'The unique user ID.';
COMMENT ON COLUMN public.dim_user_persona.user_level   IS 'The user level.';
COMMENT ON COLUMN public.dim_user_persona.persona_jsonb IS 'The user profile features in JSON format.';

COMMIT;

BEGIN;

CREATE TABLE IF NOT EXISTS public.ads_shipping_info (
    order_id         BIGINT PRIMARY KEY,
    shipping_status  INT,
    receiver_address TEXT,
    update_time      TIMESTAMPTZ
) WITH (
    orientation      = 'row,column',      -- Supports both point queries and aggregation
    distribution_key = 'order_id',        -- Controls data distribution across shards
    bitmap_columns   = 'shipping_status'  -- Accelerates "status = X" filter queries on a low-cardinality column
);

COMMENT ON TABLE  public.ads_shipping_info                  IS 'Application table for logistics status queries.';
COMMENT ON COLUMN public.ads_shipping_info.order_id         IS 'The order ID.';
COMMENT ON COLUMN public.ads_shipping_info.shipping_status  IS 'Logistics status (1: To be shipped, 2: In transit, 3: Delivered).';
COMMENT ON COLUMN public.ads_shipping_info.receiver_address IS 'The shipping address.';

COMMIT;

-- Single escaped column
BEGIN;
CREATE TABLE tbl (c1 INT NOT NULL);
CALL set_table_property('tbl', 'clustering_key', '"c1":asc');
COMMIT;

-- Multiple columns, including an uppercase one (V2.1 and later)
BEGIN;
CREATE TABLE tbl ("C1" INT NOT NULL, c2 TEXT NOT NULL) WITH (clustering_key = '"C1",c2');
COMMIT;

-- Multiple columns, including an uppercase one (V2.0 and later)
BEGIN;
CREATE TABLE tbl ("C1" INT NOT NULL, c2 TEXT NOT NULL);
CALL set_table_property('tbl', 'clustering_key', '"C1",c2');
COMMIT;

BEGIN;
CREATE TABLE tbl (c1 INT NOT NULL);
CALL set_table_property('tbl', 'clustering_key', '"c1:asc"');
COMMIT;

-- Multiple columns, including an uppercase one
BEGIN;
CREATE TABLE tbl ("C1" INT NOT NULL, c2 TEXT NOT NULL);
CALL set_table_property('tbl', 'clustering_key', '"C1,c2"');
COMMIT;

-- Enable the old syntax at the session level.
SET hg_disable_parse_holo_property = on;

-- Enable the old syntax at the database level.
ALTER DATABASE <db_name> SET hg_disable_parse_holo_property = on;