This page refers to the
sql_table_nameparameter that is part of an Explore.
sql_table_namecan also be used as part of a view, described on thesql_table_name(for views) parameter documentation page.
sql_table_namecan also be used as part of a join, described on thesql_table_name(for joins) parameter documentation page.
Usage
explore: explore_name {
sql_table_name: table_name ;;
}
sql_table_name |
Default Value
The database table on which the Explore's view is based
Accepts
The name of a database table
Special Rules
|
Definition
sql_table_name sets the table in your database that will be queried by the Explore. Typically the view for which an Explore is named defines the table that will be queried. However, in cases where multiple tables have the same structure, the same view can be used to describe all of them. sql_table_name lets you specify which of those tables should be used.
Scoping table names
When you reference a table using the table name alone, Looker will use the default search path (the database and schema) that your Looker admin has configured in the settings for the database connection. See the Things to consider section of this page for more information.
If you need to reference a table in a different database and schema that is not in the database user's default search path, you can scope the table name using the <database_name>.<schema_name>.<table_name> format to point to another database or schema:
- To reference a table from a different schema, use
<schema_name>.<table_name>, as shown in the example on this page. - To reference a table from a different database, use the full
<database_name>.<schema_name>.<table_name>.
For a Google BigQuery connection, you can reference a table in a different project and dataset by scoping the table name using the format <project_name>.<dataset_name>.<table_name>. See the Google BigQuery connection documentation page for additional information.
Examples
This example makes the customers Explore rely on the customer_1 table from the default database/schema:
explore: customers {
sql_table_name: customer_1 ;;
}
This example makes the customers Explore rely on the customer_1 table from the analytics schema by scoping the table name to include the schema name (using the format <schema_name>.<table_name>):
explore: customers {
sql_table_name: analytics.customer_1 ;;
}
Things to consider
Tables referenced by sql_table_name must be accessible from the current connection
When the sql_table_name parameter is used within an explore object, that explore object is in turn included in a model object. (The Hierarchy on this page shows this relationship chain.) The model object has a database connection defined in it. When you reference a database table in the sql_table_name parameter, the table needs to be accessible within the associated connection specified in the model file.
The default database and schema (or, for Google BigQuery, the billing project and dataset) are defined by your Looker admin when they create the Looker connection to your database for the database connection.
To name a view differently than the underlying table, apply sql_table_name at the view level
Using sql_table_name at the explore level, as described on this page, is not very common. It should only be used when the same view can describe multiple tables.
The more typical method is to use sql_table_name at the view level when you want to name a view differently than the underlying table name.
In other words, instead of this:
Model File
explore: customers {
sql_table_name: customer_1 ;;
}
Do this:
Model File
explore: customers { ... }
View File
view: customers {
sql_table_name: customer_1 ;;
}