Spotfire® User Guide

Connector for Databricks — Features and settings

You can connect to and access data from Databricks with the data connector for Databricks. 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 Databricks.

Page content

Note: The data connector for Databricks was introduced in Spotfire version 14.6.1. In earlier versions, to access data from Databricks, you would use the connector for Apache Spark SQL. If you have existing data connections to Databricks created with the Apache Spark SQL connector, and you want to use the stand-alone Databricks connector instead, replace the data connections in your analyses, see Replacing a data source.

Connector features

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

Feature Supported?
Load methods
  • Import (in-memory)
  • External (in-database)
  • Push compute (only available for users with Spotfire Industry Pro)
  • On-demand
Custom queries Yes
Stored procedures Yes
Custom connection properties Yes
Single sign-on with identity provider Yes
Authoring in web client Yes
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 Databricks.

Option Description
Server

The hostname of your Databricks workspace.

To add a port number, append it to the hostname, preceded by a colon (:).

Example

my-workspace-id.cloud.databricks.com:443

HTTP Path

The HTTP path of the Databricks cluster or warehouse.

Example

sql/1.0/warehouses/my-resource-id

Authentication method The authentication method to use when logging into Databricks. The following options are available:
  • Personal access token

    Log in with the personal access token of your Databricks workspace user.

  • Identity provider (OAuth2)

    Use an identity provider, such as Okta or Keycloak, to log in. When you open the data connection, a web browser window opens, and you log in with the identity provider's login procedure.

    To learn more about how to configure identity provider authentication, see Single sign-on with identity provider.

Personal access token [Only available for authentication method Personal access token.]

The personal access token for the Databricks account you want to log in with.

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. 

    Scope_1 Scope_2
Use secure sockets layer (SSL) Select this check box to connect using SSL.

By default, SSL is enabled for Databricks connections.

Allow common name host name mismatch [Only available when Use secure sockets layer (SSL) is selected.]

Select this check box if it should be allowed that the common name in the server certificate does not match the host name specified in the Server field.

Allow self-signed server certificate [Only available when Use secure sockets layer (SSL) is selected.]

Select this check box to allow self-signed certificates from the database server.

Use system trust store [Only available when Use secure sockets layer (SSL) is selected.]

Use the trusted certificates in the system trust store on the Spotfire server to validate the server certificate.

Connection timeout (s) The maximum time, in seconds, allowed for a connection to Databricks to be established. The default value is 120 seconds.
Command timeout (s) The maximum time, in seconds, allowed for a command to be executed. The default value is 1800 seconds.
Allow the load method 'Push compute'

Determine whether to allow the load method 'Push compute' when the connection is used. With this load method, the data is kept in the source system, and any calculations and joins are performed by Databricks.

Everybody that use an analysis with push-compute data must review the consent dialog and decide whether they allow any additional data to be uploaded to their user account on Databricks.
Note: Only users with the Spotfire Industry Pro license are allowed to add and configure analyses that use push-compute data.

If this option is cleared, nobody will see the Push compute option in the source view when trying to add data from the connection. Read more about the push compute option in Load methods and Push-compute data.

Also note that data functions that use push compute, should be created specifically for the system of interest. See Using a specific calculation engine for data functions for more details.

Compute cluster The compute cluster where push-compute calculations should be performed. Select the compute cluster from the drop-down list.

Custom properties for Databricks connection data sources

The following is the default list of driver settings that are allowed as custom properties in Databricks connection data sources. To learn how to change the allowed custom properties, see Controlling what properties are allowed.

Default allowed custom properties 

ADUserNameCase, AOSS_AuthMech, AOSS_CheckCertRevocation, AOSS_Min_TLS, AOSS_PWD, AOSS_TrustedCerts,
AOSS_UID, AOSS_UseSystemTrustStore, AsyncExecPollInterval, AutoReconnect, BinaryColumnLength, 
Canonicalization, CheckCertRevocation, ClientCert, ClientPrivateKey, ClientPrivateKeyPassword, 
ClusterAutostartRetry, ClusterAutostartRetryTimeout, DecimalColumnScale, DefaultStringColumnLength, 
DelegateKrbCreds, DelegationUID, DriverConfigTakePrecedence, EnableAsyncExec, EnablePKFK, 
EnableQueryResultDownload, EnableStragglerDownloadMitigation, EnableSynchronousDownloadFallback,
FastSQLPrepare, ForceSynchronousExec, HTTPAuthCookies, InvalidSessionAutoRecover, LCaseSspKeyName,
MaximumStragglersPerQuery, Min_TLS, ProxyHost, ProxyPort, ProxyPWD, ProxyUID, QueryTimeoutOverride,
RateLimitRetry, RateLimitRetryTimeout, RowsFetchedPerBlock, ServiceDiscoveryMode, ShowSystemTable, 
SocketTimeout, StragglerDownloadMultiplier, StragglerDownloadPadding, StragglerDownloadQuantile, 
ThrowOnUnsupportedPkFkRestriction, TrustedCerts, TwoWaySSL, UseNativeQuery, UseOnlySSPI, UseProxy, 
UseUnicodeSqlCharacterTypes

Supported data types when using data from the connector in Spotfire

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

Database data type Spotfire data type
BINARY Binary
BOOLEAN Boolean
DATE Date
NUMERIC LongInteger
TIMESTAMP DateTime
TINYINT Integer
SMALLINT Integer
INT Integer
BIGINT LongInteger
DOUBLE Real
FLOAT SingleReal
STRING String
DECIMAL (precision (p), scale (s)) When p = 0 and s = 0: Currency

When p <= 9 and s = 0: Integer

When p <=18 and s = 0: LongInteger

When p <= 15: Real

Else: Currency
Note: DECIMAL columns from temporary tables/views are always mapped to the Spotfire data type Currency, because their precision and scale (p, s) are specified as unlimited (0, 0).

Supported data types when using push compute upload from Spotfire

When you are using the push compute load method to upload data from Spotfire to an external system, the system must map the data types in Spotfire to data types in the data source. The following are the data types that the Databricks connector supports.

Spotfire data type Database data type
Boolean BOOLEAN
Date DATE
DateTime TIMESTAMP
Integer INT
LongInteger BIGINT
Real DOUBLE
SingleReal FLOAT
String 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 Databricks connector supports.

Function type Functions supported
Date and Time DateDiff, Date_Add, Date_sub, Day, DayOfMonth, From_utc_timestamp, Hour, Minute, Month, Quarter, Second, To_date, To_utc_timestamp, Week, WeekOfYear, Year
Conversion SN
Math Abs, ACos, ASin, Atan, Bin, Ceil, Ceiling, Conv, Cos, Degrees, E, Exp, Floor, Hex, Ln, Log, Log2, Log10, Negative, Pi, Pmod, Positive, Pow, Power, Radians, Rand, Round, Sign, Sin, Sqrt, Tan
Operators %, +, -, *, /
Statistical Avg, Bit_And, Bit_Or, Bool_And, Bool_Or, Corr, Count, Covar_pop, Covar_samp, Max, Min, Percentile, StdDev_Pop, StdDev_Samp, Sum, UniqueCount, Variance, Var_Pop, Var_Samp
Text ASCII, Concat, Concat_ws, Find_in_set, Get_json_object, Instr, Length, Locate, Lower, Lcase, LPad, LTrim, Parse_url, Regexp_extract, Regexp_replace, Repeat, Reverse, RPad, Rtrim, Space, Translate, Trim, Ucase, Upper
Other supported functionality
  • Temporary view / Temporary tables
  • Global temporary views
Note: Binning is not supported by this connector.
Copyright © Cloud Software Group, Inc. All rights reserved.