https://linen.dev logo
Join Slack
Powered by
Hello Team, I'm trying to use the LATEST function ...
# troubleshooting
m
Hello Team, I'm trying to use the LATEST function in the sql query. This is working fine when i run a select query to get the latest record if multiple records for a group by columns. however i'm trying the run this sql in MSQ to create a new datasource then create went through but column data contains "{"lhs":1735689647000,"rhs":"search"}" instead of the actual data so how to get rid of this to retain only actual data. Highly appreciated if any help on this. Thanks druid.apache.org/docs/latest/querying/sql-functions#…
j
Hi Maddy, that format is the "un-finalized" format containing the metadata to allow further rollups. please check the value of 
finalizeAggregations
being used in the MSQ SQL create statement: druid.apache.org/docs/latest/multi-stage-query/reference#… Also review the doc section on MSQ SQL rollups: druid.apache.org/docs/latest/multi-stage-query/concepts#… Let us know if you have any questions. Thanks. John
m
Thank you @John Kowtko; Yes it worked.
🙌 1
I'm trying with druid 34.0 with same settings and sql query. It's failing with below error
Copy code
UnknownError: java.lang.RuntimeException: java.lang.RuntimeException: org.apache.druid.rpc.HttpResponseException: Server error [400 Bad Request]; body: {"error":"druidException","errorCode":"entryAlreadyExists","persona":"USER","category":"INVALID_INPUT","errorMessage":"Task[query-1d405664-750d-40e1-abdc-09c847366965-worker0_0] already exists","context":{}}
nothing worker task exists, it's first time i'm running this query so, any idea? thxs sql
Copy code
SET finalizeAggregations = TRUE;
SET maxNumTasks = 4;

REPLACE INTO weekly_current__v
OVERWRITE ALL
SELECT
MAX(__time) AS __time,
event_id,
campaign_id,
LATEST(creative_name) AS creative_name,
LATEST(ad_type) AS ad_type,
LATEST(site_type) AS site_type,
LATEST(ad_context) AS ad_context,
LATEST(npi) AS npi,
LATEST(session_id) AS session_id,
LATEST(ad_server) AS ad_server,
LATEST(creative_id) AS creative_id,
LATEST(placement_id) AS placement_id,
LATEST(media_partner_program_name) AS media_partner_program_name,
LATEST(event_type) AS event_type,
LATEST(placement_name) AS placement_name,
LATEST(__time) AS last_modified
FROM weekly__v
GROUP BY event_id, campaign_id
PARTITIONED BY DAY
CLUSTERED BY campaign_id, event_id
@John Kowtko FYI. thanks
j
Are you submitting via API? If so, can you first try running this from the web console Query tab?
m
druid UI console (query tab) running this SQL using MSQ engine
j
Can you try runing just the select statement (remove the REPLACE OVERWRITE, PARTITIONED BY and CLUSTERED BY clauses) in MSQ to see if that works? If it doesn't, then can you try increasing the number of tasks?
m
Yes i was able to run by choosing this option - Engine Auto {SQL Native} with maxNumTasks = 4 (out of 9) and sql query returns the data however when i change it to SQL (task) - MSQ engine with maxNumTasks = 4 (out of 9). sql query fails with same error. so what does this mean? i can't run in druid console using MSQ engine of creating datasource.
j
Okay, that's what I wanted to check .. the SQL(task) is running the MSQ engine ... just not inserting the data into a table. So the problem is on the query side. Can you try the same query using the wikipedia demo datasource? Here is a converted query that I created based on your query ... curious to see if it breaks on your cluster (it works on mine) ...
Copy code
SELECT
MAX(__time) AS __time,
channel,
flags,
LATEST(page) AS page,
LATEST(diffUrl) AS diffUrl,
LATEST(comment) AS comment,
LATEST(isNew) AS ad_context,
LATEST(isMinor) AS isMinor,
LATEST(added) AS added,
LATEST(delta) AS delta,
LATEST(namespace) AS namespace,
LATEST(cityName) AS cityName,
LATEST(countryName) AS countryName,
LATEST(regionIsoCode) AS regionIsoCode,
LATEST(metroCode) AS metroCode,
LATEST(__time) AS last_modified
FROM wikipedia
GROUP BY channel, flags
By the way, are any of the fields involved complex fields? Or are they all simple (numeric, text) fields?
m
same error with wikipedia table so not sure what's causing going wrong here.
no complex fields in the my table only timestamp, varchar
j
It's working for me ... I am running a version of Imply Druid released in April 2026 ... that would be the approximate of Druid 36 or 37. I also just tried an Imply version that is closer to Druid 34 ... but that also works for me.
Check your metadata DB druid_tasks table to see if there is an entry with this task ID.
m
sorry this query worked in Druid 37. but in our production env has Druid 34 so tried in 34, it's failing. Is this expected to fail in lower version?
select query works everywhere meaning both Druid 34 and 37. however create datasource using REPLACE in 34 fails so.
j
The error appears to be coming from one of two places in the code: • github.com/apache/druid/blob/…/HeapMemoryTaskStorage.java#… • github.com/apache/druid/blob/…/TaskQueue.java#… Try cycling your Overlord to see if there is a stale entry in the task map?
m
ohh sure..thanks.
looks like it was bug in Druid 34 earlier version later it got fixed so we are planning to upgrade with latest Druid 37. It works without any issues with latest version so
👍 1
2 Views
Open in Slack
PreviousNext
Powered by