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;

Organisation des données (non modifiable après création)

Accélération par index (modifiable après création)

Les propriétés bitmap_columns et dictionary_encoding_columns peuvent être modifiées après la création de la table via l'instruction ALTER TABLE.

Syntaxe de dictionary_encoding_columns :

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

Cycle de vie des données

Notes concernant time_to_live_in_seconds :

• Le TTL n'est pas appliqué à un instant précis. Après l'expiration du TTL, les données sont supprimées dans une fenêtre de temps, et non à un moment exact.

• Seules les données sont supprimées ; la table elle-même reste intacte.

• Le TTL peut entraîner des clés primaires en double ou des résultats de requête incohérents après la suppression.

• Pour la gestion du cycle de vie des données de production, utilisez plutôt des tables partitionnées. Consultez l'instruction CREATE PARTITION TABLE.

• À partir de Hologres V4.2, pour les tables dotées d'une clé primaire, le paramètre TTL est contraint par le paramètre GUC hg_time_to_live_in_days_min_value. Ce paramètre est exprimé en jours, avec une valeur par défaut de 36 500 (100 ans), et ne peut être modifié que par un Superuser. Lorsque vous définissez le TTL d'une table avec clé primaire via CREATE TABLE, ALTER TABLE, SET_TABLE_PROPERTY ou REBUILD, le système vérifie que la valeur respecte l'exigence minimale (elle doit être supérieure ou égale au nombre de jours correspondant à hg_time_to_live_in_days_min_value). Les tables sans clé primaire ne sont pas affectées par cette contrainte.

S'il n'est pas défini, le TTL par défaut est de 100 ans (aucune expiration effective).

Syntaxe de time_to_live_in_seconds :

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

Syntaxe 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;