< Rocky> I want to copy the content of materialized view in StarRocks #questions-and-troubleshooting

<@U0A71G31CDV> I want to copy the content of mate...

Aleksandr Zakharchenko

07/15/2026, 3:37 PM

@Rocky I want to copy the content of materialized view in one starrocks cluster (let's say clusterA - shared_data mode, version 4.1.1) as a table in a separate starrocks cluster (clusterB shared_data mode version 3.5.16). Show me the sequence of actions to get it done

Rocky

07/15/2026, 3:38 PM

To copy the content of a materialized view (MV) from Cluster A (source) to a table in Cluster B (target), you have two primary methods. Given that both are in shared-data mode and have high versions (v3.5+ and v4.1+), the most efficient ways are using StarRocks External Tables or the Export/Load via Storage method. Method 1: StarRocks External Table (Recommended for simplicity) In this method, you create a "bridge" in Cluster A that points to Cluster B. You then push the data from Cluster A into Cluster B. Sequence of Actions: 1. In Cluster B (Target): Create the destination table with a schema matching the MV's output.

Copy code

sql
    CREATE TABLE target_table (
        column1 INT,
        column2 VARCHAR(255),
        ...
    )
    ENGINE=OLAP
    PRIMARY KEY (column1) -- or DUPLICATE/AGGREGATE
    DISTRIBUTED BY HASH(column1);

2. In Cluster A (Source): Create an External Table that points to the table you just created in Cluster B.

Copy code

sql
    CREATE EXTERNAL TABLE ext_target_table (
        column1 INT,
        column2 VARCHAR(255),
        ...
    )
    ENGINE=StarRocks -- Or ENGINE=OLAP in some versions
    PROPERTIES (
        "host" = "<ClusterB_FE_IP>",
        "port" = "9020",         -- RPC port of Cluster B's FE
        "user" = "<username>",
        "password" = "<password>",
        "database" = "<db_name_in_B>",
        "table" = "target_table"
    );

3. In Cluster A (Source): Write the data from the Materialized View into the External Table.

Copy code

sql
    INSERT INTO ext_target_table
    SELECT * FROM source_materialized_view;

────────── Method 2: Export/Load via
INSERT INTO FILES
(Recommended for large datasets) Since both clusters are in shared-data mode, they likely share access to the same object storage (S3/GCS/MinIO). You can export the MV results to a file and then load them into Cluster B. Sequence of Actions: 1. In Cluster A (Source): Export the MV data to your shared object storage using

INSERT INTO FILES

Copy code

sql
    INSERT INTO FILES (
        "path" = "s3://<bucket>/migration/mv_data/",
        "format" = "parquet",
        "aws.s3.access_key" = "...",
        "aws.s3.secret_key" = "...",
        "aws.s3.region" = "..."
    )
    SELECT * FROM source_materialized_view;

2. In Cluster B (Target): Create the destination table. 3. In Cluster B (Target): Load the data using the

FILES()

table function.

Copy code

sql
    INSERT INTO target_table
    SELECT * FROM FILES (
        "path" = "s3://<bucket>/migration/mv_data/*",
        "format" = "parquet",
        "aws.s3.access_key" = "...",
        "aws.s3.secret_key" = "...",
        "aws.s3.region" = "..."
    );

Key Considerations: * Version Compatibility: While Cluster A (v4.1.1) is newer than Cluster B (v3.5.16), the External Table protocol is stable. However, if using Method 1 fails due to protocol mismatches, Method 2 (via Parquet files) is the most decoupled and guaranteed way to succeed. * Permissions: Ensure the user account provided in the External Table properties has

INSERT

permissions on the target table in Cluster B. * Network: Ensure Cluster A's BE nodes can reach Cluster B's FE (port 9020) and BE (port 8060) nodes. * Shared-Data Limitation: Standard

BACKUP

and

RESTORE

commands are not supported for shared-data clusters; use the methods above instead.

Aleksandr Zakharchenko

07/15/2026, 3:42 PM

@Rocky In starrocks on a materialized view mv_test - how do I get a list of the fields together with their definitions (INT, VARCHAR, etc.)?

Rocky

07/15/2026, 3:42 PM

To get a list of the fields and their data types for a materialized view (MV) named

mv_test

, you can use several methods depending on the type of materialized view and the level of detail you need. 1. The Simplest Way:
DESC
For most materialized views (especially asynchronous ones), you can treat them like a table.

Copy code

sql
DESC mv_test;

This will return a table with the following columns:

Field

Type

Null

Key

Default

, and

Extra

. 2. For Synchronous (Single-Table) MVs If the MV is a synchronous rollup created on a specific base table, you view it by describing the base table:

Copy code

sql
DESC base_table_name ALL;

The output will list the schema for the base table followed by the schemas of all its associated synchronous materialized views. 3. Using Information Schema (For Scripting) If you need to query this information programmatically or filtered by specific criteria, use the

information_schema.columns

table:

Copy code

sql
SELECT 
    column_name, 
    data_type, 
    column_type, 
    is_nullable
FROM information_schema.columns
WHERE table_name = 'mv_test'
  AND table_schema = 'your_database_name';

Note: This method may not show columns for synchronous "rollup" style MVs. 4. See the Creation Statement If you want to see how the fields were derived from the original SQL:

Copy code

sql
SHOW CREATE MATERIALIZED VIEW mv_test;

This returns the full

CREATE

statement, which includes the query logic and any column aliases used. Summary of Commands: Goal | Command Quick view of types |

DESC mv_test;

Programmatic access | Query

information_schema.columns

View original SQL |

SHOW CREATE MATERIALIZED VIEW mv_test;

Check sync rollups |

DESC base_table ALL;

References • using_starrocks/Materialized_view-single_table.md • sql-reference/sql-statements/table_bucket_part_index/DESCRIBE.md • sql-reference/sql-statements/materialized_view/SHOW_MATERIALIZED_VIEW.md • using_starrocks/async_mv/Materialized_view.md • sql-reference/information_schema/materialized_views.md

Aleksandr Zakharchenko

07/15/2026, 3:48 PM

Rocky

07/15/2026, 3:49 PM

Since port 9020 (RPC) is blocked, the standard StarRocks External Table (which requires BE-to-BE and BE-to-FE communication over port 9020) will not work. Instead, you should use the Object Storage method (most efficient for shared-data clusters) or the MySQL External Table method (which uses port 9030). ────────── Option 1: Via Object Storage (Recommended) Since both clusters are in

shared_data

mode, they already have access to object storage (S3, Google Cloud Storage, or MinIO). This is the fastest way to move data between clusters. Step 1: Export from Cluster A to Storage In Cluster A (v4.1.1), use

INSERT INTO FILES

to export the MV content to a temporary location in your bucket.

Copy code

sql
INSERT INTO FILES (
    "path" = "s3://<your-bucket>/migration/mv_export/",
    "format" = "parquet",
    "aws.s3.access_key" = "...",
    "aws.s3.secret_key" = "...",
    "aws.s3.region" = "..."
)
SELECT * FROM mv_test;

Step 2: Create the Target Table in Cluster B In Cluster B (v3.5.16), create the destination table. Use the field definitions you gathered earlier (

DESC mv_test

Copy code

sql
CREATE TABLE target_table (
    col1 INT,
    col2 VARCHAR(255),
    ...
)
ENGINE=OLAP
DISTRIBUTED BY HASH(col1);

Step 3: Import into Cluster B In Cluster B, use the

FILES()

table function to pull the data directly from the storage path.

Copy code

sql
INSERT INTO target_table
SELECT * FROM FILES (
    "path" = "s3://<your-bucket>/migration/mv_export/*",
    "format" = "parquet",
    "aws.s3.access_key" = "...",
    "aws.s3.secret_key" = "...",
    "aws.s3.region" = "..."
);

────────── Option 2: Via MySQL External Table (No Storage Required) If you cannot use shared storage, you can use the MySQL protocol (Port 9030) to bridge the clusters. Cluster A will treat Cluster B as if it were a MySQL database. Step 1: Create the Target Table in Cluster B (Same as Step 2 in the previous option). Step 2: Create a MySQL External Table in Cluster A In Cluster A, create an external table that points to Cluster B using the MySQL port (9030).

Copy code

sql
CREATE EXTERNAL TABLE bridge_to_cluster_b (
    col1 INT,
    col2 VARCHAR(255),
    ...
)
ENGINE=MySQL
PROPERTIES (
    "host" = "<ClusterB_FE_IP>",
    "port" = "9030",
    "user" = "<ClusterB_User>",
    "password" = "<ClusterB_Password>",
    "database" = "<ClusterB_DB>",
    "table" = "target_table"
);

Step 3: Push Data from Cluster A to Cluster B Run this in Cluster A. It will select data from the MV and send it over the MySQL protocol to Cluster B.

Copy code

sql
INSERT INTO bridge_to_cluster_b 
SELECT * FROM mv_test;

────────── Which one should you choose? * Use Option 1 (Storage) if the dataset is large (GBs or TBs). It is parallelized, significantly faster, and more reliable for high volumes. * Use Option 2 (MySQL) only if the dataset is small (a few million rows) or if you do not have write access to an object storage bucket. The MySQL protocol is single-threaded for exports and much slower. Summary of Ports Used: * Option 1: Cluster A -> Storage (HTTPS/443), Cluster B -> Storage (HTTPS/443). No direct cluster-to-cluster communication. * Option 2: Cluster A (BEs) -> Cluster B (FE Port 9030). Uses the visible SQL port.

2 Views

Open in Slack

Previous Next