Hologres：CREATE TABLE

Hologres是相容PostgreSQL生態，建表文法與PostgreSQL相同，但是索引與PostgreSQL不同，Hologres支援的索引請參見設定表屬性和索引。建表時選擇合適的索引，能夠使SQL在執行時快速命中資料，減少IO消耗，以更少的計算資源，實現更快的查詢效能。下圖是一個SQL從發起到擷取資料的執行流程，可以通過下圖理解每個索引的作用，以方便實際業務中更加方便高效的為表選擇合適的索引。

1. SQL執行時，如果是分區表，那麼會通過分區裁剪，定位到所在分區。

2. 通過Distribution Key快速定位到資料所在的資料分區（Shard）。

3. 通過Event Time Column（原Segment Key）快速定位到資料所在的檔案。

4. Clustering Key為資料在檔案內的排序，可以通過Clustering Key快速定位到所在的檔案塊。

5. 位元影像索引Bitmap是檔案內的索引，可以通過Bitmap快速定位到合格資料所在的行號。

索引適用的情境如下。

在Hologres中，可以通過set_table_property或create table with命令為表設定多種屬性，合理的表屬性設定可以有助於系統高效地組織和查詢資料。與資料存放區布局有關的參數需要和建表語句同時執行。

• 命令格式

  -- 2.1版本起支援
  create table <table_name> (...)
  with (property = 'value'[, ...]);
  
  -- 所有版本支援
  call set_table_property('<table_name>', property, 'value');

  說明

  set_table_property的調用需要與create table在同一事務中執行。

  Hologres支援的設定表屬性如下所示，包括但不限於以下幾種表屬性。

  • V2.1版本起支援的文法：

    CREATE TABLE <table_name> (...)
    WITH (
        orientation = '[column | row | row,column]',
        table_group = '[tableGroupName]',
        distribution_key = 'columnName[,...]]',
        clustering_key = '[columnName{:asc]} [,...]]',
        event_time_column = '[columnName [,...]]',
        bitmap_columns = '[columnName [,...]]',
        dictionary_encoding_columns = '[columnName [,...]]',
        time_to_live_in_seconds = '<non_negative_literal>'
        [,storage_mode, ...]
    );

  • 所有版本都支援的文法：

    BEGIN;
    CREATE TABLE <table_name> (...);
    call set_table_property('table_name', 'orientation', '[column | row]');
    call set_table_property('table_name', 'table_group', '[tableGroupName]');
    call set_table_property('table_name', 'distribution_key', '[columnName[,...]]');
    call set_table_property('table_name', 'clustering_key', '[columnName{:asc]} [,...]]');
    call set_table_property('table_name', 'event_time_column', '[columnName [,...]]');
    call set_table_property('table_name', 'bitmap_columns', '[columnName [,...]]');
    call set_table_property('table_name', 'dictionary_encoding_columns', '[columnName [,...]]');
    call set_table_property('table_name', 'time_to_live_in_seconds', '<non_negative_literal>');
    COMMIT;

• 參數說明

  具體參數和相關內容如下表所示：

  參數	說明	列存表	行存表	行列共存表	建議值	建表後是否可修改
  orientation	表格儲存體格式。	column（預設值）	row	row,column	column	否，如需修改請重建立表。
  table_group	Table Group。	預設為default table group。	預設為default table group。	預設為default table group。	預設即可。	否，如需修改請重建立表或者Resharding。
  distribution_key	分布鍵。	預設為主鍵，根據業務情境修改。	預設為主鍵。	預設為主鍵。	主鍵的子集，建議只選擇一列。	否，如需修改請重建立表。
  clustering_key	聚簇索引。	預設為空白。	預設為主鍵。	預設為空白。	建議最多選擇一列，且僅支援asc序。	否，如需修改請重建立表。
  event_time_column	分段鍵。	預設為第一個非空時間戳記欄位。	不支援。	預設為第一個非空時間戳記欄位。	建議時間戳記欄位。	否，如需修改請重建立表。
  bitmap_columns	位元影像索引。	按需使用。	不支援。	按需使用。	建議用於等值比較的列，一般10列以下。	是，詳情請參見ALTER TABLE。
  dictionary_encoding_columns	位元編碼。	按需使用。	不支援。	按需使用。	建議低基數列，一般10列以下。	是，詳情請參見ALTER TABLE。
  time_to_live_in_seconds	表資料生命週期。	按需使用。	按需使用。	按需使用。	預設即可，無需設定。	是，詳情請參見ALTER TABLE。

  • orientation

    指定了資料庫表在Hologres中的儲存模式是列存還是行存，從V1.1版本開始支援行列共存的格式，命令文法如下所示。不同的儲存格式適用於不同的查詢情境，建表時預設為列存，其餘儲存模式需要建表時顯式指定，詳情請參見表格儲存體格式：列存、行存、行列共存。

    call set_table_property('table_name', 'orientation', '[column | row |row,column]');

  • table_group

    在Hologres中，Shard是指資料分區，Table Group是Hologres中特有的邏輯儲存概念，用於管理Shard數，一個Table Group唯一對應一組Shard。建立資料庫後，如果沒有建立新的Table Group，那麼建立第一個表時，會自動建立一個名稱為<db>_tg_default的預設Table Group，後續表建立時沒有指定Table Group將會被指定至預設Table Group中。一般情況下無需設定Table Group，使用預設Table Group即可，當執行個體規格較大（大於256 Core時），建議根據業務情況劃分不同的Table Group和Shard數，帶來更好的效能，使用詳情請參見Table Group與Shard Count操作指南。

    call set_table_property('table_name', 'table_group', '[tableGroupName]');

  • distribution_key

    設定分布鍵Distribution Key。Distribution Key指定了表的分布策略，資料根據Distribution Key被分配到各個Shard上，保證Distribution Key相同的記錄會被分配到同一個Shard上。Distribution Key是非常重要的分布式概念，合理的設定Distribution Key可以提高查詢效能和QPS等，詳情請參見分布鍵Distribution Key。

    call set_table_property('table_name', 'distribution_key', '[columnName[,...]]');

  • clustering_key

    設定聚簇索引Clustering Key，命令文法如下所示。Hologres會根據Clustering Key在檔案內對資料進行排序，預設為升序（acs）排序。合理地設定Clustering Key能夠加速在索引列上的Range和Filter查詢，提升查詢效能，詳情請參見聚簇索引Clustering Key。

    call set_table_property('table_name', 'clustering_key', '[columnName{:asc} [,...]]');

  • event_time_column

    設定分段鍵Event_time_column （原Segment Key），命令文法如下所示。檔案會根據Event_time_column劃分，當命中Event_time_column時，可以快速定位到資料所在的檔案，Event_time_column適用於資料為單調遞增或單調遞減的有序欄位，例如時間戳記欄位，非常適用於日誌、流量等和時間強相關的資料，合理設定可極大提升查詢效能，詳情請參見Event Time Column（Segment Key）。

    call set_table_property('table_name', 'event_time_column', '[columnName [,...]]');

  • bitmap_columns

    設定位元影像索引Bitmap，命令文法如下所示。Bitmap能夠快速定位到符合條件資料所在的行號，適合將等值查詢條件的資料設定為位元影像索引列。預設列存表所有TEXT資料類型的欄位都會被隱式地設定為位元影像索引列，詳情請參見位元影像索引Bitmap。

    call set_table_property('table_name', 'bitmap_columns', '[columnName{:[on|off]}[,...]]');

  • dictionary_encoding_columns

    設定字典編碼Dictionary Encoding，命令文法如下所示。Dictionary Encoding指定列的值構建字典映射。字典編碼可以將字串的比較轉成數位比較，加速Group By、Filter等查詢。預設列存表所有TEXT資料類型的欄位都會被設定為Dictionary Encoding列 ，在Hologres V0.9及之後版本，會根據資料特徵自動選擇是否建立字典編碼。

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

  • time_to_live_in_seconds（不建議使用）

    設定表的資料生命週期（TTL），單位為秒，命令文法如下所示。

    • TTL到期時間是按照資料寫入的時間開始計算，不是按照資料更新時間計算。不設定TTL的時候，預設為100年，Hologres從 V1.3.24版本開始，TTL允許的最小值是一天，即86400秒。TTL的詳細使用說明請參見SQL命令列表。

    • TTL不是精確的時間，即到期了之後資料會在某一段時間（不是固定時間）刪除（只刪除資料，表還會存在），因此可能會出現PK重複的問題。生產業務不建議使用TTL來管理資料的生命週期，建議使用CREATE PARTITION TABLE。

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

您可以執行如下命令查看TABLE的具體DDL：

create extension hg_toolkit; --該命令是DB層級，一個DB執行一次即可
select hg_dump_script('[<schema_name>.]<table_name>');

HoloWeb提供可視化一鍵建表功能，無需寫SQL命令就能建立表，步驟如下。

1. 進入HoloWeb頁面，詳情請參見串連HoloWeb。

2. 在HoloWeb頁面頂部功能表列，單擊中繼資料管理 > 表。

   您也可以在中繼資料管理介面的已登入執行個體列表。單擊目標資料庫，滑鼠右擊資料庫下已建立的目標模式，選擇建立內部表。

3. 在建立內部表頁面，配置各項參數。

   參數說明（單擊展開）

   類別	參數	描述
   基本屬性	模式	模式名稱。 您可以選擇預設建立的public模式，也可以選擇建立的模式名稱。
   	表名	建立的Hologres內部表名稱。
   	描述	建立的Hologres內部表描述。
   欄位	欄位名	表中每一列的標識。
   	資料類型	欄位取值的類型。
   	主鍵	表中每條資料的唯一標識。
   	可空	欄位是否可以設定為空白。
   	數組	有序的元素序列。
   	描述	欄位的描述資訊。
   	操作	包括刪除、上移和下移。
   屬性	儲存模式	包括列存、行存和行列共存三種儲存模式。 預設為列存。
   	表資料生命週期（秒）	資料第一次寫入的時間開始計算，當到達生命週期後，表資料會在某一段時間內被清除（沒有固定時間段）。 預設生命週期為永久。
   	Binlog	表是否開啟Binlog，詳情請參見訂閱Hologres Binlog。
   	Binlog生命週期	Binlog的生命週期，詳情請參見訂閱Hologres Binlog。預設生命週期為永久。
   	分布列	distribution_key，使用詳情請參見分布鍵Distribution Key。
   	分段列	event_time_column ，使用詳情請參見Event Time Column（Segment Key）。
   	聚簇列	clustering_key，使用詳情請參見聚簇索引Clustering Key。
   	字典編碼列	dictionary_encoding_columns，使用詳情請參見字典編碼Dictionary Encoding。
   	位元影像列	bitmap_columns，使用詳情請參見位元影像索引Bitmap。
   分區表	無	選擇分區欄位。

4. 在頁面右上方，單擊提交。提交之後，您可以在左側對應模式下，重新整理出建立的內部表。

其他相關操作：

• 編輯內部表

  1. 在中繼資料管理介面的已登入執行個體列表，雙擊目標內部表。

  2. 在目標內部表資訊頁，單擊編輯表，可以添加欄位、更改表資料周期等部分表屬性。

  3. 單擊提交。

• 刪除內部表

  1. 在中繼資料管理介面的已登入執行個體列表，滑鼠右擊目標內部表，選擇刪除表。

  2. 在刪除表對話方塊，單擊確認。

• 表資料預覽

  1. 在已登入執行個體列表，雙擊目標內部表。

  2. 進入表資訊頁簽，單擊資料預覽，則可以預覽表資料。

• DDL預覽

  在目標表資訊頁簽，單擊DDL語句，則可以預覽DDL語句。