1、表

OushuDB表由行（rows）和（columns）組成。每一個列有一個列名和一個資料類型，一個表的列數和列的順序是固定的。一個表的行數是可變的。SQL并不假設表中行的順序。當讀一個表時，除非顯示要求排序，傳回的行會以任意順序出現。另外，SQL并不給每一行一個唯一标志符，是以，一個表中具有同樣幾個同樣的行是可能的。

建立一個表可以使用create table指令。在指令裡面，需要指定表名，列名以及列的類型。例如：

create table my_first_table (
    first_column text,
    second_column integer
);

上面的指令建立了一個兩列的表，一列為文本類型，一列為整數類型。删除剛剛建立的表可以使用drop table指令。

drop table my_first_table;

2、表的存儲格式

OushuDB現在支援多種存儲格式：AO，Parquet，ORC，MagmaAP。AO是按行存儲的格式，而Parquet，ORC，MagmaAP是按列存儲的格式。其中MagmaAP 是在4.0.0.0釋出的全新的存儲格式。MagmaAP，ORC都支援update／delete，支援事務，且MagmaAP還支援index。

注：和GPDB類似，之前OushuDB版本支援CO格式，但CO格式不适合叢集大和分區多的情況，後續新版本去除了CO支援。

對于各種格式的表的建表文法，下面給出了幾個例子。

# 預設建立的是AO表
CREATE TABLE rank1 (id int, rank int, year smallint,gender char(1), count int );

# 和上面的建立的表一樣，顯式指定存儲格式類型
CREATE TABLE rank2 (id int, rank int, year smallint,gender char(1), count int ) with (appendonly =true, orientation =row);

# 建立一個snappy壓縮的AO表
CREATE TABLE rank3 (id int, rank int, year smallint,gender char(1), count int ) with (appendonly =true, orientation =row, compresstype = snappy);

# 建立一個snappy壓縮的Parquet表，如果不指定壓縮類型的話，預設不壓縮。
CREATE TABLE rank3 (id int, rank int, year smallint,gender char(1), count int ) with (appendonly =true, orientation =parquet, compresstype = snappy);


# 建立一個不壓縮的ORC表，如果不指定壓縮類型的話，預設不壓縮。
CREATE TABLE rank3 (id int, rank int, year smallint,gender char(1), count int ) with (appendonly =true, orientation =orc);

# 建立一個帶壓縮的ORC表，需指定壓縮類型。
CREATE TABLE rank3 (id int, rank int, year smallint,gender char(1), count int ) with (appendonly =true, orientation =orc, compresstype = lz4);

# 建立一個壓縮的magma表， magma 内部自動實作了壓縮。
CREATE TABLE rank3 (id int, rank int, year smallint,gender char(1), count int ) format 'magmaap';

# 建立一個有primary key的magma表， magma 内部自動實作了壓縮。
CREATE TABLE rank3 (id int, rank int, year smallint,gender char(1), count int，primary key(id) ) format 'magmaap';

3、表的分布

在OushuDB中，表可以兩種方式分布方式：基于Hash的分布和Random分布。基于Hash的分布方法基于分布列的Hash值進行分布，Random分布采取随機分布模式。

建立表時使用者不指定分布方式的時候非magmaap表預設使用Random分布，magmaap表目前不支援Random 分布。下面這個兩個例子等價。

CREATE TABLE rank (id int, rank int, year smallint,gender char(1), count int );

CREATE TABLE rank (id int, rank int, year smallint,gender char(1), count int );
DISTRIBUTED RANDOMLY;

下面這個例子建立一個Hash分布的表，分布的Key使用三個列（rank, gender, year）的組合，資料分布到32個bucket裡面。

如果不指定bucketnum的話，系統預設使用default_hash_table_bucket_number系統參數的值來做為bucketnum。

CREATE TABLE rank (id int, rank int, year smallint,gender char(1), count int )
WITH (bucketnum = 32)
DISTRIBUTED BY (rank, gender,year);

4、Hash分布和Random分布的選取

非Magma表： Random分布的表較靈活，在系統擴容添加節點後無需重新分布資料。而Hash分布的表在系統擴容後，為了利用新增加節點的計算能力，需要重新分布資料。另外，針對Hash分布的表資料總管在配置設定資源的時候采取配置設定固定virtual segment數的方式，不如Random分布靈活。

Hash分布的表在某些查詢上會有性能上的好處，因為有時可以避免重新分布某些表。

例如下面例子的查詢，如果lineitem和orders兩張表分别按照l_orderkey和o_orderkey分布，則這個查詢在執行時不再需要重新分布任何一張表就可以并行在各個節點并行執行連接配接操作。

SELECT l_orderkey, count(l_quantity)
FROM lineitem, orders
WHERE l_orderkey = o_orderkey

針對絕大多數查詢，實驗表明都不是網絡瓶頸，基于Hash分布和基于Random分布性能差别不大。是以我們建議使用者預設采取Random分布，隻針對特定需要優化的場合使用Hash分布的表。

Magma表具備的Hash和random 表的優勢，通過 default_magma_hash_table_nvseg_per_node 來控制每個節點能啟動的virtual segment數。在系統擴容後不需要重新分布資料。

5、Hash分布的表bucketnum的選取

針對Hash分布的表，bucketnum決定了一個查詢的并行度。在一些常見的硬體配置中（128G記憶體和12塊SAS盤），我們建議選取6 節點數或者8 節點數。硬體更好的話可以增加bucketnum。在系統初始化的時候，default_hash_table_bucket_number的初始化預設值為8 * 節點數。Magma table 使用default_magma_hash_table_nvseg_per_node, 表示每個節點是virtual segment 的個數。：

6、表分區

針對大的資料倉庫事實表，往往我們可以通過對表進行分區的方式來把一個很大的表拆分成多個子表。這樣的話，有兩個好處:

● 查詢優化器可以針對分區表進行優化，如果查詢隻設計到某些分區，則查詢計劃隻需要掃描這些分區，進而加速查詢

● 如果我們按照日期進行分區的話，我們可以簡單的加入分區和删除過期的分區。

OushuDB支援基于Range和List的兩種分區方式。

● Range分區：依據數值範圍進行分區，比如日期，價格等

● List分區：依據一個值的清單進行分區，比如地區等

下面我們通過例子說明這兩種分區的使用方式。

Range分區

# 建立一個sales表，按照date列Range分區，從2008年到2009年每月建立一個分區

postgres=＃ CREATE TABLE sales (id int, date date, amt decimal(10,2))
PARTITION BY RANGE (date)
( START (date '2008-01-01') INCLUSIVE
   END (date '2009-01-01') EXCLUSIVE
   EVERY (INTERVAL '1 month') );

檢視建立的表資訊，d+給出該表的所有資訊

postgres=# \d+ sales
              Append-Only Table "public.sales"
 Column |     Type      | Modifiers | Storage | Description
--------+---------------+-----------+---------+-------------
 id     | integer       |           | plain   |
 date   | date          |           | plain   |
 amt    | numeric(10,2) |           | main    |
Compression Type: None
Compression Level: 0
Block Size: 32768
Checksum: f
Child tables: sales_1_prt_1,
              sales_1_prt_10,
              sales_1_prt_11,
              sales_1_prt_12,
              sales_1_prt_2,
              sales_1_prt_3,
              sales_1_prt_4,
              sales_1_prt_5,
              sales_1_prt_6,
              sales_1_prt_7,
              sales_1_prt_8,
              sales_1_prt_9
Has OIDs: no
Options: appendonly=true
Distributed randomly
Partition by: (date)

你也可以顯式得聲明子分區并指定子表名字。

CREATE TABLE sales_exp (id int, date date, amt decimal(10,2))
PARTITION BY RANGE (date)
( PARTITION Jan08 START (date '2008-01-01') INCLUSIVE ,
  PARTITION Feb08 START (date '2008-02-01') INCLUSIVE ,
  PARTITION Mar08 START (date '2008-03-01') INCLUSIVE ,
  PARTITION Apr08 START (date '2008-04-01') INCLUSIVE ,
  PARTITION May08 START (date '2008-05-01') INCLUSIVE ,
  PARTITION Jun08 START (date '2008-06-01') INCLUSIVE ,
  PARTITION Jul08 START (date '2008-07-01') INCLUSIVE ,
  PARTITION Aug08 START (date '2008-08-01') INCLUSIVE ,
  PARTITION Sep08 START (date '2008-09-01') INCLUSIVE ,
  PARTITION Oct08 START (date '2008-10-01') INCLUSIVE ,
  PARTITION Nov08 START (date '2008-11-01') INCLUSIVE ,
  PARTITION Dec08 START (date '2008-12-01') INCLUSIVE
                   END (date '2009-01-01') EXCLUSIVE );

檢視建立的表資訊

postgres=＃ \d+ sales_exp
            Append-Only Table "public.sales_exp"
 Column |     Type      | Modifiers | Storage | Description
--------+---------------+-----------+---------+-------------
 id     | integer       |           | plain   |
 date   | date          |           | plain   |
 amt    | numeric(10,2) |           | main    |
Compression Type: None
Compression Level: 0
Block Size: 32768
Checksum: f
Child tables: sales_exp_1_prt_apr08,
              sales_exp_1_prt_aug08,
              sales_exp_1_prt_dec08,
              sales_exp_1_prt_feb08,
              sales_exp_1_prt_jan08,
              sales_exp_1_prt_jul08,
              sales_exp_1_prt_jun08,
              sales_exp_1_prt_mar08,
              sales_exp_1_prt_may08,
              sales_exp_1_prt_nov08,
              sales_exp_1_prt_oct08,
              sales_exp_1_prt_sep08
Has OIDs: no
Options: appendonly=true
Distributed randomly
Partition by: (date)

下面是另外一個根據Range分區的例子，這次使用的是整型列進行分區。這裡面我們添加了一個DEFAULT PARTITION，在不滿足其他分區的條件下，資料會被插入DEFAULT PARTITION。

CREATE TABLE rank (id int, rank int, year int, gender char(1), count int)
PARTITION BY RANGE (year)
( START (2001) END (2008) EVERY (1),
  DEFAULT PARTITION extra );

7、List分區

下面的例子建立了一個基于List的分區表。List分區表可以基于任意支援等值比較的資料類型。對與List分區，你需要顯式的指定所有子分區。

postgres=＃ CREATE TABLE rank (id int, rank int, year int, gender char(1), count int )
PARTITION BY LIST (gender)
( PARTITION girls VALUES ('F'),
  PARTITION boys VALUES ('M'),
  DEFAULT PARTITION other );

檢視表資訊

postgres=＃ \d+ rank
              Append-Only Table "public.rank"
 Column |     Type     | Modifiers | Storage  | Description
--------+--------------+-----------+----------+-------------
 id     | integer      |           | plain    |
 rank   | integer      |           | plain    |
 year   | integer      |           | plain    |
 gender | character(1) |           | extended |
 count  | integer      |           | plain    |
Compression Type: None
Compression Level: 0
Block Size: 32768
Checksum: f
Child tables: rank_1_prt_boys,
              rank_1_prt_girls,
              rank_1_prt_other
Has OIDs: no
Options: appendonly=true
Distributed randomly
Partition by: (gender# )

8、多級分區

你可以使用SUBPARTITION模版定義多級分區。下面的例子定義了一個兩級分區表，第一級安裝date列進行Range分區，第二級按照region列進行List分區。

CREATE TABLE sales (trans_id int, date date, amount decimal(9,2), region text)
PARTITION BY RANGE (date)
SUBPARTITION BY LIST (region)
SUBPARTITION TEMPLATE
( SUBPARTITION usa VALUES ('usa'),
  SUBPARTITION asia VALUES ('asia'),
  SUBPARTITION europe VALUES ('europe'),
  DEFAULT SUBPARTITION other_regions)
(START (date '2011-01-01') INCLUSIVE
 END (date '2012-01-01') EXCLUSIVE
 EVERY (INTERVAL '1 month'),
 DEFAULT PARTITION outlying_dates);

注：當你在使用多級分區的時候，系統會産生大量的小表，有些表可能沒有資料或包含很少資料，這樣會對系統中繼資料管理産生過多壓力。建議不要建立具有過多分區的表。一般限制分區數在100或以内比較合理。

9、檢視你的分區設計

你可以通過pg_partitions視圖來檢視你的分區表設計。例如通過下面的語句可以檢視出sales表的分區設計。

postgres=# SELECT partitionboundary, partitiontablename, partitionname, partitionlevel, partitionrank
postgres-# FROM pg_partitions
postgres-# WHERE tablename='sales';
                                           partitionboundary                                          | partitiontablename | partitionname | partitionlevel | partitionrank
------------------------------------------------------------------------------------------------------+--------------------+---------------+----------------+---------------
 START ('2008-01-01'::date) END ('2008-02-01'::date) EVERY ('1 mon'::interval) WITH (appendonly=true) | sales_1_prt_1      |               |              0 |             1
 START ('2008-02-01'::date) END ('2008-03-01'::date) EVERY ('1 mon'::interval) WITH (appendonly=true) | sales_1_prt_2      |               |              0 |             2
 START ('2008-03-01'::date) END ('2008-04-01'::date) EVERY ('1 mon'::interval) WITH (appendonly=true) | sales_1_prt_3      |               |              0 |             3
 START ('2008-04-01'::date) END ('2008-05-01'::date) EVERY ('1 mon'::interval) WITH (appendonly=true) | sales_1_prt_4      |               |              0 |             4
 START ('2008-05-01'::date) END ('2008-06-01'::date) EVERY ('1 mon'::interval) WITH (appendonly=true) | sales_1_prt_5      |               |              0 |             5
 START ('2008-06-01'::date) END ('2008-07-01'::date) EVERY ('1 mon'::interval) WITH (appendonly=true) | sales_1_prt_6      |               |              0 |             6
 START ('2008-07-01'::date) END ('2008-08-01'::date) EVERY ('1 mon'::interval) WITH (appendonly=true) | sales_1_prt_7      |               |              0 |             7
 START ('2008-08-01'::date) END ('2008-09-01'::date) EVERY ('1 mon'::interval) WITH (appendonly=true) | sales_1_prt_8      |               |              0 |             8
 START ('2008-09-01'::date) END ('2008-10-01'::date) EVERY ('1 mon'::interval) WITH (appendonly=true) | sales_1_prt_9      |               |              0 |             9
 START ('2008-10-01'::date) END ('2008-11-01'::date) EVERY ('1 mon'::interval) WITH (appendonly=true) | sales_1_prt_10     |               |              0 |            10
 START ('2008-11-01'::date) END ('2008-12-01'::date) EVERY ('1 mon'::interval) WITH (appendonly=true) | sales_1_prt_11     |               |              0 |            11
 START ('2008-12-01'::date) END ('2009-01-01'::date) EVERY ('1 mon'::interval) WITH (appendonly=true) | sales_1_prt_12     |               |              0 |            12
(12 rows)

10、添加一個分區

你可以通過下面的語句添加一個分區。

ALTER TABLE sales ADD PARTITION
            START (date '2009-02-01') INCLUSIVE
            END (date '2009-03-01') EXCLUSIVE;

如果你在建立表的時候沒有使用subpartition template，你需要在添加分區的時候給出子分區定義，例如：

ALTER TABLE sales ADD PARTITION
    START (date '2009-02-01') INCLUSIVE
    END (date '2009-03-01') EXCLUSIVE
      ( SUBPARTITION usa VALUES ('usa'),
        SUBPARTITION asia VALUES ('asia'),
        SUBPARTITION europe VALUES ('europe') );

你也可以單獨修改一個二級分區：

CREATE TABLE sales_two_level (trans_id int, date date, amount decimal(9,2), region text)
PARTITION BY RANGE (date)
SUBPARTITION BY LIST (region)
SUBPARTITION TEMPLATE
( SUBPARTITION usa VALUES ('usa'),
  SUBPARTITION asia VALUES ('asia'),
  SUBPARTITION europe VALUES ('europe'))
(START (date '2011-01-01') INCLUSIVE
 END (date '2012-01-01') EXCLUSIVE
 EVERY (INTERVAL '1 month'),
 DEFAULT PARTITION outlying_dates);

ALTER TABLE sales_two_level ALTER PARTITION FOR (RANK(12))
   ADD PARTITION africa VALUES ('africa');

其中RANK(12)表示第12個分區。

注：指定一個分區可以使用

PARTITION FOR (value) or PARTITION FOR(RANK(number))文法。

如果你的分區表有一個Default分區的話，你不可以向該分區表添加分區，你隻可以通過分裂Default分區的方法來添加分區。

11、重命名分區

Partitioned tables use the following naming convention. Partitioned subtable names are subject to uniqueness requirements and length limitations.

分區表使用以下的命名規則。

<parentname>_<level>_prt_<partition_name>

例如：sales_1_prt_jan08指的是父表名字為sales，第一級分區名字為jan08的分區。在建立Range分區表時，如果沒有指定分區名字，分區的名字會自動生成為數字。

改變父表的名字同時會改變分區表的名字。例如：

postgres=＃ ALTER TABLE sales_two_level RENAME TO globalsales;

postgres=＃ \d+ globalsales
            Append-Only Table "public.globalsales"
  Column  |     Type     | Modifiers | Storage  | Description
----------+--------------+-----------+----------+-------------
 trans_id | integer      |           | plain    |
 date     | date         |           | plain    |
 amount   | numeric(9,2) |           | main     |
 region   | text         |           | extended |
Compression Type: None
Compression Level: 0
Block Size: 32768
Checksum: f
Child tables: globalsales_1_prt_10,
              globalsales_1_prt_11,
              globalsales_1_prt_12,
              globalsales_1_prt_13,
              globalsales_1_prt_2,
              globalsales_1_prt_3,
              globalsales_1_prt_4,
              globalsales_1_prt_5,
              globalsales_1_prt_6,
              globalsales_1_prt_7,
              globalsales_1_prt_8,
              globalsales_1_prt_9,
              globalsales_1_prt_outlying_dates
Has OIDs: no
Options: appendonly=true
Distributed randomly
Partition by: (date)

你可以改變一個分區的名字，例如：

ALTER TABLE sales RENAME PARTITION FOR ('2008-01-01') TO jan08;

12、添加一個預設分區 (Default Partition)

你可以使用Alter指令添加一個預設分區。不滿足任何分區條件的分區會進入預設分區。

ALTER TABLE sales ADD DEFAULT PARTITION other;

ALTER TABLE sales ALTER PARTITION FOR (RANK(1)) ADD DEFAULT PARTITION other# ;

13、删除一個分區

你可以通過Alter指令删除一個分區。如果一個分區有子分區，在删除該分區的時候，它的子分區也會被删除。

對于一個分區的事實表，删除分區常用來删除保留時間視窗外的分區資料。

ALTER TABLE sales DROP PARTITION FOR (RANK(1));

14、Truncate分區

你可以通過Alter指令Truncate一個分區。在Truncate一個分區時，其子分區也會被Truncate。

ALTER TABLE sales TRUNCATE PARTITION FOR (RANK(1));

15、交換分區

你可以使用Alter Table指令來交換一個分區。交換分區操作把一個表和一個已存在分區進行交換（Swap）。你隻可以交換葉子節點分區。

分區交換通常對資料加載很有用。例如，你可以首先加載資料到一個中間表，然後把該中間表交換到分區表内部。

你也可以利用分區交換改變分區表的類型。例如：

CREATE TABLE sales (id int, date date, amt decimal(10,2))
PARTITION BY RANGE (date)
( START (date '2008-01-01') INCLUSIVE
END (date '2009-01-01') EXCLUSIVE
EVERY (INTERVAL '1 month') );

CREATE TABLE jan (LIKE sales) WITH (appendonly=true, orientation=parquet, compresstype = snappy);

INSERT INTO jan SELECT * FROM sales_1_prt_1 ;

ALTER TABLE sales EXCHANGE PARTITION FOR (RANK(1)) WITH TABLE jan;

16、分區分裂

你可以使用Alter分裂一個已經存在的分區，例如下面的例子把sales_split分區表分裂成兩個子分區：jan081to15和 jan0816to31。

CREATE TABLE sales_split (id int, date date, amt decimal(10,2))
PARTITION BY RANGE (date)
( START (date '2008-01-01') INCLUSIVE
END (date '2009-01-01') EXCLUSIVE
EVERY (INTERVAL '1 month') );

ALTER TABLE sales_split SPLIT PARTITION FOR ('2008-01-01')
AT ('2008-01-16')
INTO (PARTITION jan081to15, PARTITION jan0816to31);

如果你的分區表有Default分區的話，你隻可以通過分裂Default分區的方法來添加子分區。例如，下面的例子通過分裂 Default分區的方式添加一個jan2009分區。

CREATE TABLE sales_split_default (id int, date date, amt decimal(10,2))
PARTITION BY RANGE (date)
( START (date '2008-01-01') INCLUSIVE
END (date '2009-01-01') EXCLUSIVE
EVERY (INTERVAL '1 month'), DEFAULT PARTITION extra);

ALTER TABLE sales_split_default SPLIT DEFAULT PARTITION
START ('2009-01-01') INCLUSIVE
END ('2009-02-01') EXCLUSIVE
INTO (PARTITION jan2009, default partition);

17、修改子分區模版

你可以通過Alter指令修改子分區模版。先建立一個兩級分區表。

CREATE TABLE sales (trans_id int, date date, amount decimal(9,2), region text)
  DISTRIBUTED BY (trans_id)
  PARTITION BY RANGE (date)
  SUBPARTITION BY LIST (region)
  SUBPARTITION TEMPLATE
    ( SUBPARTITION usa VALUES ('usa'),
      SUBPARTITION asia VALUES ('asia'),
      SUBPARTITION europe VALUES ('europe'),
      DEFAULT SUBPARTITION other_regions )
  ( START (date '2014-01-01') INCLUSIVE
    END (date '2014-04-01') EXCLUSIVE
    EVERY (INTERVAL '1 month') );

下面這條指令修改子分區模版。

ALTER TABLE sales SET SUBPARTITION TEMPLATE
( SUBPARTITION usa VALUES ('usa'),
  SUBPARTITION asia VALUES ('asia'),
  SUBPARTITION europe VALUES ('europe'),
  SUBPARTITION africa VALUES ('africa'),
  DEFAULT SUBPARTITION regions );

下面這條指令可以删除子分區模版。

ALTER TABLE sales SET SUBPARTITION TEMPLATE ();

對已存在非分區表進行分區

對已存在表進行分區，你需要建立一個新的分區表，并把需要分區的表的資料導入新的表。并把相關權限配置設定好。

CREATE TABLE sales2 (LIKE sales)
PARTITION BY RANGE (date)
( START (date '2008-01-01') INCLUSIVE
   END (date '2009-01-01') EXCLUSIVE
   EVERY (INTERVAL '1 month') );

INSERT INTO sales2 SELECT * FROM sales;
DROP TABLE sales;

ALTER TABLE sales2 RENAME TO sales;
GRANT ALL PRIVILEGES ON sales TO admin;
GRANT SELECT ON sales TO guest;

1、表

2、表的存儲格式

3、表的分布

4、Hash分布和Random分布的選取

5、Hash分布的表bucketnum的選取

6、表分區

7、List分區

8、多級分區

9、檢視你的分區設計

10、添加一個分區

11、重命名分區

12、添加一個預設分區 (Default Partition)

13、删除一個分區

14、Truncate分區

15、交換分區

16、分區分裂

17、修改子分區模版

繼續閱讀

壓縮編碼M-JPEG、MPEG4、H.264

ASP程式設計中20個非常有用的例子

龍珠訓練營task04

阿裡雲天池龍珠計劃SQL訓練營打卡

阿裡雲天池龍珠計劃SQL訓練營day1

實驗樓sql進階之成績管理系統的資料操作(window)

轉詳解C#資料庫存取圖檔三大方式

Oracle的基本操作

BMP檔案結構及圖像每行位元組計算方法

磁盤結構及在Linux中的命名

SQL優化SQL語句優化的目的

JAVA高效程式設計指南

關于SQL語言

SQL語言基礎：常用的資料查詢語句

neo4j之cypher使用文檔

sqlServer根據經緯查距離