topic Re: SQLi ( via Postgres data connection ) UNION supported? in Administrative Discussions

SQLi ( via Postgres data connection ) UNION supported?

RADSr — Tue, 10 Oct 2023 15:39:42 GMT

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?

Re: SQLi ( via Postgres data connection ) UNION supported?

anurag — Fri, 13 Oct 2023 13:44:06 GMT

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?

Re: SQLi ( via Postgres data connection ) UNION supported?

RADSr — Fri, 13 Oct 2023 13:51:53 GMT

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:

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

Re: SQLi ( via Postgres data connection ) UNION supported?

dylanwan — Tue, 24 Oct 2023 12:53:02 GMT

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.