ElasticSearch SQL Commands入门

ElasticSearch SQL Commands

ElasticSearch SQL与mysql的语法还是比较像的，只是比较像

1、命令行启动SQL
2、DESCRIBE TABLE
3、SELECT
4、SHOW COLUMNS
5、SHOW FUNCTIONS
6、SHOW TABLES

对人工智能感兴趣的同学，可以点击以下链接：

现在人工智能非常火爆，很多朋友都想学，但是一般的教程都是为博硕生准备的，太难看懂了。最近发现了一个非常适合小白入门的教程，不仅通俗易懂而且还很风趣幽默。所以忍不住分享一下给大家。点这里可以跳转到教程。

https://www.cbedai.net/u014646662

1 命令行启动SQL

$ES_HOME/bin/elasticsearch-sql-cli http://ip:9200
#ip是自己ES服务器的地址

2 DESCRIBE TABLE 描述一个表

ES的索引名就是表名

语法：

DESCRIBE
    [table identifier | [LIKE pattern]]     

describe  LIKE 're%';
describe rep;

DESC 是 describe的别名，和describe具有同样的功能

3、SELECT

语法：

SELECT select_expr [, ...]
[ FROM table_name ]
[ WHERE condition ]
[ GROUP BY grouping_element [, ...] ]
[ HAVING condition]
[ ORDER BY expression [ ASC | DESC ] [, ...] ]
[ LIMIT [ count ] ]

示例：

select message from rep  limit 3;

select clientip from rep where verb = 'GET' group by clientip order by clientip desc  limit 3;

select count(*) from rep;

4、SHOW COLUMNS列出表中的列

语法：

SHOW COLUMNS [ FROM | IN ]?
    [table identifier | 
    [LIKE pattern] ] 

示例：查看rep下面的列

show columns from rep;

5、SHOW FUNCTIONS 支持的函数列表

SHOW FUNCTIONS;

 

以下是ES SQL支持的函数，这里不再介绍，后面的博客祥哥会分类介绍每个函数的使用方法

SHOW FUNCTIONS;
      name       |     type      
-----------------+---------------
AVG              |AGGREGATE      
COUNT            |AGGREGATE      
FIRST            |AGGREGATE      
FIRST_VALUE      |AGGREGATE      
LAST             |AGGREGATE      
LAST_VALUE       |AGGREGATE      
MAX              |AGGREGATE      
MIN              |AGGREGATE      
SUM              |AGGREGATE      
KURTOSIS         |AGGREGATE      
PERCENTILE       |AGGREGATE      
PERCENTILE_RANK  |AGGREGATE      
SKEWNESS         |AGGREGATE      
STDDEV_POP       |AGGREGATE      
SUM_OF_SQUARES   |AGGREGATE      
VAR_POP          |AGGREGATE      
HISTOGRAM        |GROUPING       
COALESCE         |CONDITIONAL    
GREATEST         |CONDITIONAL    
IFNULL           |CONDITIONAL    
ISNULL           |CONDITIONAL    
LEAST            |CONDITIONAL    
NULLIF           |CONDITIONAL    
NVL              |CONDITIONAL    
CURDATE          |SCALAR         
CURRENT_DATE     |SCALAR         
CURRENT_TIMESTAMP|SCALAR         
DAY              |SCALAR         
DAYNAME          |SCALAR         
DAYOFMONTH       |SCALAR         
DAYOFWEEK        |SCALAR         
DAYOFYEAR        |SCALAR         
DAY_NAME         |SCALAR         
DAY_OF_MONTH     |SCALAR         
DAY_OF_WEEK      |SCALAR         
DAY_OF_YEAR      |SCALAR         
DOM              |SCALAR         
DOW              |SCALAR         
DOY              |SCALAR         
HOUR             |SCALAR         
HOUR_OF_DAY      |SCALAR         
IDOW             |SCALAR         
ISODAYOFWEEK     |SCALAR         
ISODOW           |SCALAR         
ISOWEEK          |SCALAR         
ISOWEEKOFYEAR    |SCALAR         
ISO_DAY_OF_WEEK  |SCALAR         
ISO_WEEK_OF_YEAR |SCALAR         
IW               |SCALAR         
IWOY             |SCALAR         
MINUTE           |SCALAR         
MINUTE_OF_DAY    |SCALAR         
MINUTE_OF_HOUR   |SCALAR         
MONTH            |SCALAR         
MONTHNAME        |SCALAR         
MONTH_NAME       |SCALAR         
MONTH_OF_YEAR    |SCALAR         
NOW              |SCALAR         
QUARTER          |SCALAR         
SECOND           |SCALAR         
SECOND_OF_MINUTE |SCALAR         
TODAY            |SCALAR         
WEEK             |SCALAR         
WEEK_OF_YEAR     |SCALAR         
YEAR             |SCALAR         
ABS              |SCALAR         
ACOS             |SCALAR         
ASIN             |SCALAR         
ATAN             |SCALAR         
ATAN2            |SCALAR         
CBRT             |SCALAR         
CEIL             |SCALAR         
CEILING          |SCALAR         
COS              |SCALAR         
COSH             |SCALAR         
COT              |SCALAR         
DEGREES          |SCALAR         
E                |SCALAR         
EXP              |SCALAR         
EXPM1            |SCALAR         
FLOOR            |SCALAR         
LOG              |SCALAR         
LOG10            |SCALAR         
MOD              |SCALAR         
PI               |SCALAR         
POWER            |SCALAR         
RADIANS          |SCALAR         
RAND             |SCALAR         
RANDOM           |SCALAR         
ROUND            |SCALAR         
SIGN             |SCALAR         
SIGNUM           |SCALAR         
SIN              |SCALAR         
SINH             |SCALAR         
SQRT             |SCALAR         
TAN              |SCALAR         
TRUNCATE         |SCALAR         
ASCII            |SCALAR         
BIT_LENGTH       |SCALAR         
CHAR             |SCALAR         
CHARACTER_LENGTH |SCALAR         
CHAR_LENGTH      |SCALAR         
CONCAT           |SCALAR         
INSERT           |SCALAR         
LCASE            |SCALAR         
LEFT             |SCALAR         
LENGTH           |SCALAR         
LOCATE           |SCALAR         
LTRIM            |SCALAR         
OCTET_LENGTH     |SCALAR         
POSITION         |SCALAR         
REPEAT           |SCALAR         
REPLACE          |SCALAR         
RIGHT            |SCALAR         
RTRIM            |SCALAR         
SPACE            |SCALAR         
SUBSTRING        |SCALAR         
UCASE            |SCALAR         
CAST             |SCALAR         
CONVERT          |SCALAR         
DATABASE         |SCALAR         
USER             |SCALAR         
SCORE            |SCORE 

日期时间函数用法：https://blog.csdn.net/u014646662/article/details/89714542

6、SHOW TABLES

语法：

SHOW TABLES
    [table identifier | 
    [LIKE pattern ]]?

SHOW TABLES;
SHOW TABLES LIKE 'emp%';
SHOW TABLES LIKE 'em_';