Data Types and Functions
Complete reference for data types, identifiers, and built-in functions.
Identifiers
An identifier is an ID to name your database objects, such as index names, field names, aliases, and so on. Energy Logserver supports two types of identifiers: regular identifiers and delimited identifiers.
Regular identifiers
A regular identifier is a string of characters that starts with an ASCII letter (lower or upper case). The next character can either be a letter, digit, or underscore (_). It can’t be a reserved keyword. Whitespace and other special characters are also not allowed.
Energy Logserver supports the following regular identifiers:
Identifiers prefixed by a dot
.sign. Use to hide an index. For example.sample-dashboards.Identifiers prefixed by an
@sign. Use for meta fields generated by Network Probe ingestion.Identifiers with hyphen
-in the middle. Use for index names with date information.Identifiers with star
*present. Use for wildcard match of index patterns.
For regular identifiers, you can use the name without any back tick or escape characters.
In this example, source, fields, account_number, firstname, and lastname are all identifiers. Out of these, the source field is a reserved identifier.
SELECT account_number, firstname, lastname FROM accounts;
| account_number | firstname | lastname |
|---|---|---|
| 1 | Amber | Duke |
| 6 | Hattie | Bond |
| 13 | Nanette | Bates |
| 18 | Dale | Adams |
Delimited identifiers
A delimited identifier can contain special characters not allowed by a regular identifier. You must enclose delimited identifiers with back ticks (``). Back ticks differentiate the identifier from special characters.
If the index name includes a dot (.), for example, log-2021.01.11, use delimited identifiers with back ticks to escape it `log-2021.01.11`.
Typical examples of using delimited identifiers:
Identifiers with reserved keywords.
Identifiers with a
.present. Similarly,-to include date information.Identifiers with other special characters. For example, Unicode characters.
To quote an index name with back ticks:
source=`accounts` | fields `account_number`;
| account_number |
|---|
| 1 |
| 6 |
| 13 |
| 18 |
Case sensitivity
Identifiers are case sensitive. They must be exactly the same as what’s stored in Energy Logserver.
For example, if you run source=Accounts, you’ll get an index not found exception because the actual index name is in lower case.
Data types
The following table shows the data types supported by the SQL plugin and how each one maps to SQL and Energy Logserver data types:
| {{ project }} SQL Type | {{ project }} Type | SQL Type |
|---|---|---|
| boolean | boolean | BOOLEAN |
| byte | byte | TINYINT |
| short | byte | SMALLINT |
| integer | integer | INTEGER |
| long | long | BIGINT |
| float | float | REAL |
| half_float | float | FLOAT |
| scaled_float | float | DOUBLE |
| double | double | DOUBLE |
| keyword | string | VARCHAR |
| text | text | VARCHAR |
| date | timestamp | TIMESTAMP |
| date_nanos | timestamp | TIMESTAMP |
| ip | ip | VARCHAR |
| date | timestamp | TIMESTAMP |
| binary | binary | VARBINARY |
| object | struct | STRUCT |
| nested | array | STRUCT |
In addition to this list, the SQL plugin also supports the datetime type, though it doesn’t have a corresponding mapping with Energy Logserver or SQL.
To use a function without a corresponding mapping, you must explicitly convert the data type to one that does.
Date and time types
The date and time types represent a time period: DATE, TIME, DATETIME, TIMESTAMP, and INTERVAL. By default, the Energy Logserver DSL uses the date type as the only date-time related type that contains all information of an absolute time point.
To integrate with SQL, each type other than the timestamp type holds part of the time period information. Some functions might have restrictions for the input argument type.
Date
The date type represents the calendar date regardless of the time zone. A given date value is a 24-hour period, but this period varies in different timezones and might have flexible hours during daylight saving programs. The date type doesn’t contain time information and it only supports a range of 1000-01-01 to 9999-12-31.
| Type | Syntax | Range |
|---|---|---|
| date | yyyy-MM-dd | 0001-01-01 to 9999-12-31 |
Time
The time type represents the time of a clock regardless of its timezone. The time type doesn’t contain date information.
| Type | Syntax | Range |
|---|---|---|
| time | hh:mm:ss[.fraction] | 00:00:00.0000000000 to 23:59:59.9999999999 |
Datetime
The datetime type is a combination of date and time. It doesn’t contain timezone information. For an absolute time point that contains date, time, and timezone information, see Timestamp.
| Type | Syntax | Range |
|---|---|---|
| datetime | yyyy-MM-dd hh:mm:ss[.fraction] | 0001-01-01 00:00:00.0000000000 to 9999-12-31 23:59:59.9999999999 |
Timestamp
The timestamp type is an absolute instance independent of timezone or convention. For example, for a given point of time, if you change the timestamp to a different timezone, its value changes accordingly.
The timestamp type is stored differently from the other types. It’s converted from its current timezone to UTC for storage and converted back to its set timezone from UTC when it’s retrieved.
| Type | Syntax | Range |
|---|---|---|
| timestamp | yyyy-MM-dd hh:mm:ss[.fraction] | 0001-01-01 00:00:01.9999999999 UTC to 9999-12-31 23:59:59.9999999999 |
Interval
The interval type represents a temporal duration or a period.
| Type | Syntax |
|---|---|
| interval | INTERVAL expr unit |
The expr unit is any expression that eventually iterates to a quantity value. It represents a unit for interpreting the quantity, including MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, and YEAR. The INTERVAL keyword and the unit specifier are not case sensitive.
The interval type has two classes of intervals: year-week intervals and day-time intervals.
Year-week intervals store years, quarters, months, and weeks.
Day-time intervals store days, hours, minutes, seconds, and microseconds.
Convert between date and time types
Apart from the interval type, all date and time types can be converted to each other. The conversion might alter the value or cause some information loss. For example, when extracting the time value from a datetime value, or converting a date value to a datetime value, and so on.
The SQL plugin supports the following conversion rules for each of the types:
Convert from date
Because the
datevalue doesn’t have any time information, conversion to thetimetype isn’t useful and always returns a zero time value of00:00:00.Converting from
datetodatetimehas a data fill-up due to the lack of time information. It attaches the time00:00:00to the original date by default and forms adatetimeinstance. For example, conversion of2020-08-17to adatetimetype is2020-08-17 00:00:00.Converting to
timestamptype alternates both thetimevalue and thetimezoneinformation. It attaches the zero time value00:00:00and the session timezone (UTC by default) to the date. For example, conversion of2020-08-17to adatetimetype with a session timezone UTC is2020-08-17 00:00:00 UTC.
Convert from time
You cannot convert the
timetype to any other date and time types because it doesn’t contain any date information.
Convert from datetime
Converting
datetimetodateextracts the date value from thedatetimevalue. For example, conversion of2020-08-17 14:09:00to adatetype is2020-08-08.Converting
datetimetotimeextracts the time value from thedatetimevalue. For example, conversion of2020-08-17 14:09:00to atimetype is14:09:00.Because the
datetimetype doesn’t contain timezone information, converting totimestamptype fills up the timezone value with the session timezone. For example, conversion of2020-08-17 14:09:00(UTC) to atimestamptype is2020-08-17 14:09:00 UTC.
Convert from timestamp
Converting from a
timestamptype to adatetype extracts the date value and converting to atimetype extracts the time value. Converting from atimestamptype todatetimetype extracts only thedatetimevalue and leaves out the timezone value. For example, conversion of2020-08-17 14:09:00UTC to adatetype is2020-08-17, to atimetype is14:09:00, and to adatetimetype is2020-08-17 14:09:00.
Functions
You must enable fielddata in the document mapping for most string functions to work properly.
The specification shows the return type of the function with a generic type T as the argument.
For example, abs(number T) -> T means that the function abs accepts a numerical argument of type T, which could be any subtype of the number type, and it returns the actual type of T as the return type.
The SQL plugin supports the following common functions shared across the SQL and PPL languages.
Mathematical
| Function | Specification | Example |
|---|---|---|
| abs | abs(number T) -> T | SELECT abs(0.5) |
| add | add(number T, number T) -> T | SELECT add(1, 5) |
| cbrt | cbrt(number T) -> double | SELECT cbrt(8) |
| ceil | ceil(number T) -> T | SELECT ceil(0.5) |
| conv | conv(string T, integer, integer) -> string | SELECT conv('2C', 16, 10), conv(1111, 2, 10) |
| crc32 | crc32(string) -> string | SELECT crc32('MySQL') |
| divide | divide(number T, number T) -> T | SELECT divide(1, 0.5) |
| e | e() -> double | SELECT e() |
| exp | exp(number T) -> double | SELECT exp(0.5) |
| expm1 | expm1(number T) -> double | SELECT expm1(0.5) |
| floor | floor(number T) -> long | SELECT floor(0.5) |
| ln | ln(number T) -> double | SELECT ln(10) |
| log | log(number T) -> double OR log(number T, number T) -> double | SELECT log(10), SELECT log(2, 16) |
| log2 | log2(number T) -> double | SELECT log2(10) |
| log10 | log10(number T) -> double | SELECT log10(10) |
| mod | mod(number T, number T) -> T | SELECT mod(2, 3) |
| modulus | modulus(number T, number T) -> T | SELECT modulus(2, 3) |
| multiply | multiply(number T, number T) -> T | SELECT multiply(2, 3) |
| pi | pi() -> double | SELECT pi() |
| pow | pow(number T, number T) -> double | SELECT pow(2, 3) |
| power | power(number T, number T) -> double | SELECT power(2, 3) |
| rand | rand() -> float OR rand(number T) -> float | SELECT rand(), SELECT rand(0.5) |
| rint | rint(number T) -> double | SELECT rint(1.5) |
| round | round(number T) -> T OR round(number T, integer) -> T | SELECT round(1.5), SELECT round(1.175, 2) |
| sign | sign(number T) -> integer | SELECT sign(1.5) |
| signum | signum(number T) -> integer | SELECT signum(0.5) |
| sqrt | sqrt(number T) -> double | SELECT sqrt(0.5) |
| strcmp | strcmp(string T, string T) -> integer | SELECT strcmp('hello', 'hello world') |
| subtract | subtract(number T, number T) -> T | SELECT subtract(3, 2) |
| truncate | truncate(number T, number T) -> T | SELECT truncate(56.78, 1) |
| + | number T + number T -> T | SELECT 1 + 5 |
| - | number T - number T -> T | SELECT 3 - 2 |
| * | number T * number T -> T | SELECT 2 * 3 |
| / | number T / number T -> T | SELECT 1 / 0.5 |
| % | number T % number T -> T | SELECT 2 % 3 |
Trigonometric
| Function | Specification | Example |
|---|---|---|
| acos | acos(number T) -> double | SELECT acos(0.5) |
| asin | asin(number T) -> double | SELECT asin(0.5) |
| atan | atan(number T) -> double | SELECT atan(0.5) |
| atan2 | atan2(number T, number T) -> double | SELECT atan2(1, 0.5) |
| cos | cos(number T) -> double | SELECT cos(0.5) |
| cosh | cosh(number T) -> double | SELECT cosh(0.5) |
| cot | cot(number T) -> double | SELECT cot(0.5) |
| degrees | degrees(number T) -> double | SELECT degrees(0.5) |
| radians | radians(number T) -> double | SELECT radians(0.5) |
| sin | sin(number T) -> double | SELECT sin(0.5) |
| sinh | sinh(number T) -> double | SELECT sinh(0.5) |
| tan | tan(number T) -> double | SELECT tan(0.5) |
Date and time
Functions marked with * are only available in SQL.
| Function | Specification | Example |
|---|---|---|
| adddate | adddate(date, INTERVAL expr unit) -> date | SELECT adddate(date('2020-08-26'), INTERVAL 1 hour) |
| addtime | addtime(date, date) -> date | SELECT addtime(date('2008-12-12'), date('2008-12-12')) |
| convert_tz | convert_tz(date, string, string) -> date | SELECT convert_tz('2008-12-25 05:30:00', '+00:00', 'America/Los_Angeles') |
| curtime | curtime() -> time | SELECT curtime() |
| curdate | curdate() -> date | SELECT curdate() |
| current_date | current_date() -> date | SELECT current_date() |
| current_time | current_time() -> time | SELECT current_time() |
| current_timestamp | current_timestamp() -> date | SELECT current_timestamp() |
| date | date(date) -> date | SELECT date('2000-01-02') |
| datediff | datediff(date, date) -> integer | SELECT datediff(date('2000-01-02'), date('2000-01-01')) |
| datetime | datetime(string) -> datetime | SELECT datetime('2008-12-25 00:00:00') |
| date_add | date_add(date, INTERVAL integer UNIT) | SELECT date_add('2020-08-26', INTERVAL 1 HOUR) |
| date_format | date_format(date, string) -> string OR date_format(date, string, string) -> string | SELECT date_format(date('2020-08-26'), 'Y') |
| date_sub | date_sub(date, INTERVAL expr unit) -> date | SELECT date_sub(date('2008-01-02'), INTERVAL 31 day) |
| dayofmonth | dayofmonth(date) -> integer | SELECT dayofmonth(date('2001-05-07')) |
| day | day(date) -> integer | SELECT day(date('2020-08-25')) |
| dayname | dayname(date) -> string | SELECT dayname(date('2020-08-26')) |
| dayofmonth | dayofmonth(date) -> integer | SELECT dayofmonth(date('2020-08-26')) |
| dayofweek | dayofweek(date) -> integer | SELECT dayofweek(date('2020-08-26')) |
| dayofyear | dayofyear(date) -> integer | SELECT dayofyear(date('2020-08-26')) |
| dayofweek | dayofweek(date) -> integer | SELECT dayofweek(date('2020-08-26')) |
| day_of_month\* | day_of_month(date) -> integer | SELECT day_of_month(date('2020-08-26')) |
| day_of_week\* | day_of_week(date) -> integer | SELECT day_of_week(date('2020-08-26')) |
| day_of_year\* | day_of_year(date) -> integer | SELECT day_of_year(date('2020-08-26')) |
| extract\* | extract(part FROM date) -> integer | SELECT extract(MONTH FROM datetime('2020-08-26 10:11:12')) |
| from_days | from_days(N) -> integer | SELECT from_days(733687) |
| from_unixtime | from_unixtime(N) -> date | SELECT from_unixtime(1220249547) |
| get_format | get_format(PART, string) -> string | SELECT get_format(DATE, 'USA') |
| hour | hour(time) -> integer | SELECT hour(time '01:02:03') |
| hour_of_day\* | hour_of_day(time) -> integer | SELECT hour_of_day(time '01:02:03') |
| last_day\* | last_day(date) -> integer | SELECT last_day(date('2020-08-26')) |
| localtime | localtime() -> date | SELECT localtime() |
| localtimestamp | localtimestamp() -> date | SELECT localtimestamp() |
| makedate | makedate(double, double) -> date | SELECT makedate(1945, 5.9) |
| maketime | maketime(integer, integer, integer) -> date | SELECT maketime(1, 2, 3) |
| microsecond | microsecond(expr) -> integer | SELECT microsecond(time '01:02:03.123456') |
| minute | minute(expr) -> integer | SELECT minute(time '01:02:03') |
| minute_of_day\* | minute_of_day(expr) -> integer | SELECT minute_of_day(time '01:02:03') |
| minute_of_hour\* | minute_of_hour(expr) -> integer | SELECT minute_of_hour(time '01:02:03') |
| month | month(date) -> integer | SELECT month(date('2020-08-26')) |
| month_of_year\* | month_of_year(date) -> integer | SELECT month_of_year(date('2020-08-26')) |
| monthname | monthname(date) -> string | SELECT monthname(date('2020-08-26')) |
| now | now() -> date | SELECT now() |
| period_add | period_add(integer, integer) | SELECT period_add(200801, 2) |
| period_diff | period_diff(integer, integer) | SELECT period_diff(200802, 200703) |
| quarter | quarter(date) -> integer | SELECT quarter(date('2020-08-26')) |
| second | second(time) -> integer | SELECT second(time '01:02:03') |
| second_of_minute\* | second_of_minute(time) -> integer | SELECT second_of_minute(time '01:02:03') |
| sec_to_time\* | sec_to_time(integer) -> date | SELECT sec_to_time(10000) |
| subdate | subdate(date, INTERVAL expr unit) -> date, datetime | SELECT subdate(date('2008-01-02'), INTERVAL 31 day) |
| subtime | subtime(date, date) -> date | SELECT subtime(date('2008-12-12'), date('2008-11-15')) |
| str_to_date\* | str_to_date(string, format) -> date | SELECT str_to_date("01,5,2013", "%d,%m,%Y") |
| time | time(expr) -> time | SELECT time('13:49:00') |
| timediff | timediff(time, time) -> time | SELECT timediff(time('23:59:59'), time('13:00:00')) |
| timestamp | timestamp(date) -> date | SELECT timestamp('2001-05-07 00:00:00') |
| timestampadd | timestampadd(interval, integer, (date)) -> date | SELECT timestampadd(DAY, 17, datetime('2000-01-01 00:00:00')) |
| timestampdiff | timestampdiff(interval, date, date) -> integer | SELECT timestampdiff(YEAR, '1997-01-01 00:00:00', '2001-03-06 00:00:00') |
| time_format | time_format(date, string) -> string | SELECT time_format('1998-01-31 13:14:15.012345', '%f %H %h %I %i %p %r %S %s %T') |
| time_to_sec | time_to_sec(time) -> long | SELECT time_to_sec(time '22:23:00') |
| to_days | to_days(date) -> long | SELECT to_days(date '2008-10-07') |
| to_seconds | to_seconds(date) -> integer | SELECT to_seconds(date('2008-10-07')) |
| unix_timestamp | unix_timestamp(date) -> double | SELECT unix_timestamp(timestamp('1996-11-15 17:05:42')) |
| utc_date | utc_date() -> date | SELECT utc_date() |
| utc_time | utc_time() -> date | SELECT utc_time() |
| utc_timestamp | utc_timestamp() -> date | SELECT utc_timestamp() |
| week | week(date[mode]) -> integer | SELECT week(date('2008-02-20')) |
| weekofyear | weekofyear(date[mode]) -> integer | SELECT weekofyear(date('2008-02-20')) |
| week_of_year\* | week_of_year(date[mode]) -> integer | SELECT week_of_year(date('2008-02-20')) |
| year | year(date) -> integer | SELECT year(date('2001-07-05')) |
| yearweek\* | yearweek(date[mode]) -> integer | SELECT yearweek(date('2008-02-20')) |
String
| Function | Specification | Example |
|---|---|---|
| ascii | ascii(string) -> integer | SELECT ascii('h') |
| concat | concat(string, string) -> string | SELECT concat('hello', 'world') |
| concat_ws | concat_ws(separator, string, string…) -> string | SELECT concat_ws(" ", "Hello", "World!") |
| left | left(string, integer) -> string | SELECT left('hello', 2) |
| length | length(string) -> integer | SELECT length('hello') |
| locate | locate(string, string, integer) -> integer OR locate(string, string) -> integer | SELECT locate('o', 'hello'), locate('l', 'hello world', 5) |
| replace | replace(string, string, string) -> string | SELECT replace('hello', 'l', 'x') |
| right | right(string, integer) -> string | SELECT right('hello', 2) |
| rtrim | rtrim(string) -> string | SELECT rtrim('hello') |
| substring | substring(string, integer, integer) -> string | SELECT substring('hello', 2, 4) |
| trim | trim(string) -> string | SELECT trim('hello') |
| upper | upper(string) -> string | SELECT upper('hello world') |
Aggregate
| Function | Specification | Example |
|---|---|---|
| avg | avg(number T) -> T | SELECT avg(column) FROM my-index |
| count | count(number T) -> T | SELECT count(date) FROM my-index |
| min | min(number T) -> T | SELECT min(column) FROM my-index |
| show | show(string) -> string | SHOW TABLES LIKE my-index |
Advanced
| Function | Specification | Example |
|---|---|---|
| if | if(boolean, os_type, os_type) -> os_type | SELECT if(false, 0, 1),if(true, 0, 1) |
| ifnull | ifnull(os_type, os_type) -> os_type | SELECT ifnull(0, 1), ifnull(null, 1) |
| isnull | isnull(os_type) -> integer | SELECT isnull(null), isnull(1) |
Relevance-based search (full-text search)
These functions are only available in the WHERE clause. For their descriptions and usage examples in SQL and PPL, see Full-text search.