Repeated Columns from Google BigQuery

Preparations

To enable repeated columns in your connection, select Enable repeated columns when you create the connection.

If you want to access Google Analytics data with the BigQuery connector, you must enable repeated columns in your connection.

Working with repeated columns

In Spotfire, the names of repeated columns from Google BigQuery are shown inside brackets []:

[column_name]

A repeated column is a column that can contain multiple values per row. For example, the column [Cities lived] in the data table below lists every city that the person has lived in. It could be just one city, or it could be many different places.

In Spotfire, data tables with repeated columns are flattened. This means that the repeated columns with many values have to be smeared and are represented by multiple rows. The result is that you get a data table with more rows.

If you have many repeated columns, or repeated columns within nested columns, the resulting data table can become very large.

Repeated columns and external (in-database) data tables

It is especially important that you keep this in mind when you work with external data tables (in-database). With external data tables, you might see a varying number of rows in a visualization depending on the columns in the visualization. For example, if you create a table visualization of the data from above, but exclude the repeated column [Cities lived], the visualization will only have three rows. Compare this to the same visualization, where the column [Cities lived] is included:

The reason for this behavior is that Spotfire only queries the external database for the data that is relevant in the visualization. If the repeated column [Cities lived] is not included in the visualization, Spotfire does not include it in the query. Then the resulting data table does not have to be flattened, because it does not contain the repeated column.