search

pfmc-assessments / nwfscSurvey

Tool to pull and process NWFSC West Coast groundfish survey data for use in PFMC groundfish stock assessments
http://pfmc-assessments.github.io/nwfscSurvey/
10 stars 8 forks source link

list all data tables available in the warehouse #59

Open kellijohnson-NOAA opened 2 years ago

kellijohnson-NOAA commented 2 years ago

Is there a list of data tables that are available in the warehouse and how are other groups accessing these tables? This information, I think, is key to building a base function that other functions such as PullCatch.fn can use.

Curt-Whitmire-NOAA commented 2 years ago

@kellijohnson-NOAA +1 to add this functionality to the Data Warehouse. Where did you want this list returned?

For those who are not familiar, a Metadata page exists, but it is not comprehensive. For example, most of the Warehouse dimension tables are not listed and some of those include useful information to help users build API calls (e.g., taxonomy_dim).

kellijohnson-NOAA commented 2 years ago

Seems like the best option would be to make the Metadata page more comprehensive. Is this an option, then we could figure out some way to list of all the entries programmatically? I am hesitant to save a static list within this package because of the high likelihood of it becoming outdated.

Curt-Whitmire-NOAA commented 2 years ago

I'll ask Mak to explore how the Metadata page is generated. I believe the fields and definitions are scraped directly from the Postgres database objects, but I suspect the general page format is hard-coded.

Curt-Whitmire-NOAA commented 2 years ago

I just checked the Warehouse code for the Metadata page, and appears set to only show fact tables. This is likely by design as many of the other tables (e.g., dimension) are not relevant to the user. One exception is the taxonomy_dim table, but there are likely others (e.g., port_dim).

Perhaps we could have a chat with Data Team and STAT members on which additional tables we'd like to expose?

kellijohnson-NOAA commented 2 years ago

Perhaps @Curt-Whitmire-NOAA it would be best to just have a short conversation between the two of us first because my mind immediately goes to ... why not just expose them all?

kellijohnson-NOAA commented 2 years ago

Agreed to expose the species taxonomy list as a first step.

Curt-Whitmire-NOAA commented 2 years ago

@kellijohnson-NOAA , I posted a related issue to the Warehouse repo.

Curt-Whitmire-NOAA commented 1 year ago

@Curt-Whitmire-NOAA just an FYI to compare the list of source objects returned by the API and the dw schema. Use the find on page utility, searching for "name"