At times, you may need to get a list of tables from your database. This could be to help with Testing, to see what tables exist before you create a new table or remove an existing one, or some other reason. To analyze your data efficiently, sometimes you need to get information about all the tables present in the database and Snowflake Show Tables Command lets you do that.
Upon a complete walkthrough of this article, you will gain a holistic understanding of Snowflake along with the salient features that it offers. You will also learn about how to use the Snowflake Show Tables Command along with some example queries.
Table of Contents
Prerequisites
- An active Snowflake account.
What is Snowflake?
Image Source
Snowflake is one of the most popular Cloud Data Warehouses that offers a plethora of features without compromising simplicity. It scales automatically, both up and down, to offer the best Performance-to-Cost ratio. The distinguishing feature of Snowflake is that it separates Compute from Storage. This is significant as almost every other Data Warehouse, including Amazon Redshift, combines the two, implying that you must consider the size for your highest workload and then incur the costs associated with it.
Snowflake requires no hardware or software to be Chosen, Installed, Configured, or Managed, making it ideal for organizations that do not want to dedicate resources to the Setup, Maintenance, and Support of In-house Servers. It allows you to store all of your data in a centralized location and size your Compute independently. For example, if you require real-time data loads for complex transformations but only have a few complex queries in your reporting, you can script a massive Snowflake Warehouse for the data load and then scale it back down after it’s finished – all in real-time. This will save you a significant amount of money without jeopardizing your solution goals.
Key Features of Snowflake
Some of the key features of Snowflake are as follows:
- Scalability: The Compute and Storage resources are separated in Snowflakes’ Multi-Cluster Shared Data Architecture. This strategy gives users the ability to scale up resources when large amounts of data are required to be loaded quickly and scale back down when the process is complete without disrupting any kind of operation.
- No Administration Required: It enables businesses to set up and manage a solution without requiring extensive involvement from Database Administrators or IT teams. It does not necessitate the installation of software or the commissioning of hardware.
- Security: Snowflake houses a wide range of security features, from how users access Snowflake to how the data is stored. To restrict access to your account, you can manage Network Policies by whitelisting IP addresses. Snowflake supports a variety of authentication methods, including Two-Factor Authentication and SSO via Federated Authentication.
- Support for Semi-Structured Data: Snowflake’s architecture enables the storage of Structured and Semi-Structured data in the same location by utilizing the VARIANT schema on the Read data type. VARIANT can store both Structured and Semi-structured data. Once the data is loaded, Snowflake automatically parses it, extracts the attributes out of it, and stores it in a Columnar Format.
Hevo Data is a No-code Data Pipeline that offers a fully managed solution to set up Data Integration for 100+ Data Sources (including 40+ Free sources) and will let you directly load data to a Data Warehouse such as Snowflake or the Destination of your choice. It will automate your data flow in minutes without writing any line of code. Its fault-tolerant architecture makes sure that your data is secure and consistent. Hevo provides you with a truly efficient and fully automated solution to manage data in real-time and always have analysis-ready data.
Get Started with Hevo for Free
Let’s look at some of the salient features of Hevo:
- Fully Managed: It requires no management and maintenance as Hevo is a fully automated platform.
- Data Transformation: It provides a simple interface to perfect, modify, and enrich the data you want to transfer.
- Real-Time: Hevo offers real-time data migration. So, your data is always ready for analysis.
- Schema Management: Hevo can automatically detect the schema of the incoming data and map it to the destination schema.
- Connectors: Hevo supports 100+ Integrations to SaaS platforms FTP/SFTP, Files, Databases, BI tools, and Native REST API & Webhooks Connectors. It supports various destinations including Google BigQuery, Amazon Redshift, Snowflake, Firebolt, Data Warehouses; Amazon S3 Data Lakes; Databricks; and MySQL, SQL Server, TokuDB, DynamoDB, PostgreSQL Databases to name a few.
- Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
- Hevo Is Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
- Live Monitoring: Advanced monitoring gives you a one-stop view to watch all the activities that occur within Data Pipelines.
- Live Support: Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
Sign up here for a 14-Day Free Trial!
How to Use the Snowflake Show Tables Command?
Snowflake Show Tables Command displays all the tables for which you have access privileges. It also includes the Dropped Tables that are still within the Time Travel Retention Period. This command can also be used to display a list of tables for the current/specified database or schema, as well as for your entire account.
A) Syntax
SHOW [ TERSE ] TABLES [ HISTORY ] [ LIKE '<pattern>' ]
[ IN { ACCOUNT | DATABASE [ <db_name> ] | SCHEMA [ <schema_name> ] } ]
[ STARTS WITH '<name_string>' ]
[ LIMIT <rows> [ FROM '<name_string>' ] ]
The arguments involved in the Snowflake Show Tables command are as follows:
- Like <pattern>: It is an optional parameter that filters the command output by Object Name. It employs case-insensitive Pattern Matching, with SQL Wildcard characters like ‘%’ and ‘_’.
- IN ACCOUNT | DATABASE [ db_name ] | SCHEMA [ schema_name ]: It is an optional parameter that specifies the command’s scope, and determines whether the command only lists records for the current/specified Database or Schema, or for your entire account. If you use the keyword ACCOUNT, the Snowflake Show Tables command will retrieve records for all Schemas in all databases associated with the current account.
- If you specify a <database_name>, then the Snowflake Show Tables command will retrieve records for all Schemas for that particular database.
- If no <database_name> is specified, then the Snowflake Show Tables command will retrieve all the records from the current database. In case, if there is no current database, then the command will retrieve information for all Databases and Schemas in the account.
- LIMIT rows [ FROM ‘name_string’ ]: The LIMIT Clause is used to specify the maximum number of rows that need to be returned. It should be noted that the actual number of rows returned may be less than the limit specified (e.g. the number of existing objects might be less than the specified limit). The FROM ‘name string’ subclause, which is optional, effectively acts as a cursor for the results. This allows you to retrieve the specified number of rows after the first row whose object name matches the specified string.
B) Usage Notes for using Snowflake Show Tables Command
These are some key pointers that you need to take into account while working with the Snowflake Show Tables Commands:
- If an account (or database or schema) contains a large number of tables, searching the entire account (or table or schema) can consume significant compute resources.
- The value for LIMIT rows cannot be greater than 10000. Make sure to include LIMIT <rows> or query the corresponding view in the Information Schema to view results with more than 10K records.
- You can use the RESULT SCAN function to post-process the output of the Snowflake Show Tables command, which treats the output as a table that can be queried.
C) Example Queries for Snowflake Show Tables Command
Let’s observe the following use cases to understand the working of the Snowflake Show Tables Command:
- Use Case 1: If you want to display all the tables whose name starts with abc in the xyz.public schema. The query for the same would look something like:
show tables like 'abc%' in xyz.public;
- Use Case 2: If you want to display all the tables including Dropped Tables, for which you have access privileges to view in the xyz.public schema. The query for the same would look something like:
show tables history in xyz.public;
This query would display all the tables with their Date of Creation and provide details like Total Number of Rows, Comments, Number of Bytes Used, etc.
Conclusion
In this article, you have learned how to effectively use the Snowflake Show Tables Command to retrieve records associated with all the tables. As your business begins to grow, data is generated at an exponential rate across all of your company’s SaaS applications, Databases, and other sources. To meet this growing storage and computing needs of data, you would require to invest a portion of your Engineering Bandwidth to Integrate data from all sources, Clean & Transform it, and finally load it to a Cloud Data Warehouse such as Snowflake for further Business Analytics. All of these challenges can be efficiently handled by a Cloud-Based ETL tool such as Hevo Data.
Visit our Website to Explore Hevo
Hevo Data, a No-code Data Pipeline provides you with a consistent and reliable solution to manage data transfer between a variety of sources and a wide variety of Desired Destinations such as Snowflake, with a few clicks. Hevo Data with its strong integration with 100+ sources (including 40+ free sources) allows you to not only export data from your desired data sources & load it to the destination of your choice, but also transform & enrich your data to make it analysis-ready so that you can focus on your key business needs and perform insightful analysis using BI tools.
Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also have a look at our unbeatable pricing that will help you choose the right plan for your business needs!
Tell us about your experience of working with the Snowflake Show Tables command! Share your thoughts about the Snowflake Show Tables Command with us in the comments section below.
Rakesh is a Cloud Engineer with a passion for data, software architecture, and writing technical content. He has experience writing articles on various topics related to data integration and infrastructure.