0

UNION in Incorta

Dear all,

is there a known issue concerning the union in incorta? 

I'm trying to a SQL union with a table in one of my schema and whenever I execute the query, it takes ages and at some point I have an error message.

 

thanks for your help 

5replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Are you talking about extracting data from source database with UNION?

    I do not run into SQL union issue.  However, if we want to make it run faster, one option we can consider is to put the logic as multiple data sets against the same source database.

    Please note that when we use multiple data sets, each will be considered as a separate extraction and will be run in parallel.  It also means that the connection pool against the data source should be set to a higher number if we are creating a large number of data sets.  

    The SQL extraction query performance can be further divided into multiple chunks by using the chunking feature if you are using Oracle or MySQL.  We are working on the feature to make it available to other database.  

    Hope this helps.

    Like
  • Hi Dylan,

     

    thank you for your answer. 

     

    When I directly ran the SQL in Incorta , I have no issue. 

    I have been told by my Incorta adminnistrator, that I should first : 

    Create a Table with all my data inside, for example : 

    Create a incorta TABLE "TEST" with SELECT * from mytable

    -> is that the best practice?

    Then, create another table with SELECT Col1, Col2 from TEST where Col3 is not null UNION SELECT Col1, Col2 from TEST where Col4 > 4 etc....etc... but then it 's running forever and I end up with a timeout issue.

    If I directly use  "SELECT Col1, Col2 from mytable where Col3 is not null UNION SELECT Col1, Col2 from mytable where Col4 > 4" it works fast. 

    Like
    • Luigi Riccio Are you creating materialized views or a SQL database in your second query?  

      It should work if you are using Materialized View.
       

      Like
      • Luigi Riccio
      • Starbucks
      • Luigi_Riccio
      • 1 mth ago
      • 1
      • Reported - view

      Dylan Wan Hi there.

      surprisingly it is working with Materialized View. :). I haven't really figured out what's the difference between Materialized View or an SQL statement querying the incorta tables but I am happy it works , thank you 

      Like 1
  • Doing union in Materialized View or sql union query using sqli connection (using postgres adaptor to query incorta on port 5436) should work and both will use spark.

    Like
Like Follow
  • 1 mth agoLast active
  • 5Replies
  • 31Views
  • 3 Following

Product Announcement


We are happy to
announce Incorta 4.8 !!!