https://linen.dev logo
Join Slack
Powered by
This message was deleted.
# troubleshooting
s
This message was deleted.
j
I believe you have to put every column explicitly in the select list that already exists in the datasource. Use column name aliases so it knows which columns you are referring to. e.g. sum(duration) may need an alias.
t
Just tried, still the same result
Copy code
REPLACE INTO test_view OVERWRITE WHERE __time >= TIMESTAMP '2023-02-07 11:25:00' AND __time < TIMESTAMP '2023-02-07 11:30:00'
 select max(__time) __time,
traceId,
ARRAY_AGG(DISTINCT tags, 10000) AS tags,
sum(duration) AS duration,
sum (count) AS count
from test_view
WHERE __time >= TIMESTAMP '2023-02-07 11:25:00' AND __time < TIMESTAMP '2023-02-07 11:30:00'
GROUP BY traceId
PARTITIONED BY TIME_FLOOR(__time, 'PT5M')
j
Are you getting an error? I thought the partitioned by clause had to have a time type e.g. PT5M. Here is the doc page: https://druid.apache.org/docs/latest/multi-stage-query/reference.html#partitioned-by
t
j
Oh sorry, yes you have to do that due to the "missing" piece of ingestion functionality. Is this still not working? Does the select statement by itself (removing REPLACE and PARTITIONED lines) return the data you are expecting, with the proper column names to match the datasource?
t
@John Kowtko I can REPLACE segment, Issue is that after replacement segment metadata shows that metrics are converted to dimensions
j
okay that part I don't know about, how a column in SQL ingest is identified as a dimension vs a metric. This doc page: HERE doesn't imply that you have to do anything special in the select list ... however it also doesn't show what the resulting column types are.
I would expect that whatever you GROUP BY is a dimension, everything else is a metric.
How are you distinguishing between metrics and dimensions? I only see datatypes (e.g. string, numeric, complex)
t
I have only one column in group by and everything else has aggregations, what I see that every column ended up in dimension
we use kafka ingestion, dimensions and metrics are defined in ingestion spec
j
Where does it show as a dimension? Are you talking about the LHS/RHS Json syntax of the metric fields that have metadata in them?
t
I see the dimensions in segments view -> select segment and it shows metadata
j
Okay I haven't looked that that while using the SQL ingestion ... interesting ... unfortunately this will require someone who knows more about the native ingestion than me ... @Vadim, when you do a reingest using REPLACE/SELECT, does it matter if some of the resulting metric fields are classified as dimensions or metrics, if no rollup metadata is needed for the metric (e.g. it is a sum())?
v
if I set finalizeAggregations:true then the metrics become null.
I checked this on my setup…without this flag the dimensions and metrics are correctly specified in the segment metadata. My sense is that this does not matter for querying but does matter for more re-indexing with additional rollup
đź’ˇ 1
t
@Vijay Narayanan Thanks for confirming that it is side effect of using finalizeAggregations:true. Also our metrics such as duration could have high cardinality, Is it recommended to have high cardinality field as dimension?
v
having something as a dimension ro a metric does not really matter in queries. You can aggregate any numeric field and sketch any field.
so from a query performance perspective what matters is the query and the partitioning
One thing that matters is how you handle fields like userid or session id (trace id may be something to look into also). These are usually guids and hence no real value in retaining them as is. So you can sketch them and do a distinct count etc.
v
Just seeing this. I can give more info on that is going on: (we should update the docs to make it more clear). You are trying to create a rolled up datasource (as in a datasource with metadata about metrics and dimensions). In SQL you could be using a GROUP BY to either create a rolled up datasource or to simply apply a transformation to the data before ingesting it as a regular datasource. In other words in "true" SQL 
INSERT INTO tbl <your query>
should behave the same no matter what you put in 
<your query>
. So how does Druid SQL based ingestion know is you want rollup metadata written to your new segments? It does it by the logic of 
write rollup metadata is the query is INSERT or REPLACE, and the outer SELECT contains a GROUP BY clause, and finalizeAggregations is false
. So even though you have nothing to finalize (you are only doing SUM and finalization is for sketches) you still need to set it explicitly to 
false
if you want to have the rollup metadata written out.
The web console takes care of it for you:
if you leave 
Finalize aggregations
on 
auto
then it will be set to 
false
every time you do an INSERT or REPLACE, but if you are using the API yourself then you have to set it to 
false
explicitly.
t
Open in Slack
PreviousNext
Powered by