Getting familiar with HAWQs Command line Interface

[#1]

Goals

• Get familiar with psql command line CLI interface for HAWQ
• Understanding how to access and list the options available for command line interface
• Accessing help from within the CLI

NOTES

• Requirement for above is to ensure that there is either a sandbox configured with HAWQ
• A SSH interface to the sandbox either via HOST/remote OSX Or Putty when working with Windows

Connecting to the Guest Sandbox Machine

Depending upon the setup i.e., VirtualBox or VMWare Fusion for HAWQ & HDP, you can choose the connection method. For connecting via VMWare Fusion, you need to be aware of the IP address allocated to your virtual box.

Connecting via VMWare Fusion

HW13382:ODBC srai$ ssh root@172.16.105.137 -p 22
root@172.16.105.137's password: 
Last login: Mon Jun 20 15:31:20 2016 from 172.16.105.1
[root@sandbox ~]#

Connecting via Virtualbox

HW13382:ODBC srai$ ssh root@localhost -p 2222
root@172.16.105.137's password: 
Last login: Mon Jun 20 15:31:29 2016 from 172.16.105.1
[root@sandbox ~]#

NOTE: HAWQ has a preconfigured user account associated with it, similar to hive, known as "gpadmin". This by default is the superuser for the cluster and has all the privileges. For our exercise, we will be using this account.

• Switch from root user account to "gpadmin" user

[root@sandbox ~]# su - gpadmin
[gpadmin@sandbox ~]$

• Ensure that you have the binaries set in the environment, usually this should be taken care by itself during the installation

[gpadmin@sandbox ~]$ env | egrep GPHOME
GPHOME=/usr/local/hawq/.
[gpadmin@sandbox ~]$ which psql
/usr/local/hawq/bin/psql
[gpadmin@sandbox ~]$

• Now that we can locate the "psql" utility, lets list down the options[gpadmin@sandbox ~]$ psql -?

This is psql 8.2.15, the PostgreSQL interactive terminal (Greenplum version).
Usage:
  psql [OPTION]... [DBNAME [USERNAME]]
General options:
  -c, --command=COMMAND    run only single command (SQL or internal) and exit
  -d, --dbname=DBNAME      database name to connect to (default: "gpadmin")
  -f, --file=FILENAME      execute commands from file, then exit
  -l, --list               list available databases, then exit
  -v, --set=, --variable=NAME=VALUE set psql variable NAME to VALUE
  -X, --no-psqlrc          do not read startup file (~/.psqlrc)
  -1 ("one"), --single-transaction execute command file as a single transaction
  --help                   show this help, then exit
  --version                output version information, then exit
Input and output options:
  -a, --echo-all           echo all input from script
  -e, --echo-queries       echo commands sent to server
  -E, --echo-hidden        display queries that internal commands generate
  -L, --log-file=FILENAME  send session log to file
  -n, --no-readline        disable enhanced command line editing (readline)
  -o, --output=FILENAME    send query results to file (or |pipe)
  -q, --quiet              run quietly (no messages, only query output)
  -s, --single-step        single-step mode (confirm each query)
  -S, --single-line        single-line mode (end of line terminates SQL command)
Output format options:
  -A, --no-align           unaligned table output mode
  -F, --field-separator=STRING set field separator (default: "|")
  -H, --html               HTML table output mode
  -P, --pset=VAR[=ARG]     set printing option VAR to ARG (see \pset command)
  -R, --record-separator=STRING
                           set record separator (default: newline)
  -t, --tuples-only        print rows only
  -T, --table-attr=TEXT    set HTML table tag attributes (e.g., width, border)
  -x, --expanded           turn on expanded table output
Connection options:
  -h, --host=HOSTNAME      database server host or socket directory (default: "sandbox.hortonworks.com")
  -p, --port=PORT          database server port (default: "10432")
  -U, --username=USERNAME  database user name (default: "gpadmin")
  -w, --no-password        never prompt for password
  -W, --password           force password prompt (should happen automatically)
For more information, type "\?" (for internal commands) or "\help" (for SQL
commands) from within psql, or consult the psql section in the PostgreSQL
documentation.
Report bugs to <pgsql-bugs@postgresql.org>.

• Some of the key environment variables required to connect are PGPORT (-p), PGHOST(-h), PGDATABASE(-d) & -c. We can ensure that we have all these variables set

[gpadmin@sandbox ~]$ env | egrep 'PGHOST|PGPORT|PGDATABASE'
PGPORT=10432
PGDATABASE=mydemo
PGHOST=sandbox.hortonworks.co<br>

NOTE: Having these variables configured is a matter of convenience, else you would end up typing these variables. Here is how we would need to connect if these variables are not set

[gpadmin@sandbox ~]$ psql -h 172.16.105.137 -p 10432 -d mydemo
psql (8.2.15)
Type "help" for help.
mydemo=#

• We can also execute commands from CLI without going into the psql shell and execute commands from the shell

[gpadmin@sandbox ~]$ psql -h 172.16.105.137 -p 10432 -d mydb -c "select * from mytable limit 5"
 col1 | col2 | col3 
------+------+------
    0 |    0 |    0
    1 |    1 |    1
    2 |    2 |    2
    3 |    3 |    3
    4 |    4 |    4
(5 rows)

NOTE We can familiarize ourselves with the utilities and syntaxes later on since the sandbox is meant to tryout and break stuff. Syntaxes are terminated for SQL statements with a semicolon ";" similar to that in hive.

• Accessing help from within the psql prompt requires you to type "\?" i.e., a backslash followed by a question mark

[gpadmin@sandbox ~]$ psql 
psql (8.2.15)
Type "help" for help.
mydemo=# \?
General
  \copyright             show PostgreSQL usage and distribution terms
  \g [FILE] or ;         execute query (and send results to file or |pipe)
  \h [NAME]              help on syntax of SQL commands, * for all commands
  \q                     quit psql
Query Buffer
  \e [FILE]              edit the query buffer (or file) with external editor
  \ef [FUNCNAME]         edit function definition with external editor
  \p                     show the contents of the query buffer
  \r                     reset (clear) the query buffer
  \s [FILE]              display history or save it to file
  \w FILE                write query buffer to file
Input/Output
  \copy ...              perform SQL COPY with data stream to the client host
  \echo [STRING]         write string to standard output
  \i FILE                execute commands from file
  \o [FILE]              send all query results to file or |pipe
  \qecho [STRING]        write string to query output stream (see \o)
Informational
  (options: S = show system objects, + = additional detail)
  \d[S+]                 list tables, views, and sequences
  \d[S+]  NAME           describe table, view, sequence, or index
  \da[S]  [PATTERN]      list aggregates
......

• There are number of switches that can help, we will look at the most common ones. Here is the shortcut to display user tables within a specific schema.

mydb=# \dt
                List of relations
 Schema |  Name   | Type  |  Owner  |   Storage   
--------+---------+-------+---------+-------------
 public | mytable | table | gpadmin | append only
(1 row)
mydb=#

NOTE To display tables from all the schemas within a database, we can use wildcard characters

mydb=# \dt *.*
                                 List of relations
       Schema       |             Name              | Type  |  Owner  |   Storage   
--------------------+-------------------------------+-------+---------+-------------
 information_schema | sql_features                  | table | gpadmin | heap
 information_schema | sql_implementation_info       | table | gpadmin | heap
 information_schema | sql_languages                 | table | gpadmin | heap
 information_schema | sql_packages                  | table | gpadmin | heap
 information_schema | sql_parts                     | table | gpadmin | heap
 information_schema | sql_sizing                    | table | gpadmin | heap
 information_schema | sql_sizing_profiles           | table | gpadmin | heap

• To display schema, better known as namespace, we can use "\dn"

mydb=# \dn
       List of schemas
        Name        |  Owner  
--------------------+---------
 hawq_toolkit       | gpadmin
 information_schema | gpadmin
 pg_aoseg           | gpadmin
 pg_bitmapindex     | gpadmin
 pg_catalog         | gpadmin
 pg_toast           | gpadmin
 public             | gpadmin
(7 rows)
mydb=# 

• To display a list of all the databases within a cluster, we can use "\l"

mydb=# \l
                 List of databases
   Name    |  Owner  | Encoding | Access privileges 
-----------+---------+----------+-------------------
 mydb      | gpadmin | UTF8     | 
 mydemo    | gpadmin | UTF8     | 
 postgres  | gpadmin | UTF8     | 
 template0 | gpadmin | UTF8     | 
 template1 | gpadmin | UTF8     | 
(5 rows)
mydb=# 

• Viewing list of users within a cluster (Users are independent of databases, unlike in Oracle). Each user can have access to multiple databases, schemas and objects. To display all the users and roles, we can use "\du"

mydb=# \du
                       List of roles
 Role name |            Attributes             | Member of 
-----------+-----------------------------------+-----------
 gpadmin   | Superuser, Create role, Create DB | 
mydb=# 

• There are multiple commands within psql shell like SELECT, CREATE, GRANT and so on. In order to read help for these commands, a "\h" i.e., backslash followed by "h" helps print the details. Here is an example mydb=# \h create user

mydb=# \h create user
Command:     CREATE USER
Description: define a new database role
Syntax:
CREATE USER name [ [ WITH ] option [ ... ] ]


where option can be:
    
      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | CREATEUSER | NOCREATEUSER
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
    | VALID UNTIL 'timestamp' 
    | IN ROLE rolename [, ...]
    | IN GROUP rolename [, ...]
    | ROLE rolename [, ...]
    | ADMIN rolename [, ...]
    | USER rolename [, ...]
    | SYSID uid 
    | RESOURCE QUEUE queuename


mydb=# \h alter database
Command:     ALTER DATABASE
Description: change a database
Syntax:
ALTER DATABASE name [ [ WITH ] option [ ... ] ]
where option can be:

    CONNECTION LIMIT connlimit

ALTER DATABASE name SET parameter { TO | = } { value | DEFAULT }
ALTER DATABASE name RESET parameter
ALTER DATABASE name RENAME TO newname
ALTER DATABASE name OWNER TO new_owner

mydb=# 

NOTE Total number of key Catalog tables in HAWQ are close to 100. This provides the users with an option to write their own views to sort users/objects and relations rather than learning about thousands of predefined views. Again, playing around with the utility within the Sandbox can help users get familiar with the syntax.