Skip to Content

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…

Flexible_Schema_001.jpg

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…

We will have this when getting all records…

Flexible_Schema_002.jpg

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’;

Flexible_Schema_003.jpg

To report this post you need to login first.

13 Comments

You must be Logged on to comment or reply to a post.

    1. Alvaro Tejada Galindo Post author

      Well…for sure you’re adding new column dynamically, so the structure of the table needs to be changed…but the impact shouldn’t be really hard as for the column that doesn’t have values on those new fields no extra calculation need to be done…

      Anyway…I haven’t done any “performance” test to see the real impact…so this is just my assumption 🙂

      Greetings,

      Blag.

      (0) 
  1. Patrik Steuer

    Hi Alvaro,

    thanks for the great work! Do you know if there are any limitations to flexible tables in comparison to standard column store tables?

    Best regards

    Patrik

    (0) 
    1. Lars Breddemann

      You mean beyond the fact that all data in flexible columns will be stored as VARCHAR(5000)?

      Well, one thing is that you cannot create a full text index on top of those columns.

      Also, your application has to be able to deal with an effectively unknown data structure and that’s not so easy to have.

      – Lars

      (0) 
      1. Patrik Steuer

        Hi Lars,

        Yes exactly. The flexible creation of columns through the insert statement, comes with the limitation of NVARCHAR(5000). As long as you do not use “STRING” as data type this is no big deal. You can change the types afterwards… Even this does not always work on the first shot (If you need a DECIMAL(13,2) you have to make an ALTER DECIMAL first on the column before you can make the ALTER DECIMAL(13,2))


        As you described in your book you can overcome the 1000 columns limitation. This is much more important to me. I do not really need to create flexible attributes. So I can still predefine the columns in the create table statement. We had the problem that some tables had more than 1000 attributes so we had to split these tables into several … which is not the nicest solution.

        Would I be able to have some additional columns and still be able to use all the tools HANA provides or are there any hidden limitations (as full text index) you are aware of?

        Today I will check how the performance is affected by the flexible schema if the amount of columns and data are the same and the structure is predefined.

        Best regards

        Patrik

        (0) 
      2. Patrik Steuer

        Hi Lars,

        I checked the full text index on top of a NVARCHAR(5000) column created through an insert statement, which worked. We are still using Rev. 82.

        Best regards

        Patrik

        (0) 
        1. Lars Breddemann

          Hi Patrik,

          looks like this would be worth a discussion thread on its own.

          Good to hear that the text index is now supported – actually I haven’t used flexible tables much lately.

          Back when I tested it, the query performance wasn’t that bad at all generally. But as the column store select performance worsens a little bit with every single materialized column in the projection, you’ll find that even simple selects take a lot of time when they return 10.000+ columns. So, be careful here.

          Besides that, some challenges with flexible schemas are:

          • what to do with existing records when new columns are added? (default, NULL, compex rule?)
          • How do design your application in a way to deal with the flexible data (and do more than just show the data) and still be manageable.

          I guess the biggest obstacle (especially during maintenance/extension/support) with no-schema or flexible-schema applications is the implicit schema that is used by the application but not visible in the database. 

          Well, you get it – I’m not yet the biggest fan – but that might change once I learn more about this concept.

          – Lars

          (0) 
  2. Sergio Guerrero

    Alvaro,

    this is a good post and it is worth investigating further for performance, analysis, how to deal with in an application, etc. I concur with Lars, there is a lot to consider but since it is there, then we need to adapt.

    Thank again, you for the post

    (0) 
    1. Lars Breddemann

      “… there is a lot to consider but since it is there, then we need to adapt…”

      HA! 🙂

      Clearly not. Just because there is a special case tool available doesn’t mean one has to employ it.

      Looking at SAP HANA you will find that there are several features available that are barely used/advertised/developed. Some of them are simply there because at some point there existed a demand for them – even just SAP internally – and so they stuck around.

      “New” features like flexible tables, history tables, etc. need to be properly understood before packed into an application – especially when they appear to be so inviting.

      Many times there are new base-features (like table or data types) that in principle facilitate certain functions (e.g. just add a column to a table or show an old record version) and what happens is that these base-features are misunderstood as application level functions.

      Such an expectation of course cannot be met  – take for example the “time travel” with history tables; it’s not relevant in a application context.

      So, yes, learn about the new stuff, no question about that. But don’t just adapt your code because it’s there and looks fancy

      my 2 cts on this.

      (0) 
      1. Sergio Guerrero

        Lars,

        thanks for your reply. you probably misunderstood me, when I said adapt, I meant adapt to what is there, and as part of my “investigating further…” was more along the lines of educate ourselves and see what is the good, and the not so good about this and prove it. I hope no one in their right mind would just go “adapt your code” resulting in waste of time, money and probably causing other issues, however… nothing wrong with testing out concepts that maybe others could benefit from. After all, we are here for the same reason, to learn, collaborate and improve this world.

        (0) 
    1. Alvaro Tejada Galindo Post author

      Muhammad:

      I haven’t tried that…but…I don’t think it makes to much sense to be honest…and that’s probably the reason why it doesn’t work…

      When you’re uploading a file you already know how many columns and rows you have…so you must define the table in a way that can hold all that information…

      In my example things are different…I’m uploading information from Twitter…so I don’t know beforehand what I’m going to get…

      So…I don’t think using flexible schemas in importing a file is possible and I think it should stay that way…that could bring a lot of performance issues…

      Greetings,

      Blag.

      Development Culture.

      (0) 

Leave a Reply