Realtime SQL on SE API

Patrick Hofman

2:57 PM

Hi.

rene

Ola

Patrick Hofman

@andy select * from StackExchange.users where user_id = 189134 should be you indeed. I need to get the passing of filters working on the API, so only the first 30 pages now.

Andy

Greetings

Patrick Hofman

@rene "it is very picky on the syntax" can you elaborate?

@Andy Hi

rene

I had an extra line at the end and then it barked at me

Patrick Hofman

2:59 PM

@rene that shouldn't matter. do you have a sample?

rene

@PatrickHofman trying to repro, hold on

Andy

@PatrickHofman I think I'm understanding what you are saying, but to make sure: Are you not passing the ID to the API right now and I'm just not in the first 30 pages?

Patrick Hofman

@Andy yep.

Andy

k

rene

itgenemd007: An element with the key 'StackExchange.StackExchange.posts.title' already exists in the dictionary. if I do this:
select top 10 title
, creation_date
, *
from StackExchange.StackExchange.posts
order by score desc

Patrick Hofman

3:01 PM

We use this software for our company and I was allowed to make this available for the SE API. So still some work to do in passing things around.

@rene I think you need to alias that to:

` select top 10 title
, creation_date
, p.*
from StackExchange.StackExchange.posts p`

rene

OK

@PatrickHofman nope, sorry

Patrick Hofman

@rene let me check that

rene

Is the drag button at the lower right of the editor area in working order? If so, in Chrome I can't make the area bigger

Patrick Hofman

@rene I don't think so. Have to ask our designer.

rene

OK

No function to get a date from the unix time?

why does this doesn't return any titles: select top 10 title
from StackExchange.StackExchange.posts p

where post_type = 'question'
order by score desc

encoding issue: i.stack.imgur.com/9jneD.png espanõl

Patrick Hofman

3:16 PM

@rene That is a known problem indeed. SE has the content serialized twice I think. First as JSON and then as HTML.

rene

There is something strange happening when I switch databases. First the list is not sorted. If I deselect all databases I'm not sure what happens but this is what I see then:i.stack.imgur.com/KEP0r.png

after a refresh the two databases i had selected are active again

Patrick Hofman

@rene Oh, that is strange.

You see a part of the actual system behind it. Remove the Sql from the URL and you will see.

rene

itgensql056: Syntax error on line 10, column 9 'count': no viable alternative at input 'count' if I use this: select top 100 p.title
, p.link
, u.display_name
, count(u.*)
-- , score
from StackExchange.StackExchange.posts p
inner join users u on u.user_id = p.owner_user_id
inner join votes v on v.post_id = p.post_id
where post_type = 'question'
order by count(u.user_id) desc

I tried order by column_number as well

Patrick Hofman

Order by only works on columns now. The parser could give a better message though.

Working on the error message you got with field names now.

rene

3:31 PM

votes is also not a table :)

Patrick Hofman

@rene You wished it was there, right :)

rene

@PatrickHofman the schema you use is based on the types in Stack API, right?

@PatrickHofman yeah ...

Patrick Hofman

@rene Yes, but not all is implemented already. I have an intern working on that :)

rene

The code completion is not wide enough: i.stack.imgur.com/D7Txj.png

Patrick Hofman

@rene Reported that to the designer too.

Thanks for your input rene.

rene

3:39 PM

Can I get `tags` from the Question object? select top 100 q.title
, q.link
, q.tags
from StackExchange.StackExchange.questions q

That is an array of strings : api.stackexchange.com/docs/types/question

I don't get code completion on the table aliases, right? like that I can do q. and then see which columns are available. It does seem to have that info because it suggests them in the error messasge

Patrick Hofman

@rene Array support was hard (if you mimic a relational database) and the tags field was commented out. I fixed support for it now (so it returns as a concatenated string), so it can be added now. Will do in the next release.

@rene Nope. The evaluation of the SQL is done far far away. There is no way currently to get the fields over there.

rene

I do like how I now can I do cross-database queries

Patrick Hofman

What is your first impression? @rene

What is your first impression? @andy

rene

I have to get used a bit to the schema but so far it is a much easier way to query stuff from the api then it used to be

I guess I can't save queries yet, right?

I did an all sites and then I got an unknown error ...

Patrick Hofman

@rene No. This same system is used for financial systems (probably Exact Online is the one you know by name). And we tend not to save too much information (so no tokens, no user information, etc). Maybe when we feel comfortable enough to build this to a fully fledged portal.

It does save the entire snippet you have to your local storage if that helps.

rene

3:51 PM

In the sites list you really need to have an option to type a few letters to narrow down on stuff

@PatrickHofman Yeah, I peeked at your company website

Patrick Hofman

@rene Good one. Will ask to add that.

rene

@PatrickHofman can you explain how this works if I have 3 sites selected: select top 100 q.title
, q.link
, q.view_count
from StackExchange.StackExchange.questions q
where view_count > 10000
order by q.view_count

does it run the query for each and then merge?

Patrick Hofman

Yes, it pulls them all in parallel (max 3 pages now). Groups them in one set and then executes the where, orderby and top.

rene

Because if I change to top 10 like so: select top 10 q.title
, q.link
, q.view_count
from StackExchange.StackExchange.questions q
where view_count > 10000
order by q.view_count

Stack Overflow is no longer in the result

Patrick Hofman

Have to go now. I will do some work if I can and update the current state.

Thanks @rene

rene

3:57 PM

OK, thanks for sharing this all

@PatrickHofman one thing maybe: any option to convert those creation_date fields to a proper date? Your sqldialect doesn't seem to support type casting? The dates from the API are unix epoch.

I guess you want to handle this: api.stackexchange.com/docs/dates

Patrick Hofman

4:19 PM

Yeah. That is in my wish list too

rene

I do realize I have the easy part of the job ;)

Also you might want to check what happens in the operator evaluation: q.creation_date / 1000 / 1000 gives a different result then (q.creation_date / 1000) / 1000

rene

7:48 PM

I just noticed that order by doesn't have much effect. It seems to always sort on the first field in the grid

so this:
select top 1000 q.title
     , q.link
	 , q.view_count
	 , q.creation_date / 1000 / 1000
from StackExchange.StackExchange.questions q
where view_count > 10000
order by q.view_count desc

has the same effect as

select top 1000 q.title
     , q.link
	 , q.view_count
	 , q.creation_date / 1000 / 1000
from StackExchange.StackExchange.questions q
where view_count > 10000
order by q.view_count

Transcript for

Realtime SQL on SE API