@Rocky can i do select query to get image file from s3 with starrocks?

Yes, you can use StarRocks to query file information from S3, but there are specific ways to handle image files depending on whether you want to see the file metadata or the actual image content. 1. Querying Image File Metadata (Listing Files) Since StarRocks v3.4.0, you can use the

FILES()

table function with the

list_files_only

parameter to list files on S3. This allows you to "SELECT" a list of your image files, their sizes, and modification times directly from S3 without loading them. Example:

sql
SELECT * FROM FILES(
    "path" = "<s3://your-bucket/images/*.jpg>",
    "aws.s3.access_key" = "your_access_key",
    "aws.s3.secret_key" = "your_secret_key",
    "aws.s3.region" = "us-east-1",
    "list_files_only" = "true"
);

Result columns:

PATH

,

SIZE

,

IS_DIR

,

MODIFICATION_TIME

. 2. Querying Image Content (Binary Data) StarRocks does not support reading raw

.jpg

or

.png

files as rows of binary data directly via the

FILES()

function. The

FILES()

function expects structured formats like Parquet, ORC, CSV, Avro, or JSON. To query or store image content in StarRocks, you should follow these steps: * Format: Store the images as binary fields within a Parquet or ORC file on S3. * Data Type: Use the

VARBINARY

data type (available since v3.0). * Limit: The maximum length for a

VARBINARY

column is 1,048,576 bytes (1 MB). If your images are larger than 1MB, you should store the S3 path in StarRocks instead and keep the actual file on S3. Example of querying images from a Parquet file on S3:

sql
SELECT image_id, image_data 
FROM FILES(
    "path" = "<s3://your-bucket/data/images_dataset.parquet>",
    "format" = "parquet",
    "aws.s3.access_key" = "...",
    "aws.s3.secret_key" = "...",
    "aws.s3.region" = "..."
);

Summary * To list image files: Use

FILES()

with

"list_files_only" = "true"

. * To query image content: The image must be embedded in a supported format like Parquet/ORC and fit within the 1MB

VARBINARY

limit. * Best Practice: For large-scale image processing, store the image metadata and S3 URI in StarRocks and use the URI to fetch the actual image from S3 in your application layer. References • sql-reference/sql-functions/table-functions/files.md

@Rocky how about using external catalog?

Using External Catalogs is one of StarRocks' most powerful features. It allows you to query data directly from external systems—like data lakes or relational databases—without needing to migrate or load the data into StarRocks first. Here is an overview of how they work and why you would use them: 1. What is an External Catalog? An External Catalog acts as a read-only "link" to an external metastore. When you query a table through an external catalog: * Metadata is fetched from the external metastore (e.g., Hive Metastore, AWS Glue). * Data is read directly from the storage layer (e.g., HDFS, S3, GCS) by StarRocks' BE/CN nodes. 2. Supported Data Sources StarRocks supports a wide range of external sources: * Data Lakes: Hive, Apache Iceberg, Apache Hudi, Delta Lake, Apache Paimon. * JDBC Sources: MySQL, PostgreSQL, Oracle, SQL Server, ClickHouse, and more. * Others: Elasticsearch, Apache Kudu. * Unified Catalog (v3.2+): A single catalog that can manage Hive, Iceberg, Hudi, and Delta Lake sources simultaneously. 3. How to Use It (Example) Creating and using a catalog is simple. Here is an example using a Hive Catalog: Step 1: Create the Catalog

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

Step 2: Access the Data You can switch your session to use this catalog:

sql
SET CATALOG hive_test;
SHOW DATABASES;
SELECT * FROM my_db.my_table LIMIT 10;

Step 3: Cross-Catalog Queries You can join data between your internal StarRocks tables and external lake tables using "dot" notation:

sql
SELECT 
    t1.user_id, 
    t2.region 
FROM default_catalog.local_db.users t1  -- StarRocks internal table
JOIN hive_test.remote_db.locations t2    -- External Hive table
ON t1.user_id = t2.user_id;

4. Key Benefits * Zero Data Ingestion: Query petabytes of data instantly without the cost or delay of loading it. * Federated Queries: Join data across different silos (e.g., join a MySQL table with an Iceberg table). * Performance: StarRocks uses optimizations like CBO (Cost-Based Optimizer), Data Cache, and Local File Metadata Cache to make external queries extremely fast, often matching the performance of native storage. * Asynchronous Materialized Views: You can build a Materialized View on top of an external catalog to pre-aggregate data or accelerate complex lake queries. Tips for Success * Credentials: If your data is on S3/GCS/Azure, make sure you provide the necessary cloud credentials in the catalog

PROPERTIES

. * Caching: For data lake queries, enable the Data Cache feature to speed up repeated queries by storing hot data on local NVMe/SSD disks. Would you like to see a specific configuration example for a source like Iceberg or JDBC? References • data_source/catalog/catalog_overview.md • data_source/feature-support-data-lake-analytics.md • data_source/catalog/unified_catalog.md • sql-reference/sql-statements/Catalog/CREATE_EXTERNAL_CATALOG.md • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md • data_source/catalog/jdbc_catalog.md

with iceberg table, can i get image file from s3 metastore with starrock? @Rocky

Yes, you can access image data from an Iceberg table on S3 using StarRocks, but there are important size limitations and behavioral details to consider. 1. Data Type Support Since version 3.0, StarRocks supports the

VARBINARY

(or

BINARY

) data type. When you create an Iceberg catalog in StarRocks, it automatically maps Iceberg’s

BINARY

and

FIXED

types to StarRocks'

VARBINARY

. 2. Size Limitation (The 1MB Limit) The most critical limitation is that the maximum length for a

VARBINARY

or

VARCHAR

column in StarRocks is 1,048,576 bytes (1 MB). * If your image files are smaller than 1MB: You can store them directly as binary blobs in the Iceberg table and query them. * If your image files are larger than 1MB: StarRocks will not be able to store or read the full binary content in a single column. 3. How to Query StarRocks will not "display" the image like a browser; instead, it returns the raw bytes. You can use built-in functions to process or export this data: * Base64 Encoding: Useful for passing image data to web applications.

sql
    SELECT to_base64(image_column) FROM iceberg_catalog.db.table;

* Hexadecimal:

sql
    SELECT hex(image_column) FROM iceberg_catalog.db.table;

4. Best Practice for Images in StarRocks Because StarRocks is an OLAP database designed for high-performance analytics, storing large binary blobs (like high-res images) inside the table can lead to significant memory overhead during queries. Recommended Approach: Instead of storing the actual image bytes in the Iceberg table, store the S3 URI (e.g., `s3://my-bucket/images/img_01.jpg`) as a

VARCHAR

. 1. Use StarRocks to filter and find the metadata/path of the images you need. 2. Have your application or middle-tier fetch the actual file from S3 using the URI provided by StarRocks. Summary * Supported: Yes (via

VARBINARY

). * Version Required: StarRocks v3.0 or later. * Max Image Size: 1 MB. * Access Method: External Catalog (Iceberg) pointing to S3. References • sql-reference/data-types/string-type/BINARY.md • knowledge/usage/best-practice.md • sql-reference/data-types/string-type/VARCHAR.md • sql-reference/data-types/string-type/STRING.md • sql-reference/System_limit.md • faq/table_design_faq.md • faq/Others.md

@Rocky using external catalog, what kind of data type can support more than 10mb ? for example varbanary. think about using iceberg table

None References • sql-reference/data-types/string-type/STRING.md • knowledge/usage/best-practice.md • faq/Others.md • sql-reference/System_limit.md • sql-reference/data-types/string-type/BINARY.md • sql-reference/data-types/string-type/VARCHAR.md • administration/management/BE_configuration.md • faq/table_design_faq.md