@Rocky I am using shared data architecture, metadata is fetched from glue. but i am getting the below error due to column name `key`:

│ starrocks-new-fe-0 com.starrocks.sql.parser.ParsingException: Getting syntax error at line 418, column 0. Detail message: Unexpected input 'key', the most similar input is {a legal identifier}.                                                 │

Hello! I'm Rocky, your StarRocks assistant. The error you're seeing occurs because

key

is a reserved keyword in StarRocks. When a column name (or any identifier) matches a reserved keyword, the SQL parser expects a specific syntax element (like a table key definition) and fails when it finds it used as a plain identifier. The Solution To resolve this, you must enclose the column name in backticks (``

key

``) in your SQL statements. 1. In Query Statements If you are querying a table from your Glue catalog, wrap the column name:

sql
SELECT `key`, other_column 
FROM your_glue_catalog.your_db.your_table 
WHERE `key` = 'value';

2. In DDL Statements If you are executing a script to create tables or views (which seems likely given the error mentions line 418), ensure the column definition is escaped:

sql
CREATE EXTERNAL TABLE example_table (
  `key` VARCHAR(64),
  value VARCHAR(64)
) 
ENGINE=hive
PROPERTIES (...);

Why this happens with Glue When StarRocks fetches metadata from AWS Glue, it maps the Glue column names to StarRocks column names. Even though Glue allows

key

as a column name, the StarRocks SQL parser (the FE) will still treat it as a reserved word when you write or execute SQL against that metadata. Other common reserved keywords to watch for: If you have other columns with names like these, you'll need backticks for them as well: *

rank

*

keys

*

order

*

partition

*

lead

*

lag

Note: Since your error mentions line 418, it's highly probable you are running a large SQL migration script or a batch of

CREATE

statements. You should search-and-replace

key

with ``

key

`` in that script where it refers to column names. Let me know if you need help with a specific SQL command or complex SQL statement! References • data_source/catalog/hive_catalog.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • loading/Kafka-connector-starrocks.md • data_source/datalake_faq.md

@Rocky The view itself is not parsable in starrocks:

SQL Error [1064] [42000]: failed to parse hive view text

This error occurs because of how StarRocks handles Hive views from external catalogs (like AWS Glue). Why this happens Since StarRocks v3.1, when you query a Hive view, StarRocks does not just fetch the data; it actually fetches the original SQL text of the view from the Glue/Hive metastore and attempts to parse it using the StarRocks/Trino SQL parser. If the view was originally created in Spark or Hive without backticks around the column name

key

, StarRocks' parser will fail because

key

is a reserved keyword in StarRocks. This results in the specific error:

failed to parse hive view text

. Solutions 1. Fix the view definition in Glue (Recommended) The most permanent fix is to modify the view definition in AWS Glue or your Hive environment. You must wrap the column

key

in backticks so that the StarRocks parser recognizes it as an identifier. * Original Glue SQL:

SELECT key, value FROM table

* Fixed Glue SQL: `SELECT

key

, value FROM table` 2. Create a StarRocks-native View If you cannot modify the Glue view, you can create a view within StarRocks that points to the underlying table in your Glue catalog. This bypasses the need for StarRocks to parse the Glue view's original text.

sql
CREATE VIEW starrocks_db.local_view AS 
SELECT `key`, value 
FROM your_glue_catalog.glue_db.base_table;

3. Try Trino Dialect StarRocks attempts to parse Hive views using StarRocks and Trino syntax. If the view is written in a style closer to Trino, you can try setting the session variable, though this rarely fixes reserved keyword issues if they aren't escaped in the source:

sql
SET sql_dialect = 'trino';
SELECT * FROM your_glue_catalog.glue_db.your_view;

Summary of Reserved Keywords Since your view likely uses

key

, it's possible it also uses other keywords that will cause similar parsing failures. Common ones include: *

key

,

keys

,

order

,

rank

,

partition

,

lead

,

lag

,

all

. If you are on StarRocks v3.5+, the engine has improved dialect support, but for Hive views, escaping the keyword in the source remains the most reliable solution. References: • Hive Catalog - Feature Support • StarRocks Reserved Keywords References • data_source/feature-support-data-lake-analytics.md • sql-reference/System_variable.md • sql-reference/sql-statements/TRANSLATE_TRINO.md • data_source/catalog/iceberg/DDL.md • data_source/catalog/hive_catalog.md

@Rocky what are the hiccups on changing the sql_dialect to trino? what all are the breaking changes?

Changing the

sql_dialect

to

trino

is a powerful way to support external Hive views, but it introduces several behavioral shifts and "hiccups" because it moves the StarRocks parser from a MySQL-compliant logic to a Trino/ANSI-compliant logic. Here are the primary hiccups and breaking changes you should expect: 1. The Case-Sensitivity Trap (Major Hiccup) In Trino dialect mode, StarRocks treats identifiers (database, table, and column names) as case-insensitive by default. * The Hiccup: If you created tables or databases with UPPERCASE names in your catalog, queries in Trino mode will often fail because the parser converts everything to lowercase before looking them up. * Best Practice: Ensure all your Glue/Hive metadata and StarRocks objects are lowercase, or you will encounter "Table not found" errors. 2. Double Quotes vs. Single Quotes (Breaking Change) This is the most common reason existing StarRocks queries break when the dialect is switched: * MySQL/StarRocks Default: Double quotes (

"value"

) can be used for string literals. * Trino Dialect: Double quotes (

"column_name"

) are reserved only for identifiers (like backticks). Single quotes (

'value'

) must be used for strings. * Result: If your current SQL uses

"2023-01-01"

as a date string, Trino mode will look for a column named

2023-01-01

and fail. 3. Strict Type Casting (Hiccup) Trino is generally stricter about implicit type conversions than StarRocks (which follows MySQL's "fuzzy" casting). * Example: A query like

WHERE string_col = 123

might work in the default dialect but fail in Trino mode. Trino often requires explicit casting:

WHERE string_col = CAST(123 AS VARCHAR)

. * Hiccup: You may find yourself needing to add many

CAST()

functions to queries that previously "just worked." 4. Function Mapping & Syntax While

sql_dialect = 'trino'

enables Trino-specific functions (like

json_extract

), there are subtle differences: * Array/Map Indexing: Trino uses 1-based indexing for arrays, whereas some engines/functions might behave differently. * JSON Processing: You shift from StarRocks functions like

get_json_string()

to Trino's

json_extract_scalar()

. * Logical Operators: Trino has specific behaviors for

NULL

handling in complex logical expressions that might slightly differ from MySQL's approach. 5. Reserved Keywords In Trino mode, the list of reserved keywords changes to match Trino's. * The Original Problem: While

key

is reserved in both, Trino mode requires you to escape it using double quotes (

"key"

) instead of backticks (``

key

``). * Breaking Change: If you have scripts using backticks, they might fail or need to be updated to double quotes if you commit fully to the Trino dialect. Recommendation: The "Bridge" Strategy If you only need to fix the Hive view parsing error, you don't necessarily have to change the global

sql_dialect

. Instead: 1. Use Session Scope: Only set

SET sql_dialect = 'trino';

for the specific session/user running the Glue queries. 2. Use `TRANSLATE TRINO`: If you are unsure why a Trino-style query is failing, you can run

TRANSLATE TRINO <your_sql>

in StarRocks (v3.3+) to see how StarRocks is interpreting/converting the statement internally. Summary Table Feature | Default Dialect (StarRocks/MySQL) | Trino Dialect Identifier Escape | Backticks (``

key

``) | Double Quotes (

"key"

) String Literals |

'string'

or

"string"

| Only

'string'

Case Sensitivity | Case-sensitive (mostly) | Case-insensitive (forced lowercase) Type Coercion | Permissive / Implicit | Strict / Explicit References: • System Variables - sql_dialect • Translate Trino SQL • Keywords and Reserved Words