A good place to start would be SQL Runner - from there you can query the SQL directly against your database to test the syntax and to make sure the results return as expected. Once you’ve tested the query, you can create a LookML view file and add it to your project by following the steps as outlined here
- Use SQL Runner to create a SQL query that you want to use for a derived table.
Click Add to Project from the gear menu in the upper right.
- Select the project you want to add this derived table to.
- Enter a view name for the derived table.
- Click Add to add the query as a derived table in your project.
- Looker will switch to Development Mode (if not yet in development mode) and open the new derived table’s view in the selected project. If you selected a project that uses YAML-based LookML, Looker will provide the derived table in YAML-based LookML. If you selected a project that uses New LookML, Looker will provide the derived table in New LookML.
Alternatively to the SQL derived table outlined above, you can generate a Native (LookML-based) derived table from SQL runner with your SQL query:
- Use SQL Runner to double-check your query.
- From here, instead of Add to Project , click Get Derived Table LookML
- Copy the LookML from the window that pops up and manually paste it into your project, or click the add it to your project link to add the LookML to your project using the same procedure as above.
Once you have the new view file with your derived table, Looker automatically creates dimensions and measures to correspond with the table fields in the query. You can then join the table into your model as any other view!
One thing to keep in mind is that you cannot refer to LookML views, dimensions, and measures in the SQL of a SQL-based derived table.
Here is another helpful doc about derived tables, outlining the two different methods above (Native derived table vs. SQL derived table).
I hope this helps provide next steps - please feel free to contact email@example.com with any questions you may have!