Database Almanac is a very useful tool that:
- allows to select the datasource for the report/query
- displays the schemas, tables and columns in the selected database
- suggests queries to run for tables and columns to help quickly explore the data
Selecting datasource
If you have more than 1 datasource defined, the dropdown at the top of the almanac is used to select the one to use for this report. By selecting a datasource, the bottom part of the almanac changes to display the database schema.
Selecting schema
If the selected database has more than one top-level schemas, a second dropdown appears that allows to switch the display of the tables/columns for each of the schemas.
Tables
The top level of the almanac displays the list of tables with approximate row count for each of the tables.
The row counts are based on the database internal planner data and may not be exact depending on the specific database behavior, the VACUUM scheduling and permissions of the user used to connect to the database.
Table details
By selecting a table, the almanac will switch to display the table's columns and each column type.
Suggested queries
The Almanac has a very handy tool - Suggested queries - that allows to quickly explore data without having to type SQL. You can use this to get an overview of a new unfamiliar dataset, or quickly look up values of tables or columns to use as a reference when writing a complex query.
Suggested queries for tables
At the top of the table details, several suggested queries are available. By clicking the box, a query is run and the results would be displayed as usual. If the SQL query editor is empty, the query will be displayed there. If you previously entered your own query, the suggested query would not replace it, but you can do so by clicking the "Replace Query" link next to the Run button if you need to.
The suggested queries for tables are:
- Quick Overview - shows 10 rows of data from the table
- Count - runs an exact count query
Suggested queries for columns
By selecting one of the columns, some more suggested queries appear, depending on the type of the column. Some examples are:
- Count over time trends for date/timestamp columns
- Value statistics
- Distinct values of the column
- Top 10 values by count