The following are the functions that the Apache Spark SQL connector supports.
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.
Important: 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.
Date and Time functions
DateDiff, Date_Add, Date_sub, Day, DayOfMonth, From_utc_timestamp, Hour, Minute, Month, Quarter, Second, To_date, To_utc_timestamp, Week, WeekOfYear, Year
Conversion functions
SN
Math functions
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 functions
Note: Statistical functions are not available for calculated columns.
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 functions
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.
Apache Spark SQL temporary views and tables in custom queries
If you are creating a custom query and you want to use data from an Apache Spark SQL temporary table or view, you must refer to those objects using their qualified names, specifying both the name and the location of the object. The qualified names required have the following format:
databaseName.tempViewName
By default, global temporary views are stored in the global_temp database. The database name can vary, and you can see it in the hierarchy of available database tables in Spotfire. To select all columns from a global temporary view named myGlobalTempView, that is stored in the global_temp database:
SELECT * FROM global_temp.myGlobalTempView
Temporary views/tables (listed in Spotfire under ‘Temporary views’ or ‘Temporary tables’) are always located in the #temp database. To select all columns in a temporary view named myTempView:
SELECT * FROM #temp.myTempView
User agent tagging
If the ODBC driver that you use supports the UserAgentEntry option, Spotfire includes the following string as the UserAgentEntry in queries:
TIBCOSpotfire/<ProductVersion>