# UDP Context store The UDP Context store is a Google CloudSQL instance running [PostgreSQL](https://www.postgresql.org/) that stores and represents all of the context data imported into a UDP instance. It is in the Context store that you will find data that is commonly-modeled and coalesced from the SIS, LMS, and other teaching and learning tools. The UDP Context store also contains the keymap for a particular UDP instance. The keymap maintains the relationships between the UDP Context store’s own primary, surrogate identifiers for context data and the corresponding identifiers from their native systems. ## Accessing the Context store Google [maintains documentation on how to access and use a Google CloudSQL instance](https://cloud.google.com/sql/docs/postgres/quickstart). You can connect to the Context store with any tool that can connect to a PostgreSQL database. Generally speaking, most open-source and commercial SQL clients that support JDBC/ODBC connections will work. ### Unizin Member access Unizin is required to seek authorization from your Institution’s UDP Data steward. Unizin will not grant any individual access to the Context store without explicit authorization from a recognized UDP data steward. [**Request access to the UDP Context store at your institution.**](https://resources.unizin.org/support-and-training#user-help-and-support) Unizin will only create user accounts after it has received authorization from the UDP Data steward. Once approved, a username and password are sent securely to the individual by email. Note that access to your institution's UDP context store is restricted by IP address. Your Institution’s UDP Sponsor team will provide Unizin Support with Institutional IP addresses (or IP blocks) to whitelist. Additionally, an authorized UDP Data Steward at your institution must allow any particular individual access to the UDP. Relevant connection details include:
HostnameProduction environment hostname: context-store.<tenant>.cloud.unizin.org

SIT (systems integration test) environment hostname: context-store.<tenant>.sit.cloud.unizin.org

Replace <tenant> with the primary web domain of your institution. For example, the University of Iowa would replace <tenant> with "uiowa," given that the primary domain name of its institution is uiowa.edu.
Port5432
Databasecontext_store
## Schemas The `context_store` database is composed of multiple schemas. Each schema is composed of views (not tables; see below). In aggregate, the schemas and tables of the `context_store` database make up the UDP Context store as a whole. In PostgreSQL, database schemas act as namespaces in which tables, views, indexes, and other objects reside. By default when you access PostgreSQL, your “search path” (which defines the namespaces you can see) will be set to the “public” schema. You must change your search path to include the “entity” and possibly “keymap” schemas/namespaces. If you are using the psql command-line tool, for example, you would do that with this command: The two critical schemas in the `context_store` database are the `entity` schema and `keymap` schema. There is also a `report` schema which lists metadata tables.
Entity schemaThe entity schema is used to present all of the coalesced context data in the UDP in a relational model
Keymap schemaThe keymap schema is used to present the UDP’s keymapping relationships between the surrogate identifiers created by the UDP (in postgres) and the native identifiers given by a source system.
Report schemaThe report schema is used to present metadata tables. In the future, this schema may present data from other reports or logs from sources such as Airflow, Canvas data, or SIS that don't map into the UDP's context store.
## Views The UDP Context store's `entity` and `keymap` schemas are populated with [views](https://w3resource.com/PostgreSQL/postgresql-views.php), not tables. The views point to tables in other schemas in the database, where entity and keymap data resides. It is important to highlight that you will interact with views over tables because: * Views behave a bit differently than tables in PostgreSQL * Most database clients separate views from tables in their presentation of the contents of a database schema ### Entity schema The views in the `entity` schema correspond to entities of the [Unizin Common Data Model (UCDM).](https://resources.unizin.org/products/data-and-analytics/unizin-data-platform/unizin-common-data-model)

UCDM Entities & elements correspond to Entity schema tables & columns.

There is one view in the `entity` schema per Entity defined in the Unizin Common Data Model’s data dictionary. The columns in the views correspond to the elements defined for the entity in the UCDM. For example, the UCDM defines the [Course offering](https://docs.udp.unizin.org/ucdm/data-dictionary-2.0.html#course_offering) entity as composed by the following elements:
ElementElement codeDefinition
Academic career idacademic_career_idThe foreign key to the Academic career for which this Course offering is primarily offered (note: the Course offering may still be available to other Academic careers at the institution; what is required here is the primary Academic career for which this Course offering is offered).
Academic session idacademic_session_idThe foreign key to the Academic session to which this Course offering belongs.
Available creditsavailable_creditsMeasured in Carnegie units, the amount of credit available to a student who successfully meets the objectives of the Course Offering.
Campus IDcampus_idThe foreign key to the Campus to which this Course offering belongs.
CIP Code 2kcip_code2kA code representing the Classification of Instructional Program for the Course offering.
Created datecreated_dateThe date the Course offering was created.
DescriptiondescriptionThe Course Offering description.
End dateend_dateThe date and time of the last Course offering meeting, which may be different from the last Course section meeting. Note: If more than one meeting pattern exists, the first meeting pattern will be chosen.
Is Public?is_publicIndicates whether the Course offering is publicly visible.
LE statusle_statusFrom the learning environment, the workflow state of the Course offering, usually in relation to its availability for teaching and learning.
Multiple meeting patterns?multiple_meeting_patternsIndicates whether the class has multiple meeting patterns.
NumbernumberThe official reference number portion of a course Id. This number normally designates the level of the course as well as the level of the individual expected to enroll in the course. E.g. "101" in "BIO 101. This is sometimes called the catalog number.
Start datestart_dateThe date and time of the first Course offering meeting, which may be different from the first Course section meeting. Note: If more than one meeting pattern exists, the first meeting pattern will be chosen.
StatusstatusThe workflow state of the Course offering, usually in relation to its availability for teaching and learning.
SubjectsubjectThe alphabetic abbreviation of the academic department or discipline offering the course. It is one part of the total course Id number. E.g., "BIO" in "BIO 101.”
Syllabus contentsyllabus_contentThe syllabus content for this Course offering.
TitletitleThe title of the Course Offering.
The corresponding view in the **entity** schema is called course\_section: {% code fullWidth="true" %} ```sql context_store=> \d entity.course_offering View "entity.course_offering" Column | Type | Collation | Nullable | Default ---------------------------+-----------------------------+-----------+----------+--------- course_offering_id | bigint | | | academic_career_id | bigint | | | academic_session_id | bigint | | | campus_id | bigint | | | available_credits | numeric | | | cip_code2k | text | | | created_date | timestamp without time zone | | | description | text | | | end_date | date | | | is_public | boolean | | | le_status | text | | | multiple_meeting_patterns | boolean | | | number | text | | | start_date | date | | | status | text | | | subject | text | | | syllabus_content | text | | | title | text | | | ``` {% endcode %} Each Element of the Course section Entity maps to a column in the `course_offering` view. Wherever an Element refers to an identifier in another Entity, a foreign key will be defined in the view. For example, Course offering's Academic career element (`academic_career_id`) is a foreign key to the `entity.academic_career` view). You will notice that there is one column not present in the data dictionary that is present in the view: `course_section_id`, or, more generally, the primary key (PK) for the entity view. This is because the table’s PK is not relevant to the conceptual UCDM. Most if not all of context data queries should be able to be run through some combination of `entity.*` views. ### Keymap schema The `keymap` schema contains views that implement the [UDP Keymap](https://resources.unizin.org/products/data-and-analytics/unizin-data-platform/key-concepts/keymap) concept. The `keymap` schema is useful if you want to look up a native Context store ID for an Entity with that Entity’s identifier from another system, like the SIS or LMS. Like the `entity` schema, the `keymap` schema is populated with views whose names correspond to entities in the Unizin Common Data Model. Each view in the `keymap` schema is a keymap that associates data from disparate systems (e.g., SIS, LMS). This schema can be useful if you need to look up the UDP identifier but only have the LMS or SIS identifier in hand. Consider, for example, the Course section entity, whose records will be sourced from many systems, including the SIS and the LMS. Given the multi-system source for Course section records, the Entity's corresponding keymap view contains columns for the identifiers of each source system. In the view definition below, we see that the columns correspond to the SIS and LMS identifiers for a common Course section: {% code fullWidth="true" %} ```sql context_store=> \d keymap.course_section Table "keymap.course_section" Column | Type | Collation | Nullable | Default ------------+-------------------+-----------+----------+--------------------------------------------------- id | bigint | | not null | nextval('keymap.course_section_id_seq'::regclass) sis_int_id | character varying | | | sis_ext_id | character varying | | | lms_int_id | character varying | | | lms_ext_id | character varying | | | ... ``` {% endcode %} The `entity` and `keymap` views share common identifiers. Hence, the primary key for a record in an `entity` view is associated with the primary key of the same value in the corresponding `keymap` view. This fact enables users to look up records in the `entity` schema using any particular identifier (UDP, SIS, LMS, and other identifiers) to lookup their kin from other systems. For example, you can run the following query to return the Canvas global ID for a course section, given its UDP id of `10`: {% code fullWidth="true" %} ```sql context_store=> select kcs.lms_ext_id from entity.course_section ecs join keymap.course_section kcs on ecs.course_section_id = kcs.id where ecs.course_section_id = 10; lms_ext_id ------------ 12345 ``` {% endcode %} ### Report Schema The report schema is used to present metadata tables. In the future, this schema may present data from other reports or logs from sources such as Airflow, Canvas data, or SIS that may not map into the UDP's context store. You may run the below query to return results for the keymap and entity tables and their last published run information. Note: The dag\_run is intended to display the name of the ingest’s ‘run.’ {% code fullWidth="true" %} ```sql select * from report.publish_info; schema_name | table_name | published | dag_run -------------+------------------------------------------------------+------------+------------ keymap | learning_outcome_item_response | 2022-07-08 | 2022-07-07 keymap | facility | 2022-07-08 | 2022-07-07 keymap | learner_group_membership | 2022-07-08 | 2022-07-07 keymap | room | 2022-07-08 | 2022-07-07 entity | learner_activity_group_grade | 2022-07-08 | 2022-07-07 keymap | academic_minor | 2022-07-08 | 2022-07-07 entity | academic_degree | 2022-07-08 | 2022-07-07 keymap | admission_test_score | 2022-07-08 | 2022-07-07 keymap | learning_outcome_result | 2022-07-08 | 2022-07-07 keymap | grading_period_grade | 2022-07-08 | 2022-07-07 keymap | academic_major | 2022-07-08 | 2022-07-07 keymap | annotation | 2022-07-08 | 2022-07-07 keymap | person_email | 2022-07-08 | 2022-07-07 keymap | quiz_item | 2022-07-08 | 2022-07-07 keymap | conversation_message | 2022-07-08 | 2022-07-07 entity | quiz_item_response | 2022-07-08 | 2022-07-07 entity | institutional_affiliation | 2022-07-08 | 2022-07-07 keymap | academic_term | 2022-07-08 | 2022-07-07 keymap | course_section | 2022-07-08 | 2022-07-07 keymap | course_offering | 2022-07-08 | 2022-07-07 entity | quiz_result | 2022-07-08 | 2022-07-07 keymap | file | 2022-07-08 | 2022-07-07 entity | quiz_item_group | 2022-07-08 | 2022-07-07 entity | grading_period | 2022-07-08 | 2022-07-07 entity | annotation | 2022-07-08 | 2022-07-07 entity | discussion | 2022-07-08 | 2022-07-07 entity | academic_major | 2022-07-08 | 2022-07-07 keymap | quiz_result | 2022-07-08 | 2022-07-07 entity | grading_period_grade | 2022-07-08 | 2022-07-07 keymap | participant_session | 2022-07-08 | 2022-07-07 keymap | academic_degree | 2022-07-08 | 2022-07-07 keymap | wiki_page | 2022-07-08 | 2022-07-07 entity | course_section | 2022-07-08 | 2022-07-07 entity | facility | 2022-07-08 | 2022-07-07 keymap | quiz_item_group | 2022-07-08 | 2022-07-07 entity | academic_organization | 2022-07-08 | 2022-07-07 entity | academic_session | 2022-07-08 | 2022-07-07 entity | participant_session | 2022-07-08 | 2022-07-07 keymap | academic_organization | 2022-07-08 | 2022-07-07 entity | participant_session_role | 2022-07-08 | 2022-07-07 keymap | module_item | 2022-07-08 | 2022-07-07 keymap | learning_outcome_group | 2022-07-08 | 2022-07-07 keymap | participant_session_role | 2022-07-08 | 2022-07-07 keymap | academic_session | 2022-07-08 | 2022-07-07 keymap | learner_activity_result | 2022-07-08 | 2022-07-07 entity | file | 2022-07-08 | 2022-07-07 entity | learner_activity_result | 2022-07-08 | 2022-07-07 keymap | academic_major_specialization | 2022-07-08 | 2022-07-07 entity | course_offering | 2022-07-08 | 2022-07-07 keymap | person_phone_number | 2022-07-08 | 2022-07-07 entity | module_progression | 2022-07-08 | 2022-07-07 entity | module_item | 2022-07-08 | 2022-07-07 keymap | person__academic_degree | 2022-07-08 | 2022-07-07 entity | person__academic_major_specialization__academic_term | 2022-07-08 | 2022-07-07 keymap | role | 2022-07-08 | 2022-07-07 keymap | grading_period | 2022-07-08 | 2022-07-07 entity | quiz_item | 2022-07-08 | 2022-07-07 keymap | learning_outcome_rubric_criteria | 2022-07-08 | 2022-07-07 keymap | learner_activity_override | 2022-07-08 | 2022-07-07 keymap | learner_activity_group_grade | 2022-07-08 | 2022-07-07 keymap | academic_program | 2022-07-08 | 2022-07-07 entity | person__academic_term | 2022-07-08 | 2022-07-07 keymap | module | 2022-07-08 | 2022-07-07 entity | person__academic_minor__academic_term | 2022-07-08 | 2022-07-07 keymap | discussion_entry | 2022-07-08 | 2022-07-07 keymap | conversation | 2022-07-08 | 2022-07-07 keymap | academic_career | 2022-07-08 | 2022-07-07 keymap | grading_period_group | 2022-07-08 | 2022-07-07 entity | academic_program | 2022-07-08 | 2022-07-07 keymap | module_progression | 2022-07-08 | 2022-07-07 entity | wiki | 2022-07-08 | 2022-07-07 entity | wiki_page | 2022-07-08 | 2022-07-07 entity | academic_career | 2022-07-08 | 2022-07-07 entity | conversation | 2022-07-08 | 2022-07-07 entity | grading_period_group | 2022-07-08 | 2022-07-07 entity | module | 2022-07-08 | 2022-07-07 keymap | quiz_item_response | 2022-07-08 | 2022-07-07 entity | learner_activity_override | 2022-07-08 | 2022-07-07 entity | person_email | 2022-07-08 | 2022-07-07 entity | learning_outcome_rubric_criteria | 2022-07-08 | 2022-07-07 entity | conversation_message | 2022-07-08 | 2022-07-07 keymap | module_item_completion | 2022-07-08 | 2022-07-07 entity | person__academic_major__academic_term | 2022-07-08 | 2022-07-07 entity | course_grade | 2022-07-08 | 2022-07-07 entity | discussion_entry | 2022-07-08 | 2022-07-07 keymap | person | 2022-07-08 | 2022-07-07 entity | person__academic_degree | 2022-07-08 | 2022-07-07 entity | learning_outcome_result | 2022-07-08 | 2022-07-07 entity | course_section_enrollment | 2022-07-08 | 2022-07-07 keymap | learner_group | 2022-07-08 | 2022-07-07 keymap | quiz | 2022-07-08 | 2022-07-07 keymap | learner_activity | 2022-07-08 | 2022-07-07 entity | learning_outcome_group | 2022-07-08 | 2022-07-07 entity | person_phone_number | 2022-07-08 | 2022-07-07 keymap | discussion | 2022-07-08 | 2022-07-07 keymap | wiki | 2022-07-08 | 2022-07-07 entity | room | 2022-07-08 | 2022-07-07 keymap | campus | 2022-07-08 | 2022-07-07 entity | role | 2022-07-08 | 2022-07-07 keymap | learner_activity_group | 2022-07-08 | 2022-07-07 entity | module_item_completion | 2022-07-08 | 2022-07-07 keymap | learning_outcome | 2022-07-08 | 2022-07-07 entity | campus | 2022-07-08 | 2022-07-07 entity | learner_group_membership | 2022-07-08 | 2022-07-07 entity | learning_environment_organization | 2022-07-08 | 2022-07-07 keymap | learning_environment_organization | 2022-07-08 | 2022-07-07 entity | academic_major_specialization | 2022-07-08 | 2022-07-07 entity | learning_outcome_item_response | 2022-07-08 | 2022-07-07 entity | learner_group | 2022-07-08 | 2022-07-07 entity | learning_outcome | 2022-07-08 | 2022-07-07 entity | academic_term | 2022-07-08 | 2022-07-07 entity | academic_minor | 2022-07-08 | 2022-07-07 entity | admission_test_score | 2022-07-08 | 2022-07-07 entity | learner_activity_group | 2022-07-08 | 2022-07-07 entity | quiz | 2022-07-08 | 2022-07-07 entity | person | 2022-07-08 | 2022-07-07 entity | learner_activity | 2022-07-08 | 2022-07-07 ``` {% endcode %} ## Materialized Views Materialized views are a snapshot of certain data derived from a specific query in the context\_store. Performance benefits can be realized when using a materialized view as compared to querying the context\_store's schema views. Since this is a snapshot of data, it is automatically refreshed according to a schedule. ### Auto-Refreshed Materialized Views Each day, at 14:00 UTC (9 AM CDT, 8 AM CST), any materialized view with the owner of the institution role is automatically refreshed. {% code fullWidth="true" %} ```sql -- To use the "institution" owner, set the role before -- creating your views ... set role institution; create materialized view example_view as (...); -- ... Or, alter existing views to be owned by 'institution' alter materialized view example_view owner to institution; ``` {% endcode %} These views are refreshed in order based on their name. {% code fullWidth="true" %} ```sql -- If you need to guarantee a particular order, -- name your views with a prefix that sorts well. create materialized view r1_example_view as (...); create materialized view r2_example_view as (...); -- To check the order is as you expect, run the same -- query the scheduled refresh job runs select schemaname, matviewname from pg_matviews where matviewowner = 'institution' order by schemaname, matviewname asc; ``` {% endcode %}