@Rocky can you do cross catalog query on starrocks? i want to combine parquet and delta

Yes, you can absolutely perform cross-catalog queries in StarRocks. One of StarRocks' core strengths is its Global Data Analytics capability, which allows you to join data across different catalogs (like Delta Lake, Hive, Iceberg, and the internal StarRocks storage) in a single SQL statement without migrating any data. To combine Parquet and Delta Lake data, you have two primary methods depending on how your Parquet files are managed. Method 1: Cross-Catalog Join (Delta Lake + Hive/Iceberg) If your Parquet files are registered in a Hive Metastore or Glue, you can create two separate catalogs and join them. 1. Create a Delta Lake Catalog:

sql
   CREATE EXTERNAL CATALOG delta_catalog
   PROPERTIES (
       "type" = "deltalake",
       "hive.metastore.uris" = "thrift://<hms_ip>:9083"
   );

2. Create a Hive Catalog (for Parquet):

sql
   CREATE EXTERNAL CATALOG hive_catalog
   PROPERTIES (
       "type" = "hive",
       "hive.metastore.uris" = "thrift://<hms_ip>:9083"
   );

3. Perform the Cross-Catalog Query:

sql
   SELECT
       d.user_id,
       h.order_amount
   FROM delta_catalog.delta_db.users d
   JOIN hive_catalog.hive_db.orders h ON d.user_id = h.user_id
   WHERE d.region = 'US';

Method 2: Delta Lake Catalog +

FILES()

function If your Parquet data is just "raw" files sitting in S3 or HDFS without a metastore, you can use the

FILES()

table function to join them directly with your Delta Lake tables.

sql
SELECT 
    d.user_id, 
    p.event_name
FROM delta_catalog.delta_db.users d
JOIN FILES(
    "path" = "<s3://my-bucket/parquet_data/*.parquet>",
    "format" = "parquet",
    "aws.s3.region" = "us-east-1",
    "aws.s3.access_key" = "...",
    "aws.s3.secret_key" = "..."
) p ON d.user_id = p.user_id;

Key Advantages: * Zero Ingestion: You query the data directly where it lives in your data lake. * High Performance: StarRocks uses its C++ vectorized execution engine and cost-based optimizer (CBO) to make these cross-catalog joins significantly faster than traditional engines. * Unified Security: You can manage access to these catalogs using StarRocks' internal RBAC (Role-Based Access Control). Note: Ensure your StarRocks FE/BE nodes have network access to the storage (S3/HDFS) and the metastore (HMS/Glue) where your Parquet and Delta Lake reside. References • data_source/catalog/hive_catalog.md • data_source/file_external_table.md • loading/hdfs_load.md • loading/Loading_data_template.md

@Rocky StarRocks > show catalogs; +-----------------+-----------+------------------------------------------------------------------+ | Catalog | Type | Comment | +-----------------+-----------+------------------------------------------------------------------+ | deltalake | Deltalake | NULL | | hms | Hive | NULL | | iceberg | Iceberg | NULL | +-----------------+-----------+------------------------------------------------------------------+ CREATE VIEW

view_1

(columns) AS SELECT columns FROM hive_metastore.db1.table_name WHERE mp_date < '2025-09-01' UNION ALL SELECT columns FROM hive_metastore.db2.table_name WHERE mp_date >= '2025-09-01'; 1 row in set (0.04 sec) I am doing select count(*) from view_1, but since db2.table_name is delta i am not getting any data from hms catalog. the catalog should be deltalake The view is defined on aws glue. any way to make starrocks pick deltalake for db2.table_name?

The reason you aren't seeing data from the Delta Lake table when querying through your

hms

catalog is that StarRocks' Hive Catalog (type

hive

) is specifically designed to read standard Hive formats (Parquet, ORC, etc.). It does not automatically switch to the Delta Lake engine even if the table is stored in Delta format. To solve this, you should use the Unified Catalog (available from StarRocks v3.2 onwards). The Solution: Unified Catalog A Unified Catalog is designed to automatically detect the table format (Hive, Delta Lake, Iceberg, or Hudi) from the metastore and use the appropriate reader for each table. This is perfect for your scenario where a single view references tables of different formats. 1. Create a Unified Catalog Run the following command to create a catalog that handles both Parquet (Hive) and Delta Lake tables from Glue:

sql
CREATE EXTERNAL CATALOG glue_unified
PROPERTIES (
    "type" = "unified",
    "unified.metastore.type" = "glue",
    "aws.glue.region" = "your-region", -- e.g., us-east-1
    "aws.s3.region" = "your-region",
    -- Choose your authentication method (instance profile, access key, etc.)
    "aws.glue.use_instance_profile" = "true",
    "aws.s3.use_instance_profile" = "true"
);

2. Query the View Instead of querying through the

hms

catalog, query the view using the new

glue_unified

catalog:

sql
SELECT count(*) FROM glue_unified.your_db.view_1;

Why this works: * Automatic Detection: When StarRocks encounters

db1.table_name

(Parquet) and

db2.table_name

(Delta) through the Unified Catalog, it identifies their formats from the Glue metadata and uses the native high-performance Delta Lake reader for the second table and the Hive reader for the first. * Seamless View Resolution: Since the view is defined in Glue, StarRocks will resolve the table references within the context of the catalog you used to access the view. By using

glue_unified

, every table referenced in the view's definition will benefit from the unified format detection. Requirements: * StarRocks Version: Ensure you are on v3.2 or later. * Metastore: The Unified Catalog supports AWS Glue and Hive Metastore (HMS). Recommendation: If your Glue/HMS contains a mix of formats, it is generally better to use the

unified

catalog type instead of separate

hive

and

deltalake

catalogs for a much simpler cross-format querying. References • data_source/catalog/unified_catalog.md • data_source/catalog/catalog_overview.md • data_source/catalog/deltalake_catalog.md • data_source/feature-support-data-lake-analytics.md • loading/INSERT.md ) References • data_source/catalog/unified_catalog.md • data_source/feature-support-data-lake-analytics.md