http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> how can i support an internal ui th...
# questions-and-troubleshooting
k
@Rocky how can i support an internal ui that supports what 8030/query shows for historical queries, not just the last 100 queries? i want to be able to filter for queries based on user, duration, etc. and be able to link to the full query profile
r
To support an internal UI for historical queries beyond the default 100-query limit, you should combine StarRocks' AuditLoader for metadata filtering and the Query Detail API for profile retrieval. 1. Store Historical Metadata (Filter by User, Duration, etc.) The best way to persist query metadata (SQL text, user, execution time, CPU/Memory usage) is to use the AuditLoader plugin. It automatically loads data from the FE audit logs into a StarRocks table. * Setup: Install the 
AuditLoader
plugin and create the target table (usually named 
starrocks_audit_tbl__
). * Filtering: Since the data is in a standard StarRocks table, your UI can run SQL queries to filter by any field:
Copy code
sql
    SELECT queryId, user, queryTime, memCostBytes, stmt
    FROM starrocks_audit_db*.starrocks_audit_tbl*
    WHERE user = 'reporting_user'
      AND queryTime > 5000 -- Duration > 5s
      AND timestamp > now() - interval 7 day;
* Retention: You can control how long you keep this history using TTL or Partition management on the audit table. 2. Access Full Query Profiles StarRocks stores full execution profiles in the FE's memory. To support your UI, you need to manage two things: the cache size and the retrieval method. Increase the Cache Limit By default, the FE only keeps the last 100 profiles. You can increase this by modifying the FE configuration: * 
query_detail_cache_size
: Increase this (e.g., to 1000 or more) to keep more profiles in memory. * 
enable_collect_query_detail_info
: Must be set to 
true
(default is 
false
in older versions) to collect these details. Retrieve Profiles Programmatically For your internal UI to "link" to a profile, you can use the following methods: * SQL Function: 
SELECT get_query_profile('<query_id>');
returns the full text-based profile. * HTTP API: Use the Query Detail API (
http://<fe_ip>:<fe_http_port>/api/query_detail?query_id=<id>
). This returns a JSON object containing the full profile string and execution metrics. 3. Recommended Architecture for a Custom UI If you need to keep profiles for weeks or months (which would consume too much FE memory), use this pattern: 1. Metadata: Query the 
starrocks_audit_tbl__
(populated by AuditLoader) for your main UI list and filters. 2. Persistence Service: Build a small background worker that polls the StarRocks Query Detail API for new `queryId`s and saves the 
profile
text into a separate long-term StarRocks table or a blob store (S3/MinIO). 3. UI Link: When a user clicks a query in your UI, the backend fetches the profile from your long-term storage or directly from the StarRocks API if it's still in the FE cache. Summary of Key Configs Parameter | Type | Description 
enable_collect_query_detail_info
| FE Config | Set to 
true
to enable profile collection. 
query_detail_cache_size
| FE Config | Increase from 
100
to your desired in-memory history depth. 
enable_audit_loader
| Plugin | Use to persist query metadata to a table for filtering. For more details on the AuditLoader setup, refer to the StarRocks AuditLoader documentation. References • best_practices/query_tuning/query_profile_text_based_analysis.md • using_starrocks/caching/query_cache.md • data_source/data_cache.md
k
@Rocky is there a native function in starrocks that streams query profiles to an s3 location
r
There is no single native SQL function (e.g., 
STREAM_TO_S3()
) that automatically pushes query profiles to S3. However, starting from v3.2.5, StarRocks introduced a native logging mechanism specifically designed to solve the "last 100 queries" limitation by persisting profiles to disk in a structured JSON format. For your internal UI, the best approach is to leverage these Profile Logs and AuditLoader. 1. The Native Solution: 
fe.profile.log
(v3.2.5+) Instead of scraping the memory-limited Web UI or API, you can configure the Frontend (FE) to write every query profile to a local JSON log file. This allows you to store thousands of historical profiles without impacting FE memory. * How it works: The FE writes a file named 
fe.profile.log
. Each line is a JSON object containing the full 
queryDetail
(metadata + execution profile). * Key FE Configurations: * `enable_profile_log = true`: Enables the feature. * `profile_log_dir`: Path where the logs are stored. * 
profile_log_roll_num
& `profile_log_roll_interval`: Controls rotation (e.g., keep 10 files, rotate daily). * `profile_log_delete_age`: Controls retention (e.g., 
7d
for 7 days). 2. Moving Profiles to S3 Since these are standard log files, you have two "native-adjacent" ways to get them to S3: * Infrastructure Level (Recommended): Use a log collector like Fluentd, Promtail, or Vector to tail 
fe.profile.log
and stream it directly to an S3 bucket. * SQL Level (Creative Workaround): 1. Create a StarRocks table with a 
JSON
or 
TEXT
column. 2. Use a background script to 
INSERT INTO profile_table SELECT get_query_profile('<query_id>');
. 3. Use the native 
EXPORT
command to move that table data to S3:
Copy code
sql
        EXPORT TABLE historical_profiles
        TO "<s3://your-bucket/profiles/>"
        PROPERTIES ("format" = "json");
3. Recommended Architecture for Your UI To support filtering (user, duration) and linking to full profiles: 1. Metadata (Filtering): Use the *AuditLoader* plugin. It populates an internal table (
starrocks_audit_tbl__
) with query metadata. Your UI should query this table for the "List View" and filters. 2. Full Profiles (Linking): * Option A (Modern): Enable 
fe.profile.log
. Have your UI backend read from these JSON logs or a secondary store where you've indexed them (like Elasticsearch or a StarRocks JSON-column table). * Option B (API-based): In your UI, when a user clicks "View Profile," have your backend call the Query Detail API: 
GET http://<fe_ip>:8030/api/query_detail?query_id=<id>
_Note: This only works for queries still in the FE's 
query_detail_cache_size
._ Summary of Setup Goal | Native Mechanism | Config/Command History > 100 | Profile Logging | 
enable_profile_log = true
Metadata Filtering | AuditLoader | 
starrocks_audit_tbl__
Full Profile Data | SQL Utility | 
get_query_profile('id')
S3 Export | Data Unloading | 
EXPORT TABLE ... TO "s3://..."
Recommendation: Upgrade to v3.2.5+ and enable 
fe.profile.log
. This provides the most stable, high-performance way to access historical profiles in JSON format for a custom UI. References • quick_start/shared-data.md • sql-reference/sql-statements/cluster-management/storage_volume/CREATE_STORAGE_VOLUME.md
k
@Rocky for The Native Solution: 
fe.profile.log
 will saving them to disk support being queryable by the sql function SELECT get_query_profile?
r
No, saving profiles to disk via 
fe.profile.log
does not automatically make them queryable by the 
get_query_profile()
SQL function. Here is how the relationship works: 1. In-Memory Cache (Short-term) The 
get_query_profile()
function and the 
/query
Web UI (port 8030) both pull data from the FE's in-memory cache (
ProfileManager
). * Limit: This is governed by 
query_detail_cache_size
(default 100). * Behavior: Once a query is evicted from this memory cache to make room for new ones, 
SELECT get_query_profile('id')
will return empty, even if the profile still exists in the disk logs. 2. 
fe.profile.log
(Long-term) The 
fe.profile.log
file is a one-way sink. The FE writes the JSON profile to this file and then "forgets" about it once it leaves the memory cache. There is no native index that allows StarRocks to "go back" and read a specific line from a log file on disk when you run a SQL function. How to make historical profiles queryable via SQL If you want your internal UI to support historical profiles using SQL, you need to "close the loop" yourself: 1. Direct File Querying: Use a File External Table to query the log files directly where they sit on the FE disk (or after moving them to S3):
Copy code
sql
    CREATE EXTERNAL TABLE historical_profiles (
        queryId VARCHAR(100),
        queryDetail JSON  -- The profile text is inside this JSON
    )
    ENGINE=file
    PROPERTIES (
        "path" = "<s3://your-bucket/profile-logs/>",
        "format" = "json"
    );
2. Import to a Table: Use a routine load or a script to parse 
fe.profile.log
and 
INSERT
the data into a standard StarRocks table. Your UI would then just run 
SELECT profile_text FROM my_history_table WHERE query_id = '...'
. 3. UI Logic: Your UI backend can be designed to first check the Query Detail API (for recent queries). If it gets a 404/Empty, it then searches your long-term storage (S3 or the 
historical_profiles
table). Summary: 
fe.profile.log
is for persisting the data so it isn't lost. To query it after the 100-query limit, you must treat those logs as a data source and load/map them back into a StarRocks. References • administration/management/logs.md • administration/management/FE_configuration.md • administration/management/proc_profile.md • best_practices/query_tuning/query_profile_overview.md • sql-reference/System_variable.md
k
@Rocky fe.profile.log is not populating
r
If 
fe.profile.log
is not populating, it is almost always due to one of the following three reasons: 1. Missing Dependency Flag (Most Likely) Even if 
enable_profile_log
is set to 
true
, StarRocks will not write to the file unless it is actively collecting query details. You must enable this specifically: * Check/Set: Run 
ADMIN SET FRONTEND CONFIG ("enable_collect_query_detail_info" = "true");
* Note: This is a global FE configuration. Without this, the internal "Profile Manager" is empty, so there is nothing for the log sink to write. 2. Version and Mutability Requirements * Version Check: This feature was introduced in v3.2.5. If you are on an older version, the parameter is ignored. * Restart Requirement: * In versions below 4.1, 
enable_profile_log
is not mutable. If you just added it to your 
fe.conf
and didn't restart the FE, it won't take effect. * In v4.1+, it is mutable and can be toggled via 
ADMIN SET FRONTEND CONFIG
. 3. Log Directory Permissions The FE writes these logs to the directory defined by 
profile_log_dir
(default is 
STARROCKS_HOME/log/
). * Ensure the user running the StarRocks FE process has write permissions to that folder. * Check your 
fe.warn.log
or 
fe.log
for any errors containing "ProfileFileAppender" or "Log4j" which would indicate a failure to initialize the file. 4. Distinguishing "Queries" from "Loads" By default, 
fe.profile.log
primarily captures standard SQL queries (SELECT/DML). * If you are looking for Stream Load or Routine Load profiles, you need an additional flag (introduced in later versions): * 
enable_print_load_profile_to_log = true
────────── Troubleshooting Checklist To fix this right now, run these commands and verify the output: 1. Check if flags are actually on:
Copy code
sql
    ADMIN SHOW FRONTEND CONFIG LIKE '%profile%';
    ADMIN SHOW FRONTEND CONFIG LIKE '%collect_query_detail%';
2. Enable collection (if false):
Copy code
sql
    ADMIN SET FRONTEND CONFIG ("enable_collect_query_detail_info" = "true");
3. Run a test query (e.g., 
SELECT 1;
) and then check the disk:
Copy code
bash
    tail -f /path/to/starrocks/fe/log/fe.profile.log
If the file still doesn't exist after these steps and you are on v3.2.5+, you likely need to add 
enable_profile_log = true
to your 
fe.conf
and restart the FE process. References • administration/management/FE_configuration.md • administration/management/logs.md • administration/management/proc_profile.md • best_practices/query_tuning/query_profile_overview.md • sql-reference/System_variable.md References • administration/management/FE_configuration.md
k
@Rocky i have logs being added to an s3 bucket folder called fe-profile, partitioned by fe node (fe-profile/fe-0, etc.) how can i make a starrocks external table to be able to query all log files in subdirectories at the same time
r
To query query profiles across multiple FE nodes in S3 simultaneously, you should use a File External Table with the recursive listing property enabled. Since 
fe.profile.log
is a line-delimited JSON format (JSONL) and StarRocks File External Tables primarily support structured formats (Parquet, ORC, CSV), the most effective "native" way to handle this is to treat the JSON lines as single-column CSV strings and parse them. 1. The Strategy * Path: Point the table to the root folder 
<s3://your-bucket/fe-profile/>
. * Recursion: Enable 
enable_recursive_listing
so StarRocks traverses the 
fe-0/
, 
fe-1/
subdirectories. * Format Trick: Use 
format = "csv"
with a non-existent separator (like 
\x01
) to read the entire JSON line into one column. 2. Create the External Table Replace the placeholders with your S3 bucket and credentials:
Copy code
sql
CREATE EXTERNAL TABLE ext_fe_profiles (
    raw_content STRING
) 
ENGINE=file
PROPERTIES (
    "path" = "<s3://your-bucket/fe-profile/>",
    "format" = "csv",
    "column_separator" = "\x01",         -- Use a character that won't appear in your JSON
    "enable_recursive_listing" = "true", -- This is the key for subdirectories
    "aws.s3.access_key" = "YOUR_AK",
    "aws.s3.secret_key" = "YOUR_SK",
    "aws.s3.region" = "your-region"
);
3. Querying with JSON Parsing Since the data is now in a single string column, you use 
parse_json()
or 
get_json_string()
to extract fields:
Copy code
sql
SELECT 
    get_json_string(raw_content, '$.queryId') as query_id,
    get_json_string(raw_content, '$.user') as user,
    get_json_string(raw_content, '$.queryTime') as query_time,
    parse_json(raw_content) as full_profile
FROM ext_fe_profiles
WHERE get_json_string(raw_content, '$.queryId') = 'your-query-id';
4. How to identify the FE Node (Partitioning) StarRocks External Tables support Path Partitioning if the folders are named in a 
key=value
format (e.g., 
fe_node=fe-0/
). * If you can change the S3 structure: Rename folders to 
fe-profile/node=fe-0/
, 
fe-profile/node=fe-1/
. StarRocks will automatically recognize 
node
as a column. * If you cannot change the S3 structure: You can use the 
FILES()
table function instead of a permanent table, which allows you to extract columns from the path even for CSV/JSON files in newer versions (v3.2+). Example using 
FILES()
for path extraction:
Copy code
sql
SELECT *, fe_node 
FROM FILES(
    "path" = "<s3://your-bucket/fe-profile/*/*.log>",
    "format" = "csv",
    "column_separator" = "\x01",
    "columns_from_path" = "fe_node", -- Extracts the folder name
    "aws.s3.access_key" = "...",
    "aws.s3.secret_key" = "..."
);
_(Note: 
columns_from_path
typically expects the path to contain a 
=
for mapping, but if you wrap this in a View, you can also use string functions on the 
PATH
pseudo-column returned by 
FILES
to extract the node name manually.)_ Summary Checklist 1. Recursion: Ensure 
enable_recursive_listing
is 
true
. 2. Permissions: The IAM user/role must have 
s3:ListBucket
and 
s3:GetObject
permissions on the entire path. 3. Format: Using 
csv
+ 
column_separator = \x01
is the standard workaround for querying raw JSONL files via external tables. References • sql-reference/sql-functions/table-functions/files.md
k
@Rocky Query 1 ERROR at Line 4: : not supported format: csv when creating table in default catalog on 4.1
r
The error occurs because the File External Table (
ENGINE=file
) does not support the 
csv
format. In StarRocks, 
ENGINE=file
is limited to binary formats: Parquet, ORC, Avro, RCFile, and SequenceFile. Even in very recent versions like 3.x or 4.x, this specific engine does not recognize "csv" as a valid format property. The Solution: Use a VIEW with the 
FILES()
function Instead of a permanent File External Table, use a View wrapping the 
FILES()
table function. The 
FILES()
function is the modern way to query cloud storage in StarRocks; it supports the CSV format (from v3.3+) and handles wildcards for subdirectories. Since 
fe.profile.log
is actually a JSON format, the best way to query it is to read the entire line as a single CSV column and then parse it using JSON functions. 1. The Fixed SQL (Using a View) Run this in your default catalog:
Copy code
sql
CREATE VIEW v_fe_profiles AS
SELECT 
    -- Assuming you want to parse the JSON content
    get_json_string(raw_line, '$.queryId') as query_id,
    get_json_string(raw_line, '$.user') as user,
    parse_json(raw_line) as profile_data
FROM FILES(
    "path" = "<s3://your-bucket/fe-profile/*/*.log>", -- Wildcards handle subdirectories
    "format" = "csv",
    "column_separator" = "\x01",  -- Use a separator that won't appear in the JSON
    "aws.s3.access_key" = "YOUR_AK",
    "aws.s3.secret_key" = "YOUR_SK",
    "aws.s3.region" = "your-region"
) AS t(raw_line);
2. Why this works better than 
ENGINE=file
 * Format Support: 
FILES()
explicitly supports 
csv
, whereas 
ENGINE=file
does not. * Wildcard Subdirectories: By using 
_/_.log
in the path, StarRocks automatically scans all logs across your 
fe-0
, 
fe-1
folders. * Schema Inference: 
FILES()
can infer schemas, but for raw logs, the "single-column CSV" trick is safer for parsing JSON. Troubleshooting "not supported format" If you specifically need a permanent table structure and don't want a View: 1. Check Version: Ensure you are on StarRocks v3.3 or higher. (If you are indeed on 4.1, you are well covered). 2. Case Sensitivity: Ensure 
format
is lowercase 
"csv"
. 3. Alternative (External Catalog): If you have many files, consider creating an External Catalog (like a Hive catalog) pointing to the S3 bucket, though this usually requires a Metastore. For raw log analysis, the VIEW + FILES() approach is the standard recommendation. References • sql-reference/sql-functions/table-functions/files.md • data_source/file_external_table.md • loading/loading_introduction/feature-support-loading-and-unloading.md References • sql-reference/sql-functions/table-functions/files.md • data_source/feature-support-data-lake-analytics.md
k
Query 1 ERROR at Line 19: : Getting analyzing error. Detail message: Column 'raw_line' cannot be resolved.
@Rocky Query 1 ERROR at Line 19: : Getting analyzing error. Detail message: Column 'raw_line' cannot be resolved. is there a way for the fe nodes to add to the log files in a more consistent format?
r
The error "Column 'raw_line' cannot be resolved" occurred because the 
FILES()
function for CSV doesn't automatically map column aliases in the 
AS t(col)
format the same way standard tables do. Instead, StarRocks assigns default names like 
$1
, 
$2
, etc., to CSV columns. 1. The Corrected Query To fix the error, use 
$1
to reference the entire line (since we set the separator to an unused character):
Copy code
sql
CREATE OR REPLACE VIEW v_fe_profiles AS
SELECT 
    get_json_string($1, '$.queryId') as query_id,
    get_json_string($1, '$.user') as user,
    get_json_string($1, '$.state') as query_state,
    cast(get_json_string($1, '$.timestamp') as datetime) as query_time,
    parse_json($1) as full_profile
FROM FILES(
    "path" = "<s3://your-bucket/fe-profile/*/*.log>",
    "format" = "csv",
    "column_separator" = "\x01",
    "aws.s3.access_key" = "YOUR_AK",
    "aws.s3.secret_key" = "YOUR_SK",
    "aws.s3.region" = "your-region"
);
2. Can FE Nodes write in a "more consistent" format? Currently, no. The 
fe.profile.log
format is hardcoded to JSON Lines (JSONL) within the StarRocks source code. There are no configuration parameters to switch this output to CSV or Parquet. However, JSON is actually the most "consistent" format for query profiles because: * Variable Schema: Profiles for a simple 
SELECT 1
vs. a complex 
JOIN
with 50 operators have very different internal structures. JSON handles this nested, varying data naturally. * Version Compatibility: As StarRocks adds new metrics (e.g., "spill bytes" or "peak memory"), JSON allows those fields to be added without breaking existing log parsers. 3. Best Practice for Log Management If you want these logs to be more "query-friendly" without manual JSON parsing every time, the recommended approach is: 1. Use the View (as shown above): This abstracts the complexity. Users just query the view. 2. Materialized Table (Periodic Load): If the S3 log volume is huge and queries are slow, create a native StarRocks table and set up a Pipe or a Routine Load to automatically ingest the S3 files into a real table. * StarRocks v3.2+ includes the 
PIPE
feature which is perfect for this: it monitors the S3 bucket and "pipes" new log files into a structured StarRocks table automatically as they arrive. 4. Extracting the FE Node Name Since you have a directory structure like 
fe-profile/fe-0/
, you can extract which FE generated the log by adding 
columns_from_path
to your 
FILES()
call:
Copy code
sql
SELECT 
    $1 as raw_log,
    node_name 
FROM FILES(
    "path" = "<s3://your-bucket/fe-profile/*/*.log>",
    "format" = "csv",
    "column_separator" = "\x01",
    "columns_from_path" = "node_name", -- This captures the '*' folder name
    ...
);
References • sql-reference/sql-functions/table-functions/files.md • administration/management/logs.md • loading/loading_introduction/feature-support-loading-and-unloading.md • sql-reference/sql-statements/loading_unloading/CREATE_PIPE.md References • administration/management/FE_configuration.md • administration/management/logs.md
k
@Rocky will this view be updated as log files are updated / added?
r
Yes, the view will be updated automatically every time you query it. Because a View in StarRocks is just a saved SQL query, executing 
SELECT * FROM v_fe_profiles
triggers the 
FILES()
function again. The 
FILES()
function performs a fresh listing of the S3 bucket and subdirectories whenever it runs. How it behaves: * New Files: As soon as a new 
.log
file appears in an FE node's subdirectory, the next query to the view will include its data. * File Updates (Appends): If you append data to an existing log file, the next query will read the entire file (including the new lines). * Deletions: If a file is deleted from S3, it will no longer appear in the query results. Performance Warning While this approach is "live," it has a performance trade-off: As the number of log files in S3 grows (e.g., thousands of small log files), querying the view will become slower because StarRocks must perform an S3 
LIST
operation and then open/read every single file for every query. Recommended "Production" Setup: 
PIPE
 If you find the view becoming slow, the better way to handle logs is using a StarRocks Pipe. A Pipe is a continuous background task that: 1. Polls the S3 bucket for new files. 2. Incrementally loads only the new data into a high-performance native StarRocks table. 3. Automatically handles the JSON parsing during ingestion. Example of a Pipe setup:
Copy code
sql
-- 1. Create a native table to hold the logs permanently
CREATE TABLE fe_profiles_internal (
    query_id STRING,
    user STRING,
    state STRING,
    query_time DATETIME,
    full_profile JSON
) 
DISTRIBUTED BY HASH(query_id);

-- 2. Create a Pipe to sync S3 to the table
CREATE PIPE fe_profile_sync
AS INSERT INTO fe_profiles_internal
SELECT 
    get_json_string($1, '$.queryId'),
    get_json_string($1, '$.user'),
    get_json_string($1, '$.state'),
    cast(get_json_string($1, '$.timestamp') as datetime),
    parse_json($1)
FROM FILES(
    "path" = "<s3://your-bucket/fe-profile/*/*.log>",
    "format" = "csv",
    "column_separator" = "\x01",
    ...
);
With a Pipe, your queries will run instantly on the local table, and StarRocks will manage the "real-time" sync from S3 in the background. References • sql-reference/sql-functions/table-functions/files.md • sql-reference/sql-statements/loading_unloading/CREATE_PIPE.md • loading/loading_introduction/feature-support-loading-and-unloading.md References • sql-reference/sql-functions/table-functions/files.md
k
@Rocky all columns except full_profile are being populated in the view:
Copy code
get_json_string($1, '$.queryId') as query_id,
    get_json_string($1, '$.user') as user,
    get_json_string($1, '$.state') as query_state,
    cast(get_json_string($1, '$.timestamp') as datetime) as query_time,
    parse_json($1) as full_profile
the log begins content like:
Copy code
"2026-08-17 20:12:52.615Z [profile] {\"querySource\":\"EXTERNAL\",\"eventTime\":1786997572604000000,\"queryId\":\"01a0115b-0280-7404-a939-4e0d9d3f51cb\",\"isQuery\":true,\"remoteIP\":\"10.0.74.5\",\"connId\":33557274,\"startTime\":1786997572224,\"endTime\":1786997572604,\"latency\":380,\"pendingTime\":0,\"netTime\":380,\"netComputeTime\":380,\"state\":\"FINISHED\",
r
The reason 
full_profile
is returning 
NULL
while the other columns work is that 
parse_json()
is strict, whereas 
get_json_string()
is more lenient. 
parse_json()
requires the input string to be a pure, valid JSON object from start to finish. Because your log lines start with a timestamp and the 
[profile]
tag (e.g., 
2026-08-17 ... [profile] { ... }
), 
parse_json()
sees the leading text, fails to find a valid JSON start, and returns 
NULL
. The Solution: Strip the Prefix You need to extract only the JSON portion of the string (starting from the first 
{
) before passing it to 
parse_json()
. You can do this using 
substr()
and 
instr()
. Corrected View Definition
Copy code
sql
CREATE OR REPLACE VIEW v_fe_profiles AS
WITH cleaned_logs AS (
    SELECT 
        -- Extract everything from the first '{' to the end of the line
        substr($1, instr($1, '{')) as json_content
    FROM FILES(
        "path" = "<s3://your-bucket/fe-profile/*/*.log>",
        "format" = "csv",
        "column_separator" = "\x01",
        "aws.s3.access_key" = "YOUR_AK",
        "aws.s3.secret_key" = "YOUR_SK",
        "aws.s3.region" = "your-region"
    )
)
SELECT 
    get_json_string(json_content, '$.queryId') as query_id,
    get_json_string(json_content, '$.user') as user,
    get_json_string(json_content, '$.state') as query_state,
    cast(get_json_string(json_content, '$.timestamp') as datetime) as query_time,
    -- Now parse_json receives a clean JSON string
    parse_json(json_content) as full_profile
FROM cleaned_logs;
Why did the other columns work? StarRocks' 
get_json_string()
implementation often scans the string to find the first JSON-like structure that matches the path, effectively ignoring the leading timestamp. However, when converting to the 
JSON
data type using 
parse_json()
, the engine enforces strict RFC 7159 compliance, which doesn't allow random text prefixes. Pro-Tip: Querying Recent Profiles Directly If you are looking for queries that happened recently (and the FE nodes are still running), you don't necessarily need to parse the S3 logs. You can query the built-in metadata table:
Copy code
sql
SELECT * FROM information_schema.query_detail 
WHERE state = 'FINISHED' 
ORDER BY start_time DESC;
This table contains the parsed metadata for recent queries without requiring any S3 log parsing. References • sql-reference/sql-functions/json-functions/json-constructor-functions/parse_json.md • sql-reference/sql-functions/string-functions/substr.md • administration/management/logs.md • sql-reference/sql-functions/json-functions/json-query-and-processing-functions/get_json_string.md References • sql-reference/sql-functions/json-functions/json-constructor-functions/parse_json.md • administration/management/logs.md • sql-reference/sql-functions/json-functions/json-query-and-processing-functions/get_json_string.md References • sql-reference/sql-functions/json-functions/json-query-and-processing-functions/get_json_string.md
Open in Slack
PreviousNext
Powered by