Connector for Teradata — Features and settings

Page content

Connector features

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


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

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 Teradata.


Option	Description
Server	The name of the server where your data is located. To include a port number, add it directly after the server name, preceded by colon. Example with port number: MyTeradataDatabaseServer:1234
Authentication method	The authentication method to use when logging into the database. Choose from Teradata authentication (TD2), LDAP authentication, and Windows authentication. Teradata authentication (TD2) Authenticate using a database user. Users who open the connection, or an analysis using the connection, will be prompted to enter database credentials to connect to the data source. Tip: You can save database credentials, unencrypted, as part of an analysis file, using a setting in the Data Source Settings dialog. If credentials are embedded in your analysis, it means that users will not be prompted to enter credentials to connect to the data source. Note that it can be a security risk to embed database credentials in an analysis file. LDAP authentication When using LDAP authentication, users are prompted to enter their LDAP credentials. Users with valid accounts in the LDAP directory will be able to connect and read data. Windows authentication When using Windows Authentication, for example Kerberos, the access token of the logged in user will be used. Users that have been given the appropriate access rights to Teradata will be able to connect and read data. Domain credentials are not stored in the analysis file.
Connect	Connects you to the specified server, and populates the drop-down list of available databases.
Database	Lists the available databases after a successful connection has been made.
Use data encryption	Select this check box to use data encryption when connecting to Teradata.
Use X Views	Select this check box to use X Views. X Views limit a user's view to only those databases and tables that the user has permission to access. This might reduce the time it takes to load metadata.
Session character set	Select which session character set to use when connecting to Teradata.
Session mode	Select which session mode to use when connecting to Teradata. The mode you select affects how errors in the transaction process are handled, as well as performance.
Connection timeout (s)	Specifies the maximum number of seconds allowed for a connection to the database to be established. Note: If you set the connection timeout to zero, it will be interpreted as no timeout. That is, there will be no upper limit for trying to connect. This is generally not recommended.
Command timeout (s)	Specifies the maximum number of seconds allowed for a command to be executed. Note: If you set the command timeout to zero, it will be interpreted as no timeout. That is, there will be no upper limit for trying to execute the command. This is generally not recommended.
Use proxy settings	Select this check box to connect to the data source using a proxy user.
Proxy user: Use current Spotfire user	Select this option to use the Spotfire username of the user opening the connection as the proxy user.
Proxy user: Other	Select this option to manually specify a proxy user for the connection.
Proxy role	Optionally, you can specify a proxy role for the connection.

Query band information from Spotfire

To make it easier for database administrators to troubleshoot and understand where queries come from, Spotfire includes query bands with queries to Teradata. The table describes the query bands and the information that they contain.


Query band name	Description
`ApplicationName`	The name of the application. The value is always '`SpotfireDXP`' .
`Version`	The version number of the Spotfire application.
`ClientUser`	The Spotfire username of the logged in user.
`Spotfire.Domain`	The Windows domain, if the user is logged in to the Spotfire Server with Windows authentication. For other authentication methods the value is '`spotfire`'.
`Spotfire.Analysis`	The file name (for local files) or Spotfire library path of the Spotfire analysis.
`Spotfire.Visualization`	The name of the visualization that initiated the query.
`ProxyUser`	[This query band is only included if you select Use proxy settings.] The specified proxy user.
`ProxyRole`	[This query band is only included if you select Use proxy settings.] The specified proxy role.

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 Teradata connector supports.


Database data type	Spotfire data type
BLOB	Binary
BYTE	Binary
VARBYTE	Binary
DATE	Date
PERIOD DATE	Date [As two columns, begin/end]
PERIOD TIMESTAMP	DateTime [As two columns, begin/end]
PERIOD TIMESTAMP WITH TIME ZONE	DateTime [As two columns, begin/end]
TIMESTAMP	DateTime
TIMESTAMP WITH TIMEZONE	DateTime
BYTEINT	Integer
INTEGER	Integer
SMALLINT	Integer
BIGINT	LongInteger
FLOAT	Real
NUMBER	Real
CHARACTER	String
GRAPHIC	String
JSON	String Note: Because of limitations in the Teradata cast function, you can load JSON columns with a maximum of 32000 characters into Spotfire. The entirety of the JSON code, including tags, is included in the character count. When you define prompts for JSON columns from Teradata, only the prompt type Manual input is available.
LONG VARCHAR	String
LONG VARGRAPHIC	String
VARCHAR	String
VARGRAPHIC	String
XML	String Note: Because of limitations in the Teradata cast function, you can load XML columns with a maximum of 32000 characters into Spotfire. The entirety of the XML code, including tags, is included in the character count.
PERIOD TIME	Time [As two columns, begin/end]
PERIOD TIME WITH TIME ZONE	Time [As two columns, begin/end]
TIME	Time
TIME WITH TIME ZONE	Time
INTERVAL DAY	TimeSpan
INTERVAL DAY TO HOUR	TimeSpan
INTERVAL DAY TO MINUTE	TimeSpan
INTERVAL DAY TO SECOND	TimeSpan
INTERVAL HOUR	TimeSpan
INTERVAL HOUR TO MINUTE	TimeSpan
INTERVAL HOUR TO SECOND	TimeSpan
INTERVAL MINUTE	TimeSpan
INTERVAL MINUTE TO SECOND	TimeSpan
INTERVAL SECOND	TimeSpan
DECIMAL(p,s)	When p <= 9 and s = 0: Integer When 9 < p <= 18 and s = 0: LongInteger Else: Real

Note: User defined data types, including geometry types, are not supported.

Data in Amazon S3 and Microsoft Azure Blob

If your Teradata instance supports Native Object Storage (NOS), you can query and analyze data from files in Amazon S3 and Microsoft Azure Blob in Spotfire. With NOS functionality, you can define foreign tables for files in S3 and Azure Blob. Then in Spotfire, you can access the data by selecting those database tables in your connection.

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 Teradata connector supports.


Function type	Functions supported
Date and Time	`Year, Month, Day, DayOfMonth, Hour, Minute, Second, Quarter`
Conversion	`SN`
Math	`Abs, Log, Ln, Exp, Sqrt, Sin, Cos, Tan, ASin, ACos, ATan, ATan2, Sinh, Cosh, Tanh, ASinh, ACosh, ATanh, NullIfZero, ZeroIfNull, Power`
Operators	`+, -, *, /, %, Mod`
Statistical	`Sum, Avg, Min, Max, Count, UniqueCount, StdDev_Samp, StdDev_Pop, Var_Samp, Var_Pop, Skew, Kurtosis, CoVar_Pop, CoVar_Samp, Corr, Regr_AvgX, Regr_AvgY, Regr_Count, Regr_Intercept, Regr_R2, Regr_Slope, Regr_SXX, Regr_SYX, Regr_SYY`
Text	`Character_Length, Lower, Upper, Soundex, Trim`

Binning and macros are also supported by the Teradata connector.

Note: Macros are handled the same way as stored procedures in Spotfire. When you connect to Teradata, any macros in the database will be available in the list of database tables in the Select data flyout (or Views in Connection dialog).