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
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)
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)
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
After the file was activated, I can see the table was in fact populated
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
2) Open the SAP HANA Content folder > "Select Data from local file"
3) Select your target system ( sometimes you may have more than 1 system depending on hoe many connections you have opened)
4) Select your file, target table, delimiter, specify whether or not your first row contains the header (column names), etc
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
Return to your SQL console and run the select statement to insure the data was loaded
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.
- SAP Managed Tags:
2 Comments
