GETTING THINGS ROLLING, migrating a SQL Database to SAP Data Warehouse Cloud

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:

1. You use the upcoming JDBC functionality in DWC to virtually access your data on premise
2. 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.

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!

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!

And stories!

Also want to see more of Data Warehouse Cloud? Let me know!