[SOLVED] SELECT * FROM... linux commands that return table data

studentik · 2013-11-12 19:36:39

There are many commands in linux that output table-structured information, such as ls, ps, lsblk, lsof etc... Is there any generic way to send output of such commands directly to some relational database to manipulate this data via SQL?

Even more, is there a way to access somehow /sys and /proc data via SQL?

Something like
SELECT * FROM linux.processess WHERE user='root'
or
SELECT * FROM linux.block_devices WHERE fstype='ext4'

Last edited by studentik (2013-11-23 23:10:05)

karol · 2013-11-12 20:04:10

If you're lucky, grep will do it :-)

studentik · 2013-11-12 20:06:18

karol wrote:

If you're lucky, grep will do it :-)

Even JOIN and GROUP BY?

karol · 2013-11-12 20:12:46

Maybe awk then? perl?

AFAIK, text output is the glue of FLOSS programs. You can parse it, slice and dice it anyway you need.
Often they have handy command options that do just what you need, but I don't think there's any universal format.

studentik · 2013-11-12 20:39:11

karol wrote:

Maybe awk then? perl?

Sorry if I sound like troll, but after some experience with databases, everything looks like data structure. It seems wrong when every command has it's own language to CRUD its table data.

Is there any movement in linux towards normalization/unification of manipulating it's data in a relational manner? Do such projects/ideas even exist?

karol · 2013-11-12 20:56:17

systemd with its binary journal http://www.freedesktop.org/wiki/Softwar … nal-files/ is not a relational db AFAICT, but it provides some unification.
Historically, applications were meant to do one thing, and do it well. They output text which you can transform using powerful tools with a wide array of options, like 'sed', 'grep' or 'awk'. You have more specialized tools like 'paste' or 'join' too.

causasui · 2013-11-22 04:39:08

studentik wrote:

Is there any generic way to send output of such commands directly to some relational database to manipulate this data via SQL?

This question is not well formed: there cannot be any "generic" way to pipe output from stdout into "some relational database", because each RDBMS is unique. Furthermore, blindly piping a single column of plain text into a relational database is pointless since stdout is, by definition, non-relational. An RDBMS with nothing but a text column would have no advantage over a plain-text file. In a relational model, a developer defines the schema.

Therefore you must, categorically and in every case, have:

(a) an RDBMS with a defined schema suitable to the application
(b) some logical layer or wrapper that transforms the output of your shell app into legal SQL that your server can understand. That obtains for apps that print to stdout in regular formats like xml or json: you still have to transform that into an INSERT or it ain't going into any relational database.

Without that, you're not doing any work.

Last edited by causasui (2013-11-22 04:39:57)

fukawi2 · 2013-11-22 05:23:46

studentik wrote:

Sorry if I sound like troll, but after some experience with databases, everything looks like data structure. It seems wrong when every command has it's own language to CRUD its table data.

If all you have is a hammer, everything looks like a nail

studentik · 2013-11-22 06:42:49

causasui wrote:

there cannot be any "generic" way to pipe output from stdout into "some relational database", because each RDBMS is unique

Obviously, I meant not just sending text output into one field, but parsing known formatted output into prepared table structure.

I was hoping that someone already solved this problem and there is a suitable tool for that. But if no, then nothing stops me from making it and sharing with community.

studentik · 2013-11-23 23:09:49

SOLVED: sysql

Arch Linux

#1 2013-11-12 19:36:39

[SOLVED] SELECT * FROM... linux commands that return table data

#2 2013-11-12 20:04:10

Re: [SOLVED] SELECT * FROM... linux commands that return table data

#3 2013-11-12 20:06:18

Re: [SOLVED] SELECT * FROM... linux commands that return table data

#4 2013-11-12 20:12:46

Re: [SOLVED] SELECT * FROM... linux commands that return table data

#5 2013-11-12 20:39:11

Re: [SOLVED] SELECT * FROM... linux commands that return table data

#6 2013-11-12 20:56:17

Re: [SOLVED] SELECT * FROM... linux commands that return table data

#7 2013-11-22 04:39:08

Re: [SOLVED] SELECT * FROM... linux commands that return table data

#8 2013-11-22 05:23:46

Re: [SOLVED] SELECT * FROM... linux commands that return table data

#9 2013-11-22 06:42:49

Re: [SOLVED] SELECT * FROM... linux commands that return table data

#10 2013-11-23 23:09:49

Re: [SOLVED] SELECT * FROM... linux commands that return table data

Board footer