4

We are new to Hana DB and starting to build a Hana DB connector for our existing application for an existing customer adopting SAP B1 with a Hana DB. Our application just needs to make connections to the Hana DB and perform some basic SQL SELECT queries. We want to reconstruct the Hana DB data structure schema (i.e. tables, fields, etc) in our devtest environment based on a customer's new setup so we can build and tune those queries on our side.

Is there a recommended way to quickly export or dump the Hana DB, either just the structure only or the structure plus their current test data set in the database and then recreate on our side?

As an example in MySQL, it would be something as simple as:

  1. Test Customer's side: mysqldump -h host -uuser -p database_name > database_name.sql
  2. Our DevTest side: mysqladmin -h host -uuser -p create database_name
  3. Our DevTest side: mysql -h host -uuser -p database_name < database_name.sql

Is there an equivalent to the above steps for a Hana DB?

Are there both GUI tool and command line tool equivalents of the above that would already be installed on the Windows machine where they have it installed?

If our test customer is running on Windows and we intend to setup our DevTest Hana DB on Linux, are the tools and syntax to export/import the same?

Will there be any differences if we intend to use SAP Hana Express Edition from the Docker Store for our DevTest environment and our customer is likely using a full blown SAP Hana edition. Are the DB structure and commands/tools and syntax going to be all the same?

Streamline
  • 2,040
  • 4
  • 37
  • 56

1 Answers1

0

Here's answers to a few of your questions, based on my experience with HANA.

  • GUI side, we used to use Eclipse to export and import HANA schemas. You can choose to export the DB structure only or structure + data. This view was available by installing the SAP Cloud Platform tools (see https://tools.hana.ondemand.com/#cloud); a perspective which allowed database browsing was then installed into Eclipse, called SAP HANA administration console. By right clicking your schema, you had import/export options available. Eclipse is cross-platform so I imagine this would be an option on both Windows and Linux.

This approach is described in this article.

Surprisingly, this is the only option we had. Our different environments had to have their HANA schemas synced in structure and test data, and we never found any way of automating these steps. Will future enhancement on SCP allow an integration between Jenkins and HANA DBs? Time will tell.

SAP also provides a SAP HANA Studio, which is desktop software you can download from the SAP store, but it's just a revamped version of Eclipse and offers no extra functionality over it.

Note: in our case, our HANA DB was hosted on SAP Cloud Platform. Would the Eclipse method using the SCP SDK plugin work if the DB isn't hosted on SCP? I imagine it would, but cannot confirm.

  • Command-line side, although I've never done it this way, I found this link which says you can use a tool called HDBSQL and then simply call IMPORT/EXPORT/IMPORT FROM commands to export catalog objects; seemingly, quite a few options on that side. The HANA documentation is quite good on these commands.

  • Concerning the differences between Express and Developer editions of HANA, it seems a lot are related to memory and storage, and there is some functionality which is only available in the Developer edition, such as smart data streaming. So yes, it seems the schema structure, commands and tools are going to be the same. I wouldn't worry about compatability problems between both editions; they seem to differ only in the range of options available.

Chris Neve
  • 2,164
  • 2
  • 20
  • 33
  • Chris, our customer's setup is not using SCP as far as I know, they are using an AWS Windows server for everything. GUI: Is your Eclipse/HANA browser based solution assuming you will need install Eclipse and the needed perspective or the HANA browser on the AWS server or would expect it to already be installed as a standard SAP B1 and/or Hana DB installation for a Windows AWS server setup? We haven't yet started so I am without the ability to check currently. CLI: It would be great if we could find examples as that HDBSQL sounds closer to a path that could eliminate other install requirements. – Streamline Nov 30 '17 at 21:07
  • I really don't know the functioning of B1 or AWS server. But with Eclipse you can connect to a remote DB so it wouldn't require it to be installed on the same server. But I do think it would need to be installed separately, it wouldn't come as part of the HANA installation. Have you checked what does come with the HANA installation? There may be an administration guide. My experience is as a SAP developer, not customer; I understand it's a very different perspective. Re the CLI tools - I'm *pretty* confident those will be included in the HANA install. – Chris Neve Nov 30 '17 at 21:20
  • Remote connection from Eclipse isn't a path we can plan for in this case as the AWS server is currently accessible via a remote windows desktop type of connection and we don't want to introduce opening up access beyond that for this. We will keep the Eclipse GUI option as a standby but would like to focus in more on the CLI HDBSQL option. I did find that tool in the HanaDB Express setup from the Docker store so I think you are right that should be included already. – Streamline Nov 30 '17 at 21:49
  • Actually it appears the HDBSQL Export may be for exporting the data (objects) in a binary or csv output vs something like a sql output that will recreate the tables and fields on import. Can you confirm that your GUI based steps when selecting export and the structure option you referred to is exporting what will be needed to recreate the database structure from scratch on the end that imports it without tables and fields defined? Are data objects in SAP defining how to recreate the table structure so it can be created from scratch? – Streamline Nov 30 '17 at 22:06
  • I just had a look at the doc of the EXPORT command and I'm confident it exports the structure of the DB also, and that this output can be used with the IMPORT command to recreate the DB structure. This is the GUI approach described: https://blogs.sap.com/2012/04/02/sap-hana-exporting-of-tables-into-local-desktop/ As you can see, there's options during the process to export "catalog+data" or "catalog only". I added this link to my answer. – Chris Neve Dec 01 '17 at 09:13
  • Viewing that blog link you provided, it appears they are selecting tables individually (at least in the tool they are using there which looks to be different than the Eclipse presentation you mentioned). Does that approach allow for exporting all tables for the database at once? That's the goal here. To be able to export everything in the database without needing to know ahead of time which tables might be needed individually and just recreate it all on our side in one swift movement (like the original posting example of a mysqldump at the database level). – Streamline Dec 01 '17 at 18:34
  • Yes, that's HANA studio they're using there. It definitely allows to export the whole schema at once. – Chris Neve Dec 01 '17 at 20:28