Skip to Content

I got a very interesting comment on one of my Blogs: “can you send me the files you use? I also want to load 700 million records in my HANA sandbox”. This led me to believe I need to do some explanation on how you get many test records without actually loading huge files!

 

Now I have to be honest, I did not come up with this trick myself. Hans Bouwers, one of the HANA specialist in our team guided me towards this awesome trick. It will generate your much needed “Big Data” in a flash.

 

So “show me the records Ronald”!

 

Step 1 – Create some temporary tables and populate them with data

 

Let’s say my main table “SALES” has the following structure:

 

MONTH

SALESREP

CUSTOMER

SALES

TARGET

January

B. Gates

Apple

120000

122222

….

….

…..

…..

…..

 

If I would want to populate the above with 120 million records I do not have to load enormous files to do so, but I would just create some temporary tables and a get little help from my friend SQL.

 

Create 3 extra temporary tables (they mirror your original table, if you need some help on creating tables and loading data, just follow one of my SAP HANA Developer Access Beta program – get the best out of your test-drive!):

 

Table “SALES1” (total 12 records):

 

MONTH

January

February

…..

 

 

Table “SALES2” (total 1000 records):

 

SALESREP

B. Gates

S. Ballmer

…..

 

Table “SALES3” (total 10.000 records):

 

CUSTOMER

SALES

TARGET

Apple

120000

122222

Pixar

122111

122221

….

….

….

 

 

12 x 1000 x 10.000 = 120.000.000 records. The above thee small tables can give us 120 million records with a little help from SQL!

 

 SQL your way into “Big Data”

 

Go into the HANA studio, open up a SQL window and execute the following command:

 

insert into SALES

select 

  SALES1.MONTH,

  SALES2.SALESREP,

  SALES3.CUSTOMER,

  SALES3.SALES,

  SALES3.TARGET,

  from SALES1, SALES2, SALES3

 

Wait for a couple of minutes and be amazed. For every unique combination of your fields a record is generated. How is that for “Big data”!

Be nice

Now I can imagine that we do not want to stress the above to the limit. No need to bring our fantastic HANA experts Blag and Juergen into trouble by blowing up the database ;-).

 

Thanks for reading this blog and stay in touch!

 

Ronald.

To report this post you need to login first.

6 Comments

You must be Logged on to comment or reply to a post.

  1. Balaji S

    Hi,

    Nice trick.

    Does anyone have .csv or script for SALES2 and SALES3? 10000 records is still big to create manually.. 😉

    Regards,

    B

    (0) 

Leave a Reply