Skip to content

Adding New SQL

This page shows you how to use the Datameer SQL Editor to develop ad-hoc and exploratory new queries without pre-adding datasets to a Project. This might be useful if you want to copy and paste your query from other tools.

Exploring the New SQL Editor dialog, you find all associated datasets in the Data Browser on the left side after you applied your new SQL query initially. 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 and the 'Functions' tab from where you can add functions.

Accessing the New SQL Editor#

To access the SQL Editor click on "New SQL Editor" from the Workbench. Note that a Project must have been created before. The SQL Editor opens.

Applying a New Query#

To apply a new SQL query, e.g. to join datasets:

  1. Enter the query to be used in the SQL Query field and confirm with "Run Query". Note that all identifiers must be fully qualified. Note that the syntax must match the Snowflake query syntax. The query runs and the preview data is displayed below.

  2. Confirm with "Apply". The Workbench opens. The Flow displays the pipeline and associated nodes.

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:

Editing/ Continuing a New SQL Query#

After the initial query, you can apply further operations to the view, publish it to Snowflake or edit the query in the SQL Editor again.

  • To add further operations, click on "+" associated to your query and select the operation. Find all applicable transformation options here.

  • To edit the initial SQL query, click on "Open in SQL Editor" and make your adjustments. The current SQL statement is always displayed in the Inspector in the Workbench.

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