Connector for Vertica — Features and settings
You can connect to and access data from Vertica databases with the connector for Vertica. On this page, you can find information about the capabilities, available settings, and things to keep in mind when you work with data connections to Vertica.
Page content
Connector features
The following functionality is available when you access data with the connector for Vertica.
| Feature | Supported? |
|---|---|
| Load methods |
|
| Custom queries | Yes |
| Stored procedures | No |
| Custom connection properties | Yes |
| Single sign-on with identity provider | Yes |
| Authoring in web client | Yes |
| Included in Spotfire distribution on Linux | No |
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 Vertica.
| Option | Description |
|---|---|
| Server |
The name of the server where your data is located. To include a port number, add it directly after the name preceded by colon. Example with port number:
|
| Authentication method | The authentication method to use for
logging in to the Vertica database. The following authentication methods are
available:
|
| Database |
[Only applicable for Identity provider (OAuth2) authentication.] The name of the Vertica database you want to access data from. |
| Identity provider |
[Only applicable for Identity provider (OAuth2) authentication.] Select the identity provider you want to use for logging in to the data source. The options available in the drop-down menu are the identity providers you have added to the OAuth2IdentityProviders preference. |
| Scopes |
[Only applicable for Identity provider (OAuth2) authentication.] Scopes determine what permissions Spotfire requests on your behalf when you log in to the data source. Default Use the default scopes that you have specified for your identity provider in the OAuth2IdentityProviders preference. Custom Enter scopes manually in the text box. Separate values with a space. |
| Use connection load balancing | Specify whether to use load balancing in the data connection. The load balancing scheme is determined by the load balancing policy configuration of the database you are connecting to. |
| Connection timeout (s) | The maximum time, in second, allowed for a
connection to the database to be established.
The default value is 120 seconds. Note: If you set the timeout to zero, it is interpreted as no
timeout. This means that there is no upper limit for how much time the
connection attempt can take, which 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 timeout to zero, it is interpreted as no
timeout. This means that there is no upper limit to how much time it can take
for a command to execute, which is generally not recommended.
|
Custom properties for Vertica connection data sources
The following is the default list of driver settings that are allowed as custom properties in Vertica connection data sources. To learn how to change the allowed custom properties, see Controlling what properties are allowed.
Default allowed custom properties
BinaryTransfer, ConnSettings, DirectBatchInsert, IntegratedSecurity, IsolationLevel, KerberosHostname, KerberosServiceName, Label, LoadBalanceTimeout, MaxPoolSize, MinPoolSize, Pooling, PreferredAddressFamily, ReadOnly, ResultBufferSize, SSL, WorkloadSupported 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 Vertica connector supports.
| Database data type | Spotfire data type |
|---|---|
| BOOLEAN | Boolean |
| MONEY | Currency |
| DATE | Date |
| INTEGER | LongInteger |
| BIGINT | LongInteger |
| DOUBLE PRECISION | Real |
| FLOAT | Real |
| CHAR | String |
| LONG VARCHAR | String |
| VARCHAR | String |
| ARRAY | String |
| ROW | String |
| SET | String |
| TIME | Time |
| TIMETZ | DateTime |
| TIMESTAMP | DateTime |
| TIMESTAMPTZ | DateTime |
| INTERVAL DAY | TimeSpan |
| INTERVAL DAY TO HOUR | TimeSpan |
| INTERVAL DAY TO MINUTE | TimeSpan |
| INTERVAL DAY TO SECOND | TimeSpan |
| INTERVAL HOUR | TimeSpan |
| INTERVAL HOUR TO SECOND | TimeSpan |
| INTERVAL MINUTE | TimeSpan |
| INTERVAL MINUTE TO SECOND | TimeSpan |
| INTERVAL SECOND | TimeSpan |
| NUMERIC |
When p <= 9 and s = 0: Integer When 9 < p <= 18 and s = 0: LongInteger When p <= 28: Currency Else: Real |
| BINARY | Binary |
| VARBINARY | Binary |
| GEOGRAPHY | Binary |
| GEOMETRY | Binary |
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.
The following are the functions that the Vertica connector supports.
| Function type | Functions supported |
|---|---|
| Date and Time |
Year, Quarter, Month Week, Day, DayOfMonth, DayOfYear,
DayOfWeek, Hour, Minute, Second, Microsecond
|
| Conversion |
SN
|
| Math |
Abs, ACos, ASin, ATan, ATan2, Cbrt, Ceiling, Cos, Cot,
Degrees, Distance, DistanceV, Exp, Floor, Ln, Log, Mod, Pi, Power, Radians,
Random, RandomInt, Round, Sign, Sin, Sqrt, Tan, Trunc
|
| Operators |
%, -, +, *, /
|
| Statistical |
Sum, Avg, Min, Max, Count, UniqueCount, Variance,
Var_Pop, Var_Samp, StdDev, StdDev_Pop, StdDev_Samp, Corr, CoVar_Pop,
CoVar_Samp, Regr_AvgX, Regr_AvgY, Regr_Count, Regr_Intercept, Regr_R2,
Regr_Slope, Regr_SXX, Regr_SXY, Regr_SYY
|
| Text |
CONCAT, INSTR, LENGTH, LOWER, LPAD, LTRIM, MAKEUTF8,
RPAD, RTRIM, TRIM, UPPER
|
Binning is not supported by Vertica.