Question

LookML DT to SQL and PRIMARY KEY

  • 27 June 2022
  • 0 replies
  • 60 views

If I have this LookML code:

view: active_orders {
derived_table: {
sql:
SELECT o.id
FROM orders o
WHERE order.date >= {% date_start report_date_filter %}
AND order.date <= {% date_end report_date_filter %}
;;

indexes: ["id"]
}

dimension: id {
hidden: yes
primary_key: yes
type: number
sql: ${TABLE}.id ;;
}
}

Why Looker renders this query for MySQL?

  CREATE TEMPORARY TABLE active_orders (INDEX(id)) 
SELECT o.id
FROM orders o
WHERE order.date >= DATE("2020-01-01")
AND order.date <= DATE("2020-12-31")

If I provided info about what dimension is the primary key and the `sql:` tag references a single column, then why it not uses that info to generate this?

CREATE TEMPORARY TABLE active_orders (PRIMARY KEY(id)) 
SELECT o.id
FROM orders o
WHERE order.date >= DATE("2020-01-01")
AND order.date <= DATE("2020-12-31")

When InnoDB engine is used, this would prevent to create an extra internal primary key and this will save memory. The table creation would might be a bit slower since it would need to order by the `id`, but it would increase the performance on further joins. At least it would be great if I could add some kind of tag or configuration to choose between both code generation.

It is currently possible to choose if someone want to generate `PRIMARY KEY(id) rather than `INDEX(id)`?

Thanks.


0 replies

Be the first to reply!

Reply