Hologres:CREATE TABLE

テーブルスキーマの表示

次のクエリを実行して、テーブルのデータ定義言語 (DDL) 文を取得します。

SELECT hg_dump_script('public.dwd_trade_orders');

データの挿入

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

CREATE TABLE 構文

Hologres は、テーブルプロパティとコメントを定義するために 2 つの構文をサポートしています。

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

WITH キーワードを使用してプロパティをインラインで定義します。この構文はよりコンパクトで、パフォーマンスも向上します。

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;

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

CALL set_table_property を使用してプロパティを設定し、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;

テーブルプロパティ

プロパティは、その機能に基づいて 3 つのグループに分類されます。変更不可とマークされたプロパティは、テーブル作成後に変更できません。テーブルを再作成する必要があります。

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

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

-- テーブル作成時にストレージモードを設定：
CREATE TABLE <table_name> (...) WITH (storage_mode = 'hot');
CREATE TABLE <table_name> (...) WITH (storage_mode = 'cold');

-- テーブル作成後にストレージモードを設定：
CALL set_table_property('table_name', 'storage_mode', 'hot');
CALL set_table_property('table_name', 'storage_mode', 'cold');

例：大規模時系列データのためのパーティションテーブル

データ量が増加するにつれて、単一のフラットテーブルの維持コストは高くなります。既存データのパージには全行のスキャンが必要であり、時間ベースのクエリは全表スキャンを実行します。パーティションテーブルは、日ごとにデータを物理的に分離し、クリーンアップのための即時パーティションドロップと、クエリ中の自動パーティションプルーニングを可能にします。

この例では、「クイックスタート」セクションの dwd_trade_orders テーブルをパーティション構造にアップグレードします。フィールド定義とインデックス設定は継承されますが、プライマリキーにはパーティションキー dt を含める必要があります。

BEGIN;

-- パーティション化された親テーブルを作成します。
-- プライマリキーには、ビジネスキー (order_id) とパーティションキー (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 TABLE IF NOT EXISTS public.dwd_trade_orders_20231101
    PARTITION OF public.dwd_trade_orders_partitioned FOR VALUES IN ('20231101');

-- データを挿入します。アプリケーションロジックはフラットテーブルと同じです。
-- データは自動的に正しい子テーブルにルーティングされます。
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');

-- パーティションフィルターを使用してクエリを実行します。Hologres は一致する子テーブルのみをスキャンします。
SELECT COUNT(*) FROM public.dwd_trade_orders_partitioned WHERE dt = '20231101';

-- 子テーブルをドロップして、数秒でスペースを解放します (DELETE よりもはるかに高速です)。
-- DROP TABLE public.dwd_trade_orders_20231101;

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

シナリオ： 大量のデータを持つリアルタイムダッシュボードのサマリーで、中核的な要件は高速な集約です。例えば、商品総売上高 (GMV) や注文数の計算などです。

列指向ストレージはここで優れています。高い圧縮率が I/O を削減し、列スキャンは集約に必要な列のみを読み取ります。

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',           -- 大規模集約に最適。高い圧縮率、効率的な列スキャン
    distribution_key  = 'order_id',         -- 均等な分散。他の注文テーブルとのローカル結合を可能にする
    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;

例：高同時実行ポイントクエリ (ディメンションテーブル)

シナリオ： 高い秒間クエリ数 (QPS) の下で、user_id によってユーザープロファイルをミリ秒単位で取得します。

行指向ストレージは、プライマリキーベースのルックアップに最適化されています。すべての行データは隣接して保存されるため、関連のない列をスキャンすることなく、ポイントクエリが非常に高速になります。

BEGIN;

CREATE TABLE IF NOT EXISTS public.dim_user_persona (
    user_id       TEXT PRIMARY KEY,
    user_level    INT,
    persona_jsonb JSONB
) WITH (
    orientation = 'row'
    -- 行指向テーブルの場合、プライマリキーは自動的に分散キー
    -- およびクラスタリングキーとして使用されます。追加の設定は不要です。
);

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;

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

シナリオ： 物流状況の要約 (分析) と、order_id による注文詳細の取得 (ポイントクエリ) の両方を必要とする物流およびアフターサービスシステム。

行列ハイブリッドストレージは、行指向ストレージのミリ秒レベルのポイントクエリパフォーマンスと、列指向ストレージの効率的な集約を組み合わせたものです。

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',      -- ポイントクエリと集約の両方をサポート
    distribution_key = 'order_id',        -- シャード間でのデータ分散を制御
    bitmap_columns   = 'shipping_status'  -- 低カーディナリティ列での「status = X」フィルタークエリを高速化
);

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;

プライマリキーの制限

• 複合プライマリキー： 複数のフィールドでプライマリキーを構成できます。すべてのフィールドは NOT NULL でなければならず、単一の文で宣言する必要があります。

  BEGIN;
  CREATE TABLE public.test (
      id TEXT NOT NULL,
      ds TEXT NOT NULL,
      PRIMARY KEY (id, ds)
  );
  CALL SET_TABLE_PROPERTY('public.test', 'orientation', 'column');
  COMMIT;

• サポートされていない型： FLOAT、DOUBLE、NUMERIC、ARRAY、JSON、DATE、およびその他の複雑な型は、プライマリキー列として使用できません。

• 変更不可： プライマリキーはテーブル作成後に変更できません。異なるプライマリキーが必要な場合は、テーブルを再作成してください。

• ストレージ要件： 行指向および行列ハイブリッドテーブルにはプライマリキーが必要です。列指向テーブルではプライマリキーはオプションです。

制約のサポート

命名およびエスケープルール

• 列名を hg_ で始めることはできません。

• スキーマ名を holo_、hg_、または pg_ で始めることはできません。

• テーブル名は 127 バイトを超えることはできません。

• 名前が SQL キーワード、予約語、システムフィールド (例：ctid)、大文字と小文字を区別する識別子、特殊文字を含む、または数字で始まる場合は、名前を二重引用符 ("") で囲んでください。

Hologres V2.0 以降のエスケープされた列名の構文：

-- 単一のエスケープされた列
BEGIN;
CREATE TABLE tbl (c1 INT NOT NULL);
CALL set_table_property('tbl', 'clustering_key', '"c1":asc');
COMMIT;

-- 複数の列、大文字を含む (V2.1 以降)
BEGIN;
CREATE TABLE tbl ("C1" INT NOT NULL, c2 TEXT NOT NULL) WITH (clustering_key = '"C1",c2');
COMMIT;

-- 複数の列、大文字を含む (V2.0 以降)
BEGIN;
CREATE TABLE tbl ("C1" INT NOT NULL, c2 TEXT NOT NULL);
CALL set_table_property('tbl', 'clustering_key', '"C1",c2');
COMMIT;

Hologres V2.0 より前のバージョンのエスケープされた列名の構文：

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

-- 複数の列、大文字を含む
BEGIN;
CREATE TABLE tbl ("C1" INT NOT NULL, c2 TEXT NOT NULL);
CALL set_table_property('tbl', 'clustering_key', '"C1,c2"');
COMMIT;

必要に応じて Hologres V2.0 で古いパーサー構文に切り替えるには：

-- セッションレベルで古い構文を有効にします。
SET hg_disable_parse_holo_property = on;

-- データベースレベルで古い構文を有効にします。
ALTER DATABASE <db_name> SET hg_disable_parse_holo_property = on;

IF NOT EXISTS の動作

変更の制限

テーブルが作成された後、以下は変更できません。

• データ型 (Hologres V3.0 より前)

• 列の順序

• NULL 値許容制約 (NOT NULL ↔ NULL 値許容)

• ストレージレイアウトプロパティ：orientation、distribution_key、clustering_key、event_time_column

以下はテーブル作成後に変更できます。

• bitmap_columns および dictionary_encoding_columns — ALTER TABLE を介して

• データ型：V3.0 以降の一部の型、V3.1 以降の REBUILD を介したすべての型 — 詳細については、「データ型の変更」および「REBUILD」をご参照ください