References → Discovery of non-optimized tables via Advanced SQLi
Non-optimized tables are physical schema tables that are extracted and materialized to Parquet files; however, they are not loaded into the Engine memory and have no DDM files created for them. These non-optimized tables were accessible by the classic SQL Interface via Spark port.
Non-optimized tables and Advanced SQLi
With the introduction of Incorta 2024.1.3 Cloud and On-Premises releases, non-optimized tables can be accessible via the Advanced SQL Interface. As a result, all consumers of the Advanced SQLi, such as external tools: Power BI and Tableau, can discover and query non-optimized tables. The Advanced SQLi accesses these tables by reading their Parquet files.
Limitations
As of the 2024.1.3 release, the non-optimized tables that the Advanced SQLi can access are those that do NOT have any of the following:
- Security filters
- Formula columns
- Encrypted columns
The support for querying and discovering non-optimized tables via the Advanced SQLi is NOT available on Incorta Cloud before 2024.1.3 although the Advanced SQLi is available on Cloud starting 2024.1.0.
Prerequisites and Configurations
The following are the prerequisites and configurations required to have non-optimized tables accessible via Advanced SQLi.
- The Advanced SQL Interface is enabled and properly configured. For details, refer to References → Advanced SQL Interface.
- After new installations or upgrades to 2024.1.3 or later releases, you must force the sync of the Spark Metastore.
- For Cloud installations, contact the Incorta Support team to force the sync on your cluster.
- For On-Premises installations, follow the steps on the Advanced SQLi Setup and Configuration reference document.
Requirements for On-Premises installations on cloud storage
For Cloud installations and On-Premises installations on local servers, the Advanced SQLi can access Parquet files without further configurations. However, for 2024.1.x On-Premises tenants that use a cloud storage file system, such as Azure, AWS, and Google Cloud Storage (GCS), you must manually set some additional configurations to allow the Advanced SQLi access to Parquet files on these services.
For details, refer to References → SparkX Access to Cloud Storage.
Disabling and enabling the support for non-optimized tables
By default, when the Advanced SQLi is enabled, the support for querying non-optimized tables is enabled.
To disable it on a Cloud cluster, contact Incorta Support.
To disable it on an On-Premises cluster,
- Edit the
node.properties
file orservice.properties
file for each Analytics node or service and set theinx.sparksql.sync.nonoptimizedtables
property tofalse
. - Restart the Analytics services.
- Force the sync of the Spark Metastore.
- Enabling or disabling the discovery of non-optimized tables via the Advanced SQLi requires restarting all the Analytics services and forcing the sync of the Spark Metastore for all tenants.
- The settings specified on the
service.properties
file override those specified on thenode.proeprties
.
Feature limitations
- Business Views based on non-optimized tables are not supported. While these views will be discoverable via the Advanced SQLi, querying them will return no data or throw an error according to the scalability mode used.