Connecting to the Cluster
Once connected to the instance use the standard PostgreSQL psql client to connect to BrytlytDB by typing psql on the command line.
psql
ubuntu=#
You are connected to the cluster. View the nodes of the cluster by querying the contents of the catalogue.
ubuntu=# select node_name, node_port, node_host from pgxc_node;
node_name | node_port | node_host
---------+-------------+-----------
c0 | 5432 | localhost
d0 | 15432 | 127.0.0.1
(2 rows)
All the standard psql switches are available, including \timing to provide query runtime times and \q will disconnect and quit the psql client.
ubuntu=# \timing Timing is on. ubuntu=# \q >
BrytlytDB services can be viewed using the ps command.
>ps aux | grep data brytlyt 1854 /usr/local/brytlyt/bin/gtm -D /usr/local/data/g0 brytlyt 1855 /usr/local/brytlyt/bin/postgres --coordinator -D /usr/local/data/c0 brytlyt 1856 /usr/local/brytlyt/bin/postgres --datanode -D /usr/local/data/d0 ubuntu grep --color=auto data
On instance launch, six storage disks are configured as a RAID 0 array and mounted at /usr/local/data. This is the best place to stage data for loading to the cluster. Note: the brytlyt user is the owner of this folder.
Create a table using line item from the TPC-H benchmark.
CREATE FOREIGN TABLE lineitem ( l_orderkey INT, l_partkey INT, l_suppkey INT, l_linenumber INT, l_quantity REAL, l_extendedprice REAL, l_discount REAL, l_tax REAL, l_returnflag VARCHAR(1), l_linestatus VARCHAR(1), l_shipdate TIMESTAMP, l_commitdate TIMESTAMP, l_receiptdate TIMESTAMP, L_shipinstruct VARCHAR(25), l_shipmode VARCHAR(10), l_comment VARCHAR(44) ) SERVER gm_fdw_server OPTIONS (max_size '6001215') DISTRIBUTE BY HASH (L_ORDERKEY);
Import data using the COPY command
COPY lineitem FROM '/usr/local/data/lineitem.tbl' WITH (DELIMITER '|');
Run some simple queries
SELECT COUNT(*) FROM lineitem;
SELECT l_returnflag , l_linestatus , SUM(l_quantity) sum_qty, SUM(l_extendedprice) sum_base_price, SUM (l_extendedprice*(1-l_discount)) sum_disc_price, SUM (l_extendedprice*(1-l_discount)*(1+l_tax)) sum_charge, AVG(l_quantity) avg_qty, AVG (l_extendedprice) avg_price, AVG (l_discount) avg_disc, COUNT(*) count_order FROM lineitem GROUP BY l_returnflag, l_linestatus ORDER BY l_returnflag, l_linestatus