Ok, we’ve got Greenplum up and running and now we want to load some data. There are several methods available, of which we’ll look at external tables.
This demo will load a simple file containing 2 fields and 10 records into an empty table.
Here is the input file:
gpadmin$ more test1.txt1,aaa2,bbb3,ccc4,ddd5,eee6,fff7,ggg8,hhh9,iii10,jjj
So, let’s create an external table called ‘test1’:
CREATE EXTERNAL TABLE TEST1 (COL1 INT, COL2 TEXT) LOCATION (‘gpfdist://gpdemo:8080/*’) FORMAT ‘TEXT’ (DELIMITER ‘,’);
We now have a table that maps to an external file. This table can be queried just like a normal table:
test=# select * from test1;col1 | col2——+——1 | aaa2 | bbb3 | ccc4 | ddd5 | eee6 | fff7 | ggg8 | hhh9 | iii10 | jjj(10 rows)
To load the 10 records from the external table ‘test1’ to the table ‘test_load’ we simply run an INSERT/SELECT statement via psql:
INSERT INTO TEST_LOAD SELECT * FROM TEST1;
The target table ‘test_load’ now contains the same data as the external table ‘test1’ from which it has been loaded:
test=# select * from test_load;col1 | col2——+——2 | bbb4 | ddd6 | fff8 | hhh10 | jjj1 | aaa3 | ccc5 | eee7 | ggg9 | iii(10 rows)
So there you have it – a simple load into Greenplum from a file that is treated as an external table.