@Rocky Does it matter much in my wide primary key table if I use INT or BOOL to represent a true false condition?

In a StarRocks Primary Key table, it matters significantly. You should use

BOOL

(or

BOOLEAN

) instead of

INT

. Here is why: 1. Memory and Storage Efficiency In a Primary Key table, StarRocks maintains a Primary Key Index to map key values to row locations. This index is either kept in memory or on disk (persistent index), and its size depends directly on the byte length of your primary key columns. *

BOOL

is equivalent to

TINYINT

and occupies 1 byte. *

INT

occupies 4 bytes. Using

BOOL

saves 3 bytes per row. While 3 bytes sounds small, it scales linearly: * For 100 million rows (with 3 replicas and index overhead), switching from

INT

to

BOOL

can save roughly ~1.3 GB of RAM if using an in-memory index. * Persistent Index: Even if the index is on disk, a smaller key reduces I/O overhead and storage consumption. 2. Primary Key Size Limits StarRocks has a default limit for the total size of a primary key (usually 128 bytes, controlled by

primary_key_limit_size

). In a "wide" primary key table (where the PK is composed of many columns), every byte counts toward this limit. Using

BOOL

helps you stay within this limit or prevents the need to increase it, which would further increase resource consumption. 3. Query Performance While the primary key itself doesn't typically accelerate general queries (that's the job of the Sort Key), a smaller primary key makes index lookups faster and reduces the memory pressure on the Backend (BE) nodes during data ingestion and updates. Summary * Use

BOOL

for true/false conditions. It is functionally identical to using

INT

(0 or 1) but 75% more space-efficient in the context of the Primary Key index. * Best Practice: Always choose the smallest possible data type for your Primary Key columns (e.g., use

INT

instead of

BIGINT

if the range allows) to minimize memory and I/O overhead. References • best_practices/primarykey_table.md • table_design/table_types/primary_key_table.md • best_practices/query_tuning/schema_tuning.md • best_practices/table_clustering.md

@Rocky INSERT INTO FILES( "path" = "s3://mybucket/unload/data1", "format" = "parquet", "compression" = "uncompressed", "target_max_file_size" = "1024", -- 1KB "aws.s3.access_key" = "xxxxxxxxxx", "aws.s3.secret_key" = "yyyyyyyyyy", "aws.s3.region" = "us-west-2" ) SELECT * FROM sales_records; For this example, can i use use_instance_profile: true?

Yes, you can use

"aws.s3.use_instance_profile" = "true"

in your

INSERT INTO FILES

statement. This allows StarRocks to use the IAM role attached to your FE and BE instances instead of static credentials. When you enable the instance profile, you should remove the

aws.s3.access_key

and

aws.s3.secret_key

parameters. You must still provide the

aws.s3.region

. Modified Example

sql
INSERT INTO FILES(
    "path" = "<s3://mybucket/unload/data1>",
    "format" = "parquet",
    "compression" = "uncompressed",
    "target_max_file_size" = "1024",
    "aws.s3.use_instance_profile" = "true",
    "aws.s3.region" = "us-west-2"
)
SELECT * FROM sales_records;

Key Requirements 1. Version:

INSERT INTO FILES

(unloading via table function) was introduced in v3.2.0. Ensure your cluster is at this version or higher. 2. Permissions: The IAM role attached to your EC2 instances must have

s3:PutObject

,

s3:GetObject

,

s3:ListBucket

, and

s3:DeleteObject

(if overwriting) permissions on the target bucket/path. 3. Role ARN (Optional): If you need to assume a specific role rather than just using the base instance profile, you can also add

"aws.s3.iam_role_arn" = "arn:aws:iam::123456789012:role/MyUnloadRole"

. References • sql-reference/sql-functions/table-functions/files.md • sql-reference/sql-statements/loading_unloading/INSERT.md References • sql-reference/sql-functions/table-functions/files.md • sql-reference/sql-statements/loading_unloading/INSERT.md • administration/management/FE_configuration.md

@Rocky What types of compression are available for unload process mentioned previously?

When using the

INSERT INTO FILES

process to unload data, the available compression types depend on the file format you choose: Supported Compression Types (Parquet and ORC) For Parquet (v3.2+) and ORC (v3.3+) formats, the following values are supported for the

"compression"

property: *

uncompressed

: No compression. *

snappy

: High performance with moderate compression (often the default for big data). *

lz4

: Optimized for compression speed. *

zstd

: Balanced high compression ratio and speed. *

gzip

: High compression ratio, but more CPU intensive. CSV Format * No compression support: Currently, unloading into CSV files (v3.3+) does not support data compression. You must set the property to

"compression" = "uncompressed"

when using CSV. Important Note The

"compression"

property is required in the

FILES()

table function when performing an unload, even if you are setting it to

"uncompressed"

. References • sql-reference/sql-functions/table-functions/files.md • loading/loading_introduction/feature-support-loading-and-unloading.md • unloading/unload_using_insert_into_files.md References • loading/loading_introduction/feature-support-loading-and-unloading.md • unloading/unload_using_insert_into_files.md • sql-reference/sql-functions/table-functions/files.md