reading and writing data into external table using 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]$