sql

LookML
Version

On this Page
Docs Menu

Go to Join Parameter List

sql should be avoided as of release 3.12

Use a combination of sql_on, foreign_key, type, and/or sql_table_name instead, as described below.

This page refers to the sql parameter that is part of a join

sql can also be used as part of a dimension, described here

sql can also be used as part of a measure, described here

Definition

sql allows you to write the raw SQL that will join a view to an explore. It is currently used almost exclusively to execute RIGHT JOIN, which is not supported by the type parameter. Otherwise, there are better parameters to use to execute joins.

Previous to release 3.10, sql was the only method available to execute a join that was not a LEFT JOIN. For example, before 3.10 you may have created an INNER JOIN between an event explore and a member view like this:

- explore: event joins: - join: member sql: | INNER JOIN member ON member.id = event.member_id

As of release 3.10 the join should be executed with sql_on or foreign_key instead, while the type of join should be defined using type as follows:

# Option 1 Using sql_on - explore: event joins: - join: member sql_on: ${member.id} = ${event.member_id} type: inner   # Option 2 Using foreign_key - explore: event joins: - join: member foreign_key: member_id type: inner

The second common use case for sql was to join tables from two different database, which is called a “Federated Join”. For example, you might have written this LookML:

# Model File - explore: view_1 joins: - join: view_2 sql: | LEFT JOIN other_db.other_schema.view_2 AS view_2 ON view_1.something = view_2.something

This can now be achieved by using sql_table_name instead, as follows:

# Model File - explore: view_1 joins: - join: view_2 sql_on: ${view_1.something} = ${view_2.something} type: left_outer   # View File - view: view_2 sql_table_name: other_db.other_schema.view_2

Still have questions?
Go to Discourse - or - Email Support
Top