In the SQL, how would I say give me the top 100, then group the rest as "Other"? - Cloudflare Developers

© 2026 Hedgehog Software, LLC

More

Communities Docs About Terms Privacy

Aries

A

AriesOP•8/24/23, 6:25 AM

https://files.aries.fyi/2023/08/24/firefox_469e36f8aac75ae2.png

Aries

AAries In the SQL, how would I say give me the top 100, then group the rest as "Other"?

DaniFoldi

D

DaniFoldi•8/24/23, 7:04 AM

In SQL, you can use a statement like below

SELECT id, ...,
CASE
  WHEN id <= 100 THEN id
  ELSE 'Other'
END AS groupedId
FROM myTable

SELECT id, ...,
CASE
  WHEN id <= 100 THEN id
  ELSE 'Other'
END AS groupedId
FROM myTable

However, I'm not sure if clickhouse/AE supports that as I haven't played around with it too much yet, so if it doesn't work you're likely best selecting the top 100, and summing the rest "manually" in subqueries

itsmatteomanf

I

itsmatteomanf•8/31/23, 12:46 PM

Is there a way to get more than the default 25 ingestions per invocation? I have a queue that processes 100 items which need to push things a few things to AE per item.

itsmatteomanf

I

itsmatteomanf•8/31/23, 1:58 PM

Oh, I read now... maybe I should ping @46bit

John Spurlock

J

John Spurlock•8/31/23, 9:28 PM

having used AE from basically day 1, you definitely have to deal with pretty much every 5xx5xx error in the book coming back, and retry-after-delay. Most of these errors are either one-offs or transient

pigri

P

pigri•9/1/23, 7:22 AM

Hmm... anybody else who also has experience with analytics_engine is not working? My grafana dashboards are down and I can see this error: Sorry, we were unable to evaluate your querySorry, we were unable to evaluate your query

jimh

Jjimh Hi @.pigri - can you post an example of a query that returns this error?

pigri

P

pigri•9/1/23, 8:29 AM

Sure,
SELECT
timestamp AS t,
*
FROM
usageDataOutput
WHERE
t >= NOW() - INTERVAL '1' DAY
ORDER BY t

pigri

P

pigri•9/1/23, 8:29 AM

2 days ago this query is worked

pigri

P

pigri•9/1/23, 9:26 AM

Thank you! We try to figure out who can fix it.

jimh

Jjimh I searched through errors on our end based on a hunch and I'm pretty sure the is...

Kasumi

K

Kasumi•9/1/23, 9:53 AM

Sorry for disturbing but does it mean that cf cuts the responses off as soon as the response hits 100mb or is it just analytics-engine ?

jimh

Jjimh The quickest way to reduce response size for this query will be to remove the `*...

pigri

P

pigri•9/1/23, 10:52 AM

Thank you we fixed it!

Unsmart

U

Unsmart•9/1/23, 3:58 PM

Any new plugin we should be using since I guess the altinity plugin thats recommended is using legacy stuff and will eventually break unless they are working on updating it maybe

jimh

Jjimh It looks like they're aware: https://github.com/Altinity/clickhouse-grafana/issu...

itsmatteomanf

I

itsmatteomanf•9/1/23, 5:22 PM

It’s been open since March 22… doesn’t feel like it’s really moving along

Aries

A

AriesOP•9/4/23, 8:02 AM

Does this support prepared statements?

Aries

A

AriesOP•9/4/23, 8:02 AM

I cannot seem to find any references.

Aries

AAries Does this support prepared statements?

itsmatteomanf

I

itsmatteomanf•9/4/23, 8:03 AM

In the sense that you save them prior to calling them or you do pepare them in your code prior to sending them, and then call them directly like D1 does?

itsmatteomanf

Iitsmatteomanf In the sense that you save them prior to calling them or you do pepare them in y...

Aries

A

AriesOP•9/4/23, 8:04 AM

To prevent SQL injection, where you can pass values seperately from the query.

Aries

AAries To prevent SQL injection, where you can pass values seperately from the query.

itsmatteomanf

I

itsmatteomanf•9/4/23, 8:05 AM

Yeah, then no, it's a REST api, there is no direct binding for querying it.
https://developers.cloudflare.com/analytics/analytics-engine/worker-querying/#worker-script

Aries

A

AriesOP•9/4/23, 8:05 AM

It would be nice if the REST API accepted a body like {sql: string, values: string}{sql: string, values: string} so that wouldn't be an issue.

Aries

A

AriesOP•9/4/23, 8:09 AM

https://files.aries.fyi/2023/09/04/Code_6e32d17f8bd23031.png

Aries

A

AriesOP•9/4/23, 8:10 AM

https://files.aries.fyi/2023/09/04/Code_3490596e08bdb62c.png

riskiidrus

R

riskiidrus•9/5/23, 5:03 AM

Hi, can I know how long my and my customers' analytics data is stored?

I just want to make sure that the data can still be accessed for a long time.

Erisa

E

Erisa•9/5/23, 5:34 AM

3 months at the moment https://developers.cloudflare.com/analytics/analytics-engine/get-started/#data-retention

Aries

A

AriesOP•9/6/23, 12:23 AM

why does

(intDiv(toUInt32(timestamp), 120) * 120) * 1000 as x

(intDiv(toUInt32(timestamp), 120) * 120) * 1000 as x

end up as a string?

Aries

A

AriesOP•9/6/23, 12:23 AM

https://files.aries.fyi/2023/09/05/Code_f04483f7b576c9d4.png

Aries

A

AriesOP•9/6/23, 12:41 AM

same with toUnixTimestamp(timestamp) * 1000toUnixTimestamp(timestamp) * 1000

Aries

A

AriesOP•9/6/23, 12:42 AM

I assume because it doesn't fit in a 32int but still, pretty weird.

Original message was deleted

Aries

A

AriesOP•9/6/23, 12:47 AM

?? 16938009000001693800900000 is a perfectly valid js number.

Unsmart

U

Unsmart•9/6/23, 12:48 AM

A Uint64 can contain more precise numbers than js numbers can though so all the Uint64 will be string and you can look at meta for the type

Aries

A

AriesOP•9/6/23, 12:57 AM

I suppose that's true.

miki / 46bit

Mmiki / 46bit We discussed a few times what to do in our SQL API - it's safest to pass through...

Aries

A

AriesOP•9/6/23, 9:09 PM

It looks like it's configurable in Clickhouse? https://github.com/ClickHouse/ClickHouse/issues/114#issuecomment-251463427

GitHub

JSON UInt64 as string · Issue #114 · ClickHouse/ClickHouse

Is there a reason why Int64 and UInt64 are output as string in JSON? :) select toUInt64(1) as n format JSONEachRow; {"n":"1"} :) select toUInt32(1) as n format JSONEachRow; {&qu...

Aries

A

AriesOP•9/11/23, 1:09 AM

Are these functions planned to be supported?
https://clickhouse.com/docs/en/sql-reference/functions/rounding-functions

Rounding Functions | ClickHouse Docs

floor(x[, N])

vikasaditya

V

vikasaditya•10/3/23, 1:56 PM

I am getting error

 Input was invalid: cannot combine the DateTime and String types with the >= operator: "timestamp" >= '2023-03-01 00:00:00'

 Input was invalid: cannot combine the DateTime and String types with the >= operator: "timestamp" >= '2023-03-01 00:00:00'

when trying the billing query given at https://developers.cloudflare.com/analytics/analytics-engine/recipes/usage-based-billing-for-your-saas-product/

Usage-based billing for your SaaS product · Cloudflare Analytics docs

Many Cloudflare customers run software-as-a-service products with multiple customers. A big concern for such companies is understanding the cost of …

vikasaditya

V

vikasaditya•10/3/23, 1:57 PM

while

WHERE timestamp > NOW() - INTERVAL '7' DAY

WHERE timestamp > NOW() - INTERVAL '7' DAY

works