http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> What header do I use to map some co...
# questions-and-troubleshooting
m
@Rocky What header do I use to map some columns in my payload to actual columns in the starrocks primary key table for stream load? I have some fields that have camel case json marshal annotation and I need to map them to snake case
r
To map camelCase JSON fields to snake_case table columns in a StarRocks Stream Load, you need to use two headers together: 
jsonpaths
 and 
columns
. How it works: 1. 
jsonpaths
: Extracts the specific fields from your JSON payload. 2. 
columns
: Assigns names to the values extracted by 
jsonpaths
in the same order. These names should match your table's column names. Example: If your JSON payload looks like this:
Copy code
json
{"userName": "Rocky", "userId": 101}
And your StarRocks table has columns 
user_name
and 
user_id
, your 
curl
command should include these headers:
Copy code
bash
curl --location-trusted -u <user>:<password> \
    -H "format: json" \
    -H "jsonpaths: [\"$.userName\", \"$.userId\"]" \
    -H "columns: user_name, user_id" \
    -T data.json \
    http://<fe_host>:<fe_http_port>/api/<db>/<table>/_stream_load
Key Rules: * Order matters: The first element in 
jsonpaths
maps to the first name listed in 
columns
. * Mapping to Table: The names you provide in the 
columns
header are what StarRocks uses to find the matching column in your table. * Transformations: You can also perform transformations in the 
columns
header if needed (e.g., 
-H "columns: user_name, temp_id, user_id = temp_id + 100"
). If your JSON keys already match your table column names exactly, you don't need these headers at all; StarRocks will map them automatically in "simple mode." However, since you are converting camelCase to snake_case, the explicit 
jsonpaths
+ 
columns
mapping is the standard. References • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • loading/StreamLoad.md • sql-reference/sql-statements/loading_unloading/BROKER_LOAD.md • loading/Json_loading.md
m
@Rocky But for record insertion, I don't have to specify the column header. If I want to enable json to column mapping, I need to specify both jsonpaths and columns header?
r
Yes, if you want to map camelCase JSON keys to snake_case table columns, you need to specify both the 
jsonpaths
and 
columns
headers. In StarRocks Stream Load, there are two modes for JSON: 1. Simple Mode (Implicit): Used when JSON keys match table column names exactly. You don't need 
jsonpaths
or 
columns
. 2. Matched Mode (Explicit): Used when names differ or you need transformations. This requires both headers. Why both are required: * 
jsonpaths
: Extracts values from the JSON payload. It defines the order of the data being pulled out (e.g., 
["$.userName", "$.userId"]
). * 
columns
: Assigns names to those extracted values in the same order and maps them to your table columns (e.g., 
user_name, user_id
). The Mapping Flow: 
JSON Key (userName)
$\rightarrow$ 
jsonpaths ($.userName)
$\rightarrow$ 
columns (user_name)
$\rightarrow$ 
Table Column (user_name)
Example Header Configuration:
Copy code
bash
-H "format: json" \
-H "jsonpaths: [\"$.userName\", \"$.userId\"]" \
-H "columns: user_name, user_id"
Pro Tip: If you have many columns and only want to change a few, or if you want to skip some, you can use temporary variables in the 
columns
header:
Copy code
bash
-H "columns: user_name, tmp_id, user_id = tmp_id"
In this case, 
tmp_id
is a placeholder for the second value extracted by 
jsonpaths
, which is then assigned to the actual table column 
user_id
. References • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • loading/Json_loading.md • loading/StreamLoad.md References • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • faq/loading/Stream_load_faq.md • sql-reference/sql-statements/loading_unloading/BROKER_LOAD.md
m
@Rocky I set my column values to have defaults. How come during stream load, if the field corresponding to a column is omitted (not partial update, just insertion), it is set to null?
r
This behavior occurs because of how StarRocks handles the mapping between JSON paths and table columns. When you use the 
jsonpaths
and 
columns
headers, the process works as follows: 1. Extraction: StarRocks uses the 
jsonpaths
to find a value in your JSON. If the key is missing (omitted), the extractor returns 
NULL
. 2. Assignment: That 
NULL
value is then assigned to the column name you specified in the 
columns
header. 3. Insertion: Because the column is explicitly included in the load's "target list," StarRocks attempts to insert the provided value (
NULL
). The table's 
DEFAULT
value is only triggered if the column is completely omitted from the 
columns
header (meaning it's not part of the load instruction at all). How to use Default Values for missing fields To achieve your goal of mapping camelCase to snake_case while still respecting a default value, you have two options: Option 1: Use 
ifnull()
in the 
columns
header (Recommended) You can manually specify the default value in the transformation logic. This is the standard way to handle missing JSON fields.
Copy code
bash
curl --location-trusted -u <user>:<password> \
    -H "format: json" \
    -H "jsonpaths: [\"$.userName\"]" \
    -H "columns: tmp_user_name, user_name = ifnull(tmp_user_name, 'default_name')" \
    -T data.json \
    http://<fe_host>:<fe_http_port>/api/<db>/<table>/_stream_load
Option 2: Omit the column from the mapping If you want the table's schema-defined 
DEFAULT
to be used automatically, you must remove the column from both 
jsonpaths
and 
columns
. However, this means StarRocks will ignore the field even if it is present in the JSON. This is usually only helpful if you want that column to always be the default for that specific load. Important Note for Primary Key Tables If you are performing a partial update (using 
-H "partial_update: true"
), columns omitted from the 
columns
header will retain their existing values in the table rather than being set to 
NULL
or the 
DEFAULT
. If you are doing a standard insertion (upsert) and a field is missing, you must use Option 1 above to prevent 
NULL
from overwriting your intended default. References • loading/StreamLoad.md • loading/Json_loading.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • faq/loading/Stream_load_faq.md References • loading/StreamLoad.md • loading/Json_loading.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md
m
@Rocky I'm not using jsonpaths. I just provide the JSON payload with field names corresponding to the column names
r
When you perform a standard Stream Load (what StarRocks calls "Full Upsert") without any column mapping headers, StarRocks expects a value for every column in the table. If a key is missing from your JSON payload, StarRocks defaults to inserting a 
NULL
 for that column (if it is nullable), which bypasses your table's schema 
DEFAULT
value. To have StarRocks use your table's schema default values for missing fields in a Primary Key table, you have two main options: 1. Enable Partial Updates (Recommended) If you enable the Partial Update feature, StarRocks will only update/insert the columns actually provided in the JSON. For a brand new row, any column not present in your JSON will automatically take its table 
DEFAULT
value. Add this header to your 
curl
command:
Copy code
bash
-H "partial_update: true"
* Note for JSON: Even in "Simple Mode" (where keys match column names), you must still provide a 
columns
 header when using 
partial_update
to tell StarRocks which fields are being updated. If you don't want to list all columns, you only need to list the ones present in your JSON. 2. Use 
ifnull()
Mapping If you prefer not to use Partial Updates, you must explicitly tell StarRocks to use a specific value when the JSON field is missing. This requires using the 
jsonpaths
and 
columns
headers:
Copy code
bash
-H "jsonpaths: [\"$.userName\", \"$.userId\"]" \
-H "columns: user_name, tmp_id, user_id = ifnull(tmp_id, 0)"
In this example, if 
userId
is missing from the JSON, 
tmp_id
becomes NULL, and 
ifnull
sets it to 
0
. Summary of Behavior Feature | Missing JSON Key Result Standard Stream Load | Becomes 
NULL
(overwrites table default). Partial Update (
true
) | Ignored (triggers table 
DEFAULT
for new rows). Why this happens In a standard load, StarRocks treats the "record" as a full row. If a piece of data is missing, it's considered "not provided," and the system fills it with 
NULL
as it constructs the complete row before writing it to storage. The schema 
DEFAULT
is only used if the column is entirely excluded from the loading process (i.e., not mentioned in the 
columns
header at all). References • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • loading/StreamLoad.md • loading/Json_loading.md • table_design/data_distribution/Primary_key_table.md References • loading/StreamLoad.md • loading/Json_loading.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md
m
@Rocky Is there a way I can enable query profiling for expensive queries? As a DB root user I want to see historical queries to identify any causes of cpu spikes
r
To identify the causes of CPU spikes and monitor expensive queries, StarRocks provides several mechanisms ranging from real-time monitoring to long-term historical analysis. 1. Enable Profiling for "Expensive" Queries It is not recommended to enable query profiling for all queries in production due to overhead. Instead, set a threshold to capture only queries that exceed a certain duration.
Copy code
sql
-- Enable profiling only for queries slower than 30 seconds
SET GLOBAL big_query_profile_threshold = '30s';

-- (Optional) Enable for all queries if needed for a short period
SET GLOBAL enable_profile = true;
2. Monitor Running Queries (Real-time CPU usage) To identify a query causing an immediate CPU spike, use the 
proc
interface. This shows currently executing queries and their accumulated CPU time.
Copy code
sql
-- View queries running on the current FE
SHOW PROC '/current_queries';

-- View queries running across all FE nodes (v3.4+)
SHOW PROC '/global_current_queries';
Look for high values in the 
CPUTime
column to find the culprit. 3. Identify Historical Expensive Queries For historical analysis, you have three primary options: A. The Web UI (Recent History) Access 
http://<FE_IP>:<FE_HTTP_PORT>/queries
. This page lists recently finished queries. If profiling was enabled (manually or via the threshold), you can click the Profile link to see a deep-dive breakdown of where CPU time was spent (e.g., Scan, Aggregate, Join). B. Audit Logs (Long-term History) StarRocks records every query in 
fe.audit.log
. To analyze these via SQL, you should install the AuditLoader plugin. This loads logs into a table where you can run queries like:
Copy code
sql
-- Find the top 10 most CPU-intensive queries from the audit table
SELECT queryId, user, cpuCostNs, queryTime, sql 
FROM starrocks_audit_db__.starrocks_audit_tbl__ 
ORDER BY cpuCostNs DESC 
LIMIT 10;
C. Query Detail API (In-Memory Cache) You can enable a memory-based cache of recent query details. 1. Enable it in 
fe.conf
(or via 
ADMIN SET FRONTEND CONFIG
): 
enable_collect_query_detail_info = true
2. Access the data via the Query Detail API or by using the 
show profilelist
command in your SQL client. Summary Checklist for a Root User: 1. Identify spike: 
SHOW PROC '/global_current_queries'
to see what is running now. 2. Find recent history: Use the Web UI or 
SHOW PROFILELIST
to see what just finished. 3. Analyze deeply: Use 
SELECT get_query_profile('query_id')
to see the execution graph and bottleneck operators. References • best_practices/query_tuning/query_profile_overview.md • using_starrocks/running_queries.md • administration/management/audit_loader.md • administration/http_interface/query_detail.md References • administration/http_interface/query_detail.md • using_starrocks/running_queries.md • sql-reference/information_schema/loads.md
Open in Slack
PreviousNext
Powered by