Generating fake databases
Roy Hann
discuss efficient techniques for generating large volumes of completely
synthetic database content having specified characteristics? I'd
settle for pointers to any software tools that might exist.
I am not interested in generating mere random values; I want to
efficiently generate plausible/realistic values for multiple tables
and the "data" must satisfy my database constraints and have specified
distributions of key values.
If it is relevant, assume I want to create databases for an SQL DBMS.
Needless to say I've made a stab at Googling for what I want but I
haven't been able to guess effective search terms.
--
Roy
Rob
Database as a starting point. If you go to ACM and search on
David DeWitt (UWisconsin CompSci prof), you'll find references.
If that isn't possible and Google can't help, let me know and
I'll get you references or a paper.
I found that the WisconsinDB wasn't large enough for my
puposes, so I increased it's size and padded out the records
until I was able to produce discernable differences in benchmark-like
tests I was running.
Rob
Joe Thurbon
I'm not aware of anything directly relevant. The following are maybe too
far away, but just in case.
For satisfying the database constraints, it might be worth checking up on
Constraint Satisfaction Problem/Programming (CSP).
As for generating data according to distributions, there's literature in
the Machine Learning space about doing this, but I'm afraid that I can't
remember the journal where I saw it most recently - maybe JML, maybe
JetAI, probably around 2005. A lot of the literature covers generating
vectors in n-space according to various distributions (gaussian, poisson,
etc) but I'm sure I've seen stuff around generating nominal values, too.
Search terms might be 'evaluation learning algorithms synthetic data'.
Sorry I can't be more specific.
Cheers,
Joe
Roy Hann
> On Oct 11, 5:54�am, Roy Hann <specia...@processed.almost.meat> wrote:
>> Can anyone point me towards any papers, articles or web pages that
>> discuss efficient techniques for generating large volumes of completely
>> synthetic database content having specified characteristics? I'd
>> settle for pointers to any software tools that might exist.
>>
>> I am not interested in generating mere random values; I want to
>> efficiently generate plausible/realistic values for multiple tables
>> and the "data" must satisfy my database constraints and have specified
>> distributions of key values.
>>
>> If it is relevant, assume I want to create databases for an SQL DBMS.
>>
>> Needless to say I've made a stab at Googling for what I want but I
>> haven't been able to guess effective search terms.
>>
>> --
>> Roy
>
> I needed to run some performance tests and used the Wisconsin
> Database as a starting point. If you go to ACM and search on
> David DeWitt (UWisconsin CompSci prof), you'll find references.
> If that isn't possible and Google can't help, let me know and
> I'll get you references or a paper.
his database. Unfortunately it is pretty much exactly what I don't
want. :-(
According to deWitt his "data" is generated using cyclic and random
functions for each attribute, and makes no attempt to recreate the
characteristics of a real database, with skewed distributions of key
values, and correlation and autocorrelation, and gaps.
--
Roy
Derek Asirvadem
> If it is relevant, assume I want to create databases for an SQL DBMS.
Well, if you have an SQL database, life is very simple. Most of my
clients are banks, and quite often we need to create a copy of the
PRODUCTION database for UAT purposes. It appears we have the same
need as you, in that we need really good, plausible/realistic values,
with FK data distributions matching PK values. Just write a few
scripts to *obfuscate* the Prod data, and thus eliminate customer
confidentiality issues, security issues, etc, so that the UAT team and
development team (who have no access to Prod, and no security
clearance) can view and use the obfuscated data without breaching any
legal or policy requirements.
The need for scripts is because questions such as (a) whether a column
needs to be obfuscate, (b) what type of obfuscation is required, has
to be made on a table-column basis, and (c) you can them to run in
batches, in parallel, for speed. Further, as your testing progresses,
and you identify issues, you (d) need to progress your scripts. Such
scripts are simple enough to write, no "product" is necessary. Our
scripts on the last project take about one hour to execute on a 1.2TB
database.
> and the "data" must satisfy my database constraints
Well assuming you means database, when you uses the word "database",
the data in the database will *always* satisfy the database
constraints, regardless of whether it is obfuscated or transformed or
hammered. (maybe you mean something else by that statement ?). You
*have* implemented the C in ACID, haven't you ? If you have not, then
it is not a database, it is a bucket of fish. In that case, you will
have to expend some effort transforming it into a database, before you
can expect database capabilities from it.
Regards
Derek
Roy Hann
> On Oct 11, 10:54�pm, Roy Hann <specia...@processed.almost.meat> wrote:
[Much good and clever advice for solving a different problem snipped.]
>> and the "data" must satisfy my database constraints
>
> Well assuming you means database, when you uses the word "database",
> the data in the database will *always* satisfy the database
> constraints, regardless of whether it is obfuscated or transformed or
> hammered. (maybe you mean something else by that statement ?). You
> *have* implemented the C in ACID, haven't you ? If you have not, then
> it is not a database, it is a bucket of fish. In that case, you will
> have to expend some effort transforming it into a database, before you
> can expect database capabilities from it.
I fear you may be somewhat dazzled by the clarity of your own vision of
the solution.
Recall I want to generate entirely synthetic data that satisfies my
constraints, and do it efficiently. SQL table definitions and
constraints are all I expect to have available. I do not expect to have
a real database (i.e. logically consistent set of real data).
I didn't spell it out, but I want to be able to use this technique to
create data for any arbitrary database design.
--
Roy
Erwin
Roy,
How far does the scope go of what you mean by "constraints" here ?
Really just any arbitrary constraint (such as "the sum of the
percentages in this table must add up to 100", and which are at best
enforced by trigger code buried inside the database definition, and
for which you are unlikely to have any machine-readable declarative
definition), or only the classes of constraint for which SQL has
declarative support ?
Derek Asirvadem
>
> I fear you may be somewhat dazzled by the clarity of your own vision of
> the solution.
(Groan)
Well, the prospects are always brighter when one knows what one wants,
then people who feel inclined to help you can actually do so. But
never mind, we will drag it out of you, no matter what it takes. We
do not have crystal balls, so it may take a bit of to-ing and fro-ing.
1. Exactly what does "synthetic" mean ? complete rubbish ? any alpha-
numeric character in a CHAR column ? Printable/unprintable ?
- what "values" do you want, that will satisfy your "constraints"
- do you have CHECK constraints that specify data value ranges ?
- Note Erwin's question. Even simpler constraints are as yet
unidentified. Exactly what are these "constraints", if they allow
synthetic rubbish into the "database" ?
- You did not answer, have you implemented, do you want, the ACI in
ACID (the D is supplied by the DBMS) ? The answer will tell me a lot
about what you are seeking.
2. Since we know now, that "plausible/realistic" does not mean
plausible/realistic, perhaps you would be kind enough to tell us what
that means to you. And why you have the word data in quotation marks
(it is all data, synthetic or otherwise, no ?). Please be specific,
we need to know the distinction between "synthetic/plausible/
realistic" and real/example/source.
3. Perhaps you can post your "specified characteristics" to which the
data must conform. If you want the population mechanism to be driven
by this set of control tables, that would be even better.
The next bit has to do with your SQL expertise. Rather than ask
questions and receive tidbits of information, allow me give an
example. Since it is from my real life, no doubt it will be
dazzling. I realise it is not what you want, because we are clueless
re what that is (maybe you and I have something in common there,
eh ?), but it is offered as a discussion, in the hope that it may
improve your ability to specify your exact requirement. At the least,
it will, once again, confirm what you *don't* want, and and we will
progress one small but light-filled step.
Stress testing situation. I needed a full database. With specific
ranges of data distribution. I did not wish to go through the
paperwork of obtaining a copy of Prod, besides, it was a new version
of the db with about 200 new columns. I wrote a few SQL scripts to
load each table (Customer; then OrderSale from Customer; then
OrderSaleItem from OrderSale; etc). I split them into parallel
sessions, and wrote a couple of shell scripts to manage the lot. I
executed them. In a short time, the scripts finished. I ran a couple
of sprocs that produce inventory information, stats, etc, and checked
populations. data distribution, etc. I was bedazzled by my own
brilliance, the database was full, as planned. No posting of
international websites asking daft questions that only I can answer;
no products; no freeware; no reading papers.
4. Do you have the expertise to do all that (except the bedazzled
bit, of course) ?
5. Are there any steps in that, that may be relevant or irrelevant to
you ?
6. Probably the most important, in the scripting dept, do you know
how to use vectors, and perform projections ?
Anyway, contemplate that for a while, and see if you can provide a few
more words specifying exactly what you want, in the creation of
"synthetic but plausible/realistic" "data" that "satisfies
constraints" but nothing else. We will delight in whatever scraps of
information we receive from you, and we will jump at the chance of
dragging a few more scraps out of you tomorrow.
Roy Hann
> How far does the scope go of what you mean by "constraints" here ?
>
> Really just any arbitrary constraint (such as "the sum of the
> percentages in this table must add up to 100", and which are at best
> enforced by trigger code buried inside the database definition, and
> for which you are unlikely to have any machine-readable declarative
> definition), or only the classes of constraint for which SQL has
> declarative support ?
In some far future day I can see how it would be nice to have a tool
that could extract the declared constraints from the database
catalogues, but that is not an immediate need.
For the present purpose I would be content to specify the constraints
"by hand", so in principle I expect to be able to specify general
constraints along with, say, referential constraints.
--
Roy
Derek Asirvadem
On Oct 14, 6:40 pm, Roy Hann <specia...@processed.almost.meat> wrote:
>
> In some far future day I can see how it would be nice to have a tool
> that could extract the declared constraints from the database
> catalogues
No need to wait. We have had it since 1987. The ANSI/ISO/IEC SQL
Standard requires a catalogue, that can be perused using SQL. Getting
info out of the catalogue requires a few lines of SQL code.
Generating scripts that populate tables can be done quite easily, not
counting the varying number of lines of the varying columns, it takes
about ten lines of SQL code. Adding the complexity of varying
columns, datatypes, etc is another 12 lines, plus a vector of 35
lines, one per datatype. Now add your mythical table which
"constraints" the "data" to "synthetic", four more lines, and you are
done.
Silly me, I took for granted that you could do that. I dazzle myself
so much, I just assumed other people would be just as dazzling. I
should add:
7. If you were given full documentation of the catalogue, from the
definitions in the catalogue, can you write SQL scripts, to generate
SQL scripts which load tables ? We do not really want to write 200
scripts for 200 tables, do we, we want one script that generates 200
scripts.
> I would be content to specify the constraints "by hand"
Please do not duplicate the definitions in the catalogue, "by hand" or
by ink or by LED. That will introduce errors, and the possibility of
contradictory "constraints", where there were none. All of which can
be easily avoided, as long as you refrain from the hand jobs, and just
keep all your definitions in One Place. For the constraints that are
not constraints, please put them all in one non-constraint or non-data-
range table. I have enough trouble staying with this thread as it is.
We could design a GUI (Graphical User Interface) with buttons and
slides, much like an audio synthesiser, then you could just slide the
controls and synthesise any mix of "data" that you like, until the
beast sounds plausible/realistic. You know, ease up on the bass,
create a special mix for small speakers. We could have one for no-
speakers. But from the sound of it, that may be a bit of over-
engineering, no need to break the crystalware when we have nothing to
pour into it.
Roy Hann
> On Oct 14, 2:48�am, Roy Hann <specia...@processed.almost.meat> wrote:
>>
>> I fear you may be somewhat dazzled by the clarity of your own vision of
>> the solution.
>
> (Groan)
>
> Well, the prospects are always brighter when one knows what one wants,
> then people who feel inclined to help you can actually do so. But
> never mind, we will drag it out of you, no matter what it takes. We
> do not have crystal balls, so it may take a bit of to-ing and fro-ing.
[Lots snipped]
> Anyway, contemplate that for a while, and see if you can provide a few
> more words specifying exactly what you want, in the creation of
> "synthetic but plausible/realistic" "data" that "satisfies
> constraints" but nothing else. We will delight in whatever scraps of
> information we receive from you, and we will jump at the chance of
> dragging a few more scraps out of you tomorrow.
Perhaps I deserve this kind of response, but I will repeat my initial
request, which in all honesty seems pretty unobjectionable to me:
"Can anyone point me towards any papers, articles or web pages that
discuss efficient techniques for generating large volumes of completely
synthetic database content having specified characteristics?"
Once I have found such resources, read them, and familiarized myself
with the state-of-the-art and indeed the established terminology, I may
well come back here with questions in which I will provide all the
information anyone could want. But for now I just want suggestions for
reading.
--
Roy
Roy Hann
> A tidbit !
>
> On Oct 14, 6:40�pm, Roy Hann <specia...@processed.almost.meat> wrote:
>>
>> In some far future day I can see how it would be nice to have a tool
>> that could extract the declared constraints from the database
>> catalogues
>
> No need to wait. We have had it since 1987. The ANSI/ISO/IEC SQL
> Standard requires a catalogue, that can be perused using SQL.
Yes, I am aware of that and I clearly wrote "the declared constraints
*from* the database catalogues". Furthermore I was addressing Erwin's
very reasonable query about declared versus assumed constraints.
I don't mind you asking for more clarity but I do mind you not paying
attention to what detail I have given and the context in which I've
given it.
> Generating scripts that populate tables can be done quite easily, not
> counting the varying number of lines of the varying columns, it takes
> about ten lines of SQL code. Adding the complexity of varying
> columns, datatypes, etc is another 12 lines, plus a vector of 35
> lines, one per datatype. Now add your mythical table which
> "constraints" the "data" to "synthetic", four more lines, and you are
> done.
I'm not looking for recipes. I am looking for research that goes beyond
naive recipes; that's why I posted to a theory group.
--
Roy
Bob Badour
I am unaware of anything, but it's not like I have been looking for
anything like that.
Roy Hann
> I am unaware of anything, but it's not like I have been looking for
> anything like that.
Thanks Bob. If there isn't much to find that would explain why I'm not
finding it.
--
Roy
Derek Asirvadem
Ok.
So you are looking for state-of-the-art, and papers, on generating
completely synthetic data of specified characteristics. I still have
no idea what that means , but I think I have the title right. You
think that is worth a paper. I have never heard of one (I did spend
a bit of time looking for specific papers last year, although not on
that subject). Frankly, the task is so pedestrian, that I do not
think it is worth researching, I do not think you will find such
papers. The state of that art is in shell, awk and SQL, your ability
to design and code; it has not changed for about 25 years. Even the
productised scripts are cheap, $800 for a full set.
You might have more luck looking at the way the more established ETL
products are architected, their strategies.
Maybe one of the writers in the MS stable, they have huge research
centres that produce mountains of papers that are not worth reading;
no peer reviews, just publication. Snodgrass et al. Many are
available free on the web, unlike the academic, peer-reviewed papers.
Roy Hann
> [...] I still have no idea what that means [...].
> [...] I do not think it is worth researching.
So which is it? You don't know what I mean, or you do know what I mean
and it isn't worth researching?
I enjoy seeing batty ideas pierced with brilliantly economical
arguments as much as the next denizen of c.d.t. There are several
masters of that sport hanging out here. You show eagerness though
no particular skill. Observe the masters and learn. I shall watch
your development with interest.
--
Roy
Derek Asirvadem
1. Evidently you have forgotten the chronology of your actions, and
failed relate the consequences, back to them
> On Oct 14, 2:48 am, Roy Hann <specia...@processed.almost.meat> wrote:
> the solution.
You can dish it out, but you can't swallow it. If you can't swallow
it, don't dish it out.
Change the tone, you may get different consequences.
2.
> > [...] I still have no idea what that means [...].
> > [...] I do not think it is worth researching.
>
> So which is it? You don't know what I mean, or you do know what I mean
> and it isn't worth researching?
speculate on subjects *within* their field, that they may not be
*specialist* in, we do not need your permission.
Also, you have completely missed the point (that I detailed) that the
subject for which you seek papers, now that we know a tiny bit about
what it is, is so pathetic, that no self-respecting educated adult
would write a paper on it, excepting (as detailed) MS research
fellows, but they enjoy neither respect nor education.
Since you can only contemplate binary logic; you have been unable to
comprehend the whole post, and instead, you excise and inspect single
statements from it, let me spell it out for you. I note that the body
of my post fleshes out, and is consistent with, the second advice, so
go with that. To be clear, I have no idea what your *specific*
requirement is, but the *general* subject identified thus far, is not
worth researching *or* writing a paper on. Please do not break that
last sentence up, it will confuse you.
> I enjoy seeing batty ideas pierced with brilliantly economical
> arguments as much as the next denizen of c.d.t. There are several
> masters of that sport hanging out here. You show eagerness though
> no particular skill. Observe the masters and learn.
advice seriously. Until then, since you do not practice what you
preach, you are a hypocrite, which identifies the exact level of your
credibility. I couldn't care less what hypocrites think of me, or
what in-credible opinions they post.
I am not here to show off my skill (answering the question required no
technical skill, only reining in my laughter), or to gain acceptance
on a public forum (I get enough acknowledgement from my customers and
peers, thank you), or to find out about what the undocumented
requirements for acceptance are, or to take notice of your opinion of
any of the above, particularly given your demonstrated (a) credibility
and (b) technical skill. I am here to participate in a community.
Evidently you do not recognise either the batty idea, even after it is
pointed out to you, or the piercing of it. I will not be writing a
paper on the subject.
You have failed to notice that the "masters" did not bother to respond
to your question, which was devoid of theoretical substance. I
bothered to, after a couple of days. You had to explain to us, how
you positioned the question, so as to give it a theoretical element.
That is consistent with the demonstrated hypocrisy. There is nothing
in your question that is related to c.d.t; and you have posed a non-
specific question which cannot be satisfied; you have rejected all
responses, nothing is good enough. Now you can prove, in your own
cranial space, that c.d.t can't answer questions. Your game is
transparent.
Bob Badour
> Roy
>
> 1. Evidently you have forgotten the chronology of your actions, and
> failed relate the consequences, back to them
>
>>On Oct 14, 2:48 am, Roy Hann <specia...@processed.almost.meat> wrote:
>
>>I fear you may be somewhat dazzled by the clarity of your own vision of
>>the solution.
>
> You set the tone. Deal with it.
Vadim and Marshall. I haven't seen any of them in a while.
If you are here to have a dick-swinging contest, forget it. Roy's is
obviously way bigger if a gentle jibe like the above sends you
completely off the rails.
<snip>
Erwin
>
> I'm not looking for recipes. I am looking for research that goes beyond
> naive recipes; that's why I posted to a theory group.
>
> --
> Roy
When I built my generic testDB construction tool (for the IDMS system,
not an SQL one), I just had to tackle the problems one by one as they
came about.
So all in all, all I'll have to offer are the ideas and concepts
underpinning what I built at the time. A lot of those do carry over
to SQL systems, but they still remain little more than "recipes" for
an actual solution.
Sorry.
Derek Asirvadem
>
> If you are here to have a dick-swinging contest, forget it. Roy's is
> obviously way bigger if a gentle jibe like the above sends you
> completely off the rails.
uncle used to do that, but after a while it dragged on the ground,
which lead him to scream out for most of the night. His wife left him
for another man who screamed less. So I have never been attracted to
the activity. Besides, given the bedazzlement here, if I did, some
people would surely go blind.
Tone appears to be a sensitive issue to you, you must have
misunderstood my reference to it. I don't give a hoot either. I was
explaining *to Roy* that it is hypocritical to dish it out and then
squeal when others return the favour. The point was "don't dish it
out if you can't swallow it", not "watch your tone".
Regards
Derek
paul c
involved, eg., full support for negation and union, unlike SQL, and
the constraints would need to be expressed algebraically, but I doubt
there is much demand for such.
Once I had to deal with an add-on db that could interface with other
dbs, such as IMS. The problem was to fit the system on a single
mainframe instead of the four it needed as written. This was at a
very large government installation of an ancient constitutional
monarchy. The system recorded various stuff about broken families.
There was all kinds of security involved because every 'case' was
considered confidential to the families and the case worker assigned
to them, plus extra steps were taken for prominent families, with
labels such as 'locally secure' and 'nationally secure', eg., several
heirs to the throne had divorced parents. I needed to get some kind
of profile of the IMS db, volumes by segment type and so forth, and
the non-union bureaucrats told me they had a test db that matched the
volumes of the production one but with mangled data. They asked the
unionized operations people to make a mainframe available to me for
sampling purposes. After a couple of days I was kicked off because
they needed the cpu back. I thanked one of the operations guys for
their trouble and said I hoped it wouldn't take long to restore the
production data. He laughed, said it wasn't much trouble because they
didn't bother to set up the mangled data, I had been using a
production copy all along. Any complaint by the senior civil servants
would have been stymied by threats of job action.
Bob Badour
cannot take what he dishes out, I can only conclude you have problems
comprehending written English.
plonk!
-CELKO-
to distribution, etc.
http://www.red-gate.com/products/sql-development/sql-data-generator/
http://downloads.red-gate.com/HelpPDF/DatabaseUnitTestingWithRedGateSQLTools.pdf
Derek Asirvadem
Relax, mate. You do not need to tell me how to think, or how to
interpret the posts of others.
If you have some issue with me, I would prefer if you engaged me
directly, rather than speaking on someone else's behalf, purporting to
know how *they* think, feel, and react.
Cheers
Derek
Roy Hann
> On 14 okt, 12:11, Roy Hann <specia...@processed.almost.meat> wrote:
>>
>> I'm not looking for recipes. I am looking for research that goes beyond
>> naive recipes; that's why I posted to a theory group.
> When I built my generic testDB construction tool (for the IDMS system,
> not an SQL one), I just had to tackle the problems one by one as they
> came about.
>
> So all in all, all I'll have to offer are the ideas and concepts
> underpinning what I built at the time. A lot of those do carry over
> to SQL systems, but they still remain little more than "recipes" for
> an actual solution.
years. If there are no better approaches written about, then at least
I've made a reasonably good stab at proving that's the best I can do.
--
Roy
Roy Hann
> The point was "don't dish it
> out if you can't swallow it", not "watch your tone".
interested to see how long you will keep it up.
--
Roy
Erwin
>
> how long you will keep it up.
>
> --
> Roy
passed the stage as well ...
Roy Hann
--
Roy