Connector for Google BigQuery — Features and settings
You can connect to and access data from Google BigQuery. 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 Google BigQuery.
Page content
Connector features
The following functionality is available when you access data with the connector for Google BigQuery.
| Feature | Supported? |
|---|---|
| Load methods |
|
| Custom queries | Yes |
| Stored procedures | No |
| Custom connection properties | No |
| Single sign-on with identity provider | N/A |
| Authoring in web client | No |
| 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 Google BigQuery.
| Option | Description |
|---|---|
| Billing project | Select the Google Cloud Platform project
that will be paying for queries to BigQuery from the data connection.
Note: The preference setting
ListAllProjects determines if you can see
data from only the selected billing project, or all the data you have access to
in Google BigQuery (in projects that you are a member of). As an administrator,
you can change the preference in the Administration Manager under
Connectors > Google BigQuery >
ListAllProjects.
If
|
| Include data from additional projects | You can use this setting for two purposes:
|
| Enable repeated columns | Determines if you can view and select
repeated columns in connections to the data source.
Working with repeated columns in Spotfire requires that you have a good understanding of your data. For more information about how repeated columns from BigQuery work in Spotfire, see Repeated columns from Google BigQuery. By default, this check box is cleared. Repeated columns are not listed for selection in the Views in Connection dialog. Note: If you want to access Google Analytics data with the
BigQuery connector, you must enable repeated columns in your connection.
|
| Show public data | Determines if you can view and select data
from BigQuery public datasets in connections to the data source.
By default, this check box is cleared. Data from BigQuery public datasets are not listed for selection in the Views in Connection dialog. When you enable public data, loading data tables in the Views in Connection dialog can take a very long time. Tip: As a Spotfire administrator, you can control
if the
Show public data is available to
users. If you do not want users to have the option to select public data, in
Spotfire Administration Manager, set the Google BigQuery preference
ShowPublicDataCheckBox to
False.
|
| Use private endpoints | Choose if you wish to use private endpoints to connect to Google BigQuery. To do this, click on the Private Endpoints tab. There are five private endpoints which can be taken from the preferences, or custom. See Private endpoints for Google BigQuery. |
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 Google BigQuery connector supports.
| Database data type | Spotfire data type |
|---|---|
| BYTES | Binary |
| GEOGRAPHY | Binary |
| BOOL | Boolean/Bool |
| NUMERIC | Currency/Decimal |
| DATE | Date |
| DATETIME | DateTime |
| INT64 | LongInteger/Long |
| FLOAT64 | Real/Double |
| STRING | String |
| TIME | Time |
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 Google BigQuery connector supports.
| Function type | Functions supported |
|---|---|
| Aggregate functions | AVG,
COUNT,
COUNTIF,
LOGICAL_AND,
LOGICAL_OR,
MAX,
MIN,
STRING_AGG,
SUM
|
| Approximate aggregate functions | APPROX_COUNT_DISTINCT
|
| Bit functions | BIT_COUNT
|
| Conversion | STRING
|
| 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
|
| Hash functions | FARM_FINGERPRINT,
MD5,
SHA1,
SHA256,
SHA512
|
| JSON functions | JSON_EXTRACT,
JSON_EXTRACT_SCALAR,
TO_JSON_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 | +,-,
*,
/
|
| Statistical aggregate functions | CORR,COVAR_POP,
COVAR_SAMP,
STDDEV_POP,
STDDEV_SAMP,
STDDEV,
VAR_POP,
VAR_SAMP,
VARIANCE
|
| 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
|
| UUID functions | GENERATE_UUID
|