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 thesql_table_name
(for joins) parameter documentation page.
sql_table_name
can also be used as part of an Explore, described on thesql_table_name
(for Explores) parameter documentation page.
Usage
sql_table_name: table_name ;;
}
Hierarchysql_table_name |
Default ValueThe name of the viewAcceptsA table name in your databaseSpecial Rules
|
Definition
The sql_table_name
parameter specifies the table in your database that will be queried by a view.
If you want to create a view based on a derived table, do not use the
sql_table_name
parameter. Instead use thederived table
parameter, described on thederived_table
documentation page. The Looker IDE will return an error if you have bothsql_table_name
andderived_table
in a view file.
If a view does not contain either the sql_table_name
or the derived_table
parameter, Looker assumes that the view is based on a database table, and that the name of the database table matches the name of the view. In other words, this:
is logically equivalent to this:
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:
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:
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:
Then join these views using the sql_on
parameter:
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:
Make the customers
view rely on the customer_1
table from the analytics
schema:
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 additional formatting
Some dialects require that table names that have unsupported characters, also known as “unfriendly” table names, be enclosed in square brackets, double quotes, or single quotes. 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
. To correct this, if your dialect requires square brackets, you can enclose the table name in square brackets, as follows:
If your dialect requires double quotes or single quotes, you would replace the square brackets for those characters.