1. Cloud
  2. Guides
  3. Tours
  4. Concepts
  5. Tools
  6. References
    1. Built-in Functions
    2. Incorta Component SDK
    3. Incorta Connector SDK
    4. Connectors
    5. Public API
    6. Security
    7. Visualizations
    8. SQL Interface
      1. SQL Interface
      2. Configure Spark with SQLi
      3. Advanced SQL Interface
    9. Other
  7. Integrations
  8. Data applications
  9. Releases

References → Advanced SQL Interface (SQLi)

The current SQL interface presents challenges due to its incomplete adherence to SQL standards. Some queries encounter difficulties and fall back to Spark, which may result in syntax errors. Additionally, the existing SQL Interface incorporates any defined views within Incorta, even those not SQL-compliant, potentially leading to errors when non-compliant queries revert to Spark.

In response to these identified shortcomings, we are introducing the new Advanced SQL Interface that aligns with Spark SQL compliance. This pivotal feature offers an improved and more seamless experience.

Starting 2024.1.0, Incorta is introducing a new advanced SQLi that is fully Spark SQL compliant. With this compliance, the new SQLi introduces a new enhanced performance and compatibility with more external tools. You can also use the advanced SQLi with Incorta using the Kyuubi connector.

Important

Advanced SQL Interface is a preview feature.

Key Features of the Advanced SQL Interface:

  1. Compatibility with External Tools:
    • Seamless integration with popular tools such as Tableau, Power BI, and other SQL tools like DB Visualizer.
  2. Spark SQL Compliance:
    • Enables the composition of Spark SQL queries against physical tables or verified business views, ensuring adherence to Spark SQL standards.
  3. Utilization of Spark 3.4.1:
    • Incorporates Spark 3.4.1 for enhanced performance, leveraging the latest advancements in the Spark framework.
Note: The Advanced SQL Interface is an Incorta Labs feature

An Incorta Labs feature is experimental, and functionality may produce unexpected results. For this reason, an Incorta Lab feature is not ready for use in a production environment. Incorta Support will investigate issues with an Incorta Labs feature. In a future release, an Incorta Lab feature may be either promoted to a product feature ready for use in a production environment or be deprecated without notice.

How to activate Advanced SQL Interface?

To activate the Advanced SQL Interface, follow these steps in the Cloud Admin Portal (CAP):

  • Navigate to the Cluster Configurations.
  • Enable the "Enable Advanced SQL Interface" toggle.

Enabling this feature initiates the following actions:

  • The Null Handling feature is automatically enabled internally, irrespective of user-defined values.
  • The Loader and Analytics Services undergo a restart.
  • The Connection String becomes accessible for utilization.

Additional Considerations

  • The username format to use is: <username>%<tenant-name>
  • To authenticate Incorta, you must generate a personal access token (PAT) to use as a password while connecting to the advanced SQLi.
  • If you want to use OAuth 2.0 for authentication instead of PAT, you must enable OAuth 2.0-based authentication for JDBC connection from the CMC under Default Tenant Configurations > Integrations.
  • Advanced SQLi retrieves verified Views only. When you try querying a non-verified view, SQLi will result in a “not found” error.
  • Using the advanced SQLi requires your data to be synched with the Spark Metastore.
  • In case of using a tenant with the same name as a previously deleted one, please contact Incorta Support so you can avoid facing issues while using the Advanced SQL Interface.

Spark Metastore

The Spark Metastore, a MySQL database utilized by Incorta for maintaining metadata, exclusively supports the synchronization of optimized tables and verified business views. Users with the necessary authorization can utilize the Spark Metastore to access a comprehensive list of schemas and verified business schemas.

Synchronization with the Spark Metastore is not triggered by every event but specifically occurs under the following circumstances:

  • Creation of a new schema using the Schema Wizard
  • Schema Updates, including actions such as adding, editing, or deleting tables
  • Analytics service startup
  • On-Demand Sync initiated through the "Sync Spark Metastore" option available in the CMC in Clusters > cluster-name > Tenants > tenant-name > More Options.
Note

Synchronization is limited to schema names with a maximum length of 250 characters.

Advanced SQL Interface connectivity

  • Tableau – Connect Tableau to the Advanced SQL Interface using the new preview driver Incorta has developed specifically for that purpose.
  • DB Visualizer – Use Kyuubi Hive JDBC driver to connect to Incorta.
  • Incorta Over Incorta (IOI) – Use Kyuubi connector to create an Incorta over Incorta connection.
  • Power BI – Use Spark connector or Simba Spark ODBC to connect to Incorta.

Non-English characters support

Spark and Hive does not support non-English characters, yet there are a few settings that you can adjust in your third-party tool to overcome such a limitation.

For Power BI, check the Use Native Query option in the Advanced Options window. With this option enabled, there is a limitation with schema names over 128 characters.

For DBeaver and DB Visualizer, update all needed templates in the SQL Statements section to have extra backticks “`”. Knowing that, tables and columns with “$” in their names cannot be listed in the UI, but you can use them normally in queries.

For Tableau, non-English tables cannot be listed while using the Spark Simba connector, instead, use the Incorta Tableau connector.

Known limitations

  • Only a single analytics service is supported.
  • Geo data type is not supported.
  • Spark Metastore does not support synchronizing SQL views, non-optimized tables, and Analyzer views.
  • Only one schema is synchronized in case multiple exist with the same name, regardless of case sensitivity.