Splunk SPL for SQL users

This is not a perfect mapping between SQL and Splunk Search Processing Language (SPL), but if you are familiar with SQL, this quick comparison might be helpful as a jump-start into using the search commands.

Concepts

The Splunk platform does not store data in a conventional database. Rather, it stores data in a distributed, non-relational, semi-structured database with an implicit time dimension. Relational databases require that all table columns be defined up-front and they do not automatically scale by just plugging in new hardware. However, there are analogues to many of the concepts in the database world.

Database Concept

Splunk Concept

Notes

SQL query

Splunk search

A Splunk search retrieves indexed data and can perform transforming and reporting operations. Results from one search can be "piped", or transferred, from command to command, to filter, modify, reorder, and group your results.

table/view

search results

Search results can be thought of as a database view, a dynamically generated table of rows, with columns.

index

index

All values and fields are indexed by Splunk software, so there is no need to manually add, update, drop, or even think about indexing columns. Everything can be quickly retrieved automatically.

row

result/event

A result in a Splunk search is a list of fields (i.e., column) values, corresponding to a table row. An event is a result that has a timestamp and raw text. Typically an event is a record from a log file, such as:

Fields are returned dynamically from a search, meaning that one search might return a set of fields, while another search might return another set. After teaching Splunk software how to extract more fields from the raw underlying data, the same search will return more fields than it previously did. Fields are not tied to a datatype.

database/schema

index/app

A Splunk index is a collection of data, somewhat like a database has a collection of tables. Domain knowledge of that data, how to extract it, what reports to run, etc, are stored in a Splunk application.

From SQL to Splunk SPL

SQL is designed to search relational database tables which are comprised of columns. SPL is designed to search events, which are comprised of fields. In SQL, you often see examples that use "mytable" and "mycolumn". In SPL, you will see examples that refer to "fields". In these examples, the "source" field is used as a proxy for "table". In Splunk software, "source" is the name of the file, stream, or other input from which a particular piece of data originates, for example /var/log/messages or UDP:514.

When translating from any language to another, often the translation is longer because of idioms in the original language. Some of the Splunk search examples shown below could be more concise, but for parallelism and clarity, the SPL table and field names are kept the same as the SQL example.

SPL searches rarely need the FIELDS command to filter out columns because the user interface provides a more convenient method for filtering. The FIELDS command is used in the SPL examples for parallelism.

With SPL, you never have to use the AND operator in Boolean searches, because AND is implied between terms. However when you use the AND or OR operators, they must be specified in uppercase.

SPL commands do not need to be specified in uppercase. In the these SPL examples, the commands are specified in uppercase for easier identification and clarity.

SQL command

SQL example

Splunk SPL example

SELECT *

SELECT *
FROM mytable

source=mytable

WHERE

SELECT *
FROM mytable
WHERE mycolumn=5

source=mytable mycolumn=5

SELECT

SELECT mycolumn1, mycolumn2
FROM mytable

source=mytable
| FIELDS mycolumn1, mycolumn2

AND/OR

SELECT *
FROM mytable
WHERE (mycolumn1="true"
OR mycolumn2="red")
AND mycolumn3="blue"

source=mytable
AND (mycolumn1="true"
OR mycolumn2="red")
AND mycolumn3="blue"

Note: The AND operator is implied in SPL and does not need to be specified. For this example you could also use:

You can rename a column regardless of whether you use the JOIN command, a lookup, or a subsearch.

LEFT (OUTER) JOIN

SELECT *
FROM mytable1
LEFT JOIN mytable2
ON mytable1.mycolumn=
mytable2.mycolumn

source=mytable1
| JOIN type=left mycolumn
[SEARCH source=mytable2]

SELECT INTO

SELECT *
INTO new_mytable IN mydb2
FROM old_mytable

source=old_mytable
| EVAL source=new_mytable
| COLLECT index=mydb2

Note: COLLECT is typically used to store expensively calculated fields back into your Splunk deployment so that future access is much faster. This current example is atypical but shown for comparison to the SQL command. The source will be renamed orig_source

TRUNCATE TABLE

TRUNCATE TABLE mytable

source=mytable
| DELETE

INSERT INTO

INSERT INTO mytable
VALUES (value1, value2, value3,....)

Note: see SELECT INTO. Individual records are not added via the search language, but can be added via the API if need be.

Note: There are a few things to think about when updating records in Splunk Enterprise. First, you can just add the new values to your Splunk deployment (see INSERT INTO) and not worry about deleting the old values, because Splunk software always returns the most recent results first. Second, on retrieval, you can always de-duplicate the results to ensure only the latest values are used (see SELECT DISTINCT). Finally, you can actually delete the old records (see DELETE).

Splunk SPL for SQL users

Enter your email address, and someone from the documentation team will respond to you:

Send me a copy of this feedback

Please provide your comments here. Ask a question or make a suggestion.

Feedback submitted, thanks!

You must be logged into splunk.com in order to post comments.
Log in now.

Please try to keep this discussion focused on the content covered in this documentation topic.
If you have a more general question about Splunk functionality or are experiencing a difficulty with Splunk,
consider posting a question to Splunkbase Answers.

0
out of 1000 Characters

Your Comment Has Been Posted Above

We use our own and third-party cookies to provide you with a great online experience. We also use these cookies to improve our products and services, support our marketing campaigns, and advertise to you on our website and other websites. Some cookies may continue to collect information after you have left our website.
Learn more (including how to update your settings) here »