HOW TO CREATE, RENAME, TRUNCATE, DUPLICATE AND DROP A TABLE IN SNOWFLAKE?
WHAT IS A TABLE IN SNOWFLAKE ?
A table (sometimes called a database) in Snowflake is a server-based JSON document containing structured data, typically with one or more columns, or attributes, of type STRING and type INTEGER. The final attribute of each table, at all levels of the hierarchy, is a nested table which contains metadata about the table: it’s name and location, as well as any table constraints that have been defined for it.
The purpose of this article is to explain how to create and manage tables in Snowflake.
Snowflake tables may contain any number of columns, and each column can be defined as either STRING or INTEGER. Tables are the lowest level building blocks in Snowflake; they cannot be subdivided into sub-tables. Tables are the primary unit of organization in Snowflake. They provide a mechanism for organizing your data, and they also serve as the basis for partitioning your data across multiple physical partitions. The table is the fundamental data structure in Snowflake.
It’s a collection of records (rows) and each record contains one or more attributes. Each attribute has a name and type, which can be:
– String (limited to 255 characters)
– Integer (63 bits)
– Boolean (True or False)
– DateTime data types are also supported
A table can be created, renamed, truncated and dropped using the Snowflake console. This article describes how to perform these actions in detail. It also describes how to create a new table from an existing one.
HOW TO CREATE A TABLE IN SNOWFLAKE
The first step in creating a table is to connect to your Snowflake account. You can do this by clicking Connect to Snowflake on the sidebar of the console and entering your username, password and security token.
Table Partitions: A table can be partitioned into multiple partitions. Each partition contains a subset of the total number of rows in the table. When you create a table, you specify its number of partitions (defaults to one). You can also add more partitions later by using the Snowflake console or API.
Partitions are useful because they allow you to distribute your data across multiple tables. For example, if you have a table with 100 rows and 10 columns, then each partition will contain 10 rows and 1 column. Partitions can also be used as an additional level of security for sensitive data; each partition contains different values for the same column name.
Columns: A column is a single field in the table. Each partition of a table contains its own set of columns. For example, if you have a table with two partitions and each partition has three columns, then this table will contain six columns total: two per partition.
When you create a table, Snowflake assigns each partition a unique name. Partition names must be between 1 and 128 characters long and can contain only letters, numbers or underscores. You can change these names later if necessary. The number of partitions that are created when you create a table depends on the size of the data being loaded into it. For example, if your query returns 100 rows but your data file is 1 GB in size, then Snowflake creates one partition containing 100 rows instead of 10 (which would be more efficient)
CREATE A TABLE IN SNOWFLAKE
Example for creating a table in the Snowflake
Create table users1 (
id1 integer default id1_seq.nextval,
name1 varchar(1000),
preferences string,
created_at timestamp
)
In the parenthesis column of the CREATE TABLE statement, enter the name of your table. You can use letters and numbers but cannot include spaces or any other special characters like underscores or dashes.
The id1 column is a primary key and must be unique, which means that each record in your table must have a different value for this column. The id1_seq.nextval function call generates the next sequential number to use as an identifier. This will ensure that no two records have the same ID number.
You can define “default values” and “not null constraints” in the Table:
create table users1 (
Id1 integer default id1_seq.nextval,
name1 varchar(1000) not null,
active boolean default true;
);
The default value is the value that will be assigned to a column if no other value is provided when inserting a new record into the table.
The not null constraint ensures that all values in this column are non-blank, which will ensure that you don’t accidentally insert an empty record into the database with this field left blank.
The previous example defines a table called users1 that has two columns: id1 and name1. The id1 column is an integer data type with a default value of the next sequential number generated by the database engine, which will be assigned to any new records added to this table.
This will create a table with two columns. The first column, id1, is an integer data type and cannot be null. The second column, name1, is a varchar(1000) data type (up to 1000 characters long) and can be null.
You can also create temporary tables Which allows you to break your analysis into tiny pieces.
Create temporary table1 active_users (
id1 integer default id_seq.nextval1,
name1 varchar(1000) not null,
active boolean default false
);
Temporary tables are great for when you want to quickly check out some data and then throw it away. Temporary tables are automatically dropped when the session ends.
Temporary tables are always temporary. They’re automatically deleted when the session ends or when you drop them. To create permanent tables that persist for the duration of the database, use the following syntax:
create table1 active_users (id1 integer not null primary key autoincrement,
Snowflakes help us to create transient tables which are used to store temporary data that you don’t want to persist when the session terminates.
you want to quickly check out some data and then throw it away and are automatically deleted when the session ends or when you drop them.
You can create a transient table using either of the following syntaxes:
Create transient table inactive_users (
id1 integer default id_seq.nextval,
name1 varchar(100) not null,
active boolean default false
);
HOW TO RENAME A TABLE IN SNOWFLAKE
In Snowflake, you can rename a table by using the ALTER TABLE statement. You can use the ALTER TABLE command to rename a table, as shown in the following example:
To do so, use the following syntax:
Syntax:
alter_table old_table_name1 rename to new_table_name1;
alter_table sessions_db2 rename to sessions_db_3;
When you rename a table in Snowflake, the following things happen:
1. The new name is used to identify the table and all of its columns in subsequent queries.
2. All existing data stored in the old table is automatically moved to the new table.
3. All indexes associated with the old table are renamed as well, so they continue to be functional after renaming tables in your database cluster.
4. All tables in your database cluster that reference the old table name are automatically updated to reference the new one.
For example, if you have a view or materialized view that references the old table name, those views are automatically updated with the new name.
HOW TO TRUNCATE A TABLE IN SNOWFLAKE
- TRUNCATE TABLE can be used to remove all rows from a table.
- You cannot use the DELETE statement to delete all rows from a table; instead, use DROP TABLE with CASCADE clause or TRUNCATE TABLE with no clauses.
- If you have indexes on your database cluster and want to truncate a table in Snowflake, then you must drop any indexes first by using DROP INDEX before truncating the table itself.
- Snowflake has a built-in TRUNCATE TABLE command that you can use to completely remove all data in a table, without affecting any indexes. The syntax is identical to the SQL standard, with one exception: it requires no WHERE clause.
- If you have indexes on your database cluster and want to truncate a table in Snowflake, then you must drop any indexes first by using DROP INDEX before truncating the table itself.
We will use this command to empty the data from the table
SYNTAX
The syntax for using the TRUNCATE TABLE command is very simple:
truncate table name
truncate users1;
If the table has an Auto Increment column , the truncate command will reset the value of the Auto Increment column to 0. If you have a primary key on your table, then the truncate command will drop all rows from the table.
HOW TO DUPLICATE A TABLE IN SNOWFLAKE
In order to duplicate a table in Snowflake, we need to make sure that the table has been created in Snowflake and is accessible.
If it’s not, then we will need to create it first. If the target schema exists, then we should be able to use the “copy clone” command. We can use the “copy clone” command to create a new table with the same schema and data as an existing table.
We have various methods to duplicate a table in snowflake
Copy both the data and table structure include:
Procedure 1:
create table sessions1_copy clone sessions1;
Procedure 2:
create table sessions1_copy as
select *
From sessions1;
The above step will create a new table with the same schema and data as an existing one. The next step is to rename the newly created table to its desired name.
Copy only specific columns into the fresh table along with the specific data set:
Create table sessions1_dm_2_copy as
select
id1,
start_date1,
end_date1
from sessions_dm_1
where category = 2;
The above syntax describes the query to create a new table with the same schema and data as an existing one. The next step is to rename the newly created table to its desired name. Copy only specific columns into the fresh table along with the specific data set
Copy only specific columns from multiple tables into the latest table with specific data set:
create table users1_sessions12_rpt as
select*
u1.name,
s.start_date1 as session_start_date,
s.end_date1 as session.end_date1,
from sessions1 s
left join user_sessions us on s.id1 = us. session.id1
left join users_1 u on us. user_id1 = u.id1
where u1.active = true;
The above syntax describes a SQL query that selects specific columns from multiple tables and merges them into a single table. The syntax shows how to select only certain columns from multiple tables and merge them into a single table in Oracle.
Copy only table structure, not data
create table users1_copy like users1;
The above syntax describes how to copy only the table structure, not data. The syntax describes how to copy only the table structure.
HOW TO DROP A TABLE IN SNOWFLAKE
If we want to delete a table permanently then we can use DROP TABLE statement.
In Snowflake, Dropping the Table is an easy process
Drop table user1;
The above syntax describes how to deleta a table permanently using “Drop table”. If we want to undo the command we can use “Undrop table ” command :
undrop table users;
Using the above command the data deleted can be restored easily.
Conclusion
Table is a very important data structure in any database system. In this blog post, we have discussed how to create, rename, truncate, duplicate and drop tables in Snowflake.
In Snowflake, tables will enable us to store the data. Here in the Blog we have learnt how to create different kinds of tables like temporary tables, transient tables, etc.
We also discussed briefly about How to delete the table using Truncate Command.
We also learned how to rename the table using the Command “ALTER TABLE” and how to drop the table using the “DROP TABLE” command. If we want to undo the command we can use “Undrop table “
This article was an overview of how to work with tables in Snowflake. We saw some basic syntax and examples of how to create, alter, drop and copy the table. If you have any questions or comments then feel free to comment below.