Versions Compared

Old Version 4

changes.mady.by.user Juliane Wetzel

Saved on

compared with

New Version 5

changes.mady.by.user Juliane Wetzel

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Table of Contents

...

SQL worksheets give you the ability to write SQL queries directly in a workbook. Use SQL worksheets to combine processes that might take several steps using the traditional interface.

Using SQL Worksheets

Open a SQL worksheet

Open a workbook, select Sheets from the menu bar and select Add SQL Sheet.

A new worksheet is added to your workbook with a multiline SQL editor instead of a formula editor.

...

Supported features

The Datameer X SQL editor interface emulates structured query language. The following SQL features are available for use within a Datameer X SQL worksheet:

TitleSQL command
Logic functions
  • AND
  • OR
Comparison functions

  • value1 = value2

  • value1 <> value2

  • value1 > value2

  • value1 >= value2

  • value1 < value2

  • value1 <= value2

  • value IS NULL

  • value IS NOT NULL

  • value1 IS DISTINCT FROM value2

  • value1 IS NOT DISTINCT FROM value2

  • value1 BETWEEN value2 AND value3

  • value1 NOT BETWEEN value2 AND value3

  • string1 LIKE string2 [ ESCAPE string3 ]

  • string1 NOT LIKE string2 [ ESCAPE string3 ]

  • string1 SIMILAR TO string2 [ ESCAPE string3 ]

  • string1 NOT SIMILAR TO string2 [ ESCAPE string3 ]

  • value IN (value [, value]*)

  • value NOT IN (value [, value]*)

  • value IN (sub-query)

  • value NOT IN (sub-query)

  • EXISTS (sub-query)

  • NOT EXISTS (sub-query)

SQL logic

  • boolean1 OR boolean2

  • boolean1 AND boolean2

  • NOT boolean

  • boolean IS FALSE

  • boolean IS TRUE

  • boolean IS NOT TRUE

  • boolean IS UNKNOWN

  • boolean IS NOT UNKNOWN

Numeric functions

  • + numeric

  • - numeric

  • numeric1 + numeric2

  • numeric1 - numeric2

  • numeric1 * numeric2

  • numeric1 / numeric2

  • POWER(numeric1, numeric2)

  • ABS(numeric)

  • SQRT(numeric)

  • LN(numeric)

  • LOG10(numeric)

  • EXP(numeric)

  • RAND([seed])

  • ACOS(numeric)

  • ASIN(numeric)

  • ATAN(numeric)

  • ATAN2(numeric, numeric)

  • COS(numeric)

  • COT(numeric)

  • DEGREES(numeric)

  • PI

  • RADIANS(numeric)

  • SIN(numeric)

  • TAN(numeric)

String functions

  • string || string

  • CHAR_LENGTH(string)

  • CHARACTER_LENGTH(string)

  • UPPER(string)

  • LOWER(string)

  • POSITION(string1 IN string2)

  • REPLACE (string_expression, string_pattern, string_replacement)

  • TRIM( { BOTH | LEADING | TRAILING } string1 FROM string2)

  • SUBSTRING(string FROM integer)

  • SUBSTRING(string FROM integer FOR integer)

Date/Time/Timestamp functions

  • LOCALTIME

  • LOCALTIME(precision)

  • LOCALTIMESTAMP

  • LOCALTIMESTAMP(precision)

  • CURRENT_DATE

  • EXTRACT(timeUnit FROM datetime)

Case functions
  • CASE value
  • CASE
  • NULLIF(value, value)
  • COALESCE(value, value [, value ]*)
Aggregation functions
  • COUNT( [ ALL | DISTINCT ] value [, value ]*)
  • COUNT(*)
  • APPROX_COUNT_DISTINCT(value [, value ]*)
  • AVG( [ ALL | DISTINCT ] numeric)
  • SUM( [ ALL | DISTINCT ] numeric)
  • MAX( [ ALL | DISTINCT ] value)
  • MIN( [ ALL | DISTINCT ] value)
  • STDDEV_POP( [ ALL | DISTINCT ] numeric)
  • STDDEV_SAMP( [ ALL | DISTINCT ] numeric)
  • VAR_POP( [ ALL | DISTINCT ] value)
  • VAR_SAMP( [ ALL | DISTINCT ] numeric)
Set clauses
Join clauses
  • INNER JOIN
  • LEFT OUTER JOIN
  • RIGHT OUTER JOIN
  • FULL OUTER JOIN
  • INNER JOIN (sub-query)
  • LEFT OUTER JOIN (sub-query)
  • RIGHT OUTER JOIN (sub-query)
  • FULL OUTER JOIN (sub-query)

...

  • Create Table, Delete Table, and Modify Table structure / DDL aren't currently supported. 
  • Window functions aren't currently supported.
  • Using Hive UDFs /UDAs in the SQL editor isn't currently supported.
  • Using all the Datameer X functions in the SQL editor isn't currently supported.
  • Datameer's LIST data type isn't supported for SQL worksheets.

Performance Tuning

Tip

If the main query contains the function 'ORDER BY' the Datameer X SQL engine will limit sort task parallelism to '1'. For a better performance we recommend to apply the 'SORT' function to the SQL sheet instead.

If the main query contains the function 'ORDER BY' with a limitation we recommend to apply the 'SORT' function with a limit.

Both functions turn the SQL sheet to an SQL sheet with inline sort. Datameer X then performs parallel sorting more efficiently.