@Rocky can StarRocks 4.1.1 consume json file directly from S3 or it needs to be streamed? WHat are best options to ingest JSONs? if not JSONs what are other options?

Yes, StarRocks can consume JSON files directly from S3, but the method you use matters. Direct Ingestion from S3 As of version 4.1.1, the simpler *`INSERT INTO ... SELECT * FROM FILES()`* and Pipe methods do not yet support the JSON format (they currently support Parquet, ORC, CSV, and Avro). To ingest JSON files directly from S3, you should use Broker Load. * Broker Load is an asynchronous method that pulls data from S3 in the background. It has supported JSON since version 3.2.3. * Best Option for S3 JSONs: Use Broker Load if your data is already on S3 and you want a direct, "set-and-forget" batch ingestion. Other Ingestion Options for JSON If you prefer not to use Broker Load, you have these alternatives: 1. Stream Load (Push/Streamed): If you can "stream" the data from an application or a local script, you can use the

curl

-based Stream Load. This pushes data to StarRocks over HTTP. It is high-performance and supports JSON compression (GZIP, ZSTD, etc.). 2. Routine Load (Continuous): If your JSON data is coming from a message queue like Kafka, Routine Load is the best option for continuous, real-time ingestion. 3. Flink/Spark Connectors: If you have an existing data processing pipeline in Flink or Spark, their respective StarRocks connectors handle JSON ingestion very efficiently. If not JSON, what are the best alternatives? If you have the flexibility to change your file format, Parquet or ORC are significantly better choices for StarRocks: * Why? They are columnar formats that StarRocks can process much faster than row-based JSON. * Simplified Ingestion: Both formats are fully supported by the

FILES()

table function, allowing you to run a simple SQL command like:

sql
    INSERT INTO your_table
    SELECT * FROM FILES(
        "path" = "<s3://your-bucket/data.parquet>",
        "format" = "parquet",
        ...
    );

* Efficiency: Parquet/ORC provide better compression and allow StarRocks to perform "schema inference," meaning you don't have to manually define every column if the file matches your table. Summary Table Method | Supports JSON from S3? | Best Use Case Broker Load | Yes | Batch loading JSON/Parquet/CSV files directly from S3. INSERT + FILES() | No (Parquet/ORC/CSV/Avro only) | Simple, synchronous loading from S3 (easiest to use). Stream Load | Yes | Streaming JSON from local files or applications via HTTP. Pipe | No (Parquet/ORC only) | Continuous automated loading of large S3 file volumes. Routine Load | Yes | Real-time streaming from Kafka. References • loading/s3.md • loading/loading_introduction/feature-support-loading-and-unloading.md

@Rocky So Broker load will run inside StarRocks right? or it's new tool whoch I need to run?

Broker Load runs inside StarRocks. You do not need to install or run any external tools or separate services. Here is how it works in modern StarRocks (version 2.5 and later): 1. It is "Brokerless" Although it is called "Broker Load," StarRocks no longer requires an external "Broker" service to connect to S3. The Frontend (FE) and Backend (BE) nodes in your cluster handle everything: * FE (Frontend): Receives your SQL command and creates a plan. * BE (Backend): Connects directly to S3, pulls the JSON data, and writes it to the local disks. 2. You still use the

WITH BROKER

keyword Even though you aren't running an external tool, the SQL syntax still requires the keyword

WITH BROKER

(think of it as a legacy name for the S3 connector). You don't need to specify a broker name; you just provide your S3 credentials. Example Command for JSON:

sql
LOAD LABEL my_json_load
(
    DATA INFILE("<s3://my-bucket/data/*.json>")
    INTO TABLE my_table
    FORMAT AS "json"
)
WITH BROKER 
(
    "aws.s3.access_key" = "...",
    "aws.s3.secret_key" = "...",
    "aws.s3.region" = "us-east-1"
);

3. Why use this instead of a "Stream"? * Asynchronous: You run the command, and StarRocks handles it in the background. You don't have to keep a terminal or script running. * Scale: It uses all the BE nodes in your cluster to pull data in parallel, making it very fast for large JSON datasets. * Retries: StarRocks will automatically retry if there are transient network issues with S3. Summary: You don't need to run anything extra. Broker Load is a built-in feature that uses your existing StarRocks cluster resources to pull JSON files directly from S3. References • loading/BrokerLoad.md • sql-reference/sql-statements/loading_unloading/BROKER_LOAD.md • loading/Loading_data_template.md • loading/StreamLoad.md

@Rocky My StarRocks is on AWS EC2 so how can I do this with IAM based access. EC2 has access to s3 directlly

Since your StarRocks nodes are running on EC2 instances with an IAM role attached, you can use Instance Profile authentication. This is the most secure method because you don't need to hardcode any access keys in your SQL scripts. To use IAM-based access with Broker Load, follow these steps: 1. Requirements * IAM Role: Ensure the IAM role attached to your EC2 instances (both Frontend and Backend nodes) has

s3:GetObject

and

s3:ListBucket

permissions for your target bucket. * Protocol: For Broker Load, you must use the

s3a://

prefix instead of

s3://

. 2. SQL Syntax for JSON In your

LOAD LABEL

command, set

aws.s3.use_instance_profile

to

true

. You do not need to provide an access key or secret key.

sql
LOAD LABEL my_secure_json_load
(
    DATA INFILE("<s3a://your-bucket/path/to/files/*.json>")
    INTO TABLE your_table
    FORMAT AS "json"
)
WITH BROKER 
(
    "aws.s3.use_instance_profile" = "true",
    "aws.s3.region" = "us-east-1" -- Replace with your actual S3 region
)
PROPERTIES
(
    "timeout" = "3600"
);

Key Parameters: *

"aws.s3.use_instance_profile" = "true"

: Tells StarRocks to automatically retrieve temporary credentials from the EC2 metadata service. *

"aws.s3.region"

: The AWS region where your bucket resides (e.g.,

us-east-1

,

eu-west-1

). *

s3a://

: StarRocks Broker Load uses the S3A client, so this prefix is mandatory. Why this is better: * No Secret Management: You don't have to rotate keys or worry about them leaking in query logs. * Automatic Rotation: AWS handles the rotation of the temporary credentials for you. * Direct Access: Your StarRocks nodes communicate directly with S3 using the EC2 network backbone. References • sql-reference/sql-statements/loading_unloading/BROKER_LOAD.md • integrations/authenticate_to_aws_resources.md • loading/s3.md References • sql-reference/sql-statements/loading_unloading/BROKER_LOAD.md • loading/Loading_data_template.md • loading/BrokerLoad.md • loading/s3.md • integrations/authenticate_to_aws_resources.md

@Rocky same thing can be used for iceberg tables as well right? Instead of native table i can push data in iceberg table as well?

While you can use the same IAM-based access (Instance Profile) for both native tables and Iceberg tables, the methods for getting JSON data into them differ because of current format support. 1. Can you load JSON directly into Iceberg? Not directly from S3 JSON files. * Broker Load (which supports JSON files) is designed to load data into native StarRocks tables (internal OLAP storage). It cannot currently target an Iceberg catalog table as the destination. *

INSERT INTO ... SELECT FROM FILES()

is the standard way to write to Iceberg from S3, but the

FILES()

function currently supports Parquet, ORC, CSV, and Avro—it does not yet support the JSON file format. 2. Best Option to ingest JSON into Iceberg Since there is no "direct" JSON-to-Iceberg path from S3, the best workflow is a two-step process: 1. Stage to Native Table: Use Broker Load to pull the JSON from S3 into a temporary native StarRocks table. 2. Insert into Iceberg: Use a simple SQL

INSERT

to move the data from the native table to your Iceberg table. Step 1: Broker Load (JSON to Native Table) Using your EC2 IAM role:

sql
LOAD LABEL stage_json_to_native
(
    DATA INFILE("<s3a://your-bucket/data/*.json>")
    INTO TABLE staging_table
    FORMAT AS "json"
)
WITH BROKER 
(
    "aws.s3.use_instance_profile" = "true",
    "aws.s3.region" = "us-east-1"
);

Step 2: INSERT INTO (Native to Iceberg)

sql
INSERT INTO iceberg_catalog.your_db.iceberg_table
SELECT * FROM staging_table;

────────── 3. Alternative: Convert to Parquet/ORC If you have control over the data source, the most efficient option is to provide the data in Parquet format. If your S3 files are Parquet, you can skip the staging table entirely and use a single command with IAM access:

sql
INSERT INTO iceberg_catalog.your_db.iceberg_table
SELECT * FROM FILES(
    "path" = "<s3://your-bucket/data/*.parquet>",
    "format" = "parquet",
    "aws.s3.use_instance_profile" = "true",
    "aws.s3.region" = "us-east-1"
);

Summary of Options Destination | Format | Best Method Native Table | JSON | Broker Load (Direct, asynchronous) Iceberg Table | JSON | Broker Load to staging table, then INSERT INTO Iceberg Table | Parquet | INSERT INTO ... SELECT FROM FILES() (Direct, synchronous) Key Tip for IAM When configuring your Iceberg Catalog, ensure you also set

"aws.s3.use_instance_profile" = "true"

in the catalog properties so StarRocks can write the final Parquet files back to S3 using the EC2's IAM role. References • loading/loading_introduction/feature-support-loading-and-unloading.md • data_source/catalog/iceberg/iceberg_catalog.md • sql-reference/sql-functions/table-functions/files.md • data_source/catalog/iceberg/DML.md References • loading/loading_introduction/feature-support-loading-and-unloading.md • sql-reference/sql-functions/table-functions/files.md • using_starrocks/Flat_json.md • sql-reference/data-types/semi_structured/JSON.md • loading/Json_loading.md • loading/StreamLoad.md

@Rocky but in that case why can't i just cop data in parquet to parquet directly why do i need starrocks?

That is a fair question! If you were only moving Parquet files from one folder to another, you wouldn't need a compute engine like StarRocks. However, there are four main reasons why StarRocks is valuable for this specific "JSON to Iceberg" workflow: 1. JSON is not Parquet A simple file copy (like

aws s3 cp

) cannot convert JSON (text-based, nested, schema-less) into Parquet (binary, columnar, strictly typed). You need a compute engine to: * Parse the JSON strings. * Map JSON keys to table columns. * Handle data type conversions (e.g., converting a JSON string

"2023-01-01"

into a SQL

DATE

type). * Compress the data into the highly optimized Parquet format that Iceberg uses. 2. Iceberg is a "Table," not just a "Folder of Files" If you just drop Parquet files into an S3 bucket, an Iceberg-compatible engine (like Athena, Spark, or StarRocks) will not see that data. * Metadata: Iceberg requires a "Snapshot" and "Manifest" files to be updated every time data is added. * Transactionality: StarRocks handles the Iceberg commit process, ensuring that the new data is visible to other readers only after the write is successful and consistent. 3. SQL-Based ETL (No Spark/Coding required) If you don't use StarRocks, you would typically need to write a Python/Spark or Flink job to do this conversion. With StarRocks, you use standard SQL: * You can filter data during the load (e.g.,

WHERE status = 'active'

). * You can transform data (e.g.,

SELECT lower(user_name), price * 1.1

). * You can join the incoming JSON data with other existing tables in StarRocks before writing the final result to Iceberg. 4. High Performance StarRocks’ Broker Load is a distributed C++ engine. It can pull JSON from S3 and parse it in parallel across all your EC2 nodes. It is often significantly faster and uses fewer resources than a comparable Spark job for simple ingestion tasks. Summary If your data were already Parquet, you could potentially use simpler tools, but because your source is JSON, you need an engine to do the heavy lifting of parsing and schema enforcement. Using StarRocks allows you to do this using the same SQL and infrastructure you are already using for your analytics. References • loading/BrokerLoad.md • data_source/catalog/iceberg/iceberg_catalog.md • loading/Json_loading.md • data_source/catalog/iceberg/DML.md • loading/s3.md