Connector for Greenplum — Features and settings

Page content

Connector features

The following functionality is available when you access data with the connector for Greenplum.


Feature	Supported?
Load methods	Import (in-memory) External (in-database) On-demand
Custom queries	Yes
Stored procedures	Yes, if they exist in the database
Custom connection properties	No
Single sign-on with identity provider	No
Authoring in web client	No
Included in Spotfire distribution on Linux	Yes, if a driver exists

Data source properties

The following are the supported data source properties that you can configure when you create a data connection with the connector for Greenplum.


Option	Description
Fetch TSWTZ as timestamp	[ Only applicable when you use the DataDirect Greenplum ODBC driver.] Determines how the driver handles columns with `timestamp with time zone (TSWTZ)` data. When selected, `TSWTZ` columns are returned as `TIMESTAMP`. When cleared, `TSWTZ` columns are returned as `VARCHAR`.
Fetch TWFS as time	[Only applicable when you use the DataDirect Greenplum ODBC driver.] Determines how the driver handles columns with `time with fractional seconds (TWFS)` data. When selected, `TWFS` columns are returned as `TIME`. When cleared, `TWFS` columns are returned as `TIMESTAMP`.
Connection reset	[Only applicable when you use the DataDirect Greenplum ODBC driver.] Determines if connections are reset when they are removed from the connection pool. By default, Connection reset is selected.
Load balancing	[Only applicable when you use the DataDirect Greenplum ODBC driver.] Determines if load balancing should be enabled in the connection. When selected, you connect to either the main server that you entered as the Server, or to a server in the list of Alternate servers at random. You must enter at least one alternate server to use load balancing.
Load balance timeout (s)	[Only applicable when you Load balancing is selected, and you use the DataDirect Greenplum ODBC driver.] Determines the amount of time (in seconds), that inactive connections should remain open in the connection pool. Default value is 0.
Connection retry delay (s)	[Only applicable if Connection retry count is not 0, and you use the DataDirect Greenplum ODBC driver.] The amount of time (in seconds) to wait between connection retry attempts, if the connection is not successful. Default value is 0.
Connection retry count	[Only applicable when you use the DataDirect Greenplum ODBC driver.] The number of times to retry connecting to the database server, if the connection is not successful. Default value is 0.
Alternate servers	[Only applicable when you use the DataDirect Greenplum ODBC driver.] Specifies alternate servers to attempt connecting to, if the connection to the main database server is not successful, or if load balancing is enabled. To enter multiple servers, separate each server entry with comma.
IANA app code page	[Only applicable when you use the DataDirect Greenplum ODBC driver.] Determines what IANA code page to use to convert functions to ANSI. For more information, see the official documentation for the DataDirect Greenplum ODBC driver.
Connection timeout (s)	The maximum time, in seconds, allowed for a connection to the database to be established. The default value is 120 seconds. Note: If you set the connection timeout to zero, it will be interpreted as no timeout. This means that there will be no upper limit for trying to execute the command. This is generally not recommended.
Command timeout (s)	The maximum time, in seconds, allowed for a command to be executed. The default value is 1800 seconds. Note: If you set the command timeout to zero, it will be interpreted as no timeout. This means that there will be no upper limit for trying to execute the command. This is generally not recommended.

Supported data types

When you are setting up a connection to an external data source, Spotfire needs to map the data types in the data source to data types in Spotfire. The following are the data types that the Greenplum connector supports.


Database data type	Spotfire data type
BYTEA	Binary
BOOL	Boolean/Bool
DATE	Date
TIMESTAMP	DateTime
TIMESTAMPTZ	DateTime
INT2	Integer/Int
INT4	Integer/Int
OID	Integer/Int
REGCLASS	Integer/Int
REGOPER	Integer/Int
REGOPERATOR	Integer/Int
REGPROC	Integer/Int
REGPROCEDURE	Integer/Int
REGTYPE	Integer/Int
INT8	LongInteger/Long
FLOAT8	Real/Double
FLOAT4	SingleReal/Float
BIT	String
BPCHAR	String
CHAR	String
CIDR	String
INET	String
INTERVAL	String
MACADDR	String
MONEY	String
NAME	String
TEXT	String
VARBIT	String
VARCHAR	String
TIME	Time
TIMETZ	Time
RELTIME	Time
NUMERIC(p,s)	When p <= 9 and s = 0: Integer/Int When 9 < p <= 18 and s = 0: LongInteger/Long When p <= 15: Real/Double Else: Currency/Decimal

Supported functions

Supported functions are the functions that you can use when you work with in-database data tables, for example for calculated columns and custom expressions.

Note: Some supported functions might not be possible to use with your database. This depends on what functions are available in the database, which often differs between database versions and types.

The following are the functions that the Greenplum connector supports.


Function type	Functions supported
Date and Time	`YEAR, QUARTER, MONTH, WEEK, WEEKOFYEAR, WEEKOFMONTH, DAY, DAYOFWEEK, DAYOFMONTH, DAYOFYEAR, HOUR, MINUTE, SECOND, MILLISECOND, MICROSECOND`
Conversion	`SN`
Math	`ABS, ACOS, ASIN, ATAN, CBRT, CEILING, COS, COT, DEGREES, EXP, FLOOR, LN, LOG, MOD, PI, POWER, RADIANS, RANDOM, ROUND, SIGN, SIN, SQRT, TAN, TRUNC`
Operators	`%, -, +, *, /, &`
Statistical	`SUM, AVG, MIN, MAX, COUNT, UNIQUECOUNT, VARIANCE, VAR_POP, VAR_SAMP, STDDEV, STDDEV_POP, STDDEV_SAMP`
Text	`CONCAT, INITCAP, LENGTH, LOWER, LPAD, LTRIM, MD5, RPAD, REPEAT, REPLACE, RTRIM, STRPOS, SUBSTR, TRIM, UPPER`

Binning is also supported by the Greenplum connector.