Hologres:CREATE TABLE

事前準備

• Hologres インスタンスの購入。

• データベースが作成済みであること。

• 対象の Hologres データベースに接続します。 HoloWeb を使用して Hologres に接続し、クエリを実行することをお勧めします。

テーブルの作成

Hologres V2.1 以降で利用可能な CREATE TABLE WITH 構文を使用することを推奨します。次の例では、階層的な命名規則に従い、さまざまなフィールドと包括的なコメントを含む取引詳細テーブルを作成します。

BEGIN;

-- 取引詳細のファクトテーブルを作成 (標準の非パーティションテーブル)
-- public スキーマを明示的に指定し、テーブル名に階層的な命名 (dwd_xxx) を使用
CREATE TABLE IF NOT EXISTS public.dwd_trade_orders (
    order_id BIGINT NOT NULL,           -- 一意の注文 ID
    shop_id INT NOT NULL,              -- ショップ ID
    user_id TEXT NOT NULL,             -- ユーザー ID
    order_amount NUMERIC(12, 2) DEFAULT 0.00,       -- 注文金額
    payment NUMERIC(12, 2) DEFAULT 0.00,         -- 支払金額
    payment_type INT DEFAULT 0,                     -- 支払タイプ (0: 未払い, 1: Alipay, 2: WeChat, 3: クレジットカード)
    is_delivered BOOLEAN DEFAULT false,             -- 配送済みかどうか
    dt TEXT NOT NULL,                               -- データタイムスタンプ
    order_time TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, -- 注文時間
    PRIMARY KEY (order_id)                          -- プライマリキーを設定してデータの一意性を保証
)
WITH (
    orientation = 'column',                         -- カラムストアを使用: 大量データセットに対するリアルタイム分析 (OLAP) に最適
    distribution_key = 'order_id',                  -- ディストリビューションキーを設定: order_id によってデータがシャーディングされる
    clustering_key = 'order_time:asc',              -- クラスタリングキーを設定: ファイル内で時間によってデータが昇順にソートされる
    event_time_column = 'order_time',               -- セグメントキーを設定: ファイルレベルのプルーニングを有効にし、時間範囲を迅速にフィルタリング
    bitmap_columns = 'shop_id,payment_type,is_delivered', -- ビットマップインデックスを設定: 低カーディナリティ列での等価フィルターを高速化
    dictionary_encoding_columns = 'user_id:auto'    -- ディクショナリエンコーディングを設定: 文字列型の列での集約とフィルタリングを高速化
);

-- メタデータコメントの追加
COMMENT ON TABLE public.dwd_trade_orders IS '取引注文詳細の基本ファクトテーブル';
COMMENT ON COLUMN public.dwd_trade_orders.order_id IS '一意の注文 ID';
COMMENT ON COLUMN public.dwd_trade_orders.shop_id IS '一意のショップ ID';
COMMENT ON COLUMN public.dwd_trade_orders.user_id IS '購入者のユーザー ID';
COMMENT ON COLUMN public.dwd_trade_orders.dt IS 'データタイムスタンプ、フォーマット YYYYMMDD';
COMMENT ON COLUMN public.dwd_trade_orders.order_time IS '注文が作成された正確なタイムスタンプ';

COMMIT;

データの挿入

Hologres は、標準のデータ操作言語 (DML) 構文をサポートしています。次の例では、シミュレートされた本番データを 10 行テーブルに挿入します。

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

データのクエリ

-- ショップごとの合計支払額を計算し、金額の降順でソート
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

テーブル作成構文

Hologres は、テーブルプロパティの設定とコメントの追加のために 2 つの構文を提供します。

• 標準構文 (V2.1 以降で推奨)

  WITH 句を使用してプロパティを定義できます。この構文はよりコンパクトで、パフォーマンスも向上します。CREATE 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;

• 互換構文 (すべてのバージョンでサポート)

  set_table_property を伴う CALL 文を使用してプロパティを設定し、COMMENT 文を使用してコメントを追加できます。これらの文は、CREATE TABLE 文と同じトランザクションブロック (BEGIN...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;

テーブルプロパティ

例：大量データのリアルタイム分析 (ファクトテーブル)

シナリオ説明： このタイプのテーブルは、リアルタイムのダッシュボード集計に使用されます。これらのテーブルは通常、大量のデータを保持します。中核となる要件は、商品総流通額 (GMV) や注文量などの高性能な統計集計を実現することです。

BEGIN;

-- public スキーマを明示的に指定
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',             -- ストレージフォーマット: カラムストア。大量集計分析に最適で、高い圧縮率と効率的なカラムスキャンを提供
    distribution_key = 'order_id',      -- ディストリビューションキー: order_id でデータをシャーディングして均等な分散を保証。他の注文テーブルと結合する際に Local Join を有効化
    event_time_column = 'order_time',   -- セグメントキー: 通常は時間フィールドに設定。システムはこのフィールドでファイルをセグメント化し、時間範囲のフィルタリングを高速化
    clustering_key = 'order_time:asc'   -- クラスタリングキー: データを時間昇順で物理的にソート。「直近 1 時間」や「特定の日」のデータをクエリする際のディスク I/O を削減
);

-- 将来のデータガバナンスをサポートするためにメタデータコメントを追加
COMMENT ON TABLE public.dwd_order_summary IS '注文概要ファクトテーブル';
COMMENT ON COLUMN public.dwd_order_summary.order_id IS '一意の注文 ID';
COMMENT ON COLUMN public.dwd_order_summary.category_id IS 'カテゴリ ID';
COMMENT ON COLUMN public.dwd_order_summary.gmv IS '商品総流通額';
COMMENT ON COLUMN public.dwd_order_summary.order_time IS '注文時間';

COMMIT;

例：プライマリキーによる高並行ポイントルックアップ (ディメンションテーブル)

シナリオ説明： このタイプのテーブルは、user_id によってユーザーペルソナをミリ秒単位で取得するために使用されます。これらのテーブルは、高い QPS (queries per second) の下で超低レイテンシーの応答を提供するように設計されています。

BEGIN;

-- public スキーマを明示的に指定
CREATE TABLE IF NOT EXISTS public.dim_user_persona (
    user_id TEXT PRIMARY KEY,           -- プライマリキー: ポイントルックアップシナリオに不可欠
    user_level INT,
    persona_jsonb JSONB 
) WITH (
    orientation = 'row'                 -- ストレージフォーマット: ローストア。プライマリキーによるポイントルックアップ (Point Lookup) に特化して最適化されており、ミリ秒単位の応答時間を実現
    -- 注意: ローストアテーブルは自動的にプライマリキーをディストリビューションキーとクラスタリングキーとして使用するため、追加の設定は不要です。
);

-- メタデータコメントの追加
COMMENT ON TABLE public.dim_user_persona IS 'ユーザーペルソナディメンションテーブル';
COMMENT ON COLUMN public.dim_user_persona.user_id IS '一意のユーザー ID';
COMMENT ON COLUMN public.dim_user_persona.user_level IS 'ユーザーレベル';
COMMENT ON COLUMN public.dim_user_persona.persona_jsonb IS 'JSON 形式のユーザープロファイル特徴';

COMMIT;

例：混合ワークロード (ハイブリッドストレージ)

シナリオ説明： このタイプのテーブルは、物流アフターサービスシステムで使用されます。これらのシステムでは、物流ステータスの分析集計と、注文 ID による物流詳細の即時取得の両方が必要です。

BEGIN;

-- public スキーマを明示的に指定
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',         -- ストレージフォーマット: ハイブリッド。ミリ秒単位のポイントルックアップ (ローストア) と効率的な集計分析 (カラムストア) を組み合わせる
    distribution_key = 'order_id',      -- ディストリビューションキー: shard 間でのデータ分散ロジックを決定
    bitmap_columns = 'shipping_status'  -- ビットマップインデックス: ステータスフィールドのような低カーディナリティの列に設定し、「ステータスフィルター」クエリを大幅に高速化
);

-- メタデータコメントの追加
COMMENT ON TABLE public.ads_shipping_info IS '物流ステータス照会アプリケーションテーブル';
COMMENT ON COLUMN public.ads_shipping_info.order_id IS '注文 ID';
COMMENT ON COLUMN public.ads_shipping_info.shipping_status IS '物流ステータス (1: 出荷待ち, 2: 輸送中, 3: 配達済み)';
COMMENT ON COLUMN public.ads_shipping_info.receiver_address IS '配送先住所';

COMMIT;