Business Context
An administrator is managing a CAS environment that processes large volumes of IoT sensor data. A daily raw table needs to be processed, its columns reordered for query efficiency, and its lifecycle managed to prevent it from consuming memory indefinitely.
Data Preparation
Create a wide table simulating IoT sensor readings, where column order is not optimal for typical queries.
Copied!
| 1 | DATA casuser.IOT_RAW_20251126; |
| 2 | LENGTH device_id $20 status_code $5 payload $100; |
| 3 | FORMAT event_ts datetime22.3; |
| 4 | INPUT temp_c humidity pressure_kpa device_id $ event_ts status_code $ payload $; |
| 5 | DATALINES; |
| 6 | 22.5 45.2 101.3 DEV-A7B 27NOV2025:00:00:00.123 OK data_block_1 |
| 7 | 22.6 45.1 101.3 DEV-A7B 27NOV2025:00:00:01.125 OK data_block_2 |
| 8 | -999 99.9 105.0 DEV-C4E 27NOV2025:00:00:02.500 ERR data_block_3 |
| 9 | 35.1 60.0 100.1 DEV-B3F 27NOV2025:00:00:03.000 OK data_block_4 |
| 10 | ; |
| 11 | RUN; |
Étapes de réalisation
1
Load the daily raw IoT data into CAS.
Copied!
| 1 | |
| 2 | PROC CAS; |
| 3 | TABLE.loadTable / caslib='casuser' path='IOT_RAW_20251126.sashdat' casout={name='iot_raw_20251126', replace=true}; |
| 4 | RUN; |
| 5 |
2
Reorder the columns to place identifiers and timestamps first, which is a common pattern for improving query performance in time-series data. Also rename the table.
Copied!
| 1 | PROC CAS; |
| 2 | TABLE.alterTable / |
| 3 | caslib='casuser' |
| 4 | name='iot_raw_20251126' |
| 5 | rename='iot_processed_temp' |
| 6 | columnOrder={'device_id', 'event_ts', 'status_code', 'temp_c', 'humidity', 'pressure_kpa', 'payload'}; |
| 7 | RUN; |
3
Set the table's lifetime to 3600 seconds (1 hour) to ensure automatic cleanup after processing. Also, define the redistribution policy for cluster elasticity.
Copied!
| 1 | PROC CAS; |
| 2 | TABLE.alterTable / |
| 3 | caslib='casuser' |
| 4 | name='iot_processed_temp' |
| 5 | lifetime=3600 |
| 6 | tableRedistUpPolicy='REBALANCE'; |
| 7 | RUN; |
4
Verify the new column order and check the table options to confirm the lifetime has been set.
Copied!
| 1 | |
| 2 | PROC CAS; |
| 3 | TABLE.tableDetails / name='iot_processed_temp', level='column'; |
| 4 | RUN; |
| 5 |
Expected Result
The table 'iot_processed_temp' should have its columns physically reordered in the metadata as specified. The table's properties should show a lifetime of 3600 seconds, meaning it will be dropped from memory one hour after its last access. The redistribution policy should be set to 'REBALANCE'.