CODEWITHSUNDEEP
postgresqlgreenplumhawq
NEO
NEO

Reputation: 399

How do i create and read External table in GPDB

We have small gpdb cluster. When i am trying to create and read on my first External table using gpfdist protocol.

Getting error at reading of external table : that is 

prod=# select * from ext_table
prod-# ;
ERROR:  connection with gpfdist failed for gpfdist://gpmasterhost:8080/demo/gp_RevenueReport_stg0.txt. effective url: http://gpmasterhost:8080/demo/gp_RevenueReport_stg0.txt. error code = 111 (Connection refused)  (seg0 slice1 datanode2 40000 pid=5402)
prod=#

We tried DDL command for external table :

CREATE EXTERNAL TABLE ext_table
(
  "ID" bigint,
  "time" timestamp without time zone,
   )
 LOCATION (
    'gpfdist://gpmasterhost:8080/demo/gp_RevenueReport_stg0.txt'
)
 FORMAT 'TEXT' (delimiter ';' null '' escape '~' )
ENCODING 'UTF8';

Any help on it would be much appreciated !

Upvotes: 0

Views: 1154

Answers (2)

CanuckAFK
CanuckAFK

Reputation: 101

As Jon had said, you will need to run gpfdist on your "gpmasterhost" system.

However, based on your notes gpfdist is running in your demo directory:

ps aux |grep gpfdist root 9417 0.0 0.0 103244 868 pts/1 R+ 14:57 0:00 grep gpfdist gpadmin 32581 0.0 0.0 27148 1692 pts/0 S 14:49 0:00 gpfdist -p 8080 -d /home/gpadmin/demo

So you will either need to change your EXTERNAL definition to (note I am not using the demo directory):

CREATE EXTERNAL TABLE ext_table ( "ID" bigint, "time" timestamp without time zone, ) LOCATION ( 'gpfdist://gpmasterhost:8080/gp_RevenueReport_stg0.txt' ) FORMAT 'TEXT' (delimiter ';' null '' escape '~' ) ENCODING 'UTF8';

Or run gpfdist up one level (/home/gpadmin) with no modification to your external table.

Upvotes: 1

Jon Roberts
Jon Roberts

Reputation: 2106

You have to create the gpfdist process on "gpmasterhost" listening on port 8080 and serving files that include the directory demo which contains gp_RevenueReport_stg0.txt. 

gpfdist -p 8080 -d path_to_demo &

Upvotes: 1

Related Questions