Extracting the DWH table names for views

Looker Q&A Looker
1

For a project involving a data catalog, we’re looking at the Looker API to retrieve definitions, etc. We want to connect this to the table names in the data warehouse.

My question has been asked a number of times and the response was usually: “Parse the files from the git repository”, but it would be great to add the capability to the API. A question like this one: https://www.googlecloudcommunity.com/gc/Developing-Applications/Looker-API-for-lookml-views/m-p/574170

The only attribute we’re interested in is the “sql_table_name” one for a view. We don’t care much about the rest of the view definition for now.

So maybe this should be treated as a feature request. My findings so far:

The “Get LookML Model Explore” API contains both the view_name and the sql_table_name for the base view in the explore. So that can be used. But the “sql_table_name” for the joins that are mentioned is always empty. That’s a real bummer, because just because those attributes are missing, there’s a lot of complication to handle: git clone/checkout, git authentication, caching the project files, etc.

  1. Can someone look into whether the sql_table_name in the joins should be populated, so this is essentially treated as a bug?
  2. It would be very useful to add a “get_all_views” API call that returns the actual view definitions. This would decomplicate integrating with Looker and return this essential piece of information.

Thanks!

1 Like
2

I asked internally. It is not a bug because the “sql_table_name” in the “joins” section would only be populated if the sql_table_name is overridden as part of the join definition. I’m doing further research.

3

These requests always have more weight coming from a customer. I would suggest opening a feature request for a lookml_model_view(lookml_model_name, [fields]) API call that would list all view metadata including the sql_table_name for the views in a model.

4

An alternate solution is to use the field names from the API call to make sure you get one field from each view, then generate the SQL for a query using run_inline_query(result_format, body with a result format of ‘sql’. Then you can parse the mapping of views to tables from the SQL.