Greenplum6 数据库数据库学习_基本语法-低调大师

Greenplum6 数据库数据库学习_基本语法

2021-04-14 1197

Greenplum6 数据库分布

1. hash分布
2. 随机分布
3. 复制分布

基本语法介绍

1. 获取语法

[gpadmin@mdw logs]$ psql
psql (9.4.24)
Type "help" for help.

postgres=# \h
Available help:
  ABORT                            ALTER TEXT SEARCH TEMPLATE       CREATE RESOURCE GROUP            DROP FUNCTION                    LOAD
  ALTER AGGREGATE                  ALTER TRIGGER                    CREATE RESOURCE QUEUE            DROP GROUP                       LOCK
  ALTER COLLATION                  ALTER TYPE                       CREATE ROLE                      DROP INDEX                       MOVE
  ALTER CONVERSION                 ALTER USER                       CREATE RULE                      DROP LANGUAGE                    NOTIFY
  ALTER DATABASE                   ALTER USER MAPPING               CREATE SCHEMA                    DROP MATERIALIZED VIEW           PREPARE
  ALTER DEFAULT PRIVILEGES         ALTER VIEW                       CREATE SEQUENCE                  DROP OPERATOR                    PREPARE TRANSACTION
  ALTER DOMAIN                     ANALYZE                          CREATE SERVER                    DROP OPERATOR CLASS              REASSIGN OWNED
  ALTER EVENT TRIGGER              BEGIN                            CREATE TABLE                     DROP OPERATOR FAMILY             REFRESH MATERIALIZED VIEW
  ALTER EXTENSION                  CHECKPOINT                       CREATE TABLE AS                  DROP OWNED                       REINDEX
  ALTER EXTERNAL TABLE             CLOSE                            CREATE TABLESPACE                DROP PROTOCOL                    RELEASE SAVEPOINT
  ALTER FOREIGN DATA WRAPPER       CLUSTER                          CREATE TEXT SEARCH CONFIGURATION DROP RESOURCE GROUP              RESET
  ALTER FOREIGN TABLE              COMMENT                          CREATE TEXT SEARCH DICTIONARY    DROP RESOURCE QUEUE              REVOKE
  ALTER FUNCTION                   COMMIT                           CREATE TEXT SEARCH PARSER        DROP ROLE                        ROLLBACK
  ALTER GROUP                      COMMIT PREPARED                  CREATE TEXT SEARCH TEMPLATE      DROP RULE                        ROLLBACK PREPARED
  ALTER INDEX                      COPY                             CREATE TRIGGER                   DROP SCHEMA                      ROLLBACK TO SAVEPOINT
  ALTER LANGUAGE                   CREATE AGGREGATE                 CREATE TYPE                      DROP SEQUENCE                    SAVEPOINT
  ALTER LARGE OBJECT               CREATE CAST                      CREATE USER                      DROP SERVER                      SECURITY LABEL
  ALTER MATERIALIZED VIEW          CREATE COLLATION                 CREATE USER MAPPING              DROP TABLE                       SELECT
  ALTER OPERATOR                   CREATE CONVERSION                CREATE VIEW                      DROP TABLESPACE                  SELECT INTO
  ALTER OPERATOR CLASS             CREATE DATABASE                  DEALLOCATE                       DROP TEXT SEARCH CONFIGURATION   SET
  ALTER OPERATOR FAMILY            CREATE DOMAIN                    DECLARE                          DROP TEXT SEARCH DICTIONARY      SET CONSTRAINTS
  ALTER PROTOCOL                   CREATE EVENT TRIGGER             DELETE                           DROP TEXT SEARCH PARSER          SET ROLE
  ALTER RESOURCE GROUP             CREATE EXTENSION                 DISCARD                          DROP TEXT SEARCH TEMPLATE        SET SESSION AUTHORIZATION
  ALTER RESOURCE QUEUE             CREATE EXTERNAL TABLE            DO                               DROP TRIGGER                     SET TRANSACTION
  ALTER ROLE                       CREATE FOREIGN DATA WRAPPER      DROP AGGREGATE                   DROP TYPE                        SHOW
  ALTER RULE                       CREATE FOREIGN TABLE             DROP CAST                        DROP USER                        START TRANSACTION
  ALTER SCHEMA                     CREATE FUNCTION                  DROP COLLATION                   DROP USER MAPPING                TABLE
  ALTER SEQUENCE                   CREATE GROUP                     DROP CONVERSION                  DROP VIEW                        TRUNCATE
  ALTER SERVER                     CREATE INDEX                     DROP DATABASE                    END                              UNLISTEN
  ALTER SYSTEM                     CREATE LANGUAGE                  DROP DOMAIN                      EXECUTE                          UPDATE
  ALTER TABLE                      CREATE MATERIALIZED VIEW         DROP EVENT TRIGGER               EXPLAIN                          VACUUM
  ALTER TABLESPACE                 CREATE OPERATOR                  DROP EXTENSION                   FETCH                            VALUES
  ALTER TEXT SEARCH CONFIGURATION  CREATE OPERATOR CLASS            DROP EXTERNAL TABLE              GRANT                            WITH
  ALTER TEXT SEARCH DICTIONARY     CREATE OPERATOR FAMILY           DROP FOREIGN DATA WRAPPER        INSERT                           
  ALTER TEXT SEARCH PARSER         CREATE PROTOCOL                  DROP FOREIGN TABLE               LISTEN                           
postgres=#

2. 创建数据库

[gpadmin@mdw logs]$ createdb testDB -E utf-8
[gpadmin@mdw logs]$ psql -h 10.10.10.101 -p 5432 -d testDB -U gpadmin
psql (9.4.24)
Type "help" for help.

testDB=# \q
[gpadmin@mdw logs]$ export PGDATABASE=testDB
[gpadmin@mdw logs]$ psql
psql (9.4.24)
Type "help" for help.

testDB=#

3. 建表语句

-  GreenPlum中创建表需要指定表的分布键。
-  如果表需要用某个字段分区，可以通过partition by 将表建成分区表。
-  可以使用like操作创建与like的表一样结构的表，功能类似create table t1 as select * from t2 limit 0。
-  可以使用inherits实现表的继承，具体实现参考postgresql文档。

--语法查询
\h create table
--创建表
create table test001(id int,name varchar(128)); --id 为分布键
create table test002(id int,name varchar(128)) distributed by (id); --同上

testDB=#  create table test001(id int,name varchar(128));
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE
testDB=# create table test002(id int,name varchar(128)) distributed by (id); 
CREATE TABLE
testDB=# 

create table test003(id int,name varchar(128)) distributed by (id,name) --多个分布键
testDB=# create table test003(id int,name varchar(128)) distributed by (id,name);
CREATE TABLE

create table test004(id int,name varchar(128)) distributed randomly; --随机分布键
testDB=# create table test004(id int,name varchar(128)) distributed randomly; 
CREATE TABLE

create table test005(id int primary key ,name varchar(128));
create table test006(id int unique ,name varchar(128));

testDB=# create table test005(id int primary key ,name varchar(128));
CREATE TABLE
testDB=# create table test006(id int unique ,name varchar(128));
CREATE TABLE
testDB=# 

采用随机分布策略的表默认将主键，或者唯一键作为分布键，因为每个Segment都是一个单一的数据库，单个数据库可以确保唯一性，多个数据库节点就无法保证全局的跨库唯一性，故只能按照唯一键分布，同一个值的数据都在一个节点上，以此来保证唯一性。

--如果指定的分布键与主键盘不一样，那么分布键会被更改为主键。在greenplum6 中这句话貌似显示不兼容，如下：

create table test007(id int unique,name varchar(128)) distributed by (id,name);

testDB=# create table test007(id int unique,name varchar(128)) distributed by (id,name);
ERROR:  UNIQUE constraint and DISTRIBUTED BY definitions are incompatible
HINT:  When there is both a UNIQUE constraint and a DISTRIBUTED BY clause, the DISTRIBUTED BY clause must be a subset of the UNIQUE constraint.
testDB=# 
按照它的要求进行修改，我们distribute by 修改成id
testDB=# create table test007(id int unique,name varchar(128)) distributed by (id);
CREATE TABLE
testDB=# 

---创建一模一样的 表
create table test_like (like test001);

--使用like 创建表的时候，只是表结构会跟原表一摸一样，表的特殊属性并不会一样，例如压缩，只增（appendonly）等属性，如果不指定分布键，默认分布键与原表一致。

testDB=# create table test_like (like test001);
NOTICE:  table doesn't have 'DISTRIBUTED BY' clause, defaulting to distribution columns from LIKE table
CREATE TABLE
testDB=# 

create table as 和select into 有一样的功能。都可以根据直接执行select 的结果创建一个新的表。创建一个表的时候，如果默认不指定分布键，那么Greenplum根据执行select 得到的结果集来选择，不用再次重分布数据的字段作为表的分布键。

testDB=# create table test2 as select * from test002;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause. Creating a NULL policy entry.
SELECT 4
testDB=# create table test1 as select * from test001;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause. Creating a NULL policy entry.
SELECT 4

手工加入distributed by，指定分布键，这样就会根据指定分布键再建表。
testDB=# create table test02 as select * from test002 distributed by(id);
SELECT 4
select into 不能指定分布键，只能使用默认的分布键
testDB=# select *  into test01 from test001;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause. Creating a NULL policy entry.
SELECT 4

4. 插入语句

插入语句 
执行insert语句注意分布键不要为空,否则分布键默认变成null',数据都被保存到一个节点上会导致分布不均

insert into test001 values (100,'tom'),(101,'lily'),(102,'jack'),(103,'linda');
insert into test002 values (200,'tom'),(101,'lily'),(202,'jack'),(103,'linda');

5. 更新语句

不能批量对分布键执行update,因为分布键执行update需要将数据重分布.

testDB=# update test002 set id=203 where id=202;
UPDATE 1
testDB=#

6. 删除语句delete--truncate

在Greenplum 3.x 的版本中，如果delete 操作涉及子查询，子查询的结果还涉及到数据重分布，这样的删除语句会报错，Greenplum 4.x以上，支持该操作。
testDB=# delete from test001 where name in (select name from test002);
DELETE 4
testDB=# 

如果对整张表执行delete会比较慢，建议使用truncate.

truncate执行truncate直接删除表的物理文件，然后创建新的数据文件。如果有sql正在操作这张表，那么truncate会被锁住，直到表上面的所有锁会被释放。

7. 查询语句

testDB=# select * from test001 x,test002 y where x.id=y.id;
 id  | name  | id  | name  
-----+-------+-----+-------
 103 | linda | 103 | linda
 101 | lily  | 101 | lily
(2 rows)

8. 执行计划

testDB=# explain select * from test001 x,test002 y where x.id=y.id;
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Gather Motion 6:1  (slice1; segments: 6)  (cost=0.00..862.00 rows=5 width=18)
   ->  Hash Join  (cost=0.00..862.00 rows=1 width=18)
         Hash Cond: (test001.id = test002.id)
         ->  Seq Scan on test001  (cost=0.00..431.00 rows=1 width=9)
         ->  Hash  (cost=431.00..431.00 rows=1 width=9)
               ->  Seq Scan on test002  (cost=0.00..431.00 rows=1 width=9)
 Optimizer: Pivotal Optimizer (GPORCA)
(7 rows)

testDB=# explain select * from test001 x,test002 y where x.id=y.id;
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Gather Motion 6:1  (slice1; segments: 6)  (cost=0.00..862.00 rows=5 width=18)
   ->  Hash Join  (cost=0.00..862.00 rows=1 width=18)
         Hash Cond: (test001.id = test002.id)
         ->  Seq Scan on test001  (cost=0.00..431.00 rows=1 width=9)
         ->  Hash  (cost=431.00..431.00 rows=1 width=9)
               ->  Seq Scan on test002  (cost=0.00..431.00 rows=1 width=9)
 Optimizer: Pivotal Optimizer (GPORCA)
(7 rows)

第一步： 数据库先顺序扫描test002表，扫描大概有431单位的消耗，有1行数据，平均长度为9个字节。
其中1是一个估算值，是一个segment的数据量，如果数据分布均匀，大概是总数据量除以segment的个数。
由于这个gp集群有6个segment节点，因此可以推算test002大概有6条数据。

第二步： 扫描test002表，并计算hash值，将其保存在内存中。

第三步： 顺序扫描test001表

第四步： 在扫描test001表的过程中，与test002表进行hash后的结果关联（hash join）,关联条件是(test001.id = test002.id)

第五步：将数据汇总到master上，master 将数据结果进行汇总并展现。

常用函数

字符串函数


testDB=# select 'green'||'plum' as dbname;
  dbname   
-----------
 greenplum
(1 row)

testDB=# select split_part(col,'|',1) ,split_part(col,'|',2) from (values('hello|world!'),('greenplum|database')) t(col);   --values  特有语法，可以堪称一张表，有两行数据，表明为t,字段名为col
 split_part | split_part 
------------+------------
 hello      | world!
 greenplum  | database
(2 rows)

values('hello|world!'),('greenplum|database');
testDB=# values('hello|world!'),('greenplum|database');
      column1       
--------------------
 hello|world!
 greenplum|database
(2 rows)

testDB=# 

select substr('hello world!',2,3);

select position('world' in 'hello world');

testDB=# select substr('hello world!',2,3);
 substr 
--------
 ell
(1 row)

testDB=# select position('world' in 'hello world');
 position 
----------
        7
(1 row)

时间函数

[root@mdw ~]# su - gpadmin
Last login: Wed Apr 14 14:45:20 CST 2021 on pts/0
^[[A^[[A[gpadmin@export PGDATABASE=testDB
[gpadmin@mdw ~]$ psql -h 10.10.10.101 -p 5432 -d testDB -U gpadmin
psql (9.4.24)
Type "help" for help.

testDB=# select '2021-03-23 1:0:0'::timestamp + interval '10 days 2 hours 10 seconds';
      ?column?       
---------------------
 2021-04-02 03:00:10
(1 row)

testDB=# select now(),current_date,current_time,current_timestamp;
              now              |    date    |       timetz       |              now              
-------------------------------+------------+--------------------+-------------------------------
 2021-04-14 15:48:52.166167+08 | 2021-04-14 | 15:48:52.166167+08 | 2021-04-14 15:48:52.166167+08
(1 row)

当月第一天

testDB=# select date_trunc('months',now())::date;
 date_trunc 
------------
 2021-04-01
(1 row)

获取当前时间距离2021-01-3 10:10:10 过了多少秒
testDB=# select extract(EPOCH from now() -'2021-01-3 10:10:10');
   date_part   
---------------
 8746923.35616
(1 row)

时间域有重叠返回t，不重叠f
testDB=# select (DATE '2020-02-16',DATE '2020-12-21') OVERLAPS (DATE '2020-10-30',DATE '2021-1-15');
 overlaps 
----------
 t
(1 row)

testDB=# select (DATE '2020-02-16',INTERVAL '100 days') OVERLAPS (DATE '2020-10-30',DATE '2021-1-15');
 overlaps 
----------
 f
(1 row)

testDB=#

其他常用函数

* 序列号生成函数
testDB=# select * from generate_series(10,20);
 generate_series 
-----------------
              10
              11
              12
              13
              14
              15
              16
              17
              18
              19
              20
(11 rows)

testDB=# create table test_gen as select generate_series(1,10000) as id ,'hello'::text as name distributed by (id);
SELECT 10000
testDB=# select sum(num) from generate_series(1,2000,2) num;
   sum   
---------
 1000000
(1 row)

* 字符串列转行函数
testDB=# create table test_string (id integer,str varchar(50)) distributed by (id);
CREATE TABLE
testDB=# insert into test_string values(1,'hello'),(1,'world'),(2,'greenplum'),(2,'database'),(2,'system');
INSERT 0 5
testDB=# select * from test_string;
 id |    str    
----+-----------
  2 | greenplum
  2 | database
  2 | system
  1 | hello
  1 | world
(5 rows)

testDB=# select id,string_agg(str,'|' order by str) from test_string group by id;
 id |        string_agg         
----+---------------------------
  2 | database|greenplum|system
  1 | hello|world
(2 rows)

testDB=# select id,string_agg(str,'|') from test_string group by id;
 id |        string_agg         
----+---------------------------
  2 | greenplum|database|system
  1 | hello|world
(2 rows)

* 字符串行转列
testDB=# create table test_string2 as select id,string_agg(str,'|') as str from test_string group by id  distributed by (id);
SELECT 2
testDB=# select * from test_string2                                        
;
 id |            str            
----+---------------------------
  2 | greenplum|database|system
  1 | hello|world
(2 rows)

testDB=# select id,regexp_split_to_table(str,E'\\|') str from test_string2;
 id |    str    
----+-----------
  2 | greenplum
  2 | database
  2 | system
  1 | hello
  1 | world
(5 rows)

* hash函数 

testDB=# select md5('helloworld');
               md5                
----------------------------------
 fc5e038d38a57032085441e7fe7010b0
(1 row)

testDB=# select hashbpchar('helloworld');
 hashbpchar 
------------
 1836618988
(1 row)

testDB=#

## 参考

```
https://www.cnblogs.com/kingle-study/p/10550785.html
《GreenPlum企业应用实战》
```

微信关注我们

原文链接：https://blog.51cto.com/lishiyan/2705450

转载内容版权归作者及来源网站所有！

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

智慧城市：大数据运营中心 IOC —— Web GIS 地图应用

IOC（Intelligent Operations Center）——智慧城市智能运营中心就是智慧城市的大脑，是建立在各个智慧应用系统之上的系统。通过对政府各职能部门的业务信息共享与整合，聚焦城市运行监测、分析决策、可视化指挥、应急管理等环节，对人口统计、民生服务、信访举报、产业经济、突发事件等一系列综合指标进行有效监控，并围绕网格化管理、综合执法、环境卫生、园林绿化等重点领域，提升城市运行管理水平和突发事件的处置效率。打造智慧市政、智慧城管、智慧社区等智慧型城市。 HT for Web GIS产品的定位在于运用产品强大的可视化技术，将地理信息系统（Geographic Information System，GIS）的数据进行丰富的可视化展示。以城市为基础，对城市各类基础设施数据以更加多样化形式进行可视化展示；将 GIS 数据和云计算、大数据、物联网等技术相结合，构建真正的数字经济，数字城市，数字中国；以时空为基础，通过可视化分析技术，对城市的规划、布局、分析和决策提供技术支撑，推进城市数字化转换和建设。图扑软件（Hightopo）总部正位于美丽的鹭岛厦门，厦门是一个集文化、生态、...

2021-04-14

720

前面做过jenkins发布nodejs项目，使用ssh插件，这回没有使用ssh插件。 1、将项目、发布动作、主机作为参数，配置参数化构建 2、配置拉取git仓库项目 3、构建环境选项nodejs 4、编写构建脚本 url="harbor.xxx.com:10443/library" opt="/opt/kubernetes/bin/kubectl" value=`sshroot@$host"$opt-nwehgcgetdeploy$project"|tail-1|awk'{print\$1}'` VER=`sshroot@$host"cd/home/lucky-front/$project;grep$url$project.yaml|cut-d":"-f4"`##这个$VER是取docker镜像的标签 exportNODE_HOME=/var/jenkins_home/node-v14.15.4-linux-x64 exportPATH=$PATH:$NODE_HOME/bin exportPATH=$PATH:/root/.npm-global/bin update(){ #npmi...

2021-04-14

715

资源下载

更多资源

Mario

马里奥是站在游戏界顶峰的超人气多面角色。马里奥靠吃蘑菇成长，特征是大鼻子、头戴帽子、身穿背带裤，还留着胡子。与他的双胞胎兄弟路易基一起，长年担任任天堂的招牌角色。

腾讯云软件源

为解决软件依赖安装时官方源访问速度慢的问题，腾讯云为一些软件搭建了缓存服务。您可以通过使用腾讯云软件源站来提升依赖包的安装速度。为了方便用户自由搭建服务架构，目前腾讯云软件源站支持公网访问和内网访问。

Rocky Linux

Rocky Linux（中文名：洛基）是由Gregory Kurtzer于2020年12月发起的企业级Linux发行版，作为CentOS稳定版停止维护后与RHEL（Red Hat Enterprise Linux）完全兼容的开源替代方案，由社区拥有并管理，支持x86_64、aarch64等架构。其通过重新编译RHEL源代码提供长期稳定性，采用模块化包装和SELinux安全架构，默认包含GNOME桌面环境及XFS文件系统，支持十年生命周期更新。

Sublime Text

Sublime Text具有漂亮的用户界面和强大的功能，例如代码缩略图，Python的插件，代码段等。还可自定义键绑定，菜单和工具栏。Sublime Text 的主要功能包括：拼写检查，书签，完整的 Python API ， Goto 功能，即时项目切换，多选择，多窗口等等。Sublime Text 是一个跨平台的编辑器，同时支持Windows、Linux、Mac OS X等操作系统。