Accessing VERTICA_READER Plug-in Attributes

Note that, when you use Vertica as source/reader, the plug-in supports only pass-through partitioning. When you use Vertica as target/writer, the plug-in supports pass-through partitioning and key range partitioning. (Note that the key value partitioning source should not be through the Vertica plug-in.)

  1. While in Workflow Manager, double-click any task.
  2. In the Edit Tasks dialog, select the Mappings tab.
  3. Select your source qualifier (this example uses SQ_datatypes_src).
  4. Minimize the Readers and Connections areas to focus on Properties.

    Plug-in features are listed under the Properties area.

The table that follows lists and describes the plug-in attributes for source/reader.

Attribute Value

Schema Name

Schema Name allows you to override the default schema name of the mapped table. If you have only one source, you can override the schema name by simply entering the new schema name.

Where you have many sources in a mapping that all go to the one source qualifier, you override schema names with the following format. Use a semicolon as separator:

<old_table_name>=<new_schema_name>; <another_table_name>=<another_new_schema_name>

Important: In the format given above, you do not actually enter the schema name to change the schema name. Instead, you enter the table name in the schema name field (to the left of the equal sign).You then provide the new schema name as given in the format above (to the right of the equal sign; the equal sign acts to set the new name). Use a semicolon as separator for multiple schema name changes.

Example:

datatype_src=newschemaname
Table Name

Table Names changes are similar to Schema Name changes in regards to how you format the changes. If you have only one table name to change, you just enter the new table name.

If you have more than one table name to change, you use the following format:

<old_table_name>=<new_table_name>; <another_table_name>=<another_new_table_name>

Example:

datatypes_src=newname;othertablename_src=newerone

Important: If using the Schema Name option along with the Table Name option, note that the Schema Name entry uses the old table name versus the replacement name you have added here in the Table Name option. An example follows.

Schema Name entry:

  • tbl1=new-schema-name

Table Name entry:

  • tbl1=tbl2

That is, you would not specify tbl2 under the Schema Name entry.

Select Distinct If selected, returns only distinct values.
Number of Sorted Ports

Sorts incoming data, specifying order by ports.

User Defined Join

Allows you to do overrides to your mapping, enabling you to do very specific joins (e.g., inner and outer joins). When you click the arrow to the right of the field, a SQL box pops up. What you enter in the box becomes a custom join clause added to the generated SQL statement.

Basically you can enter a typical join statement with SELECT being assumed.

Examples:

  • s1.a.col = s2.b.col

    (where s1 and s2 are schema names)

  • a JOIN b ON a.col = b.col
  • a.col = b.col
Pre SQL

Enter complete SQL statements that run before you read a table.

For example, truncate or add data to a table before you read it.

Post SQL

Enter complete SQL statements that run after you read a table.

Modulus Partitioning

If selected, performs modulus partitioning (no replication of data) rather than straight pass-through partitioning.

Check this option only if using pass-through partitioning.

Time UTC Conversion

If selected, keeps time synchronized when you are using Vertica as both source and target. Time data changes to UTC time zone.

Select this option and the writer option only when using Vertica as both source and target.

If unchecked, when you are using Vertica as both source and target, time data changes to the JVM time zone.

SQL Query

Overrides the entire query rather than overriding just a portion of a query.

Source Filter

Allows you to do overrides to your mapping by overriding the WHERE clause (similar to the way User Defined Join allows you to override a join clause).