Awesome ClickHouse › Examples

droher/boxball

⭐ 132 Python repository created 2019-04-27

Boxball is a project that provides prebuilt Docker images containing Retrosheet's comprehensive baseball history data, integrated for use with various analytical database frameworks. It includes support for multiple database systems such as Postgres with cstore_fdw extension, MySQL, SQLite, Clickhouse, Apache Drill, Parquet, and CSV formats. The project aims to facilitate easy access and analysis of two major open-source baseball datasets: Retrosheet, which offers detailed data on every major-league pitch since 2000, every play since 1928, every box score since 1901, and every game since 1871; and the Baseball Databank, which provides yearly summaries for every player and team in history. Boxball leverages Docker for consistent builds and distribution, SQLAlchemy for database abstraction, and the Chadwick tool for converting Retrosheet's complex event files into a relational database format. The project offers several database distributions optimized for different use cases. For example, the Postgres cstore_fdw distribution uses a column-oriented extension to improve speed and reduce disk usage, while the Clickhouse distribution offers impressive performance with lower disk space usage but higher RAM requirements. Apache Drill allows SQL queries directly on files without predefined schemas, suitable for large datasets. Traditional row-oriented databases like Postgres, MySQL, and SQLite are also supported but are less efficient for querying detailed play-by-play data. Boxball is designed to be user-friendly, with Docker commands provided for easy setup and data loading. The data is persisted locally to avoid reloading on container restarts. The project is actively maintained, with a newer successor project called baseball.computer that runs entirely in the browser and offers more features. Boxball encourages community feedback and donations to social causes. Overall, Boxball is a valuable resource for baseball analysts and researchers seeking a ready-to-use, comprehensive baseball data platform across multiple database technologies.

https://github.com/droher/boxball

apache-drillbaseballbaseball-analyticsbaseball-databaseball-databankbaseballdatabankchadwickclickhousecolumn-oriented-databasecolumn-storecontainerscstore_fdwcsvdockermysqlopen-source-baseball-datasetsparquetplay-by-playpostgrespostgresqlretrosheetrow-oriented-databasesabermetricssportssports-datasports-statssqlsqlalchemysqlite