Business Context
A manufacturing company needs to ingest millions of sensor readings from its assembly line into a central CAS table for real-time anomaly detection. Performance is critical to ensure the monitoring dashboard is up-to-date. The goal is to append new data batches as efficiently as possible.
Data Preparation
Creates an initial `sensor_data_main` table and a very large `new_sensor_batch` table to simulate a high-volume data stream.
Copied!
| 1 | DATA mycas.sensor_data_main; |
| 2 | DO i = 1 to 100; |
| 3 | sensor_id = ceil(rand('uniform') * 10); |
| 4 | reading = rand('normal', 100, 5); |
| 5 | OUTPUT; |
| 6 | END; |
| 7 | RUN; |
| 8 | |
| 9 | DATA mycas.new_sensor_batch; |
| 10 | DO i = 1 to 2000000; |
| 11 | sensor_id = ceil(rand('uniform') * 50); |
| 12 | reading = rand('normal', 100, 5); |
| 13 | OUTPUT; |
| 14 | END; |
| 15 | RUN; |
Étapes de réalisation
1
Load the initial main table and the large batch table into CAS.
Copied!
| 1 | PROC CAS; |
| 2 | /* Data is already loaded via data steps */ |
| 3 | TABLE.tableInfo / caslib='mycas'; |
| 4 | RUN; |
2
Append the large source table to the target table using `singlePass=True` to optimize for performance by avoiding a transient copy of the source.
Copied!
| 1 | PROC CAS; |
| 2 | TABLE.append / |
| 3 | SOURCE={name='new_sensor_batch', singlePass=True}, |
| 4 | target={name='sensor_data_main'}; |
| 5 | RUN; |
3
Verify that all rows from the source batch have been successfully appended by checking the final row count.
Copied!
| 1 | |
| 2 | PROC CAS; |
| 3 | TABLE.rowCount / TABLE={caslib='mycas', name='sensor_data_main'}; |
| 4 | RUN; |
| 5 |
Expected Result
The 2,000,000 rows from `new_sensor_batch` are appended to the initial 100 rows of `sensor_data_main`. The final table should contain exactly 2,000,100 rows. The operation should complete efficiently, demonstrating the benefit of the `singlePass` option.