{"action":"created","issue":{"url":"https://api.github.com/repos/fishtown-analytics/dbt/issues/911","repository_url":"https://api.github.com/repos/fishtown-analytics/dbt","labels_url":"https://api.github.com/repos/fishtown-analytics/dbt/issues/911/labels{/name}","comments_url":"https://api.github.com/repos/fishtown-analytics/dbt/issues/911/comments","events_url":"https://api.github.com/repos/fishtown-analytics/dbt/issues/911/events","html_url":"https://github.com/fishtown-analytics/dbt/issues/911","id":348899928,"node_id":"MDU6SXNzdWUzNDg4OTk5Mjg=","number":911,"title":"Cache relations in DBT","user":{"login":"beckjake","id":452302,"node_id":"MDQ6VXNlcjQ1MjMwMg==","avatar_url":"https://avatars2.githubusercontent.com/u/452302?v=4","gravatar_id":"","url":"https://api.github.com/users/beckjake","html_url":"https://github.com/beckjake","followers_url":"https://api.github.com/users/beckjake/followers","following_url":"https://api.github.com/users/beckjake/following{/other_user}","gists_url":"https://api.github.com/users/beckjake/gists{/gist_id}","starred_url":"https://api.github.com/users/beckjake/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/beckjake/subscriptions","organizations_url":"https://api.github.com/users/beckjake/orgs","repos_url":"https://api.github.com/users/beckjake/repos","events_url":"https://api.github.com/users/beckjake/events{/privacy}","received_events_url":"https://api.github.com/users/beckjake/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":9,"created_at":"2018-08-08T21:32:32Z","updated_at":"2018-08-10T19:52:21Z","closed_at":null,"author_association":"CONTRIBUTOR","body":"## Relation Caching plan\r\nThis is a general roadmap for how we can implement a relation caching mechanism for dbt adapters. Instead of `list_relations()` going out to the database every time, we can maintain a local cache in between adapter invocations. As long as we're careful to invalidate and update the cache as appropriate, we can save a lot of time on metadata queries.\r\n\r\n### New concepts\r\nRelations will be cached on a per-schema basis.\r\n\r\nFor a given schema, its cache can be fully valid, partially valid, or not present at all.\r\n\r\nA fully valid cache means that no changes have been made since the last call to list_relations except via `drop_relation`, `truncate_relation`, or `rename_relation` - so the cache is 100% up to date (see below for new methods around this).\r\n\t- note that creating a new relation will mean it isn't fully valid under this plan\r\n\r\nA partially valid cache means that there may be more relations that aren't in the list of relations\r\n\t- so for any calls to `list_relations` or `get_relation` with an identifier that isn't present, the cache must be refreshed\r\n\r\nWhen a relation in a schema is invalidated, it is removed from the cache, and the cache is marked as partially valid.\r\n\r\n\r\n### New types/modules\r\n\r\n#### new type `CacheInvalid(Exception)`\r\nInternal control-flow exception. Raised when the cache is not fully valid and the caller needs to re-populate the cache and call again.\r\n\r\n#### new type `RelationsCache`\r\nStores per-schema relations caches\r\n\r\n##### methods\r\n- `__contains__(self, schema)`:\r\n\t- Returns True if the schema is cached.\r\n- `clear(self)`: Clears the schema for all schemas.\r\n\t- No return value\r\n- `clear_schema(self, schema)`: Clears the schema if it's present, otherwise does nothing.\r\n\t- No return value.\r\n- `invalidate_schema(self, schema)`: Marks a schema as not being fully valid, if it's present. Otherwise does nothing.\r\n\t- No return value.\r\n- `rename_relation(self, from_relation, to_relation)`: Replace the old relation with a new one, if present. Otherwise insert it (or error?).\r\n\t- No return value.\r\n- `clear_relation(self, relation)`: Invalidate a single relation by removing it from the schema cache, if present. Otherwise does nothing (or error?).\r\n\t- No return value.\r\n- `list_relations(self, schema)`: See SchemaCache.list_relations\r\n\t- Raises `CacheInvalid` if the schema is not in the cache at all\r\n- `get_relation(self, schema, search)`: See SchemaCache.get_relation.\r\n- `add_relation(self, relation)`: See SchemaCache.add_relation.\r\n\t- Creates a new partially valid SchemaCache if none exists.\r\n\r\n\r\n#### new (internal) type `SchemaCache`\r\nStore a single schema's relations caches. I don't think this has to be exposed anywhere outside of the `RelationsCache`.\r\n\r\n##### attributes\r\n- `fully_valid`: `bool` indicating that the cache is a complete representation of the relations in the schema\r\n\r\n##### methods\r\n- `__contains__(self, relation)`:\r\n\t- Returns True if the relation is in the cache\r\n- `clear_relation(self, identifier)`: Invalidate a single relation by removing it from the schema cache. (TODO: ensure we don't need realationship_type!)\r\n\t- No return value.\r\n- `add_relation(self, relation)`: Add a new relation to the schema cache.\r\n\t- No return value.\r\n- `list_relations(self)`: Get the list of relations cached for this schema\r\n\t- Returns a list of Relations if the cache is fully valid\r\n\t- Raises `CacheInvalid` if the cache is not fully valid\r\n- `get_relation(self, search)`: Search for the relation in the cache.\r\n\t- Returns a list of Relations that match (caller must handle duplicates).\r\n\t- Raises `CacheInvalid` if the relation is not found and the schema cache is partially valid, raise `CacheInvalid` instead of returning an empty list.\r\n\t- if the relation is found and the schema cache is partially valid, we assume there are no dupliates since we filled the cache.\r\n\r\n### Changes to existing code\r\n\r\n#### Update `DefaultAdapter` and subclasses\r\n- They will now call into the RelationsCache to update it in `drop_relation`, `rename_relation`\r\n\t- `drop_relation` does a cascade, so we will have to invalidate the full schema on drops\r\n\t\t- do we have to invalidate all schemas?\r\n\t- `rename_relation` should be fine.\r\n- They will look at the cache for `get_relation`/`list_relations`, catch any `CacheInvalid` and update the cache appropriately\r\n- Add an `execute_hook(cls, profile, sql, ...)` method that parses the SQL before running\r\n\t- detect 'destructive' changes\r\n\t\t- `drop ...`\r\n\t\t- `alter ...`\r\n\t\t- invalidate just the schema if we can tell it matches, otherwise we need to invalidate the full cache (with `cascade`, does it have to be the entire cache all the time?)\r\n\t- call execute()\r\n- Add an `execute_model`(cls, profile, schema, identifier, relation_type, sql, ...) method\r\n\t- add the new relation (it will always be an addition because we rename existing tables)\r\n\t- call execute()\r\n\r\n#### Update Runner.run_hooks\r\n- Instead of calling `adapter.execute()` in `run_hooks`, call the new `execute_hook` above\r\n\r\n\r\n#### Update macros\r\n- Macros that create things will also need to add those relations to the cache:\r\n\t- `create_view_as`\r\n\t- `create_table_as`\r\n\t- `create_archive_table`?\r\n\r\n#### Add new module-level fields\r\n- `dbt/adapters/__init__.py` gets a new field, `relations_cache` that is initialized to an empty RelationCache\r\n\t- I think integration tests will have to clear this in setup/teardown\r\n\r\n\r\n### Alternatives\r\nInstead of raising a control-flow exception, we can change anything that would raise `CacheInvalid` to return `None` and make sure we always properly inspect return values of functions.\r\n\r\nInstead of putting the relations cache at the global level, instantaite adapters where we currently return the types, and make everything instance methods instead of classmethods.\r\n\r\nInstead of updating macros, we could try to introspect into the query inside adapter.execute. We don't know the schema there, or even that we are executing a model, so we'd have to extract it from the parsed query.\r\n\r\nInstead of adding `execute_model` and changing the macros to call it, the macros could call the\r\nWe could also update the macros to call a new `execute_model` like the `execute_hook` described above, and handle caching there where we can pass along schemas and such.\r\n\r\nWe might not need to have a per-schema cache if we only ever care about the target schema, but I think it might matter for archives? If it doesn't, we can get rid of the `RelationCache` and combine it into the `SchemaCache`"},"comment":{"url":"https://api.github.com/repos/fishtown-analytics/dbt/issues/comments/412188016","html_url":"https://github.com/fishtown-analytics/dbt/issues/911#issuecomment-412188016","issue_url":"https://api.github.com/repos/fishtown-analytics/dbt/issues/911","id":412188016,"node_id":"MDEyOklzc3VlQ29tbWVudDQxMjE4ODAxNg==","user":{"login":"drewbanin","id":1541139,"node_id":"MDQ6VXNlcjE1NDExMzk=","avatar_url":"https://avatars2.githubusercontent.com/u/1541139?v=4","gravatar_id":"","url":"https://api.github.com/users/drewbanin","html_url":"https://github.com/drewbanin","followers_url":"https://api.github.com/users/drewbanin/followers","following_url":"https://api.github.com/users/drewbanin/following{/other_user}","gists_url":"https://api.github.com/users/drewbanin/gists{/gist_id}","starred_url":"https://api.github.com/users/drewbanin/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/drewbanin/subscriptions","organizations_url":"https://api.github.com/users/drewbanin/orgs","repos_url":"https://api.github.com/users/drewbanin/repos","events_url":"https://api.github.com/users/drewbanin/events{/privacy}","received_events_url":"https://api.github.com/users/drewbanin/received_events","type":"User","site_admin":false},"created_at":"2018-08-10T19:52:21Z","updated_at":"2018-08-10T19:52:21Z","author_association":"CONTRIBUTOR","body":"@beckjake that's right. I wanted to investigate if there's any sort of difference in how postgres/redshift handles the drop-and-swap if we do it inside of a transaction. I don't think it makes any difference, but I wanted to confirm."}}
{"actor":{"display_login":"drewbanin"}}