Using a Custom Protocol

Using a Custom Protocol

Greenplum provides protocols such as gpfdist, http, and file for accessing data over a network, or you can author a custom protocol. You can use the standard data formats, TEXT and CSV, or a custom data format with custom protocols.

You can create a custom protocol whenever the available built-in protocols do not suffice for a particular need. For example, if you need to connect Greenplum Database in parallel to another system directly, and stream data from one to the other without the need to materialize the system data on disk or use an intermdiate process such as gpfdist.

  1. Author the send, receive, and (optionally) validator functions in C, with a predefined API. These functions are compiled and registered with the Greenplum Database. For an example custom protocol, see Example Custom Data Access Protocol.
  2. After writing and compiling the read and write functions into a shared object (.so), declare a database function that points to the .so file and function names.

    The following examples use the compiled import and export code.

    CREATE FUNCTION myread() RETURNS integer
    as '$libdir/gpextprotocol.so', 'myprot_import'
    LANGUAGE C STABLE;
    CREATE FUNCTION mywrite() RETURNS integer
    as '$libdir/gpextprotocol.so', 'myprot_export'
    LANGUAGE C STABLE;
    

    The format of the optional function is:

    CREATE OR REPLACE FUNCTION myvalidate() RETURNS void 
    AS '$libdir/gpextprotocol.so', 'myprot_validate' 
    LANGUAGE C STABLE; 
    
  3. Create a protocol that accesses these functions. Validatorfunc is optional.
    CREATE TRUSTED PROTOCOL myprot(
    writefunc='mywrite',
    readfunc='myread', 
    validatorfunc='myvalidate');
  4. Grant access to any other users, as necessary.
    GRANT ALL ON PROTOCOL myprot TO otheruser
    
  5. Use the protocol in readable or writable external tables.
    CREATE WRITABLE EXTERNAL TABLE ext_sales(LIKE sales)
    LOCATION ('myprot://<meta>/<meta>/…')
    FORMAT 'TEXT';
    CREATE READABLE EXTERNAL TABLE ext_sales(LIKE sales)
    LOCATION('myprot://<meta>/<meta>/…')
    FORMAT 'TEXT';
    

Declare custom protocols with the SQL command CREATE TRUSTED PROTOCOL, then use the GRANT command to grant access to your users. For example:

  • Allow a user to create a readable external table with a trusted protocol
    GRANT SELECT ON PROTOCOL <protocol name> TO <user name>
                   
  • Allow a user to create a writable external table with a trusted protocol
    GRANT INSERT ON PROTOCOL <protocol name> TO <user name>
                   
  • Allow a user to create readable and writable external tables with a trusted protocol
    GRANT ALL ON PROTOCOL <protocol name> TO <user name>