1. Home
  2. Previous Releases
  3. Latest Release
  4. Guides
  5. Concepts
  6. Tools
  7. References
    1. Apache Spark
    2. Built-in Functions
    3. Incorta Component SDK
    4. Incorta Connector SDK
    5. Connectors
    6. Data Studio Recipes
    7. Public API
    8. Security
    9. SQL Interface
      1. SQL Interface
      2. Configure Spark with SQLi
      3. Advanced SQL Interface
        1. Setup and Configuration
        2. SparkX Metastore Sync
        3. Discovery of Non-Optimized Tables
        4. SparkX Access to Cloud Storage
        5. Data Extraction via Advanced SQLi and Public API
        6. Scalability Modes
        7. Advanced SQLi Ports
        8. Custom SparkX Sizes
        9. Notebook for Business Users
    10. Visualizations
    11. Other
  8. Integrations
  9. Data applications
  10. Releases

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
Note

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 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.

Note

To disable it on a Cloud cluster, contact Incorta Support.

To disable it on an On-Premises cluster,

  1. Edit the node.properties file or service.properties file for each Analytics node or service and set the inx.sparksql.sync.nonoptimizedtables property to false.
  2. Restart the Analytics services.
  3. Force the sync of the Spark Metastore.
Important
  • 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 the node.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.