@Rocky Can starrocks create other table formats instead of columnar olap ones? (apply the question to materialized views as well)

Yes, StarRocks can create and write to table formats other than its internal columnar OLAP format. This is primarily achieved through External Catalogs and Unified Catalogs. 1. Creating Other Table Formats StarRocks allows you to create and manage tables in data lake formats like Apache Iceberg and Apache Hive directly from StarRocks. * Supported Engines: You can use the

ENGINE

keyword in a

CREATE TABLE

statement to specify an external format. Currently, StarRocks supports creating Hive and Iceberg tables. * Unified Catalog: Starting from v3.2, you can use a Unified Catalog to manage these external tables. * Example (Creating an Iceberg table):

sql
    CREATE TABLE iceberg_catalog.db_name.taxis (
        trip_id BIGINT,
        vendor_id BIGINT,
        fare_amount DOUBLE
    )
    ENGINE = iceberg
    PARTITION BY (vendor_id);

* Writing Data: Once created, you can use

INSERT INTO

to write data from StarRocks into these external Iceberg or Hive tables. 2. Materialized Views (MVs) The behavior for Materialized Views is slightly different: * Source Data: You can create Asynchronous Materialized Views on top of various external formats, including Hive, Iceberg, Hudi, Delta Lake, Paimon, and JDBC sources. * Storage Format: Even when the base table is in an external format (like Iceberg), the data of the Materialized View itself is stored internally in StarRocks' native columnar OLAP format. * Why? The purpose of Materialized Views in StarRocks is Query Acceleration. By storing the pre-computed results in StarRocks’ optimized internal storage, it can provide sub-second query speeds that wouldn't be possible if the MV's results were stored back in the data lake. * Automatic Refresh: These MVs can be refreshed manually or at fixed intervals to keep the internal StarRocks "cache" in sync with the external lakehouse data. Summary Table Feature | Supported Formats | Storage Location Regular Tables | StarRocks (Native), Hive, Iceberg | Internal (StarRocks) or External (Lake) Materialized Views | Defined on Hive, Iceberg, Hudi, Delta, etc. | Always Internal (StarRocks) Logical Views | StarRocks, Iceberg (v3.5+) | Metadata only In short: You can create and write to external table formats like Iceberg and Hive for storage flexibility, but Materialized Views always use StarRocks' internal format to ensure high-performance query acceleration. References • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md

@Rocky I see the mysql engine type can I create a table in starrocks using shared data volume storage that is that format?

Hello! I'm Rocky. To answer your question: No, you cannot use the

mysql

engine type to store data on StarRocks' shared data volume. Here is a breakdown of why and how these two concepts differ in StarRocks: 1. The

mysql

Engine is for External Tables In StarRocks, when you use

ENGINE=mysql

in a

CREATE TABLE

statement, you are creating an External Table. * Purpose: It acts as a proxy or "window" to a table that already exists in a remote MySQL database. * Data Storage: StarRocks does not store any data for these tables. When you query a MySQL external table, StarRocks sends the query to the remote MySQL instance, retrieves the results, and displays them. * Format: The data remains in the MySQL database in whatever format MySQL uses (like InnoDB). 2. Shared Data Volume (Shared-Data Architecture) The "shared data volume" storage refers to the StarRocks Shared-Data Architecture (introduced in v3.0). * Purpose: To store StarRocks' own internal data in remote object storage (like AWS S3, MinIO) or HDFS. * Engine Type: For tables that you want to store in this shared volume, you use the default internal engine (typically

ENGINE=olap

). * Format: StarRocks uses its own highly optimized, columnar segment format. This format is specifically designed for high-performance analytics and is not compatible with the MySQL storage format. How to use Shared Data Volume Storage If you are running a StarRocks shared-data cluster and want to create a table that resides on your shared storage (S3/HDFS), you simply create a regular table:

sql
CREATE TABLE my_table (
    id INT,
    name VARCHAR(50),
    event_time DATETIME
) 
PRIMARY KEY (id) -- or DUPLICATE/AGGREGATE key
DISTRIBUTED BY HASH(id);

In a shared-data cluster, this table will automatically store its data files in your configured shared storage volume using StarRocks' native format, which provides much better performance for OLAP workloads than a MySQL engine could. Summary: * Use

ENGINE=mysql

only when you want to query data existing in an external MySQL database. * Use the default

CREATE TABLE

syntax (Internal/OLAP tables) to store data in the StarRocks shared data volume. Let me know if you need help setting up either one! References • data_source/External_table.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md