http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> does starrock support security on c...
# questions-and-troubleshooting
l
@Rocky does starrock support security on colume like oracle
r
StarRocks supports column-level security and data masking, but it is primarily implemented through Apache Ranger rather than as a native internal command (unlike Oracle's native Data Redaction or VPD). Here is the breakdown of how it works in StarRocks: 1. External Integration (Apache Ranger) Starting from version 3.1.9 (and enhanced in 3.2.9+), StarRocks supports fine-grained access control via Apache Ranger. Through this integration, you can define: * Column-level Permissions: Restrict specific users or roles from accessing certain columns. * Column Masking: Automatically obfuscate sensitive data (e.g., showing only the last four digits of a credit card number) based on user roles. * Row-level Filtering: Limit the rows a user can see based on specific conditions. 2. Native Support Limitations In the Open-source Edition, native column-level and row-level access controls (using standard 
GRANT
or 
REVOKE
on individual columns) are not supported. * Standard StarRocks privileges currently operate at the Global, Database, and Table levels. * While views like 
column_privileges
exist in the 
information_schema
, the documentation notes that these views do not currently apply to internal native features. 3. Comparison to Oracle Feature | Oracle | StarRocks Column-level Access | Native (
GRANT SELECT(col)
) | Via Apache Ranger Plugin Data Masking | Data Redaction (Native) | Via Apache Ranger Row-level Security | VPD / Label Security | Via Apache Ranger Recommendation If you require column-level security similar to Oracle, you should: 1. Use StarRocks version 3.2 or later. 2. Deploy Apache Ranger and configure the StarRocks Ranger Plug-in. 3. Manage your column policies and masking rules within the Ranger Admin UI. For more details on setting this up, you can refer to the Manage permissions with Apache Ranger documentation. References • sql-reference/information_schema/column_privileges.md • faq/operation_maintenance_faq.md • sql-reference/sys/policy_references.md • table_design/table_design.md • best_practices/authentication_authorization.md • administration/user_privs/authorization/User_privilege.md • administration/user_privs/authorization/ranger_plugin.md
l
@Rocky ALTER TABLE dmapp.dtm_crm_lead MODIFY COLUMN LOAN_AMOUNT_PROPOSED decimal(28, 5) NULL; correct this sql
r
In StarRocks, you cannot directly change the precision or scale of a 
DECIMAL
column using the 
ALTER TABLE ... MODIFY COLUMN
statement. StarRocks currently only supports a limited set of type conversions via 
MODIFY COLUMN
, such as converting integers to larger integers/doubles or converting types to 
VARCHAR
. If you attempt to run your SQL, you will likely receive an error stating that the type conversion is not supported. Correct Way to Modify Decimal Precision To achieve this, you must follow a multi-step process: add a new column, migrate the data, and then swap the columns. Option 1: Using Column Rename (Recommended for v3.3.2+) If you are on version 3.3.2 or later, you can use the 
RENAME COLUMN
feature: 1. Add a new column with the desired precision:
Copy code
sql
    ALTER TABLE dmapp.dtm_crm_lead
    ADD COLUMN LOAN_AMOUNT_PROPOSED_NEW decimal(28, 5) NULL;
2. Migrate the data: * For Primary Key tables: 
UPDATE dmapp.dtm_crm_lead SET LOAN_AMOUNT_PROPOSED_NEW = LOAN_AMOUNT_PROPOSED;
* For other table types (Duplicate/Aggregate): You must re-insert or reload the data into the table. 3. Drop the old column:
Copy code
sql
    ALTER TABLE dmapp.dtm_crm_lead DROP COLUMN LOAN_AMOUNT_PROPOSED;
4. Rename the new column:
Copy code
sql
    ALTER TABLE dmapp.dtm_crm_lead RENAME COLUMN LOAN_AMOUNT_PROPOSED_NEW TO LOAN_AMOUNT_PROPOSED;
Option 2: Syntax Check (If you were converting to a supported type) If you were converting to a supported type (like 
VARCHAR
), the syntax requires specific keywords depending on your table's model: * Duplicate Key Table (Key Column): You must include the 
KEY
keyword.
Copy code
sql
    ALTER TABLE dmapp.dtm_crm_lead MODIFY COLUMN LOAN_AMOUNT_PROPOSED VARCHAR(50) KEY NULL;
* Aggregate Table: You must include the aggregation type (e.g., 
SUM
, 
REPLACE
).
Copy code
sql
    ALTER TABLE dmapp.dtm_crm_lead MODIFY COLUMN LOAN_AMOUNT_PROPOSED decimal(28, 5) REPLACE NULL;
Important Notes * Nullability: StarRocks does not support converting a 
NULL
column to 
NOT NULL
. * Asynchronous: Schema changes in StarRocks are asynchronous. You can check the progress of the change using:
Copy code
sql
    SHOW ALTER TABLE COLUMN FROM dmapp;
References • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • sql-reference/data-types/numeric/DECIMAL.md • sql-reference/sql-statements/table_bucket_part_index/SHOW_ALTER_TABLE.md • table_design/table_design.md • sql-reference/data-types/Data_type.md References • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • sql-reference/data-types/numeric/DECIMAL.md • sql-reference/System_variable.md
l
@Rocky CREATE TABLE LOSREP.APPLICATION_DATA_HIS ( ID NUMBER(20,0), APP_CODE VARCHAR2(50), STEP_CODE VARCHAR2(50), API_URL VARCHAR2(250), API_METHOD VARCHAR2(50), CREATED_BY VARCHAR2(50), CREATED_DATE DATE, APP_DATA CLOB, CONSTRAINT PK_APPLICATION_DATA_HIS PRIMARY KEY (ID), CONSTRAINT SYS_C00249985 CHECK ("ID" IS NOT NULL) ); CREATE INDEX APPLICATION_DATA_HIS_APP_CODE_IDX ON LOSREP.APPLICATION_DATA_HIS (APP_CODE); CREATE INDEX APPLICATION_DATA_HIS_CREATED_DATE_IDX ON LOSREP.APPLICATION_DATA_HIS (CREATED_DATE); CREATE INDEX APPLICATION_DATA_HIS_STEP_CODE_IDX ON LOSREP.APPLICATION_DATA_HIS (STEP_CODE); CREATE UNIQUE INDEX PK_APPLICATION_DATA_HIS ON LOSREP.APPLICATION_DATA_HIS (ID); create starrocks primary key table
r
To convert your Oracle 
CREATE TABLE
script to a StarRocks Primary Key table, you need to adjust the data types and include the required 
PRIMARY KEY
and 
DISTRIBUTED BY
clauses. Here is the recommended StarRocks SQL:
Copy code
sql
CREATE TABLE LOSREP.APPLICATION_DATA_HIS (
    ID BIGINT NOT NULL,
    APP_CODE VARCHAR(50) NOT NULL,
    STEP_CODE VARCHAR(50) NOT NULL,
    API_URL VARCHAR(250) NULL,
    API_METHOD VARCHAR(50) NULL,
    CREATED_BY VARCHAR(50) NULL,
    CREATED_DATE DATETIME NULL,
    APP_DATA STRING NULL -- Equivalent to CLOB (supports up to 1MB)
) ENGINE=OLAP
PRIMARY KEY (ID)
DISTRIBUTED BY HASH(ID) BUCKETS 16; -- Adjust bucket count based on data volume
Key Mapping Details: 1. 
NUMBER(20,0)
→ 
BIGINT
: Oracle's 
NUMBER(20,0)
typically maps to 
BIGINT
. If you expect values larger than $2^{63}-1$, use 
DECIMAL(20,0)
or 
LARGEINT
. 2. 
VARCHAR2
→ 
VARCHAR
: StarRocks uses standard 
VARCHAR
. 3. 
DATE
→ 
DATETIME
: In Oracle, 
DATE
includes time. In StarRocks, 
DATE
is only 
YYYY-MM-DD
. Use 
DATETIME
to preserve the time component. 4. 
CLOB
→ 
STRING
: The 
STRING
type in StarRocks supports up to 1MB of data (or use 
JSON
if the 
APP_DATA
is specifically JSON formatted). 5. Indices: * Primary Index: In StarRocks Primary Key tables, the 
PRIMARY KEY (ID)
automatically creates the index for fast point lookups on 
ID
. You do not need to create a separate unique index. * Secondary Indices: StarRocks does not use traditional B-Tree indices like Oracle. For the other indexed columns (
APP_CODE
, 
STEP_CODE
), StarRocks automatically uses its storage engine to speed up queries. If performance is slow on those columns, you can later add a Bitmap Index (for low cardinality) or an Inverted Index (for high cardinality/text search):
Copy code
sql
        -- Optional: Example of adding a Bitmap Index for STEP_CODE
        ALTER TABLE LOSREP.APPLICATION_DATA_HIS ADD INDEX index_step_code (STEP_CODE) USING BITMAP;
Important Constraints * Nullability: Columns in the Primary Key cannot be 
NULL
. * Distribution: Every StarRocks table must have a 
DISTRIBUTED BY HASH
clause. Using the Primary Key (
ID
) as the distribution key is the standard best practice for point lookups and updates. References • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • table_design/table_models/primary_key_table.md • sql-reference/data-types/Data_type.md • table_design/table_design.md • sql-reference/data-types/numeric/BIGINT.md
l
@Rocky the 
APP_DATA
is specifically JSON formatted
r
Since the 
APP_DATA
column contains JSON, you should use StarRocks' native 
JSON
 data type. This is much more efficient than 
STRING
or 
VARCHAR
because StarRocks stores it in a binary format (JSONB), allowing for faster parsing and direct querying of internal fields. Here is the updated StarRocks SQL:
Copy code
sql
CREATE TABLE LOSREP.APPLICATION_DATA_HIS (
    ID BIGINT NOT NULL,
    APP_CODE VARCHAR(50) NOT NULL,
    STEP_CODE VARCHAR(50) NOT NULL,
    API_URL VARCHAR(250) NULL,
    API_METHOD VARCHAR(50) NULL,
    CREATED_BY VARCHAR(50) NULL,
    CREATED_DATE DATETIME NULL,
    APP_DATA JSON NULL, -- Uses the native JSON type
    INDEX idx_app_code (APP_CODE) USING BITMAP, -- Optional: helps with filtering
    INDEX idx_step_code (STEP_CODE) USING BITMAP -- Optional: helps with filtering
) ENGINE=OLAP
PRIMARY KEY (ID)
DISTRIBUTED BY HASH(ID) BUCKETS 16;
Why use the 
JSON
type? 1. Performance: It avoids the overhead of parsing text every time you read a field. 2. Simplified Queries: You can extract values using the 
->
or 
->>
operators instead of complex string functions. _ _Example:* 
SELECT APP_DATA->'user_id' FROM LOSREP.APPLICATION_DATA_HIS;
3. Automatic Optimization: StarRocks can automatically optimize JSON data through features like Generated Columns, which can index specific keys inside the JSON for even faster performance. Tips for JSON in StarRocks * Max Size: The 
JSON
type supports documents up to 16MB per row. * Partial Updates: Since this is a Primary Key table, you can perform partial updates to specific columns (though currently, you must update the entire JSON blob at once; you cannot yet update a single key inside the JSON via a partial update SQL). References • sql-reference/data-types/other-types/JSON.md • table_design/table_models/primary_key_table.md • using_starrocks/JSON_data.md • sql-reference/sql-functions/json-functions/get_json_string.md
l
@Rocky query mainly base on APP_CODE
r
If your queries are primarily filtered by 
APP_CODE
, you should optimize the table design to enable Tablet Pruning and Short-Key Indexing. In StarRocks, the fastest way to query a column is to make it either the Distribution Key or the Sort Key. Option 1: Maximum Performance (Recommended) This design uses 
APP_CODE
as the distribution key. This allows StarRocks to scan only the specific bucket (tablet) containing that code, rather than searching the entire table. Note: In a Primary Key table, the distribution column must be part of the Primary Key. Therefore, we define a composite Primary Key 
(ID, APP_CODE)
.
Copy code
sql
CREATE TABLE LOSREP.APPLICATION_DATA_HIS (
    ID BIGINT NOT NULL,
    APP_CODE VARCHAR(50) NOT NULL,
    STEP_CODE VARCHAR(50) NOT NULL,
    API_URL VARCHAR(250) NULL,
    API_METHOD VARCHAR(50) NULL,
    CREATED_BY VARCHAR(50) NULL,
    CREATED_DATE DATETIME NULL,
    APP_DATA JSON NULL,
    INDEX idx_app_code (APP_CODE) USING BITMAP -- Fast filtering for APP_CODE
) ENGINE=OLAP
PRIMARY KEY (ID, APP_CODE)            -- APP_CODE must be in PK to be a distribution key
PARTITION BY (CREATED_DATE)            -- Optional: useful if you also filter by time
DISTRIBUTED BY HASH(APP_CODE) BUCKETS 16
ORDER BY (APP_CODE, ID);               -- Decouples sort order from PK (v3.0+)
Option 2: If 
ID
must be the only Unique Column If you cannot include 
APP_CODE
in the Primary Key because 
ID
is the only column that guarantees uniqueness, use this design. It keeps 
ID
as the sole primary key but uses a Bitmap Index and a custom Sort Key to accelerate 
APP_CODE
filters.
Copy code
sql
CREATE TABLE LOSREP.APPLICATION_DATA_HIS (
    ID BIGINT NOT NULL,
    APP_CODE VARCHAR(50) NOT NULL,
    ...
    APP_DATA JSON NULL,
    INDEX idx_app_code (APP_CODE) USING BITMAP -- Essential for filtering non-distribution keys
) ENGINE=OLAP
PRIMARY KEY (ID)
DISTRIBUTED BY HASH(ID) BUCKETS 16
ORDER BY (APP_CODE); -- Sorts data by APP_CODE on disk for fast range/point scans
Key Optimizations Explained: 1. 
DISTRIBUTED BY HASH(APP_CODE)
: This is the most powerful optimization. It ensures that all rows with the same 
APP_CODE
are stored together. Queries like 
WHERE APP_CODE = 'XYZ'
will only hit 1 out of 16 buckets. 2. 
ORDER BY (APP_CODE)
: By default, Primary Key tables sort data by the PK. Using a custom 
ORDER BY
clause (available in v3.0+) allows you to sort the data on disk by 
APP_CODE
, enabling StarRocks to use its Short-Key Index for sub-second filtering. 3. 
BITMAP INDEX
: This is highly effective for 
VARCHAR
columns. It creates a compact index that allows the engine to quickly skip rows that don't match your 
APP_CODE
filter. Which one to choose? * Choose Option 1 if the combination of 
(ID, APP_CODE)
is unique (which is usually true if 
ID
is unique) and you want the absolute fastest query performance for 
APP_CODE
. * Choose Option 2 if you want to keep your table schema simple and only rely on 
ID
for deduplication`. References • table_design/table_types/primary_key_table.md • table_design/data_distribution/Data_distribution.md • table_design/indexes/Bitmap_index.md • best_practices/primarykey_table.md References • table_design/table_types/primary_key_table.md • table_design/data_distribution/Data_distribution.md
l
@Rocky "Message": "The size of this batch exceed the max size [104857600] of json type data data [ 144216904 ]. Set ignore_json_size to skip the check, although it may lead huge memory consuming.",
r
This error occurs because StarRocks has a default safety limit of 100 MB for the total size of a JSON body in a single loading request (typically via Stream Load). Solution: Add 
ignore_json_size: true
 To bypass this limit, you need to add a specific header to your 
curl
command or your ingestion client's configuration:
Copy code
bash
curl --location-trusted -u <username>:<password> \
    -H "format: json" \
    -H "ignore_json_size: true" \
    -H "strip_outer_array: true" \
    -T your_large_file.json \
    http://<fe_host>:<fe_http_port>/api/<db>/<table_name>/_stream_load
Why does this limit exist? * Memory Usage: When StarRocks parses a JSON batch, it expands the data in memory. A very large JSON file (e.g., several hundred MBs) can consume several GBs of RAM during the parsing phase, potentially leading to Out-of-Memory (OOM) errors on the Backend (BE) nodes. * Performance: Loading massive single batches is generally less efficient than splitting data into smaller chunks (e.g., 50MB–100MB per batch). Other JSON Limits to keep in mind: 1. Individual Column Limit: While the batch can be large, a single 
JSON
column value within a row cannot exceed 16 MB. If your 
APP_DATA
field for a single row is larger than 16 MB, the load will fail even with 
ignore_json_size: true
. 2. BE Configuration: You can also increase the global default by modifying the BE configuration 
streaming_load_max_batch_size_mb
(though the header method is preferred as it is per-job). Recommendation: If your file is significantly larger than 100 MB, consider splitting it into smaller files or using a more memory-efficient format like Parquet or CSV for ingestion. References • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • sql-reference/data-types/semi_structured/JSON.md • loading/Json_loading.md • administration/management/BE_configuration.md References • sql-reference/data-types/semi_structured/JSON.md • administration/management/BE_configuration.md • sql-reference/sql-statements/loading_unloading/BROKER_LOAD.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • using_starrocks/Flat_json.md • loading/Json_loading.md
2 Views
Open in Slack
PreviousNext
Powered by