Migrating a HCP MaxDB database to HANA
Overview: This blog describes migrating a simple MaxDB database from HCP to a dedicated HCP HANA instance. Also has some details around a open source tool called XSImport – a free, open source native XS app for importing CSV files into HANA.
In the Enterprise Mobility team at SAP we are currently working on an exciting project migrating one of our existing applications to a HANA backend, while going through a major code rewrite. During this process, I had the task of migrating a HCP based MaxDB over to a dedicated HCP HANA DB. The dataset I was working with was not particularly large (@1.3 GB) with the majority of the data residing in just a few tables. (For reference this is a standalone Java HCP app). Since remote tunnel access to production HCP Databases is not available, the only way to get the raw data was through a HCP ticket, the support team were helpful and responsive, and after a couple of hours I had my exported dataset.
I loaded the data into my local MaxDB instance and chose to use the MaxDB loadercli command line tool to export the data out of the source DB and into CSV files, during this step I transformed some of the data as well put the data into the corresponding column order which matched my target table to make things simpler. I had one challenge with using loadcli which I posted to MaxDB SCN forums – but still no solution, so I had to clean some of the CSV files up using regex in my text editor.
Here is an example export statement from MaxDB. The use of CASE statements can be helpful to transform data types:
EXPORT COLUMNS ID, CREATIONDATE, DESCRIPTION, CASE WHEN TYPE = 'single' THEN '1' WHEN TYPE = 'group' THEN '2' WHEN TYPE = 'public' THEN '3' ELSE ' ' END FROM NEO_12345.T_TABLE DATA OUTSTREAM 'T_TABLE.csv'
Once I had a set of CSV’s I needed to decide how I was going to load the data into my HANA instance, after some trial and error of using the HDBTI files, via the build in IMPORT statements (I have no way of getting access to the file system of the HANA instance), using HANA Studio, or a dedicated import script via XSJS – I decided on the latter. I found the studio process just did not work for some reason, but the xsjs import process was reasonably quick. Since I was going to do multiple loads, I thought it would be pretty helpful to have a tool which facilitates loading the CSV files and wrote a small open source tool called XS Import.
Overall the process was fairly straight forward, aside from some of the minor challenges described above 🙂
If you have done any similar migrations in HCP? and had a different experience, please feel free to share your thoughts on some of the processes and methods I chose above as I would like hear some of your opinions. (I am hoping to hear if there is faster/better/simpler?)
Thanks and cheers, Paul
Nice approach, with the reduced table size.
To share another case, here in SAP GSS Brazil we had a requirement to migrate our local internal project base from a Sybase ASE to an on-premise HANA.
We started with the .csv approach, but also got caught in some HANA Studio obstacles.
Then, the chosen strategy was 1) map ASE in the HANA Studio using SDA, 2) run a simple script with the following statement to go through all tables in ASE and replicate them in HANA: create table <table_name> as (select * from <source>)
Worked just fine.
Hi Eduardo -
Thanks for sharing - I think the SDA approach is awesome and often overlooked. Another one of my favorites SQL statements for importing is the INSERT INTO/SELECT FROM.
- I think a really cool solution in our case, and possibly other SAP customers, would be around using SDA to connect to a HCP MaxDB from their on-premise HANA instance, thus creating a sort of "Hybrid" cloud/on-prem data store.