Malcolm: SQL for the command line: "show"

Posted Mar 23, 2009 16:29 UTC (Mon) by clugstj (subscriber, #4020)
Parent article: Malcolm: SQL for the command line: "show"

"Why can't I just use SQL"

Because SQL sucks as a general programming language.

Malcolm: SQL for the command line: "show"

Posted Mar 23, 2009 16:33 UTC (Mon) by flewellyn (subscriber, #5047) [Link] (13 responses)

Well, of course it does. It ISN'T a general programming language.

But, that's not what he wants to use it for. He only wants to use it for its problem domain: querying a data store of some kind. And in that domain, it's perhaps not the best thing ever, but it's quite suitable and certainly well-understood.

I just wonder how he'll handle issues like complex queries, with things like subqueries and joins and the like.

Malcolm: SQL for the command line: "show"

Posted Mar 23, 2009 16:53 UTC (Mon) by Wol (subscriber, #4433) [Link] (8 responses)

Use something like ENGLISH :-) (otherwise known as ACCESS, INFORM, RETRIEVE etc :-)

But seriously, if your data naturally fits a flat table, SQL is a good fit. Most data, however, doesn't.

Cheers,
Wol

Malcolm: SQL for the command line: "show"

Posted Mar 23, 2009 18:09 UTC (Mon) by mrshiny (guest, #4266) [Link]

Yeah but for analyzing structured log files such as the apache logs, this is the most awesome thing ever. I want this for my servers at work.

Malcolm: SQL for the command line: "show"

Posted Mar 23, 2009 18:51 UTC (Mon) by KGranade (guest, #56052) [Link] (6 responses)

First, as has been mentioned already, the data he's proposing accessing with SQL IS mostly (or totally, not sure) flat tables ( log files, /proc, tables of installed packages, sounds like flat tables to me ).

Secondly, you recommend using something like English, but I have no idea how the examples you present would be used, which means learning a specialized syntax, which means I might as well use a generalized syntax that is a moderately straightforward mapping of the concepts of database retrieval to English... which is a decent description of SQL.

Malcolm: SQL for the command line: "show"

Posted Mar 24, 2009 0:21 UTC (Tue) by Wol (subscriber, #4433) [Link] (5 responses)

:-)

Sorry, but I did *not* say "use English". I said "use ENGLISH" (ENGLISH being a dedicated data access language).

ENGLISH is the original Pick data query language, and is a very good NFNF query tool (It's also called ENGLISH because it is, actually, very similar to English!) For example

SELECT INVOICE WITH INVOICE.TOTAL EQ 1600 AND WHERE LINE.ITEM EQ 215

will select all invoices where the invoice value is 1600 and any individual line is 215.

SQL is *not* a "moderately straightforward mapping of the concepts of database retrieval to English" - no way would I describe it as "moderately straightforward", and it is very relational-oriented. Using it to query a non-relational database is *horrid*.

Cheers,
Wol

Malcolm: SQL for the command line: "show"

Posted Mar 24, 2009 0:44 UTC (Tue) by flewellyn (subscriber, #5047) [Link] (4 responses)

Well, is it incorrect to interpret log files relationally?

Malcolm: SQL for the command line: "show"

Posted Mar 24, 2009 15:51 UTC (Tue) by Wol (subscriber, #4433) [Link] (3 responses)

Log files are typically two-dimensional :-)

So yes, SQL is probably a good language for querying them. But then, so is ENGLISH, because it's n-dimensional (actually, it doesn't work that well if n hits 4 or more :-( so horses for courses, I'd use ENGLISH because that's what I'm comfortable with.

Cheers,
Wol

Malcolm: SQL for the command line: "show"

Posted Mar 24, 2009 16:24 UTC (Tue) by flewellyn (subscriber, #5047) [Link] (2 responses)

That's fine, but who typically knows ENGLISH these days? SQL is far more common.

Malcolm: SQL for the command line: "show"

Posted Mar 26, 2009 14:06 UTC (Thu) by Wol (subscriber, #4433) [Link] (1 responses)

You'd be surprised ...

IBM are on record as saying that their version (the U2 databases) is the fastest growing product in their database VAR sales.

And International Spectrum is holding their conference right now - against a background of collapsing conference attendances (typically down 25 - 50 %), they're holding their own - I think they were down 7% (or was it up?)

imho relational *theory* is great. Unfortunately, relational practice falls foul of Einstein's corollary to Occam - practice is TOO simple, therefore system complexity (as in all the stuff *round* the database) rises sharply as a result. SQL queries are a classic example :-)

Cheers,
Wol

Malcolm: SQL for the command line: "show"

Posted Mar 28, 2009 0:55 UTC (Sat) by nix (subscriber, #2304) [Link]

SQL extensions are an even more classic example. Look at MODEL, for
instance. In case the relational model is 'too hard', now you can turn
your DB into a tiny spreadsheet and bash at it in the query. How
relational...

(actually it *is* useful, but that doesn't mean it's not totally bizarre
and screwy. The real problem here is SQL's halfassed incapable
implementation of half the relational calculus in a non-Turing-complete
fashion. But it paid for my house so I can't complain *too* terribly
hard.)

Malcolm: SQL for the command line: "show"

Posted Mar 23, 2009 20:30 UTC (Mon) by clugstj (subscriber, #4020) [Link] (1 responses)

OK, I was a little flippant before. A useful tool makes simple things simple and complex things possible. I see SQL in this case as only the first. Once you try to do something complex, you will have to throw it out and go back to a more general programming language.

Malcolm: SQL for the command line: "show"

Posted Mar 23, 2009 20:42 UTC (Mon) by flewellyn (subscriber, #5047) [Link]

It depends. SQL is really a domain-specific language, and it does queries very well. But it doesn't do other things that we might want. Having a query language which was fully general, and able to do any sort of general-purpose programming, would be nice. But then, what language to use?

Malcolm: SQL for the command line: "show"

Posted Mar 24, 2009 21:35 UTC (Tue) by skx (subscriber, #14652) [Link] (1 responses)

It is funny how people get hooked on using SQL for querying logfiles. Last year or so I wrote asql which I use for producing adhoc statistics form Apache logfiles.

Simple usage is:

$ asql 
asql v1.2 - type 'help' for help.
asql> load /home/www/www.steve.org.uk/logs/access.log
Loading: /home/www/www.steve.org.uk/logs/access.log
asql> SELECT source,SUM(size) AS Number FROM logs GROUP BY source ORDER BY Number DESC, source LIMIT 0,10;
67.195.37.112 4681922
74.6.17.185 2353628
87.120.8.52 2066975
77.36.6.72 1859180
...

Finding the top ten referers becomes:

asql> SELECT referer,COUNT(referer) AS number from logs WHERE referer NOT LIKE '%steve.org.uk%' GROUP BY referer ORDER BY number DESC,referer LIMIT 0,10;
- 1888
http://www.gnu.org/software/gnump3d/download.html 12
http://community.livejournal.com/lotr_tattoos/?skip=20 5
http://lua-users.org/wiki/LibrariesAndBindings 4

Although SQL is often not the most natural way to query things I found it very useful and natural in this context.

Malcolm: SQL for the command line: "show"

Posted Mar 24, 2009 21:50 UTC (Tue) by flewellyn (subscriber, #5047) [Link]

Well, SQL is not, perhaps, the best thing every when it comes to query languages. I mean, it works okay, and it's "good enough", and I'll defend it on those grounds. But it's hardly the epitome of query languages.

Truth be told, my ideal query language would be something Lisplike, with query operations specified by functions and special operators (or macros), and "views" just being newly defined querying functions or macros.

Hmmm...now there's an idea...