page214
300
Confirm the row count from the new view,
core_customer_order
, matches the
original table row count of 1,500,000,000.
USE WAREHOUSE IDENTIFIER ( $tpc_warehouse_xs );
SELECT count(1)
FROM core_customer_order;
To preserve backward compatibility, re-create the grants for the original table,
which would look something like this, substituting in the role names to match your
environment:
GRANT INSERT, UPDATE, DELETE, TRUNCATE ON TABLE
part_customer_order_asia TO ROLE <YOUR_INGEST_ROLE>;
GRANT INSERT, UPDATE, DELETE, TRUNCATE ON TABLE
part_customer_order_europe TO ROLE <YOUR_INGEST_ROLE>;
GRANT INSERT, UPDATE, DELETE, TRUNCATE ON TABLE
part_customer_order_africa TO ROLE <YOUR_INGEST_ROLE>;
GRANT INSERT, UPDATE, DELETE, TRUNCATE ON TABLE
part_customer_order_america TO ROLE <YOUR_INGEST_ROLE>;
GRANT INSERT, UPDATE, DELETE, TRUNCATE ON TABLE
part_customer_order_middle_east TO ROLE <YOUR_INGEST_ROLE>;
GRANT SELECT ON VIEW
core_customer_order TO ROLE <YOUR_CONSUMER_ROLE>;
With the target core table segmented and reconstituted via a view, let’s now
investigate parallelizing data ingestion.
Concurrent Warehouse Processing
Each segment requires a matching warehouse processing unit as you will shard the data
to match the clustering key range.
Figure 8-19 shows how you can automate the data ingest using one pair of streams
and tasks per partition; you also use a common stored procedure. Using a common
stored procedure enables a parameter-driven approach to parallelization. Alternative
orchestration tooling exists, which I will leave to you for your further investigation.
Chapter 8
parallelization