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 or virtual segments. The virtual segments run the command in parallel. Scripts must be executable by the gpadmin user and reside in the same location on the master or the hosts of virtual segments.

The command that you specify in the external table definition executes from the database and cannot access environment variables from .bashrc or .profile. Set environment variables in the EXECUTE clause. The following external web table, for example, runs a command on the HAWQ master host:

  1. CREATE EXTERNAL WEB TABLE output (output text)
  2. EXECUTE 'PATH=/home/gpadmin/programs; export PATH; myprogram.sh'
  3.     ON MASTER 
  4. FORMAT 'TEXT';

The following command defines a web table that runs a script on five virtual segments.

  1. CREATE EXTERNAL WEB TABLE log_output (linenum int, message text) 
  2. EXECUTE '/var/load_scripts/get_log_data.sh' ON 5 
  3. FORMAT 'TEXT' (DELIMITER '|');

The virtual segments are selected by the resource manager at runtime.