cancel
Showing results for 
Search instead for 
Did you mean: 

SQLi ( via Postgres data connection ) UNION supported?

RADSr
Partner
Partner

Setup: I need to union two Incorta tables.   I also need to add formula columns so not an Incorta SQL derived table.   

This *has* to be done in Incorta because half of the UNION is an Incorta snapshot table the contents of which do not persist in the DB.   The second half comes from the DB, but it's quicker to query it once and then not hit the DB again.  

I have a UNION query broken down to its simplest form:

select <column> from schema.table

UNION

select <column> from otherschema.othertable

Both parts work independently, column and othercolumn are both timestamps, and the result is:  

==============

SQL Error [P0001]: ERROR: Not a single engine compatible query Not supported query[ Unsupported Operation Exception: Not a single engine compatible query Not supported query].
Not a single engine compatible query Not supported query.
Error while fallback to Spark.
SQL Exception.
nothing to resolve

< more >

================

Is UNION supported?    What else should I be looking at?   

3 REPLIES 3

anurag
Employee
Employee

Yes, Union is supported.

I am assuming both the tables are in Incorta and you are using incorta over incorta SQLi to run this query?

Can you try this query using MV - Spark SQL?

 

I was trying to use SQLi but I gave up on it.   It does work via the notebook in an MV with Spark which lead to the separate post asking for guidelines about property settings - the MV didn't work until I set these:   

RADSr_0-1697205067358.png

https://community.incorta.com/t5/administrative-discussions/guidelines-for-spark-property-settings/t...

dylanwan
Employee
Employee

UNION is supported in SQLi via Spark fallback.  In the other word, if Spark cluster is deployed and the Spark SQL App is running, Incorta will try  to use the Spark engine to handle UNION, which is not handled directly by the Incorta analytics engine.

The error shown in your message indicates "Error while fallback to Spark".

We don't have the details and thus don't know why this happened.  You may want to check your configuration first.

Screen Shot 2023-10-24 at 5.51.23 AM.png