The directives that are processed by the Redshift reader are listed below. The suffixes shown are prefixed by the current <ReaderKeyword>_ in a mapping file. By default, the <ReaderKeyword> for the Redshift reader is REDSHIFT_IN.
DATASET/DATABASE
This specifies the name of the Redshift database. The database must exist in the ORDBMS.
Required/Optional
Required
Mapping File Syntax
REDSHIFT_DATASET testdb
Workbench Parameter
Source Redshift Dataset
HOST
Required/Optional: Required
This specifies the machine running the Redshift ORDBMS as either an IP address or host name. The database must have proper permissions and be set up to accept TCP/IP connections if connecting from a remote machine.
Required/Optional
Required
Mapping File Syntax
REDSHIFT_IN_HOST myserver
Workbench Parameter
Host
PORT
When connecting remotely, this specifies the TCP/IP port on which to connect to the ORDBMS service. The default port is 5439.
Required/Optional
Required
Mapping File Syntax
REDSHIFT_IN_PORT 5439
Workbench Parameter
Port
USER_NAME
The name of user who will access the database. The named user must exist with appropriate Redshift permissions.
Required/Optional
Required
Mapping File Syntax
REDSHIFT_IN_USER_NAME user
Workbench Parameter
User Name
PASSWORD
The password of the user accessing the database. This parameter is optional when using a trusted connection. Other authentication types such as password or MD5 require this parameter to be set.
Required/Optional
Required
Mapping File Syntax
REDSHIFT_IN_PASSWORD secret
Workbench Parameter
Password
DEF
Required/Optional
Required
Mapping File Syntax
REDSHIFT_DEF <tableName> \
[redshift_where_clause <whereClause>] \
[<fieldName> <fieldType>] +
OR
REDSHIFT_DEF <queryName> \
[redshift_sql_statement <sqlQuery>]
[<fieldName> <fieldType>] +
The <tableName> must match a Redshift table in the database. This will be used as the feature type of all the features read from the table. The exception to this rule is when using the sql_statement keyword. In this case, the DEF name may be any valid alphabetic identifier; it does not have to be an existing table name – rather, it is an identifier for the custom SQL query. The feature type of all the features returned from the SQL query are given the query name.
The <fieldType> of each field must be given, but it is not verified against the database definition for the field. In effect, it is ignored.
The definition allows specification of separate search parameters for each table. If any of the configuration parameters are given, they will override, for that table, whatever global values have been specified by the reader keywords listed above. If any of these parameters is not specified, the global values will be used.
The following table summarizes the definition line configuration parameters:
|
Parameter |
Contents |
|---|---|
| where_clause |
This specifies the SQL WHERE clause applied to the attributes of the layer’s features to limit the set of features returned. If this is not specified, then all the tuples are returned. This keyword will be ignored if the sql_statement is present. |
| sql_statement |
This specifies an SQL SELECT query to be used as the source for the results. If this is specified, the Redshift reader will execute the query, and use the resulting rows as the features instead of reading from the table <queryName>. All returned features will have a feature type of <queryName>, and attributes for all columns selected by the query. |
If no <whereClause> is specified, all rows in the table will be read and returned as individual features. If a <whereClause> is specified, only those rows that are selected by the clause will be read. Note that the <whereClause> does not include the word WHERE.
The Redshift reader allows one to use the sql_statement parameter to specify an arbitrary SQL SELECT query on the DEF line. If this is specified, FME will execute the query, and use each row of data returned from the query to define a feature. Each of these features will be given the feature type named in the DEF line, and will contain attributes for every column returned by the SELECT. In this case, all DEF line parameters regarding a WHERE clause or spatial querying are ignored, as it is possible to embed this information directly in the text of the <sqlQuery>.
The following example selects rows from the table ROADS, placing the resulting data into FME features with a feature type of MYROADS. Imagine that ROADS defines the geometry for the roads, and has a numeric field named ID, a text field named NAME and a geometry column named GEOM.
REDSHIFT_DEF MYROADS \
sql_statement ‘SELECT id, name FROM ROADS’
IDs
This optional specification is used to limit the available and defined database tables files that will be read. If no IDs are specified, then no tables are read. The syntax of the IDs keyword is:
REDSHIFT_IDs <featureType1> \
<featureType2> \
<featureTypeN>
The feature types must match those used in DEF lines.
The example below selects only the ROADS table for input during a translation:
REDSHIFT_IDs ROADS
Required/Optional
Optional
FEATURES_PER_FETCH
In order to avoid loading all the features in memory at once when reading a large dataset, cursors are used to retrieve the rows from the database. This optional keyword specifies the number or rows to be read at one time for a given query. The default is 10000 rows and should be sufficient in most cases. However this may need to be lowered or raised depending on the capabilities of the specific hardware in use and the data being read.
Required/Optional
Optional
Mapping File Syntax
The example below selects a small set of features per extraction:
REDSHIFT_IN_FEATURES_PER_FETCH 5000
Workbench Parameter
Number Of Features To Fetch At A Time
RETRIEVE_ALL_SCHEMAS
This parameter is applicable only when generating a mapping file, generating a workspace, or when retrieving schemas in an FME Objects application.
When set to yes, schemas for all of the tables and feature classes in the database are returned.
When set to no (or missing), and if RETRIEVE_ALL_TABLE_NAMES is similarly set to no (or missing), only schemas requested by the IDs parameter are returned.
Required/Optional
Optional
Values
YES | NO (default)
NO: The reader will return the schemas for the feature types specified in the IDs. If no features are specified in IDs, then FME returns the schema features for all the tables.
If this value is not specified, then it is assumed to be No.
YES: Indicates to the reader to return all the schemas of the tables in the database.
Mapping File Syntax
Not applicable.
FME Objects applications would include RETRIEVE_ALL_SCHEMAS followed by “YES” in the parameters array passed to IFMEUniversalReader::open().
Workbench Parameter
Not applicable
RETRIEVE_ALL_TABLE_NAMES
This parameter is only applicable when generating a mapping file, generating a workspace or when retrieving schemas in an FME Objects application.
When set to yes, and if RETRIEVE_ALL_SCHEMAS is set to no (or missing), names for all of the tables and feature classes in the database are returned. When set to no (or missing), and if RETRIEVE_ALL_SCHEMAS is similarly set to no (or missing), the schemas requested by the IDs directive are returned.
Note: If RETRIEVE_ALL_SCHEMAS is also set to Yes, then RETRIEVE_ALL_SCHEMAS takes precedence.
Required/Optional
Optional
Values
YES | NO (default)
Mapping File Syntax
Not applicable.
FME Objects applications would include RETRIEVE_ALL_TABLE_NAMES followed by “YES” in the parameters array passed to IFMEUniversalReader::open().
Workbench Parameter
Not applicable (used when you browse a Table List)
EXPOSED_ATTRS
This directive allows the selection of format attributes to be explicitly added to the reader feature type.
This is similar to exposing format attributes on a reader feature type once it has been generated; however, it is even more powerful because it enables schema-driven applications other than Workbench to access and leverage these attributes as if they were explicitly on the schema as user attributes.
The result of picking a list of attributes is a comma-separated list of attribute names and types that will be added to the schema features. Currently all reader feature types will receive the same set of additional schema attributes for a given instance of the reader.
Required/Optional
Optional
Mapping File Syntax
Not applicable.
While it is possible for FME Objects applications to invoke this directive, the required format is not documented.
This directive is intended for use in our GUI applications (for example, Workbench) only.
Workbench Parameter
Additional Attributes to Expose