Skip to content

Instantly share code, notes, and snippets.

@kosho
Created June 29, 2018 08:43
Show Gist options
  • Save kosho/8339c9f262ef9767996e1726bd304ecf to your computer and use it in GitHub Desktop.
Save kosho/8339c9f262ef9767996e1726bd304ecf to your computer and use it in GitHub Desktop.
#
# Elastic Stack 6.3 Release Webinar
#
### SQL ###
# Create EMP table
PUT emp
{
"settings": {
"number_of_shards": 1,
"number_of_replicas": 0
},
"mappings": {
"doc": {
"properties": {
"comm": {
"type": "long"
},
"deptno": {
"type": "long"
},
"empno": {
"type": "long"
},
"ename": {
"type": "keyword"
},
"hiredate": {
"type": "date",
"format": "dd-MMM-yy"
},
"job": {
"type": "keyword"
},
"mgr": {
"type": "long"
},
"sal": {
"type": "long"
}
}
}
}
}
PUT emp/doc/_bulk
{"index":{}}
{"ename": "SMITH", "sal": 800, "empno": 7369, "mgr": 7902, "job": "CLERK", "comm": null, "hiredate": "17-DEC-80", "deptno": 20}
{"index":{}}
{"ename": "ALLEN", "sal": 1600, "empno": 7499, "mgr": 7698, "job": "SALESMAN", "comm": 300, "hiredate": "20-FEB-81", "deptno": 30}
{"index":{}}
{"ename": "WARD", "sal": 1250, "empno": 7521, "mgr": 7698, "job": "SALESMAN", "comm": 500, "hiredate": "22-FEB-81", "deptno": 30}
{"index":{}}
{"ename": "JONES", "sal": 2975, "empno": 7566, "mgr": 7839, "job": "MANAGER", "comm": null, "hiredate": "02-APR-81", "deptno": 20}
{"index":{}}
{"ename": "MARTIN", "sal": 1250, "empno": 7654, "mgr": 7698, "job": "SALESMAN", "comm": 1400, "hiredate": "28-SEP-81", "deptno": 30}
{"index":{}}
{"ename": "BLAKE", "sal": 2850, "empno": 7698, "mgr": 7839, "job": "MANAGER", "comm": null, "hiredate": "01-MAY-81", "deptno": 30}
{"index":{}}
{"ename": "CLARK", "sal": 2450, "empno": 7782, "mgr": 7839, "job": "MANAGER", "comm": null, "hiredate": "09-JUN-81", "deptno": 10}
{"index":{}}
{"ename": "SCOTT", "sal": 3000, "empno": 7788, "mgr": 7566, "job": "ANALYST", "comm": null, "hiredate": "19-APR-87", "deptno": 20}
{"index":{}}
{"ename": "KING", "sal": 5000, "empno": 7839, "mgr": null, "job": "PRESIDENT", "comm": null, "hiredate": "17-NOV-81", "deptno": 10}
{"index":{}}
{"ename": "TURNER", "sal": 1500, "empno": 7844, "mgr": 7698, "job": "SALESMAN", "comm": 0, "hiredate": "08-SEP-81", "deptno": 30}
{"index":{}}
{"ename": "ADAMS", "sal": 1100, "empno": 7876, "mgr": 7788, "job": "CLERK", "comm": null, "hiredate": "23-MAY-87", "deptno": 20}
{"index":{}}
{"ename": "JAMES", "sal": 950, "empno": 7900, "mgr": 7698, "job": "CLERK", "comm": null, "hiredate": "03-DEC-81", "deptno": 30}
{"index":{}}
{"ename": "FORD", "sal": 3000, "empno": 7902, "mgr": 7566, "job": "ANALYST", "comm": null, "hiredate": "03-DEC-81", "deptno": 20}
{"index":{}}
{"ename": "MILLER", "sal": 1300, "empno": 7934, "mgr": 7782, "job": "CLERK", "comm": null, "hiredate": "23-JAN-82", "deptno": 10}
# Basic SELECT
POST _xpack/sql?format=txt
{
"query": "SELECT ename, job, sal FROM emp LIMIT 5"
}
POST _xpack/sql?format=csv
{
"query": "SELECT ename, job, sal FROM emp"
}
# SELECT statement with WHERE and ORDER
POST _xpack/sql?format=csv
{
"query": "SELECT ename, job, sal FROM emp WHERE sal > 1000 ORDER BY job, sal DESC"
}
# Translate GROUP BY to Elasticsearch's query and aggregation
POST _xpack/sql/translate
{
"query": "SELECT job, count(*), max(sal) FROM emp GROUP BY job"
}
### Rollup ###
# Create rollup job
PUT _xpack/rollup/job/apache_elastic_example
{
"index_pattern": "apache_elastic_example",
"rollup_index": "apache_elastic_example_rollup",
"cron": "*/30 * * * * ?",
"page_size" :1000,
"groups" : {
"date_histogram": {
"field": "@timestamp",
"interval": "1h",
"delay": "7d"
},
"terms": {
"fields": ["clientip.raw"]
}
},
"metrics": [
{
"field": "bytes",
"metrics": ["sum", "max", "min"]
}
]
}
# Start rollup job
POST _xpack/rollup/job/apache_elastic_example/_start
# Search upon original index
GET apache_elastic_example/_search
{
"size": 0,
"aggs": {
"1": {
"date_histogram": {
"field": "@timestamp",
"interval": "1d"
},
"aggs": {
"2": {
"terms": {
"field": "clientip.raw",
"size": 10
},
"aggs": {
"3": {
"sum": {
"field": "bytes"
}
}
}
}
}
}
}
}
# Search upon rollup and original index with by _rollup_search endpoint
GET apache_elastic_example,apache_elastic_example_rollup/_rollup_search
{
"size": 0,
"aggs": {
"1": {
"date_histogram": {
"field": "@timestamp",
"interval": "1d"
},
"aggs": {
"2": {
"terms": {
"field": "clientip.raw",
"size": 10
},
"aggs": {
"3": {
"sum": {
"field": "bytes"
}
}
}
}
}
}
}
}
# Cleanup
POST _xpack/rollup/job/apache_elastic_example/_stop
DELETE _xpack/rollup/job/apache_elastic_example
DELETE apache_elastic_example_rollup
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment