Administration Guide

  [gpadmin@mdw ~]$ cat .pgpass
  *:5432:gpperfmon:gpmon:xxxxxxxx

  Option 1:
  [gpadmin@mdw ~]$ /bin/bash -c 'source /usr/local/greenplum-cc/gpcc_path.sh \
   && /usr/local/greenplum-cc/bin/gpcc start'
  Option 2:
  [gpadmin@mdw ~]$ source /usr/local/greenplum-cc/gpcc_path.sh
  [gpadmin@mdw ~]$ gpcc start
  Starting the gpcc agents and webserver...
  Agent successfully started on 3/3 hosts
  View Greenplum Command Center at http://mdw:28080

   ###### PL/Python #####
   psql -d ecom -U gpadmin -c 'CREATE EXTENSION plpythonu;'
   ###### create pymax as gpadmin#####
   CREATE FUNCTION pymax (a integer, b integer)
   RETURNS integer
   AS $$
   if (a is None) or (b is None):
   return None
   if a > b:
   return a
   return b
   $$ LANGUAGE plpythonu;
   grant usage on plpythonu to hsio;   

---

   ###### from sql editor: as hsio
   SELECT ( pymax(123, 43));
   pymax|
   -------+
   123     |

   ###### customer types from cust_type table #####
   -- tid|cust_type_id|cust_type_name
   -- ---+------------+---------------
   -- 1 |   1   | external
   -- 2 |   2   | internal
   -- 3 |   3   | prospect
   -- 4 |   4   | OEM
   -- 5 |   5   | software vendor

   ###### add customer record function#####
   CREATE OR REPLACE FUNCTION public.add_customer(name text, custTypeId integer)
   RETURNS integer
   LANGUAGE plpgsql
   AS $function$
   DECLARE
    customer_row customer%ROWTYPE;
   BEGIN
   insert into customer (cust_name,cust_type_id) values(name,custTypeId);
   RETURN 0 ;
   END;
   $function$
  Example: add customer: add customer Casper with type_id of 2(internal)
  select add_customer('Casper',2)
  ======================================
   ###### delete customer record function#####
  CREATE OR REPLACE FUNCTION public.del_customer(customerId integer)
   RETURNS integer
   LANGUAGE plpgsql
   AS $function$
   declare
   custid integer;
   customer_row customer%ROWTYPE;
   BEGIN
   update customer set customer_status =0 where cust_id =customerId;
   RETURN 0 ;
   END;
  $function$
  Example: delete customer: with cust_id of 2222(internal)
  select del_customer(2222)
  ======================================
  ###### add customer attribute (cust_attr) record function #####
  CREATE OR REPLACE FUNCTION public.add_cust_attr(cust_id_var integer,attrid integer,
  attribute_value_var text)
  RETURNS integer
  LANGUAGE plpgsql
  AS $function$
  DECLARE
   cust_attr_row cust_attr%ROWTYPE;
  BEGIN
   insert into cust_attr(cust_id, attr_id, attribute_value) values(cust_id_var, attrid,
   attribute_value_var);
   RETURN 0 ;
  END;
  $function$
  Example: add customer attribute
  cust_id_var integer, attrid integer, attribute_value_var
  1=Hyperscale IO 3=office phone 408-781-6849
  SELECT add_cust_attr(1,3,'408-781-6849')
  ======================================
  ###### add orders record function #####
  CREATE OR REPLACE FUNCTION public.add_order(custId integer, orderDesc text)
  RETURNS integer
  LANGUAGE plpgsql
  AS $function$
  DECLARE
   orders_row orders%ROWTYPE;
  BEGIN
    insert into orders (cust_id,order_desc) values(custId,orderDesc);
    RETURN 0 ;
  END;
  $function$
  Example: add order
  custId integer, orderDesc text
  SELECT add_order(2,'Casper''s first order')
  ======================================
  ###### add order_detail record function #####
  CREATE OR REPLACE FUNCTION public.add_order_detail(orderId integer, prodId integer,
  orderDetailDesc text)
  RETURNS integer
  LANGUAGE plpgsql
  AS $function$
  DECLARE
   order_detail order_detail%ROWTYPE;
  BEGIN
   insert into order_detail (order_id,prod_id,order_detail_desc) values
   (orderId,prodId,orderDetailDesc);
   RETURN 0 ;
  END;
  $function$
  Example: insert order_detail:
   order_id=1, prod_id=101, order_detail_desc
   select add_order_detail(1,101,'testing testing testing...')
  ======================================
  ###### remove order_detail record function #####
  CREATE OR REPLACE FUNCTION public.rm_order_detail(orderDetailId integer)
  RETURNS integer
  LANGUAGE plpgsql
  AS $function$
  DECLARE
   order_detail order_detail%ROWTYPE;
  BEGIN
   delete from order_detail where order_detail_id =orderDetailId;
   RETURN 0 ;
  END;
  $function$
  Example: insert order_detail:
   order_id=1, prod_id=101, order_detail_desc
   select add_order_detail(1,101,'testing testing testing...')
  ======================================
  ######add product function#######
  CREATE OR REPLACE FUNCTION public.add_product(prodName text,
  prodDesc text,prodNote text,prodImage text,
  prodInStock integer,prodCategory integer, prodPrice NUMERIC(10,2))
  RETURNS integer
  LANGUAGE plpgsql
  AS $function$
  DECLARE
  product_row products%ROWTYPE;
  BEGIN
   insert into products (prod_name,prod_description,prod_note,
   prod_image,prod_in_stock,prod_categoryid,prod_price) values
   (prodName, prodDesc,prodNote,prodImage,prodInStock,prodCategory, prodPrice);
   RETURN 0 ;
  END;
  $function$
  Example: add_product:
  select add_product('Casper bed','Casper''s sleeping headquarter','casper note',
  'casper.png',1,1,100.00)
  ======================================
  ###### add product attribute function #####
  CREATE OR REPLACE FUNCTION public.add_product_attr(attrid integer, prodId integer,
  attrName text, attrValue text) RETURNS integer
  LANGUAGE plpgsql
  AS $function$
  DECLARE
  prod_attr prod_attr%ROWTYPE;
  BEGIN
   insert into prod_attr(attr_id,prod_id,attr_name,attr_value)
   values(attrid,prodId,attrName,attrValue);
   RETURN 0 ;
  END;
  $function$
  Example: add_product_attr(attr_id,prod_id,attr_name,attr_value)
  select add_product_attr(8,1,'retail price','100.00')
  ======================================
  ###### add service function #####
  CREATE OR REPLACE FUNCTION public.add_service(orderId integer, prodId integer,
  serviceDesc text, serviceTech text, serviceStatus integer
  RETURNS intege
  LANGUAGE plpgsq
  AS $function
  DECLAR
   service_row service%ROWTYPE
  BEGI
   insert into service (order_id,prod_id,service_desc,service_tech,status)
   values(orderId, prodId,serviceDesc,serviceTech,serviceStatus)
   RETURN 0
  END
  $function
  Example: add_service(orderId,prodId,serviceDesc,serviceTech,serviceStatus)
  select add_service(1,1,'make bed more comfortable','Brady Chang',1)
  ======================================

   See section webtemplate
   [root@hdm01 templates]# pip3 list
   Package Version    -------------------- -------
   configparser 5.0.2
   greenlet 1.1.1
   pip 21.2.4
   psycopg2-binary 2.9.1
   setuptools 57.4.0
   SQLAlchemy 1.4.34
   sqlalchemy-greenplum 0.1.0
   Flask-SQLAlchemy 2.5.1
   Flask-Babel 2.5.1
   Flask-migrate 2.5.1
   Flask 2.5.1
   alembic 1.7.3
   Babel 2.9.1
   wheel 0.37.0

  import sqlalchemy as db
  from sqlalchemy import create_engine
  from sqlalchemy.orm import sessionmaker
  def main():
    engine = db.create_engine('greenplum://hsio:xxxxxxxx@192.168.88.101/ecom')
    con = engine.connect()
    metadata=db.MetaData()
    users = db.Table('users',metadata, autoload=True,autoload_with=engine)
    query = db.select([users])
    rsProxy= con.execute(query)
    rs=list(rsProxy.fetchall())
    for row in rs:
     print(row)
     rsProxy.close()
  if __name__ == '__main__':
    main()


  Add gpadmin to /data/master/gpseg-1/pg_hba.conf
  # TYPE       DATABASE       USER           ADDRESS                     METHOD
  host             all               gpadmin         192.168.88.102/32         trust
  Download Dbeaver from https://dbeaver.io/files/dbeaver-ce-latest-x86_64-setup.exe

CREATE SEQUENCE cust_id_serial
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1;

CREATE SEQUENCE cust_type_id_serial
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1;

CREATE SEQUENCE public.attr_id_serial
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1;

CREATE SEQUENCE activity_id_serial
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1;

CREATE TABLE public."attribute" (
attr_id int4 NOT NULL DEFAULT nextval('attr_id_serial'::regclass),
attr_value varchar(30) NULL,
CONSTRAINT attribute_pkey PRIMARY KEY (attr_id)
);
-- public.cust_type definition
-- Drop table
DROP TABLE IF exists public.cust_type;
CREATE TABLE cust_type
(
tid int PRIMARY KEY DEFAULT nextval('cust_type_id_serial'),
cust_type_id int, -- cust_type_id 1 = internal 2 = EXTERNAL 3 = prospect/leads
cust_type_name varchar(15),
cust_type_date timestamp DEFAULT now()
)
-----------------------------------
-- customer
-----------------------------------
drop table if exists customer;
CREATE TABLE public.customer (
cust_id int4 NOT NULL DEFAULT nextval('cust_id_serial'::regclass),
cust_name varchar(30) NULL,
tid int, --FOREIGN KEY TO cust_type.tid
CONSTRAINT customer_pkey PRIMARY KEY (cust_id)
);
ALTER TABLE customer ADD CONSTRAINT cust_tid_fkey FOREIGN KEY (tid)
REFERENCES public."cust_type"(tid);

drop sequence if exists cust_attr_id_serial;
create sequence cust_attr_id_serial;
drop table if exists cust_attr CASCADE;
-----------------------------------
-- cust_attr
-----------------------------------
CREATE TABLE public.cust_attr(
attr_id int4 NOT NULL DEFAULT nextval('cust_attr_id_serial'::regclass),
cust_id int4 NULL,
attr_value varchar(30) NULL,
CONSTRAINT cust_attr_pkey PRIMARY KEY (attr_id)
);
-- public.cust_attr foreign keys
ALTER TABLE public.cust_attr ADD CONSTRAINT cust_attr_attr_id_fkey FOREIGN KEY (attr_id)
REFERENCES public."attribute"(attr_id);
ALTER TABLE public.cust_attr ADD CONSTRAINT cust_attr_cust_id_fkey FOREIGN KEY (cust_id)
REFERENCES public.customer(cust_id);
-----------------------------------
-- conact
-----------------------------------
DROP TABLE IF EXISTS public.contact;
DROP SEQUENCE IF EXISTS public.contact_id_serial;
CREATE SEQUENCE public.contact_id_serial
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1;
CREATE TABLE public.contact(
contact_id int4 NOT NULL DEFAULT nextval('contact_id_serial'::regclass),
cust_id int4 NULL,
contact_name varchar(30) NULL,
CONSTRAINT contact_pkey PRIMARY KEY (contact_id)
);
-----------------------------------
-- conatct_attr
-----------------------------------
drop table if exists contact_attr;
drop sequence if exists contact_attr_id_serial;
create sequence contact_attr_id_serial;
create table contact_attr(
contact_attr_id int primary key default nextval('contact_attr_id_serial'),
contact_id int4 not null,
attr_id int references "attribute" (attr_id),
attr_value varchar(30)
);
ALTER TABLE public.contact_attr ADD CONSTRAINT contact_attr_fkey
FOREIGN KEY (contact_id) REFERENCES public.contact(contact_id);
-------------------
-- category tables
-------------------
DROP table if exists public.category ;
CREATE TABLE public.category (
categoryid int4 NOT NULL,
"name" varchar(20) NULL,
CONSTRAINT catid_pkey PRIMARY KEY (categoryid)
);
-------------------
-- product tables
-------------------
drop TABLE IF EXISTS products cascade;
drop sequence if exists prod_id_serial;
create sequence prod_id_serial;
CREATE TABLE public.products(
prod_id int4 NOT NULL DEFAULT nextval('prod_id_serial'::regclass),
prod_name varchar(30) NULL,
prod_description varchar(100) NULL,
prod_note varchar(100) NULL,
prod_image varchar(50) NULL,
prod_in_stock int4 NULL,
prod_categoryid int4 NULL,
prod_price numeric(10, 2) NULL,
CONSTRAINT product_pkey PRIMARY KEY (prod_id)
);
-- public.products foreign keys
ALTER TABLE public.products ADD CONSTRAINT categorid_fkey
FOREIGN KEY (prod_categoryid) REFERENCES public.category(categoryid);
---------------------------------
-- prod_attr (product attribute)
---------------------------------
drop table if exists public.prod_attr;
drop sequence if exists prod_attr_id_serial;
create sequence prod_attr_id_serial;
CREATE TABLE public.prod_attr (
prod_attr_id int4 primary key DEFAULT nextval('prod_attr_id_serial'),
attr_id int4 ,
prod_id int4 references products(prod_id),
attr_name varchar(30) NULL,
attr_value varchar(50) NULL
);
------------------
-- orders table
------------------
CREATE SEQUENCE public.order_id_serial
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1;
-- public.orders definition
-- Drop table
-- DROP TABLE public.orders;
CREATE TABLE public.orders (
order_id int4 NOT NULL DEFAULT nextval('order_id_serial'::regclass),
cust_id int4 NULL,
order_status int4 NOT NULL DEFAULT 1,
order_date date NULL DEFAULT now(),
order_desc varchar(500) NULL,
CONSTRAINT orders_pkey PRIMARY KEY (order_id)
);
-- order_status 1=open, 2=assigned, 3=pending, 4=closed 5=deleted
-- public.orders foreign keys
ALTER TABLE public.orders ADD CONSTRAINT orders_cust_id_fkey FOREIGN KEY (cust_id)
REFERENCES public.customer(cust_id);
CREATE SEQUENCE public.order_detail_id_serial
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1;
-- public.order_detail definition
-- Drop table
-- DROP TABLE public.order_detail;
----------------------
-- order_detail table
----------------------
CREATE TABLE public.order_detail(
order_detail_id int4 NOT NULL DEFAULT nextval('order_detail_id_serial'::regclass),
order_id int4 NULL,
prod_id int4 NULL,
order_detail_date timestamp NULL DEFAULT now(),
order_detail_desc varchar(50) NULL
);
-- public.order_detail foreign keys
ALTER TABLE public.order_detail ADD CONSTRAINT order_detail_order_id_fkey
FOREIGN KEY (order_id) REFERENCES public.orders(order_id);
ALTER TABLE public.order_detail ADD CONSTRAINT order_detail_prod_id_fkey
FOREIGN KEY (prod_id) REFERENCES public.products(prod_id);
-------------------
-- activity table
-------------------
-- public.activity definition
-- Drop table
-- DROP TABLE public.activity;
CREATE TABLE public.activity (
act_id int4 NOT NULL DEFAULT nextval('activity_id_serial'::regclass),
order_id int4 NULL,
contact_id int4 NULL,
act_type varchar(10) NULL,
act_date timestamp NULL DEFAULT now(),
act_desc varchar(1000) NULL,
CONSTRAINT activity_act_type_check CHECK (((act_type)::text = ANY
((ARRAY['pc'::character varying, 'im'::character varying, 'em'::character varying,
'pm'::character varying])::text[]))),
CONSTRAINT activity_pkey PRIMARY KEY (act_id)
);
--pc = phonecall im=messaging em=email pm(physical meeting)
-- public.activity foreign keys
ALTER TABLE public.activity ADD CONSTRAINT activity_contact_id_fkey
FOREIGN KEY (contact_id) REFERENCES public.contact(contact_id);
ALTER TABLE public.activity ADD CONSTRAINT activity_order_id_fkey
FOREIGN KEY (order_id) REFERENCES public.orders(order_id);
------------------
-- service table
------------------
drop sequence if exists service_id_serial;
create sequence service_id_serial;
alter sequence service_id_serial restart with 101;
drop table if exists service;
CREATE TABLE service(
service_id int4 primary key default nextval('service_id_serial'),
order_id int4 references orders(order_id),
prod_id int4 references products(prod_id),
service_desc varchar(500) null,
service_tech varchar(30),
status int DEFAULT 1
);
--service.status: 1 open 2 close 3 pending
----------------------------------
-- service_attr(service attribute)
----------------------------------
drop table if exists service_attr;
drop sequence if exists service_attr_id_serial;
create sequence service_attr_id_serial;
alter sequence service_attr_id_serial restart with 101;
-- public.service_attr definition
-- Drop table
DROP TABLE public.service_attr;
CREATE TABLE public.service_attr (
service_attr_id int4 NOT NULL DEFAULT nextval('service_attr_id_serial'::regclass),
service_id int4 NULL,
cust_attr_id int4 NULL,
order_id varchar(15) NULL,
service_type varchar(4) NULL,
converse_date timestamptz NULL DEFAULT now(),
converse_tech varchar(30) NULL,
conversation varchar(500) NULL,
CONSTRAINT service_attr_pkey PRIMARY KEY (service_attr_id),
CONSTRAINT service_attr_service_type_check CHECK (((service_type)::text = ANY
((ARRAY['sfwr'::character varying, 'hdwr'::character varying])::text[]))));
);
-- public.service_attr foreign keys
ALTER TABLE public.service_attr ADD CONSTRAINT service_attr_cust_attr_id_fkey
FOREIGN KEY (cust_attr_id) REFERENCES public.cust_attr(cust_attr_id);
ALTER TABLE public.service_attr ADD CONSTRAINT service_attr_service_id_fkey
FOREIGN KEY (service_id) REFERENCES public.service(service_id);

CREATE SEQUENCE public.user_id_serial
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1;

CREATE TABLE public.users (
userid int4 NOT NULL DEFAULT nextval('user_id_serial'::regclass),
pswd varchar(20) NULL,
email varchar(40) NULL,
firstname varchar(40) NULL,
lastname varchar(40) NULL,
address1 varchar(40) NULL,
address2 varchar(40) NULL,
zipcode varchar(10) NULL,
city varchar(40) NULL,
state varchar(40) NULL,
country varchar(40) NULL,
phone varchar(16) NULL,
CONSTRAINT users_pkey PRIMARY KEY (userid)
);
CREATE OR REPLACE FUNCTION public.add_customer(name text, custTypeId integer)
RETURNS integer
LANGUAGE plpgsql
AS $function$
DECLARE
 customer_row customer%ROWTYPE;
BEGIN
 insert into customer (cust_name,tid) values(name,custTypeId);
RETURN 0 ;
END;
$function$
-- customer delete function
CREATE OR REPLACE FUNCTION public.del_customer(name text)
RETURNS integer
LANGUAGE plpgsql
AS $function$
declare
 custid integer;
  customer_row customer%ROWTYPE;
BEGIN
 DELETE FROM customer WHERE cust_name = name;
 RETURN 0 ;
END;
$function$
-- contact insert function
CREATE OR REPLACE FUNCTION public.add_contact(
custid_var integer, contactname text)
RETURNS integer
LANGUAGE plpgsql
AS $function$
DECLARE
 contact_row contact%ROWTYPE;
BEGIN
 insert into contact (cust_id, contact_name) values(custid_var,contactname);
RETURN 0 ;
END;
$function$
-- contact delete function
CREATE OR REPLACE FUNCTION public.del_contact(contactid integer)
RETURNS integer
LANGUAGE plpgsql
AS $function$
DECLARE
 contact_row contact%ROWTYPE;
BEGIN
 DELETE FROM contact WHERE contact_id = contactid;
 RETURN 0 ;
END;
$function$
-- contact_attr insert function
CREATE OR REPLACE FUNCTION public.add_contact_attr(
contact_id_var integer, attr_id_var integer, attr_value_var text)
RETURNS integer
LANGUAGE plpgsql
AS $function$
DECLARE
 contact_attr_row contact_attr%ROWTYPE;
BEGIN  insert into contact_attr(contact_id, attr_id, attr_value) values
 (contact_id_var, attr_id_var, attr_value_var);
 RETURN 0 ;
END;
$function$
CREATE OR REPLACE FUNCTION public.add_order(custId integer, orderDesc text)
RETURNS integer
LANGUAGE plpgsql
AS $function$
DECLARE
 contact_row orders%ROWTYPE;
BEGIN
 insert into orders (cust_id,order_desc) values(custId,orderDesc);
 RETURN 0 ;
END;
$function$
CREATE OR REPLACE FUNCTION public.add_order_detail(
orderId integer, prodId integer, orderDetailDesc text)
RETURNS integer
LANGUAGE plpgsql
AS $function$
DECLARE
 order_detail order_detail%ROWTYPE;
BEGIN
 insert into order_detail (order_id,prod_id,order_detail_desc)
 values(orderId,prodId,orderDetailDesc);
 RETURN 0 ;
END;
$function$
CREATE OR REPLACE FUNCTION public.add_product(
prodName text, prodDesc text,prodNote text,prodImage text,
prodInStock integer,prodCategory integer, prodPrice NUMERIC(10,2))
RETURNS integer
LANGUAGE plpgsql
AS $function$
DECLARE
 product_row products%ROWTYPE;
BEGIN
 insert into products (prod_name,prod_description,prod_note,prod_image,
 prod_in_stock,prod_categoryid, prod_price) values(
 prodName, prodDesc,prodNote,prodImage,prodInStock,prodCategory, prodPrice);
 RETURN 0 ;
END;
$function$
CREATE OR REPLACE FUNCTION public.add_product_attr(
attrid integer, prodId integer, attrName text, attrValue text)
RETURNS integer
LANGUAGE plpgsql
AS $function$
DECLARE
 prod_attr prod_attr%ROWTYPE;
BEGIN
 insert into prod_attr(attr_id,prod_id,attr_name,attr_value)
 values(attrid,prodId,attrName,attrValue);
 RETURN 0 ;
END;
$function$

CREATE OR REPLACE FUNCTION public.add_service(
orderId integer, prodId integer, serviceDesc text, serviceTech text,
serviceStatus integer)
RETURNS integer
LANGUAGE plpgsql
AS $function$
DECLARE
 service_row service%ROWTYPE;
BEGIN
 insert into service (order_id,prod_id,service_desc,service_tech,status)
 values(orderId, prodId,serviceDesc,serviceTech,serviceStatus);
 RETURN 0 ;
END;
$function$

--------------------
--function usage-
--------------------
--customer table
select add_customer('casperbbq');
select del_customer('casperbbq');
--cust_attr table
--cust_id_var integer, attrid integer, attribute_value_var
-- 1=Hyperscale IO 3=office phone 408-781-6849
SELECT add_cust_attr(1,3,'408-781-6849')
--contact table
select add_contact(2,'Casper Chang') -- cust_id=2
select del_contact(7) -- contact_id = 7
--order table
SELECT add_order(2,'Casper''s first order')
--order_detail table
select add_order_detail(1,101,'testing testing testing...')
--end function usage

Install MADlib

Download MADlib RPM
Copy apache-madlib-1.20.0-Centos7.rpm to all nodes
[root@mdw ~]# forcp /tmp/apache-madlib-1.20.0-CentOS7.rpm /tmp
install madlib rpm to all nodes
# master node
[root@mdw ~]# fornodes -m ' yum install -y /tmp/apache-madlib-1.20.0-CentOS7.rpm'
# worker nodes
[root@mdw ~]# fornodes -n ' yum install -y /tmp/apache-madlib-1.20.0-CentOS7.rpm'
# install madpack to ecom database
[root@mdw ~]# /usr/local/madlib/Versions/1.20.0/bin/madpack -p greenplum -c gpadmin@mdw:5432/ecom install

Naive Bayes Classification

Naive Bayes analysis predicts the likelihood of an outcome of a class variable, or category,
based on one or more independent variables, or attributes. The class variable is a non-numeric
categorial variable, a variable that can have one of a limited number of values or categories.
The class variable is represented with integers, each integer representing a category.
For example, if the category can be one of “true”, “false”, or “unknown,” the values can be
represented with the integers 1, 2, or 3.

The attributes can be of numeric types and non-numeric, categorical, types. The training function
has two signatures – one for the case where all attributes are numeric and another for mixed numeric
and categorical types. Additional arguments for the latter identify the attributes that should be
handled as numeric values. The attributes are submitted to the training function in an array.

test
[root@mdw ext]# pwd
/opt/greenplum/ext
[root@mdw ext]# chmod 664 /var/log/messages
[gpadmin@mdw ext]# ln -sf /var/log/messages messages
[gpadmin@mdw ~]$ nohup gpfdist -p 8801 -d /opt/greenplum/ext -l /home/gpadmin/gpfdist.log &
Create External Table
# var.log.messages
drop external table if exists var_log_messages;
CREATE READABLE EXTERNAL table var_log_messages
(
msg text
)
location ('gpfdist://mdw:8801/messages')
format 'TEXT' ;

Test select from External Table
[gpadmin@mdw scripts]$ psql -d ecom
hyperscaleio=# select msg from var_log_messages where msg like '%mdw%'

Script for above select is located in /opt/greenplum/scripts
[gpadmin@mdw scripts]$ pwd
/opt/greenplum/scripts
[root@mdw scripts]# cat view_logs
#!/bin/bash
usage()
{
cat << EOF
usage: bash ./ -n host_name
-m master
-w worker
-a all
-h Brings up this menu
EOF
}
   case $1 in
   -m)
     shift
     psql -d ecom -f select_var_log_messages.mdw.sql
   ;;
   -w)
     shift
     psql -d ecom -f select_var_log_messages.hdw.sql
   ;;
   -a)
     shift
     psql -d ecom -f select_var_log_messages.all.sql
   ;;
   *) usage
     exit 1
   esac
[gpadmin@mdw scripts]$ chmod 775 view_logs
[gpadmin@mdw scripts]$ cat select_var_log_messages.sql
select msg from var_log_messages;
Write "test" message to /var/log/message
[root@mdw log]# logger test
[gpadmin@mdw scripts]$ ./view_logs -m
Jul 25 03:32:01 mdw rsyslogd: [origin software="rsyslogd" swVersion="8.24.0-57.el7_9.1"
Jul 25 03:40:01 mdw systemd: Started Session 647 of user root.
...
Jul 25 11:30:01 mdw systemd: Started Session 717 of user root.
Jul 25 11:33:25 hdm01 root: test
(172 rows)

Scripting Tips

Sort Process by Size descending order
ps -elf |sort -k 9 -r |more

Explain

EXPLAIN displays the query plan that the Greenplum or Postgres Planner generates for
the supplied statement. Query plans are a tree plan of nodes. Each node in the plan represents a
single operation, such as table scan, join, aggregation or a sort.
[gpadmin@hdm01 ~]$ psql ecom
psql (9.4.24)
Type "help" for help.
shop=# explain select * from users
INFO: analyzing "public.categories"
INFO: Executing SQL: select pg_catalog.gp_acquire_sample_rows(41456, 10000, 'f');
INFO: ANALYZE estimated relpages=5 for index categories_pkey
INFO: analyzing "public.users"
INFO: Executing SQL: select pg_catalog.gp_acquire_sample_rows(40970, 10000, 'f');
INFO: ANALYZE estimated relpages=5 for index users_pkey
INFO: analyzing "public.products"
INFO: Executing SQL: select pg_catalog.gp_acquire_sample_rows(41477, 10000, 'f');
INFO: ANALYZE skipping index products_pkey since relation products has no rows.
INFO: analyzing "public.orders"
INFO: Executing SQL: select pg_catalog.gp_acquire_sample_rows(41662, 10000, 'f');
INFO: ANALYZE skipping index orders_pkey since relation orders has no rows.

Analyze

analyze collects statistics about the contents of tables in the database,
and stores the results in the system catalog table pg_statistic. Greenplum database uses these
statistics to determine the best execution plan for the queries.
ANALYZE [VERBOSE] [ROOTPARTITION [ALL] ] [table [ (column [, ...] ) ]]
[gpadmin@mdw ~]$ psql ecom
hyperscaleio=# analyze verbose;
INFO: analyzing "pg_catalog.pg_authid"
INFO: "pg_authid": scanned 1 of 1 pages, containing 7 live rows and 0 dead rows; 7 rows in sample, 7 estimated total rows
INFO: ANALYZE estimated relpages=2 for index pg_authid_rolname_index
INFO: ANALYZE estimated relpages=2 for index pg_authid_oid_index
INFO: ANALYZE estimated relpages=2 for index pg_authid_rolresqueue_index
INFO: ANALYZE estimated relpages=2 for index pg_authid_rolresgroup_index
INFO: analyzing "pg_catalog.pg_user_mapping"
...

Vacuumdb

vacuumdb cleans and analyzes a PostgreSQL database.
vacuumdb [OPTION]... [DBNAME]
Options:
-a, --all vacuum all databases
-d, --dbname=DBNAME database to vacuum
-e, --echo show the commands being sent to the server
-f, --full do full vacuuming
[gpadmin@mdw ~]$ vacuumdb ecom

Down Segment

Identify and fix the problem(typically an HW/OS related).
Run gprecoverseg from master to recover the segment node.
---[gpadmin@mdw ~]$ gpstop -r
Perfrom full recovery.
---[gpadmin@mdw ~]$ gprecoverseg -F
Run gprecoverseg with the -r option to return the segments to their preferred roles.
---[gpadmin@mdw ~]$ gprecoverseg -r
After rebalancing, run gpstate -e to confirm all segments are in their preferred roles.
---[gpadmin@mdw ~]$ gpstate -e
  ...
  20210802:12:33:35:003059 gpstate:mdw:gpadmin-[INFO]:-All segments are running normally
Check segment status with one of the following command:
  ---select preferred_role,mode,status,port,hostname,address,datadir from pg_catalog.gp_segment_configuration
 ---[gpadmin@hdm01 ~]$ gpstate -m
Summary
--- gpstate -s = Greenplum Array Configuration details
--- gpstate -m = Mirror Segments in the system and their status
--- gpstate -c = To see the primary to mirror segment mappings
--- gpstate -f = To see the status of the standby master mirror:
--- gpstate -e = show segments with mirror status issues

Reference
https://gpdb.docs.pivotal.io/vxrail/1-0/index.html
https://greenplum-kubernetes.docs.pivotal.io/2-3/index.html
https://greenplum-kubernetes.docs.pivotal.io/2-3/deploy-operator.html

Move Mirror Segment

[gpadmin@mdw ~]$ cat move_c
mdw|50000|/data/mirror/gpseg2 mdw|50000|data2/mirror/gpseg
[gpadmin@mdw ~]$ gpmovemirrors --verbose --input move_c

Dump and Restore Data

#Database create script
[gpadmin@mdw Downloads]$ pg_dump --create shop >shop.sql
#Database data
[gpadmin@mdw Downloads]$ pg_dump -a shop >shop.dat
[gpadmin@mdw Downloads]$ ls -l shop.dat
-rw-rw-r-- 1 gpadmin gpadmin 3549 Mar 18 20:10 shop.dat

Delete Greenplum Instance

[gpadmin@mdw gpconfigs]$ gpdeletesystem