Breaking News: Grepper is joining You.com. Read the official announcement!

snowflake queries

Innocent Iguana answered on August 13, 2024 Popularity 3/10 Helpfulness 1/10

answer snowflake queries

snowflake queries

Comment

-- Create a stage and put a file in that staging area
CREATE STAGE my_local_stage -- create a stage
PUT file:///path_to_your_local_file/filename.csv 
@my_local_stage -- put a file in the created stage 

-- Create a table
CREATE TABLE table_name (
col_id NUMBER COMMENT 'This is a Unique identifier',
col_date DATE,
col_time TIME,
col_datetime TIMESTAMP
)
COMMENT = 'This is a table'

-- load file from staging area to a table
COPY INTO table_name FROM @my_local_stage/filename.csv
FILE_FORMAT = (TYPE = 'CSV' SKIP_HEADER=1 )

-- Show information
SHOW DATABASES -- show all databases
SHOW [TABLES|SCHEMAS|VIEWS] IN DATABASE db_name -- show tables or schemas in a database
SHOW COLUMNS IN table_name -- show columns in a table
SHOW TABLES LIKE '%PIZZA%' IN DATABASE db_name -- show all tables with name that has word "PIZZA" in it

-- See description
DESCRIBE [DATABASE|TABLE|VIEW|STAGE] name
DESCRIBE SCHEMA PUBLIC -- see public schema

-- Create a view
CREATE VIEW view_name AS
SELECT col1, col2
FROM table_name

-- Renaming
ALTER TABLE IF EXISTS old_table_name RENAME TO new_table_name;
ALTER TABLE IF EXISTS table_name RENAME COLUMN old_col_name TO new_col_name;

-- Dropping a table
DROP TABLE table_name

-- INSERT using specified values
INSERT INTO table_name (id, date, time) VALUES (1, '2015-01-01', '11:38:36')
-- INSERT using query
INSERT INTO table_name
    SELECT * FROM some_table WHERE date > '2015-01-02'
-- UPDATE
UPDATE table_name
SET time = '17:00:00' WHERE id = '5'

-- MERGE / UPSERT or synchronization update or insert
MERGE INTO target_table AS t
USING source_table AS s
ON t.id = s.id
WHEN MATCHED THEN 
    UPDATE SET t.amount = s.amount
WHEN NOT MATCHED THEN 
    INSERT (id, product, amount) VALUES (s.id, s.product, s.amount);

-- Top 10 rows
SELECT TOP 10* FROM table_name
-- Casting
SELECT CAST(col_date AS TIMESTAMP) AS col_timestamp FROM orders
-- Conversion functions 
SELECT [TO_DATE|TO_VARCHAR|AVG|CONCAT|UPPER] (col_name) from table_name
-- standalone functions
SELECT [CURRENT_DATE|CURRENT_TIME]

-- extract date information
SELECT EXTRACT(YEAR FROM col_timestamp) AS year FROM table_name

-- group by operation
SELECT col1, col2, AVG(col3) AS average_val
FROM table_name
GROUP BY ALL -- No need to specify columns / you can specify col1, col2 if you want, which also works 
ORDER BY pizza_type_id, average_price DESC

-- JOIN IN SNOWFLAKE

-- Snowflake does not allow LIMIT keyword on correlated subqueries, use CTE
-- CTE
WITH cte AS ( 
    SELECT id, MAX(price) AS max_price
    FROM some_table GROUP BY id )
SELECT l.col1, r.col2,
FROM left_table AS l
JOIN right_table AS r ON l.id = r.id
JOIN cte ON r.col3 = cte.id
WHERE l.price < cte.max_price

-- query history for analyzing query performance
SELECT * FROM snowflake.account_usage.query_history
WHERE execution_time > 1000

Popularity 3/10 Helpfulness 1/10 Language sql

Source: Grepper

Tags: sql

Link to this answer
Share Copy Link

Contributed on Aug 13 2024

Innocent Iguana

0 Answers Avg Quality 2/10

snowflake queries

Contents

More Related Answers

snowflake queries

Grepper

Documentation

Social

Legal

Contact

Oops, You will need to install Grepper and log-in to perform this action.