@Rocky which version include native_query() function?

The

native_query()

function is introduced in StarRocks version 4.1. It is a JDBC catalog table function that provides "SQL passthrough" capabilities. This allows you to execute raw SQL statements directly on an external data source (like MySQL, PostgreSQL, Oracle, etc.) through a JDBC catalog, bypassing StarRocks' standard query parsing and translation for that specific statement. Usage and Syntax The function must be used within the

TABLE()

keyword and prefixed with the name of your JDBC catalog. Syntax:

sql
SELECT * FROM TABLE(<jdbc_catalog_name>.native_query('<sql_statement>'));

Example If you have a JDBC catalog named

mysql_db

and want to run a specific MySQL-dialect query:

sql
SELECT * FROM TABLE(mysql_db.native_query('SELECT * FROM remote_table WHERE custom_column = 1'));

Key Details * Availability: Starting from v4.1.0. * Type: Table Function. * Scope: Currently supported for JDBC catalogs. * Benefit: Useful for executing queries that use source-specific syntax or features not directly supported by the StarRocks optimizer or SQL dialect. References • release_notes/release-4.0.md

@Rocky my cluster is verison 4.1.1 but when I use the function, it gives error like this

ROR 1064 (HY000): Getting analyzing error. Detail message: Unknown table function 'dealicious_catalog.native_query(VARCHAR)'.

The error

Unknown table function 'dealicious_catalog.native_query(VARCHAR)'

typically occurs when the StarRocks analyzer cannot resolve the

native_query

function for the specific catalog provided. Although you are on version 4.1.1, please check the following requirements and common pitfalls: 1. Verify Catalog Type The

native_query()

function is only supported for JDBC catalogs. It will not work for Hive, Iceberg, Hudi, or other catalog types. * Check: Run

SHOW CREATE CATALOG dealicious_catalog;

and ensure the

type

property is set to

jdbc

. 2. Correct Syntax Requirement The function must be wrapped in the

TABLE()

keyword and prefixed by the catalog name. * Correct Syntax:

sql
    SELECT _ FROM TABLE(dealicious_catalog.native_query('SELECT _ FROM remote_table LIMIT 10'));

* Common Mistake: Omitting the

TABLE()

wrapper or the catalog prefix will cause an "Unknown function" error. 3. Case Sensitivity StarRocks catalog names are case-sensitive in some contexts. Ensure that

dealicious_catalog

exactly matches the name shown in

SHOW CATALOGS;

. 4. Feature Maturity (Beta) Since this feature is introduced in the v4.1 series, it is considered a Beta feature. In some early 4.1.x builds, certain table functions might be restricted by session variables. While not explicitly required in the standard documentation, if you continue to see the error, try enabling the following session variable:

sql
SET enable_external_table_query = true; -- Note: Variable names may vary by patch

5. Check FE Logs If the syntax is correct and the catalog is definitely a JDBC catalog, check the FE (Frontend) leader log (

fe.log

). Look for "SemanticException" or "Analyzing error" around the time of your query. This may reveal if the catalog failed to initialize, which would prevent its table functions from being registered. If you have verified the catalog type and syntax and it still fails, it is recommended to refresh the catalog to ensure the metadata is correctly loaded:

sql
REFRESH EXTERNAL CATALOG dealicious_catalog;

References • release_notes/release-4.0.md • data_source/catalog/jdbc_catalog.md References • sql-reference/information_schema/session_variables.md • sql-reference/System_variable.md • sql-reference/user_defined_variables.md • sql-reference/information_schema/verbose_session_variables.md • using_starrocks/Flat_json.md

@Rocky I'm running 4.1.4 and also getting this error: SQL Error [1064] [42000]: Getting analyzing error. Detail message: Unknown table function 'catalog_prod_svc_dbt.native_query(VARCHAR)'. I tried SET enable_external_table_query = true; and got: SQL Error [1193] [HY000]: Getting analyzing error. Detail message: Unknown system variable 'enable_external_table_query', the most similar variables are {'enable_write_hive_external_table', 'enable_rbo_table_prune', 'enable_parquet_reader_bloom_filter'}.

The error

Unknown table function 'catalog_prod_svc_dbt.native_query(VARCHAR)'

occurs when the StarRocks analyzer cannot find the

native_query

function associated with the specified catalog. Even in version 4.1.4, this function has specific requirements: 1. Catalog Type Check (Crucial) The

native_query()

function is exclusive to JDBC catalogs. It is not supported for internal catalogs, Hive, Iceberg, or other data lake catalogs. * Check: Run

SHOW CREATE CATALOG catalog_prod_svc_dbt;

. * Requirement: The

type

property must be

"jdbc"

. If this is an Iceberg or Hive catalog (common in DBT setups), the function will not be available. 2. Correct Syntax The function must be wrapped in the

TABLE()

keyword. If your catalog name contains underscores or special characters, try using backticks to ensure the parser handles the name correctly:

sql
SELECT * FROM TABLE(`catalog_prod_svc_dbt`.native_query('SELECT * FROM remote_table LIMIT 10'));

3. Feature Enablement In some early builds of v4.1, experimental or beta features like SQL passthrough might be controlled by session variables. Since

enable_external_table_query

was not found in your version, try checking for these similar variables: *

enable_table_function

(ensure this is

true

) *

enable_jdbc_table_function

You can search for available variables using:

sql
SHOW VARIABLES LIKE '%table_function%';

4. Refresh the Catalog If you upgraded from an older version (e.g., v3.x) to v4.1, the catalog metadata might not have registered the new table function automatically. Try refreshing the catalog to trigger a reload of its capabilities:

sql
REFRESH EXTERNAL CATALOG catalog_prod_svc_dbt;

5. Why the variable error occurred? The variable

enable_external_table_query

was an internal flag in certain development branches. In the stable v4.1.x releases, StarRocks typically enables these catalog functions by default if the catalog type is JDBC. If the variable is "Unknown," it simply means your version does not use that specific flag to control the feature. Recommendation: If your catalog is indeed a JDBC catalog and the above steps fail, check the FE Leader logs (

fe.log

) for any

Analyzing error

or

Failed to load catalog

messages. If the catalog failed to initialize its JDBC driver, it might not register its extension functions. References • release_notes/release-4.0.md • data_source/catalog/jdbc_catalog.md References • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • sql-reference/System_variable.md • using_starrocks/SQL_plan_manager.md

@Rocky 1. catalog is jdbc 2. syntax is correct 3. there are no such variables 4. catalog is brand new 5. Get same error on fe.log a. LIMIT 0, 200, error: Getting analyzing error. Detail message: Unknown table function 'catalog_prod_svc_dbt.native_query(VARCHAR)'.

The

native_query()

function is indeed a feature introduced in the StarRocks v4.1 series. It is a JDBC catalog table function designed for SQL passthrough. The error

Unknown table function 'catalog_prod_svc_dbt.native_query(VARCHAR)'

in version 4.1.4 suggests that the StarRocks analyzer is not finding the function registration for that catalog. Since this feature is in the Preview/Beta stage for v4.1, please check the following specific configuration and requirements: 1. Enable JDBC Table Functions In some builds of v4.1, you must explicitly enable table function support for JDBC catalogs via a session variable. Since

enable_external_table_query

did not work for you, try the following variable which was specifically added for JDBC table functions:

sql
SET enable_jdbc_table_function = true;

_Note: If this variable is also "Unknown," check

SHOW VARIABLES LIKE '%jdbc%';

to see if there are related flags like

enable_jdbc_query_pushdown

._ 2. Catalog Name with Underscores The error message shows your catalog name is

catalog_prod_svc_dbt

. Because this name contains multiple underscores, the SQL parser might occasionally struggle to distinguish the catalog name from the function call. Always wrap the catalog name in backticks:

sql
SELECT * FROM TABLE(`catalog_prod_svc_dbt`.native_query('SELECT 1'));

3. Check FE Configuration (

fe.conf

) As this is a new feature in the 4.1 branch, it may be controlled by a static configuration in the Frontend. Check your

fe.conf

file for: *

enable_udf = true

(Must be true for any custom or extension functions to load). * Look for any parameter containing

jdbc

or

native

. 4. JDBC Driver Scope The

native_query

function relies on the FE being able to analyze the query schema by executing a "mock" or "dry run" of the passthrough SQL. * Ensure the JDBC driver is properly loaded on all FEs (not just the leader). * Verify that the

driver_url

in your catalog creation is accessible by all FE nodes. If the FE cannot load the driver, it cannot register the dynamic functions associated with that catalog. 5. Why it might be "Unknown" (Version Status) StarRocks v4.1 is currently in a Preview or Alpha/Beta phase (the current stable long-term branch is v3.3/v3.4). If you are using a development build or an early 4.1.x release, some features may be disabled by default or have temporary naming conventions. Recommendation: If

SET enable_jdbc_table_function = true;

does not resolve it, please check the FE leader logs (

fe.log

) during the execution. Look specifically for any "Failed to load catalog functions" or "Exception during catalog initialization" messages. Since this is a cutting-edge feature, it is also worth reaching out on the StarRocks Slack in the

#support

channel, as v4.1-specific bugs are often addressed in the latest daily builds. References • release_notes/release-4.0.md • data_source/catalog/jdbc_catalog.md • sql-reference/sql-functions/table-functions/files.md References • best_practices/query_tuning/query_profile_operator_metrics.md • sql-reference/sql-functions/JAVA_UDF.md • administration/management/FE_configuration.md • data_source/External_table.md • sql-reference/sql-functions/table-functions/files.md • integrations/dbt.md