Free SQL dump with 200 million tweets from 13 million users

https://news.ycombinator.com/item?id=2633384

by calufa • 15 years ago

98 36 15 years ago

news.ycombinator.com

About the data:

- DB Size: 543 million rows

- Data Size: 173GB (uncompressed)

- Stored in mysql

- 200+ Million tweets from 13+ Million users

- Collected in 1 week

- Operation costs: 100+ dollars

- Rackspace Cloud - 1 CentOS 8GB Ram server

- Java, memcache, mysql and perl for core processing

- js, php for analytics & visualization

* Download the data at this url http://www.archive.org/details/2011-06-calufa-twitter-sql

ck22• 15 years ago

Hmm, how many days back does it go?

Twitter search still only goes back 10 days in 2011, so how deep is this data?

calufa10• 15 years ago

To be honest I have no idea. It crawled 13MM users, some accounts can be very old with very old tweets... You can look at the CD_data table and look for the tweet html code and parse the timestamp.

ck22• 15 years ago

Apparently Twitter now has 100+ Million tweets per DAY.

So you caught about 2 days worth but randomly in time.

calufa10• 15 years ago

@ck2 --- correct.

aonic• 15 years ago

Thanks! More interested in the scraper.. is it open-source? If yes, where can we download it? If not, can you write about your experience in building it?

calufa10• 15 years ago

I will blog about how I did it in a few days...

jason_slack• 15 years ago

Where do you Blog so I can add to my RSS?

tibbon3• 15 years ago

Writing a Twitter scraper is pretty trivial and you can find several good examples on Github. I'd put mine online, but the commands I was using in 2009/2010 are changed/deprecated largely and my code wouldn't run.

In either case, as Sethish said, distributing dumps like this is against the new ToS.

sethish• 15 years ago

Twitter changed their ToS to explicitly disallow distributing twitter dumps like this: http://chronicle.com/blogs/profhacker/the-end-of-twapperkeep...

I was a part of the webecology project (and 140kit.com, both of which gave large twitter datasets to researchers.

calufa10• 15 years ago

-- Ups, I forgot to scrape the TOS

tibbon3• 15 years ago

Additionally, this data probably isn't as useful as many might think. We found that collecting random tweets probably isn't that useful for most research overall, partially because using any of the streaming APIs omit tweets. Even 'full' firehose seems to omit some tweets, so it can't be considered a complete set, nor verified as a completely random set.

calufa10• 15 years ago

-- I disagree.

- You can cluster users based on tweet data, links relationships &/or even user-to-user relationships

- Understand how retweets work and how fast they propagate.

- Sentiment analysis based on a specific keyword.

- Trend analysis.

There are N number of ways this dataset can be helpful. You have 200MM tweets. Enough for a quick experiment using real data.

* Its true that is "random" data. Just unrandom it!

achompas• 15 years ago

This is a gold mine for a budding programmer. Anyone interested in learning MapReduce frameworks or messing with sentiment analysis/classification should get this data.

With that said...the data is now unavailable?

sigil• 15 years ago

I have 500GB of tweets from the random sample stream, in raw json form, going back to August 2010. Aside from some very broad measures it hasn't been all that useful to us. I never considered distributing it until now, but the TOS clause forbidding redistribution wrankles me. Are we really all okay with contributing content under these terms?

a3_nm• 15 years ago

This is ridiculous. Are ToS of this kind really enforceable? (What about outside the US?)

(Anyway, I guess that in practice, if a torrent with a dump of tweets appears somewhere, it's pretty hard to find out who did it. Yes, Twitter could do some clever watermarking of the API results or correlate the dump contents with the server logs, but it would probably be a lot of work.)

calufa10• 15 years ago

import to mysql:

bunzip2 < my_database.sql.bz2 | mysql -h localhost -u root -p my_database

StavrosK• 15 years ago

Torrent here, when done: http://burnbit.com/torrent/170493/twitter_sql_bz2

juiceandjuice• 15 years ago

Wow, I just downloaded that whole archive in a minute.

calufa10• 15 years ago

bz2 compression ;) --- 1147480:1 compression ratio

joelthelion• 15 years ago

Just shows how much real information is in tweets : not much :)

jdvolz• 15 years ago

Calufa, next time you're in Vegas, send me a message and we'll get a beer. Thank you. You just made something I'm doing vastly more awesome.

chrisjsmith• 15 years ago

All that is meaningless chatter between people and information about bathroom habits. Perhaps if we pooled that distributed effort into something constructive, the world would be a better place.

PostOnce• 15 years ago

http://twitter.com/#!/id_aa_carmack

"Msbuild seems to limit to 100 files on a cl command line, which introduces noticeable sync losses when parallel building on 24 threads."

It's not all meaningless, you just choose to follow meaningless users.

calufa10• 15 years ago

A EMAIL FROM TWITTER KILLED THE DATASET --- :S

user24• 15 years ago

Can you give more detail? The link is still up... What did they say?

edit reply via twitter: "they asked me to remove the dump due TOS" (http://twitter.com/#!/calufa/status/78556903772393474)

which I guess is what I expected.

But are scrapers subject to TOS?

laprise• 15 years ago

Neat ! here some tips for creating a kick ass graph visualization: http://www.martinlaprise.info/2010/02/15/visualize-your-own-...

JeeyoungKim3• 15 years ago

Hey guys, what would be the most sane way to work with this dataset? If it's 173GB, it's probably hard to load it up in a single machine.

nametoremember• 15 years ago

Damn, I just saw this. I would have liked to use it. How can Twitter make you take it down when it is all public information anyway?

JeeyoungKim3• 15 years ago

Does anybody want to share MD5 hash of the file? I'm trying to decompress this file, and I'm keep getting an error.

JeeyoungKim3• 15 years ago

wait, the torrent link has it. I do have the same md5hash, and yet, it's keep crashing whenever i'm trying to uncompress this shit... wtf is going on.

justadude• 15 years ago

Did you figure out how to get this working? I tried 7-zip as well as winrar and both errored out

8maki• 15 years ago

Oh it's awesome dump. Are these mainly from US?

Free SQL dump with 200 million tweets from 13 million users

Related Stories