backend: aggregate and serve 1hr, and 24hr ledger data

[acharb]

from the new designs: https://www.figma.com/file/4z9lsHvbsEoVAtzfj3mNzy/Stellar-Dashboard?node-id=206%3A808

we have last 30 days aggregated and cached now, but will need to add 1hr and 24hr

[miguelnietoa]

Hi there @acharb! I would like to contribute to this issue, but I have some questions.
How could I have read access to the BigQuery project with projectId="hubble-261722", or how could I send the data to my own BigQuery project?

[acharb]

hey @miguelnietoa , that's awesome you want to contribute to the project. We have a public dataset on big query you can use: https://console.cloud.google.com/projectselector2/bigquery?p=crypto-stellar&d=crypto_stellar&page=dataset&supportedpurview=project.

We should change the configs in bigQuery.ts to pull from an env variable, but in the mean time you can change locally to pull in the data from the public dataset

[Selhar]

I'm thinking of using the same approach we used on DEX. Make a redis cache and have it cleaned on a CRON (on a 24 and 1h basis), basically for the same reasons we decided to use a cron on dex. What do you think @acharb ?

[Selhar]

On the 30 day ledger we listen for new ledgers closed with the horizon SDK, should we also do that for the 1h and 1day ledgers? Since they get updated so frequently, it doesn't seem necessary.

Also, regarding the graph that is shown here:

Should i make 5 queries to get the 5 datapoints between the last hour and right now? (Or a large query joining all datapoints, i think it doesn't make a difference from a throughput POV). Or is there another way to get an interval?
And one last thing, what should be the interval on the 1 day graph?

[acharb]

I'm thinking of using the same approach we used on DEX. Make a redis cache and have it cleaned on a CRON

my thinking is we don't use cron for this. Since eg. the last 1hr data should show the last hour up to the current time, it'll be updated on each new ledger. (Maybe doesn't have to be up the current second, up to the current minute should be fine.) Same thinking applies for the 24hr data.

Should i make 5 queries to get the 5 datapoints between the last hour and right now?

Not sure I'm 100% on your question. But currently we're grabbing every new ledger, grouping them by day and then adding it to redis. Since we're already grabbing every ledger from last 30 days, we can use those ledgers to get the last 1hr and last 24hr aggregate data as well. I don't think we'll need any additional calls to Horizon 🤔

Also when you mention "queries", we shouldn't use Big Query here since we need this data for both mainnet and testnet (as mentioned here BigQuery doesn't have testnet data).

If any of that doesn't make sense lmk

[Selhar]

I see, what i mean by multiple calls is that on the front end graph we have an interval, we show data for 1PM, 1:15PM, 1:30PM, 1:45PM and 2PM. How do we get that data for the intervals?

Also when you mention "queries", we shouldn't use Big Query #220 (comment) since we need this data for both mainnet and testnet (as mentioned here BigQuery doesn't have testnet data).

That makes sense!

[acharb]

oh I see, hmm I think we can return the data sorted by minute? Similar to how currently last 30 days are sorted by day: https://dashboard.stellar.org/api/ledgers/public .

[Selhar]

oh I see, hmm I think we can return the data sorted by minute? Similar to how currently last 30 days are sorted by day: https://dashboard.stellar.org/api/ledgers/public .

Yeah that's what i was thinking, but how do we get data with that much granularity? Do we have an endpoint that could serve between minute ranges?

[acharb]

the new ledgers are returned every 5s, and give a closed_at field. So we can use that closed_at to organize. See

dashboard/backend/ledgers.ts

Line 117 in 17cbc05

const date: string = formatDate(ledger.closed_at);

[Selhar]

That makes sense, thanks a lot!

[Selhar]

#287 solves this issue.