Skip to Content

Hello everyone, thank you for taking the time to read this short blog. I wanted to share a couple ways to load data into HANA tables as I prepare for a demo where I am starting from an empty slate.

For data loads involving more than one-time loads, there are other tools/techniques used which are not on the scope of this blog such as (Data services – ETL tool, SLT (trigger based replication), XS Jobs running on schedule, etc)


My demo will involve creating tables, views, and other information models as I shared with you on my CDS blogs a few weeks ago.

Now, I need to load some data into my tables (yes these are dummy tables and not what I will use for my true demo but you will get the idea)

My first approach to load data is via the CDS table import – official documentation – This approach is very clean and straight forward to do.

1) I created my target table “testTable” with 3 columns, namely, ID, col1, col2

/wp-content/uploads/2016/03/1_907814.jpg

2) Created a sample csv file and uploaded it to my project folder (just dragged and dropped and activated it there) – below you see the file and the sample data  (6 records for demo purposes)

/wp-content/uploads/2016/03/2_907815.jpg

3) once the target table was created, and the sample data is uploaded to the project, we need to create an .hdbti file (table import) which will do the trick for us.

* if you are using HANA Studio, you may click on New > Other > (table import wizard) I typed “impo” and just followed the wizard steps (provided a name, selected a folder in my project, etc)

/wp-content/uploads/2016/03/3_907818.jpg

/wp-content/uploads/2016/03/4_907816.jpg

/wp-content/uploads/2016/03/5_907823.jpg

The file below gets created and then you need to provide the correct information for the target CDS table ( which we created on step 1) , the file that contains the data (step 2) and the delimiter field (default is , but it could be anything else ) once this is completed, activate your .hdbti file

/wp-content/uploads/2016/03/6_907824.jpg

After the file was activated, I can see the table was in fact populated

/wp-content/uploads/2016/03/7_907825.jpg

My next attempt to perform data loads into the same table (append to existing content) is to use the Data Modeler perspective and use the (Window > Show View) Import Wizard so that I do not have to load and activate the flat file into my package and only doing a one-time import following the wizard.

1) Open the SAP HANA Modeler perspective  / Quick view tab

/wp-content/uploads/2016/03/8_907826.jpg

2) Open the SAP HANA Content  folder >  “Select Data from local file”

/wp-content/uploads/2016/03/9_907827.jpg

3) Select your target system ( sometimes you may have more than 1 system depending on hoe many connections you have opened)

/wp-content/uploads/2016/03/10_907828.jpg

4) Select your file, target table, delimiter, specify whether or not your first row contains the header (column names), etc

/wp-content/uploads/2016/03/11_907832.jpg

5) perform a column mapping (drag from the left panel columns to the right panel columns).

notice also that the data preview on the bottom panel is available (data from flat file)

Click Finish

/wp-content/uploads/2016/03/12_907833.jpg

Return to your SQL console and run the select statement to insure the data was loaded

/wp-content/uploads/2016/03/13_907834.jpg

if you noticed on step 1 that my ID column was a key. My imported records cannot contain a duplicate record with the same ID. I did this on my first attempt and received an error due to the constraint was violated, therefore, I went back to my flat file, and made sure the IDs were different and finally I was able to load the data without issues after that change.

Just to conclude, this short demo was about doing some initial data loads for demo purposes. For data loads involving more than one-time data loads, there are other tools/techniques which I did not discussed on the scope of this blog. such techniques/tools are Data Services – ETL tool, SLT (trigger based replication), XS jobs running on a schedule, etc.

Thank you again for reading this blog and please feel free to ask questions or share your experiences.

To report this post you need to login first.

2 Comments

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

  1. Werner Daehn

    You might want to mention Smart Data Integration, given it is the most commonly used technology to load data into Hana.

    My second concern is that you are actually not describing how to load data into Hana but how the file import options work. What would you do when the file is differently formatted, say Japanese codepage, fixed width or whatever? And worse, users do not load files into Hana but copy data from e.g. a source database into Hana or Twitter or Facebook or WebServices…

    Hana Smart Data Integration – Overview

    (0) 
    1. Sergio Guerrero Post author

      Werner, thanks for suggesting the HANA SDI part as you probably saw it was not on the scope of my demo but I will gladly check it out as well.

      As far as the file being formatted differently, if the data types are not correct, most likely your file will not be able to import. If you have more columns than you need, you may be ok as there is a step for mapping the columns. As far as code base, I really did not need to check anything other than English on my part but if you had any experiences/suggestions, please share with us.

      The point of integration you mentioned when data comes from an external source such as Twitter, FB, web services, etc… there are other tools/techniques which I didn’t cover, again that was out of my scope, but you may definitely use Data Services, XS engine outbound connectivity, nodejs, etc. I hope this is helpful for others as well. funny thing you mentioned Twitter as I have a small nodejs utility myself where I pull tweets based on a word or phrase 🙂 and I am about to insert to a HANA DB. (currently I am just saving json files as I just started on that last week)

      Again, thank you for bringing this to everyone’s attention and if you have any other suggestions or would like to share your experiences, we will all appreciate it.

      Good day!

      (0) 

Leave a Reply