When working with Trino, I often find myself needing to inspect the contents of a catalog or specific tables. Knowing how to quickly check the structure and data inside a table is crucial for understanding the data you’re working with. In this post, I’ll walk through how I navigate catalog contents and view table structures or data within the Trino CLI.
Thank me by sharing on Twitter 🙏
Table Contents in Trino
Trino is incredibly powerful for querying distributed data, but managing and exploring the catalogs, schemas, and tables can be tricky if you’re unfamiliar with the commands. Whether you need to check available schemas, understand table structure, or inspect actual data, mastering a few simple commands can make your life easier.
How to View Catalog Contents in Trino
First, let’s say you want to list all the schemas in a specific catalog. For this, Trino provides the SHOW SCHEMAS command, which gives you a clear view of the database organization. Here’s the command I use:
SHOW SCHEMAS FROM <catalog_name>;For example, if your catalog is named sales_data:
SHOW SCHEMAS FROM sales_data;This will give you a list of schemas (or databases) inside the sales_data catalog. Once you have the schema name, you can list the tables in that schema using:
SanDisk 128GB microSDXC Card, Licensed for Nintendo Switch - SDSQXAO-128G-GNCZN
$16.99 (as of December 21, 2024 08:38 GMT +00:00 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)
Anker USB to USB C Cable [2 Pack, 3FT], USB A to USB C Charger Cord for Samsung Galaxy S10 S10+, LG V30, Beats Fit Pro and More (USB 2.0, Black)
$8.99 (as of December 21, 2024 08:38 GMT +00:00 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)
Nexus: A Brief History of Information Networks from the Stone Age to AI
$15.99 (as of December 21, 2024 19:39 GMT +00:00 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)SHOW TABLES FROM <catalog_name>.<schema_name>;If I wanted to view tables in the analytics schema under the sales_data catalog, I’d run:
SHOW TABLES FROM sales_data.analytics;Viewing Table Structure and Data
Now that you have the tables, you probably want to know what each table looks like or contains. To describe a table’s structure—its columns and data types—Trino provides the DESCRIBE command. Here’s how you can use it:
DESCRIBE <catalog_name>.<schema_name>.<table_name>;For example:
DESCRIBE sales_data.analytics.customers;This will return the table structure, including column names and their types.
If you want to see the actual data in the table, you can run a SELECT query:
SELECT * FROM <catalog_name>.<schema_name>.<table_name> LIMIT 10;For instance:
SELECT * FROM sales_data.analytics.customers LIMIT 10;The LIMIT clause is important here. It ensures that you don’t accidentally pull an overwhelming amount of data, especially when working with large tables.
Conclusion
Knowing how to inspect catalog contents and view table structures or data in Trino is essential for efficient data analysis. By using commands like SHOW SCHEMAS, SHOW TABLES, and DESCRIBE, I can easily navigate through catalogs and schemas to get the information I need. If you’re working with data in Trino, these commands will help you get familiar with your dataset quickly and efficiently.
