Greenplum Query

Use this activity to run a simple or complex SQL query on a Greenplum database. The Greenplum Query activity returns information in the form of rows.

Configuration

The Configuration tab has the following fields:

Field Description
Connection Name of the PostgreSQL database connection from which to retrieve information. You can select a connection from the drop-down list.

Input Settings

Field Description
Query An SQL query used to query the database. The query can be a simple query or a complex query. A complex query has nested SQL statements. In the query statement, you can construct Prepared SQL queries by using the substitution variables of the form ?<fieldname>. For example: 
select * from student where name = ?name;
Each substitution variable identifies an input parameter. The mapped value of the input parameter is added in the substitution variable at run time. The substitution variable can be reused for the same input parameter elsewhere in the query. Input parameters used in the WHERE clause, and output parameters used in the SELECT clause, and the information about their type is automatically fetched from the database by using the connection that you have selected for the query. Input and output fields on the Input and Output tabs of the activity are also automatically generated.
Note: Include a semicolon (;) at the end of the query to indicate the end of the query. A missing semicolon at the end of the query results in a syntax error.
Examples of simple and complex queries:

  • Simple query example:

    In the following query, the output fields are generated from the column of the student table. 

    SELECT * FROM student;

    In the following query, the output fields are generated for name, dept_name and tot_cred and input fields are generated for dept_name (varchar) and tot_cred (numeric). Also the mapped values for dept_name and tot_cred fields are added in ?dept_name and ?tot_cred substitution variables at run time. 

    SELECT name, dept_name, tot_cred
    FROM student 
    WHERE dept_name = ?dept_name and tot_cred > ?tot_cred 
    ORDER BY dept_name;

  • Complex query example: 

    SELECT *
    FROM (SELECT dept_name, SUM(tot_cred) AS total_credit
            FROM student
            GROUP BY dept_name) SUBS, department WHERE 
            SUBS.dept_name = department.dept_name AND total_credit > 8000;
Fields The grid is provided for information purposes only.

Input

This tab contains the input schema. The fields that were selected on the Input Settings tab are available in the schema. You can either hard code their values or map them to a field from the output of a preceding activity in the flow using the Mapper.

Output

This tab displays the activity output schema in a tree structure format. The output of an activity is displayed for informational purposes only and cannot be modified or altered. The information in this schema varies depending on the fields that you selected on the Input Settings tab.

The properties that are displayed on the Output tab schema correspond to the output of this activity and can be used as input by subsequent activities in the flow.

Related concepts
Related reference