#Random sql data generator code
In case you want to adjust the code or just understand what happens, an example table definition and procedure call is provided in the header section of the procedure. To insert two records into that table, the procedure is called as follows: CALL "GenerateData"(Ī select * on that table yields the following result: Therefore, you will likely see minimal compression that is not to be compared with real-life use cases.Īs an example, a table is created with the following statement: CREATE SCHEMA GENERATEDATA The random generated values make compression difficult.
Created with HANA 2, SPS04, not guaranteed to work with older service packs.Binary data types are not supported at all. The primary key should contain at least one column that can hold integer values.
Only supports tables with a primary key.Keep in mind the following limitations, or adjust the code to work around them: Call the procedure using the following statement:.A sequence with that name is used in the procedure. Create a sequence with following statement: “CREATE SEQUENCE GENERATEDATASEQ1”.Dowload the code in this Git repository and copy it into the SQL console.
#Random sql data generator how to
How to run the data generator procedureįollow below steps to generate data for your table That was a lot faster than the first version of the script, which was not set-based. On my little test system, I achieved an insert rate of 9 million records per minute for a table with just a few fields. Record updates or deletions are randomized based on the first column of the primary key. The procedure uses the HANA metadata tables to retrieve the table definition. For example, for an integer field, a random integer value is inserted, and for a character-based field, a random string of characters is inserted. Inserts and updates are based on the field definition of the provided table and contain randomized data.
The procedure inserts, updates, or deletes data of a single table, using mostly set-based operations for high-performance. Because others might find the script useful too, I extended the script for easy re-use. Therefore, I wrote a database procedure to fill my table with the data that I needed. I googled something like “data generator for HANA” but didn’t find what I was looking for. Recently I ran a scenario on the SAP HANA database for which I needed a large initial data set for a specific table, and another large delta data set consisting of deletions, updates and inserts on the initial data set.
0 kommentar(er)
