This topic lists information about what functions you can use in Spotfire, for example for writing custom expressions and adding calculated columns, when you work with in-database data tables from Google BigQuery.
Aggregate functions
AVG, COUNT, COUNTIF, LOGICAL_AND, LOGICAL_OR, MAX, MIN, STRING_AGG, SUM
Approximate aggregate functions
APPROX_COUNT_DISTINCT
Statistical aggregate functions
CORR,COVAR_POP, COVAR_SAMP, STDDEV_POP, STDDEV_SAMP, STDDEV, VAR_POP, VAR_SAMP, VARIANCE
Conversion functions
STRING
Mathematical functions
ABS, SIGN, IS_INF, IS_NAN, IEEE_DIVIDE, RAND, SQRT, POW, POWER, EXP, LN, LOG, LOG10, GREATEST, LEAST, DIV, SAFE_DIVIDE, MOD, ROUND, TRUNC, CEIL, CEILING, FLOOR, COS, COSH, ACOS, ACOSH, SIN, SINH, ASIN, ASINH, TAN, TANH, ATAN, ATANH, ATAN2
Operators
+, -, *, /
String functions
BYTE_LENGTH, CHAR_LENGTH, CHARACTER_LENGTH, CONCAT, ENDS_WITH, FROM_BASE64, FROM_BASE32, FROM_HEX, LENGTH, LPAD, LOWER, LTRIM, NORMALIZE, NORMALIZE_AND_CASEFOLD, REGEXP_CONTAINS, REGEXP_EXTRACT, REGEXP_REPLACE, REPLACE, REPEAT, REVERSE, RPAD, RTRIM, SAFE_CONVERT_BYTES_TO_STRING, STARTS_WITH, SUBSTR, TO_BASE32, TO_BASE64, TO_HEX, TRIM
Time functions
TIME, TIME_ADD, TIME_SUB, TIME_DIFF, TIME_TRUNC, FORMAT_TIME, PARSE_TIME
Timestamp functions
EXTRACT, STRING, TIMESTAMP, TIMESTAMP_ADD, TIMESTAMP_SUB, TIMESTAMP_DIFF, TIMESTAMP_TRUNC, FORMAT_TIMESTAMP, TIMESTAMP_SECONDS, TIMESTAMP_MILLIS, TIMESTAMP_MICROS, UNIX_SECONDS, UNIX_MILLIS, UNIX_MICROS
Date functions
EXTRACT, DATE, DATE_ADD, DATE_SUB, DATE_DIFF, DATE_TRUNC, DATE_FROM_UNIX_DATE, FORMAT_DATE, PARSE_DATE, UNIX_DATE
DateTime functions
DATETIME, DATETIME_ADD, DATETIME_SUB, DATETIME_DIFF, DATETIME_TRUNC, FORMAT_DATETIME, PARSE_DATETIME
Geography functions
ST_GEOGPOINT, ST_MAKELINE, ST_MAKEPOLYGON, ST_GEOGFROMGEOJSON, ST_GEOGFROMTEXT, ST_GEOGFROMWKB, ST_ASGEOJSON, ST_ASTEXT, ST_ASBINARY, ST_BOUNDARY, ST_CENTROID, ST_CLOSESTPOINT, ST_DIFFERENCE, ST_INTERSECTION, ST_SNAPTOGRID, ST_UNION, ST_X, ST_Y, ST_CONTAINS, ST_COVEREDBY, ST_COVERS, ST_DISJOINT, ST_DWITHIN, ST_EQUALS, ST_INTERSECTS, ST_INTERSECTSBOX, ST_TOUCHES, ST_WITHIN, ST_ISEMPTY, ST_ISCOLLECTION, ST_DIMENSION, ST_NUMPOINTS, ST_AREA, ST_DISTANCE, ST_LENGTH, ST_MAXDISTANCE, ST_PERIMETER, ST_UNION_AGG
Bit functions
BIT_COUNT
Hash functions
FARM_FINGERPRINT, MD5, SHA1, SHA256, SHA512
JSON functions
JSON_EXTRACT, JSON_EXTRACT_SCALAR, TO_JSON_STRING
UUID functions
GENERATE_UUID
See also:
Accessing Data from Google BigQuery
Details on Google BigQuery Connection
Repeated Columns from Google BigQuery
Enabling Google BigQuery in Spotfire web clients