GETTING THINGS ROLLING, migrating a SQL Database to SAP Data Warehouse Cloud
DBeaver is a free universal database client with excellent built-in spatial datatype support. Since version 6.2.2 it offers support for SAP HANA and since version 6.3.1 the support of SAP HANA Spatial has been contributed. DBeaver and pgAdmin can be primarily classified as 'Database' tools. DBeaver and pgAdmin are both open source tools. DBeaver with 9.56K GitHub stars and 796 forks on GitHub appears to be more popular than pgAdmin with 270 GitHub stars and 126 GitHub forks.
Here at Interdobs, we like SAP Data Warehouse Cloud. I’m guessing this is no secret by now, looking at the list of blogs which can be found here.
The more I dig into the solution, the more I see the added value. Especially for the busines user who wants to combine existing data with data from external sources.
We already tried out the Odata connection, ABAP and HANA connector in our previous blogs and also just recently tinkered with the new JDBC connection which will open up Data Warehouse Cloud virtually to any database known to man. How’s that for openness.
Migrating SQL to SAP Data Warehouse Cloud
Now there is one element which I did not tackle before: the possibility to migrate existing data directly into the Cloud using a 3rd party tool. Let’s figure out what we can do with that!
Imagine this: you have a very large SQL database which you’ve used to produce all kind of Marketing reports across the globe. Performance is lacking behind, you are pretty much fed up with having a zillion tools to Extract, load, consolidate and report out of SQL and want to move to Cloud Data Warehouse in combination with SAC. You have basically two options:
- You use the upcoming JDBC functionality in DWC to virtually access your data on premise
- You migrate your tables in one go to the Cloud
Let’s see how we would tackle option 2.
Dbeaver
It’s amazing to see how many open source tools I use nowadays to make my life easier. I use visual SQL tools to create AMDP scripts and if I want to investigate exotic databases I use one of the best tools around: Dbeaver.
Dbeaver Sap Hana
Here at Interdobs, we (also) love Dbeaver. Virtually any database can connect to it, so let’s try if we can connect Data Warehouse Cloud .
Local Schema
First we create a local schema in Data Warehouse Cloud. This creates a schema, a user and PW to connect to the underlying HANA database
Once connected I go to Dbeaver and create a connection
Creating the connection can be a bit tricky. Most importantly, use the following user property to create make a secure connection
Once that is done, the rest is “kinderspiel”. Besides being an awesome database client tool, Dbeaver can also migrate content from one database to another!
Sakila
Sakila is a fantastic open source project which holds database scripts for many databases. You guessed it by know, we like Sakila ;-). We used it extensively in our HANA SQL Webflix demo’s which you can find here.
You can use Sakila to create a sample database which we will use to test migrating the data to the cloud.
We choose the tables and press export
Dbeaver does an auto mapping of all the fields and generates the DDL and insert scripts under the hood.
And off we go!
Dbeaver Hana School
The result is a 1:1 copy of the data in the Cloud which can be seen in Dbeaver.
But ofcourse also in Data Warehouse Cloud
The result is a fully migrated environment on which I can create my models!
Dbeaver Hana
And stories!
Dbeaver Hana Sso
Also want to see more of Data Warehouse Cloud? Let me know!