[
https://issues.apache.org/jira/browse/CALCITE-6443?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-6443:
---------------------------------
Description:
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}
On these views we can execute queries that use the measures, for example
{code}
SELECT `customers.state`,
AGGREGATE(`orders.total_amount`)
FROM OrdersCube
WHERE `orders.created_year` = 2023
GROUP BY `customers.state`
ORDER BY 2 DESC LIMIT 5
{code}
was:
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}
> 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
> Priority: Major
>
> 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}
> On these views we can execute queries that use the measures, for example
> {code}
> SELECT `customers.state`,
> AGGREGATE(`orders.total_amount`)
> FROM OrdersCube
> WHERE `orders.created_year` = 2023
> GROUP BY `customers.state`
> ORDER BY 2 DESC LIMIT 5
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
