Hand History Database (One billion hands for Research)

BlueSilicon · **Posts:** 447 **Favourite Bot:** Mine, of course!

Indiana wrote:

@Trash - make a proof of concept that you can store and handle 5 billion hands in DB (retaining full information about the hands). If you don't optimize the way I did (in the proprietary format) you might end up easily with 10 terabytes of disk space needed just to store that on disk.

Your layout of the DB is irrelevant for important scenarios for which I am interested in. Indeces and layout would help you to filter out hands easily (which I can do as well), but in scenarios where you need to really consider every single hand that was played - you have no other option than to read it all, all the 5Tb data on disk. Now consider how much is just the time to read 5Tb once from the disk - and find the minimum time to execute your query (assuming RDBMS overhead is zero). The proprietary format that I have allows me to shrink the neccessary reading to less than 100G (which is still a lot, but with under 1k$ you can get this on a SDD or even alternative disks with read speed upto 1G/sec, so at least making you query in the range of 1-2 minutes). And on a normal HDD - 10-15 minutes.

I have 2 million hands in a completely normalized SQL database. With indexes, so that I can actually query the data out quickly, it takes up 4 GB of space on the disk. So, 5 billion hands will take me 12 TB of space. If I turn on database compression, which SQL 2008 has, it reduces down to 1.6 GB so for 5 billion hands that would be about 4 TB of compressed database.

For research, there really isn't anything you can learn from 5 billion hands that you can't learn from 5 million hands.

indiana · **Posts:** 7622 **Favourite Bot:** V12

BlueSilicon wrote:

For research, there really isn't anything you can learn from 5 billion hands that you can't learn from 5 million hands.

I diasgree. For example, if you do research in collusion detection, you need many hands of many players, and 5 million is certainly not enough. And many other cases actually.

BlueSilicon · **Posts:** 447 **Favourite Bot:** Mine, of course!

@indiana: Ok, I was only thinking as far a AI research. Yes, for some stuff the relevant data may be rare so you would need lots of it to find valid data for the research.

abanger · **Posts:** 49 **Favourite Bot:** Hyperborean

indiana wrote:

Is this pre-rake or post-rake :?:

indiana · **Posts:** 7622 **Favourite Bot:** V12

Post rake (so rake has been taken). And pre-rakeback

indiana · **Posts:** 7622 **Favourite Bot:** V12

Checkout http://pokerftp.com

Nothing is yet available for download, but preliminary feedback is welcome.

indiana · **Posts:** 7622 **Favourite Bot:** V12

Sample DB plus the software to read it is now available:
http://pokerftp.com/index_files/general.htm

indiana · **Posts:** 7622 **Favourite Bot:** V12

New example added

abanger · **Posts:** 49 **Favourite Bot:** Hyperborean

Can you do a breakdown of Percentage of winning players, but instead of by # of hands played, would you mind doing it by hour (time of day)? I've always wanted a definitive answer to the question, "What's the 'best' time to play?" Thanks :!:

indiana · **Posts:** 7622 **Favourite Bot:** V12

You won't get huge difference by hour because timezones are mixed up. I can give this a try if I have some time left.

But now you all have the software. You can just code what you want to see, send it to me, and I will run it against the DB.

indiana · **Posts:** 7622 **Favourite Bot:** V12

CodeD · **Posts:** 331 **Favourite Bot:** code: d

Interesting...very interesting...

btw, do you use any java library to create the charts (jfreechart?) or how do you make them?

indiana · **Posts:** 7622 **Favourite Bot:** V12

All examples I include in the distribution. Basically I dump something that I copy/paste to Excel and then do the chart.

abanger · **Posts:** 49 **Favourite Bot:** Hyperborean

indiana wrote:

You won't get huge difference by hour because timezones are mixed up. I can give this a try if I have some time left.

But now you all have the software. You can just code what you want to see, send it to me, and I will run it against the DB.

Are you giving this a try? Or shall I attempt to code?

indiana · **Posts:** 7622 **Favourite Bot:** V12

I would really like if you give a try and code whatever example you want to have executed. Then I'll just run it against the full DB.

Master · **Posts:** 7 **Favourite Bot:** dunno

i kinda have a doubt... do you mean one billion hands like table hands or single hands? for example one hand that pokertracker imports is both one hand or 6 hands, if 6 players were involved in it. its just to clarify

aiaiai · **Posts:** 2264 **Favourite Bot:** My next one

Master wrote:

you mean one billion hands like table hands or single hands?

One hand = one observed table hand.

indiana · **Posts:** 7622 **Favourite Bot:** V12

Yes, each hand is imported once, not for each player that participated.
Every imported hand has unique (different) game ID, so we have 1 billion hands, all with unique game ids.

c2008 · **Posts:** 732 **Favourite Bot:** Johnny #5

One small suggestion... Get someone that speaks English well to edit pokerftp.com. :xx20

Xavier · **Posts:** 147 **Favourite Bot:** khan

Imo, you should have a section with a list of people who were granted access to the DB and papers published as a result. Of course they will eventually appear.

Hand History Database (One billion hands for Research)

Who is online