Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

How to create a 1M record table with a single query

antonz.org

Post reply on HN
Print view View original

21–30 of 40 posts

Re: How to create a 1M record table with a single query

#21

Post by dspillett »

alexf95 wrote: post #6

Can someone give me a specific use case for this? As "check how a query behaves on a large table" is very vague to me. E.g. I have a table structure but not alot of rows in it, so i go use this to get alot of rows in to check how fast queries get processed?

Generating test data batches like this is a common use.

The other is for populating "numbers tables" which can be very useful in reporting contexts and/or dealing with certain gaps/islands type problems.

You can even use the techniques dynamically rather than a stored numbers table, if you need it in a DB that doesn't have one and you don't have schema permissions to create one, though this is generally less efficient.

Re: How to create a 1M record table with a single query

#22

Post by deepstack »

cribwi wrote: post #4

If you're running PostgreSQL, you can use the built-in generate_series (1) function like: SELECT id, random() FROM generate_series(0, 1000000) g (id); There seems to be an equivalent in SQLite: https://sqlite.org/series.html [1] https://www.postgresql.org/docs/current/functions-srf.html

use a sequence or uuid

Re: How to create a 1M record table with a single query

#23

Post by zikani_03 »

If you need another repeatable way to create random data that you can export as SQL (or CSV/Excel files). You may find a tool we built and use at work useful: https://github.com/creditdatamw/zefaker

Needs a little Groovy but very convenient for generating random (or non-random) data.

Re: How to create a 1M record table with a single query

#25

Post by schoetbi »

cribwi wrote: post #4

If you're running PostgreSQL, you can use the built-in generate_series (1) function like: SELECT id, random() FROM generate_series(0, 1000000) g (id); There seems to be an equivalent in SQLite: https://sqlite.org/series.html [1] https://www.postgresql.org/docs/current/functions-srf.html

you have to use it since PostgreSQL does not support limit within a with expression.

Re: How to create a 1M record table with a single query

#26

Post by openquery »

This looks convenient (and performant). But how does it scale as queries join across tables?

If you need to create test data with complex business logic, referential integrity and constraints we've been working on declarative data generator that is build exactly for this: https://github.com/openquery-io/synth.

Re: How to create a 1M record table with a single query

#28

Post by psing »

alexf95 wrote: post #6

Can someone give me a specific use case for this? As "check how a query behaves on a large table" is very vague to me. E.g. I have a table structure but not alot of rows in it, so i go use this to get alot of rows in to check how fast queries get processed?

Creating realistic fake data is useful in lower environments and for load testing. Outside of SQL I like faker: https://github.com/joke2k/faker

Re: How to create a 1M record table with a single query

#29

Post by jeffbee »

cribwi wrote: post #4

If you're running PostgreSQL, you can use the built-in generate_series (1) function like: SELECT id, random() FROM generate_series(0, 1000000) g (id); There seems to be an equivalent in SQLite: https://sqlite.org/series.html [1] https://www.postgresql.org/docs/current/functions-srf.html

If you're running PostgreSQL you can also just

  xxd -ps -c 16 -l $bignum
Depending on how you want your random keys formatted.

Re: How to create a 1M record table with a single query

#30

Post by em500 »

jeffbee wrote: post #29
cribwi wrote: post #4

If you're running PostgreSQL, you can use the built-in generate_series (1) function like: SELECT id, random() FROM generate_series(0, 1000000) g (id); There seems to be an equivalent in SQLite: https://sqlite.org/series.html [1] https://www.postgresql.org/docs/current/functions-srf.html

If you're running PostgreSQL you can also just xxd -ps -c 16 -l $bignum Depending on how you want your random keys formatted.

If you're importing from command line tools, you might as well use the specialized `shuf` tool:

  shuf -i 1-$bignum
or for random numbers with replacement

  shuf -i 1-$bignum -r -n $bignum
These give you a sample of random integers. `shuf` is part of GNU coreutils, so present in most Linux installs (but not on macOS).
Post reply on HN