@Rocky im using StarRocksSinkConnector but having problem with NULL value coming from a topic and the stream is to a starrocks primary key table with column that is DATETIME NOT NULL DEFAULT "1970-01-01 000000",

The problem occurs because StarRocks (and most SQL engines) treats an explicit

NULL

value differently from a "missing" field. When a column is defined as

NOT NULL DEFAULT ...

, the default value is only triggered if the column is omitted from the input. If the Kafka message contains an explicit

null

for that field, the connector sends

{"column_name": null}

to StarRocks, which violates the

NOT NULL

constraint and causes the load to fail. To resolve this, you can use one of the following approaches: 1. Use StarRocks Transformation (

sink.properties.columns

) You can handle the

NULL

during the loading process by mapping the incoming field to a temporary variable and using the

ifnull()

function to apply your default value. Add the following to your connector configuration:

properties
# Example: if your DATETIME column is 'event_time'
# You must list all columns in the order they appear in the source or specify mapping
sink.properties.columns = col1, col2, event_time_tmp, event_time = ifnull(event_time_tmp, '1970-01-01 00:00:00')

Note: This requires you to explicitly list the columns. If you are using JSON format, you may also need to define

sink.properties.jsonpaths

to match the Kafka message fields to these column names. 2. Use Kafka Connect Transformations (SMT) You can use a Single Message Transformation (SMT) to either remove the null fields or replace them before they reach the StarRocks connector. * Option A: Replace nulls. Use the

ReplaceField

transformation if you want to replace the value with a specific string. * Option B: Remove null fields. If you can configure your Kafka value converter (e.g.,

JsonConverter

) to omit null fields entirely, StarRocks will then see the field as "missing" and correctly apply the table's

DEFAULT

value. * For the standard

JsonConverter

, you can try setting:

value.converter.schemas.enable = false

value.converter = org.apache.kafka.connect.json.JsonConverter

* However, the

JsonConverter

usually includes nulls. You might need a custom SMT or a transformation like

org.apache.kafka.connect.transforms.Filter

(to drop records) or a community SMT like

Cast

to change the value. 3. Change Table Schema (If applicable) If you cannot easily transform the data, consider if the column truly needs to be

NOT NULL

. If you change it to

NULL

, the data will load successfully, and you can handle the "1970-01-01" logic at query time using

COALESCE(col, '1970-01-01 00:00:00')

. Summary of why

DEFAULT

isn't working: * Missing Field in JSON:

DEFAULT

is applied. *

{"col": null}

in JSON: StarRocks attempts to insert

NULL

, fails because of

NOT NULL

. *

{"col": ""}

in JSON (for DATETIME): Usually results in an error or

NULL

depending on strict mode. Recommendation: The most robust way within the connector is Approach 1 using

sink.properties.columns

with

ifnull()

. References • loading/Kafka-connector-starrocks.md

@Rocky this is the actual error of the connector Error: NULL value in non-nullable column 'utc_created'.

This error happens because the Kafka message contains an explicit

"utc_created": null

value. Even though your table has a

DEFAULT

value, StarRocks only applies that default when the field is entirely missing from the input. If a

null

is explicitly provided for a

NOT NULL

column, the load fails. To fix this in the

StarRocksSinkConnector

, you must use a transformation to catch the

null

and replace it with your desired default value using the

ifnull()

function. Option 1: Full Update (Standard) If you are performing a standard load, you need to map the JSON field to a temporary variable and then assign the value to the column using

ifnull

. Add these to your connector configuration (replacing

col1, col2

with your actual other columns):

properties
# 1. Define the mapping. Map the incoming JSON field to 'utc_created_tmp'
# then calculate 'utc_created' using ifnull.
sink.properties.columns = col1, col2, utc_created_tmp, utc_created = ifnull(utc_created_tmp, '1970-01-01 00:00:00')

# 2. Map the JSON keys to the columns/variables defined above
# Note: the order must match the order in sink.properties.columns
sink.properties.jsonpaths = ["$.col1", "$.col2", "$.utc_created"]

Option 2: Partial Update (If applicable) If you only want to update specific columns and your table is a Primary Key table, you can enable partial updates. This allows you to only specify the columns you care about.

properties
sink.properties.partial_update = true
# List only the PK and the columns you are updating
sink.properties.columns = id, utc_created_tmp, utc_created = ifnull(utc_created_tmp, '1970-01-01 00:00:00')
sink.properties.jsonpaths = ["$.id", "$.utc_created"]

Why this is necessary: * StarRocks Logic: A

NULL

in the source data is treated as an instruction to insert

NULL

. Since the column is

NOT NULL

, it triggers the error you see. * Connector Logic: The Kafka connector simply passes the JSON as-is to the StarRocks Stream Load API. By using

sink.properties.columns

, you are instructing the StarRocks backend to perform the

ifnull

logic during the ingestion process. Important Note: When you use

sink.properties.columns

with JSON data, you must also provide

sink.properties.jsonpaths

so StarRocks knows which JSON key maps to which column or temporary variable. References • loading/Spark-connector-starrocks.md • loading/Kafka-connector-starrocks.md • loading/StreamLoad.md • loading/Flink-connector-starrocks.md • sql-reference/sql-statements/loading_unloading/routine_load/CREATE_ROUTINE_LOAD.md

@Rocky Should I prefer setting default roles at the user level or simply relying on

SET GLOBAL activate_all_roles_on_login = TRUE;

. This needs to work with MySQL session, HTTP SQL query API and HTTP Stream Load features.

Whether you should use

SET DEFAULT ROLE

or the global

activate_all_roles_on_login = TRUE

depends on your security requirements (Least Privilege vs. Convenience), but both are compatible with MySQL sessions, the HTTP SQL Query API, and HTTP Stream Load. Here is the breakdown of how they behave across your requested features: 1.

SET GLOBAL activate_all_roles_on_login = TRUE

* Behavior: When a user authenticates, StarRocks automatically activates every role assigned to that user. * Best For: Development environments or scenarios where you want to simplify permissions and don't want to manage default roles individually for every user. * Compatibility: * MySQL Session: Works immediately upon login. * HTTP SQL API: Works. Since this API uses Basic Auth for every request, the "login" happens at the start of the request, and all roles are activated before the SQL is executed. * HTTP Stream Load: Works. Stream Load uses Basic Auth; the global setting ensures the user has the required

INSERT

or

LOAD

permissions from any of their assigned roles. 2.

SET DEFAULT ROLE <role_name> TO <user>

* Behavior: Only the specific roles you define are active upon login. Other assigned roles must be manually activated using

SET ROLE

(which is impossible in a Stream Load). * Best For: Production environments and the Principle of Least Privilege. For example, you might give a user a "Read" role by default and require them to explicitly

SET ROLE 'admin'

for dangerous tasks. * Compatibility: * MySQL Session: Works. * HTTP SQL API: Works, but the user is restricted to the permissions in the default roles only. If they need a non-default role, they must include

SET ROLE

in their SQL or set it via

sessionVariables

in the JSON body (if supported by your version). * HTTP Stream Load: Critical. Since you cannot send a

SET ROLE

command during a Stream Load (it's a single PUT/POST request), the user must have the necessary load permissions in their Default Role. Summary Comparison Feature |

activate_all_roles_on_login = TRUE

|

SET DEFAULT ROLE

Security | Lower (All permissions always "on") | Higher (Least privilege) Maintenance | Set once globally | Set per user/role change Stream Load | Easiest (Any assigned role works) | Requires mapping the "Load" role to default SQL API | Any assigned role works | Only default roles work Recommendation * If you prioritize security: Use

SET DEFAULT ROLE

. This ensures that even if a user is granted powerful roles (like

db_admin

), those roles aren't active by default unless specifically needed. For your Stream Load to work, just ensure the role with

INSERT

privileges is included in the user's default role list:

sql
    SET DEFAULT ROLE ALL TO <user>; -- Or specific roles

* If you prioritize simplicity: Use

SET GLOBAL activate_all_roles_on_login = TRUE;

. This removes the overhead of managing default roles and ensures that any role granted to a user is usable across all HTTP and MySQL interfaces immediately. Note:

activate_all_roles_on_login

takes precedence over

SET DEFAULT ROLE

if enabled. Both features were introduced in v3.0. References • administration/http_interface/query_detail.md • sql-reference/http_sql_api.md • developers/code-style-guides/restful-api-standard.md • administration/http_interface.md