Introduction
Does your company use Snowflake to store data for analysis and reporting? If that’s the case, the Snowflake Alter Table column aggregator tool is one of several beneficial capabilities that allow you to adjust your data after it’s been ingested into the data warehouse, allowing you to capture additional business situations or use cases. .
In addition, scripting, full shortcut capability, and a high level of security are features of SnowSQL, Snowflake’s query language.
This article will give you an introduction to Snowflake Alter Table and also explain how to modify RENAME Tables in Snowflake, swap tables with Alter Table in Snowflake, and modify temporary table in Snowflake.
snowflake altar table
Snowflake Alter Table’s add columns feature allows you to add a new column to an existing table in your data warehouse. This is useful when you need to add new values/measures, categorical values that depend on other table columns, or capture more data.
Syntax:
ALTER TABLE [ IF EXISTS ]
[ , … ]
Where, columnAction ::= { ADD COLUMN
[ { DEFAULT <expr> | { AUTOINCREMENT | IDENTITY } [ { ( <start_num> , <step_num> ) | START <num> INCREMENT <num> } ] } ]/* AUTO INCREMENT (or IDENTITY) supported only for columns with numeric data types (NUMBER, INT, FLOAT, etc.). */ /* Also, if the table is not empty (ie, rows exist in the table), only DEFAULT can be modified. */
[ inlineConstraint ]
| CHANGE THE NAME OF THE COLUMN
How to alter RENAME tables in Snowflake?
To replace or edit the table name, use the Alter Rename clause.
Syntax:
— Alter SQL syntax command: ALTER TABLE [OLD_TABLE_NAME] RENAME TO [NEW_TABLE_NAME]
Example: To start, create a table called Snowflake Practice, then rename it Snowflake Rename Practice. — Create table query: create or replace table Snowflake_Practice (sid int, department_name text, fees int) — Output +———————– — ———————–+ | state | |————————————————| | The SNOWFLAKE_PRACTICE table was created successfully. | +————————————————+ 1 Row(s) produced. Elapsed time: 0.452 s: now RENAME table to Snowflkae_Rename_Practice ALter table Snowflake_Practice Rename To Snowflake_Practice_Rename; +———————————-+ | state | |————————————————- | | Declaration executed successfully. | +———————————+ 1 Row(s) produced. Elapsed Time: 0.392 s Snowflake Rename Table Example with GUI — Example: ALTER TABLE EMP RENAME TO EMP_ONE EMP was the name of the old table, while EMP ONE was the name of the new table. As a result, the table will be renamed EMP ONE instead of EMP. Choose the table that needs to be renamed from the drop down menu. I selected EMP as the table name. Run the command to rename the table. As a result, the table will be renamed EMP ONE instead of EMP. The name was successfully changed to EMP ONE.
SWAP tables using Alter Table in Snowflake
To change table names in Snowflake, use the SWAP TABLE function.
In Snowflake, an example of a TRADE TABLE is as follows:
–SQL command syntax: ALTER TABLE [TABLE_ONE] TRADE WITH [TABLE_TWO]
–Example: ALTER TABLE EMP SWAP WITH EMP_ONE Table names are swapped regularly. EMP ONE will be the name of the EMP table and EMP will be the name of the EMP ONE table. To get started, decide which tables need to be changed. I chose EMP and EMP ONE to change tables. After that, run the command to swap the table names. Check the data in the tables to make sure it is correct. The table names were successfully exchanged, as you can see.
Alter temporary table in Snowflake
To modify or replace a temporary table with a permanent table, use the rename table or swap table query clauses.
Alter temporary table using Swap in Snowflake Example
— Modify the table Customer_Temp Swap with Customer
Alter Temporary Table Using Rename in Snowflake Example
— Modify table Customer_Temp Rename to Customer
Also, read: snowflake data types
Conclusion
The Add Snowflake Alter Table Column command is a very useful command to learn because you will almost definitely need it when running your data warehouse. Hopefully, you’re now ready to start incorporating it into your own Snowflake database code.
Categories: Technology
Source: SCHOOL TRANG DAI
