v5.10.1 (latest)

Overview

Installation and Configuration

Loading and Exporting Data

SQL

Immerse

Data Science

APIs and Interfaces

Tutorials

Troubleshooting and Special Topics

Functions and Operators

Functions and Operators (DML)

Basic Mathematical Operators

Operator

Description

`+`

`numeric`

Returns

`numeric`

`–`

`numeric`

Returns negative value of

`numeric`

`numeric1`

`+`

`numeric2`

Sum of and

`numeric1`

`numeric2`

`numeric1`

`–`

`numeric2`

Difference of and

`numeric1`

`numeric2`

`numeric1`

`*`

`numeric2`

Product of and

`numeric1`

`numeric2`

`numeric1`

`/`

`numeric2`

Quotient ( divided by )

`numeric1`

`numeric2`

- 1.Parenthesization
- 2.Multiplication and division
- 3.Addition and subtraction

Comparison Operators

Operator

Description

`=`

Equals

`<>`

Not equals

`>`

Greater than

`>=`

Greater than or equal to

`<`

Less than

`<=`

Less than or equal to

`BETWEEN`

`x`

`AND`

`y`

Is a value within a range

`NOT BETWEEN`

`x`

`AND`

`y`

Is a value not within a range

`IS NULL`

Is a value that is null

`IS NOT NULL`

Is a value that is not null

`NULLIF(`

`x`

`,`

`y`

`)`

Compare expressions **x** and **y**. If different, return **x**. If they are the same, return

`null`

. For example, if a dataset uses ‘NA’ for `null`

values, you can use this statement to return `null`

using `SELECT NULLIF(field_name,'NA')`

.`IS TRUE`

True if a value resolves to TRUE.

`IS NOT TRUE`

True if a value resolves to FALSE.

Mathematical Functions

Function

Description

`ABS(`

`x`

`)`

Returns the absolute value of **x**

`CEIL(`

`x`

`)`

Returns the smallest integer not less than the argument

`DEGREES(`

`x`

`)`

Converts radians to degrees

`EXP(`

`x`

`)`

Returns the value of e to the power of **x**

`FLOOR(`

`x`

`)`

Returns the largest integer not greater than the argument

`LN(`

`x`

`)`

Returns the natural logarithm of **x**

`LOG(`

`x`

`)`

Returns the natural logarithm of **x**

`LOG10(`

`x`

`)`

Returns the base-10 logarithm of the specified float expression **x**

`MOD(`

`x,y`

`)`

Returns the remainder of int **x** divided by int **y**

`PI()`

Returns the value of pi

`POWER(`

`x,y`

`)`

Returns the value of **x** raised to the power of **y**

`RADIANS(`

`x`

`)`

Converts degrees to radians

`ROUND(`

`x`

Rounds **x** to the nearest integer value, but does not change the data type. For example, the double value 4.1 rounds to the double value 4.

`ROUND_TO_DIGIT (`

`x,y`

`)`

Rounds **x** to **y** decimal places

`SIGN(`

`x`

`)`

Returns the sign of **x** as -1, 0, 1 if **x** is negative, zero, or positive

`SQRT(`

`x`

`)`

Returns the square root of **x**.

`TRUNCATE(`

`x,y`

`)`

Truncates **x** to **y** decimal places

`WIDTH_BUCKET(`

`target,lower-boundary,upper-boundary,bucket-count`

`)`

Define equal-width intervals (buckets) in a range between the lower boundary and the upper boundary, and returns the bucket number to which the target expression is assigned.

`target`

- A constant, column variable, or general expression for which a bucket number is returned.`lower-boundary`

- Lower boundary for the range of values to be partitioned equally.`upper-boundary`

- Upper boundary for the range of values to be partitioned equally.`partition_count`

- Number of equal-width buckets in the range defined by the lower and upper boundaries.

Expressions can be constants, column variables, or general expressions.

age of a customer accordingly:

`SELECT WIDTH_BUCKET(age, 0, 100, 10) FROM customer;`

``

For example, a customer of age 34 is assigned to bucket 3 ([30,40]) and the function returns the value 3.

Trigonometric Functions

Function

Description

`ACOS(`

`x`

`)`

Returns the arc cosine of **x**

`ASIN(`

`x`

`)`

Returns the arc sine of **x**

`ATAN(`

`x`

`)`

Returns the arc tangent of **x**

`ATAN2(`

`x`

`,`

`y`

`)`

Returns the arc tangent of **x** and **y**

`COS(`

`x`

`)`

Returns the cosine of **x**

`COT(`

`x`

`)`

Returns the cotangent of **x**

`SIN(`

`x`

`)`

Returns the sine of **x**

`TAN(`

`x`

`)`

Returns the tangent of **x**

Geometric Functions

Function

Description

`DISTANCE_IN_METERS(`

`fromLon`

`,`

`fromLat`

`,`

`toLon`

`,`

`toLat`

`)`

Calculates distance in meters between two WGS84 positions.

`CONV_4326_900913_X(`

`x`

`)`

Converts WGS84 latitude to WGS84 Web Mercator **x** coordinate.

`CONV_4326_900913_Y(`

`y`

`)`

Converts WGS84 longitude to WGS84 Web Mercator **y** coordinate.

String Functions

Function

Description

`CHAR_LENGTH(`

`str`

`)`

Returns the number of characters in a string. Only works with unencoded fields (ENCODING set to

`none`

).`KEY_FOR_STRING(`

`str`

`)`

Returns the dictionary key of a dictionary-encoded string column.

`LENGTH(`

`str`

`)`

Returns the length of a string in bytes. Only works with unencoded fields (ENCODING set to

`none`

).Pattern-matching Functions

Name

Example

Description

`str`

`LIKE`

`pattern`

`'ab' LIKE 'ab'`

Returns true if the string matches the pattern (case-sensitive)

`str`

`NOT LIKE`

`pattern`

`'ab' NOT LIKE 'cd'`

Returns true if the string does not match the pattern

`str`

`ILIKE`

`pattern`

`'AB' ILIKE 'ab'`

Returns true if the string matches the pattern (case-insensitive)

`str`

`REGEXP`

`POSIX pattern`

`'^[a-z]+r#x27;`

Lowercase string ending with r

`REGEXP_LIKE (`

`str`

`,`

`POSIX pattern`

`)`

`'^[hc]at'`

cat or hat

The following wildcard characters are supported by

`LIKE`

and `ILIKE`

:`%`

matches any number of characters, including zero characters.`_`

matches exactly one character.

Date/Time Functions

Function

Description

`CURRENT_DATE`

`CURRENT_DATE()`

Returns the current date in the GMT time zone.

Example:

`SELECT CURRENT_DATE();`

`CURRENT_TIME`

`CURRENT_TIME()`

Returns the current time of day in the GMT time zone.

Example:

`SELECT CURRENT_TIME();`

`CURRENT_TIMESTAMP`

`CURRENT_TIMESTAMP()`

`NOW()`

Return the current timestamp in the GMT time zone.

Example:

`SELECT CURRENT_TIMESTAMP();`

`DATE_TRUNC(`

**date_part**

`,`

**timestamp**

`)`

Truncates the **timestamp** to the specified **date_part**.

`DATE_TRUNC(week,...)`

starts on Monday (ISO), which is different than `EXTRACT(dow,...)`

, which starts on Sunday.Example:

`SELECT DATE_TRUNC(MINUTE, arr_timestamp) Arrival FROM flights_2008_10k LIMIT 10;`

`EXTRACT(`

**date_part**

`FROM`

**timestamp**

`)`

Returns the specified **date_part** from **timestamp**.

Example:

`SELECT EXTRACT(HOUR FROM arr_timestamp) Arrival_Hour FROM flights_2008_10k LIMIT 10;`

`INTERVAL`

**'count'**

**date_part**

Adds or Subtracts **count** **date_part** units from a timestamp. Note that **'count'** is enclosed in single quotes.

Example:

`SELECT arr_timestamp + INTERVAL '10' YEAR FROM flights_2008_10k LIMIT 10;`

`TIMESTAMPADD(`

**date_part**

`,`

**count**

`,`

**timestamp**

`|`

**date**

`)`

Adds an interval of **count** **date_part** to **timestamp** or **date** and returns signed **date_part** units in the provided **timestamp** or **date** form.

Example:

`SELECT TIMESTAMPADD(DAY, 14, arr_timestamp) Fortnight FROM flights_2008_10k LIMIT 10;`

`TIMESTAMPDIFF(`

**date_part**

`,`

**timestamp1**

`,`

**timestamp2**

`)`

Subtracts **timestamp1** from **timestamp2** and returns the result in signed **date_part** units.

Example:

`SELECT TIMESTAMPDIFF(MINUTE, arr_timestamp, dep_timestamp) Flight_Time FROM flights_2008_10k LIMIT 10;`

`DATEDIFF(`

**'date_part'**

`,`

**date**

`,`

**date**

`)`

Returns the difference between two **dates**, calculated to the lowest level of the **date_part** you specify. For example, if you set the **date_part** as DAY, only the year, month, and day are used to calculate the result. Other fields, such as hour and minute, are ignored.

Example:

`SELECT DATEDIFF('YEAR', plane_issue_date, now()) Years_In_Service FROM flights_2008_10k LIMIT 10;`

`DATEADD(`

**'date_part'**

`,`

**interval**

`,`

**date**

`|`

**timestamp**

`)`

Returns a date after a specified time/date interval has been added.

Example:

`SELECT DATEADD('MINUTE', 6000, dep_timestamp) Arrival_Estimate FROM flights_2008_10k LIMIT 10;`

`DATEPART(`

**'interval'**

`,`

**date**

`|`

**timestamp**

`)`

Returns a specified part of a given date or timestamp as an integer value. Note that **'interval'** must be enclosed in single quotes.

Example:

`SELECT DATEPART('YEAR', plane_issue_date) Year_Issued FROM flights_2008_10k LIMIT 10;`

Supported Types

Supported **date_part** types:

1

DATE_TRUNC [YEAR, QUARTER, MONTH, DAY, HOUR, MINUTE, SECOND, MILLISECOND,

2

MICROSECOND, NANOSECOND, MILLENNIUM, CENTURY, DECADE, WEEK,

3

WEEK_SUNDAY, QUARTERDAY]

4

EXTRACT [YEAR, QUARTER, MONTH, DAY, HOUR, MINUTE, SECOND, MILLISECOND,

5

MICROSECOND, NANOSECOND, DOW, ISODOW, DOY, EPOCH, QUARTERDAY,

6

WEEK, WEEK_SUNDAY, DATEEPOCH]

7

DATEDIFF [YEAR, QUARTER, MONTH, DAY, HOUR, MINUTE, SECOND, MILLISECOND,

8

MICROSECOND, NANOSECOND, WEEK]

Copied!

Supported **interval** types:

1

DATEADD [DECADE, YEAR, QUARTER, MONTH, WEEK, WEEKDAY, DAY,

2

HOUR, MINUTE, SECOND, MILLISECOND, MICROSECOND, NANOSECOND]

3

TIMESTAMPADD [YEAR, QUARTER, MONTH, WEEKDAY, DAY, HOUR, MINUTE,

4

SECOND, MILLISECOND, MICROSECOND, NANOSECOND]

5

DATEPART [YEAR, QUARTER, MONTH, DAYOFYEAR, QUARTERDAY, WEEKDAY, DAY, HOUR,

6

MINUTE, SECOND, MILLISECOND, MICROSECOND, NANOSECOND]

Copied!

Accepted Date, Time, and Timestamp Formats

Datatype

Formats

Examples

DATE

YYYY-MM-DD

2013-10-31

DATE

MM/DD/YYYY

10/31/2013

DATE

DD-MON-YY

31-Oct-13

DATE

DD/Mon/YYYY

31/Oct/2013

EPOCH

1383262225

TIME

HH:MM

23:49

TIME

HHMMSS

234901

TIME

HH:MM:SS

23:49:01

TIMESTAMP

DATE TIME

31-Oct-13 23:49:01

TIMESTAMP

DATETTIME

31-Oct-13T23:49:01

TIMESTAMP

DATE:TIME

11/31/2013:234901

TIMESTAMP

DATE TIME ZONE

31-Oct-13 11:30:25 -0800

TIMESTAMP

DATE HH.MM.SS PM

31-Oct-13 11.30.25pm

TIMESTAMP

DATE HH:MM:SS PM

31-Oct-13 11:30:25pm

TIMESTAMP

1383262225

- For two-digit years, years 69-99 are assumed to be previous century (for example, 1969), and 0-68 are assumed to be current century (for example, 2016).
- For four-digit years, negative years (BC) are not supported.
- Hours are expressed in 24-hour format.
- When time components are separated by colons, you can write them as one or two digits.
- Months are case insensitive. You can spell them out or abbreviate to three characters.
- For timestamps, decimal seconds are ignored. Time zone offsets are written as +/-HHMM.
- For timestamps, a numeric string is converted to +/- seconds since January 1, 1970. Supported timestamps range from -30610224000 (January 1, 1000) through 29379456000 (December 31, 2900).
- On output, dates are formatted as YYYY-MM-DD. Times are formatted as HH:MM:SS.
- Linux EPOCH values range from -30610224000 (1/1/1000) through 185542587100800 (1/1/5885487). Complete range in years: +/-5,883,517 around epoch.

Statistical and Aggregate Functions

Both double-precision (standard) and single-precision floating point statistical functions are provided. Single-precision functions run faster on GPUs but might cause overflow errors.

Double-precision FP Function

Single-precision FP Function

Description

`AVG(`

**x**

`)`

Returns the average value of **x**

`COUNT()`

Returns the count of the number of rows returned

`COUNT(DISTINCT`

**x**

`)`

Returns the count of distinct values of **x**

`APPROX_COUNT_DISTINCT(`

**x**

`,`

**e**

`)`

Returns the approximate count of distinct values of **x** with defined expected error rate **e**, where **e** is an integer from 1 to 100. If no value is set for e, the approximate count is calculated using the system-wide

`hll-precision-bits`

configuration parameter.`APPROX_MEDIAN(`

**x**

`)`

Returns the approximate median of **x**. Two server configuration parameters affect memory usage:

`APPROX_PERCENTILE(`

**x**

`,`

**y**

`)`

Returns the approximate quantile of , where is the value between 0 and 1.

`x`

`y`

For example,

`y=0`

returns `MIN(x)`

, `y=1`

returns `MAX(x)`

, and `y=0.5`

returns `APPROX_MEDIAN(x)`

.`MAX(`

**x**

`)`

Returns the maximum value of **x**

`MIN(`

**x**

`)`

Returns the minimum value of **x**

`SINGLE_VALUE`

Returns the input value if there is only one distinct value in the input; otherwise, the query fails.

`SUM(`

**x**

`)`

Returns the sum of the values of **x**

`SAMPLE(`

**x**

`)`

Returns one sample value from aggregated column **x**. For example, the following query returns population grouped by city, along with one value from the state column for each group:

`LAST_SAMPLE`

, which is now deprecated.`CORRELATION(x, y)`

`CORRELATION_FLOAT(x, y)`

Alias of CORR. Returns the coefficient of correlation of a set of number pairs.

`CORR(x, y)`

`CORR_FLOAT(x, y)`

Returns the coefficient of correlation of a set of number pairs.

`COVAR_POP(x, y)`

`COVAR_POP_FLOAT(x, y)`

Returns the population covariance of a set of number pairs.

`COVAR_SAMP(x, y)`

`COVAR_SAMP_FLOAT(x, y)`

Returns the sample covariance of a set of number pairs.

`STDDEV(x)`

`STDDEV_FLOAT(x)`

Alias of STDDEV_SAMP. Returns sample standard deviation of the value.

`STDDEV_POP(x)`

`STDDEV_POP_FLOAT(x)`

Returns the population standard the standard deviation of the value.

`STDDEV_SAMP(x)`

`STDDEV_SAMP_FLOAT(x)`

Returns the sample standard deviation of the value.

`VARIANCE(x)`

`VARIANCE_FLOAT(x)`

Alias of VAR_SAMP. Returns the sample variance of the value.

`VAR_POP(x)`

`VAR_POP_FLOAT(x)`

Returns the population variance sample variance of the value.

`VAR_SAMP(x)`

`VAR_SAMP_FLOAT(x)`

Returns the sample variance of the value.

`COUNT(DISTINCT`

`x`

`)`

, especially when used in conjunction with GROUP BY, can require a very large amount of memory to keep track of all distinct values in large tables with large cardinalities. To avoid this large overhead, use APPROX_COUNT_DISTINCT.`APPROX_COUNT_DISTINCT(`

`x`

`,`

`e`

`)`

gives an approximate count of the value**x**, based on an expected error rate defined in**e**. The error rate is an integer value from 1 to 100. The lower the value of**e**, the higher the precision, and the higher the memory cost. Select a value for**e**based on the level of precision required. On large tables with large cardinalities, consider using`APPROX_COUNT_DISTINCT`

when possible to preserve memory. When data cardinalities permit, OmniSci uses the precise implementation of`COUNT(DISTINCT`

`x`

`)`

for`APPROX_COUNT_DISTINCT`

. Set the default error rate using the`-hll-precision-bits`

configuration parameter.- The accuracy of
`APPROX_MEDIAN (`

`x`

`)`

upon the distribution of data. For example:- For 100,000,000 integers (1, 2, 3, ... 100M) in random order, APPROX_MEDIAN can provide a highly accurate answer 5+ significant digits.
- For 100,000,001 integers, where 50,000,000 have value of 0 and 50,000,001 have value of 1, APPROX_MEDIAN returns a value close to 0.5, even though the median is 1.

- Currently, OmniSci does not support grouping by non-dictionary-encoded strings. However, with the
`SAMPLE`

aggregate function, you can select non-dictionary-encoded strings that are presumed to be unique in a group. For example:1SELECT user_name, SAMPLE(user_decription) FROM tweets GROUP BY user_name;Copied!If the aggregated column (user_description in the example above) is not unique within a group,`SAMPLE`

selects a value that might be nondeterministic because of the parallel nature of OmniSci query execution.

Miscellaneous Functions

Function

Description

`SAMPLE_RATIO(`

**x**

`)`

Returns a Boolean value, with the probability of

`True`

being returned for a row equal to the input argument. The input argument is a numeric value between 0.0 and 1.0. Negative input values (return `False`

), input values greater than 1.0 returns `True`

, and null input values return `False`

.The result of the function is deterministic per row; that is, all calls of the operator for a given row return the same result. The sample ratio is probabilistic, but is generally within a thousandth of a percentile of the actual range when the underlying dataset is millions of records or larger.

The following example filters approximately 50% of the rows from

`t`

and returns a count that is approximately half the number of rows in `t`

:`SELECT COUNT(*) FROM t WHERE SAMPLE_RATIO(0.5)`

User-Defined Functions

You can create your own C++ functions and use them in your SQL queries.

- User-defined Functions (UDFs) require clang++ version 9. You can verify the version installed using the command
`clang++ --version`

. - UDFs currently allow any authenticated user to register and execute a runtime function. By default, runtime UDFs are globally disabled but can be enabled with the runtime flag
`enable-runtime-udf`

.

- 1.Create your function and save it in a .cpp file; for example, /var/lib/omnisci/udf_myFunction.cpp.
- 2.Add the UDF configuration flag to omnisci.conf. For example:1udf = "/var/lib/omnisci/udf_myFunction.cpp"Copied!
- 3.Use your function in a SQL query. For example:1SELECT udf_myFunction FROM myTableCopied!

Sample User-Defined Function

This function, udf_diff.cpp, returns the difference of two values from a table.

1

#include <cstdint>

2

#if defined(_CUDA_ARCH) && defined(CUDACC) && defined(clang_)

3

#define DEVICE _device_

4

#define NEVER_INLINE

5

#define ALWAYS_INLINE

6

#else

7

#define DEVICE

8

#define NEVER_INLINE _attribute_((noinline))

9

#define ALWAYS_INLINE _attribute_((always_inline))

10

#endif

11

#define EXTENSION_NOINLINE extern "C" NEVER_INLINE DEVICE EXTENSION_NOINLINE int32_t udf_diff(const int32_t x, const int32_t y) { return x - y; }

Copied!

Include the standard integer library, which supports the following datatypes:

- bool
- int8_t (cstdint), char
- int16_t (cstdint), short
- int32_t (cstdint), int
- int64_t (cstdint), size_t
- float
- double
- void

1

#include <cstdint>

Copied!

The next four lines are boilerplate code that allows OmniSci to determine whether the server is running with GPUs. OmniSci chooses whether it should compile the function inline to achieve the best possible performance.

1

#if defined(_CUDA_ARCH) && defined(CUDACC) && defined(clang_)

2

#define DEVICE _device_

3

#define NEVER_INLINE

4

#define ALWAYS_INLINE

5

#else

6

#define DEVICE

7

#define NEVER_INLINE _attribute_((noinline))

8

#define ALWAYS_INLINE _attribute_((always_inline))

9

#endif

Copied!

The next line is the actual user-defined function, which returns the difference between INTEGER values x and y.

1

EXTENSION_NOINLINE int32_t udf_diff(const int32_t x, const int32_t y) { return x - y; }

Copied!

To run the

`udf_diff`

function, add this line to your /var/lib/omnisci/omnisci.conf file (in this example, the .cpp file is stored at /var/lib/omnisci/udf_diff.cpp):1

udf = "/var/lib/omnisci/udf_diff.cpp"

Copied!

Restart the OmniSci server.

Use your command from an OmniSci SQL client to query, for example, a table named myTable that contains the INTEGER columns

`myInt1`

and `myInt2`

.1

SELECT udf_diff(myInt1, myInt2) FROM myTable LIMIT 1;

Copied!

OmniSci returns the difference as an INTEGER value.

Last modified 3mo ago

Export as PDF

Copy link

Contents

Basic Mathematical Operators

Mathematical Operator Precedence

Comparison Operators

Mathematical Functions

Trigonometric Functions

Geometric Functions

String Functions

Pattern-matching Functions

Date/Time Functions

Supported Types

Accepted Date, Time, and Timestamp Formats

Usage Notes

Statistical and Aggregate Functions

Usage Notes

Miscellaneous Functions

User-Defined Functions

Sample User-Defined Function