SQL concepts for views

Each view file in your LookML project defines a single view within Looker, which specifies a table to query and which fields — dimensions and measures — from that table will be surfaced in the Looker UI. A view corresponds to either a single table in your database or a single derived table.

In this guide, you'll learn about the following topics:

To learn more about using SQL to define and customize derived tables in LookML using SQL, see SQL concepts for derived tables.

The view

Here is an example of a view file called users.view, which includes definitions for the database table that will be queried along with several dimensions and measures:

view: users {
  sql_table_name: thelook.users ;;

  dimension: id {
    primary_key: yes
    type: number
    sql: ${TABLE}.id ;;
  }

  dimension: age {
    type: number
    sql: ${TABLE}.age ;;
  }
   measure: average_age {
    type: average
    sql: ${age} ;;  }

  dimension_group: created {
    type: time
    timeframes: [raw, time, date, week, month, quarter, year]
    sql: ${TABLE}.created_at ;;
  }

  measure: count {
    type: count
  }
}

The first element of the view definition is the sql_table_name parameter, which specifies the table in your database that will be queried by a view. This value is the only place in the entire model where the table name is defined, because all other references to the view will use the table alias ${TABLE}. If you want to change the database table name, it only needs to be changed in the sql_table_name parameter. There are some things to consider when you reference a database table.

Looker uses the sql_table_name value to write its SQL FROM clause, followed by the view name, which becomes the table alias. The SQL equivalent would look like this:

FROM `thelook`.`users` AS `users`

Looker uses the view's defined dimensions and measures to generate its SQL SELECT clause. Each dimension defines the type of dimension — such as string, number, or Boolean — and a sql LookML parameter that references the dimension within the view, using the table alias. For a dimension called age, see the following example:

  dimension: age {
    type: number
    sql: ${TABLE}.age ;;
  }

When Looker creates the SQL to send to your database, Looker substitutes the alias for the view into the ${TABLE}. For the age dimension from the previous example, Looker would produce a SELECT clause like the following:

SELECT `users`.`age` AS `users.age`

Measures are often aggregations that are performed on dimensions. You specify the dimension alias in a measure's sql expression. For example, a measure that calculates the average of the age dimension may have a sql expression with the alias ${age} in it, as in the following example:

  dimension: age {
    type: number
    sql: ${TABLE}.age ;;
  }

  measure: average_age {
    type: average
    sql: ${age} ;;
  }

If you rename the age dimension, its new alias propagates to any of its dimension alias references.

Customizing a view file

You can customize the SQL expressions of your view file or use Looker's built-in LookML logic to mimic the logic of a SQL expression.

Using a SQL expression

Suppose that you want to divide the age data into four cohorts, with users younger than 18 defined as "Youth", users aged 18-35 as "Young Adult", users aged 36-65 as "Older Adult", and users 65 and older as "Senior". To perform this division, you must define a new dimension — say, dimension: age_cohort — with a sql expression that captures these cohorts. The following LookML dimension definition uses a CASE statement that is appropriate for a MySQL database connection:

dimension: age_cohort {
  type: string
  sql:
    CASE
      WHEN ${age} < 18 THEN 'Youth'
      WHEN ${age} < 35 THEN 'Young Adult'
      WHEN ${age} < 65 THEN 'Older Adult'
      ELSE 'Senior'
    END ;;
}

Now that you have defined your age cohort as a dimension, you can reuse the CASE logic by including the age cohort dimension in your Explore queries.

When you create an Explore query with the age cohort dimension, you can use the SQL tab of the Explore to view the SQL that Looker generates. With the age cohort dimension, the SQL will look something like this:

SELECT
CASE
  WHEN users.age < 18 THEN 'Youth'
  WHEN users.age < 35 THEN 'Young Adult'
  WHEN users.age < 65 THEN 'Older Adult'
  ELSE 'Senior'
END  AS `users.age_cohort`,
AVG(`age`) AS `users.average_age`,
COUNT(*) AS `users.count`
FROM
  `thelook`.`users` AS `users`
GROUP BY
  1
ORDER BY
  2 DESC
LIMIT 500

Using Looker's built-in case logic

You can achieve the same effect as a SQL CASE statement with an expression that is database-independent. The LookML case parameter lets you define the cohort buckets that are made up of when statements that use sql expressions to capture specific conditions and strings for labeling the results.

The following is an example of the same new age_cohort dimension that is written with the case LookML parameter:

  dimension: age_cohort {
    case: {
      when: {
        sql: ${age} < 18 ;;
        label: "Youth"
      }
      when: {
        sql: ${age} < 35 ;;
        label: "Young Adult"
      }
      when: {
        sql: ${age} < 65 ;;
        label: "Middle-aged Adult"
      }
      else: "Older Adult"
    }
  }

At runtime, Looker builds the correct SQL CASE syntax for your database. In addition, Looker builds another expression to handle sorting the groups, so the resulting labels won't just be sorted alphanumerically (unless you define the sort order as alphanumeric). Looker builds a resulting SQL query that resembles the following:

SELECT
CASE
  WHEN users.age < 18  THEN '0'
  WHEN users.age < 35  THEN '1'
  WHEN users.age < 65  THEN '2'
  ELSE '3'
END AS `users.age_cohort__sort_`,
CASE
  WHEN users.age < 18  THEN 'Youth'
  WHEN users.age < 35  THEN 'Young Adult'
  WHEN users.age < 65  THEN 'Older Adult'
  ELSE 'Senior'
END AS `users.age_cohort`,
AVG(`age`) AS `users.average_age`,
COUNT(*) AS `users.count`
FROM
  `thelook`.`users` AS `users`
GROUP BY
  1,
  2
ORDER BY
  1
LIMIT 500

Using Looker's built-in bin or tier logic

Another method for specifying how numeric values should be grouped uses Looker's built-in bin or tier parameter types. The type:bin is used in conjunction with the bins parameter — likewise, the type: tier is used in conjunction with the tiers parameter — to separate a numeric dimension into a set of number ranges. The trade-off is that you cannot define labels for each bin.

The following LookML example uses the bins parameter in a dimension to define the minimum value in each set:

  dimension: age_cohort {
    type: bin
    bins: [18,36,65]
    style: integer
    sql: ${age} ;;
  }

You can use the tiers parameter in a dimension exactly the same way. For example:

  dimension: age_cohort {
    type: tier
    tiers: [18,36,65]
    style: integer
    sql: ${age} ;;
  }

Looker then generates something like the following SQL statement:

SELECT
CASE
  WHEN users.age  < 18 THEN '0'
  WHEN users.age  >= 18 AND users.age  < 36 THEN '1'
  WHEN users.age  >= 36 AND users.age  < 65 THEN '2'
  WHEN users.age  >= 65 THEN '3'
  ELSE '4'
END AS `users.age_cohort__sort_`,
CASE
  WHEN users.age  < 18 THEN 'Below 18'
  WHEN users.age  >= 18 AND users.age  < 36 THEN '18 to 35'
  WHEN users.age  >= 36 AND users.age  < 65 THEN '36 to 64'
  WHEN users.age  >= 65 THEN '65 or Above'
  ELSE 'Undefined'
END AS `users.age_cohort`,
AVG(`age`) AS `users.average_age`,
COUNT(*) AS `users.count`
FROM
  `thelook`.`users` AS `users`
GROUP BY
  1,
  2
ORDER BY
  1
LIMIT 500