S(plunk)QL

The Splunk Search Processing Language can be easier than SQL … or at least as easy.

If you’re familiar with relational databases, no doubt you’re more than a little familiar with the Structured Query Language, or SQL, probably pretty good at it, and therefore probably comfortable with it.  And although even SQL’s co-inventor (and originator of the relational model), Dr. E. F. “Ted” Codd, was not a fan of SQL, it has nonetheless become the most common method to access relational databases (RDBMS).

Learning a new data access language is a huge pain … until the Search Processing Language.

I created an example of how the Splunk Search Processing Language can be simpler than well-known SQL syntax … even against structured data in RDBMS tables.

I created a MySQL database called ‘cars’ that contains a table called ‘cars’ and loaded it with data (about cars, duh!).  I then installed the MySQL Connector App (which can be downloaded for free on splunkbase), defined a connection specification called ‘cars’ and created the two following equivalent searches.

The first queries the field, performs the aggregation, and puts all of the processing and formatting in SQL.  In other words, the query string is the complete operation expressed in a SQL statement.  The second simply queries the field data in the MySQL table and does the processing and formatting in the Search Processing Language.

SQL

 | mysqlquery spec=cars query="select make_name, count(make_name) as models from cars group by make_name having models>=10 order by models desc"

Splunk

 | mysqlquery spec=cars query="select make_name from cars" | chart count by make_name | rename count as models | search models>=10 | sort -models

Notice the equivalence of operations between the two languages:

• grouping – ‘group by make_name’ and ‘chart count by make_name’
• aggregation – ‘count(make_name)’ and ‘chart count by make_name’
• aliasing – ‘count(make_name) as models’ and ‘rename count as models’
• post-processing – ‘having’ and ‘| search’
• formatting: ‘order by models desc’ and ‘sort -models’

It could even be said that the Search Processing Language command is more intuitive than the equivalent SQL command.  It’s certainly more flexible – while the ordering of the SQL commands and operators is fixed, the Splunk commands can be reordered.  In this example, the ‘| search’ and ‘sort’ commands can be in either order.

Granted, there are scenarios where it makes sense to push more logic into the SQL statement, and thereby the RDBMS – a highly selective filter criteria on a table with a large number of rows, or limiting the number of fields from a very wide table, as done in this example, or complex table joins.  Splunk allows you to do this.

PLUS … the Search Processing Language allows you to enrich, enhance and append this structured data with unstructured data stored and indexed in Splunk. And vice versa.

----------------------------------------------------
Thanks!
dlux