Skip to content

Sql editor

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

SQL Editor General Information#

When accessing the SQL Editor page, you find all available Snowflake sources in the Source 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.

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". The query runs and the output is displayed below.
  2. Close the SQL Editor by clicking on "Done". You are guided back to the Project's Workbench.

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#

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
  • Semi-structured, like VARIANT, OBJECT and ARRAY
  • Geospatial

SQL Constructs That Are Currently Not Supported in the Free Form SQL Editor#

The following constructs can not be applied at the moment:

  • PIVOT: Use custom SQL with 'GROUP BY's.
  • UNPIVOT: Use custom SQL with 'GROUP BY's.
  • WITH: No merge mechanism yet. Not supported yet.
  • TOP <n>: Syntax is not supported yet.
    • Does not work for:
      select top 4 from SUPPLIER
      
    • Workaround: Use 'LIMIT'
  • AT/ BEFORE: Syntax is not supported yet.
    • Does not work:
      select * from SUPPLIER at(offset => -86400)
      
  • SAMPLE/ TABLESAMPLE: Syntax is not supported yet.
    • Does not work:
      select * from SUPPLIER sample(10)
      
  • CHANGES: Syntax is not supported yet.
    • Does not work:
      select * from t1 changes(information => default) at(timestamp => $ts1)
      
  • CONNECT BY: Syntax is not supported yet.
    • Does not work:
      select employee_id, manager_id, title
        from employees
          start with title = 'President'
          connect by
            manager_id = prior employee_id
      order by employee_id;
      
  • MATCH_RECOGNIZE: Syntax is not supported yet.
  • From <table function>: Mostly not supported; Usage of custom UDTF tables functions is working.
    • Works for:
      select * from table(fibonacci_sequence_udtf(6.0::float))
      
    • Does not work:
      select * from table(information_schema.pipe_usage_history(
        date_range_start=>to_timestamp_tz('2017-10-24 12:00:00.000 -0700'),
        date_range_end=>to_timestamp_tz('2017-10-24 12:30:00.000 -0700')))
      
  • From <stage>: Syntax is not supported yet.
    • Does not work:
      select t.$1, t.$2 from @mystage1 t
      
  • QUALIFY: Syntax is not supported yet.
    • Does not work:
      select i, p, o from qt qualify row_number() over (partition by p order by o) = 1
      
  • Querying semi-sturctured data: Partly supported only
    • Works for: Bracket syntax, e.g.
      select src['salesperson']['name'] from car_sales
      
    • Does not work for:
      select src:salesperson.name from car_sales;
      
    • Workaround: Use 'Dot notation' or 'get/ get_path' functions

Minor Limitations#

  • 'Order By' in a subquery is parsed incorrectly
    • Works for:
      select S_NAME from SUPPLIER ORDER BY S_NATIONKEY DESC
      
    • Does not work for:
      select * from (select S_NAME from SUPPLIER ORDER BY S_NATIONKEY DESC)
      
  • 'OFFSET' without 'LIMIT' not possible
    • Does not work for:
      select * from demo1 order by i limit null offset 3
      
  • (+) (left/right outer) Join syntax not supported
    • Works for:
      select c1, c2 from t1 left outer join t2 on c1 = c2
      
    • Works not for:
      select c1, c2 from t1, t2 where c1 = c2(+)
      
  • 'ONLY' in 'FETCH' is mandatory
    • Works for: SELECT * FROM T1 FETCH FIRST 1 ROW ONLY
    • Works not for : SELECT * FROM T1 FETCH FIRST 1 ROW

SQL Literals/ Keywords#

  • Unquoting in string literal with backslash or $: Syntax is not supported yet.
    • Does not work for:
      SELECT 'a\'b'
      
    • Does not work for:
      SELECT $$a'b$$'
      
    • Workaround:
      SELECT 'a''b'
      
  • Time literals with timezone: Syntax is not supported yet.
    • Works for:
      SELECT TIME '19:30:20.05', TIMESTAMP '1997-07-15 19:30:20';
      
    • Does not work for:
      SELECT TIME '19:30:20+03:00', TIMESTAMP '1997-07-15 19:30:20.05-10:30'
      
  • Date & Time Data Types: Syntax is not supported yet.
    • Does not work for:
      select to_date ('2019-02-28') + INTERVAL '1 day, 1 year'
      
    • Workaround: Use the 'dateadd' function instead
  • 'date': Syntax is not supported yet.
    • Does not work for:
      SELECT date from SUPPLIER
      
    • Workaround: Quote 'date', e.g.
      SELECT "DATE" from SUPPLIER
      

Unsupported or Partially Supported Functions#

The usage of the following functions is either not supported or supported only partly:

  • 'FLOOR': No second scale_expr parameter possible
  • 'TRIM': No second characters parameter possible
  • 'MINHASH': No use of '*', e.g.
    select minhash(5, *)
    
  • 'POSITION': only IN syntax usable
    • Works for:
      select position(UPPER('an') in 'banana')
      
    • Does not Work for:
      select position('an', 'banana', 1)
      
  • time(): Not supported yet.
    • Workaround: Use 'to_time()'.
  • ILIKE ANY: Not supported.
  • INSERT: Not supported.
  • LIKE ANY: Not supported.
  • REGEXP: Not supported.
  • TRY_CAST: Not supported.
  • No infix cast to number
    • Works for:
      select 10::bigint
      
    • Does not work for:
      select 10::number(38,0)