[SPLUNK] SPL 이해 (SQL비교)

SPL 쿼리시 SQL로 쿼링할 때처럼 잘안되서, 해당 부분 정리

---

참고 

• https://docs.splunk.com/Documentation/Splunk/7.3.2/SearchReference/SQLtoSplunk (Splunk docs 참고) 

---

비교

SQL command	SQl example	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"
AS (alias)	SELECT mycolumn1, mycolumn2 FROM mytable	source=mytable | RENAME mycolumn as column_alias | FIELDS column_alias
BETWEEN	SELECT * FROM mytable WHERE mycolumn BETWEEN 1 AND 5	source=mytable mycolumn >=1 mycolumn <=5
GROUP BY	SELECT mycolumn, avg(mycolumn) FROM mytable WHERE mycolumn=value GROUP BY mycolumn	source=mytable mycolumn=value | STATS avg(mycolumn) BY mycolumn | FIELDS mycolumn, avg(mycolumn)
HAVING	SELECT mycolumn, avg(mycolumn) FROM mytable WHERE mycolumn=value GROUP BY mycolumn HAVING avg(mycolumn)=value	source=mytable mycolumn=value | STATS avg(mycolumn) BY mycolumn | SEARCH avg(mycolumn)=value | FIELDS mycolumn, avg(mycolumn)
LIKE	SELECT * FROM mytable WHERE mycolumn LIKE "%some text%"	source=mytable    mycolumn="*some text*"
ORDER BY	SELECT * FROM mytable ORDER BY mycolumn desc	source=mytable | SORT -mycolumn
SELECT DISTINCT	SELECT DISTINCT    mycolumn1, mycolumn2 FROM mytable	source=mytable | DEDUP mycolumn1 | FIELDS mycolumn1, mycolumn2
SELECT TOP	SELECT TOP(5)  mycolum1,  mycolum2 FROM mytable1 WHERE mycolum3 = "bar" ORDER BY mycolum1 mycolum2	Source=mytable1 mycolum3="bar" | FIELDS mycolum1 mycolum2 | SORT mycolum1 mycolum2 | HEAD 5
INNER JOIN	SELECT * FROM mytable1 INNER JOIN mytable2 ON mytable1.mycolumn=    mytable2.mycolumn	index=myIndex1 OR index=myIndex2 | stats values(*) AS * BY myField
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
TRUNCATE TABLE	TRUNCATE TABLE mytable	source=mytable | DELETE
INSERT INTO	INSERT INTO mytable VALUES (value1, value2, value3,....)	SPLUNK는 INSERT가 없음
UNION	SELECT mycolumn FROM mytable1 UNION SELECT mycolumn FROM mytable2	source=mytable1 | APPEND    [SEARCH source=mytable2] | DEDUP mycolumn
UNION ALL	SELECT * FROM mytable1 UNION ALL SELECT * FROM mytable2	source=mytable1 | APPEND    [SEARCH source=mytable2]
DELETE	DELETE FROM mytable WHERE mycolumn=5	source=mytable1 mycolumn=5 | DELETE
UPDATE	UPDATE mytable SET column1=value,    column2=value,... WHERE some_column=some_value	UPDATE 없음

SPLUNK는 시계열 데이터라, 일반 RDB랑 개념이 다름, 참고하자 (DELETE는 항상조심)