https://pinot.apache.org/ logo
#getting-started
Title
# getting-started
x

xtrntr

09/21/2021, 6:05 AM
hello, i’ll like to clarify the usage of dimension tables - can i use the columns in
dimTable
but not
factTable
to filter in the WHERE clause? https://docs.google.com/document/d/1InWmxbRqwcqIakzvoEWHLxtX4XR9H5L01256EbAUHV8/edit#
Copy code
Table factTable:
string    uuid
int       metric
timestamp event_time
string    status
Copy code
Table dimTable:
string uuid
string name 
string country
Copy code
SELECT
  f.uuid,
  d.name,
  d.country,
  abs(sum(m.metric)) as sum_metric
FROM
  factTable f join dimTable d on f.uuid = d.uuid
WHERE 
  d.country in ('USA')
GROUP BY
  1,
  2,
  3
ORDER BY
  2
bump, hopefully there’s an answer for this 🙂
m

Mayank

09/22/2021, 3:29 AM
@Jackie ^^
k

Kishore G

09/22/2021, 3:40 AM
@Yupeng Fu might also be able to answer this
You cannot use join syntax. You will have to use the lookup udf
x

xtrntr

09/22/2021, 6:11 AM
ah right so it’ll be:
Copy code
SELECT
  f.uuid,
  lookUp('dimTable', 'name', 'uuid', f.uuid) as name,
  lookUp('dimTable', 'country', 'uuid', f.uuid) as country,
  abs(sum(m.metric)) as sum_metric
FROM
  factTable f 
WHERE 
  ...
GROUP BY
  1,
  2,
  3
ORDER BY
k

Kishore G

09/22/2021, 6:15 AM
yes