Schema Flexibility on SAP HANA - In a nutshell

Some time ago I post a blog called Getting flexible with SAP HANA that used SAP HANA, R and Twitter to demonstrate the Schema Flexibility capabilities...

I came to realize that even when that example is really cool...it's not really aimed for beginners, because you need a lot of R and Regular Expressions experience to fully understand what's going on...so...I decided to write a more simple blog...using only SAP HANA to show this awesome option...

So...what "Schema Flexibility" means? Well...it means that you can define a table with some columns and then dynamically add more columns at run time without the need of redefine the table structure...

First...let's create a table using plain SQL...

Create Table
CREATE COLUMN TABLE Products( PRODUCT_CODE VARCHAR(3), PRODUCT_NAME NVARCHAR(20), PRICE DECIMAL(5,2) ) WITH SCHEMA FLEXIBILITY;

As you can see...it's just a table...but we're adding the WITH SCHEMA FLEXIBILITY option...

Now...we can simply insert one product...

Insert Product
INSERT INTO Products values ('001','Blag Stuff', 100.99);

Let's say that we need to add a new product...that comes in different colors...but our table doesn't have a COLOR column defined...but it doesn't matter...our table is flexible enough to hold it up...

Insert New Product
INSERT INTO Products (PRODUCT_CODE,PRODUCT_NAME,PRICE,COLOR) values ('002','More Blag Stuff',100.99,'Black');

Notice that we're defining all the columns and adding a new one called "COLOR"...and simply pass the new value...when we select all the records from the table, we will have this...

As you can see...for the second record, we have the new column "COLOR" along with it's value...for the first record, we simple have an "?" because the "COLOR
column didn't exist at the time of it's creation...

Now...let's say we need to add another new product...that doesn't come in colors...

Insert last new product
INSERT INTO Products (PRODUCT_CODE, PRODUCT_NAME, PRICE) values ('003','Even More Blag Stuff',101.99);

Notice that we need to specify the "regular" column names, but we don't need to care about the dynamic column...ll have this when getting all records...

As the column "COLOR" already exist at the time of the creation of the last product...we will see a "?"  value again...

I hope that with this small blog...this gets more clear -:) Even where there are not so many use cases for this...I expect many people to get creative and use this cool feature...

Small update

You might have realized that when you create a new column..it's going to be generated as NVARCHAR(5000)...so that's not very helpful right?

Sadly...we cannot change this because the feature is not "yet" supported...and that's because the field length can't be shortened...

However, if we are passing a numeric value...then we're allow to change it...consider the following example...

Altering COLOR Column
INSERT INTO Products values ('001','Blag Stuff', 100.99); INSERT INTO Products (PRODUCT_CODE,PRODUCT_NAME,PRICE,QUANTITY) values ('002','More Blag Stuff',100.99,10); ALTER TABLE Products ALTER (QUANTITY INT);

Here...we're adding a new column called "QUANTITY" with a value of 10...at first is going to be created a NVARCHAR(5000) but with a simple ALTER TABLE we can change it to INT...

Now, let's say we need to update the first field to include a quantity as well...using a simple UPDATE will do the trick...

Updating a field
UPDATE Products SET QUANTITY = 20 WHERE PRODUCT_CODE = '001';

Greetings,

Blag.