http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> If we have different schema definit...
# questions-and-troubleshooting
m
@Rocky If we have different schema definition in Iceberg and in Starrocks, how can we query both tables in the same time, how does the pagination works, filter, sort and so on?
r
When you have different schema definitions in Iceberg and StarRocks (e.g., your local StarRocks table has one schema and your external Iceberg table has another), you can query them together seamlessly using StarRocks' External Catalog feature. 1. Querying Both Tables at the Same Time StarRocks allows "Federated Queries," which means you can join local tables and external Iceberg tables in a single SQL statement. * How to do it: You refer to the Iceberg table using its full path: 
catalog_name.database_name.table_name
. * Example:
Copy code
sql
    SELECT
        ice.user_id,
        sr.username,
        ice.action
    FROM iceberg_catalog.sales_db.logs AS ice
    JOIN default_catalog.local_db.users AS sr
      ON ice.user_id = sr.id
    WHERE ice.event_date = '2023-10-01';
* Schema Differences: * Data Types: StarRocks automatically maps Iceberg data types to StarRocks data types (e.g., Iceberg 
string
to StarRocks 
varchar
, 
long
to 
bigint
). * Column Names: If names differ, use Aliases (
AS
) in your query or create a Unified View:
Copy code
sql
        CREATE VIEW unified_users AS
        SELECT id, username FROM local_db.users
        UNION ALL
        SELECT user_id AS id, name AS username FROM iceberg_catalog.db.users;
2. How Filtering Works (Predicate Pushdown) StarRocks does not just "download" the whole Iceberg table. It uses Predicate Pushdown to make queries efficient: * Metadata Filtering: StarRocks reads Iceberg's metadata (manifest files) to skip entire files or partitions that don't match your 
WHERE
clause. * Storage-level Filtering: For Parquet or ORC files in Iceberg, StarRocks pushes filters down to the data files, reading only the necessary columns and rows (using min/max stats). * Supported Operators: Standard filters like 
=
, 
>
, 
<
, 
IN
, 
BETWEEN
, 
IS NULL
, and 
AND/OR
are all pushed down. 3. How Sorting Works * Execution: Sorting (
ORDER BY
) is generally performed by StarRocks' BE (Backend) nodes. * Optimization: If you join a local table with an Iceberg table, StarRocks' Cost-Based Optimizer (CBO) decides whether to sort before or after the join to minimize memory usage. * Hidden Partitioning: StarRocks supports Iceberg's hidden partitioning, so if you sort or filter by a partition transformed column (like 
days(timestamp)
), StarRocks can still optimize the scan. 4. How Pagination Works (LIMIT and OFFSET) * Limit Pushdown: If you use 
LIMIT 10
, StarRocks pushes this down to the scan level. Once the BE nodes have read 10 rows from the Iceberg files that satisfy your filters, they stop reading further. * Offset: 
OFFSET
is handled by the StarRocks execution engine. For example, 
LIMIT 10 OFFSET 100
tells StarRocks to read 110 rows and discard the first 100. * Best Practice: For large-scale pagination (deep paging), avoid high 
OFFSET
values. Instead, use a "seek" or "keyset" method (e.g., 
WHERE id > last_id_from_previous_page ORDER BY id LIMIT 10
) for better performance. 5. Schema Evolution If the schema in Iceberg changes (e.g., you added a column or renamed one in the Iceberg source), StarRocks handles this automatically: * Automatic Sync: The Iceberg Catalog periodically refreshes its metadata. * Consistency: You don't need to run 
ALTER TABLE
in StarRocks; it will see the new schema from the Iceberg metadata automatically. You can control the refresh frequency using the property 
metastore_cache_refresh_interval_sec
(default is 60s). References • knowledge/usage/best-practice.md • data_source/External_table.md • data_source/catalog/elasticsearch_catalog.md
m
Copy code
SELECT
        ice.user_id,
        sr.username,
        ice.action
    FROM iceberg_catalog.sales_db.logs AS ice
    JOIN default_catalog.local_db.users AS sr
      ON ice.user_id = sr.id
    WHERE ice.event_date = '2023-10-01';
Copy code
lets say in sr the field name is event_start_date, and in ice its event_date
how would I approach this where clause? I want it to filter from both tables @Rocky
r
To ensure both the Iceberg table and the StarRocks local table are filtered (which improves performance by reducing the data scanned from both sources), you have two primary approaches depending on your business logic. Option 1: Explicit Filtering (Recommended for Clarity) The most direct way to ensure StarRocks pushes the filter down to both the Iceberg storage (S3/HDFS) and the local StarRocks storage is to include the condition for both columns in the 
WHERE
clause.
Copy code
sql
SELECT
    ice.user_id,
    sr.username,
    ice.action
FROM iceberg_catalog.sales_db.logs AS ice
JOIN default_catalog.local_db.users AS sr
  ON ice.user_id = sr.id
WHERE ice.event_date = '2023-10-01'       -- Pushed down to Iceberg (S3/HDFS)
  AND sr.event_start_date = '2023-10-01'; -- Pushed down to StarRocks local storage
Why this works: StarRocks' optimizer sees two independent filters and pushes them both to their respective data sources. This minimizes the number of rows sent to the join engine. ────────── Option 2: Join on the Date Column If your logic implies that you only want to join rows where the dates match, you can include the date columns in the 
JOIN
condition. StarRocks’ Cost-Based Optimizer (CBO) will then perform Predicate Propagation.
Copy code
sql
SELECT
    ice.user_id,
    sr.username,
    ice.action
FROM iceberg_catalog.sales_db.logs AS ice
JOIN default_catalog.local_db.users AS sr
  ON ice.user_id = sr.id 
  AND ice.event_date = sr.event_start_date -- Logic: Dates must match
WHERE ice.event_date = '2023-10-01';
Why this works: Because of the equality 
ice.event_date = sr.event_start_date
, the optimizer automatically infers that 
sr.event_start_date
must also be 
'2023-10-01'
. It will push that filter down to the local table automatically. ────────── Option 3: Using a CTE (Cleanest for Schema Differences) If you have many queries and want to hide the different column names, use a Common Table Expression (CTE). This makes the query logic look "unified" while still allowing the optimizer to push filters down.
Copy code
sql
WITH unified_logs AS (
    SELECT user_id, event_date, action FROM iceberg_catalog.sales_db.logs
),
unified_users AS (
    SELECT id, event_start_date AS event_date, username FROM default_catalog.local_db.users
)
SELECT
    l.user_id,
    u.username,
    l.action
FROM unified_logs l
JOIN unified_users u ON l.user_id = u.id
WHERE l.event_date = '2023-10-01'
  AND u.event_date = '2023-10-01';
Summary of Performance Behaviors: * Predicate Pushdown: In all three options, StarRocks will use Iceberg manifest files to skip irrelevant data files and use StarRocks' zone maps/indexes to skip local data. * Runtime Filters: If one table is significantly smaller, StarRocks may generate a "Runtime Filter" (like a Bloom Filter) based on the 
user_id
from the smaller table and push it to the scan of the larger table to further reduce I/O. References • using_starrocks/accelerate-cardinality-preserving-joins.md
m
1. We want lets say data from the last 30 days, it should get last 7 days from SR, and the rest 23 from Iceberg, can it be by union and not by join? 2. can we do unified view and give aliases to the same name -> ice.date as start_date and sr.date2 as start_date and then do the clauses by start_date @Rocky
r
Yes, you can absolutely use 
UNION ALL
to combine data from StarRocks (hot data) and Iceberg (cold data). This is a common "Hot/Cold" architecture pattern in StarRocks. 1. Using 
UNION ALL
for Time-Based Split You can create a unified view that handles the logic of where the data comes from. For your 7-day/23-day split, you should define the time boundaries clearly in the view to ensure StarRocks only queries the necessary source. Example View Definition:
Copy code
sql
CREATE VIEW unified_events AS
-- Last 7 days from StarRocks
SELECT 
    user_id, 
    event_start_date AS start_date, -- Alias here
    action 
FROM default_catalog.local_db.users
WHERE event_start_date >= CURRENT_DATE() - INTERVAL 7 DAY

UNION ALL

-- Rest (days 8 to 30) from Iceberg
SELECT 
    user_id, 
    event_date AS start_date,       -- Same alias here
    action 
FROM iceberg_catalog.sales_db.logs
WHERE event_date >= CURRENT_DATE() - INTERVAL 30 DAY 
  AND event_date < CURRENT_DATE() - INTERVAL 7 DAY;
2. How Filtering and Aliases Work When you query this view with a 
WHERE
clause on the alias 
start_date
, StarRocks' Cost-Based Optimizer (CBO) performs Predicate Pushdown: * Logic: If you run 
SELECT * FROM unified_events WHERE start_date = '2023-10-01'
, the engine "unwraps" the view. * Translation: It translates the filter back to the original columns. It will apply 
event_start_date = '2023-10-01'
to the StarRocks branch and 
event_date = '2023-10-01'
to the Iceberg branch. * Pruning: If the filter is 
'2023-10-01'
and that date only falls into the Iceberg range (e.g., it's 20 days ago), StarRocks is smart enough to prune the StarRocks branch entirely and only scan Iceberg. 3. Sorting and Pagination across Union * Sorting (
ORDER BY
): When you sort the results of a 
UNION ALL
, StarRocks first fetches the filtered results from both sources and then performs a merge-sort or a global sort in the BE (Backend) nodes. * Pagination (
LIMIT
& 
OFFSET
): * Limit Pushdown: If you use 
LIMIT 10
, StarRocks pushes 
LIMIT 10
down to both the Iceberg and StarRocks scans. * Efficiency: Each source only sends back 10 rows. The StarRocks coordinator then takes those 20 rows (10 from each), sorts them if needed, and returns the final top 10. * Offset: Just like a single table, 
OFFSET 100 LIMIT 10
will require the engine to process 110 rows from the unioned set and discard the first 100. 4. Summary of Why This Works Well 1. Schema Decoupling: The View acts as a mapping layer. You can change the underlying table names or column names in Iceberg, and you only need to update the View definition once. 2. Performance: Because StarRocks treats the Iceberg catalog as a first-class citizen, it uses the same vectorized execution engine for both branches. 3. Transparency: Your BI tools or applications just see one table (
unified_events
) and don't need to know that data is physically split between local disks and S3/HDFS. Pro-Tip: If you find the Iceberg part of the query is slower than you'd like, you can create an Asynchronous Materialized View on top of the Iceberg table to cache the "cold" data in StarRocks' native format while still keeping it logically separated. References • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • data_source/feature-support-data-lake-analytics.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md
m
@Rocky
Copy code
event_date AS start_date,
I dont see the usage of the alias in the where clause, why so?
r
The reason you didn't see the alias 
start_date
used in the 
WHERE
clauses inside the View definition is due to the standard SQL Order of Operations. 1. Why you use original names inside the View In SQL, the 
WHERE
clause is evaluated before the 
SELECT
clause. • The engine first filters the data (
WHERE
). • Then it picks and renames the columns (
SELECT ... AS ...
). Because the alias 
start_date
doesn't "exist" yet when the 
WHERE
filter is running, you must use the original column names from the physical tables (
event_date
and 
event_start_date
).
Copy code
sql
-- Inside the view definition, you use original names:
CREATE VIEW unified_events AS
SELECT event_start_date AS start_date FROM local_table 
WHERE event_start_date > '2023-01-01' -- Alias 'start_date' not yet available
UNION ALL
SELECT event_date AS start_date FROM iceberg_table 
WHERE event_date > '2023-01-01';      -- Alias 'start_date' not yet available
2. Why you use the alias when querying the View Once the view is created, 
start_date
becomes a "real" column name for that view. When you query the view, you must use the alias because that is the only name the view exposes.
Copy code
sql
-- When querying the view, you use the alias:
SELECT * 
FROM unified_events 
WHERE start_date = '2023-10-01'; -- Here you use the alias!
3. How StarRocks handles this (Predicate Pushdown) Even though you query using the alias 
start_date
, StarRocks is smart enough to "map" that filter back down to the original columns in the underlying tables. 1. You run: 
SELECT ... WHERE start_date = '2023-10-01'
2. StarRocks looks at the View definition. 3. It sees 
start_date
comes from 
event_start_date
in the local table and 
event_date
in the Iceberg table. 4. It pushes the filter down as: • 
WHERE event_start_date = '2023-10-01'
for the StarRocks scan. • 
WHERE event_date = '2023-10-01'
for the Iceberg scan. Summary * Defining the View: Use original names in 
WHERE
. * Querying the View: Use the alias in 
WHERE
. * Result: Efficient filtering happens at the source level (S3/Local Disk) for maximum performance. References • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md
m
@Rocky
Copy code
sql
-- Inside the view definition, you use original names:
CREATE VIEW unified_events AS
SELECT event_start_date AS start_date FROM local_table 
WHERE event_start_date > '2023-01-01' -- Alias 'start_date' not yet available
UNION ALL
SELECT event_date AS start_date FROM iceberg_table 
WHERE event_date > '2023-01-01';      -- Alias 'start_date' not yet available
Copy code
does the select from local_table
and from iceberg_table
should be the same for the union to succeed? in starrocks we have a JSON field and in iceberg the table is this JSON but flattened into columns
r
Yes, for a 
UNION ALL
to succeed, the number of columns and the data types of those columns must match (or be compatible). The column names do not have to match (the view will use the names from the first 
SELECT
statement). Since your StarRocks table uses a JSON field and your Iceberg table is flattened, you have two ways to "sync" them. Option 1: Flatten the StarRocks JSON (Recommended) If you want to treat the data as individual columns, you can extract fields from the StarRocks JSON using the 
->
operator or 
get_json_int/string
functions.
Copy code
sql
CREATE VIEW unified_events AS
-- StarRocks: Extract fields from JSON to match Iceberg columns
SELECT 
    user_id,
    event_start_date AS start_date,
    data_json->'action' AS action,        -- Extracting from JSON
    CAST(data_json->'amount' AS INT) AS amount
FROM default_catalog.local_db.users
WHERE event_start_date >= CURRENT_DATE() - INTERVAL 7 DAY

UNION ALL

-- Iceberg: Already flattened
SELECT 
    user_id, 
    event_date AS start_date,
    action,                               -- Already a column
    amount                                -- Already a column
FROM iceberg_catalog.sales_db.logs
WHERE event_date < CURRENT_DATE() - INTERVAL 7 DAY;
Option 2: Package Iceberg into JSON If your application expects a single JSON blob, you can use the 
json_object
function to package the Iceberg columns into a JSON format that matches StarRocks.
Copy code
sql
CREATE VIEW unified_events AS
-- StarRocks: Use the native JSON field
SELECT 
    user_id,
    event_start_date AS start_date,
    data_json 
FROM default_catalog.local_db.users

UNION ALL

-- Iceberg: Create a JSON object from columns
SELECT 
    user_id, 
    event_date AS start_date,
    -- Creates {"action": "login", "amount": 100}
    json_object('action', action, 'amount', amount) AS data_json
FROM iceberg_catalog.sales_db.logs;
Which one should you choose? * Choose Option 1 (Flattening) if you frequently filter or aggregate by specific fields (like 
amount
). StarRocks can optimize these extracted fields much better. * Choose Option 2 (Packaging) if your schema changes very often and you want the flexibility of JSON without updating the view every time a new column is added to Iceberg. Key Rules for `UNION ALL`: 1. Column Count: Must be identical in both 
SELECT
blocks. 2. Order: The engine matches columns by position (1st to 1st, 2nd to 2nd), not by name. 3. Data Types: If one side is a 
VARCHAR
and the other is an 
INT
, StarRocks will try to convert them to a common type (like 
VARCHAR
). However, complex types like 
JSON
generally need to be matched with other 
JSON
types or converted to 
VARCHAR
. References • sql-reference/sql-statements/table_bucket_part_index/SELECT.md • using_starrocks/Flat_json.md References • using_starrocks/Flat_json.md
m
@Rocky In iceberg we also have one of the field as nested struct (row) (with more nested fields inside of it) Will it know how to union it as well?
r
Yes, StarRocks can handle Iceberg's nested 
STRUCT
(row) types in a 
UNION ALL
, but you must ensure the data types match between the two sides of the union. Since your StarRocks side uses a JSON column and your Iceberg side has a nested STRUCT, you have two primary ways to align them: Option 1: Cast the Iceberg STRUCT to JSON (Easiest) If your StarRocks table stores everything in a single 
JSON
column, the simplest approach is to cast the Iceberg nested struct to the 
JSON
type. StarRocks will automatically convert the nested structure into a JSON object.
Copy code
sql
CREATE VIEW unified_events AS
-- StarRocks side (already JSON)
SELECT 
    event_id,
    metadata -- This is a JSON column
FROM local_db.events

UNION ALL

-- Iceberg side (nested STRUCT)
SELECT 
    event_id,
    CAST(user_details AS JSON) -- Converts STRUCT to JSON
FROM iceberg_catalog.db.events;
Option 2: Define a Matching STRUCT on both sides If you prefer strict typing (which is faster for performance), you can define the specific structure on both sides. StarRocks supports the 
STRUCT
data type.
Copy code
sql
CREATE VIEW unified_events AS
-- StarRocks: Cast JSON to a specific STRUCT
SELECT 
    event_id,
    CAST(metadata AS STRUCT<user_id INT, info STRUCT<city STRING, zip INT>>) as user_info
FROM local_db.events

UNION ALL

-- Iceberg: Use the native STRUCT (ensure the sub-field names/types match)
SELECT 
    event_id,
    user_details -- This is already a nested STRUCT in Iceberg
FROM iceberg_catalog.db.events;
Important Considerations: 1. Field Order in Structs: When unioning two 
STRUCT
types, StarRocks matches them by position and type, not necessarily by the name of the sub-fields. Ensure the order of fields in your 
CAST
or Iceberg schema is identical. 2. Accessing Nested Data: Once you've unioned them into a view, you can access the nested fields using the dot notation regardless of whether the underlying source was JSON or STRUCT: * 
SELECT user_info.info.city FROM unified_view
3. Variant Type (v3.3+): If your Iceberg table uses very complex or evolving schemas, StarRocks 3.3+ introduced the 
VARIANT
type. You can cast both the StarRocks JSON and the Iceberg Struct to 
VARIANT
for a completely schema-less union. Recommendation: If the schema of the nested struct is stable, Option 2 (STRUCT) is faster. If the schema changes often or has many optional fields, Option 1 (JSON) is much more flexible. References • sql-reference/data-types/semi_structured/VARIANT.md • sql-reference/data-types/semi_structured/JSON.md • using_starrocks/Flat_json.md • data_source/iceberg_catalog.md -source/iceberg_catalog) References • using_starrocks/Flat_json.md • sql-reference/data-types/semi_structured/VARIANT.md • sql-reference/data-types/semi_structured/JSON.md
e
@Rocky when loading parquet files from s3 via pipe files() -> can it load it into iceberg instead of local table ?
r
No, StarRocks Pipe (and the 
CREATE PIPE
feature) is designed specifically for loading data into internal StarRocks tables (OLAP tables). It cannot be used to load data into an external Iceberg table. Here is the breakdown of how these features work and what you can do instead: 1. Why 
PIPE
doesn't work for Iceberg The Pipe feature (
CREATE PIPE
) is an automated background management object. In StarRocks, "loading" refers to the process of ingesting data into its own high-performance storage engine. Writing data to an external catalog like Iceberg is technically a "Sink" or "Unload" operation. * The 
PIPE
mechanism tracks state (which files are loaded) specifically for Internal tables. * The StarRocks documentation explicitly states that 
INSERT INTO ... SELECT FROM FILES
for loading targets a destination internal table. 2. The Alternative: One-off 
INSERT INTO
 While you cannot use a 
PIPE
, you can manually run a standard 
INSERT INTO
to move data from Parquet files on S3 into an Iceberg table. StarRocks acts as the compute engine to process the Parquet and write it to the Iceberg format. Example:
Copy code
sql
-- This works as a manual command, but NOT inside a CREATE PIPE
INSERT INTO iceberg_catalog.target_db.target_table
SELECT * FROM FILES(
    "path" = "<s3://my-bucket/incoming-parquet/*.parquet>",
    "format" = "parquet",
    "aws.s3.region" = "us-east-1",
    "aws.s3.access_key" = "...",
    "aws.s3.secret_key" = "..."
);
3. Summary of Support Feature | Target: Internal Table | Target: Iceberg Table INSERT INTO ... SELECT FROM FILES | ✅ Supported | ✅ Supported (v3.1+) CREATE PIPE | ✅ Supported | ❌ Not Supported Continuous Auto-Ingest | ✅ Supported (via Pipe) | ❌ Not Supported Recommendation If you need to continuously move data from S3 to Iceberg, you typically wouldn't use StarRocks' 
PIPE
. Instead, you would: 1. Use an external orchestrator (like Airflow or a Cron job) to trigger the 
INSERT INTO iceberg_table SELECT FROM FILES()
command periodically. 2. Load into StarRocks first: If you need the extreme performance of StarRocks for queries, use the 
PIPE
to load into a local table, and then occasionally "sync" that data to Iceberg if needed for a data lake. References • loading/loading_introduction/feature-support-loading-and-unloading.md • sql-reference/sql-statements/loading_unloading/pipe/CREATE_PIPE.md • data_source/catalog/iceberg/DML.md • loading/InsertInto.md References • loading/loading_introduction/feature-support-loading-and-unloading.md • sql-reference/sql-statements/loading_unloading/pipe/CREATE_PIPE.md • data_source/External_table.md • sql-reference/information_schema/pipes.md
2 Views
Open in Slack
PreviousNext
Powered by