Julian Hyde created CALCITE-6443:
------------------------------------
Summary: Create view based on LookML model
Key: CALCITE-6443
URL: https://issues.apache.org/jira/browse/CALCITE-6443
Project: Calcite
Issue Type: Improvement
Reporter: Julian Hyde
Create a view based on LookML model (more specifically, based on an explore in
a LookML model).
Note: I have no plans to implement this in open source, but it's helpful to set
out the requirements.
For example, the following statement using the LOOKML_VIEW table function
{code}
CREATE VIEW OrdersCube AS
SELECT *
FROM TABLE LOOKML_VIEW('orders', '
model: model_name {
view: orders {
sql: SELECT * FROM orders ;;
dimension: id {
primary_key: yes
type: number
}
dimension_group: created {
type: time
timeframes: [time, date, week, month]
sql: created_at;;
}
dimension: amount {
type: number
value_format: “0.00”
}
measure: count
measure: total_amount {
type: sum
sql: amount ;;
}
}
view: customers {
sql_table_name: customers
label: “Customer”
dimension: id {
primary_key: yes
}
dimension: zipcode
dimension: state
}
explore: orders {
from: orders
join: customers {
sql_on: customers.id = orders.customer_id ;;
}
}
}');
{code}
is equivalent to the following:
{code}
CREATE VIEW OrdersCube AS
SELECT *
FROM (
SELECT `orders.id`, // PK
`orders.customer_id`,
TIME(created_at) AS `orders.created_time`,
DATE(created_at) AS `orders.created_date`,
WEEK(created_at) AS `orders.created_week`,
MONTH(created_at) AS `orders.created_month`,
amount AS `orders.amount`, // value_format: “0.00”
COUNT(*) AS MEASURE `orders.count`,
SUM(amount) AS MEASURE `orders.total_amount`
FROM orders) AS orders
JOIN (
SELECT id AS `customers.id`, // PK
zip_code AS `customers.zip_code`,
state AS `customers.state`
FROM customers) AS customers // label: “Customer”
ON `customers.id` = `orders.customer_id`;
{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
