VMware Tanzu Greenplum

https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/
Admin
VMware Tanzu Greenplum is a massively parallel processing SQL database built on PostgreSQL for...
Tokens:1,114,002
Snippets:19,071
Trust Score:9.9
Update:3 months ago
Show doc for...
Tokens:
Raw
### Execute the ODBC Installer

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/datadirect-datadirect_ODBC_71.html

Run the installer binary as the root user to begin the installation process.

```bash
$ ./PROGRESS_DATADIRECT_ODBC_LINUX_64_INSTALL.bin
```

--------------------------------

### View PL/Container Installation Progress

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/analytics-pl_container.html

Example output displayed by the plcontainer utility during the image installation process.

```text
20200127:21:54:43:004607 plcontainer:mdw:gpadmin-[INFO]:-Checking whether docker is installed on all hosts...
20200127:21:54:43:004607 plcontainer:mdw:gpadmin-[INFO]:-Distributing image file /home/gpadmin/plcontainer-python-images-1.5.0.tar to all hosts...
20200127:21:54:55:004607 plcontainer:mdw:gpadmin-[INFO]:-Loading image on all hosts...
20200127:21:55:37:004607 plcontainer:mdw:gpadmin-[INFO]:-Removing temporary image files on all hosts...
```

--------------------------------

### SHOW ALL Example

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/ref_guide-sql_commands-SHOW.html

Displays the current values of all system configuration parameters.

```sql
SHOW ALL;
       name       | setting |                  description
------------------+---------+----------------------------------------------------
 application_name | psql    | Sets the application name to be reported in sta...
       .
       .
       .
 xmlbinary        | base64  | Sets how binary values are to be encoded in XML.
 xmloption        | content | Sets whether XML data in implicit parsing and s...
(331 rows)
```

--------------------------------

### Create Example Database Objects

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/admin_guide-ddl-ddl-view-find-depend.html

Sets up tables, functions, and views to demonstrate dependency tracking.

```sql
CREATE TABLE t1 (
   id integer PRIMARY KEY,
   val text NOT NULL
);

INSERT INTO t1 VALUES
   (1, 'one'), (2, 'two'), (3, 'three');

CREATE FUNCTION f() RETURNS text
   LANGUAGE sql AS 'SELECT ''suffix''::text';

CREATE VIEW v1 AS
  SELECT max(id) AS id
  FROM t1;
 
CREATE VIEW v2 AS
  SELECT t1.val
  FROM t1 JOIN v1 USING (id);
 
CREATE VIEW v3 AS
  SELECT val || f()
  FROM t1;

CREATE VIEW v5 AS
  SELECT f() ;

CREATE SCHEMA mytest ;

CREATE TABLE mytest.tm1 (
   id integer PRIMARY KEY,
   val text NOT NULL
);

INSERT INTO mytest.tm1 VALUES
   (1, 'one'), (2, 'two'), (3, 'three');

CREATE VIEW vm1 AS
  SELECT id FROM mytest.tm1 WHERE id < 3 ;

CREATE VIEW mytest.vm1 AS
  SELECT id FROM public.t1 WHERE id < 3 ;

CREATE VIEW vm2 AS
  SELECT max(id) AS id
  FROM mytest.tm1;

CREATE VIEW mytest.v2a AS
  SELECT t1.val
  FROM public.t1 JOIN public.v1 USING (id);
```

--------------------------------

### Install Wheel Package on Windows

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/ref_guide-modules-greenplumpython.html

Execute the pip install command from the Greenplum client installation directory.

```bash
cd C:\Program Files\Greenplum\greenplum-clients
python3.9 -m pip install greenplum_python-<version>-py3-none-any.whl
```

--------------------------------

### Install Sample PL/Java Functions

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/analytics-pl_java.html

Executes the provided examples.sql script to create test functions in the database.

```bash
$ psql -f $GPHOME/share/postgresql/pljava/examples.sql
```

--------------------------------

### Initialize test environment

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/ref_guide-modules-diskquota.html

Create a test database and enable the diskquota extension.

```bash
$ createdb testdb
$ psql -d testdb
```

```sql
CREATE EXTENSION diskquota;
```

--------------------------------

### Install Greenplum Database Dependencies

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/install_guide-install_gpdb.html

Manually install required system dependencies on each host before proceeding with the Greenplum Database installation.

```bash
$ sudo yum install apr apr-util bash bzip2 curl krb5 libcurl libevent \
libxml2 libyaml zlib openldap openssh openssl openssl-libs perl readline rsync R sed tar zip
```

--------------------------------

### Create a User Mapping Example

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/ref_guide-sql_commands-CREATE_USER_MAPPING.html

An example of mapping the user 'bob' to the foreign server 'foo' with specific credentials.

```sql
CREATE USER MAPPING FOR bob SERVER foo OPTIONS (user 'bob', password 'secret');
```

--------------------------------

### Extracting files with a start timestamp

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/utility_guide-ref-gpmemreport.html

Filter the generated reports by specifying a start time using the --start flag.

```bash
$ gpmemreport mdw.ps.out.gz --start='2021-11-19 15:38:00'
>>>21:11:19:15:37:18<<<

>>>21:11:19:15:38:18<<<

>>>21:11:19:15:39:18<<<
```

```bash
$ ls -thrl
-rw-rw-r--. 1 gpadmin gpadmin 1.2K Nov 19 15:50 20211119-153818
-rw-rw-r--. 1 gpadmin gpadmin 1.2K Nov 19 15:50 20211119-153918
```

--------------------------------

### Install the extension

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/ref_guide-modules-adv_passwd_check.html

Create the extension within the target database.

```sql
CREATE EXTENSION advanced_password_check;
```

--------------------------------

### gpservice start

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/utility_guide-ref-gpservice.html

Starts the hub service.

```APIDOC
## gpservice start

### Description
Starts the hub service.

### Parameters
- **--verbose** (flag) - Optional - Provide verbose output
```

--------------------------------

### Create Initialization Host File

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/install_guide-init_gpdb.html

Example content for the hostfile_gpinitsystem file, listing segment host interface addresses.

```text
sdw1-1
sdw1-2
sdw2-1
sdw2-2
sdw3-1
sdw3-2
sdw4-1
sdw4-2
```

--------------------------------

### START TRANSACTION

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/ref_guide-GPReferenceSQLSummaryLOP.html

Starts a transaction block.

```APIDOC
## START TRANSACTION

### Description
Starts a transaction block.

### Syntax
START TRANSACTION [<transaction_mode>] [READ WRITE | READ ONLY]
```

--------------------------------

### Install R packages on segment hosts

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/analytics-pl_r.html

Execute the R CMD INSTALL command via the R home directory to install the packages on the segment hosts.

```bash
$R_HOME/bin/R CMD INSTALL Matrix_0.9996875-1.tar.gz   arm_1.5-03.tar.gz
```

--------------------------------

### View all configuration parameters using psql

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/admin_guide-topics-g-viewing-server-configuration-parameter-settings.html

Use the SHOW ALL command via psql to display current settings for the master instance.

```bash
$ psql -c 'SHOW ALL;'
```

--------------------------------

### View PL/Container Configuration Details

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/analytics-pl_container_using.html

Example output displaying configuration details for containers, including image paths and memory settings.

```text
INFO:  plcontainer: Container 'plc_py_test' configuration
 INFO:  plcontainer:     image = 'pivotaldata/plcontainer_python_shared:devel'
 INFO:  plcontainer:     memory_mb = '1024'
 INFO:  plcontainer:     use container network = 'no'
 INFO:  plcontainer:     use container logging  = 'no'
 INFO:  plcontainer:     shared directory from host '/usr/local/greenplum-db/./bin/plcontainer_clients' to container '/clientdir'
 INFO:  plcontainer:     access = readonly
                
 ...
                
 INFO:  plcontainer: Container 'plc_r_example' configuration  (seg0 slice3 192.168.180.45:40000 pid=3304)
 INFO:  plcontainer:     image = 'pivotaldata/plcontainer_r_without_clients:0.2'  (seg0 slice3 192.168.180.45:40000 pid=3304)
 INFO:  plcontainer:     memory_mb = '1024'  (seg0 slice3 192.168.180.45:40000 pid=3304)
 INFO:  plcontainer:     use container network = 'no'  (seg0 slice3 192.168.180.45:40000 pid=3304)
 INFO:  plcontainer:     use container logging  = 'yes'  (seg0 slice3 192.168.180.45:40000 pid=3304)
 INFO:  plcontainer:     shared directory from host '/usr/local/greenplum-db/bin/plcontainer_clients' to container '/clientdir'  (seg0 slice3 192.168.180.45:40000 pid=3304)
 INFO:  plcontainer:         access = readonly  (seg0 slice3 192.168.180.45:40000 pid=3304)
 gp_segment_id | plcontainer_show_local_config
 ---------------+-------------------------------
  0 | ok
 -1 | ok
  1 | ok
```

--------------------------------

### Install R Packages

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/analytics-pl_r.html

Installs new R packages into the environment.

```R
> install.packages("<package_name>")
> install.packages("mypkg", dependencies = TRUE, type="source")
```

--------------------------------

### List Installed Packages Matrix

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/analytics-pl_r.html

Returns a matrix of all installed packages.

```R
> installed.packages()
```

--------------------------------

### Create and populate a sales table

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/analytics-pl_python.html

Sets up a sample table and inserts data for use in subsequent examples.

```sql
CREATE TABLE sales (id int, year int, qtr int, day int, region text)
  DISTRIBUTED BY (id) ;

INSERT INTO sales VALUES
 (1, 2014, 1,1, 'usa'),
 (2, 2002, 2,2, 'europe'),
 (3, 2014, 3,3, 'asia'),
 (4, 2014, 4,4, 'usa'),
 (5, 2014, 1,5, 'europe'),
 (6, 2014, 2,6, 'asia'),
 (7, 2002, 3,7, 'usa') ;
```

--------------------------------

### SHA-256 Implementation Example

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/security-guide-topics-Authorization.html

Complete workflow for verifying settings, creating a role, updating pg_hba.conf, and verifying hash storage.

```sql
SHOW password_hash_algorithm 
 password_hash_algorithm 
 ------------------------------- 
 SHA-256
```

```sql
CREATE ROLE testdb WITH PASSWORD 'testdb12345#' LOGIN; 
```

```text
host all testdb 0.0.0.0/0 password
```

```bash
psql -U testdb
```

```sql
# SELECT rolpassword FROM pg_authid WHERE rolname = 'testdb';
Rolpassword
-----------
sha256<64 hexadecimal characters>
```

--------------------------------

### Install Docker Engine

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/analytics-pl_container.html

Install the Docker Community Edition package.

```bash
sudo yum -y install docker-ce
```

--------------------------------

### Initialize using an existing configuration file

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/utility_guide-ref-gpinitsystem.html

Command to initialize the database system using a pre-defined configuration file.

```bash
$ gpinitsystem -I cluster_init.config
```

--------------------------------

### Download PL/Container Installer

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/analytics-pl_container_customize.html

Reference for the PL/Container installer package name.

```text
plcontainer-2.4.0-gp6-rhel7_x86_64.gppkg
```

--------------------------------

### Start Kerberos daemons

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/admin_guide-kerberos.html

Start the KDC and admin server services.

```bash
# /sbin/service krb5kdc start#
/sbin/service kadmin start
```

--------------------------------

### SHOW geqo Example

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/ref_guide-sql_commands-SHOW.html

Displays the current setting for the geqo parameter.

```sql
SHOW geqo;
 geqo
------
 off
(1 row)
```

--------------------------------

### Update Installation Permissions

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/install_guide-migrate-oss-to-vmware.html

Change the ownership of the installed files to the gpadmin user.

```bash
$ sudo chown -R gpadmin:gpadmin /usr/local/greenplum*
```

--------------------------------

### Run ODBC Example Utility

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/datadirect-datadirect_ODBC_71.html

Execute the sample application to test the DSN connection with database credentials.

```bash
$ cd /opt/Progress/DataDirect/Connect64_for_ODBC_71/samples/example
$ ./example
```

--------------------------------

### Example mirror configuration file

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/admin_guide-highavail-topics-g-enabling-segment-mirroring.html

A sample configuration file content for two segment hosts with two segment instances per host.

```text
0=2|sdw1-1|41000|/data/mirror1/gp2
1=3|sdw1-2|41001|/data/mirror2/gp3
2=0|sdw2-1|41000|/data/mirror1/gp0
3=1|sdw2-2|41001|/data/mirror2/gp1
```

--------------------------------

### Navigate to Installation Directory

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/datadirect-datadirect_ODBC_71.html

Change the working directory to the location where the driver was installed.

```bash
$ cd /opt/Progress/DataDirect/Connect64_for_ODBC_71/
```

--------------------------------

### Install PL/Container Package

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/analytics-pl_container.html

Install the PL/Container extension using the gppkg utility.

```bash
gppkg -i plcontainer-2.1.1-rhel7-x86_64.gppkg
```

--------------------------------

### Create a Partitioned Table

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/admin_guide-ddl-ddl-partition.html

Initial setup of a range-partitioned table for sales data.

```sql
CREATE TABLE sales (id int, year int, qtr int, day int, region text)
  DISTRIBUTED BY (id) 
  PARTITION BY RANGE (year) 
  ( PARTITION yr START (2010) END (2014) EVERY (1) ) ;
```

--------------------------------

### List Installed R Packages

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/analytics-pl_r.html

Displays all currently installed R libraries.

```R
> library()
```

--------------------------------

### Execute SQL Query via Example Utility

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/datadirect-datadirect_ODBC_71.html

Run a SQL statement within the example utility to verify database connectivity.

```bash
SQL> select version();
```

--------------------------------

### Install Rdpack documentation

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/analytics-greenplum_r_client.html

Install the Rdpack package to support documentation features.

```R
> install.packages("Rdpack")
```

--------------------------------

### Table Configuration File Examples

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/utility_guide-ref-gpreload.html

Examples of the table and column sorting configuration format, where column order impacts compression efficiency.

```text
public.cust_table: state, city
public.cust_table: city, state
```

--------------------------------

### Install the GreenplumR package

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/analytics-greenplum_r_client.html

Install the GreenplumR package from the local tar.gz file.

```R
> install.packages("/path/to/greenplumR‑<version>‑gp6.tar.gz", repos = NULL, type = "source")
```

--------------------------------

### Start Greenplum Database

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/utility_guide-ref-gpstart.html

Standard command to start the Greenplum Database system.

```bash
gpstart
```

--------------------------------

### Create and populate test table

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/ref_guide-extensions-gppc.html

Sets up a sample table with data for testing the function.

```sql
CREATE TABLE gppc_testtbl( id int, msg text );
INSERT INTO gppc_testtbl VALUES (1, 'f1');
INSERT INTO gppc_testtbl VALUES (7, 'f7');
INSERT INTO gppc_testtbl VALUES (10, 'f10');
INSERT INTO gppc_testtbl VALUES (13, 'f13');
INSERT INTO gppc_testtbl VALUES (15, 'f15');
INSERT INTO gppc_testtbl VALUES (17, 'f17');
```

--------------------------------

### Example existing data directories

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/admin_guide-expand-expand-initialize.html

Reference paths for existing primary or mirror data directories.

```text
/gpdata/primary/gp0
/gpdata/primary/gp1
```

```text
/gpdata/mirror/gp0
/gpdata/mirror/gp1
```

--------------------------------

### greenplumpython.builtins.functions.generate_series(start, stop, step=None)

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/ref_guide-modules-greenplumpython.html

Generates a series of values from start to stop.

```APIDOC
### greenplumpython.builtins.functions.generate_series(start, stop, step=None)

#### Parameters
- **start** (Any) - Required - The starting value.
- **stop** (Any) - Required - The ending value.
- **step** (Any | None) - Optional - The step size (defaults to 1).

#### Returns
- **FunctionExpr** - An expression representing the generated series.
```

--------------------------------

### Create a sample table for dblink queries

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/ref_guide-modules-dblink.html

Set up a test table in the target database to be queried later via dblink.

```sql
$ psql -d postgres
psql (9.4.20)
Type "help" for help.

postgres=# CREATE TABLE testdblink (a int, b text) DISTRIBUTED BY (a);
CREATE TABLE
postgres=# INSERT INTO testdblink VALUES (1, 'Cheese'), (2, 'Fish');
INSERT 0 2
postgres=# \q
$
```

--------------------------------

### Hostfile Example

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/utility_guide-ref-gpservice.html

Example content for a hostfile used with the --hostfile flag.

```text
cdw
sdw1
sdw2
```

--------------------------------

### Drop a Database Example

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/ref_guide-sql_commands-DROP_DATABASE.html

Example demonstrating how to remove a database named testdb.

```sql
DROP DATABASE testdb;
```
Tokens:
Raw