6 ms·

If anyone from the dolt team is reading this, I'd like to make an enquiry: At bugout.dev, we have an ongoing crawl of public GitHub. We just created a dataset

If anyone from the dolt team is reading this, I'd like to make an enquiry: At bugout.dev, we have an ongoing crawl of public GitHub. We just created a dataset of code snippets crawled from popular GitHub repositories, listed by language, license, github repo, and commit hash and are looking to release it publicly and keep it up-to-date with our GitHub crawl. The dataset for a single crawl comes in at about 60GB. We uploaded the data to Kaggle because we thought it would be a good place for people to work with the data. Unfortunately, the Kaggle notebook experience is not tailored to such large datasets. Our dataset is in a SQLite database. It takes a long time for the dataset to load into Kaggle notebooks, and I don't think they are provisioned with SSDs as queries take a long time. Our best workaround to this is to partition into 3 datasets on Kaggle - train, eval, and development, but it will be a pain to manage this for every update, especially as we enrich the dataset with results of static analysis, etc. I'd like to explore hosting the public dataset on Dolthub. If this sounds interesting to you please, reach out to me - email is in my HN profile.

zomglings 6y ago
This is the dataset on Kaggle - https://www.kaggle.com/simiotic/github-code-snippets https://www.kaggle.com/simiotic/github-code-snippets
- justinclift 6y ago
  Yeah, that sized database is likely to be a challenge unless the computer system it's running on has scads of memory. One of my projects (DBHub.io) is putting effort towards working through the problem with larger sized SQLite databases (~10GB), and that's mainly through using bare metal hosts with lots of memory. eg 64GB, 128GB, etc. Putting the same data into PostgreSQL, or even MySQL, would likely be much more efficient memory wise. :)
  - zomglings 6y ago
    Can't beat SQLite for distribution as a public dataset, though.
    - zachmu 6y ago
      We think dolt can :)
      - justinclift 6y ago
        How do you send someone a dolt database as a file?
        acidbaseextract 6y ago
        How do you send someone a git repository as a file? Why would a tarball not work?
        touisteur 6y ago
        Git bundles. Amazing simple tool. Doing it all the time via sneaker net, or mail/chat-with-attachments, better that sending patches around IME.
        411111111111111 6y ago
        Probably by first pushing it into a file. This command is in the readme. dolt remote add <remote> file:///Users/xyz/abs/path/
        zachmu 6y ago
        Push it to DoltHub, tell them to clone it. Just like with source code.
  - zachmu 6y ago
    We have 200 GB databases in dolt format that are totally queryable. They don't work well querying on the web though - you need a local copy to query it effectively. Making web query as fast as local is an ongoing project.
    - justinclift 6y ago
      Yeah the "on the web" piece is the thing we're talking about. :) 200GB databases for PostgreSQL (etc) isn't any kind of amazing.
  - tomcam 6y ago
    Uh, personal question here. Where does your ~10G number come from? I pretty much run my life on the Apple Notes app. My Notes database is about 12G and now I’m scared.
    - justinclift 6y ago
      Oh, it's just the vast majority of SQLite databases we see are pretty tiny. eg a few MB's at max So, resource usage is pretty much not a consideration in any way when doing things with them. Once people start doing things with multi-GB databases though, if the system they're running on has a small amount of memory (say 4GB, 8GB) then things can start going poorly. Not like "crash and burn" poorly (so far). More like "tries to read 10GB of data into 4GB of ram" poorly. eg Dog slow, not lightning quick. If your machine has a bunch of ram in it, you're likely safe . Though, I'd be making damn sure there are tested and working backups of it, just to be safe. :)
      - tomcam 6y ago
        I missed this answer the first time around. Thanks so much for the reply. I always get machines with the maximum amount of RAM precisely for this reason.
zachmu 6y ago
We'll be in touch :)
StreamBright 6y ago
You have other options too. If I have time i can try to reduce the size with a columnar format that is designed for this use case (repeated values, static dataset).
- zomglings 6y ago
  That would be really great. Let me know if there's any way we can help. Maybe if we released a small version of the dataset for testing/benchmarking and then I could take care of running the final processing on the full dataset?
  - StreamBright 6y ago
    That would be amazing. I get back my internet tomorrow and i can play with the dataset see how much we could optimize.
    - zomglings 6y ago
      Hi StreamBright - just published the development version of the dataset also to Kaggle: https://www.kaggle.com/simiotic/github-code-snippets-development-sample https://www.kaggle.com/simiotic/github-code-snippets-develop... Compressed, it's 471 MB. Uncompressed, just a little more than 3 GB. If you want to get in touch with me in a better way than HN comments two good options: 1. My email is in my profile 2. You can direct message me (@zomglings) on the Bugout community Slack: https://join.slack.com/t/bugout-dev/shared_invite/zt-fhepyt87-5XcJLy0iu702SO_hMFKNhQ https://join.slack.com/t/bugout-dev/shared_invite/zt-fhepyt8... Looking forward to collaborating with you. :)
      - StreamBright 6y ago
        I have messaged you on Slack. The preliminary tests show significant reduction in space usage when using Parquet over Sqlite3. This is not unexpected at all. Parquet is much better for analytical use. - 2.9G Mar 8 08:37 snippets-dev.db - 427M Mar 8 14:05 test1.parquet (venv) snippets-dev sqlite3 -header -csv -readonly snippets-dev.db 'SELECT COUNT() FROM snippets;' COUNT() 4850000 (venv) snippets-dev python test.py test1.parquet 4850000 I can share the 3 lines of Python and 1 line for SQL I used to convert the dataset. There was only weird thing that I encountered, you somehow have a new line character in the commit_hash field for every value.
st1ck 6y ago
You can just try converting it into Parquet with ZSTD compression (and set high enough level). You can even fiddle with dictionary settings: https://arrow.apache.org/docs/python/generated/pyarrow.parquet.write_table.html#pyarrow.parquet.write_table https://arrow.apache.org/docs/python/generated/pyarrow.parqu...
st1ck 6y ago
Dataset similar to yours (github data) which you can query using Clickhouse: https://gh.clickhouse.tech/explorer/ https://gh.clickhouse.tech/explorer/

zomglings 6y ago

This is the dataset on Kaggle - https://www.kaggle.com/simiotic/github-code-snippets https://www.kaggle.com/simiotic/github-code-snippets

justinclift 6y ago
Yeah, that sized database is likely to be a challenge unless the computer system it's running on has scads of memory. One of my projects (DBHub.io) is putting effort towards working through the problem with larger sized SQLite databases (~10GB), and that's mainly through using bare metal hosts with lots of memory. eg 64GB, 128GB, etc. Putting the same data into PostgreSQL, or even MySQL, would likely be much more efficient memory wise. :)
- zomglings 6y ago
  Can't beat SQLite for distribution as a public dataset, though.
  - zachmu 6y ago
    We think dolt can :)
    - justinclift 6y ago
      How do you send someone a dolt database as a file?
      - acidbaseextract 6y ago
        How do you send someone a git repository as a file? Why would a tarball not work?
        touisteur 6y ago
        Git bundles. Amazing simple tool. Doing it all the time via sneaker net, or mail/chat-with-attachments, better that sending patches around IME.
      - 411111111111111 6y ago
        Probably by first pushing it into a file. This command is in the readme. dolt remote add <remote> file:///Users/xyz/abs/path/
      - zachmu 6y ago
        Push it to DoltHub, tell them to clone it. Just like with source code.
- zachmu 6y ago
  We have 200 GB databases in dolt format that are totally queryable. They don't work well querying on the web though - you need a local copy to query it effectively. Making web query as fast as local is an ongoing project.
  - justinclift 6y ago
    Yeah the "on the web" piece is the thing we're talking about. :) 200GB databases for PostgreSQL (etc) isn't any kind of amazing.
- tomcam 6y ago
  Uh, personal question here. Where does your ~10G number come from? I pretty much run my life on the Apple Notes app. My Notes database is about 12G and now I’m scared.
  - justinclift 6y ago
    Oh, it's just the vast majority of SQLite databases we see are pretty tiny. eg a few MB's at max So, resource usage is pretty much not a consideration in any way when doing things with them. Once people start doing things with multi-GB databases though, if the system they're running on has a small amount of memory (say 4GB, 8GB) then things can start going poorly. Not like "crash and burn" poorly (so far). More like "tries to read 10GB of data into 4GB of ram" poorly. eg Dog slow, not lightning quick. If your machine has a bunch of ram in it, you're likely safe . Though, I'd be making damn sure there are tested and working backups of it, just to be safe. :)
    - tomcam 6y ago
      I missed this answer the first time around. Thanks so much for the reply. I always get machines with the maximum amount of RAM precisely for this reason.

zachmu 6y ago

We'll be in touch :)

StreamBright 6y ago

You have other options too. If I have time i can try to reduce the size with a columnar format that is designed for this use case (repeated values, static dataset).

zomglings 6y ago
That would be really great. Let me know if there's any way we can help. Maybe if we released a small version of the dataset for testing/benchmarking and then I could take care of running the final processing on the full dataset?
- StreamBright 6y ago
  That would be amazing. I get back my internet tomorrow and i can play with the dataset see how much we could optimize.
  - zomglings 6y ago
    Hi StreamBright - just published the development version of the dataset also to Kaggle: https://www.kaggle.com/simiotic/github-code-snippets-development-sample https://www.kaggle.com/simiotic/github-code-snippets-develop... Compressed, it's 471 MB. Uncompressed, just a little more than 3 GB. If you want to get in touch with me in a better way than HN comments two good options: 1. My email is in my profile 2. You can direct message me (@zomglings) on the Bugout community Slack: https://join.slack.com/t/bugout-dev/shared_invite/zt-fhepyt87-5XcJLy0iu702SO_hMFKNhQ https://join.slack.com/t/bugout-dev/shared_invite/zt-fhepyt8... Looking forward to collaborating with you. :)
    - StreamBright 6y ago
      I have messaged you on Slack. The preliminary tests show significant reduction in space usage when using Parquet over Sqlite3. This is not unexpected at all. Parquet is much better for analytical use. - 2.9G Mar 8 08:37 snippets-dev.db - 427M Mar 8 14:05 test1.parquet (venv) snippets-dev sqlite3 -header -csv -readonly snippets-dev.db 'SELECT COUNT() FROM snippets;' COUNT() 4850000 (venv) snippets-dev python test.py test1.parquet 4850000 I can share the 3 lines of Python and 1 line for SQL I used to convert the dataset. There was only weird thing that I encountered, you somehow have a new line character in the commit_hash field for every value.

st1ck 6y ago

You can just try converting it into Parquet with ZSTD compression (and set high enough level). You can even fiddle with dictionary settings: https://arrow.apache.org/docs/python/generated/pyarrow.parquet.write_table.html#pyarrow.parquet.write_table https://arrow.apache.org/docs/python/generated/pyarrow.parqu...

st1ck 6y ago

Dataset similar to yours (github data) which you can query using Clickhouse: https://gh.clickhouse.tech/explorer/ https://gh.clickhouse.tech/explorer/