Connector for Hortonworks — Features and settings

Page content

Connector features

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


Feature	Supported?
Load methods	Import (in-memory) External (in-database) On-demand
Custom queries	Yes
Stored procedures	No
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 Hortonworks.


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. The default port is 10000. Example with port number: MyDatabaseServer:10011 Note: The Hortonworks connector supports only HiveServer2.
Authentication method	The authentication method to use when logging into the database. Choose from No authentication Kerberos Username Username and password HDInsight on Azure
Realm	[Only available for Kerberos authentication.] Realm in Kerberos is similar to a domain name. The Kerberos realm name is case-sensitive and conventionally uppercase. Leave the field blank if a default realm has been configured for your Kerberos setup. For details and further help, contact your Hortonworks system administrator.
Host FQDN	[Only available for Kerberos authentication.] The Fully Qualified Domain Name (FQDN) is the complete URL or path to a server. For example: `myServer.myDomain.com` To use the Hive server host name as the FQDN, type `_HOST`. For details and further help, contact your Hortonworks system administrator.
Service name	[Only available for Kerberos authentication.] The service name of the Hive server. For example, “hive”. For details and further help, contact your Hortonworks system administrator.
Use secure sockets layer (SSL)	Select to connect using Secure Sockets Layer (SSL).
Use simple authentication and security layer (SASL)	Select to connect using Simple Authentication and Security Layer (SASL).
Connect	Connect to the specified server and populate the list of available databases in the Database drop-down list.
Database	Select the database of interest from the drop-down list.
Unicode SQL character types	If this check box is selected, the Hortonworks ODBC driver will return `SQL_WVARCHAR` for `STRING` and `VARCHAR` columns. For `CHAR` columns it will return `SQL_WCHAR`. If this check box is not selected, the Hortonworks ODBC driver will return `SQL_VARCHAR` for `STRING` and `VARCHAR` columns. For `CHAR` columns it will return `SQL_CHAR`.
Allow self-signed server certificate	Select this check box to allow self-signed certificates from the server.
Allow common name host name mismatch	Select this check box to allow the name of a CA-issued SSL certificate to not match the host name of the Hive server.
HTTP path	If you want to connect to a Hortonworks database via Apache Knox Gateway, specify the partial URL that corresponds to the Hive server. Note: The partial URL is appended to the host and port specified in the server field. For example, to connect to the HTTP address `http://localhost:10002/gateway/default/hive`, enter `localhost:10002` as the server and `/gateway/default/hive` as the HTTP path.
Delegation UID	Optionally, operations against the Hive server can be delegated by the Hortonworks ODBC driver to a specific user, instead of the authenticated user of the connection. Type the name of the user that the Hortonworks ODBC driver will delegate operations to.
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.

Note: For more information about the advanced settings, please refer to the official documentation provided by the driver vendor.

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


Database data type	Spotfire data type
BINARY	Binary
BOOLEAN	Boolean/Bool
DATE	Date
TIMESTAMP	DateTime
INT	Integer/Int
SMALLINT	Integer/Int
TINYINT	Integer/Int
BIGINT	LongInteger/Long
DECIMAL	Real/Double
DOUBLE	Real/Double
FLOAT	SingleReal/Float
STRING	String
VARCHAR	String

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


Function type	Functions supported
Date and Time	`YEAR, QUARTER, MONTH, WEEK, WEEKOFYEAR, DAY, DAYOFMONTH, HOUR, MINUTE, SECOND, DATEDIFF, DATE_ADD`
Conversion	`SN`
Math	`ABS, ACOS, ASIN, ATAN, BIN, CEIL, CEILING, COS, DEGREES, E, EXP, FLOOR, LN, LOG, LOG10, LOG2, NEGATIVE, PI, POW, POWER, POSITIVE, PMOD, RADIANS, RAND, ROUND, SIGN, SIN, SQRT, TAN`
Operators	`%, -, +, *, /`
Statistical	`SUM, AVG, MIN, MAX, COUNT, UNIQUECOUNT, VARIANCE, VAR_POP, VAR_SAMP, STDDEV_POP, STDDEV_SAMP, CORR, COVAR_POP, COVAR_SAMP, PERCENTILE`
Text	`CONCAT, INSTR, LENGTH, LOWER, LPAD, LTRIM, RPAD, RTRIM, TRIM, UPPER, LOCATE, REPEAT, SPACE, REGEXP_REPLACE, SUBSTR, SUBSTRING, REVERSE, FORMAT_NUMBER`

Non-supported functions

The following functions are not supported in this version of the connector.

Date and Time: FROM_UNIXTIME, UNIXTIMESTAMP, TO_DATE, DATE_SUB, FROM_UTC_TIMESTAMP, TO_UTC_TIMESTAMP

Statistical: HISTOGRAM_NUMERIC, COLLECT_SET, COLLECT_LIST

Text:

ASCII, BASE64, CONTEXT_NGRAMS, CONCAT_WS, DECODE, ENCODE, FIND_IN_SET, GET_JSON_OBJECT, IN_FILE, NGRAMS, PARSE_URL, PRINTF, REGEXP_EXTRACT, SENTENCES, SPLIT, STR_TO_MAP, TRANSLATE, UNBASE64

Collection Functions, Type Conversion Functions, Conditional Functions, Miscellaneous Functions and Table-Generating Functions are not supported by this connector.