Case Scenario: Schema Evolution¶
Procedure¶
- Apply the command which you want to execute
- To observe the changes within the Workbench, execute the below command
Dataset - dataos://icebase:retail/city
Add Field/Column¶
The following command can be used to add a column to the table or a nested struct.
dataos-ctl dataset -a dataos://icebase:retail/city add-field \
-n ${{column-name}} \
-t ${{column-datatype}}
Only the following column data types are supported
Data type | Description | Requirements |
---|---|---|
boolean | True or false | |
int | 32-bit signed integers | Can promote toย long |
long | 32-bit signed integers | |
float | 32-bit IEEE 754ย floating point | Can promote to double |
double | 64-bit IEEE 754ย floating point | |
decimal | Fixed-point decimal; precision P, scale S | Scale is fixed, [1], precision must be 38 or less |
date | Calendar date without timezone or time | |
time | Time of day without date, timezone | Microsecond precision |
timestamp | Timestamp without timezone | Microsecond precision [2] |
timestamptz | Timestamp with timezone | Stored as UTC [2] |
string | Arbitrary-length character sequences | Encoded with UTF-8 [3] |
uuid | Universally unique identifiers | Should use 16-byte fixed |
fixed(L) | Fixed-length byte array of length L | |
binary | Arbitrary-length byte array |
Initial Schema observed in Workbench
![Schema Evolution 1](/resources/lakehouse/command_reference/case_scenario_schema_evolution/1.png)
Let's add a new column called new1
into the city dataset with a type string
. Execute the following code in the terminal.
Output (on successful execution of code)
To observe the changes made to the initial dataset, execute the following code
Output (on successful execution)
Observe the change in the workbench; a new field by the name new1
is added
![Schema Evolution 2](/resources/lakehouse/command_reference/case_scenario_schema_evolution/2.png)
Drop Field/Column¶
To remove an existing column from the table or a nested struct, the following command can be executed
![Schema Evolution 2](/resources/lakehouse/command_reference/case_scenario_schema_evolution/2.png)
Letโs remove column new1
from the city dataset. Execute the following code in the terminal
Output (on successful execution of code)
To observe the changes made to the initial dataset, execute the following code
Output (on successful execution)
Observe the change in the workbench; the new1
column is removed
![Schema Evolution 1](/resources/lakehouse/command_reference/case_scenario_schema_evolution/1.png)
Rename Field/Column¶
To rename an existing column or field in a nested struct, execute the below code
dataos-ctl dataset -a dataos://icebase:retail/city rename-field \
-n ${{column-name}} \
-m ${{column-new-name}}
Letโs rename the column city_name
in the city dataset to name
. For this following code needs to be executed -
![Schema Evolution 5](/resources/lakehouse/command_reference/case_scenario_schema_evolution/4.png)
Output (on successful code execution)
To observe the renamed column in the workbench, run the set-metadata command
Output (on successful execution)
The city_name
column is renamed to name
. As observed below
![Schema Evolution 6](/resources/lakehouse/command_reference/case_scenario_schema_evolution/6.png)
Updateย Field/Column¶
To widen the type of a column, struct field, map key, map value, or list element, the below command can be executed
dataos-ctl dataset -a dataos://icebase:retail/city update-field \
-n ${{column-name}} \
-t ${{column-datatype}}
๐ฃ Updating column type is limited to only certain data types for any particular type -
integer
tolong
-float
todouble
- Increasing the precision ofdecimal
type
![Schema Evolution 1](/resources/lakehouse/command_reference/case_scenario_schema_evolution/1.png)
Letโs update the zip_code
column type from INTEGER (integer)
to BIGINT (long)
. The code is as follows -
Output(successful execution)
To observe the changes in the workbench, execute the set-metadata command as follows -
Output (on successful execution)
The type of zip_code
is changed from INTEGER (integer)
to BIGINT (long)
![Untitled 8](/resources/lakehouse/command_reference/case_scenario_schema_evolution/8.png)