Notification

This article is about Looker Studio. For Looker documentation, visit https://cloud.google.com/looker/docs/intro.

NATIVE_DIMENSION

Returns the result of a Google BigQuery SQL expression as evaluated by the underlying dataset.

Native functions are disabled by default. See the Enabling native functions section in this article for instructions on how to enable native functions.

The NATIVE_DIMENSION function lets you write SQL directly into a Looker Studio field. This is useful for functions that are not included in the function list, but that your database supports.

In this article:

Enabling native functions

Native functions are disabled by default and are managed at the data source level.

The NATIVE_DIMENSION function can be used only with a Google BigQuery data source for which the BigQuery native integration in Looker Studio is enabled.

If you are an owner of a Google BigQuery data source, you can enable native functions for the data source by following these steps:

  1. Enable the BigQuery native integration in Looker Studio.
  2. In Looker Studio, edit the data source.
  3. In the top right of the data source editor, click Native functions.
  4. In the Native functions dialog, select On.
  5. Click Save.

If you disable native functions, no users can create fields using native functions. Additionally, any existing charts that reference fields with native functions will display an error.

Sample usage

NATIVE_DIMENSION("JSON_VALUE('{"name": "Dana"}','$.name')","STRING")

Syntax

NATIVE_DIMENSION(expression, type)

Parameters

expression - An expression to be evaluated by the underlying dataset. This can be any valid Google BigQuery SQL that would go into a single column of a SELECT statement. The expression cannot include any aggregations.

type - A data type literal. See the following section for the types that this function supports.

NATIVE_DIMENSION types

You can select from the following types:

Type Expected data Report examples
STRING
Zero or more letters, numbers, characters, or symbols.

A1B2C3

BOOL

A logical value.

true

false

DOUBLE A floating point number. 1,234.56
INT64 An integer. 1,234
DATE A calendar date.

2020-09-21

DATETIME A calendar date with time. 2020-09-21 12:35 PM
GEOGRAPHY

A collection of points and polygons that correspond to the BigQuery geography data type.

Only the BigQuery functions POINT and POLYGON are supported.

POINT(32 210)

POLYGON((0 0, 2 2, 2 0, 0 0), (2 2, 3 4, 2 4, 2 2))

If your expression returns a timestamp data type, such as BigQuery's TIMESTAMP type, use the CAST function to cast it to a DATETIME type. For example:

NATIVE_DIMENSION("CAST({expression} AS DATETIME)", "DATETIME")

Looker Studio will return an error if your expression returns an unsupported data type, such as ARRAY, STRUCT, or JSON.

NATIVE_DIMENSION examples

JSON functions in SQL

Assume that your data includes a users_ages_json field whose values are formatted as JSON payloads. An example row value of this field might look like the following example:

{"name": "Jakob", "age": "26"}

Use the NATIVE_DIMENSION function to retrieve the age from users_ages_json, using BigQuery's JSON_VALUE function:

NATIVE_DIMENSION("JSON_VALUE(user_ages_json, '$.age')","INT64")

For this example row value, the NATIVE_DIMENSION function would return the age 26 as an integer.

Limits of NATIVE_DIMENSION

Supported data sources

The NATIVE_DIMENSION function can be used only with a Google BigQuery data source for which the BigQuery native integration in Looker Studio is enabled.

Who can use native functions

If native functions are enabled, owners and editors of the data source can create fields with native functions on the data source.

Viewers of the data source can use fields with native functions in charts. However, viewers cannot create new fields using native functions, nor can they edit any fields using native functions.

Aggregate fields

You cannot include any aggregated fields in the expression parameter.

Window functions

You cannot include any window functions in the expression parameter.

Blends

You cannot create a field with the NATIVE_DIMENSION function on a blended data source.

However, you can create a field with this function prior to blending and then use that field in a blend.

TIMESTAMP data types

If your expression returns a timestamp data type, such as BigQuery's TIMESTAMP type, use the CAST function to cast it to a DATETIME type. For example:

NATIVE_DIMENSION("CAST({expression} AS DATETIME)", "DATETIME")

Related resources

Was this helpful?

How can we improve it?
true
What's new in Looker Studio?

Learn about new features and recent changes. Looker Studio release notes live on Google Cloud.

Search
Clear search
Close search
Google apps
Main menu