[Retired] Using Native Derived Tables

[lloydtabb]

This article has been retired as the information is now in documentation on this page.

If you reached here from the product, our apologies – we’re working on getting that link redirected to this LookML reference page.

[quinn_morrison]

Hi @devwiredau,

I sent you an email, but it will be beneficial to post a response here as well.
As of release 5.0, Looker now officially supports timezone conversion for NDTs (you can check this out in our release notes.

There is now a timezone parameter supported in the explore_source to convert to a consistent timezone or, for ephemeral derived tables, to the timezone your query is in. Not specifying a timezone will default to no timezone conversion (AKA the database timezone).

Thanks!

Quinn

[Stephen_Bronste]

IMO this is a really big limitation to this feature which I think 1) should be noted at the top of the page, and 2) will hopefully be addressed by Looker soon.

Our standard workflow for creating a new DT is:

1. can we calculate the column we need using the existing explore & underlying views?
2. if no, create DT to aggregate as needed - and if NDT, using that same explore
3. join DT back to the original explore

@quinn_morrison I think you are saying that the only way to commit the code created in this process is to turn off LookML validation? Does that mean we are turning off LookML validation forever for all commits?

[lloydtabb]

@Stephen_Bronste the best practice in complex models with NDTs is to put your explores in separate files. You can do this by creating a new file in a project and simply giving it the name foo.explore.lkml. Once you do this, include all the views that the explore needs.

In the views that are NDTs, include the explore files. Everything will validate properly. Here area couple of examples (and sorry for the compressed format. The example I’m drawing from is illustrating a way to write more dense LookML)

You will notice the view user_order_sequence is a native derived table based on the explore order_items and also joined in to order_items.

order_items.explore.lkml

include: "users.view"
include: "inventory_items.view"
include: "products.view"
include: "distribution_centers.view"
include: "user_order_sequence.view"
include: "order_items.view"
include: "user_joins.explore"


explore: order_items {
  extends: [user_joins]
  join: users {relationship:many_to_one  sql_on: ${order_items.user_id} = ${users.id} ;;}
  join: inventory_items {relationship:many_to_one  sql_on: ${order_items.inventory_item_id} = ${inventory_items.id} ;;}
  join: products {relationship:many_to_one  sql_on: ${inventory_items.product_id} = ${products.id} ;;}
  join: distribution_centers {relationship:many_to_one  sql_on: ${products.distribution_center_id} = ${distribution_centers.id} ;;}
  join: user_order_sequence {relationship: many_to_one
    sql_on: ${user_order_sequence.order_id} = ${order_items.order_id} ;;}
}

order_items.view.lkml

include: "order_items.explore"

explore: user_order_sequence {}
view: user_order_sequence {
  derived_table: {
    #persist_for: "2 hours"
    explore_source: order_items {
      column: user_id { field: order_items.user_id}
      column: order_id {field: order_items.order_id}
      column: created_time {field: order_items.created_time}
      derived_column: user_sequence { sql: ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_time) ;;}
    }
  }
  dimension: order_id {hidden:yes}
  dimension: user_sequence {type:number}
}

The full repository is here;

https://github.com/looker/bq_thelook

[Stephen_Bronste]

Thanks @lloydtabb for the detailed reply! I am now up and running with the NDT after I created a separate explore file as per your instructions. And apologies for misreading @quinn_morrison’s comment where she does indeed say to create a new explore, not a new model as I had (mis)read.

Note that, in order to create a file that ended with .lkml, I ultimately selected ‘Create View’ and then changed the name to replace .view with .explore. I first tried ‘Create Document’ but that ended up with an invisible .md at the end.

[markmcdonald]

I need to UNION two tables. Is it possible/good practice to union two NDTs?

[izzymiller]

I don’t believe you can do a UNION within an NDT, but you could union two NDTs together in a non-native derived table, just like:

view: derived_table {
  derived_table: {
    sql: select * from ${an_ndt.SQL_TABLE_NAME} 
            UNION ALL 
            select * from ${another_ndt.SQL_TABLE_NAME};;
  }

Takes you out of the nicely defined NDT ecosystem, but it should work 🙂