SQL Worksheet
SQL worksheets are available as of Datameer 7.2
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 SQL editor interface emulates structured query language. The following SQL features are available for use within a Datameer SQL worksheet:
Title | SQL command |
---|---|
Logic functions |
|
Comparison functions |
|
SQL logic |
|
Numeric functions |
|
String functions |
|
Date/Time/Timestamp functions |
|
Case functions |
|
Aggregation functions |
|
Set clauses |
|
Join clauses |
|
Data types mapping
Datameer | SQL |
---|---|
date | timestamp |
integer | big integers |
big decimal | decimal |
big intiger | decimal |
floats | doubles |
strings | varchars |
boolean | boolean |
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 call | Response |
---|---|
SELECT salesInfo.Date | Error |
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.
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 functions in the SQL editor isn't currently supported.
- Datameer's LIST data type isn't supported for SQL worksheets.