Why can't I pull in all the SO users from Data Explorer?

Question

It only returns 50,000 rows when I do the query below. I thought there are more than that.

select id,DisplayName from Users 

Answer 1

As already said in the comments: we limit the result set to a maximum of 50,000 rows

To get all rows without the artificial limits opposed by the Data Explorer your best bet is to get the datadump as pointed out by @ben is uǝq backwards, import all data in your own database (like mysql or sqlserver) and run your analysis there. To give you an indication, these are the current row counts for some of the major tables on Stack Overflow:

table            count    
----------- ---------- 
users        3,227,916  
posts       20,580,052 
posthistory 49,728,723 
votes       62,972,310 
comments    30,468,154  

For a very limited use case and when you only want to retrieve less then an handfull of columns you could use a couple of CTE's and join the results to give you what I call a wall of users. Technically this returns more than 50,000 id and diplayname pairs from the Users table (it returns 250,000 pairs). I leave it as an excercise for the reader to try to add more columns but please don't try to timeout the server.

With the newer versions of SQL Server, we get more options to shape the output data. The FOR JSON AUTO clause is beneficial in this context. Using that statement in a query like this one

select top 250000 *
from users
for json auto

returns you all the data in json in just under 40,000 rows. So there is still room to fetch more then 250,000 users specially if you reduce the columns to include.

And you concluded your self, select * from users order by reputation desc is a reasonable way to limit and/or give meaning to so many rows.

Answer 2

Since December 2016, you can also access Stack Exchange data via Google’s BigQuery.

This has the advantages of:

1. Not limited to 50,000 rows, like SEDE is.
2. Has a REST API.
3. Don't need to download the huge dump data and run your own SQL server.
4. It's easier to do fun things by mashing up with other data sets on BigQuery. For example you could cross query the Stack Overflow data with the Medicare data, to try and figure out what meds user-x was on when he wrote that question...

The BigQuery data is updated at about the same rate as the Data Dump (which should be fine for such large amounts of data).

Unfortunately, BigQuery seems to still only contain Stack Overflow data, and not for other sites, but I haven't rigorously tested this in a long while...

Answer 3

If the total number of rows is not orders of magnitude larger than 50000, you can just repeat the query a few times, download the results of each query, and merge them in a spreadsheet, etc. For example:

select top 50000 Id, DisplayName from users
where Id>##MinId##
order by Id asc

Initially MinId = 0, but for subsequent runs it should be the Id in the last row returned by the previous run.

Ideally, this is not something you would do often because it puts a bit of strain on the server.