Using SQL Sheets

Owned by Juliane Wetzel
Last updated: May 10, 2023 by Konsta Danyliuk (Deactivated)

SQL Worksheets

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.

Write supported SQL syntax to create a query in Datameer's SQL editor like you would when using an actual SQL database.

To activate the query, click the check box on the right side of the SQL editor.

SQL Editor Features

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)


Data types mapping

DatameerSQL
datetimestamp
integerbig integers
big decimaldecimal
big intigerdecimal
floatsdoubles
strings varchars
booleanboolean

Using  reserved SQL keywords 

There are reserved keywords that SQL uses  for defining, manipulating, and accessing databases. 

If you reference a workbook column in an SQL worksheet that has the same name as a reserved keyword, an error is displayed. 

To reference a column in an SQL sheet that has the same name as a reserved SQL keyword, the reference column name must be escaped using backticks (`) around the word. 

Example

SQL sheet callResponse
SELECT salesInfo.DateError
SELECT salesInfo.`Date`Success

In the example above, the reserved SQL keyword Date must be escaped using backticks.

Permissions

The run, edit, and delete privileges for the SQL worksheet are as they are assigned for the workbook.

Commentary Function

You can comment in the SQL Editor:

  • for commenting a single line: 
     
  • for commenting multiline: 

Limitations

  • 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

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 applying 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.