Hologres:CREATE TABLE

Preparations

• Purchase a Hologres instance.

• You have created a database.

• You have connected to the target database in Hologres. We recommend that you connect to HoloWeb to execute queries.

Create a table

We recommend that you use the CREATE TABLE WITH syntax, which is supported in Hologres V2.1 and later. This example shows how to create a transaction details table that uses a hierarchical naming convention, contains multiple fields, and includes comprehensive comments.

BEGIN;

-- Create a transaction details fact table (a standard table).
-- Explicitly specify the public schema and follow the hierarchical naming convention (dwd_xxx) for the table name.
CREATE TABLE IF NOT EXISTS public.dwd_trade_orders (
    order_id BIGINT NOT NULL,           -- The unique identifier of the order.
    shop_id INT NOT NULL,              -- The shop ID.
    user_id TEXT NOT NULL,             -- The user ID.
    order_amount NUMERIC(12, 2) DEFAULT 0.00,       -- The order amount.
    payment NUMERIC(12, 2) DEFAULT 0.00,         -- The actual amount paid.
    payment_type INT DEFAULT 0,                     -- The payment type (0: Unpaid, 1: Alipay, 2: WeChat Pay, 3: Credit Card).
    is_delivered BOOLEAN DEFAULT false,             -- Indicates whether the order is delivered.
    dt TEXT NOT NULL,                               -- The data timestamp.
    order_time TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, -- The time when the order was placed.
    PRIMARY KEY (order_id)                          -- Set the primary key to ensure data uniqueness.
)
WITH (
    orientation = 'column',                         -- Set the storage mode to column-oriented. This is suitable for real-time analytics (OLAP) scenarios with large data volumes.
    distribution_key = 'order_id',                  -- Set the distribution key. Data is sharded by order_id.
    clustering_key = 'order_time:asc',              -- Set the clustering key. Data is sorted by time in ascending order within files.
    event_time_column = 'order_time',               -- Set the event time column. This enables fast filtering of time ranges using file-level clipping.
    bitmap_columns = 'shop_id,payment_type,is_delivered', -- Set the bitmap index. This accelerates equality filtering on low-cardinality columns.
    dictionary_encoding_columns = 'user_id:auto'    -- Set dictionary encoding. This accelerates 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 'The unique identifier of the order.';
COMMENT ON COLUMN public.dwd_trade_orders.shop_id IS 'The unique ID of the shop.';
COMMENT ON COLUMN public.dwd_trade_orders.user_id IS 'The ID of the buyer.';
COMMENT ON COLUMN public.dwd_trade_orders.dt IS 'The data timestamp, in YYYYMMDD format.';
COMMENT ON COLUMN public.dwd_trade_orders.order_time IS 'The precise timestamp when the order was created.';

COMMIT;

Insert data

Hologres is compatible with the standard Data Manipulation Language (DML) syntax. The following statement inserts 10 rows of sample data that simulate a production environment.

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 the total transaction amount for each shop and sort the results in descending order by amount.
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;

Query 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

CREATE TABLE syntax

Hologres provides two syntaxes to set table properties and add comments:

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

  Use the WITH keyword to define properties. This syntax is more compact and offers better performance. The comment statements typically follow 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;

• Compatible syntax (supported in all versions)

  Use the CALL set_table_property statement 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 for large datasets (fact table)

Scenario description: This scenario is for real-time dashboard summaries. These tables usually contain very large data volumes, and the core requirement is high performance for statistical aggregation, such as calculating Gross Merchandise Volume (GMV) and 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 mode: Column-oriented. This is the preferred choice for large-scale aggregation and analysis, providing a high compression ratio and efficient column scans.
    distribution_key = 'order_id',      -- Distribution key: Shard by order_id to ensure even data distribution. This enables local joins when joining with other order tables.
    event_time_column = 'order_time',   -- Event time column: Usually set to a time field. The system divides files into segments based on this field to accelerate filtering by time range.
    clustering_key = 'order_time:asc'   -- Clustering key: Physically sorts data in ascending order of time. This reduces disk I/O when you query data from the "last hour" or a "specific day".
);

-- Add metadata comments for future data governance.
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;

Example: High-concurrency point queries (dimension table)

Scenario description: Retrieve a user persona by user_id in milliseconds. This type of table is optimized for extremely 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: Core to point query scenarios. Must be set.
    user_level INT,
    persona_jsonb JSONB 
) WITH (
    orientation = 'row'                 -- Storage mode: Row-oriented. This is optimized for primary key-based point lookups, with response times in the millisecond range.
    -- Note: For row-oriented tables, the primary key is automatically set as the distribution key and clustering key. No extra settings are needed.
);

-- Add metadata comments.
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;

Example: Hybrid workload (row-column hybrid storage)

Scenario description: This scenario is for a logistics and after-sales system. It requires both analytical summaries of logistics statuses (analytics) and instant retrieval of logistics details by order ID (point queries).

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 mode: Row-column hybrid. This combines the millisecond-level point query capability of row-oriented storage with the efficient aggregation and analysis capability of column-oriented storage.
    distribution_key = 'order_id',      -- Distribution key: Determines the data distribution logic across shards.
    bitmap_columns = 'shipping_status'  -- Bitmap index: Set on low-cardinality columns such as status fields to greatly accelerate "status filtering" queries.
);

-- Add metadata comments.
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;