Caching and Denormalising User Metadata
Last updated
Last updated
Various data products in sunbird depends on the user metadata which are generated on joining different tables from core cassandra and LP cassandra. The logic for joining the table and fetching the information per user type is complex.
Fetch the information from cassandra table, compute all the required fields in the flink job (user-cache-updater job) and update all the fields to the redis cache.
1
User-ID
String
User
It indicates user unique Identifier
2
Mobile Number
String
User
User phone number in an encrypted format
3
Email ID
String
User
User mail id in an encrypted format
4
ID
String
User
User unique identifier
5
First Name
String
User
User first name
6
Last Name
String
User
User Last Name
7
Phone Verified
String
User
It indicates whether user is verified the phone number or not
8
Email Verified
String
User
It indicates whether user is verified the email or not
9
Flags Value
Int
User
10
Framework
Map[String, List[String]]
User
User framework
11
Rootorgid
String
User
User root org id (can be used to differentiate between custodian and state user)
12
CreatedBy
String
User
User created By
13
Subject
List[String]
User
User subjects
14
Language
List[String]
User
User Language
15
Grade
List[String]
User
User grades
16
Roles
List[String]
User
User roles
17
Status
Int
User
User status
18
Webpages
List[Map[String, String]]
User
19
Createddate
String
User
User created date
20
Emailverified
Boolean
User
User email is verified or not
21
Isdeleted
Boolean
User
User is deleted or not
22
Locationids
List[Strings]
User
If the user is Self Signed Up (custodian) user: USER.locationids
If the user is tenant user: ORGANISATION.locationids
23
Updateddate
String
User
User last updated date
24
Profilevisibility
String
User
User profile visibility
25
Loginid
String
User
User login id
26
Username
String
User
It’s a combination of user first name and last name columns
27
External-ID
String
User_external_identity
If the user is a self signed up user in the custodian org then the user’s self declared ID will be the value to the field.
Filter the User_external_identity.idtype='declared-ext-id
'
Join with ORG table with condition User_external_identity.provider=ORG.channel and fetch User_external_identity.userid , User_external_identity.externalID
If the user is a state user then the tenant provided External ID will be the value to the field.
Join USER and User_external_identity table with User_external_identity.idType =User.channel and User_external_identity.provider=User.channel and fetch User_external_identity.userid , User_external_identity.externalID
28
School Name
String
Custodian User: User_external_identity State User: Organisation
If the user is Self Signed Up (custodian) user then user’s self declared sub org Information code will be the value to the field.
User_external_identity.idtype='declared-school-name' anf fetch User_external_identity.externalid,userid
If the user is state user then tenant provided sub org Information will be the values.
Join on User.userId = User_org.userId and then on get User_org.orgId and then join with Organisation.id where isRootOrg = false and fetch the Organisation.orgcode and ORG.orgname as school_name
29
School UDISE Code
String
Custodian User: User_external_identity State User: Organisation
If the user is Self Signed Up (custodian) user then user’s self declared sub org Information code will be the value to the field.
User_external_identity.idtype='declared-school-udise-code' and fetch User_external_identity.externalid,userid
If the user is tenant user then tenant provided sub org Information will be the values.
Join on User.userId = User_org.userId and then on get User_org.orgId and then join with ORGANISATION.id where isRootOrg = false and fetch the ORGANISATION.orgcode and ORG.orgname as school_name
30
State Name
String
Location
If the user is Self Signed Up (custodian) user then user’s self declared location will be the value to the field
USER.locationids=LOCATION.id and LOCATION.type='state/' and fetch the name as LOCATION.{state_name},USER.userid
If the user is tenant user then tenant location will be selected
Join ORGANISATION and location table having condition: ORG.locationids=Location.id && Location.type='state and fetch the Location.name, ORG.id
Join the Dataframe given in (i) and user table with condition: ORG.id = USER.rootorgid && ORG.isrootorg=true and get the USER.userid, Location.name
31
District Name
String
Location
If the user is Self Signed Up (custodian) user then user’s self declared location will be the value to the field
USER.locationids=LOCATION.id and LOCATION.type='district' and fetch the name as LOCATION.{district_name},USER.userid
If the user is tenant user then then tenant location will be selected
Join ORGANISATION and location table having condition: ORG.locationids=Location.id && Location.type='district and fetch the Location.name, ORG.id
Join the Dataframe given in (i) and user table with condition: ORG.id = USER.rootorgid && ORG.isrootorg=true and get the USER.userid, Location.name
32
Block Name
String
Location
If the user is Self Signed Up (custodian) user then user’s self declared location will be the value to the field
USER.locationids=LOCATION.id and LOCATION.type='block' and fetch the name as LOCATION.{block_name},USER.userid
If the user is tenant user then tenant location will be the value to the field
Join ORGANISATION and location table having condition: ORG.locationids=Location.id && Location.type='block' and fetch the Location.name, ORG.id
Join the Dataframe given in (i) and user table with condition: ORG.id = USER.rootorgid && ORG.isrootorg=true and get the USER.userid, Location.name