前言

? ? ? ? Greenplum 的剩余部分主要其實主要就是 DDL 和之前學的 MySQL 不大一樣，畢竟 Greenplum 是基于 PostgreSQL 數據庫的，不過那些?DML 和 MySQL、Hive 基本上大差不差，所以就沒有必要浪費時間了。

1、DDL

1.1、庫操作

1.1.1、創建數據庫

語法

CREATE DATABASE name
[ [ WITH ] [ OWNER [=] db_owner ] -- 指定數據庫所有者[ TEMPLATE [=] template ] -- 指定數據庫模板(規范和約束),默認是 template1[ ENCODING [=] encoding ] -- 指定當前數據庫的編碼[ TABLESPACE [=] tablespace ]  -- 表的命名空間(默認是 pg_default)[CONNECTION LIMIT [=] connlimit ]  -- 限制當前數據庫的最大連接數 -1代表無限制
]

案例

CREATE DATABASE my_db1
WITH OWNER gpadmin
ENCODING 'utf-8'
TABLESPACE pg_dafault
CONNECTION LIMIE 10;

1.1.2、切換數據庫

如果是使用 PSQL 的 CLI 命令行的話，我們可以使用下面的命令來切換數據庫：

\c my_db1

使用 DataGrip 只能通過下面的按鈕來切換

?1.1.3、創建 schema

????????schema 相當于是一種歸類分組的作用，畢竟海量數據場景下，一個數據庫下面可能有成千上萬張表，Greenplum 在庫和表之間添加了一層 schema 使得表可以更好被管理。

CREATE SCHEMA my_biz;

1.1.4、查詢所有數據庫

同樣，如果在 PSQL 的 CLI 命令行的話可以通過下面的命令來查看所有數據庫：

\l

也可以使用下面的 SQL 命令：

-- 顯示所有的庫
SELECT * FROM pg_database;

1.1.5、刪除數據庫

?如果要刪除數據庫，必須先離開該數據庫（\c 切換到別的數據庫）才能刪除

DROP DATABASE my_db1;

?1.2、表操作

1.2.1、創建表

語法

CREATE [EXTERNAL] TABLE table_name( -- 創建外部表,建表的同時可以指定一個實際數據的路徑（location 可以是linux，也可以是 HDFS）column1 datatype [NOT NULL] [DEFAULT] [CHECK] [UNIQUE], -- 字段約束column2 datatype,column3 datatype,.....columnN datatype,[PRIMARY KEY()] -- 指定當前主鍵
)[ WITH ()] -- 定義數據追加方式、壓縮格式、壓縮級別等[LOCATION()] -- 如果使用外部表才會配合使用這個關鍵字[FORMAT] -- 定義當前表的存儲格式[COMMENT] -- 注釋[PARTITION BY] -- 分區字段 同時也是創建分區表的關鍵字[DISTRIBUTE BY ()]; -- 指定分布數據的鍵值（比如使用哈希算法計算數據的存儲位置）

內部表和外部表的區別

（1）內部表（Regular Tables）

數據存儲：內部表的數據直接存儲在 GreenPlum 數據庫的數據文件中。這意味著數據被物理存儲在數據庫服務器上。

事務管理：內部表完全支持事務管理。這包括 ACID 屬性（原子性、一致性、隔離性和持久性），確保數據完整性和可靠性。

索引和約束：你可以在內部表上創建索引和約束，這有助于提高查詢性能和維護數據完整性。

管理和維護：內部表可以使用數據庫的全部管理和維護功能，如備份和恢復。

適用性：適用于需要高性能查詢和事務完整性的數據。

（2）外部表（External Tables）

數據存儲：外部表的數據存儲在數據庫外部，如在文件系統、Hadoop HDFS 或任何可通過 SQL/MED（SQL Management of External Data）訪問的數據源。外部表僅存儲數據的元數據和位置信息。

事務管理：外部表不支持事務管理。它們主要用于讀取和加載操作，不保證 ACID 屬性。

索引和約束：由于數據實際存儲在外部，你不能在外部表上創建索引或強制執行數據庫級別的約束。

管理和維護：外部表的管理相對簡單，因為它們只是對外部數據源的引用。備份和恢復通常不適用于外部表本身，而是應用于數據源。

適用性：適用于 ETL（Extract, Transform, Load）操作，即從外部數據源提取數據，然后可能將其轉換和加載到內部表中進行進一步處理。

案例

????????創建外部表這里使用的數據源是 linux 文件系統下的一個 csv文件，需要我們使用 Greenplum 為該路徑開啟一個 gpfdist 服務：?

-- -d指定數據所在目錄 -p 指定端口
gpfdist -d ./ -p 8081 &

-- 創建內部表
CREATE TABLE doctor(doctor_id serial primary key ,name varchar(100),department varchar(100),hire_date date
);INSERT INTO doctor (name, department, hire_date) VALUES('小美','護士','2024-07-08'),('二狗','呼吸內科','2024-07-08'),('鐵蛋','骨科','2024-07-08');DROP EXTERNAL TABLE supplier;
-- 外部表
CREATE EXTERNAL TABLE supplier(id varchar(10),name varchar(20),job varchar(20),birthday varchar(20))
LOCATION ('gpfdist://hadoop102:8081/patient.csv')
FORMAT 'CSV';-- 查詢外部表
SELECT * FROM supplier;

查詢結果：

??

1.2.2、修改表

修改表名

ALTER TABLE table_name RENAME TO new_name;

增加/修改/替換列信息

-- 新增列
ALTER TABLE doctor ADD COLUMN addr varchar(20);
-- 更新列名
ALTER TABLE doctor  RENAME addr TO address;
-- 更新列數據類型
ALTER TABLE doctor ALTER COLUMN age TYPE int;
-- 刪除列信息
ALTER TABLE doctor DROP COLUMN address;

1.2.3、清空表

注意：只能清除內部表，但是不能清除外部表?

TRUNCATE TABLE doctor;

1.2.4、刪除表

DROP [EXTERNAL] TABLE table_name;

2、DML

2.1、數據導入

2.1.1、copy 方式

語法

COPY table_name FROM file_path DELIMITER sep;

創建內部表并準備數據：

CREATE TABLE student(name varchar(20),department varchar(20),age int
);

導入并查詢（這里的文件路徑為主節點的文件路徑，上面的外部表數據源雖然也在主節點但是需要通過 gpfdist 協議，和這里不一樣）：

COPY student FROM '/home/gpadmin/software/test/student.csv' DELIMITER ',';SELECT * FROM student;

查詢結果：

?

2.1.2、通過查詢向表中查詢數據

INSERT INTO student
SELECT name,job AS department,0 AS age FROM supplier;

2.1.3、通過查詢語句創建并加載數據

CREATE TABLE student2 AS SELECT * FROM student;

2.2、數據導出

COPY student2 TO '/home/gpadmin/software/test/student.txt';

2.3、數據更新和刪除

?2.3.1、更新數據

UPDATE student2 SET age = 18,name = 'test' WHERE age = 0;

2.3.2、刪除數據

DELETE FROM student2 WHERE age = 20;

總結?

????????剩下的一些查詢語句已經在 MySQL、HQL 中練習很多了，這里不再浪費時間了，下去之后多花點時間練練 SQL 題就OK了。

? ? ? ? 關于 Greenplum 再深入的內容比如分布式事務、數據備份與遷移等資料網上比較少，之后慢慢精進。