-
Notifications
You must be signed in to change notification settings - Fork 1
/
Athena_Iceberg_SQL_3.sql
50 lines (36 loc) · 1.49 KB
/
Athena_Iceberg_SQL_3.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
--
-- Schema Evolution
--
-- Add new column
ALTER TABLE iceberg_database.amazon_reviews_iceberg ADD COLUMNS (comment string)
-- Populare column
UPDATE iceberg_database.amazon_reviews_iceberg SET comment = 'High rated' WHERE star_rating >=4
-- Verify
SELECT * FROM iceberg_database.amazon_reviews_iceberg WHERE star_rating >=4 LIMIT 10
--
-- Time travel and version travel queries
--
-- View snapshots
SELECT * FROM "iceberg_database"."amazon_reviews_iceberg$iceberg_history"
-- Query data From past snapshot
SELECT * FROM iceberg_database.amazon_reviews_iceberg FOR SYSTEM_VERSION AS OF <<replace snapshot_id>> WHERE marketplace ='UK' LIMIT 10
-- Quert data from past time
SELECT * FROM iceberg_database.amazon_reviews_iceberg for SYSTEM_TIME as of TIMESTAMP '<<replace with made_current_at time>>' where marketplace ='UK' LIMIT 10
--
-- Delete rows
--
-- Delete rows
DELETE FROM iceberg_database.amazon_reviews_iceberg WHERE product_category = 'Software'
-- Verify
SELECT * FROM iceberg_database.amazon_reviews_iceberg WHERE product_category = 'Software'
--
-- Retrieve deleted data
--
-- View snapshots
SELECT * FROM "iceberg_database"."amazon_reviews_iceberg$iceberg_history"
-- Select 2nd snapshot ID
INSERT INTO iceberg_database.amazon_reviews_iceberg
SELECT * FROM iceberg_database.amazon_reviews_iceberg FOR SYSTEM_VERSION AS OF <<Enter Snapshot ID>>
WHERE product_category = 'Software' LIMIT 10
-- Verify
SELECT * FROM iceberg_database.amazon_reviews_iceberg WHERE product_category = 'Software' LIMIT 10