Steampipe Data Model

[misterek]

I've been playing around with a steam pipe plugin (for learning, not actually useful at the moment), and was curious what folk there might to represent this relationship.

I'm workin on a plugin for this API: https://studio.apollographql.com/public/star-wars-swapi/variant/current/home

And I was curious what might be the cleanest way to model the connection.

For instance, there are people, who have connections to movies, and starships.
Starships with connections to movies and people.
etc.. Basically, a bunch of many too many relationships.

So my two questions probably are:

• What would your tables look like in this case?
• When would you load things?

For instance, if someone did "select * from starwars_people", you'd get essentially the foreign keys for the movies. You could create a join table. But... it's be sparsely populated at that point. I'm also not sure how you'd fully populate it without a lot of calls.

Anyway, was curious if any of the other plugins had similar patterns I could take a look at.

[cbruno10]

Hey @misterek , great to hear you're creating a new plugin!

We have a few plugins that are based off of a GraphQL API:

• GitHub, code available at https://github.com/turbot/steampipe-plugin-github
• Linear, code available at https://github.com/turbot/steampipe-plugin-linear
• Turbot Guardrails, code available at https://github.com/turbot/steampipe-plugin-guardrails

The GitHub and Linear plugins are newer and are using some of our updated patterns, so my suggestion would be to have a look at those first.

As part of developing those plugins, we found out some ways to work well with GraphQL APIs in general and had created some guidelines (included below) that may be helpful.

If you still have more questions, please let us know and we're happy to discuss more specifics!

---

General

• We should take advantage of GraphQL to reduce the number of requests due to rate limits
  • GitHub has min 1 point/request and uses points/100 calculations
  • Linear has rate limits of 1500 requests/hour and 300,000 points/hour
• Usually getting info out of nodes instead of edges is better, except when edges contains additional data
• Key columns and limits should be used in GraphQL query filters/arguments when available
• Parent hydrates should not usually be used due to the increased number of requests
• If a resource has a parent that can be listed, include the plural parent in the GraphQL query to allow fewer requests, e.g., GitHub issues should use organization { repositories }, not repository, which allows users to list all issues for all repos in an organization more easily

Page Size

• Page size should be set at the maximum allowed to reduce number of requests, except when costs are calculated based on page size instead of actual number of items returned (like in Linear)
  • If cost is based off of page size, set default page size lower, e.g., 50, and add a config argument to allow users to modify it.
  • The plugin should then universally or per table lower page size if the given page_size config argument value is higher than the max allowed.
  • If page_size is set:
    • If limit is specified, use the lower of the limit and page_size values (may reduce number of requests if there are fewer items than the limit value)
    • If limit is not specified, use page_size
  • If page_size is not set:
    • If limit is specified, use the lower of the limit and default page size values (may reduce number of requests if there are fewer items than the limit value)
    • If limit is not specified, use the default page size

Columns

• Paginated fields should be their own table and should not be included in parent object tables as columns due to complexity of nested GraphQL queries
  • Exceptions can be made when the maximum page size is higher than the possible or typical number of items, so it's likely to get all items in the first page, and it is beneficial to get as many fields as possible for the rate limit, e.g., users allowed to bypass restrictions on a GitHub branch protection rule where the maximum page size is 100
  • Another case for exceptions is when paginated data includes references to related objects, e.g., in Linear issueLabel object, the issues field should be added to the GraphQL query to put issue IDs in a issues or issue_ids column
• All flat fields should be included in all GraphQL queries
• Nested fields should only be included by default if they do not increase costs
  • If not included by default, the GraphQL query should be updated based on d.QueryContext.Columns, where d is QueryData
  • If a nested field has paginated fields, those fields should not be included
  • For GitHub, they do not increase costs (include by default)
  • For Linear, they do increase costs (do not include by default)
• Nested fields should be included as JSON columns to prevent too many columns being added

Search Queries

• We should have tables for search queries as well
• Search queries are helpful for getting large sets of data, but often have limits
  • GitHub search queries are limited to 1000 results (even when paginated)
  • Linear has search queries in alpha, but no published limits I could find

[misterek]

Thank you, that's great information. I'll keep playing around with it.