reading and writing data into external table using gpfdist



Note: A simple demo how to read data from greenplum readable external table and write data into greenplum writable external table using gpfdist

1. created a tmp directory under user home directory.

2. started the gpfdist

[gpadmin@mjbes001 tmp]$ gpfdist -t 30 -w 30 -p 8080 -d $PWD -l $PWD/gpfdist.log &
[1] 24991
[gpadmin@mjbes001 tmp]$ 2016-06-17 17:41:01 24991 INFO Before opening listening sockets - following listening sockets are available:
2016-06-17 17:41:01 24991 INFO IPV6 socket: [::]:8080
2016-06-17 17:41:01 24991 INFO IPV4 socket: 0.0.0.0:8080
2016-06-17 17:41:01 24991 INFO Trying to open listening socket:
2016-06-17 17:41:01 24991 INFO IPV6 socket: [::]:8080
2016-06-17 17:41:01 24991 INFO Opening listening socket succeeded
2016-06-17 17:41:01 24991 INFO Trying to open listening socket:
2016-06-17 17:41:01 24991 INFO IPV4 socket: 0.0.0.0:8080
Serving HTTP on port 8080, directory /home/gpadmin/tmp

[gpadmin@mjbes001 tmp]$ ps -ef|grep gpfdist
ps: /usr/local/greenplum-db/./lib/libz.so.1: no version information available (required by /lib64/libdw.so.1)
gpadmin  24991 24684  0 17:41 pts/0    00:00:00 gpfdist -t 30 -w 30 -p 8080 -d /home/gpadmin/tmp -l /home/gpadmin/tmp/gpfdist.log
gpadmin  24993 24684  0 17:41 pts/0    00:00:00 grep --color=auto gpfdist
3. create a simple x.csv file and insert 2 rows in it
[gpadmin@mjbes001 tmp]$ vi x.csv
[gpadmin@mjbes001 tmp]$ cat x.csv
1,2
1,2
~
~

4. Now lets login into database and create readable external table.
[gpadmin@mjbes001 tmp]$ psql
psql (8.2.15)
Type "help" for help.
test=# create external table ext_test_x (a int, b int) location ('gpfdist://mjbes001:8080/x.csv') format 'csv';
CREATE EXTERNAL TABLE

5. Create regular table to persist data into database.
test=# create table test_x (like ext_test_x);
NOTICE:  Table doesn't have 'distributed by' clause, defaulting to distribution columns from LIKE table
CREATE TABLE

6. Select data from external table to check if it is reading from external table and from csv file. 
test=# select * from ext_test_x;
 a | b
---+---
 1 | 2
 1 | 2
(2 rows)

7. Insert data into database table by reading the data from readable external table using gpfdist
test=# insert into test_x select * from ext_test_x;
INSERT 0 2

8. Now we have succesfully demonstrated how readable external table works in greenplum. Now lets write this table into a writable external table.

9. create writable external table
test=# create writable external table ext_test_x_w(a int, b int) location ('gpfdist://mjbes001:8080/x_w.csv') format 'csv';
CREATE EXTERNAL TABLE

10. insert data into writable external table from the database table.
test=# insert into ext_test_x_w select * from test_x;
INSERT 0 2
test=# \q

11. Validate if data is really inserted into writable external table.
[gpadmin@mjbes001 tmp]$ cat x_w.csv
1,2
1,2
[gpadmin@mjbes001 tmp]$


Comments