T_OSS Select count(*) from T_GP Group by city OSS作为“数据湖”，GP作为分析引擎 2016Postgres中国用户大会 支持外部扩展已插件形式管理 支持插件创建的语法 CREATE EXTENSION DROP EXTENSION Patch已提交社区 2016Postgres中国用户大会 HyperLogLog支持 create extension userids hll); select #userids from access_date where acc_date=current_date; 2016Postgres中国用户大会 目前支持的插件 MADLib PL/Java Pgcrypto Fuzzstringmatch PostGIS Orca hll 2016Postgres中国用户大会 完善的JSON数据类型支持 rds_superuser; Alter role my_user nords_superuser; 为该用户放开部分superuser的权限 查看其它用户数据 查看所有连接信息 杀连接 创建和删除插件 2016Postgres中国用户大会 解决OOM问题 实例的OOM有时很频繁，同时OOM很难提前监控 我们的办法 利用外部脚本监控cgroup中的内存统计 发生内存水位较高时，将实例移入公共

接⼊ 接⼊ Zeppelin Zeppelin 简介 简介 Zeppelin 是⼀个开源的 Apache 的孵化项⽬. 它是⼀款基本 web 的 notebook ⼯具，⽀持交互式数据分析。通过插件的⽅式接⼊各种解释器（interpreter），使得⽤⼾能够以特定的语⾔ 或数据处理后端来完成交互式查询，并快速实现数据可视化。 部署 部署 Zeppelin 1) 安装 Java Zeppelin 下载安装https://www.elastic.co/downloads/logstash 2. logstash依赖java环境、确保已经安装过java 3. 安装logstash-output-kafka插件 4. 配置logstash收集⽇志写⼊Kafka 参考配置如下（更多参数和含义请参考官⽅⽂档）： UDW 使⽤案例 Greenplum数据仓库 UDW Copyright © 2012-2021

3A Segment 3B Segment 3C Segment 3D Segment 1A Segment 1B Segment 1C Segment 1D Segment 2A Segment 2B Segment 2C Segment 2D Segment 3A Segment 3B Segment 3C Segment 3D Segment 1A Segment 1B Segment 1C 1C Segment 1D Segment 2A Segment 2B Segment 2C Segment 2D Segment 3A Segment 3B Segment 3C Segment 3D 分布和分区 SELECT COUNT(*) FROM orders WHERE order_date >= ‘Oct 20 2007’ AND order_date < ‘Oct 27 2007’ 1C Segment 1D Segment 2A Segment 2B Segment 2C Segment 2D Segment 3A Segment 3B Segment 3C Segment 3D 10 Pivotal Confidential–Inter nal Use Only 多级分区存储 • 哈希Distribution：数据均 匀的分布到各个数据节点 • 范围分区： 数据节点内部，

Segment 1D Segment 2A Segment 2B Segment 2D Segment 3A Segment 3B Segment 2C Segment 3C Segment 3D 分区 SELECT COUNT(*) FROM orders WHERE order_date >= ‘Oct 1 2007’ AND order_date <= ‘Oct 31 2007’ 1C Segment 1D Segment 2A Segment 2B Segment 2C Segment 2D Segment 3A Segment 3B Segment 3C Segment 3D 15 Pivotal Confidential–Internal Use Only 递归查询 表‘SALES’ 表‘SALES’ ■ 层次结构 ■ 树状结构 WITH RECURSIVE 3C Segment 3D 直接分发 SELECT * FROM orders WHERE id = 1； Segment 1A Segment 1B Segment 1C Segment 1D Segment 2A Segment 2B Segment 2C Segment 2D Segment 3A Segment 3B Segment 3C Segment 3D UPDATE orders

对运维安全无能为力 基于pgcypto的加密 • 可以满足数据安全要求 • 非原生方案 • 问题很多 基于pgcypto的数据加密方案 pgcypto Postgresql社区提供的一款简单加密插件 • https://www.postgresql.org/docs/13/pgcrypto.html • https://github.com/greenplum-db/gpdb/tree/m

备，或者直接备份到独立的存储设备上，最常见的方式是通过NFS的方式将远程存储挂 载为一个本地的路径，直接将备份数据存储到该路径。 通过使用gpbacku和gprestore的存储插件，可以发送备份数据到远程存储，或 者从远程存储恢复到数据库中，目前GP数据库的备份恢复存储插件支持Amazon的S3 协议和Dell EMC的Data Domain存储设备。更多内容可以参考gpbackup和 gprestore命令的详细说明。

5 and install PostGIS 2.5.4. Note: The PostGIS topology extension postgis_topology and the PostGIS 3D and geoprocessing extension postgis_sfcgal are not supported by Greenplum PostGIS and are not included 'POLYGON((0 0 0,0 5 0,5 5 0,5 0 0,0 0 0))', '3D Square'); INSERT INTO geom_test ( gid, geom, name ) VALUES ( 2, 'LINESTRING(1 1 1,5 5 5,7 7 5)', '3D Line' ); INSERT INTO geom_test ( gid, geom, name does not support the following features: The PostGIS topology extension postgis_topology The PostGIS 3D and geoprocessing extension postgis_sfcgal A small number of user defined functions and aggregates

5 and install PostGIS 2.5.4. Note: The PostGIS topology extension postgis_topology and the PostGIS 3D and geoprocessing extension postgis_sfcgal are not supported by Greenplum PostGIS and are not included 'POLYGON((0 0 0,0 5 0,5 5 0,5 0 0,0 0 0))', '3D Square'); INSERT INTO geom_test ( gid, geom, name ) VALUES ( 2, 'LINESTRING(1 1 1,5 5 5,7 7 5)', '3D Line' ); INSERT INTO geom_test ( gid, geom, name does not support the following features: The PostGIS topology extension postgis_topology The PostGIS 3D and geoprocessing extension postgis_sfcgal A small number of user defined functions and aggregates

5 and install PostGIS 2.5.4. Note: The PostGIS topology extension postgis_topology and the PostGIS 3D and geoprocessing extension postgis_sfcgal are not supported by Greenplum PostGIS and are not included 'POLYGON((0 0 0,0 5 0,5 5 0,5 0 0,0 0 0))', '3D Square'); INSERT INTO geom_test ( gid, geom, name ) VALUES ( 2, 'LINESTRING(1 1 1,5 5 5,7 7 5)', '3D Line' ); INSERT INTO geom_test ( gid, geom, name does not support the following features: The PostGIS topology extension postgis_topology The PostGIS 3D and geoprocessing extension postgis_sfcgal A small number of user defined functions and aggregates

the PostGIS TIGER geocoder. Note: The PostGIS topology extension postgis_topology and the PostGIS 3D and geoprocessing extension postgis_sfcgal are not supported by Greenplum PostGIS and are not included 'POLYGON((0 0 0,0 5 0,5 5 0,5 0 0,0 0 0))', '3D Square'); INSERT INTO geom_test ( gid, geom, name ) VALUES ( 2, 'LINESTRING(1 1 1,5 5 5,7 7 5)', '3D Line' ); INSERT INTO geom_test ( gid, geom, name does not support the following features: The PostGIS topology extension postgis_topology The PostGIS 3D and geoprocessing extension postgis_sfcgal A small number of user defined functions and aggregates