Wanted: Online querying/analysis tool

Posted in

#1 by yorhel
2019-08-23 at 10:36
So I regularly run queries against the VNDB database to find entries in need of fixing. Now that we have public database dumps, anyone could run such queries, and I'd imagine that would be really useful in helping people to improve the database. Only thing we're missing right now is an online tool we can use to run such queries, create useful dashboards and share the results.

Unfortunately, I've not found an open source tool that ticks all the boxes. The closest thing I've found is Metabase, but it's rather... enterprisey. I suppose it could work for us, but it may take some effort to learn the interface and figure out how to organize dashboards and stuff.

If anyone's willing to step up and host and manage such a tool, that'd be awesome. Alternatively, I can host something, but I'm not enthusiastic about managing a tool as complex as Metabase - so if someone is willing to take on that part we could go with that approach as well. Yet alternatively, if there's a simple not-too-heavy easy-to-host and easy-to-manage tool out there that I've missed, I'm all ears.
#2 by eacil
2019-08-23 at 12:19
Metabase is what is called a BI (Business Intelligence). There are tons of that on github but most of them are an overkill as I doubt you want to create some pie charts.
All you want is an UI to query the db with or without knowing sql, right? I was thinking of web clients as those can be noob friendly but I have no idea if you can even set them up for such situation and if they are not too limited when not using the sql editor. It would personally freak me out to give such tool in the hand of everyone, though, as they are db managers first.
#3 by yorhel
2019-08-24 at 07:39
If all we need is a way to run SQL and display tables, I suppose I could hack something simple together myself. But it wouldn't be very user-friendly and I figured people may have analysis use cases other than the moderation tools I had in mind, so having graphs and a little more flexibility may still be useful.

Problem with those db managers is that, afaik, none of them have easy sharing of queries and results - no fancyness like dashboards either.
#4 by mooncake4132
2019-08-27 at 12:22
Two tools come to mind: SQLPad and redash. I never used either of them so I can't be sure how much they fit your needs, but I think it's worth exploring.Last modified on 2019-08-27 at 12:23
#5 by yorhel
2019-08-27 at 17:14
I wonder how I missed SQLPad, it looks perfect for this use case. It also looks pretty fragile and it may need a few modifications here and there, but that seems doable.
#6 by yorhel
2019-08-28 at 14:11
Yeah, SQLPad is full of quirks and bugs and weird limitations, but it seems to mostly work.

I'm running a test instance at query.vndb.org, feel free to play around. I hacked its authentication system so that you won't have to create a separate account, but you'll need to be logged in on VNDB in order to be able to use it.

Some known issues:
- Some columns are displayed twice in the schema browser.
- Some columns (e.g. in the wikidata table) aren't displayed at all in the schema browser.
- (fixed) There should be a more friendly error message when you're not logged in.
- (fixed) Sharing tables and queries shouldn't require authentication.Last modified on 2019-08-31 at 10:50
#7 by mooncake4132
2019-08-29 at 01:42
The version you have (3.0.0) is still in beta. Maybe the latest stable (2.8.1) will have less issues?Last modified on 2019-08-29 at 01:42
#8 by yorhel
2019-08-29 at 05:51
Let's just there was a reason I tried the beta. :-)
#9 by roadi
2019-08-29 at 16:13
For some reason, selecting columns with type 'int{2,4}' fail with a blank screen and some javascript errors.

For example, the following fail:
select * from vn limit 10;
select id from vn limit 10;
select length from vn limit 10;
select image from vn limit 10;
while the following succeeds:
select title, original, alias, img_nsfw, vn.desc, l_wp, l_encubed, l_renai from vn limit 10;
#10 by yorhel
2019-08-29 at 16:24
That was totally my fault. Fixed.
#11 by rampaa
2019-08-29 at 23:37
SELECT *
FROM releases
WHERE id = '65763'
returns nothing even though r65763 exists.
#12 by yorhel
2019-08-30 at 05:41
Queries are run against a daily import of d14#5 and that entry looks pretty new. The release should show up in a few hours.

Reply

You must be logged in to reply to this thread.