| title | redirect_from | |
|---|---|---|
Table metadata admin settings |
|
Metabase lets you add and edit metadata to your tables and columns so that you can manage your org's business logic as it changes. Go to the Table Metadata tab in your Admin settings to:
- Set display names and descriptions.
- Hide outdated data.
- Cast a text or number column to a date column.
- Change the style of a filter widget.
- Display a column as a link.
The Table Metadata admin settings only affect the way data's displayed and interpreted in Metabase. None of the settings will change the data in your database.
Click on a table name in the left sidebar to view the table's settings in Metabase.
If you've got more than one database connected to Metabase, click on the database name (for example, "Sample Database") and select another database from the dropdown menu. Once you select a database, the tables in that database will appear in the sidebar.
- Change the display name.
- Add or edit the description.
- Show or hide the table across Metabase.
- View the original schema.
- Edit column (field) settings.
To edit a table's display name in Metabase, click into the box that contains the current table name. Changes will be saved automatically once you click out of the box.
To add a table description, click into the box below the table name. Descriptions are displayed in Metabase's data reference to help people find the right table for their use case.
Queryable tables are visible across all of Metabase.
Hidden tables won't show up in the query builder or data reference. But this is not a security feature: hidden tables can still be used in SQL questions if someone writes SELECT * FROM hidden_table from the SQL editor. To prevent people from writing queries against specific tables, see data permissions.
Tip: To hide all of the tables in a database (say, if you've migrated to a new database), click on the hidden eye icon beside "# queryable tables" in the left sidebar.
To remind yourself of column names and data types as they're stored in your database, click Original schema (below Visibility).
Select a database and click on a table's name in the sidebar to bring up basic column display settings:
- Change the display name
- Add or edit the description
- Show or hide the column across Metabase
- Set a default column order
- Change the column's field type
For extra column settings, click on the gear icon at the right of a column's settings box:
- Cast text or numbers to dates
- Change the filter widget (for example, to a dropdown menu)
- Remap column values (for example, from "5" to "Great")
- Display the column as a link
To change the global display name of a column in Metabase, click on the name of the column. For example, you could display "auth.user" as "User" to make the column more readable. People can use models to give columns a display name that's local to the model.
To add a description, click into the box below the column name. Descriptions are displayed in the data reference to help people interpret the column's values. You should consider adding a description if your column contains:
- abbreviations or codes
- zeroes, nulls, or blank values
- placeholder values, like
9999-99-99
Everywhere: by default, users can see all of the columns in a table.
Only in detail views will hide lengthy text from question results. This setting is applied by default if a column's values have an average length of more than 50 characters. For example, you could use this setting on a column like "Customer Comments" if you already have a column for "Customer Rating".
Do not include columns won't show up in the query builder or data reference. You can set "do not include" on sensitive columns (such as PII) or irrelevant columns. But this visibility option is a simple omit/hide option; it's not a security feature. These columns are still accessible for people with native query privileges; they can write SELECT hidden_column FROM table or SELECT * from table in the SQL editor and they'll be able to view these fields and their values. To prevent people from viewing certain columns, see data sandboxing.
Metabase defaults to the column order defined in your database schema. To reorder the column display order in question results and menus manually, click on the grab bar to the right of each column, and drag the column to a new position.
To sort the columns automatically, click on the sort icon at the top right of the first column's settings box. The sorting options are:
- Database. (Default) The order of columns as they appear in the database.
- Alphabetical. A, B, C... however the alphabet works.
- Custom. You choose the order. Metabase will automatically switch the sort order to "Custom" if you rearrange any of the columns.
- Smart. Metabase chooses for you.
To change the field type of a column, click on the Type dropdown menu in a column's setting box. You can also use the Type dropdown to label a column as an entity key (primary key) or foreign key in Metabase (with no consequence to your database).
If you want Metabase to treat a text or number column as a datetime column:
- Go to Admin settings > Table Metadata.
- Find your database and table.
- Click on the gear icon at the right of a column's settings box.
- Scroll to Cast to a specific data type
- Select a casting option.
Metabase currently supports only casting to a datetime type in Cast to a specific data type. If you need to cast to a different type (like float), you can create a SQL question that casts the data and save it as a model, or create a view directly in your database.
Text to datetime casting options:
- ISO8601->Date
- ISO8601->Datetime
- ISO8601->Time
Numeric to datetime casting options:
- UNIXMicroSeconds->DateTime
- UNIXMilliSeconds->DateTime
- UNIXNanoSeconds->DateTime
- UNIXSeconds->DateTime
Casting is different from setting the field type. For example, say you have a "Created At" column with a string data type in your database. You'll need to cast "Created At" to one of the datetime types above if you want to do things like:
- Create relative date filters, such as "Created At = Last week".
- Use "Created At" with formulas like datetimeAdd.
Casting data types from the Table Metadata admin settings won't affect the original data types in your database.
To change a column's filter widget:
- Go to Admin settings > Table Metadata.
- Find your database and table.
- Click on the gear icon at the right of a column's settings box.
- Scroll to Filtering on this field.
- Select a filter widget option.
The default behavior for the Is filter for the field.
- Search box: Display a search box and suggest autocompletions for values in that column that match the search term(s).
- A list of all values: Display a search box, as well as a list of checkboxes for values in a dropdown menu that people can select as search terms.
- Plain input box: Display a search box, but do NOT suggest autocompletions.
The dropdown filter widget can be finicky, because Metabase needs to run a scan to get the list of values for the dropdown menu.
- Go to Admin settings > Table Metadata.
- Find your database and table.
- Scroll to your column.
- In the column's settings box, set Type to “Category”.
- Set Filtering on this field to “A list of all values".
When you change a default filter to a dropdown filter, you'll trigger a database query that gets the first 1,000 distinct values (ordered ascending) for that column. Metabase will cache the first 100kB of text to display in the dropdown menu. If you have columns with more than 1,000 distinct values, or columns with text-heavy data, we recommend setting Filtering on this field to "Search box" instead.
Say you have a column with the values 1, 2, and 3, and you want to map each number to the values "low", "medium" and "high". This kind of mapping can be done on columns that have numeric or foreign key field types.
- Go to Admin settings > Table Metadata.
- Find your database and table.
- Click gear icon at the right of a column's settings box.
- Scroll to Display values.
- Select "Custom mapping" from the dropdown menu.
- Enter the display values under Mapped values.
- Go to Admin settings > Table Metadata.
- Find your database and table.
- Click gear icon at the right of a column's settings box.
- Scroll to Display values.
- Select "Use foreign key" from the dropdown menu.
- Select a column name from the second dropdown menu.
- Go to Admin settings > Table Metadata.
- Find your database and table.
- Click on the gear icon at the right of a column's settings box.
- Select Formatting from the sidebar.
- From Display as, select Link.
- Optional: set display text under Link text.
- Enter the URL in the Link URL field.
- Optional: create a dynamic URL by adding the column name as a
{% raw %}{{parameter}}{% endraw %}.
For example, if you set the Link URL for an "Adjective" column to:
https://www.google.com/search?q={% raw %}{{adjective}}{% endraw %}
When someone clicks on the value "askew" in the "Adjective" column, they'll be taken to the Google search URL:
https://www.google.com/search?q=askew
To update the values in your filter dropdown menus, refresh or reset the cached values. Cache actions include:
- Re-scan this table or field to run a manual scan for new or updated column values. If possible, re-scan the table during off-peak hours, as scans can slow down your database.
- Discard cached field values to clear cached values and stop them from showing up in your filter widgets.
- Go to Admin settings > Table Metadata.
- Find your database and table.
- Click the gear icon at the top right (below Exit admin).
- Select a cache action.
- Go to Admin settings > Table Metadata.
- Find your database and table.
- Click the gear icon at the right of a column's settings box.
- Scroll to Cached field values.
- Select a cache action.
If Metabase supports JSON unfolding for your database, you can decide whether to unfold JSON into component fields, where each JSON key becomes a column. You can turn this off if performance is slow.
- Go to Admin settings > Table Metadata.
- Find your database and table.
- Click the gear icon at the right of a column's settings box.
- Scroll to Unfold JSON.
- Toggle JSON unfolding.