User Guide Getting Started Help Center Documentation Community Training
New LookML
Old LookML
New LookML
Looker
  
English
Français
Deutsch
日本語
sql_table_name (for Views)

This page refers to the sql_table_name parameter that is part of a view.

sql_table_name can also be used as part of a join, described on this documentation page.

sql_table_name can also be used as part of an Explore, described on this documentation page.

Usage

view: view_name {
  sql_table_name: table_name ;;
}

Hierarchy

sql_table_name

Default Value

The name of the view

Accepts

A table name in your database

Special Rules

  • The table must be accessible within the database connection
  • If the table is in a different schema, the schema must be specified
  • If the table is in a different database, the database and schema must be specified and the other database must be on the same machine and be in the same SQL dialect

Definition

sql_table_name sets the table in your database that will be queried by a view. If you do not add a sql_table_name Looker defaults to the name of the view.

In other words, this:

view: my_name { … }

is logically equivalent to this:

view: my_name { sql_table_name: my_name ;; }

It is quite common to fall back on this default behavior, and not use sql_table_name. However, there are some major situations where it can be useful. First, in some cases you’ll simply want the view to have a different name than the underlying table, such as to clean up a messy table name:

view: orders { sql_table_name: prod_orders_2 ;; }

Additionally, there are cases when a view needs to be based on a database or schema that is not in the database user’s default search path. You can then make references like this:

view: desired_view_name { sql_table_name: database_name.schema_name.table_name ;; }

Federated Joins

Joining two tables from different databases is called a federated join. Federated joins can be performed using Looker if the two databases are on the same SQL dialect, reside on the same physical machine, and are accessible from the same connection in Looker.

Under those conditions, you can create a federated join by specifying the database name of each table in the sql_table_name parameter for that view:

view: view_1 { sql_table_name: database_name.table_1 ;; } view: view_2 { sql_table_name: another_database_name.table_2 ;; }

Then join these views using the sql_on parameter:

explore: view_1 { join: view_2 { sql_on: ${view_1.view_1_ID} = ${view_2.view_2_ID} ;; } }

If you want to join databases that are in different SQL dialects, we recommend replicating one database into the other so that you can pull all data from a single source.

If you want to join across two MySQL databases on separate machines, you can perform a federated join across both database connections by creating a FEDERATED table using CONNECTION. Essentially, you establish a connection to tables on a different server entirely in your MySQL database. See this MySQL documentation to learn more.

Examples

Make the customers view rely on the customer_1 table from the default schema:

view: customers { sql_table_name: customer_1 ;; }

Make the customers view rely on the customer_1 table from the analytics schema:

view: customers { sql_table_name: analytics.customer_1 ;; }

Common Challenges

Tables Referenced By view Must Be Accessible From The Current Connection

views are referenced by an explore or join parameter, which in turn is referenced by a model. The model determines which database connection is used (see connection for model). Database connections themselves are defined in the Admin section of Looker. When you reference a table in the sql_table_name parameter, the table needs to be accessible within the associated connection.

Unfriendly Table Names May Require Square Brackets

Some dialects require that table names that have unsupported characters, or “unfriendly” table names, be enclosed in square brackets. For example, the table name Customers.PaymentInfo includes a period in the table name, which could be interpreted as a table called PaymentInfo in the schema Customers. You can enclose the table name in square brackets to correct this, as follows:

sql_table_name: [Customers.PaymentInfo] ;;

Top