HOW TO DELETE, INSERT, UPDATE DATA IN SNOWFLAKE
SNOWFLAKE
Snowflake is a cloud-based data warehousing platform that allows organizations to store, manage, and analyze their data using a scalable and flexible architecture.
Snowflake's architecture is designed for the cloud, and it separates compute and storage, allowing organizations to scale their computing resources and storage independently as their data needs grow. This architecture enables Snowflake to offer fast and efficient query processing, even for large-scale data sets.
Overall, Snowflake is a powerful and flexible data warehousing platform that enables organizations to store, manage, and analyze their data with ease and efficiency.
Data in Snowflake
In Snowflake, data refers to any information that is stored within the Snowflake cloud data warehouse. This data can come from a variety of sources, including transactional systems, customer relationship management (CRM) software, or any other system that generates data.
Snowflake stores data in a structured format, meaning that the data is organized into tables and columns, similar to a traditional relational database. However, Snowflake also supports semi-structured data such as JSON, Avro, ORC, and Parquet files, allowing for more flexibility in data storage.
How to Delete Data in Snowflake
Snowflake’s data retention policies are flexible, with options for keeping the data for a day or forever.
If you need to delete your data from Snowflake, follow these steps:
1) Navigate to your account settings page.
2) Click on Data Retention under “General Settings."
3) Select the checkbox next to each table you want deleted and click "Delete selected tables."
Data can be deleted from Snowflake in two ways: by deleting the table or by removing data from the table. Deleting a table is permanent and cannot be undone, so it’s important to make sure you have all of your data before doing this step. You can delete a table by clicking on its name under Your Data > Tables in the left-hand navigation menu. From there, click on Delete next to the table you want to remove Snowflake allows you to delete data from tables in different ways.
You can use the Snowflake web interface or the command line tool to delete data, depending on what works best for your organization. The Snowflake data management platform is designed to be flexible, with a wide range of use cases. As a result, there are many different ways to delete data from it. You can delete an entire table, or just specific rows and columns within that table.
For deleting the rows from the Snowflake table, we utilize the “DELETE” statement:
Example:
delete from sessions1 where id =8;
Using the “WHERE” clause is optional, but we need it to delete a particular from the data.
delete from sessions1;
The “DELETE” statement is a very powerful tool, but it can be dangerous if you don’t know what you are doing. When used incorrectly, it can cause lost data and corrupted tables. For example, if we were to delete all rows from the sessions1 table without specifying any conditions in the WHERE clause (see above), then all of our data would be gone forever!
HOW TO INSERT DATA IN SNOWFLAKE
Inserting data into a Snowflake database is similar to inserting data into any other relational database. We use the “INSERT” statement, passing in the columns we want to add and their corresponding values.
The “INSERT” statement is used to insert data into the database. It has two main components: The table that we want to add data to (called the target table). We specify the columns in which we want to add our new data. This can be done by referencing a list of values directly or using an expression that calculates what should go into each column.
The data that we want to add to the table. In our example, we are adding a new row of data into the sessions1 table by inserting a value of “2019” into column 1 (which is the date).
In snowflake data insertion is not a complicated process . It’s a simple process of defining the table on which you want to insert data, then providing a list of values or an expression that defines what should be inserted into each column. The advantage of using statements like “INSERT'' is that they are easy to write, understand and maintain. They also work with all types of databases (including Oracle and Microsoft SQL Server).
The important aspect is we have to pass every value in order. For example, if we have 10 columns, we have to specify 10 columns. For example, if we want to insert a new record into the table, we have to specify the values for each column. This can be done by using an INSERT statement that contains a list of values or an expression that defines what should be inserted into each column.
SYNTAX
Insert into sessions1 values (2, ‘2022-03-10 10:15:19.400’, ‘2022-04-10 10:35:19.400’, 1);
The above syntax describes how to insert into a table called sessions 1, 2 columns with values and timestamp. We can also use the VALUES clause in an INSERT statement if we want to specify only one value for each column in the table. For example, this statement specifies that the first record should contain two columns with the values ‘2022-03-10 10:15:19.400’ and ‘2022-04-10 10:35:19.400’
Specifying Columns list before the “VALUES” is highly recommended:
Insert into sessions1 (id, start_date, end_date, category)
Values (13, ‘2022-03-09 16:15:19.500’, ‘2022-03-11 17:53:57.753’, 2);
This statement specifies that the first record should contain two columns with the values ‘2022-03-10 10:15:19.400’ and ‘2022-04-10 10:35:19.400’: Specifying Columns list before the “VALUES” is highly recommended.
After specifying the column list, we don’t need to remember the column order as specified in the Snowflake table.
Insert into sessions1 (category, id, start_date, end_date)
values(2,3, ‘2022-06-08 15:05:15.500’, ‘2022:08:10 17:07:53.600’);
This statement specifies that the first record should contain a column with an integer data type, and another column with a string data type
Advantages of Including column list
- The column list is a very useful feature for creating tables in Snowflake. When we use this feature,
- we don’t need to remember the order of columns in Snowflake tables.
- We can also use the column list for inserting data into multiple columns with one statement.
- A column list can help clarify what we want to do with our data. If you have a column list, then you don’t need to remember the order of columns in the table as well as their type.
- The column list will also prevent us from making errors when specifying data types for our columns.
For example, if you have various columns, we have to specify only some columns:
Insert into sessions1(start_date, id) values (5, ‘2022-05-03 15:17:35.600’);
We can specify only some columns to insert data into a column list: Insert into sessions1(start_date, id) values (5, ‘2022-05-03 15:17:35.600’), (6, ‘2022-04-02 15:17:35.400’);
How to Insert in multiple rows ?
If you want to insert multiple rows of data at once, then you can use the “INSERT INTO'' statement. In this case, each row should be separated by a comma:then we can use the following syntax:
Insert into sessions1 (category, start_date, end_date, id)
values
(7, ‘2022-05-03 16:10:25.500’, ‘2022-03-04 17:15:45.553’, 5),
(6, ‘2022-03-05 17:20:25.300’, ‘2022-09-05 18:25:10.500’, 6),
(8, ‘2022-05-07 19:15:20.200’, ‘2022-07-09 21:05:09.600’, 7);
The above syntax describes that we are creating three rows: 7, 6, and 8. Each row has three columns: category, start_date, end_date and id.
We can also utilize the “CREATE TABLE” with the “SELECT” command for copying the data from the existing table.
copying the data from the existing table.
The following syntax will help us create a new table named “sessions2” that contains the values from the existing table (“sessions1”)
---excluding the column list specification
Create table sessions_dm_2 as
Select *
from sessions1
Where id <=6
The above syntax describes that we are creating a new table named “sessions2” which contains the values from the existing table (“sessions1”) excluding only the id column. We can also use the “CREATE TABLE” with the “INSERT INTO” command for copying data into an existing table.
If we want to insert the data in JSON column into our existing table, then we can use the following syntax:
Insert into sessions1(dates) values( ‘ {“start_date”: “2022-05-07 17:10:20.600”, “end_date”: “2022-08-09 16:12:25.180”}’);
The above syntax describes that we are inserting the values into the “dates” column of the existing table (sessions1). The data is formatted in JSON format.
HOW TO UPDATE DATA IN SNOWFLAKE
If we want to update the data we can update using the "where" clause or "update" clause. Then we can use the following syntax:
Snowflake Update Table for Single Row
Example:
update sessions1
set start_date = ‘2022-05-03 11:15:13.430’
end_date = ‘2022-07-25 12:30:20.321’
where id = 2;
The above syntex describes that we are updating the data in “sessions1” table. We are using "where" clause to identify the row which needs to be updated.
For Multiple Rows
We use the following code to update multiple rows.
update sessions1
set end_date = null
where category = 2;
The above syntax describes multiple rows that we are updating the data in “session 1” table. We are using "where" clause to identify the rows which needs to be updated.
For updating all the rows in the Snowflake table, just use the “UPDATE” statement without the “WHERE” clause:
update sessions1
set end_date = ‘2022-08-09 15:45:57.753’
The above syntax describes updating all the rows in the Snowflake table. We are using "SET" clause to update the data in “session 1” table.
To update multiple rows at a time, use the below code:
update sessions1
set start_date = ‘2022-09-09 15:50:57.625’
end_date = ‘2022-07-06 17:50:55.737’
The above syntax describes updating multiple rows at a time. We are using the "SET" clause to update the data in the “session 1” table.
CONCLUSION
In this post, we learned about deleting data in a snowflake table using the "DELETE" command and "WHERE" clause.
We also learned about inserting data into a snowflake table using the "INSERT" command and "WHERE" clause.
We also discussed briefly about updating data in the snowflake table using the "SET" clause and column name or expression. We have learned how to update one row at a time and multiple rows at a time.
I hope this post will be helpful for you to understand the concept of deleting, inserting and updating data in snowflake tables. The post will help you to understand the concept of deleting, inserting and updating data in a snowflake table. If you have any questions or suggestions, please leave a comment below.