.png "Employee"){kind=icon}
Introduction
The Incorta SQL Interface, known as SQLi, is an integral part of Incorta that allows external clients to connect and integrate with the Incorta data platform. External BI tools like Power BI, Tableau, and Microstrategy can harness the data in incorta with the PostgreSQL JDBC connector and with the speed of Incorta Direct Data Mapping and Spark.
What you need to know before reading this article
Basic knowledge of Incorta and familiarity with Incorta UI which is covered by this course
Let's Go
How does SQLi work?
Clients can select the component with which to execute a query. Based on the port you choose, you can let the Incorta Engine or Spark handle the query.
Based on the functions used and whether they are incorta native functions or Spark UDFs (user defined functions), Incorta decides whether to process the query with the Incorta engine or whether to redirect it to Spark
SQLi is an Incorta module enabling external clients to connect to Incorta as if it is a PostgreSQL database. This allows the use of external BI tools (like Tableau and Power BI) as the front end while Incorta serves as their data source. Under the hood, SQLi uses both the Incorta engine and Spark to fulfill clients queries, favoring Incorta’s engine (for speed) where possible.
So, clients send standard PostgreSQL queries to Incorta and it then decides whether:
- To run the query against the Incorta engine and return the result from its in-memory stores, or
- To delegate the query to Spark which will run directly over the corresponding parquet files.
When not to use SQLi
You should not use SQLi for Incorta-over-Incorta (IoI) tables that consume data from the same schemas in which they reside as this will cause the SQLi to read from the prior version of the table because of data isolation.
“Data isolation here simply means that the state of the data is maintained until the load of the table is successfully finished; so don’t expect seeing the new data being loaded in the current load job if an Incorta-over-Incorta table is based on a table being loaded in the same job”
In recent versions of Incorta, SQLi for Incorta-over-Incorta is discouraged and derived tables of type `Incorta SQL` or `Incorta Analyzer` should be used instead.
- Incorta Analyzer provides a drag and drop way to build new tables based on existing tables.
- Incorta SQL enables users to create tables using SQL for more complex needs.
Related Material
