http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> is it okay to ingest 100 GBs of dat...
# questions-and-troubleshooting
k
@Rocky is it okay to ingest 100 GBs of data per second from gcs to starrocks via stream load ? how is replay and checkpointing handled here and tranformation henadled here?
r
Ingesting 100 GB/s (GigaBytes per second) is an extremely high throughput requirement—equivalent to over 8.6 Petabytes per day. While StarRocks is designed for massive scale and object storage like GCS can provide significant throughput, achieving 100 GB/s via Stream Load requires a very large cluster and careful architecture. Typically, for data already in GCS, using StarRocks' Pipe or INSERT INTO SELECT FROM FILES() is more efficient than Stream Load because it allows StarRocks nodes to pull data directly and in parallel from GCS, rather than having an external client push it through HTTP. Here is how the specific mechanisms work in this context: 1. Feasibility of 100 GB/s * Scaling: To reach 100 GB/s, you would need a cluster with hundreds or potentially thousands of nodes, as each node's ingestion throughput is typically in the range of 100-200 MB/s depending on the hardware and data complexity. * Network/IO: Your VPC and GCS bucket limits must be considered. While GCS scales well, you may need to distribute data across multiple buckets or prefixes to avoid hitting GCS-specific limits. * Recommendation: If the data is already in GCS, Pipe (continuous loading) is the recommended method. It is more robust and scalable than pushing 100 GB/s via Stream Load from an external application. 2. Replay and Checkpointing In StarRocks, Stream Load is a synchronous, atomic operation. It does not "checkpoint" in the middle of a single HTTP request. * Labels (Idempotency): Each Stream Load job should be submitted with a unique 
label
. If a load fails (e.g., network timeout), you can retry with the same label. StarRocks uses this label to ensure exactly-once semantics; if a label has already successfully committed, StarRocks will not reload the data. * Transaction Interface (2PC): For high-concurrency or multi-table loads, StarRocks provides a Stream Load Transaction Interface (v2.4+). This supports a two-phase commit (Begin -> Prepare -> Commit) which allows external systems (like Flink) to manage "checkpointing" by only committing the transaction once their own state is saved. * Pipe Checkpointing: If you use Pipe for GCS ingestion, StarRocks manages the "checkpointing" internally by tracking which files have been successfully processed, ensuring that even if a node fails, it knows where to resume. 3. Data Transformation Transformations in Stream Load are handled during the ETL phase on the StarRocks nodes using the following parameters: * 
columns
Parameter: You can define column mapping and transformations. For example: 
columns: col1, col2, col3, col4 = col1 + col2, col5 = upper(col3)
* 
where
Parameter: You can filter rows during ingestion: 
where: col1 > 100
* Functions: Most StarRocks scalar functions are available during ingestion to parse dates, transform strings, or perform math. Summary Comparison for GCS Ingestion Feature | Stream Load (Push) | Pipe / INSERT FROM FILES (Pull) Source | Client-side (Local/Memory) | Cloud Storage (GCS/S3) Scalability | Limited by client/network bottleneck | High (Nodes pull in parallel) Checkpointing | Client-managed via Labels | StarRocks-managed (File-level) Ease of Use | Simple HTTP PUT | Best for bulk/continuous GCS data Rocky's Advice: If you truly need to hit 100 GB/s, I strongly recommend using Pipe with a large StarRocks cluster and ensuring your data is partitioned across many files in GCS to maximize parallel processing. If you meant 100 GB/hour or 100 MB/s, Stream Load is perfectly capable of handling that with a modest cluster! References • loading/gcs.md
k
@Rocky can INSERT INTO SELECT FROM FILES() be done with transformation in between?
r
Yes, you can absolutely perform transformations when using 
INSERT INTO ... SELECT FROM FILES()
. Since 
FILES()
acts as a table function that returns a result set, you can treat it like any other table in a 
SELECT
statement. This allows you to use SQL functions, expressions, column remapping, and filtering (
WHERE
clauses) to transform your data before it is inserted into the target table. How to perform transformations: 1. For CSV files: Use positional references like 
$1
, 
$2
, etc., to represent the columns in the file. 2. For Parquet/ORC files: Use the actual column names defined within the files (as 
FILES()
automatically infers the schema). Examples 1. Column Mapping and Simple Expressions (CSV) If you have a CSV with a raw string and a price, and you want to convert the string to uppercase and add tax to the price:
Copy code
sql
INSERT INTO target_table (name, price_with_tax)
SELECT 
    upper($1),        -- Transformation: convert to uppercase
    $2 * 1.15         -- Transformation: calculate tax
FROM FILES(
    "path" = "<s3://bucket/data.csv>",
    "format" = "csv",
    ...
);
2. Using Functions and Filtering (Parquet) If you are reading from a Parquet file and want to transform a timestamp string into a Date type and filter rows:
Copy code
sql
INSERT INTO target_table
SELECT 
    id, 
    str_to_date(event_time, '%Y-%m-%d %H:%i:%s'), -- Transformation: String to Date
    amount
FROM FILES(
    "path" = "<s3://bucket/data.parquet>",
    "format" = "parquet",
    ...
)
WHERE amount > 100; -- Transformation: Filtering
3. Handling Nulls or Defaults You can use 
coalesce
or 
case
statements to handle missing data during the load:
Copy code
sql
INSERT INTO target_table
SELECT 
    $1, 
    COALESCE($2, 'Unknown') -- Transformation: Handle NULLs
FROM FILES(
    "path" = "<s3://bucket/data.csv>",
    "format" = "csv",
    ...
);
Key Benefits * Schema Inference: 
FILES()
automatically detects column types and names for Parquet/ORC, making it easy to reference them in your 
SELECT
list. * Standard SQL: You have access to the full suite of StarRocks scalar functions (date, string, math, etc.) during the ingestion process. * No Intermediate Table: You don't need to create an external table first; you can perform the "Extract, Transform, Load" (ETL) in a single command. References • sql-reference/sql-functions/table-functions/files.md • loading/InsertInto.md
k
@Rocky how do i handle continous load while doing this?
r
Handling continuous load depends on what "this" (the operation you are performing) refers to. Here are the most common scenarios and how to manage your data ingestion (like Stream Load or Routine Load) during each: 1. During Schema Changes (
ALTER TABLE
) StarRocks performs schema changes online and asynchronously. * How it works: You can continue loading data while the schema change is in progress. StarRocks maintains multiple versions of the data (MVCC). * Best Practice: * Routine Load: It will continue to run. If you added a column, the existing Routine Load might need to be updated if the data format in Kafka changes. * Stream Load: You can keep sending data. If you are adding a column with a default value, the load will continue to work. If you are changing a data type, ensure your source data is compatible with the new type to avoid task failures. 2. During Cluster Migration or Backup/Restore If "this" refers to moving data between clusters: * Parallel Loading (Dual-Write): This is the recommended approach. 1. Start a Backup on the source cluster. 2. Continue loading data into the Source cluster. 3. Once the Restore is complete on the target cluster, start a parallel load job (e.g., a new Routine Load or Flink job) to the Target cluster. 4. Verify data consistency before stopping the load on the source cluster. * Note on Backups: A backup is a point-in-time snapshot. Any data loaded after the backup starts will not be in the snapshot. You will need to "replay" that data or use dual-writing. 3. Managing Routine Load Jobs If you need to temporarily stop ingestion to perform maintenance: * Pause and Resume: You don’t need to delete the job. Use these commands:
Copy code
sql
    PAUSE ROUTINE LOAD FOR <job_name>;
    -- Perform your maintenance --
    RESUME ROUTINE LOAD FOR <job_name>;
* Kafka Offsets: Routine Load tracks offsets. When you resume, it will pick up exactly where it left off, so no data is lost as long as the data is still within your Kafka retention period. 4. During Large Data Deletes or Updates If you are doing a massive 
DELETE
or `INSERT INTO ... SELECT`: * Batching: StarRocks handles concurrent loads well, but a massive background operation plus heavy continuous load can pressure the BE memory and disk I/O. * Monitoring: Keep an eye on the 
Compaction Score
and 
BE Memory
via the StarRocks FE/BE web UI. If the system is struggling, consider slowing down the frequency of your continuous load (increase the 
max_batch_interval
for Routine Load). Which specific operation are you performing? If you provide more details about "this," I can give you a more tailored recommendation! References • administration/management/Backup_and_restore.md • loading/RoutineLoad.md • knowledge/usage/best-practice.md • best_practices/query_tuning/schema_tuning.md • sql-reference/sql-statements/backup_restore/BACKUP.md
k
@Rocky does starrocks arrow expect standard 3 level list map encoding . how do i use insert +files() if my files are standard 2 encoing?
r
StarRocks (Rocky) is here to help! Regarding your question about Apache Arrow and Parquet encoding for nested types (Lists and Maps) in StarRocks: 1. Does StarRocks expect standard 3-level or 2-level encoding? StarRocks' native Parquet reader is designed to be highly compatible with various Parquet producers. It supports both the standard 3-level encoding (the modern Parquet standard) and the legacy 2-level encoding (often used by older versions of Spark or other legacy tools). * 3-level (Standard): 
List -> "list" -> "element"
* 2-level (Legacy): 
List -> "element"
StarRocks automatically detects the structure of the nested types by analyzing the Parquet schema and repetition/definition levels. You do not need to explicitly tell StarRocks which encoding is being used for reading/loading data. 2. How to use 
INSERT INTO ... SELECT FROM files()
with 2-level encoding? If your files use the legacy 2-level encoding, you can use the 
FILES()
table function just as you would with standard files. StarRocks will handle the mapping to its internal 
ARRAY
or 
MAP
types automatically. Example usage:
Copy code
sql
-- 1. Create a target table with the appropriate nested types
CREATE TABLE my_target_table (
    id INT,
    my_list ARRAY<INT>,
    my_map MAP<STRING, INT>
) ENGINE=OLAP
DUPLICATE KEY(id)
DISTRIBUTED BY HASH(id);

-- 2. Use INSERT INTO with FILES()
-- StarRocks will infer the schema from the Parquet files and match the 2-level structure to the table's ARRAY/MAP columns.
INSERT INTO my_target_table
SELECT * 
FROM FILES(
    "path" = "<s3://my-bucket/data/legacy_files/*.parquet>",
    "format" = "parquet",
    "aws.s3.access_key" = "...",
    "aws.s3.secret_key" = "..."
);
Key Considerations: * Version Compatibility: Support for loading nested types (MAP/STRUCT/ARRAY) via 
FILES()
and 
INSERT INTO
was significantly enhanced in StarRocks v3.1 and later. If you are on an older version, you might encounter limitations with nested type loading. * Schema Inference: In v3.1+, 
FILES()
supports schema inference for Parquet. If the inference has trouble with a specific legacy 2-level file, ensure that your target table is explicitly defined with the correct types, and StarRocks will perform the necessary casting during the 
INSERT
operation. * Unloading Data: If you ever need to export data from StarRocks to a legacy system that requires legacy encoding, you can use the property 
"parquet.use_legacy_encoding" = "true"
in your 
INSERT INTO FILES()
statement (available in v3.2+). For loading, this property is not required as the reader is already compatible. If you encounter a specific error message (e.g., "Field not found" or "Type mismatch"), it might be related to case sensitivity or column naming rather than the level of encoding. In that case, you can use the 
columns
parameter or check the session variable 
parquet_load_schema_by_name
. References • release_notes/release-3.1.md • sql-reference/data-types/semi_structured/Map.md • release_notes/release-3.4.md • release_notes/release-3.3.md
k
@Rocky when i am using encoding type 2 .. i am getting this error SQL Error [5609] [22001]: Data truncation: Apache Arrow type (nested) list does not match the type STRUCT in StarRocks: BE:10006
r
The error message 
Data truncation: Apache Arrow type (nested) list does not match the type STRUCT in StarRocks
indicates a schema mismatch between the data being transferred via the Apache Arrow format (which you've enabled or is triggered by "encoding type 2") and the target column type in your StarRocks table. Why this is happening: 1. Encoding Type 2: In the context of StarRocks connectors (like Spark or Flink) or internal protocols, "encoding type 2" typically refers to the Arrow-based columnar transfer format. This format is highly efficient but requires an exact match between the source data types and StarRocks data types. 2. Type Mismatch: * Arrow (nested) list: In Apache Arrow, a "List" represents a sequence of values, which StarRocks maps to the 
ARRAY
data type. * STRUCT in StarRocks: A 
STRUCT
is a collection of named fields with potentially different types. * The error occurs because the source data (e.g., from a Spark DataFrame or an external file) is providing a List/Array, but the StarRocks column is defined as a STRUCT. StarRocks cannot automatically cast an Arrow List into a StarRocks Struct when using this high-performance encoding. How to resolve: 1. Check your Table Schema Verify the definition of the table you are interacting with.
Copy code
sql
SHOW CREATE TABLE your_table_name;
If your column is defined as 
STRUCT<...>
, ensure the source data is actually a struct (with named fields) and not just a list of values. 2. Align Data Types * If the data is an Array: Change your StarRocks column type from 
STRUCT
to 
ARRAY
.
Copy code
sql
    -- Example change
    ALTER TABLE your_table_name MODIFY COLUMN your_column ARRAY<INT>;
* If the data should be a Struct: Ensure that your source (Spark/Flink/Python) is sending a 
StructType
and that the field names and order match the StarRocks 
STRUCT
definition exactly. 3. Disable Arrow Encoding (Workaround) If you cannot easily change the schema and want to rely on StarRocks' more flexible internal casting, you can disable the Arrow-based transfer (switching back to "encoding type 1" which usually uses CSV or JSON format). * In the Spark Connector: Set the 
starrocks.write.properties.format
to 
JSON
or 
CSV
(the default is CSV, but it avoids the strict Arrow type matching).
Copy code
scala
    // If using Spark
    .option("starrocks.write.properties.format", "json")
* In a Session (if applicable): If this is happening during a query or a specific transfer, check if there are session variables related to Arrow:
Copy code
sql
    -- Example (behavior varies by version)
    SET enable_arrow_flight_sql = false;
4. Specify Column Types (Connector Specific) If you are using the StarRocks Spark Connector, you might need to explicitly define the column mapping to help the connector infer the type correctly:
Copy code
scala
.option("starrocks.column.types", "your_col_name STRUCT<field1 INT, field2 STRING>")
Recommendation: Most users encounter this when using the Spark-StarRocks-Connector. Upgrading to the latest version of the connector (v1.1.1+) and ensuring the 
starrocks.column.types
matches your StarRocks schema is the most common fix. References • unloading/Spark_connector.md • loading/Spark-connector-starrocks.md
k
@Rocky even this is not working select * FROM *FILES*( "path" = "gs://gcs-one-dsp-prod-eus-commons-adrr/prod/2026/02/23/08/adserv_servingfills_00-00-243_0_2_133_ff1a4eb1-cf39-4568-aa63-fcffd646c683.parquet", "format" = "parquet" ) SQL Error [1064] [42000]: Apache Arrow type (nested) list does not match the type STRUCT in StarRocks: BE:10022
r
The error 
Apache Arrow type (nested) list does not match the type STRUCT in StarRocks
 usually occurs when StarRocks' automatic schema inference for the 
FILES()
function misidentifies a nested column in your Parquet file. Specifically, the StarRocks Frontend (FE) detects a column as a 
STRUCT
based on the Parquet group metadata, but the Backend (BE) reader (which uses Apache Arrow) sees it as a 
LIST
(which corresponds to an 
ARRAY
in StarRocks). This mismatch between the plan and the actual data causes the BE to fail. Here are the recommended steps to resolve or work around this: *1. Avoid `SELECT ` The most common cause is a specific nested column that isn't strictly necessary for your query but is failing during the automatic "detect-everything" phase. * Action: Try to specify the column names explicitly in your 
SELECT
statement instead of using 
*
. Skip any complex nested/struct columns if you don't need them. * Reason: If the problematic column isn't in the projection, StarRocks might bypass the type mismatch error. 2. Disable Struct Inference If you are on a version that supports it (v3.2+), you can try to disable the inference of 
STRUCT
types specifically, which might force StarRocks to treat the column as a different type or skip the strict check. * Action: Run this session variable before your query:
Copy code
sql
    SET enable_struct_type_infer = false;
3. Check StarRocks Version Automatic 
STRUCT
inference for the 
FILES()
function was significantly improved and officially supported starting from v3.4.0. * Action: If you are on an older version (e.g., v3.1 or v3.2), consider upgrading to v3.4.x. 4. Use an External Table/Catalog (Recommended for Complex Schemas) The 
FILES()
function is convenient but relies heavily on automatic inference which can fail on non-standard Parquet files (e.g., files with 3-level list structures from certain Spark/Flink versions). Creating an External Table or using a Unified Catalog allows you to explicitly define the schema, which overrides the inference logic. * Example (External Table):
Copy code
sql
    CREATE EXTERNAL TABLE temp_gcs_table (
        col1 INT,
        col2 ARRAY<STRING>, -- Manually define as ARRAY if StarRocks was guessing STRUCT
        col3 STRUCT<id:INT, name:STRING>
    )
    ENGINE=file
    PROPERTIES (
        "path" = "<gs://your-bucket/path/to/file.parquet>",
        "format" = "parquet"
    );

    SELECT * FROM temp_gcs_table;
5. Adjust Session Variables for Parquet Reading Sometimes the optimized Parquet reader hits edge cases with nested types. Disabling these optimizations can sometimes help the reader fall back to a more compatible path:
Copy code
sql
SET enable_shared_scan = false;
SET parquet_late_materialization_enable = false;
Summary Tip: If 
SELECT _
fails, your best bet is to use 
DESC SELECT _ FROM FILES(...)
(if it works) to see what StarRocks thinks the schema is, and then explicitly select only the columns you need. If the 
DESC
also fails, you should use the External Table approach to manually define the schema. References • loading/load_concept/strict_mode.md • release_notes/release-3.3.md
k
@Rocky is enable_struct_type_infer a valid config?
r
k
@Rocky how do i add maven reposrity jars in the starrocks deployment ? what is the best recommended way?
r
How you add Maven repository JARs to a StarRocks deployment depends on whether you are adding custom logic via Java UDFs or connecting to external data via JDBC Catalogs. StarRocks does not have a "Maven repository" folder that it monitors; instead, it provides mechanisms to load JARs via HTTP URLs or local file paths. 1. User-Defined Functions (Java UDFs) If you want to add custom calculation logic, the recommended way is to develop a Java UDF and register the JAR file. * Recommended Approach: 1. Package your Maven project into a "fat JAR" (a JAR containing all its dependencies). You can use the 
maven-assembly-plugin
or 
maven-shade-plugin
for this. 2. Host the JAR on an HTTP server accessible to all FE (Front-End) and BE (Back-End) nodes. 3. Register the function using the 
CREATE FUNCTION
statement, pointing the 
file
property to the JAR's URL. Example:
Copy code
sql
CREATE FUNCTION my_custom_logic(string) 
RETURNS string
PROPERTIES (
    "symbol" = "com.your company.udf.MyUDF", 
    "type" = "StarrocksJar",
    -- You can point directly to Maven Central or your private Artifactory/Nexus URL
    "file" = "<http://your-http-server/my-udf-1.0-jar-with-dependencies.jar>"
);
2. JDBC Drivers (External Catalogs) If you are adding a JAR to connect to an external database (like MySQL, PostgreSQL, or Oracle), you use the JDBC Catalog. * Recommended Approach: Provide the 
driver_url
in the 
PROPERTIES
when creating the catalog. StarRocks supports both HTTP(S) and local file paths. Example:
Copy code
sql
CREATE EXTERNAL CATALOG my_mysql_catalog
PROPERTIES (
    "type" = "jdbc",
    "user" = "root",
    "password" = "password",
    "jdbc_uri" = "jdbc:<mysql://127.0.0.1:3306>",
    -- Pointing directly to a Maven Central URL is recommended for standard drivers
    "driver_url" = "<https://repo1.maven.org/maven2/mysql/mysql-connector-java/8.0.28/mysql-connector-java-8.0.28.jar>",
    "driver_class" = "com.mysql.cj.jdbc.Driver"
);
Note: The FE will download the JAR once, and BEs will download it when they first execute a query using that catalog. 3. Manual Classpath Addition (Advanced) If you have a JAR that must be part of the core FE or BE startup classpath (rarely needed for standard users), you can manually place the JAR files in the 
lib/
directory of your StarRocks installation. * FE Path: 
${STARROCKS_HOME}/fe/lib/
* BE Path: 
${STARROCKS_HOME}/be/lib/
* Best Practice: This requires a restart of the service to take effect. It is generally better to use the UDF or Catalog methods above to avoid manual file management across a distributed cluster. Summary Checklist for Best Results: 1. Use Fat JARs: Always ensure dependencies are shaded into the JAR if using UDFs to avoid 
ClassNotFoundException
. 2. Network Accessibility: Ensure every node (FE and BE) can reach the 
driver_url
or UDF 
file
URL. 3. JAVA_HOME: For BEs to run Java code, ensure 
JAVA_HOME
is set to a valid JDK (not just JRE) in your 
be/bin/start_be.sh
or environment variables. References • sql-reference/sql-functions/JAVA_UDF.md • loading/SparkLoad.md • deployment/upgrade.md • loading/Flink-connector-starrocks.md • deployment/deploy_manually.md
k
@Rocky can starrocks nothing deployment tables be exposed as tables in polaris iceberg catalogue?
r
Hello! I'm Rocky. To answer your question: StarRocks internal tables (used in Shared-nothing or Shared-data deployments) cannot be directly "exposed" or shared as Iceberg tables in a Polaris Iceberg catalog. Here is the breakdown of how StarRocks integrates with Polaris and why "nothing deployment" tables (Shared-nothing internal tables) are treated differently: 1. Internal vs. External Tables * Internal Tables (Shared-nothing): These are stored in StarRocks' proprietary format (segment files) optimized for ultra-fast OLAP performance. This format is not compatible with the Iceberg specification. Therefore, Polaris (or any Iceberg catalog) cannot see or manage these internal tables directly. * External Tables (Iceberg): StarRocks can manage tables that reside in an Iceberg catalog (like Polaris). These tables are stored in Parquet/ORC format with Iceberg metadata, which Polaris can track. 2. How to "Expose" StarRocks Data to Polaris If you have data in a StarRocks internal table and you want it to be accessible via a Polaris Iceberg catalog, you need to sink that data into an Iceberg table: 1. Create an Iceberg Catalog in StarRocks: Use the Iceberg REST catalog type to connect to your Polaris instance.
Copy code
sql
    CREATE EXTERNAL CATALOG polaris_catalog
    PROPERTIES (
        "type" = "iceberg",
        "iceberg.catalog.type" = "rest",
        "iceberg.catalog.uri" = "http://<polaris-host>:8181/api/catalog",
        "iceberg.catalog.warehouse" = "your_warehouse",
        "iceberg.catalog.security" = "oauth2",
        "iceberg.catalog.oauth2.credential" = "your_credential",
        "iceberg.catalog.oauth2.scope" = "PRINCIPAL_ROLE:ALL"
    );
2. Create an Iceberg Table: Create the table within that Polaris catalog from StarRocks.
Copy code
sql
    CREATE TABLE polaris_catalog.your_db.exported_table (
        id INT,
        name STRING
    ) ENGINE=iceberg;
3. Insert Data: Sync your internal (Shared-nothing) table data into the Iceberg table.
Copy code
sql
    INSERT INTO polaris_catalog.your_db.exported_table
    SELECT * FROM default_catalog.local_db.internal_table;
3. Querying via Polaris Once the data is inserted into the 
polaris_catalog
table, StarRocks writes the data in Iceberg-compliant format (Parquet) and updates the metadata in Polaris. At this point, any other engine connected to Polaris can see and query that table. Summary * Can they be exposed directly? No, because internal tables use a proprietary storage format. * Can you bridge them? Yes, by using StarRocks to write the data into an Iceberg table managed by Polaris. If you are looking for a way to automatically sync metadata without moving data, that is currently not supported for StarRocks' internal storage engine. You must perform an 
INSERT
or set up an Asynchronous Materialized View targeting the Iceberg catalog to keep the data in sync. References • best_practices/primarykey_table.md • administration/management/BE_configuration.md • administration/management/FE_configuration.md
k
@Rocky can i connect to another starrocks from a starrocks as exterbal data source using arrow flight sql endpoint ? if yes then how
r
k
@Rocky how do i connect to another starrocks from a starrocks as exterbal data source
r
To connect one StarRocks cluster to another as an external data source, you have two primary methods depending on your version and use case: JDBC Catalog (recommended for queries in v3.0+) and StarRocks External Tables (recommended for data synchronization or writing). 1. JDBC Catalog (Recommended for Queries, v3.0+) Starting from v3.0, you can use a JDBC Catalog to connect to another StarRocks cluster. Since StarRocks is MySQL-compatible, you can use the MySQL JDBC driver. This allows you to browse all databases and tables in the remote cluster without manual schema definitions. Steps: 1. Ensure the MySQL JDBC driver (e.g., 
mysql-connector-java
) is available on all FE and BE nodes (you can provide a download URL in the statement). 2. Create the catalog:
Copy code
sql
CREATE EXTERNAL CATALOG remote_sr_catalog
PROPERTIES (
    "type" = "jdbc",
    "user" = "your_username",
    "password" = "your_password",
    "jdbc_uri" = "jdbc:mysql://<remote_fe_ip>:<fe_query_port>", -- Default port is 9030
    "driver_url" = "<https://repo1.maven.org/maven2/mysql/mysql-connector-java/8.0.28/mysql-connector-java-8.0.28.jar>",
    "driver_class" = "com.mysql.cj.jdbc.Driver"
);
3. Query the data:
Copy code
sql
SELECT * FROM remote_sr_catalog.db_name.table_name LIMIT 10;
────────── 2. StarRocks External Table (Recommended for Ingestion/Writing, v1.19+) If you are on an older version or specifically want to write data from one cluster to another (e.g., for read-write separation), you can use a StarRocks external table. This requires you to manually define the table schema on the source cluster. Steps: 1. In the source cluster, create an external table pointing to the destination cluster:
Copy code
sql
CREATE EXTERNAL TABLE external_t
(
    k1 DATE,
    k2 INT,
    v1 VARCHAR(2048)
)
ENGINE=olap
DISTRIBUTED BY HASH(k1)
PROPERTIES
(
    "host" = "<remote_fe_ip>",
    "port" = "9020",         -- Remote BE Thrift port (default 9020)
    "user" = "your_username",
    "password" = "your_password",
    "database" = "remote_db_name",
    "table" = "remote_table_name"
);
2. You can now read or write data:
Copy code
sql
-- Sync data to the remote cluster
INSERT INTO external_t SELECT * FROM local_table;

-- Query data from the remote cluster
SELECT * FROM external_t WHERE k2 > 100;
Summary of Key Differences Feature | JDBC Catalog | External Table StarRocks Version | v3.0 or later | v1.19 or later Ease of Use | High (automatic metadata sync) | Low (manual schema definition) Primary Port | 9030 (MySQL/FE) | 9020 (Thrift/BE) Best For | Federated queries and browsing | High-performance data sync/writing If you're looking for the most flexible way to query across clusters, I recommend the JDBC Catalog. Let me know if you need help with the specific network or driver configurations! References • data_source/catalog/maxcompute_catalog.md • data_source/catalog/kudu_catalog.md • data_source/catalog/hive_catalog.md
k
@Rocky how do i address the below problem The StatefulSet uses Parallel pod management, meaning all 3 FE pods can start simultaneously. But StarRocks' bootstrap logic requires sequential startup: • FE-0 must start first and become LEADER • FE-1 and FE-2 must join an existing LEADER Problem: When FE-0 crashes with existing metadata marking it as FOLLOWER, the entrypoint script has no recovery logic - it just times out and crashes. 2. Split-Brain Prevention with No Recovery The entrypoint logic (fe_entrypoint.sh): • FE-0 (pod index 0): Waits 30 seconds for a leader, then assumes it's the first node • FE-1+ (other pods): Wait 120 seconds for a leader, then EXIT (no fallback) Critical Gap: If FE-0 has corrupted/old metadata saying "I'm a FOLLOWER", it will: 1. Find no leader after 30s 2. Try to start as first node BUT with FOLLOWER metadata 3. BDB replication refuses (UUID mismatch, expects LEADER) 4. Crash and repeat 3. No Persistent Identity Management StarRocks uses BDB group membership which includes: • Node UUID • Node name (FQDN + edit_log_port + timestamp) • Role (LEADER/FOLLOWER)
r
It sounds like you are hitting the limitations of a manual or custom-scripted FE deployment on Kubernetes. The behavior you're describing—where FE-0 fails to recover because its metadata contradicts its bootstrap role—is a known challenge when managing StarRocks FEs outside of a dedicated operator. Here are the recommended ways to address these issues: 1. Use the StarRocks Operator (Recommended) The most robust solution is to migrate to the StarRocks Operator. * Why: The Operator manages the complex "leader-follower" handshake. It doesn't rely on a simple timeout in a shell script. Instead, it monitors the cluster state via the StarRocks API and Kubernetes API to determine which node should be the helper and which should join. * Recovery: It handles pod restarts and metadata recovery more gracefully than a static 
fe_entrypoint.sh
script. 2. Change 
podManagementPolicy
 If you must use a manual StatefulSet, change 
podManagementPolicy
from 
Parallel
to 
OrderedReady
. * Effect: This forces K8s to wait for FE-0 to be "Ready" (passing its readiness probe) before starting FE-1. * Benefit: This ensures that the leader (FE-0) is actually up and running to accept 
ALTER SYSTEM ADD FOLLOWER
requests or to serve as the helper node for the subsequent pods. 3. Improve the Entrypoint Script Logic Your current script logic assumes FE-0 is always the "creator" and others are "joiners." This fails in recovery scenarios. A production-grade script should: 1. Check for existing metadata: If the 
meta/
directory is not empty, the node should try to start normally without the 
--helper
flag. BDB JE will automatically attempt to find the rest of the group based on its previous knowledge. 2. Dynamic Leader Discovery: Instead of hardcoding a 30s/120s wait, the script should use 
mysql
client commands or 
curl
against the FE's HTTP port of other pods to see if a leader exists. 3. Avoid UUID Mismatches: StarRocks generates a unique ID in 
meta/image/VERSION
. If you are manually clearing metadata on some pods but not others, or if the FQDN changes without the cluster knowing, BDB will refuse to sync. 4. Handling the "Follower vs. Leader" Metadata Conflict If FE-0 crashes and its metadata says it's a FOLLOWER, but it's the first one up, it will try to find the old LEADER. If that leader is gone (e.g., in a total cluster restart), the cluster hangs. * Recovery Mode: If the cluster cannot elect a leader because the majority of nodes are gone or metadata is out of sync, you can temporarily add 
metadata_failure_recovery=true
to 
fe.conf
on the node with the most recent data to force it to become the leader. Note: Remove this flag immediately after the cluster recovers. 5. Persistent Identity Management Ensure your StatefulSet uses a Headless Service. * The nodes must be added to the cluster using their FQDNs (e.g., 
starrocks-fe-0.starrocks-fe-service.default.svc.cluster.local
), not IP addresses. * BDB records these names. If a pod restarts and its FQDN remains the same, BDB will recognize it. If you use IPs and the IP changes, BDB will treat it as a new, unknown node, leading to the "Split-Brain" or "Connection Refused" issues you mentioned. Summary Checklist for your `fe_entrypoint.sh`: 1. Does 
meta/image/VERSION
exist? * Yes: Start FE normally (
start_fe.sh
). BDB handles the rest. * No: This is a fresh node. 2. Is it the first time for the whole cluster? _ Only use 
--helper
for FE-1 and FE-2 to point to FE-0 _during the very first initialization*. 3. Self-Registration: Have the entrypoint script (or a sidecar) run a loop that tries to 
ALTER SYSTEM ADD FOLLOWER "node_fqdn:edit_log_port"
by connecting to the current leader. Pro-tip: If you are stuck in a crash loop right now, check the 
meta/sys_log/fe.log
. If you see "elected leader is not the local node," but no leader exists, you may need to manually clean the metadata or use the recovery flag mentioned in point 4.
k
@Rocky The StatefulSet uses Parallel pod management, meaning all 3 FE pods can start simultaneously. But StarRocks' bootstrap logic requires sequential startup: • FE-0 must start first and become LEADER • FE-1 and FE-2 must join an existing LEADER Problem: When FE-0 crashes with existing metadata marking it as FOLLOWER, the entrypoint script has no recovery logic - it just times out and crashes. 2. Split-Brain Prevention with No Recovery The entrypoint logic (fe_entrypoint.sh): • FE-0 (pod index 0): Waits 30 seconds for a leader, then assumes it's the first node • FE-1+ (other pods): Wait 120 seconds for a leader, then EXIT (no fallback) Critical Gap: If FE-0 has corrupted/old metadata saying "I'm a FOLLOWER", it will: 1. Find no leader after 30s 2. Try to start as first node BUT with FOLLOWER metadata 3. BDB replication refuses (UUID mismatch, expects LEADER) 4. Crash and repeat 3. No Persistent Identity Management StarRocks uses BDB group membership which includes: • Node UUID • Node name (FQDN + edit_log_port + timestamp) • Role (LEADER/FOLLOWER) i am using helm to install in kubernetes
r
Hi there! I'm Rocky. I see you're running into some significant hurdles with the StarRocks FE bootstrap process on Kubernetes using Helm. You've hit on some of the most complex aspects of FE lifecycle management, especially around BDB JE (Berkeley DB) group membership and leader election in a dynamic environment like K8s. The issues you described—sequential startup requirements vs. Parallel pod management, FOLLOWER metadata conflicts on FE-0, and timeout exits—are known challenges when using a "standard" StatefulSet/Helm approach without an orchestrator that understands StarRocks' internal state. Here are some recommendations to address these gaps: 1. Switch to the StarRocks Kubernetes Operator The most effective way to resolve these issues is to use the StarRocks Kubernetes Operator. * Why: The Operator is designed to handle exactly what you're describing. It manages the bootstrap logic, identifies which node should be the "helper" for new nodes to join, and handles pod restarts and identity management much more robustly than the standard 
fe_entrypoint.sh
script. * Identity: It helps maintain stable network identities and manages the FE roles more effectively during upgrades and restarts. 2. Change Pod Management Policy If you prefer to stick with a pure Helm/StatefulSet approach, you should change the 
podManagementPolicy
from 
Parallel
to 
OrderedReady
. * Effect: This ensures FE-0 must be healthy and ready before FE-1 starts. While this doesn't fix the metadata corruption issue, it eliminates the race condition during initial bootstrap where FE-1/2 might try to join a leader that hasn't finished initializing yet. 3. Handling the "Follower" Metadata on FE-0 If FE-0 crashes and won't restart because its local BDB metadata says it's a FOLLOWER, but no other LEADER exists (the "split-brain" or "no-leader" trap), you have a few options: * Metadata Failure Recovery: You can temporarily set 
metadata_failure_recovery=true
in 
fe.conf
for FE-0. This forces the node to ignore its previous role and start as a LEADER. _ _Warning:* Use this only as a last resort and ensure no other LEADER is actually running, as this can lead to data inconsistency. * Automated Cleanup: Some users modify the entrypoint script to detect this state and automatically clear the local metadata 
image
directory if and only if the cluster is being bootstrapped from scratch or the node is irrecoverably out of sync. However, this is risky for production. 4. Improve the Entrypoint Logic If you are maintaining your own 
fe_entrypoint.sh
, consider these improvements: * Dynamic Leader Discovery: Instead of hardcoded 30/120s waits, use a loop that checks the status of other FE pods via the Kubernetes API or a headless service DNS check to see if anyone is responding as a LEADER. * Helper Node Logic: Ensure that when a node starts, it attempts to join using all other FE FQDNs as potential helpers, not just FE-0. 5. Persistent Identity and BDB JE The UUID/timestamp issue you mentioned (Node name including a timestamp) usually happens when the FE thinks it's a new node joining the cluster. * Ensure your Helm chart uses stable FQDNs (e.g., 
starrocks-fe-0.starrocks-fe-search.svc.cluster.local
). * In the FE metadata directory, StarRocks stores the 
node_name
and 
role
. If a pod restarts with the same FQDN but the BDB JE environment doesn't match the existing group records, it will fail. The Operator handles the 
ALTER SYSTEM DROP FOLLOWER
/ 
ADD FOLLOWER
commands automatically to clean up these stale identities. Summary Recommendation: Transitioning to the StarRocks Operator is the best path forward. It moves the "intelligence" of the cluster state out of a shell script and into a controller that can properly orchestrate FE recovery and membership changes. If you'd like to dive into specific 
fe.conf
settings or Operator configurations, let me know!
k
@Rocky why creating create external tables from files how can i create a table with multiple files
r
Hi there! I'm Rocky. To create an external table from multiple files in StarRocks, you have a few powerful options depending on your needs. 1. Using the 
files()
Table Value Function (Recommended) The most flexible way to work with multiple files (on S3, HDFS, GCS, or Azure Storage) is using the 
files()
table value function. You can use wildcards (like 
*
) in the path to include multiple files. Example: Querying multiple Parquet files on S3
Copy code
sql
SELECT * FROM files(
    "path" = "<s3://my-bucket/data/2023/*.parquet>",
    "format" = "parquet",
    "aws.s3.access_key" = "your_key",
    "aws.s3.secret_key" = "your_secret",
    "aws.s3.region" = "us-east-1"
);
You can then create a permanent table from this by using 
CREATE TABLE AS SELECT (CTAS)
or simply use this logic to load data into an existing table. 2. Creating an External Table (HDFS/S3) When you create a standard External Table, you specify the 
location
. If you point the location to a directory, StarRocks will automatically include all files within that directory. Example: HDFS External Table
Copy code
sql
CREATE EXTERNAL TABLE my_multiple_files_table (
    id INT,
    name STRING
) 
ENGINE=HDFS
PROPERTIES (
    "path" = "<hdfs://namenode:9000/user/data/my_folder/>",
    "format" = "parquet"
);
_Note: In this case, StarRocks reads all files inside 
my_folder/
._ 3. Using Wildcards in Path Most external resource integrations in StarRocks support the 
*
wildcard in the path property. This allows you to: _ Match all files in a folder: 
<s3://bucket/folder/_>
_ Match specific patterns: 
<s3://bucket/folder/2023-_.csv>
Summary of Tips: * Directory Level: If you provide a path to a directory, StarRocks reads all files in that directory that match the format. * Wildcards: Use 
*
to filter specific sets of files (e.g., all files ending in 
.parquet
). * Schema Consistency: Ensure that all files in the specified path share the same schema (column names and types), or StarRocks may encounter errors during the scan. Which storage system are you using (S3, HDFS, etc.)? I can provide a more specific syntax example if you let me know!
k
@Rocky i have 5 files of different names i want to create one external table how can i do that?
r
To create a single external table in StarRocks that covers multiple files, you can use wildcards (like 
*
) in the file path. As long as the files have the same schema and format (e.g., all are Parquet or all are CSV), StarRocks will treat them as a single dataset. Depending on your version and preference, here are the two most common ways to do this: 1. Using a File External Table (Recommended) You can define an external table that points to a specific directory or a pattern of filenames.
Copy code
sql
CREATE EXTERNAL TABLE my_combined_table (
    id INT,
    name STRING,
    event_date DATE
) 
ENGINE=file
PROPERTIES (
    "path" = "<s3://my-bucket/my-folder/*.parquet>", -- Use * to match all files
    "format" = "parquet",
    "aws.s3.access_key" = "your_access_key",
    "aws.s3.secret_key" = "your_secret_key",
    "aws.s3.region" = "your_region"
);
2. Using the 
FILES()
Table Function If you want to query them quickly without predefined schemas, or use them to create a view/table, you can use the 
FILES()
function.
Copy code
sql
SELECT * FROM FILES(
    "path" = "<s3://my-bucket/my-folder/*.csv>",
    "format" = "csv",
    "use_mmap" = "true",
    "aws.s3.access_key" = "your_access_key",
    "aws.s3.secret_key" = "your_secret_key",
    "aws.s3.region" = "your_region"
);
Key Tips: * Wildcard Matching: You can use 
_
to match everything in a folder (e.g., 
folder/_
) or specific prefixes (e.g., 
folder/data_2023_*.parquet
). * Directory Path: If you provide a directory path (e.g., `s3://bucket/folder/`), StarRocks will automatically read all files inside that directory. * Recursive Traversal: By default, StarRocks looks at the files in the specified path. Ensure all 5 files are in the same location or match the pattern you provide. * Consistency: All files must have the same column structure and file format for the external table to work correctly. If your files are stored in HDFS, Azure Storage, or Google Cloud Storage, the syntax is almost identical—you just change the 
path
prefix and the corresponding credential properties.
k
@Rocky but there is no naming commonality between the paths
r
If there is no naming commonality between your file paths (meaning you can't use wildcards like 
*
), you aren't stuck! You can handle this in StarRocks by explicitly listing the files or using multiple data descriptions. Here are the best ways to handle this situation: 1. Broker Load (Recommended for Bulk) In a Broker Load, you can specify multiple 
DATA INFILE
clauses within a single load job. This allows you to point to completely different paths, even across different directories or buckets (as long as the credentials have access).
Copy code
sql
LOAD LABEL my_label_123
(
    DATA INFILE("<s3://my-bucket/folder-a/data_01.csv>"),
    DATA INFILE("<s3://my-bucket/completely/different/path/info_final.csv>"),
    DATA INFILE("<s3://other-bucket/logs/2023/report.csv>")
    INTO TABLE my_target_table
    COLUMNS TERMINATED BY ","
)
WITH BROKER (
    "aws.s3.access_key" = "...",
    "aws.s3.secret_key" = "..."
);
2. INSERT INTO SELECT with 
FILES()
 If you are using StarRocks 3.0+, you can use the 
FILES()
table function. Since there's no pattern commonality, you can perform multiple inserts or use a 
UNION ALL
if you want to do it in one transaction:
Copy code
sql
INSERT INTO my_target_table
SELECT * FROM FILES(
    "path" = "<s3://bucket/path-one/file1.parquet>",
    "format" = "parquet"
)
UNION ALL
SELECT * FROM FILES(
    "path" = "<s3://bucket/different-path/file2.parquet>",
    "format" = "parquet"
);
3. Scripting the Load If you have hundreds of unrelated files, the easiest approach is to use a small script (Python or Bash) to: 1. Generate the list of paths. 2. Programmatically construct the 
LOAD
statement or run multiple 
INSERT INTO ... SELECT FROM FILES()
statements in a loop. Which one should you use? * Use Broker Load if you have a large number of files and want the load to be handled asynchronously in the background. * Use 
FILES()
 if you want to perform a synchronous query (great for smaller datasets or interactive data exploration). Does one of these methods fit your current workflow, or are you looking for a way to automate the discovery of these paths?
k
@Rocky what is the best way to do starrocks shared nothing as source for starrocks data deployment with the shared data supporting struct columnn type query ?
r
It sounds like you are looking to move or sync data from a StarRocks Shared-nothing (Standard) cluster to a StarRocks Shared-data (Cloud-native) cluster, while ensuring that complex types like 
STRUCT
are fully supported for querying. The best way to achieve this depends on your data volume and whether you need a one-time migration or a continuous sync. Here are the recommended approaches: 1. The Direct Way: StarRocks External Table (Best for small to medium datasets) You can create an External Table in your Shared-data cluster that points directly to the Shared-nothing cluster. * How it works: The Shared-data cluster acts as the "consumer." You define a table using the 
starrocks
table engine. * Struct Support: StarRocks supports 
STRUCT
types in its internal engine and cross-cluster queries via external tables in recent versions (v3.1+). * Workflow: 1. In the Shared-data cluster, create an external table:
Copy code
sql
       CREATE EXTERNAL TABLE source_table_standard (
           id INT,
           user_info STRUCT<name:VARCHAR, age:INT> -- Ensure types match
       )
       ENGINE=STARROCKS
       PROPERTIES (
           "host" = "source_fe_ip",
           "port" = "9030",
           "user" = "user",
           "password" = "password",
           "database" = "source_db",
           "table" = "source_table"
       );
2. Sync data:
Copy code
sql
       INSERT INTO target_shared_data_table
       SELECT * FROM source_table_standard;
2. The Scalable Way: Export to Object Storage (Best for large-scale migration) For very large datasets, using a direct SQL link can put pressure on the FE/BE of the source cluster. The most robust method for StarRocks deployments is to use Object Storage (S3/OSS/GCS) as an intermediary. * How it works: Export data from Shared-nothing to S3 in Parquet format (which has native support for 
STRUCT
), then load it into the Shared-data cluster. * Workflow: 1. Export from Shared-nothing:
Copy code
sql
       EXPORT TABLE source_table
       TO "<s3://my-bucket/export/data/>"
       PROPERTIES (
           "format" = "parquet"
       );
2. Load into Shared-data: Use 
INSERT INTO ... SELECT FROM FILES()
or a 
Broker Load
.
Copy code
sql
       INSERT INTO target_shared_data_table
       SELECT * FROM TABLE(
           files(
               "path" = "<s3://my-bucket/export/data/*>",
               "format" = "parquet",
               "aws.s3.access_key" = "...",
               "aws.s3.secret_key" = "..."
           )
       );
* Why this is "Best": Parquet preserves the 
STRUCT
schema perfectly, and the 
FILES()
table function in StarRocks 3.x is highly optimized for Shared-data clusters. 3. Backup and Restore (Version Compatibility Dependent) If both clusters are on compatible versions (e.g., both 3.x), you can use the Backup/Restore feature using a Shared Broker (S3/HDFS). * This is the fastest way to move an entire database. _ _Note:* Ensure the Shared-data cluster is initialized and the storage volume is correctly configured before restoring. Key Considerations for 
STRUCT
Queries: * Version: Ensure you are on StarRocks v3.1 or higher. Comprehensive support for 
STRUCT
in all parts of the pipeline (including 
FILES()
and 
INSERT INTO SELECT
) was significantly matured in the 3.x series. * Schema Evolution: If you change the 
STRUCT
definition in the source, you will need to manually update the target table or the external table definition. * Querying: Once the data is in your Shared-data cluster, you can query fields using the dot notation: 
SELECT user_info.name FROM target_table;
. Recommendation: If you are doing a fresh deployment, use the Export/Load via S3 (Parquet) method. It is the most resilient way to handle complex types like 
STRUCT
while leveraging the elastic compute of the Shared-data architecture.
k
@Rocky isnt it
Copy code
CREATE EXTERNAL TABLE source_table_standard (
           id INT,
           user_info STRUCT<name:VARCHAR, age:INT> -- Ensure types match
       )
       ENGINE=STARROCKS
       PROPERTIES (
           "host" = "source_fe_ip",
           "port" = "9030",
           "user" = "user",
           "password" = "password",
           "database" = "source_db",
           "table" = "source_table"
       );
deprecate din starrocks 4
r
Hi there! I'm Rocky. You are correct that the syntax using 
CREATE EXTERNAL TABLE ... ENGINE=STARROCKS
is considered a legacy method for accessing data from another StarRocks cluster. While StarRocks version 4.0 has not been officially released yet (the current major version is 3.x), the transition away from "External Tables" toward "External Catalogs" has been the standard since StarRocks 2.5 and 3.0. Why it's being phased out in favor of External Catalogs: 1. Automatic Metadata Sync: With the legacy 
ENGINE=STARROCKS
approach, you have to manually define the schema for every table. If the source table changes, your external table breaks or becomes out of sync. With an External Catalog, StarRocks automatically syncs all databases and tables from the target cluster. 2. Ease of Use: You don't need to create a table for every single object. You just create one catalog. 3. Performance: External Catalogs are better integrated with the cost-based optimizer (CBO). The Recommended Modern Syntax (StarRocks Catalog): Instead of creating an external table, you should create a StarRocks External Catalog:
Copy code
sql
CREATE EXTERNAL CATALOG remote_sr_cluster
PROPERTIES (
    "type" = "starrocks",
    "starrocks.host" = "source_fe_ip",
    "starrocks.port" = "9030",
    "starrocks.user" = "user",
    "starrocks.password" = "password"
);
Then you can query the data directly using the three-tier identifier:
Copy code
sql
SELECT * FROM remote_sr_cluster.source_db.source_table;
Summary: While the 
ENGINE=STARROCKS
syntax might still function in current 3.x versions for backward compatibility, it is no longer the recommended way to connect StarRocks clusters. If you are designing a new system or upgrading, moving to External Catalogs is the way to go!
k
Copy code
sql
CREATE EXTERNAL CATALOG remote_sr_cluster
PROPERTIES (
    "type" = "starrocks",
    "starrocks.host" = "source_fe_ip",
    "starrocks.port" = "9030",
    "starrocks.user" = "user",
    "starrocks.password" = "password"
);
@Rocky will it connect via jdbc?
r
No, it will not connect via JDBC. When you use 
"type" = "starrocks"
, you are creating a StarRocks External Catalog. This is a native integration designed specifically for StarRocks-to-StarRocks communication. Here is how it works: 1. Metadata Access: It uses the MySQL protocol (connecting to the FE at the specified 
starrocks.host
and 
starrocks.port
, typically 
9030
) to synchronize metadata like database and table schemas. 2. Data Scanning: When queries are executed, the local cluster communicates directly with the remote cluster's BEs using StarRocks' native internal communication protocols (not JDBC) to fetch data efficiently. If you specifically wanted to use JDBC (for example, if you needed to use a specific JDBC driver version or if you were connecting to a different database type), you would need to set 
"type" = "jdbc"
. However, for StarRocks-to-StarRocks clusters, the native 
"type" = "starrocks"
catalog is recommended as it provides much better performance and supports more features than a generic JDBC connection.
k
@Rocky
Copy code
sql
CREATE EXTERNAL CATALOG remote_sr_cluster
PROPERTIES (
    "type" = "starrocks",
    "starrocks.host" = "source_fe_ip",
    "starrocks.port" = "9030",
    "starrocks.user" = "user",
    "starrocks.password" = "password"
);
is type = starrocks even a thing ?
r
Yes, 
"type" = "starrocks"
is a valid catalog type in StarRocks! It is used to create a StarRocks External Catalog, which allows one StarRocks cluster to query data residing in another remote StarRocks cluster. This feature was introduced in StarRocks version 3.1. Your syntax is almost correct. Here is the standard structure for creating it:
Copy code
sql
CREATE EXTERNAL CATALOG remote_sr_cluster
PROPERTIES (
    "type" = "starrocks",
    "starrocks.host" = "source_fe_ip",
    "starrocks.port" = "9030", -- The FE MySQL query port of the target cluster
    "starrocks.user" = "user",
    "starrocks.password" = "password"
);
Key Details: * 
starrocks.host
: The IP address or FQDN of the leader FE in the remote cluster. * 
starrocks.port
: The MySQL query port of the remote cluster (default is 
9030
). * Usage: Once created, you can access the remote data using the three-tier identifier: 
remote_sr_cluster.db_name.table_name
. * Permissions: The user specified in the properties must have at least 
SELECT
permissions on the remote tables you intend to query. This is a very efficient way to perform cross-cluster data analysis or migration without having to manually export and import data.
k
@Rocky i am facing error with type = starrocks not a valid type
r
Hi there! I'm Rocky. It looks like you're encountering an error because the system doesn't recognize 
starrocks
as a valid type in your command. This error usually happens in one of two situations: 1. You are creating an External Catalog (Version Requirement) The ability to create a StarRocks External Catalog using 
SET "type" = "starrocks"
was introduced in StarRocks version 3.1. * If you are on a version earlier than 3.1: This type is not supported for catalogs. To query data from another StarRocks cluster in older versions, you should use the External Table approach with the 
StarRocks
engine instead:
Copy code
sql
    CREATE EXTERNAL TABLE remote_table (
        id INT,
        name STRING
    ) ENGINE=StarRocks
    PROPERTIES (
        "host" = "remote_fe_host",
        "port" = "9030",
        "user" = "user",
        "password" = "password",
        "database" = "remote_db",
        "table" = "remote_table"
    );
* If you are on 3.1 or later: Ensure your syntax follows this pattern:
Copy code
sql
    CREATE EXTERNAL CATALOG remote_sr
    PROPERTIES (
        "type" = "starrocks",
        "starrocks.http_port" = "8030",
        "starrocks.rpc_port" = "9020",
        "starrocks.thrift_port" = "9030",
        "starrocks.user" = "root",
        "starrocks.password" = "password",
        "starrocks.fe_nodes" = "127.0.0.1"
    );
2. Typo or Incorrect Command Context Check if you are using 
type
= 
starrocks
in a command where it isn't expected, such as 
CREATE RESOURCE
. Generally, "Resource" types are reserved for things like 
Spark
, 
HDFS
, or `Iceberg`/`Hive` in specific configurations. To help you further, could you share: 1. Which version of StarRocks are you using? (
SELECT apache_starrocks_version();
) 2. The full SQL command you were trying to run? I'm here to help you get this sorted!
k
@Rocky what is the best way to do StarRocks-to-StarRocks cross-cluster federation with array and struct type columns?
r
For StarRocks-to-StarRocks cross-cluster federation, the best and most modern approach is to use the StarRocks External Catalog. This method is superior to the older "External Table" approach as it is easier to manage and provides better support for StarRocks-specific features, including complex data types. 1. Why use StarRocks External Catalog? The StarRocks External Catalog (available in StarRocks 3.0 and later) allows you to map an entire remote StarRocks cluster. It uses StarRocks' internal communication protocol (rather than just the MySQL protocol), which is essential for handling complex types like 
ARRAY
and 
STRUCT
correctly. 2. Supporting ARRAY and STRUCT * ARRAY: StarRocks-to-StarRocks federation supports the 
ARRAY
type. When you query a remote table via a StarRocks Catalog, the 
ARRAY
data is transmitted in its native format, maintaining performance and type integrity. * STRUCT: Support for 
STRUCT
types in cross-cluster federation was further improved in versions 3.1 and 3.2+. It is recommended that both the source (remote) and destination (local) clusters are on version 3.1.x or higher to ensure full compatibility for nested 
STRUCT
and 
MAP
types. 3. Implementation Steps To set up the federation, run the following command on your local cluster:
Copy code
sql
CREATE EXTERNAL CATALOG remote_sr_cluster
PROPERTIES (
    "type" = "starrocks",
    "starrocks.http_urls" = "10.0.x.x:8030,10.0.x.y:8030", -- FE HTTP ports
    "starrocks.user" = "your_user",
    "starrocks.password" = "your_password"
);
Once created, you can query your tables (including those with 
ARRAY
and 
STRUCT
columns) directly:
Copy code
sql
SELECT array_column, struct_column 
FROM remote_sr_cluster.remote_db.remote_table 
LIMIT 10;
Key Considerations: * Version Matching: For the best experience with complex types, ensure your clusters are on the same or similar minor versions (3.1+ is highly recommended). * Network: Ensure that the local FE and BE nodes can communicate with the remote FE nodes (via HTTP port 
8030
) and the remote BE nodes (via the Heartbeat/Internal ports), as the catalog might perform predicate pushdown and direct data fetching. * Predicate Pushdown: The StarRocks catalog automatically pushes down filters and projections to the remote cluster, which is more efficient for large datasets containing complex types. If you are using a version older than 3.0, you would be forced to use the 
StarRocks External Table
syntax, but this is deprecated and much more limited in its handling of complex types compared to the Catalog method.
k
@Rocky i am ingesting 1000+ columns pk table and when i am putting upserts with partial update true column mode. The read queries are facing ERROR 1064 (HY000): query timeout. backend id: 10050 And the wrtes are also becoming slow. The compaction score is consistently high what can the resolution?
r
Dealing with 1,000+ columns in a Primary Key (PK) table using Partial Update in Column Mode can lead to significant performance bottlenecks if not configured correctly. The high compaction score, slow writes, and query timeouts are classic symptoms of "Write Amplification" and "Compaction Lag." In StarRocks, Column Mode is optimized for updating a small number of columns across a large number of rows. With 1,000+ columns, if you are updating more than a handful of columns or ingesting in small, frequent batches, you are likely overwhelming the background compaction process. Here are the resolutions to address this: 1. Evaluate Row Mode vs. Column Mode * Column Mode (your current setting) is efficient only when the number of updated columns is very small (e.g., < 10 columns out of 1,000). For each update, StarRocks creates separate delta files for the specific columns. With 1,000 columns, this creates massive metadata and file management overhead. * Row Mode is generally better for wide tables when you are updating many columns or performing frequent real-time updates in small batches. It reads the existing row, merges the update, and writes a new row. * Recommendation: If you are updating more than ~1-5% of your 1,000 columns, try switching to 
row
mode. * For Flink CDC/Stream Load: Set 
partial_update_mode = row
. * Check your `be.conf`: ensure 
unstable_row_mode_compaction_threshold
is not working against you. 2. Increase Ingestion Batch Size The high compaction score indicates that you are creating too many versions (Rowsets) faster than the system can merge them. * Increase the batch size: Instead of many small writes, buffer more data (e.g., 100MB+ per batch or increase the interval to 30-60 seconds). This reduces the number of Rowsets generated per minute, directly lowering the compaction score. 3. Tune Compaction Resources To help the BE catch up with the high number of versions, increase the threads dedicated to PK table compaction in `be.conf`:
Copy code
properties
# Increase compaction threads (Default is often 1 per disk)
update_compaction_num_threads_per_disk = 2 

# For Shared-data (StarRocks 3.x+), adjust this instead:
# compact_threads = 8
Note: Increasing these will use more CPU and Disk I/O, so monitor the system load. 4. Enable Persistent Index For a 1,000-column PK table, the Primary Key index can consume significant memory. If the memory is pressured, compaction and queries will slow down. * Ensure 
enable_persistent_index
is set to 
true
in the table properties. This stores the PK index on disk (preferably SSD), freeing up memory for query execution. 5. Check Tablet Distribution (Bucketing) If your compaction score is high on a specific backend (10050), you might have data skew. * Run 
SHOW TABLET FROM <table_name>
and check if some tablets are much larger or have significantly higher 
MaxCS
(Compaction Score) than others. * Ensure your 
DISTRIBUTED BY HASH(pk)
uses a column with high cardinality to spread the load evenly across all BEs. 6. Address Query Timeout (ERROR 1064) The timeout is a result of the query having to scan through too many uncompacted versions. * Temporary fix: Increase the session variable 
SET query_timeout = 300;
. * Permanent fix: Lowering the compaction score (via the steps above) is the only way to restore read performance. Summary of Action Plan: 1. Verify if 
row
mode is more appropriate for your update pattern. 2. Slow down the ingestion frequency (larger batches). 3. Increase 
update_compaction_num_threads_per_disk
in 
be.conf
and restart the BE. References • loading/Load_to_Primary_Key_tables.md • table_design/table_types/primary_key_table.md
k
@Rocky one stream updates 90% of the columns rest of the streams updates rest 10%. what should be the mode ?
r
For your scenario where different streams update different subsets of columns in a Primary Key table, you should leverage the Partial Update feature. Since StarRocks v3.1, there are two distinct modes you can choose from based on the update pattern of each stream: *1. Stream updating 90% of columns: Use Row Mode (Default)* Row mode is the traditional partial update method in StarRocks. It uses a "read-on-write" mechanism where it reads the existing row, merges it with the new data, and writes the complete row back. * Why: When you are updating 90% of the columns, the overhead of reading the remaining 10% is very small. Row mode is highly efficient for "thick" updates (many columns) and small real-time batches. * Configuration: This is the default. You just need to set 
partial_update: true
. *2. Stream updating 10% of columns: Use Column Mode* Column mode is designed specifically for scenarios where you update a small number of columns across a large number of rows. * Why: In this mode, StarRocks only writes the 10% of columns being updated and does not need to read the 90% of existing data. This significantly reduces I/O and improves performance when the update involves only a small fraction of the table's total width. * Configuration: Set 
partial_update: true
and 
partial_update_mode: column
. How to apply this in practice: You can specify the mode independently for each load job (Stream Load, Broker Load, Flink Connector, etc.). For the 10% Stream (Example using Stream Load):
Copy code
bash
curl --location-trusted -u <user>:<pass> \
    -H "partial_update: true" \
    -H "partial_update_mode: column" \
    -H "columns: id, col_a, col_b" \
    -T data.csv \
    http://<fe_host>:<fe_http_port>/api/db/table/_stream_load
For the 90% Stream (Example using Stream Load):
Copy code
bash
curl --location-trusted -u <user>:<pass> \
    -H "partial_update: true" \
    -H "partial_update_mode: row" \
    -H "columns: id, col_c, col_d, ...[all other columns]" \
    -T data.csv \
    http://<fe_host>:<fe_http_port>/api/db/table/_stream_load
Summary of Recommendations: * StarRocks Version: Ensure you are on v3.1 or later to use Column Mode. * Threshold: A general rule of thumb is to use Column Mode if you are updating less than 25-30% of the columns, especially if the total number of columns in the table is large. * Memory: Column Mode can consume more memory during the "apply" phase for very large batches, so monitor your BE memory usage if you have extremely high concurrency. References • sql-reference/sql-statements/table_bucket_part_index/UPDATE.md • loading/Load_to_Primary_Key_tables.md • loading/Spark-connector-starrocks.md • sql-reference/System_variable.md • sql-reference/sql-statements/loading_unloading/BROKER_LOAD.md • administration/management/BE_configuration.md • administration/management/monitoring/metrics.md • loading/Kafka-connector-starrocks.md
k
what configurations need to be tweaked to have high ingestion ?
@Rocky what configurations need to be tweaked to have high ingestion ?
r
To achieve high ingestion performance in StarRocks, you need to tune configurations across the FE (Frontend) and BE (Backend/Compute Node), specifically focusing on parallelism, memory management, and compaction. The following configurations are categorized by their impact on ingestion throughput: 1. Backend (BE) Parallelism & Write Buffering These settings in 
be.conf
control how many threads are dedicated to writing data and how much memory is used to buffer data before flushing to disk. * 
number_tablet_writer_threads
: (Default: 16 or 0.5 * CPU cores) * Controls the number of threads used for writing to tablets. For high-concurrency ingestion, increase this (e.g., to 32 or 48) if you have spare CPU. * 
flush_thread_num_per_store
: (Default: 2) * Number of threads per disk for flushing data. For high-throughput NVMe/SSD, increasing this (e.g., to 4 or 8) can speed up the "flush to disk" phase. * 
write_buffer_size
: (Default: 100MB) * Size of the memory buffer before flushing. Larger buffers (e.g., 200MB–500MB) reduce the number of small files generated but consume more BE memory. * 
streaming_load_rpc_max_alive_time_sec
: (Default: 1200) * Timeout for the writer process. Increase this if you are loading very large batches that take a long time to process. 2. Memory Management for Loads If you encounter "Memory Limit Exceeded" errors during high-volume loads, tweak these in `be.conf`: * 
load_process_max_memory_limit_percent
: (Default: 30) * The percentage of BE memory dedicated to all loading processes. For ingestion-heavy clusters, you might increase this to 50% or 60%. * 
load_process_max_memory_limit_bytes
: * The hard limit in bytes. Ensure this is consistent with your total RAM and the percentage above. 3. Compaction Tuning (Crucial for High Frequency) High ingestion often leads to "Too many versions" or "High compaction score" errors. You must ensure compaction can keep up with the incoming data. For Shared-Nothing (Local Storage): * 
update_compaction_num_threads_per_disk
: (be.conf) Increase to allow more parallel compaction threads per disk. * 
tablet_max_versions
: (be.conf, Default: 1000) Increase this if you have very frequent small writes and can tolerate slightly higher query latency. For Shared-Data (StarRocks Lake / Storage-Compute Separation): * 
lake_enable_batch_publish_version
: (fe.conf) Set to 
true
(available in v3.2.3+) to batch transaction commits and significantly improve real-time ingestion throughput. * 
compact_threads
: (be.conf) Increase the number of threads dedicated to compaction in the lake architecture. * 
lake_ingest_slowdown_threshold
: (fe.conf, Default: 100) Increase this to delay the "backpressure" mechanism that slows down ingestion when compaction scores are high. 4. Load-Specific Optimizations Depending on your loading method, adjust these parameters in `fe.conf`: * Routine Load (Kafka): * `max_routine_load_batch_size`: Increase the data size per task. * `routine_load_task_consume_second`: Increase the consumption time (e.g., to 15s or 20s) to create larger batches. * Stream Load / Broker Load: * `stream_load_default_timeout_second`: Increase if large files are timing out. * `max_stream_load_timeout_second`: Increase the global ceiling for load timeouts. 5. Best Practices for "High Ingestion" * Batching is King: Instead of thousands of small Stream Loads per second, aim for fewer, larger loads (e.g., once every 1–5 seconds, or 100MB+ per batch). * Primary Key Tables: If using Primary Key tables, ensure you have enough memory for the 
Primary Index
, as disk-based indexes are slower for ingestion. * Monitor Scores: Use 
SHOW PROC '/transactions/{db_name}/running';
to see if ingestion is being delayed by high compaction scores.
Note: Most BE configurations require a restart to take effect, while many FE configurations (marked as 
Is mutable: Yes
in docs) can be changed at runtime using 
ADMIN SET FRONTEND CONFIG ("name" = "value");
. References • administration/management/BE_configuration.md
k
@Rocky does starrocks shared data deployment support updates in iceberg tables ?
r
StarRocks shared-data deployment (cloud-native architecture) does support DML operations on Iceberg tables, but with some specific limitations regarding the 
UPDATE
statement itself. Here is the breakdown of support for Iceberg table updates and DML in StarRocks: *1. Is the 
UPDATE
statement supported?* Currently, the standard SQL 
UPDATE
statement is not supported for Iceberg tables in StarRocks (neither in shared-nothing nor shared-data deployment). The 
UPDATE
statement in StarRocks is specifically designed for internal Primary Key tables. *2. Supported DML for Iceberg (Shared-Data & Shared-Nothing)* Starting from version 3.1, StarRocks supports several DML operations for Iceberg via the Iceberg Catalog: * 
INSERT INTO
: Appends data to an existing Iceberg table. * 
INSERT OVERWRITE
: Overwrites existing data in the table or specific partitions. This is the recommended way to perform updates in Iceberg tables currently—by overwriting the data that needs to be changed. * 
TRUNCATE
: Quickly removes all data from an Iceberg table. * 
DELETE
: Support for the 
DELETE
statement on Iceberg tables is currently in the roadmap (planned for future versions like v4.x). *3. Deployment Mode Considerations* For external catalogs like Iceberg, there is no functional difference between shared-nothing and shared-data deployments regarding write capabilities. Both architectures use the Compute Nodes (CN) or Backend nodes (BE) to write data files directly to the underlying remote storage (S3, HDFS, etc.) and update the Iceberg metadata (via HMS, Glue, or REST). Summary of Workaround If you need to update records in an Iceberg table using StarRocks: 1. Use 
INSERT OVERWRITE
to replace entire partitions or the whole table with the updated data. 2. Alternatively, you can ingest the data into a StarRocks native Primary Key table (which fully supports 
UPDATE
and 
DELETE
in shared-data mode) and then periodically export it back to Iceberg if needed. Recommended Versions: For the best experience with Iceberg DML (Insert/Overwrite), it is recommended to use StarRocks v3.1 or higher. For shared-data clusters specifically, ensure you are on a stable release like v3.2.x or v3.3.x to leverage the latest data cache and metadata performance optimizations. References • data_source/catalog/iceberg/iceberg_catalog.md • release_notes/release-4.0.md • release_notes/release-3.4.md • data_source/catalog/iceberg/DML.md
k
@Rocky is starrocks a HTAP system?
r
While StarRocks is primarily categorized as a high-performance OLAP (Online Analytical Processing) database, it provides several HTAP-like capabilities that allow it to handle both analytical and some transactional-style workloads (specifically high-concurrency point queries and real-time updates) within a single system. Here is how StarRocks addresses the requirements of an HTAP system: 1. Real-time Data Updates (The "T" in HTAP) StarRocks features a Primary Key storage engine that supports real-time 
UPSERT
and 
DELETE
operations. This allows data changes from transactional databases (like MySQL or PostgreSQL) to be synchronized into StarRocks in seconds via CDC (Change Data Capture), ensuring that analytics are always performed on the freshest data. 2. High-Concurrency Point Queries Traditionally, OLAP databases struggle with point lookups (finding a single row by ID). However, StarRocks introduced Hybrid Row-Column Storage (since v3.2.3): * Columnar Storage: Used for large-scale analytical queries and aggregations. * Row Storage: Optimized for low-latency point queries and high-concurrency access. * Benefit: This hybrid approach allows StarRocks to serve user-facing applications (like dashboards or customer portals) that require sub-second response times for specific record lookups, a core requirement of the "Transactional" side of HTAP. 3. Unified Analytics StarRocks aims to simplify the data stack by providing a Unified Analytics experience. Instead of having one system for transactions and another for analytics, StarRocks can: * Act as a high-speed serving layer for concurrent queries. * Perform complex multi-table joins and ad-hoc analysis using its MPP (Massively Parallel Processing) engine. * Query data lakes directly without migration. Summary While you wouldn't typically use StarRocks as your primary system of record for complex ACID-compliant business transactions (like a banking core system), it is considered an HTAP-capable engine because it can handle real-time updates and high-concurrency point lookups alongside heavy analytical workloads without requiring data to be moved between different systems. For more details on these features, you can check the StarRocks Documentation on Hybrid Row-Column Storage. References • introduction/what_is_starrocks.md • introduction/StarRocks_intro.md • table_design/table_design.md • table_design/hybrid_table.md • table_design/StarRocks_table_design.md • table_design/indexes/indexes.md
k
@Rocky how to address this? StarRocks internally does this: 1. Reads source schema: 
bid_event_info
has 
VARCHAR(1048576)
fields 2. Reads target schema: Even if created as 
VARCHAR(1048576)
, StarRocks auto-expands to 
VARCHAR(<tel:1073741824|1073741824>)
3. Compares types: Source STRUCT ≠ Target STRUCT (different VARCHAR sizes) 4. Fails: 
Not support cast STRUCT{...VARCHAR(1048576)...} to STRUCT{...VARCHAR(<tel:1073741824|1073741824>)...}
r
This error occurs because StarRocks (particularly in versions prior to 3.2) has strict type-checking for 
STRUCT
data types. It treats 
STRUCT<col VARCHAR(M)>
and 
STRUCT<col VARCHAR(N)>
as incompatible types if 
M != N
, even if one is a subset of the other or if both represent "unlimited" strings. In your case, 
VARCHAR(1048576)
is the 1MB limit often used in table definitions, while 
VARCHAR(1073741824)
is the internal representation of the 
STRING
type (1GB). Here are the recommended ways to address this: 1. The JSON Bridge Workaround (Most Reliable) StarRocks has more flexible casting logic when moving between 
JSON
and 
STRUCT
. You can cast the source column to 
JSON
and then immediately to the target 
STRUCT
type. This bypasses the strict length-matching check for nested 
VARCHAR
fields.
Copy code
sql
INSERT INTO target_table
SELECT 
    CAST(CAST(source_struct_col AS JSON) AS STRUCT<field1 VARCHAR(1073741824), ...>)
FROM source_table;
2. Explicitly Cast with 
STRING
Type If your StarRocks version supports the 
STRING
keyword (available in 3.0+), use it in your 
CAST
statement. In StarRocks, 
STRING
is internally equivalent to 
VARCHAR(1073741824)
. By explicitly casting the source to match the target's expanded length, you can resolve the mismatch.
Copy code
sql
-- Cast the source to use STRING (1GB) for its internal fields
INSERT INTO target_table
SELECT 
    CAST(source_struct_col AS STRUCT<field1 STRING, field2 INT, ...>)
FROM source_table;
3. Use 
named_struct
to Reconstruct If the field names are simple, you can reconstruct the struct. This allows you to explicitly define the type of each component.
Copy code
sql
INSERT INTO target_table
SELECT 
    named_struct(
        'field1', CAST(source_struct_col.field1 AS STRING),
        'field2', source_struct_col.field2
    )
FROM source_table;
4. Align Schema Definitions If you are creating the target table, try using the 
STRING
type for the nested fields instead of 
VARCHAR(1048576)
. This prevents the "auto-expansion" discrepancy because both the source (if coming from a data lake/catalog) and the target will be treated as 
VARCHAR(1073741824)
.
Copy code
sql
CREATE TABLE target_table (
    id INT,
    bid_event_info STRUCT<field1 STRING, field2 STRING> -- Use STRING here
) ...
Why does this happen? * Source: When reading from external catalogs (like Hive or Iceberg) or certain Parquet files, StarRocks maps the 
STRING
type to its internal maximum 
VARCHAR
length of 1,073,741,824 bytes. * Target: When you define a table with 
VARCHAR(1048576)
, StarRocks treats that as a fixed limit of 1,048,576 bytes. * The Conflict: The 
STRUCT
type equality check currently requires the parameters of the nested types to match exactly. Improving this implicit casting is a focus of newer StarRocks versions (v3.2+). References • knowledge/usage/best-practice.md • sql-reference/data-types/string-type/VARCHAR.md • sql-reference/data-types/semi_structured/VARIANT.md • faq/table_design_faq.md • sql-reference/data-types/semi_structured/STRUCT.md • sql-reference/System_limit.md
k
@Rocky i am using starrocks 4.0.5 what can a slution there
r
Hi there! It looks like you're using StarRocks version 4.0.x (with 4.0.3 being one of the latest documented versions). The 4.0 series introduces several major "solutions" and performance enhancements that might be relevant to what you're looking for. Since "solution" is a bit broad, could you clarify if you are looking for a fix for a specific error, or if you're asking about the new capabilities in this version? Here are some of the key solutions and features introduced in the StarRocks 4.0 series: *1. High-Precision Analytics (DECIMAL256)* If you are in finance or scientific computing, StarRocks 4.0 now supports the DECIMAL256 data type. This expands precision from 38 to 76 bits, effectively solving precision overflow issues for very large aggregations and high-order operations. *2. JSON Performance (Flat JSON V2)* If you work with semi-structured data, Flat JSON V2 provides significant performance boosts. It includes: * Page- and segment-level indexes (ZoneMaps, Bloom filters). * Predicate pushdown and dictionary encoding for JSON fields. * The ability to configure Flat JSON at the table level. *3. Query Stability (SQL Plan Manager)* To solve the problem of query performance jitter (where a plan changes due to data updates), 4.0 introduced the SQL Plan Manager. This allows you to bind a specific query plan to a query to ensure stable performance. *4. Handling Large Data Sets (Spillable Operators)* Version 4.0 introduced Partition-wise Spillable Aggregate/Distinct operators. This is a solution for high-cardinality 
GROUP BY
scenarios that previously might have run out of memory (OOM), as it allows the system to spill to disk more efficiently. *5. Enhanced Observability* * Improved EXPLAIN ANALYZE: Metrics are now grouped by operator for better readability. * Cross-FE Query Profile: You can now retrieve query profiles across all Frontends (FEs). *6. Data Lake Integration (Iceberg/Lakehouse)* If your solution involves a Data Lake, 4.0 has improved: * Support for Iceberg views and altering Iceberg table partition specs. * Optimized CSV parsing and ORC file splitting for faster external scans. If you are facing a specific error or performance bottleneck, please provide more details (like the query or error message) and I'll be happy to help you find a specific solution! References • release_notes/release-4.0.md • release_notes/release-2.5.md
k
@Rocky i am using starrocks 4.0.5 how to address this? StarRocks internally does this: 1. Reads source schema: 
bid_event_info
has 
VARCHAR(1048576)
fields 2. Reads target schema: Even if created as 
VARCHAR(1048576)
, StarRocks auto-expands to 
VARCHAR(<tel:1073741824|1073741824>)
3. Compares types: Source STRUCT ≠ Target STRUCT (different VARCHAR sizes) 4. Fails: 
Not support cast STRUCT{...VARCHAR(1048576)...} to STRUCT{...VARCHAR(<tel:1073741824|1073741824>)...}
r
This error occurs because StarRocks (starting from recent versions including the 3.x series) maintains strict type checking for nested types like 
STRUCT
. When comparing or casting two 
STRUCT
types, the lengths of the 
VARCHAR
fields within them must match exactly. The 
VARCHAR(1073741824)
($2^{30}$) is the internal representation for the 
STRING
type or a "Large String" often seen in external catalogs (like Hive, Iceberg, or MaxCompute) or when a column is defined as 
STRING
without a length. How to Address This To resolve this mismatch, you need to ensure the source 
STRUCT
fields are cast to match the target's internal type definition. 1. Explicitly Cast the Source Column In your 
INSERT INTO ... SELECT
or query, explicitly cast the source column to the exact 
STRUCT
signature of the target. Since the target is using the internal "Large String" length, you can use the 
STRING
alias or the specific large length in the cast:
Copy code
sql
-- Example: If your source column is 'my_struct'
INSERT INTO target_table
SELECT 
    CAST(source_struct_col AS STRUCT<field1 STRING, field2 STRING>)
FROM source_table;
Or use the exact length if 
STRING
doesn't resolve to 
1073741824
in your context:
Copy code
sql
CAST(source_struct_col AS STRUCT<field1 VARCHAR(1073741824), field2 VARCHAR(1073741824)>)
2. Reconstruct the Struct using 
row()
 If casting the whole struct fails or is complex due to many fields, you can reconstruct it using the 
row()
function while casting the individual fields:
Copy code
sql
INSERT INTO target_table
SELECT 
    row(
        CAST(source_struct_col.field1 AS STRING), 
        CAST(source_struct_col.field2 AS STRING)
    )
FROM source_table;
3. Adjust the Target Table Definition If the target table is a StarRocks native (OLAP) table, try defining the fields as 
STRING
instead of 
VARCHAR(1048576)
. In many cases, 
STRING
is treated as a "Large String" which aligns with the internal 
1073741824
length in the planner's metadata comparison. Why does this happen? StarRocks distinguishes between 
VARCHAR(N)
(where $N \le 1,048,576$) and the internal 
STRING
type used by catalogs/large objects. When a 
STRUCT
is involved, the engine's type-checker currently requires an exact match for nested 
VARCHAR
lengths to prevent potential data truncation or memory allocation issues, even if the source is smaller than the target. Note on Version: You mentioned version 4.0.5. Please note that as of today, the latest stable major release series is 3.3.x. If you are using a development or nightly build, this behavior is part of the ongoing refinement of the expression framework and strict type validation for complex types. References • sql-reference/sql-functions/cast.md • sql-reference/sql-functions/json-functions/json-query-and-processing-functions/cast.md • sql-reference/sql-functions/bitmap-functions/to_bitmap.md • data_source/catalog/maxcompute_catalog.md • sql-reference/data-types/semi_structured/STRUCT.md • sql-reference/data-types/semi_structured/VARIANT.md
k
@Rocky how to address this What We've Proven DOESN'T Work (Within StarRocks): ❌ 
CAST(bid_event_info AS STRING)
 - StarRocks doesn't support ❌ 
to_json(bid_event_info)
 - Doesn't work for complex types ❌ Creating Iceberg table via Polaris API - StarRocks still auto-expands at query time ❌ 
CAST(... AS STRUCT<...VARCHAR(<tel:1073741824|1073741824>)...>)
 - Still fails when nested 
ARRAY<STRUCT>
 exists ❌ Creating a View/Staging table - Same casting limitations apply 1. ❌ 
ARRAY_MAP
 lambda transformation - Creates mismatched internal representation
r
Hello! Rocky here. Dealing with deeply nested 
ARRAY<STRUCT>
types in StarRocks can indeed be tricky when you want to treat them as a single string or JSON blob. Based on the failures you've listed, here is how you can address this using the latest features (StarRocks v3.1+ and especially v3.3+): 1. Why your previous attempts failed * 
CAST(... AS STRING)
: StarRocks does not support direct casting of complex types (Array/Struct/Map) to String. * 
to_json(bid_event_info)
: Prior to recent updates, 
to_json
only accepted 
MAP
or 
STRUCT
at the root. It does not natively handle a top-level 
ARRAY
. * 
VARCHAR(1073741824)
: This length (1GB) exceeds the maximum allowed size for a 
VARCHAR
in StarRocks. For large semi-structured data, you should use the 
JSON
or 
VARIANT
type instead. * Iceberg Auto-expansion: External catalogs automatically map Iceberg's schema to StarRocks types. If Iceberg says it's a list of structs, StarRocks will use 
ARRAY<STRUCT>
. 2. The Solution: Use the 
VARIANT
Type (v3.3+) Starting from v3.3, StarRocks introduced the 
VARIANT
 type, which is specifically designed to handle flexible, complex schemas without manual expansion. It supports casting from any complex type. To convert your 
ARRAY<STRUCT>
to a JSON string: You can chain casts: 
Complex Type
-> 
VARIANT
-> 
JSON
-> 
STRING
.
Copy code
sql
SELECT 
    CAST(CAST(bid_event_info AS VARIANT) AS STRING) as bid_event_json_string
FROM iceberg_catalog.db.table;
* `CAST(col AS VARIANT)`: Captures the entire nested structure. * `CAST(variant AS STRING)`: Returns the JSON string representation of that structure. 3. Addressing Iceberg Schema Expansion Since the Iceberg catalog automatically discovers the nested schema, you cannot prevent it from seeing the 
ARRAY<STRUCT>
at the catalog level. However, you can "flatten" it into a single column in your application layer using a View:
Copy code
sql
CREATE VIEW simplified_iceberg_view AS
SELECT 
    id,
    -- Convert the complex field to a JSON type to prevent further expansion
    CAST(bid_event_info AS VARIANT) AS bid_event_info
FROM iceberg_catalog.db.table;
4. Workaround for versions 3.1 or 3.2 (If no 
VARIANT
) If you are on a version that has 
to_json
(v3.1+) but not 
VARIANT
, you can use 
array_map
to convert each struct element into a JSON object first:
Copy code
sql
SELECT 
    -- This creates an ARRAY<JSON>
    array_map(x -> to_json(x), bid_event_info) as bid_event_json_array
FROM iceberg_catalog.db.table;
If you need that array to be a single string, you would currently need to use 
VARIANT
or wait for v3.3+ features. Summary Checklist: 1. Use 
VARIANT
: It is the most robust way to handle 
ARRAY<STRUCT>
without manually defining every nested field. 2. Avoid huge VARCHAR lengths: Use 
JSON
or 
VARIANT
types which handle large blobs more efficiently. 3. Use a View: Use the 
CAST(... AS VARIANT)
inside a view to present a "cleaner" schema to your BI tools or downstream queries. Does this help clarify the path forward for your specific version? Let me know! References • data_source/catalog/maxcompute_catalog.md • sql-reference/data-types/semi_structured/VARIANT.md • data_source/catalog/paimon_catalog.md • sql-reference/data-types/semi_structured/STRUCT.md • loading/Flink-connector-starrocks.md • unloading/Spark_connector.md
k
@Rocky how to make the prmimitive array columns work • 3 pure STRUCT columns work with 
to_json()
• 5 ARRAY columns work with 
ARRAY_MAP(x -> to_json(x), column)
• 3 primitive ARRAY columns (
ARRAY<VARCHAR>
, 
ARRAY<INT>
) cannot be converted with StarRocks functions
r
To handle the conversion of primitive array columns (like 
ARRAY<INT>
or 
ARRAY<VARCHAR>
) to JSON or string formats in StarRocks, you should use 
CAST
 or 
parse_json
 rather than 
to_json()
, as 
to_json()
is primarily designed for 
MAP
and 
STRUCT
types (since v3.1). The reason your 
ARRAY_MAP(x -> to_json(x), column)
approach works for 
STRUCT
arrays but fails for primitive arrays is that 
to_json()
does not support primitive types like 
INT
or 
VARCHAR
as direct inputs. Recommended Solutions 1. Convert to JSON Type (Direct Cast) In StarRocks 3.x, you can directly cast an 
ARRAY
to the 
JSON
data type. This is the most efficient method.
Copy code
sql
SELECT CAST(your_primitive_array_column AS JSON) FROM your_table;
2. Convert to JSON Type (Fallback) If your version has limitations on direct casting for certain complex nesting, you can cast the array to a 
VARCHAR
first (which produces a JSON-formatted string like 
[1,2,3]
) and then use `parse_json()`:
Copy code
sql
SELECT parse_json(CAST(your_primitive_array_column AS VARCHAR)) FROM your_table;
3. Convert to String (for Exporting/Sinks) If you simply need a string representation of the array: • For `ARRAY<VARCHAR>`: You can use 
array_join
if you want a custom delimiter.
Copy code
sql
  SELECT array_join(your_varchar_array, ', ') FROM your_table;
• For any primitive array: Use 
CAST
to 
VARCHAR
to get the standard 
[elem1, elem2]
format.
Copy code
sql
  SELECT CAST(your_int_array AS VARCHAR) FROM your_table;
Why your current methods behave differently: * 
to_json(STRUCT)
: Works because 
to_json
is explicitly built to serialize 
STRUCT
and 
MAP
into JSON. * 
ARRAY_MAP(x -> to_json(x), ...)
: Works when elements 
x
are `STRUCT`s. However, when elements are 
INT
or 
VARCHAR
, 
to_json(x)
fails because it doesn't support primitive arguments. * Primitive Arrays: Since these elements are already "JSON-compatible" primitives, you don't need to map over them with a conversion function. You can convert the entire container at once using 
CAST(... AS JSON)
. Summary Table Target Format | Function to Use JSON Type | 
CAST(col AS JSON)
JSON String | 
CAST(col AS VARCHAR)
Custom String | 
array_join(col, separator)
(Varchar only) References • sql-reference/sql-functions/json-functions/json-constructor-functions/json_array.md • sql-reference/data-types/semi_structured/JSON.md • sql-reference/sql-functions/json-functions/json-query-and-processing-functions/to_json.md • sql-reference/sql-functions/cast.md • sql-reference/sql-functions/json-functions/json-constructor-functions/json_object.md
2 Views
Open in Slack
PreviousNext
Powered by