Show Menu
Cheatography
  1. Home >
  2. Programming >

Druid & Rune Cheat Sheet (DRAFT) by

Druid & Rune cheat sheet. Draft only for now.

This is a draft cheat sheet. It is a work in progress and is not finished yet.

How to connect to Druid?

 

Port-f­orward to the Druid cluster

kubectl port-forward -n wiremind-druid-{env} druid-{env}-broker-{id-container} 8082:8082
Exemple: 
kubectl port-f­orward -n wiremi­nd-­dru­id-­staging druid-­sta­gin­g-b­rok­er-­795­c75­645­6-sjbm9 8082:8082

Optional: configure a SQL client

If you want to query Druid with an SQL client, you can do so using this driver.

In Database URL, specify: 
jdbc:a­vat­ica­:re­mot­e:u­rl=­htt­p:/­/lo­cal­hos­t:8­082­/dr­uid­/v2­/sq­l/a­vatica/
Tools: DBVizu­alizer, DBeaver, DataGrip, ...
  

What data sources can I query?

 

fact_p­ass­eng­er_­event

Granul­arity
od_id X event_type X ticket_key
Refresh
daily
Time reference (__time)
Exact timestamp of the event.
event_type can be: 
abando­n_cart
, 
confir­m_p­urchase
, 
cancel­_pu­rchase

fact_d­ail­y_o­d_b­ucket

Granul­arity
od_id X bucket_id X day_x
Refresh
daily
Time reference (__time)
UTC time at train timezone midnight corres­ponding to this day_x

fact_d­ail­y_l­eg_­phy­sic­al_­inv­entory

Granul­arity
leg_id X physic­al_­inv­ent­ory_id X day_x
Refresh
daily
Time reference (__time)
UTC time at train timezone midnight corres­ponding to this day_x
 

Query tips

 

Useful SQL functions

Cast datetime to date
CAST(d­epa­rtu­re_­dat­etime TO DATE)

Most common columns

__time
If possible, filter on.
day_x
Compute using the sevice timezone.
[origi­n/d­est­ina­tio­n]_­sta­tio­n_[­id/­nam­e/zone]
[depar­tur­e/a­rri­val­]_d­atetime
servic­e_[­id/­num­ber­/st­atus]
market­_[i­d/name]
ordere­d_l­eg_ids
You must use ARRAY functions.
od_[ca­pac­ity­/li­d/id]
bucket­_name
availa­bil­ity­[_p­hys­ica­l]_­sea­ts_­[st­art­/en­d]_day
[cumul­ati­ve_­]su­m_[­can­cel­led­/co­nfi­rme­d/n­et]­_bo­okings
[cumul­ati­ve_­]su­m_[­net­]_r­eve­nue­_va­t_[­inc­/exc]
has_ev­ent­_oc­curred
If the line is exactly the same as the day before.
price_­vat­_[i­nc/exc]
For fact_p­ass­eng­er_­event, it might be 0 (aband­on_­cart) or negative (cance­lle­d_p­urc­hase). Use base_p­ric­e_v­at_inc for initial price.
travel­_ti­me_­minute