Skip to content

Instantly share code, notes, and snippets.

@angelozehr
Last active Jul 29, 2021
Embed
What would you like to do?
set up UTS PostgreSQL Database

Roger Federer

Preprocessing: Restore Database from backup file

How to read in Ultimate Tennis Statistics data with Postgres on Mac OS

  1. Download and Install Postgres.app:
    https://postgresapp.com/de/ or install Postgres on linux: https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-16-04

  2. Download and Install PgAdmin

  3. Mac: Start Postgres.app and Initialize

  4. Start PgAdmin

  5. Add localhost as a server by clicking on Server > Create > Server...

  6. Chose localhost as name and as Host name (second Tab, no password required)

  7. Create user/role tcb by expanding the PostgreSQL 9.6 and right clicking on Login/Group Roles

  8. Create database tcb and assign to role tcb

  9. When tcb data base is set up, right click on extensions and create extension pg_trgm.

  10. Then right click on database tcb and Restore

  11. chose ultimatetennisstatistics.backup from preprocessing/input folder and role tcb

  12. Start restore process (takes about 5 minutes to finish)

User main.Rmd in preprocessing which has RPostgreSQL package installed for analysis.


Create new backup file from scratch

This is only executable on windows

  1. You need to have Java JDK 1.8 installed on the machine.

  2. You need PostgreSQL 9.5+ (9.6 preferable)

  3. Install PgAdmin and run it.

  4. As PostgreSQL admin user, create PostgreSQL login tcb with password tcb

  5. As PostgreQL admin user, create PostgreSQL extension: CREATE EXTENSION pg_trgm

  6. Change to directory ...\github\>

  7. You need to clone UTS Git repo https://github.com/mcekovic/tennis-crystal-ball: git clone https://github.com/mcekovic/tennis-crystal-ball.git

  8. You need to clone Jeff Sackmann ATP data git repo https://github.com/JeffSackmann/tennis_atp to some local directory also: git clone https://github.com/JeffSackmann/tennis_atp.git

  9. Correct two data errors in tennis_atp in files:
    atp_rankings_00s.csv, atp_rankings_10s.csv search/replace: "bioTableWrap bioTableWrapAlt"">" -> "1" "20160613,1709,,2" -> ""

  10. Then you build the UTS by going to UTS local source directory and running: gradlew.bat assemble

  11. Unzip content of data-load\build\distributions\data-load-1.0-SNAPSHOT.zip that is created by step 6

  12. Cd to ...\github\tennis-crystal-ball\data-load\build\distributions

  13. change file data-load-1.0-SNAPSHOT\bin\data-load.bat by changing / adding to line 17: "-Dtcb.db.connections=15" "-Dtcb.data.base-dir=...github\tennis_atp"'

  14. Run database schema creation: data-load-1.0-SNAPSHOT\bin\data-load.bat -cd

  15. Run database schema creation: data-load-1.0-SNAPSHOT\bin\data-load.bat -lt

  16. Run data load (you need Internet connection during the data load to fetch additional data from ATP web site): data-load-1.0-SNAPSHOT\bin\data-load.bat -Dtcb.db.connections=15 -Dtcb.data.base-dir=<full path of tennis_atp data directory>

  17. Wait 20-30 minutes

  18. Open PgAdmin, right click on Databases > tcb and chose Backup…

  19. Leave all options as they are and chose tcb as Role name and e.g. C:\ultimatetennisstatistics.backup as a filename and UTF8 as encoding

  20. Continue by restoring data on another computer (see above)

@ShahzaibAyyub

This comment has been minimized.

Copy link

@ShahzaibAyyub ShahzaibAyyub commented Jul 25, 2021

Hi, that's very helpful. Do you have the ultimatetennisstatistics.backup by any means because Im trying to create a database but it has many errors (e.g. ERROR: Player G Whytecross not found) Thanks!

@angelozehr

This comment has been minimized.

Copy link
Owner Author

@angelozehr angelozehr commented Jul 26, 2021

Hi @ShahzaibAyyub … unfortunately I don't work for the company anymore where I did this project. The latest file I found on my computer is from January 2018, but if that still helps you, you can download it here: http://az.sg/transfer/ultimatetennisstatistics.backup (I will delete this file after 10 days)

@ShahzaibAyyub

This comment has been minimized.

Copy link

@ShahzaibAyyub ShahzaibAyyub commented Jul 29, 2021

Thank you so much for the help, much appreciated!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment