StarburstGalaxyTraining.md

Data and Innovation Summit hands-on Workshop

Lab 1

CREATE SCHEMA demo_datalake_catalog.transactions;

Lab 2

CREATE TABLE demo_datalake_catalog.transactions."orders" (custkey ,orderkey , totalprice , orderdate ,orderstatus, orderyear , ordermonth) WITH (partitioned_by = ARRAY[ 'orderyear', 'ordermonth' ] , format= 'ORC') AS SELECT custkey, orderkey, totalprice,orderdate,orderstatus , extract( year from orderdate) , extract (month from orderdate) FROM tpch.tiny.orders;

Lab 3

SELECT * FROM demo_datalake_catalog.transactions.orders;

Lab 4

SELECT * FROM demo_rdb_catalog.master_data.customer;

Lab 5

SELECT c.custkey, c.name,c.nationkey, o.orderkey , o.totalprice , o.orderdate ,o.orderstatus , o.orderyear , o.ordermonth FROM demo_datalake_catalog.transactions.orders o INNER JOIN demo_rdb_catalog.master_data.customer c ON c.custkey = o.custkey;

Lab 6

CREATE VIEW demo_datalake_catalog.sales_view.customer_orders SECURITY INVOKER AS SELECT c.custkey, c.name,c.nationkey, o.orderkey, o.totalprice , o.orderdate, o.orderstatus, o.orderyear, o.ordermonth FROM demo_datalake_catalog.transactions.orders o INNER JOIN demo_rdb_catalog.master_data.customer c ON c.custkey = o.custkey;

Lab 7

SELECT c.* , o.* FROM tpch.tiny.orders o INNER JOIN demo_rdb_catalog.master_data.customer c ON c.custkey = o.custkey;

Lab 8

CREATE SCHEMA demo_datalake_catalog.raw;

Lab 9

CREATE TABLE demo_datalake_catalog.raw.nation( nationkey varchar, name varchar, regionkey varchar, comment varchar) WITH ( external_location = 's3://data-innovation-summit/data/static/nation/', format='CSV' );

Lab 10

SELECT * FROM demo_datalake_catalog.raw.nation;

Lab 11

CREATE VIEW demo_datalake_catalog.sales_view.customer_orders_nation SECURITY INVOKER AS SELECT c.custkey, c.name,c.nationkey, o.orderkey , o.totalprice , o.orderdate ,o.orderstatus , o.orderyear , o.ordermonth FROM demo_datalake_catalog.transactions.orders o INNER JOIN demo_rdb_catalog.master_data.customer c ON c.custkey = o.custkey INNER JOIN demo_datalake_catalog.raw.nation n ON c.nationkey = cast(n.nationkey as int);

Lab 12

SELECT * FROM demo_datalake_catalog.sales_view.customer_orders_nation;