HMQL, Custom Stats, Opponents Tab, Other Questions

[insi]

Hello,

this is gonna be a long post, I'm trying to split up the topics as clearly as possible.

I'm currently writing my bachelor thesis on poker AIs where I analyze thats like VPIP, PFR, AF etc.

I am using (or rather trying to use) both PT4 and HEM1/2 however I've run into quite a few problems. First of all my "specs":

- Database of roughly 21 million NLHE cash game hands from PartyPoker
- PC specs: i5 3570k @ 4.4 GHz, 8GB RAM, SSD and fast HDD available, Win 7 Pro
- PostgreSQL Version 9.2

Import Speeds:
I was able to get my database imported in HM2 without any bigger problems and it went quite fast with 200 to 500 h/s. Importing in HM1 is WAY slower, even with database tweaks (buffers, segment sizes etc) - roughly 50-100 h/s, mostly 50 h/s though. Do I just have to accept the fact that its way slower or is there any other way to speed up the process? PT4 has similar speeds as HM1 when I placed the DB on a SSD, whereas HM1 DB was on my HDD (I know this is the HM forum). Importing my DB into HM1 probably takes one or two weeks.

PostgreSQL DB and Stats:
I have inspected the PGSQL DB for both HM1, HM2 and PT4 and obviously they're vastly different (duh..) - I have read several threads about how HM2s DB is split up between a PGSQL part and a file part located in the DB parent folder under \Players\...\playername\files...

As far as I understand HM2 saves the HHs in an extremely compressed format for each player in the players folder. Each day has its own file as well. I tried to reverse engineer information out of it by comparing a player with a single hand recorded in the DB, but the values are just as confusing.

I am interested in stats, or rather raw numbers like:

- Opportunies to do X (raise, call, fold, check...) which are not present in the HM2 DB unless I did not manage to find it (I only saw totalbets, totalcalls, but no totalraises for example)

- Raw number of X (r, c, f, x, 3bet etc...) where the player actually DID X (in contrast to opportunity)

- the HM1 had such values, and for each position to boot, in the compiledplayerresults_month table or the PFA table

Is there any documentation on what exactly is logged in these files? I know that something similar (HMQL API) has been requested a countless number of times, but I don't really have the time to wait for a complete documentation as I need to finish my thesis soonish. Hints alone would help me greatly - and I highly doubt that your programmers have not at least taken SOME kind of notes on how the hand histories are compressed or what kind of information these files actually consist. Are any of the above stats in the player files, if yes which of them are they?

Unlike HM2, HM1s DB actually contains these values (although quite split up between a few tables), so HM2 has to save these values somewhere as well if it provides the same stat functionality. I dont believe it calculates everything "on the fly".

HMQL:
I have seen screenshots of a HM2 beta(?) client with a implemented HMQL tab (although I dont remember seeing anything on it). I know there are ticket requests for HMQL etc and I know that the documentation isn't complete yet, but I would be really happy if I could get maybe an early access or hints on how to use it. I have already applied for beta testing, but I haven't recieved an answer yet (and I'm not sure if I qualify as you guys seem to look for players). I've also mailed the support two days ago for another problem but I have not yet recieved an answer to that either (why so slow? ).


What I want:
I want to create an "Opponents Tab"-like view of all players that I can export to Excel or as a CSV file. I need custom stats which is the reason why the opponents tab isn't enough. I have read countless threads on ppl wanting custom thats or have stat X added, but as far as I can see there has been no real change as of yet. Even one of the most important bb/100 or BB/100 stats have not yet been implemented which is quite sad.
I could also create the view externally via SQL queries, however as HM2 uses a split up database approach this isn't really easy to do as some information isn't stored in the DB.


I know that the forum guys here will probably not be able to help me as this is quite advanced, so I'd be glad if I could get a direct contact with a HM2 developer or something. I could help in beta-testing HMQL.

Kind Regards
Andreas

[fozzy71]

Hello,

this is gonna be a long post, I'm trying to split up the topics as clearly as possible.

I'm currently writing my bachelor thesis on poker AIs where I analyze thats like VPIP, PFR, AF etc.

I am using (or rather trying to use) both PT4 and HEM1/2 however I've run into quite a few problems. First of all my "specs":

- Database of roughly 21 million NLHE cash game hands from PartyPoker
- PC specs: i5 3570k @ 4.4 GHz, 8GB RAM, SSD and fast HDD available, Win 7 Pro
- PostgreSQL Version 9.2

Import Speeds:
I was able to get my database imported in HM2 without any bigger problems and it went quite fast with 200 to 500 h/s. Importing in HM1 is WAY slower, even with database tweaks (buffers, segment sizes etc) - roughly 50-100 h/s, mostly 50 h/s though. Do I just have to accept the fact that its way slower or is there any other way to speed up the process? PT4 has similar speeds as HM1 when I placed the DB on a SSD, whereas HM1 DB was on my HDD (I know this is the HM forum). Importing my DB into HM1 probably takes one or two weeks.

It sounds like you have done all you can. HM1 simply imports slower than HM2 due to the way they were designed. In our testing HM1 imports considerably faster than PT4.

PostgreSQL DB and Stats:
I have inspected the PGSQL DB for both HM1, HM2 and PT4 and obviously they're vastly different (duh..) - I have read several threads about how HM2s DB is split up between a PGSQL part and a file part located in the DB parent folder under \Players\...\playername\files...

As far as I understand HM2 saves the HHs in an extremely compressed format for each player in the players folder. Each day has its own file as well. I tried to reverse engineer information out of it by comparing a player with a single hand recorded in the DB, but the values are just as confusing.

I am interested in stats, or rather raw numbers like:

- Opportunies to do X (raise, call, fold, check...) which are not present in the HM2 DB unless I did not manage to find it (I only saw totalbets, totalcalls, but no totalraises for example)

- Raw number of X (r, c, f, x, 3bet etc...) where the player actually DID X (in contrast to opportunity)

- the HM1 had such values, and for each position to boot, in the compiledplayerresults_month table or the PFA table

Is there any documentation on what exactly is logged in these files? I know that something similar (HMQL API) has been requested a countless number of times, but I don't really have the time to wait for a complete documentation as I need to finish my thesis soonish. Hints alone would help me greatly - and I highly doubt that your programmers have not at least taken SOME kind of notes on how the hand histories are compressed or what kind of information these files actually consist. Are any of the above stats in the player files, if yes which of them are they?

Unlike HM2, HM1s DB actually contains these values (although quite split up between a few tables), so HM2 has to save these values somewhere as well if it provides the same stat functionality. I dont believe it calculates everything "on the fly".

HMQL:
I have seen screenshots of a HM2 beta(?) client with a implemented HMQL tab (although I dont remember seeing anything on it). I know there are ticket requests for HMQL etc and I know that the documentation isn't complete yet, but I would be really happy if I could get maybe an early access or hints on how to use it.

This has not been documented publicly yet. I will forward it to the tech support manager so he can ask the developers if they would be willing to share the existing internal documentation but I can't promise that they will be willing to do this at this time.

There are a few examples in this post/thread - http://forums.holdemmanager.com/feat...ml#post1509441

I have already applied for beta testing, but I haven't recieved an answer yet (and I'm not sure if I qualify as you guys seem to look for players).

I've also mailed the support two days ago for another problem but I have not yet recieved an answer to that either (why so slow? ).

Brent replied to you and I see you just responded via email again. I will forward your most recent email and a link to this thread to the tech support manager so he can ask the developer about your questions as they are beyond us on the front lines of tech support.

What I want:
I want to create an "Opponents Tab"-like view of all players that I can export to Excel or as a CSV file. I need custom stats which is the reason why the opponents tab isn't enough. I have read countless threads on ppl wanting custom thats or have stat X added, but as far as I can see there has been no real change as of yet. Even one of the most important bb/100 or BB/100 stats have not yet been implemented which is quite sad.
I could also create the view externally via SQL queries, however as HM2 uses a split up database approach this isn't really easy to do as some information isn't stored in the DB.


I know that the forum guys here will probably not be able to help me as this is quite advanced, so I'd be glad if I could get a direct contact with a HM2 developer or something. I could help in beta-testing HMQL.

Kind Regards
Andreas

I think the HMQL report will do what you need but I can't be sure as it is beyond me. We have an open request to add some more stats to the Opponents tab (bb/BB per 100 hands, etc) but I am not sure when that will be realized.

[insi]

Thanks for your reply!

I've already found the mentioned custom hud thread and tried out some things, but as it stands I have not managed to pull data similar to the opponents tab. I can only get data for the currently selected player and not for all players in the database - if thats possible with HMQL I'm very interested to know how.

As for the beta request Brent and Denise have already replied so thats "solved".

Another thing which came up was the comparison between PT4 and HEM2s DB. PT4 has a functionality to convert a HEM2 DB to a PT4 DB. The interesting thing is: the PT4 DB has table columns for cnt_f_raise for example (count flop raises). I was wondering how and where exactly does PT4 extract this information out of the HEM2 DB as I could not find similar entries. Are they stored externally or did I just not look hard enough? I don't think PT4 accesses the original hand histories so there must be SOME way to get the raw counts of actions and opportunities from the database. Could you forward this question as well? Thanks in advance!


I'm looking forward to hearing from the tech support manager and/or the developers.

[fozzy71]

Thanks for your reply!

I've already found the mentioned custom hud thread and tried out some things, but as it stands I have not managed to pull data similar to the opponents tab. I can only get data for the currently selected player and not for all players in the database - if thats possible with HMQL I'm very interested to know how.

As for the beta request Brent and Denise have already replied so thats "solved".

Another thing which came up was the comparison between PT4 and HEM2s DB. PT4 has a functionality to convert a HEM2 DB to a PT4 DB. The interesting thing is: the PT4 DB has table columns for cnt_f_raise for example (count flop raises). I was wondering how and where exactly does PT4 extract this information out of the HEM2 DB as I could not find similar entries. Are they stored externally or did I just not look hard enough? I don't think PT4 accesses the original hand histories so there must be SOME way to get the raw counts of actions and opportunities from the database. Could you forward this question as well? Thanks in advance!


I'm looking forward to hearing from the tech support manager and/or the developers.

I will forward this to the tech support manager so he can discuss it with the developers.

p.s. I think that PT4 extracts the original hands from the handhistories table in postgresql and imports those to their database to create their own database schema.

[fozzy71]

I will forward this to the tech support manager so he can discuss it with the developers.
......

Replied via email.