alterTable - WeAreCAS

Q: What is the primary purpose of the `alterTable` action?

The `alterTable` action is used to modify the metadata and structure of an in-memory table. This includes renaming the table, changing the table's label, altering column properties like names, labels, and formats, as well as dropping columns.

Q: How can I rename a table using this action?

To rename a table, use the `name` parameter to specify the current table and the `rename` parameter to provide the new table name.

Q: What is the difference between the top-level `drop` parameter and the `drop` option within the `columns` parameter?

The top-level `drop` parameter takes a list of column names to be dropped from the table. The `drop` option within the `columns` parameter is a boolean (TRUE/FALSE) that applies to the single column specified in that column's definition block.

Q: How do I change the format and label of a specific column?

To change a column's properties, use the `columns` parameter. Provide a list containing a dictionary where you specify the column's `name`, and then set the new `format` and `label` values.

Q: Can I reorder the columns in my table?

Yes, you can specify the new physical order of columns in the table's metadata by providing a list of column names to the `columnOrder` parameter.

Q: What does the `lifetime` parameter control?

The `lifetime` parameter specifies the number of seconds to keep the table in memory after it is last accessed. If the table is not accessed within this duration, it is automatically dropped from memory. A value of 0 means the table will not be dropped automatically.

Q: How can I keep only a specific set of columns and remove all others?

You can use the `keep` parameter, providing a list of the column names that you wish to retain in the table. All other columns will be dropped.

Q: What is the purpose of the `tableRedistUpPolicy` parameter?

This parameter specifies the table redistribution policy when the number of worker pods increases on a running CAS server. It can be set to 'NOREDIST' to prevent redistribution, 'REBALANCE' to rebalance the data across the new set of workers, or 'DEFER' to let a higher-level entity decide.

At a glance

Data Engineers and Architects frequently rely on the alterTable command to manage schema evolution without incurring the performance penalty of reloading datasets. This utility provides a mechanism to manipulate in-memory metadata on the fly, allowing for the renaming of tables, reassignment of column labels, and adjustment of data types directly within the CAS environment. By enabling structural changes without physical data movement, it significantly streamlines the ETL pipeline. The Q&A section below offers deep dives into common use cases, exploring how to effectively manage column attributes and troubleshoot modification errors in active sessions.

Codes SAS Liés

Beyond the Basics: Using PARTITION and ALTERTABLE to Reshape CAS Data

High-Speed Aggregation: How to Filter and Group Data Instantly with SAS Viya

Beyond the DATA Step: How to Filter and Alter CAS Tables Faster with PROC CASUTIL

High-Performance Aggregation in SAS Viya: Mastering CAS GroupBy Actions

Mastering SAS CAS: How to Dynamically Alter Table Labels and Formats with PROC CAS

Rename a CAS table

Description

The alterTable action allows for modifying the metadata and properties of an in-memory CAS table. This includes renaming the table, changing its label, modifying column properties such as names, labels, and formats, as well as dropping columns or reordering them. It is a versatile tool for managing table structures directly on the CAS server without needing to recreate tables.

table.alterTable { caslib="string", columnOrder={"variable-name-1", "variable-name-2", ...}, columns={{ binaryType="IMAGE" | "SOUND" | "UNKNOWN" | "VIDEO", drop=TRUE | FALSE, format="string", label="string", name="variable-name", newType="VARBINARY" | "VARCHAR", rename="string" }, {...}}, drop={"variable-name-1", "variable-name-2", ...}, keep={"variable-name-1", "variable-name-2", ...}, label="string", lifetime=64-bit-integer, name="table-name", rename="string", tableRedistUpPolicy="DEFER" | "NOREDIST" | "REBALANCE" };

Settings

Parameter	Description
caslib	Specifies the caslib for the input table. By default, the active caslib is used.
columnOrder	Specifies the order of columns in the table's metadata.
columns	Specifies a list of dictionaries, each defining alterations for a specific column (e.g., renaming, dropping, changing format/label).
drop	Specifies a list of column names to be dropped from the table.
keep	Specifies a list of column names to be kept in the table; all other columns are dropped.
label	Assigns a new label to the table.
lifetime	Specifies the number of seconds to keep the table in memory after its last access. After this period, the table is dropped.
name	Specifies the name of the table to be altered.
rename	Specifies a new name for the table.
tableRedistUpPolicy	Specifies the table redistribution policy when the number of worker pods increases on a running CAS server. Can be 'DEFER', 'NOREDIST', or 'REBALANCE'.

Data Preparation View data prep sheet

Data Creation

This code creates a sample table 'CARS_DATA' in the 'casuser' caslib, which will be used in the following examples. The table contains information about different car models.

Copied!

1	DATA casuser.CARS_DATA;
2	LENGTH Make $ 15 Model $ 20 Type $ 10;
3	INFILE DATALINES delimiter=',';
4	INPUT Make $ Model $ Type $ MSRP Horsepower;
5	DATALINES;
6	Toyota,Camry,Sedan,24000,203
7	Ford,Mustang,Coupe,27000,310
8	Honda,CR-V,SUV,25000,190
9	Chevrolet,Silverado,Truck,30000,355
10	Tesla,Model 3,Sedan,40000,283
11	;
12	RUN;