开发指南 开发指南 1、连接数据库 2、数据库管理 3、模式管理 4、表格设计 5、加载数据 6、分区表 7、序列 8、索引 9、 ANALYZE/VACUUM 10、常⽤SQL⼤全 12、常⽤SQL命令 13、⽤⼾⾃定义函数 ⽬录 Greenplum数据仓库 UDW Copyright © 2012-2021 UCloud 优刻得 3/206 128 132 132 132 greenplum 集群 FAQs 创建好数据仓库之后怎么连接到UDW？ UDW⽀持从mysql导⼊数据吗？ HDFS/Hive与UDW之间可以导⼊导出数据吗？ UDW中怎么kill掉正在执⾏的SQL语句？ 如何通过外⽹访问UDW？ 节点扩容时数量有没有什么限制？ 数据仓库价格 数据仓库价格 ⽬录 Greenplum数据仓库 UDW Copyright © 2012-2021 UCloud 产品架构 产品架构 数据仓库（UCloud Data Warehouse）是⼤规模并⾏处理数据仓库产品，基于开源的Greenplum开发的⼤规模并发、完全托管的PB级数据仓库服务。UDW可以通过SQL让数据分析更简 单、⾼效，为互联⽹、物联⽹、⾦融、电信等⾏业提供丰富的业务分析能⼒。⽀持MADlib扩展，客⼾可以在udw上使⽤MADlib的扩展功能，从⽽让机器学习变得简单，⽀持PostGIS，可以⽅便

134 External Tables 134 Database Utilities 134 Additional Supplied Modules and Extensions 134 SQL Commands 135 Database Maintenance 136 System Functions, Catalog Tables and Views 137 Greenplum Query Limitations 582 Unsupported SQL Query Features 582 Performance Regressions 583 Determining the Query Optimizer that is Used 584 Examples 585 Defining Queries 586 SQL Lexicon 586 SQL Value Expressions 586 Database 762 Uninstall the Java JAR files and Software Package 762 Writing PL/Java functions 763 SQL Declaration 763 Type Mapping 763 NULL Handling 764 Complex Types 765 Returning Complex Types 765

载分解，并使用所有的系统资源并行处理单个查询。同时 Greenplum 具备数据库 ACID 特性，运行符合 ANSI 标准 的 SQL，可以让服务器群集能够以单一数据超级计算机的方式运行，且性能比传统数据库或其他同类平台高出数十甚 至数百倍。其多种分析扩展功能支持 ANSI SQL，并通过封装扩展提供多种内置语言和附加功能。Greenplum 能够 管理各种规模的数据容量，数据量从数 GB 到数 PB greSQL、 Rcpp、RcppEigen、RobustRankAggreg 等 R 语言算法库和程序包。 此外，Greenplum 还支持最新版 Apache MADlib（可用 SQL 进行机器学习、深度学习和图分析），它支持高并行 和基于 GPU 的深度学习模型训练，内置于集群硬件中的 GPU，能帮助 Greenplum 6 的用户获得超过 CPU 2 个数量 级的性能加速 性能测试。测试过程如下： a. TPC-DS 介绍 TPC-DS 采用星型、雪花型等多维数据模式。包含 7 张事实表、17 张维度表，平均每张表含有 18 列，其工作负载包 含 99 个 SQL 查询，覆盖 SQL99 和 2003 的核心部分以及 OLAP。这个测试集包含对大数据集的统计、报表生成、联 机查询、数据挖掘等复杂应用，测试用的数据和取值是有倾斜的，与真实场景非常相似。 b.

Enhancements with GPORCA 777 Changed Behavior with GPORCA 777 GPORCA Limitations 778 Unsupported SQL Query Features 779 Performance Regressions 780 Determining the Query Optimizer that is Used 780 781 About Uniform Multi-level Partitioned Tables 782 Example 782 Defining Queries 784 SQL Lexicon 784 SQL Value Expressions 785 Column References 785 Positional Parameters 786 Subscripts 786 Field Database 960 Uninstall the Java JAR files and Software Package 960 Writing PL/Java functions 961 SQL Declaration 961 Type Mapping 962 NULL Handling 962 Complex Types 963 Returning Complex Types 963

Documentation VMware by Broadcom 37 Changed Behavior with GPORCA 786 GPORCA Limitations 787 Unsupported SQL Query Features 787 Performance Regressions 788 Determining the Query Optimizer that is Used 788 789 About Uniform Multi-level Partitioned Tables 790 Example 791 Defining Queries 793 SQL Lexicon 793 SQL Value Expressions 793 Column References 794 Positional Parameters 794 Subscripts 794 Field Database 973 Uninstall the Java JAR files and Software Package 973 Writing PL/Java functions 974 SQL Declaration 974 Type Mapping 974 NULL Handling 975 Complex Types 976 Returning Complex Types 976

Enhancements with GPORCA 756 Changed Behavior with GPORCA 757 GPORCA Limitations 758 Unsupported SQL Query Features 758 Performance Regressions 759 Determining the Query Optimizer that is Used 759 35 About Uniform Multi-level Partitioned Tables 761 Example 762 Defining Queries 763 SQL Lexicon 764 SQL Value Expressions 764 Column References 765 Positional Parameters 765 Subscripts 765 Field Database 942 Uninstall the Java JAR files and Software Package 942 Writing PL/Java functions 942 SQL Declaration 943 Type Mapping 943 NULL Handling 944 Complex Types 944 Returning Complex Types 945

Enhancements with GPORCA 749 Changed Behavior with GPORCA 750 GPORCA Limitations 751 Unsupported SQL Query Features 751 Performance Regressions 752 Determining the Query Optimizer that is Used 753 754 About Uniform Multi-level Partitioned Tables 754 Example 755 Defining Queries 757 SQL Lexicon 757 SQL Value Expressions 757 Column References 758 Positional Parameters 758 Subscripts 758 Field value of null should not be confused with a SQL NULL. While calling json_typeof('null'::json) will return null, calling json_typeof(NULL::json) will return a SQL NULL. 796 f1 f2 797 1 true [2,false] 798

361 Encrypting Data in Tables using PGP 363 Encrypting gpfdist Connections 366 Tuning SQL Queries 368 Tuning SQL Queries 0 How to Generate Explain Plans 368 How to Read Explain Plans 369 Optimizing Database 434 Uninstall the Java JAR files and Software Package 435 Writing PL/Java functions 435 SQL Declaration 436 Type Mapping 436 NULL Handling 437 Complex Types 437 Returning Complex Types 438 Language 0 About Greenplum Database PL/pgSQL 452 Greenplum Database SQL Limitations 452 The PL/pgSQL Language 452 Running SQL Commands 454 PL/pgSQL Plan Caching 454 VMware Greenplum v6.18 Documentation

370 Encrypting Data in Tables using PGP 372 Encrypting gpfdist Connections 375 Tuning SQL Queries 377 Tuning SQL Queries 0 How to Generate Explain Plans 377 How to Read Explain Plans 378 Optimizing Database 444 Uninstall the Java JAR files and Software Package 444 Writing PL/Java functions 445 SQL Declaration 445 Type Mapping 445 NULL Handling 446 Complex Types 447 Returning Complex Types 447 Language 0 About Greenplum Database PL/pgSQL 461 Greenplum Database SQL Limitations 462 The PL/pgSQL Language 462 Running SQL Commands 463 PL/pgSQL Plan Caching 463 PL/pgSQL Examples 464 Example:

Tables using PGP 298 Encrypting gpfdist Connections 301 Tuning SQL Queries 303 VMware Greenplum v6.17 Documentation VMware, Inc. 13 Tuning SQL Queries 0 How to Generate Explain Plans 303 How to Read Explain Database 369 Uninstall the Java JAR files and Software Package 370 Writing PL/Java functions 370 SQL Declaration 371 Type Mapping 371 NULL Handling 372 Complex Types 372 Returning Complex Types 373 Language 0 About Greenplum Database PL/pgSQL 387 Greenplum Database SQL Limitations 387 The PL/pgSQL Language 387 Running SQL Commands 389 PL/pgSQL Plan Caching 389 PL/pgSQL Examples 390 Example: