@Rocky whats storage_engine in system variables? and whats difference if we set it hive vs olap

In StarRocks,

storage_engine

is a system variable that lists the supported table engines. While it exists as a session-level variable (often for MySQL compatibility), its most practical use is as the

ENGINE

property when creating a table. Here is the breakdown of what it is and the difference between

olap

and `hive`: 1. What is

storage_engine

? In the context of system variables,

storage_engine

(and

default_storage_engine

) exists primarily to satisfy MySQL-compatible clients and libraries (like Django) that expect a default engine to be reported. * Default:

olap

* Behavior: Changing the session variable

SET storage_engine = 'hive'

typically does not automatically convert internal table creation into Hive table creation because Hive tables require additional mandatory metadata (like

resource

or

location

). 2. Difference between

olap

and

hive

The choice between these two "engines" defines where and how your data is stored and managed: Feature |

olap

(StarRocks Native) |

hive

(External) Data Storage | Stored on StarRocks Backend (BE) nodes in an optimized columnar format. | Stored in external systems like HDFS or S3 in formats like Parquet, ORC, or Text. Performance | High. Optimized for sub-second queries, high concurrency, and real-time updates. | Moderate. Depends on network and external storage speed (though very fast via Data Lake Analytics). Data Ownership | StarRocks manages both metadata and the physical data files. | StarRocks only manages metadata; Hive or the FileSystem owns the data files. Supported Models | Supports Duplicate, Aggregate, Unique, and Primary Key models. | Used only for external table access (reading/writing to the lake). Usage Scenario | Internal data warehouse, real-time analytics, and high-performance dashboards. | Querying existing data lakes or federated queries across different systems. Summary Recommendation * Use

olap

for your core data where you need the fastest possible performance and full StarRocks feature support (like indexes and materialized views). * Use

hive

when you want to query data that already exists in a Hive data lake without moving it into StarRocks. Note: For Hive integration, modern StarRocks versions (v2.3+) recommend using Catalogs (

CREATE EXTERNAL CATALOG

) instead of setting the engine manually in

CREATE TABLE

. Catalogs allow you to map an entire Hive cluster automatically without creating individual tables. References • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • sql-reference/System_variable.md

@Rocky so that engine is for creating tables for instance in what type they should be created and what should manage it starrocks or external system right?

Yes, exactly. The

ENGINE

parameter (and the

storage_engine

variable) defines data ownership and which system is responsible for the storage. Here is how that management works in practice: 1.

ENGINE=olap

(StarRocks Native) * Who manages it? StarRocks. * Storage: The data is physically stored on the StarRocks Backend (BE) nodes in a proprietary, highly optimized columnar format. * Management: StarRocks handles everything: data replication, compaction (merging files), indexing, and consistency. * Use Case: You use this for your main production tables where you need the absolute best performance and features like Primary Key models, materialized views, and sub-second query speeds. 2.

ENGINE=hive

(External System) * Who manages it? An external system (HDFS/S3 and Hive Metastore). * Storage: The data stays where it is (e.g., in Parquet or ORC files on S3/HDFS). StarRocks does not "own" these files. * Management: StarRocks only stores the metadata (the schema and the location of the files). If you delete the table in StarRocks, the underlying data files in Hive/S3 usually remain untouched because StarRocks is just "pointing" to them. * Use Case: You use this when you already have a massive data lake and want to query it using StarRocks' compute power without the cost and time of moving all that data into StarRocks' own storage. Important Note on Modern Usage While you can still use

ENGINE=hive

in a

CREATE TABLE

statement, StarRocks now recommends using External Catalogs instead. * Old Way (

ENGINE=hive

): You have to manually run a

CREATE TABLE

statement for every single Hive table you want to query. * New Way (Catalogs): You run one

CREATE EXTERNAL CATALOG hive_catalog

command. StarRocks then automatically "sees" all databases and tables in your Hive cluster. You don't have to manage individual table engines anymore; StarRocks handles the mapping for you. Summary: * Set it to

olap

if you want StarRocks to host the data. * Set it to

hive

(or use a Catalog) if you want StarRocks to query data hosted elsewhere. References • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • sql-reference/System_variable.md References • sql-reference/System_variable.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • administration/management/FE_configuration.md