SAP HANA basic questions

[Former Member]

I am new to SAP HANA and started understanding the basic architecture. have a few questions. Appreciate if you can clear my doubts.

1) when we load the data (lets take BODS 4.0) to SAP HANA DB, where the data will go intially Disk or In-memory ?

2) If it is to Disk, will the data be automatically moved to In-memory (RAM) OR do we need to do anything ?

3) If i have 100 tables loaded from ECC to HANA, in which i'll use 20 tables only for reporting. so how to move only these tables to In-memory ?

4) I have seen Load,merge options on tables in HANA studio . will these be used for above steps ?

Thanks in advance !

[mathanponnucham]

Hello,

Please do find answer for your questions below:-

1) when we load the data (lets take BODS 4.0) to SAP HANA DB, where the data will go intially Disk or In-memory ?

Ans: Data will be loaded to RAM and then persisted to disk automatically(Data & log) during save points.

3) If i have 100 tables loaded from ECC to HANA, in which i'll use 20 tables only for reporting. so how to move only these tables to In-memory ?

Ans: Can you please explain why do you want to move data from ECC to HANA if you are not using it?

Data will be loaded from disk to RAM when ever you execute a query that involves a table which is not already in the RAM

4) I have seen Load,merge options on tables in HANA studio . will these be used for above steps ?

Ans: Delta Merge is not used for data loading.

A column store table is comprised of two index types, for each column a main index and a delta index. The delta storage is optimized for write operations and the main storage is optimized in terms of read performance and memory consumption. Delta mege is used to transform data from delta index to main index. More informaiton in link : http://scn.sap.com/docs/DOC-27558

Rgds,

Mat.

[SK-EA]

Most accurate answers

[premsukh_bishnoi]

Hi,

Regarding your question please find below response and i have tried to give you in best of my knowledge.

1> Initially data will be placed in disk not in memory.

2> Data will be not moved automatically, it will be moved vai hana studio .

3> yes you are correct.

Regards, Premsukh

[henrique_pinto]

Are you sure of these replies??

When running a query in a unloaded table, HANA will automatically load in memory the slice of data relevant for that query (since all queries can only be run against data in memory).

Additionally, you can also define a table in such a manner where it will always be loaded during system start up. Check http://help.sap.com/hana/html/sql_alter_table.html#alter_table_preload_clause

Finally, when loading data in HANA from external sources, it will go through the persistence layer, which will save the data being written in the log buffer in memory and only after the transaction is committed, then the transaction will be persisted to the log disk. Hence HANA inputs data in the memory before. This information can be found in chapter 11 (The Persistence Layer) of the HANA Bluebook (which is an internal document, hence I cannot paste the content here).

[patrickbachmann]

Hey Henrique in regards to what you mentioned about loading data from external sources;  Today I copied a large table in HANA to another new table in HANA via SQL.  I watched as the memory seemed to fill up quickly and yet the disk size did not seem to grow as I expected.  Eventually I killed the load because it was using all of the memory.  It made me wonder if the data was first being queued up in memory until committed to disk which apparently I never got far enough to reach that point.  Is that how it would work using SQL to copy from one table to another using insert?  It would first queue up in memory before committing to disk?

Thanks

[henrique_pinto]

I cannot commit to this, since I'm not a development architect myself, but apparently that's it.

Initially, during Write phase, all data is written to a log buffer (in memory).

Then, after the transaction commit is triggered, it would dump the log buffer to log files (disk).

[Former Member]

1) Lets take i loaded 1 million records today (Intial load). tomorrow for delta load i added 0.2 million. do i need to explicitly execute DELTA MERGE statement to load these delta records to main table ?

2) I heard column tables are much faster to query compared to Row based...any reason?

3) for row-based tables: Will the data be stored in Disk also ? Because if the DB is restarted, all the data in the In-memory will be lost...right.

column tables: ok. by default the data will be loaded in Disk as well as memory...right.

[former_member189905]

Hi Hana user, 

in row-based tables each row is unique and the reading of the row is sequentially field by field. 

In column-based tables the data can be aggregate and compression by the same value of one colum. This reduces the number of rows to be read. When you want to access at the country "CHINA" of one table of world population the system read only the column "COUNTRY" and access directly at the records of "CHINA". It do not read record by record and field by field to test each record "COUNTRY" fields to select data of "CHINA".

This greatly reduces the time of each reading when you work with lot of data (Big Data;). 

However,when performing operations on single tuples or for inserting new rows, a row-based layout is beneficial.

[patrickbachmann]

Thanks Henrique!  That makes perfect sense.

[Former Member]

1) Lets take i loaded 1 million records today (Intial load). tomorrow for delta load i added 0.2 million. do i need to explicitly execute DELTA MERGE statement to load these delta records to main table ?

Delta merge will automatically be done by the system and its not necessary to execute this step manually.

2) I heard column tables are much faster to query compared to Row based...any reason?

It depends on the kind of query mostly. Usually the analytical query tries to access only few columns of the table..and such queries will be faster in columnar storage tables.

However if a query tries to access few records of a table(all column) I would expect this query to faster for row storage

3) for row-based tables: Will the data be stored in Disk also ? Because if the DB is restarted, all the data in the In-memory will be lost...right.

Always the data first goes to RAM and then persisted at disk. The LOG and DATA volume ensure you do not loose either row or column storage data.

column tables: ok. by default the data will be loaded in Disk as well as memory...right.

Data is first loaded to RAM and then persisted at DISK

Hope this clears some doubts.

Regards,

Rahul

[mathanponnucham]

Hello,

1) Lets take i loaded 1 million records today (Intial load). tomorrow for delta load i added 0.2 million. do i need to explicitly execute DELTA MERGE statement to load these delta records to main table ?

Ans: A system process called "Mergedog" periodically checks the column store tables which are loaded locally to the storage server and for which auto merge is enabled. It then determines for each individual table (or single partition of a split storage) if a merge should be executed based on configurable criteria.

More informaiton in link : http://scn.sap.com/docs/DOC-27558

2) I heard column tables are much faster to query compared to Row based...any reason?

Ans: Query performance is much faster in Column tables If your tables have more of non-distinct values.

Row Store = Many data rows involving all columns needs to be accessed to get the result for your query.

Column Store = Data can be accessed in one read  by accessing only the columns mentioned in the query

3) for row-based tables: Will the data be stored in Disk also ? Because if the DB is restarted, all the data in the In-memory will be lost...right.

Ans: All the data in HANA RAM will be persisted to disk during save points. Data will not be lost.

Rgds,

Mat.