Views via SQL -- Column or row store?

[Former Member]

Hi community,

when I create a view via a SQL Statement " Create View..." based on column tables, will this view be in column store layout, too?

In HANA Studio, those views are listed in "Views", therefore I think it's row-store.

- Is there a way to create views with a column store layout, via SQL?

   " Create Column View.." doesn't work.

- How to find out the store layout of a view? Can't find anything about it in the properties...

Thanks in advance!

Kind Regards,

Demian

[lbreddemann · Accepted solution]

Hi Demian,

the question is totally appropriate and currently our documentation team is working hard on filling the gaps here.

To answer your question:

Yes, SAP HANA provides two kinds of VIEW types - plain SQL views (created via CREATE VIEW) and so called column views (create via CREATE COLUMN VIEW).

The SQL view is the same as views are in all other DBMS. A SELECT statement encoded in a dictionary objects so that you can use it as if was a table on its own.

Whether or not the ROW or the COLUMN engines get involved in processing queries against this view does not depend on the view type. It can fully use row-store and column-store tables, just as you could do this with SQL.

The Column view on the other hand is a SAP HANA specific view type. It allows to create view-like structures for so called calculation scenarios. The SAP HANA modeler views (attribute/analytic/calculation view) are technically all defined as column views with the specific calculation scenario assigned to them.

You can check on this by reviewing the DDL statements for the generated runtime objects in schema _SYS_BIC.

- Lars

[former_member182862 · Accepted solution]

HI Lars

Looking at

http://help.sap.com/hana/html/sql_create_view.html

CREATE COLUMN VIEW <ViewName> ...

is new to me.

Is this a new thing in HANA?

Thanks

Dennis

[Former Member · Accepted solution]

Hi Lars,

thanks for your answer!

I tried "Create Column View..." but it didn't work for me...

So starting which version of HANA is this available?

Kind Regards

Demian

[lbreddemann · Accepted solution]

Nope - had been there from before rev. 1.

- Lars

[lbreddemann · Accepted solution]

It's available with *all* versions of HANA - I assume you just don't use the correct syntax.

And - to be fair - the syntax is just not easy to know, especially as it's not externally documented...

- Lars

[former_member182862 · Accepted solution]

You are kidding...

Are you confused with CREATE TABLE and CREATE VIEW?

Dennis

[henrique_pinto · Accepted solution]

Of course not.

It just wasn't documented, but since the first version of HANA you could create attribute/analytic/calc views, which are nothing more (actually a bit more) than column views created in the _SYS_BIC schema.

[former_member182862 · Accepted solution]

Thanks Henrique

Dennis

[former_member184768 · Accepted solution]

I think one can check the syntax in the generated column views. .

It goes something like

CREATE COLUMN VIEW "_SYS_BIC"."blah/blah_view" WITH PARAMETERS

(indexType=5, joinIndex="SOMEONE"."SOMETABLE", joinIndexType=1, joinIndexEstimation=0,

  viewAttribute=etc etc etc...

)

I wish I could understand all the "blahs" and "etc"s in the syntax. If it seems like a trade secret for SAP .

Regards,

Ravi

[former_member182862]

Hi Demian

As far as I know, View is always row based. Regardless whether the table(s) that are referenced in the view is/are column and row stored.

Dennis

[Former Member]

Hi Raj, Jerry,

your answers really helped me to understand the problem.

Thank you!

Regards,

Demian

[Former Member]

Hi Demian,

As you create database view by SQL statement, it is only for row store, as you know, when you can create attribute view/ analytic view and calculation view, they are column stored strategy.

Jerry

[AtulKumarJain]

Hi Demian,

Please check

http://help.sap.com/hana/html/sql_create_view.html

http://scn.sap.com/thread/0002141404

BR

Atul

[Former Member]

Hi atul jain,

thanks for the second link. Couldn't find it myself.

I need to clarify my question:

I know that views are not persisted. But still HANA seems to differentiate between "Views" and "Column Views".

When creating a view via SQL based on column tables, will those views behave like column tables or row store tables from a performance perspective. E.g. when selecting all attributes vs. one of the view.

Looking at the architecture of HANA there seem to be seperated row- and column-store "areas". I hoped to answer the question of the behavior when knowing which storage "area" the view belongs to.

Thanks again, and sorry if it turns out to be just a stupid question

Regards,

Demian

[rindia]

Hi Demian,

Your question is not stupid but puts anyone to think about it. My opinion is:

Whenever a table is created, it will created either row store or column store and we can see this in the table definition type of HANA studio.

When we create a view based on a row store or column store,  we do not have type in the open definition, hence we cannot say how it behaves.

For the time being let us assume that as I created the view based on column table, let us imagine that it is view based on column type. Then i created calculation view (SQLScript) and then used CE_COLUMN_TABLE on the view created. Then i got the error saying that "Parameter must be a valid column table name". By this we can conclude that a view created on column table is not an view based on column store.

From the performance point of view, we get better results when information views are based on column tables using CE functions.

Hope you understand my point of thinking.

Regards

Raj