Hologres:CREATE TABLE

Preparations

• Purchase a Hologres instance.

• You have created a database.

• Connect to the target Hologres database. We recommend that you connect to Hologres using HoloWeb and run queries.

Create a table

We recommend that you use the CREATE TABLE WITH syntax, which is available in Hologres V2.1 and later. The following example creates a transaction details table that follows hierarchical naming conventions, includes various fields, and has comprehensive comments.

BEGIN;

-- Create a transaction details fact table (standard non-partitioned table)
-- Explicitly specify the public schema and use hierarchical naming (dwd_xxx) in the table name
CREATE TABLE IF NOT EXISTS public.dwd_trade_orders (
    order_id BIGINT NOT NULL,           -- Unique order ID
    shop_id INT NOT NULL,              -- Shop ID
    user_id TEXT NOT NULL,             -- User ID
    order_amount NUMERIC(12, 2) DEFAULT 0.00,       -- Order amount
    payment NUMERIC(12, 2) DEFAULT 0.00,         -- Amount paid
    payment_type INT DEFAULT 0,                     -- Payment type (0: unpaid, 1: Alipay, 2: WeChat, 3: credit card)
    is_delivered BOOLEAN DEFAULT false,             -- Whether shipped
    dt TEXT NOT NULL,                               -- Data timestamp
    order_time TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, -- Order time
    PRIMARY KEY (order_id)                          -- Set primary key to ensure data uniqueness
)
WITH (
    orientation = 'column',                         -- Use column store: ideal for real-time analytics (OLAP) on massive datasets
    distribution_key = 'order_id',                  -- Set distribution key: data is sharded by order_id
    clustering_key = 'order_time:asc',              -- Set clustering key: data is sorted in ascending order by time within files
    event_time_column = 'order_time',               -- Set segment key: enables file-level pruning to quickly filter time ranges
    bitmap_columns = 'shop_id,payment_type,is_delivered', -- Set bitmap index: accelerates equality filters on low-cardinality columns
    dictionary_encoding_columns = 'user_id:auto'    -- Set dictionary encoding: speeds up aggregation and filtering on string columns
);

-- Add metadata comments
COMMENT ON TABLE public.dwd_trade_orders IS 'Base fact table for transaction order details';
COMMENT ON COLUMN public.dwd_trade_orders.order_id IS 'Unique order ID';
COMMENT ON COLUMN public.dwd_trade_orders.shop_id IS 'Unique shop ID';
COMMENT ON COLUMN public.dwd_trade_orders.user_id IS 'Purchaser user ID';
COMMENT ON COLUMN public.dwd_trade_orders.dt IS 'Data timestamp, format YYYYMMDD';
COMMENT ON COLUMN public.dwd_trade_orders.order_time IS 'Exact timestamp when the order was created';

COMMIT;

Insert data

Hologres supports standard Data Manipulation Language (DML) syntax. The following example inserts 10 rows of simulated production data into the table.

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');

Query data

-- Calculate total payment per shop and sort by amount 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;

The query returns the following result:

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

Table creation syntax

Hologres provides two syntaxes for setting table properties and adding comments:

• Standard syntax (recommended for V2.1 and later)

  You can use the WITH clause to define properties. This syntax is more compact and provides better performance. You can add comments after the CREATE TABLE statement. For a complete example, see Create a table.

  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;

• Compatibility syntax (supported in all versions)

  You can use the CALL statement with set_table_property to set properties and the COMMENT statement to add comments. These statements must be in the same transaction block (BEGIN...COMMIT) as the CREATE TABLE statement.

  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;

Table properties

Example: Real-time analytics on massive data (fact table)

Scenario description: This type of table is used for real-time dashboard aggregation. These tables typically hold massive amounts of data. The core requirement is to achieve high-performance statistical aggregation, such as calculating Gross Merchandise Volume (GMV) or order volume.

BEGIN;

-- Explicitly specify the public schema
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',             -- Storage format: column store. Best for massive aggregation analytics, offering high compression and efficient column scans
    distribution_key = 'order_id',      -- Distribution key: shards data by order_id to ensure even distribution; enables Local Join when joining other order tables
    event_time_column = 'order_time',   -- Segment key: usually set to a time field. The system segments files by this field to accelerate time-range filtering
    clustering_key = 'order_time:asc'   -- Clustering key: physically sorts data in ascending time order. Reduces disk I/O when querying data from "the last hour" or "a specific day"
);

-- Add metadata comments to support future data governance
COMMENT ON TABLE public.dwd_order_summary IS 'Order summary fact table';
COMMENT ON COLUMN public.dwd_order_summary.order_id IS 'Unique order ID';
COMMENT ON COLUMN public.dwd_order_summary.category_id IS 'Category ID';
COMMENT ON COLUMN public.dwd_order_summary.gmv IS 'Gross merchandise value';
COMMENT ON COLUMN public.dwd_order_summary.order_time IS 'Order time';

COMMIT;

Example: High-concurrency point lookup by primary key (dimension table)

Scenario description: This type of table is used to retrieve user personas by user_id in milliseconds. These tables are designed to provide ultra-low latency responses under high queries per second (QPS).

BEGIN;

-- Explicitly specify the public schema
CREATE TABLE IF NOT EXISTS public.dim_user_persona (
    user_id TEXT PRIMARY KEY,           -- Primary key: essential for point lookup scenarios
    user_level INT,
    persona_jsonb JSONB 
) WITH (
    orientation = 'row'                 -- Storage format: row store. Optimized specifically for primary key point lookups (Point Lookup), with millisecond response times
    -- Note: Row store tables automatically use the primary key as the distribution key and clustering key. No additional settings are needed.
);

-- Add metadata comments
COMMENT ON TABLE public.dim_user_persona IS 'User persona dimension table';
COMMENT ON COLUMN public.dim_user_persona.user_id IS 'Unique user ID';
COMMENT ON COLUMN public.dim_user_persona.user_level IS 'User level';
COMMENT ON COLUMN public.dim_user_persona.persona_jsonb IS 'User profile features in JSON format';

COMMIT;

Example: Mixed workload (hybrid storage)

Scenario description: This type of table is used in logistics after-sales systems. These systems require both analytical aggregation of logistics status and instant retrieval of logistics details by order ID.

BEGIN;

-- Explicitly specify the public schema
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',         -- Storage format: hybrid. Combines millisecond point lookups (row store) and efficient aggregation analytics (column store)
    distribution_key = 'order_id',      -- Distribution key: determines data distribution logic across shards
    bitmap_columns = 'shipping_status'  -- Bitmap index: set on low-cardinality columns like status fields to greatly accelerate "status filter" queries
);

-- Add metadata comments
COMMENT ON TABLE public.ads_shipping_info IS 'Logistics status query application table';
COMMENT ON COLUMN public.ads_shipping_info.order_id IS 'Order ID';
COMMENT ON COLUMN public.ads_shipping_info.shipping_status IS 'Logistics status (1: pending shipment, 2: in transit, 3: delivered)';
COMMENT ON COLUMN public.ads_shipping_info.receiver_address IS 'Delivery address';

COMMIT;