Command-based Web External Tables
The output of a shell command or script defines command-based web table data. Specify the command in the
EXECUTE clause of
CREATE EXTERNAL WEB TABLE. The data is current as of the time the command runs. The
EXECUTE clause runs the shell command or script on the specified master, and/or segment host or hosts. The command or script must reside on the hosts corresponding to the host(s) defined in the
By default, the command is run on segment hosts when active segments have output rows to process. For example, if each segment host runs four primary segment instances that have output rows to process, the command runs four times per segment host. You can optionally limit the number of segment instances that execute the web table command. All segments included in the web table definition in the
ON clause run the command in parallel.
The command that you specify in the external table definition executes from the database and cannot access environment variables from
.profile. Set environment variables in the
EXECUTE clause. For example:
=# CREATE EXTERNAL WEB TABLE output (output text) EXECUTE 'PATH=/home/gpadmin/programs; export PATH; myprogram.sh' ON MASTER FORMAT 'TEXT';
Scripts must be executable by the
gpadmin user and reside in the same location on the master or segment hosts.
The following command defines a web table that runs a script. The script runs on five virtual segments selected by the resource manager at runtime.
=# CREATE EXTERNAL WEB TABLE log_output (linenum int, message text) EXECUTE '/var/load_scripts/get_log_data.sh' ON 5 FORMAT 'TEXT' (DELIMITER '|');