page203
289
Use a standard table called
base_customer_order
to hold a baseline set of data for
later use.
CREATE OR REPLACE TABLE base_customer_order
AS
SELECT c.c_custkey AS customer_key,
c.c_name AS customer_name,
o.o_orderkey AS order_key,
o.o_orderdate AS order_date,
n.n_name AS nation_name,
r.r_name AS region_name
FROM snowflake_sample_data.tpch_sf1000.region r,
snowflake_sample_data.tpch_sf1000.nation n,
snowflake_sample_data.tpch_sf1000.customer c,
snowflake_sample_data.tpch_sf1000.orders o
WHERE c.c_custkey = o.o_custkey
AND c.c_nationkey = n.n_nationkey
AND n.n_regionkey = r.r_regionkey;
Create a target core table to simulate an existing application table.
CREATE OR REPLACE TABLE core_customer_order
AS
SELECT customer_key, customer_name, order_key, order_date,
nation_name, region_name
FROM base_customer_order;
Now add a clustering key on
region_name
and
nation_name
.
ALTER TABLE tpc.tpc_owner.core_customer_order
CLUSTER BY ( region_name, nation_name );
Reset the warehouse to X-Small.
USE WAREHOUSE IDENTIFIER ( $tpc_warehouse_xs );
Count the number of records created and identify the latest
order_date
and highest
order_key
values, because you will use this information shortly when creating raw or
staging data.
Chapter 8
parallelization