adb-data-engineer-cert-prep/1.4 Views.sql at main · devruji/adb-data-engineer-cert-prep · GitHub

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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
-- Databricks notebook source
-- MAGIC %md
-- MAGIC
-- MAGIC ### Create actual table

-- COMMAND ----------

SELECT current_catalog()

-- COMMAND ----------

SELECT current_database()

-- COMMAND ----------

CREATE TABLE IF NOT EXISTS customers (CustomerName STRING, ContactName STRING, Address STRING, City STRING, PostalCode STRING, Country STRING);

INSERT INTO customers
VALUES
  ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway'),
  ('Greasy Burger', 'Per Olsen', 'Gateveien 15', 'Sandnes', '4306', 'Norway'),
  ('Tasty Tee', 'Finn Egan', 'Streetroad 19B', 'Liverpool', 'L1 0AA', 'UK');

SELECT * FROM customers

-- COMMAND ----------

-- MAGIC %md
-- MAGIC
-- MAGIC ### Create View

-- COMMAND ----------

CREATE OR REPLACE VIEW v_country_norway
AS
SELECT *
FROM customers
WHERE country = 'Norway';

SELECT * FROM v_country_norway;

-- COMMAND ----------

SHOW TABLES

-- COMMAND ----------

-- MAGIC %md
-- MAGIC
-- MAGIC ### Create TEMP View

-- COMMAND ----------


CREATE OR REPLACE TEMP VIEW v_country_uk
AS
SELECT *
FROM customers
WHERE country = 'UK';

SELECT * FROM v_country_uk;

-- COMMAND ----------

SHOW TABLES

-- COMMAND ----------

-- MAGIC %md
-- MAGIC
-- MAGIC ### Create GLOBAL View

-- COMMAND ----------


CREATE OR REPLACE GLOBAL TEMP VIEW v_customers_eiei
AS
SELECT *
FROM customers;

SELECT * FROM global_temp.v_customers_eiei;

-- COMMAND ----------

SHOW TABLES

-- COMMAND ----------

SHOW TABLES

-- COMMAND ----------

SHOW VIEWS IN global_temp;