Skip to content

Using the SQL Editor

This page shows you how to use the Datameer SQL Editor to develop ad-hoc and exploratory queries in a Project to complete your use cases faster.

SQL Editor General Information#

When accessing the SQL Editor page, you find all available Snowflake sources in the Data Browser on the left side. All source tables are collapsable to view the single columns. In the middle of the SQL Editor page, you can insert and perform your SQL queries as well as view the columns preview below. On the right side you have the 'Columns' tab where you can search for columns and list them as well.

NOTE: Datameer supports all available Snowflake operations although they are not listed. We will add the functions step by step.

Accessing the SQL Editor#

To access the SQL Editor click on a "+" button next to a view in the Flow Area in the Workbench and then click on "SQL". The SQL Editor opens.

Applying a New Query#

To apply your query:

  1. Enter the query to be used in the SQL Query field and confirm with "Run Query". Note that the syntax must match the Snowflake query syntax. The query runs and the output is displayed below.
  2. Close the SQL Editor by clicking on "Done". Note that if you close the SQL Editor without confirming with 'Done', the already run query is not saved. You are guided back to the Project's Workbench.

Using the Formatter#

Using the SQL Formatter allows you to format your SQL.

  1. Enter the SQL in the text block and then click "Formatter". The formatter executes.

  2. View the formatted SQL.

SQL Syntax#

The SQL syntax is highlighted to identify the single syntax expressions. Find the following highlighting in the SQL Editor:

Deleting a Query#

To delete an already applied SQL query from the operation stack, just click on the "Delete" icon next to the formula name in the operation stack. The operation is deleted immediately.

Limitations#

INFO

Datameer supports nearly 100% of SQL that can be queried in Snowflake (except the unsupported data types). If a SQL query is working in Snowflake but not in Datameer, please report a bug in a service ticket.

Unsupported Data Types#

Currently the following data types are not supported in Datameer. Therefore all columns with those unsupported data types are invisible in Datameer.

The following data types are not supported:

  • BINARY/ VARBINARY

SQL Constructs with Aliases#

The SQL query in the SQL Editor will be part of an enveloping SQL query which connects the query to the predecessor views in the Project. There might be a name conflict in case the SQL Editor query picks table aliases which are already used in the enveloping SQL.

Do to not use existing view names from the Project in the SQL query as table aliases, e.g.:

  • Views in Project: CUSTOMER, CUSTOMER2
  • SQL Editor query: SELECT * FROM CUSTOMER AS CUSTOMER2

Do not define table aliases that are overshadowing existing tables, e.g.:

SELECT A, (SELECT B FROM T1 WHERE A = 'a2') AS B FROM table1 T1

User-Defined Functions (UDF)#

When using a user-defined function (UDF), please ensure that the Datameer service account has appropriate permissions

GRANT USAGE ON FUNCTION <function_name> TO ROLE <role_name>

for this function and that the UDF is called by a fully qualified name:

database_name.schema_name.function_name