Database in Snowflake

This topic describes how a user can access the taxonomy database in the Snowflake account.

RChilli Taxonomy database is now available on the Snowflake, and a user can access the Taxonomy database using the Snowflake account, see Taxonomy Using Snowsight View.

Advantage of using Snowflake

  • The Taxonomy database available on the Snowflake gets updated on the monthly basis, hence user receives the updated taxonomy database.
  • A manual upload/download of the database is not needed, users can sync the taxonomy database with Snowflake account and to their local database.

Taxonomy Using Snowsight View

Follow the below procedure to access the Taxonomy database from your Snowflake account.

Note: Below graphics from the Snowflake are for reference only and can be changed based on any updates by the Snowflake. Refer Snowflake for the latest graphics.
  1. Sign in to your Reader account in Snowflake, enter your Username, Password, and click Sign in.
    Note: Contact support@rchilli.com for your Snowflake URL, Username, and Password.


  2. Click Go to New UI for the Snowsight view.
    Note: If you want to work in a classic view, refer Taxonomy Using Classic Console View.


  3. If you clicked Remind me later by mistake in the above step, then move to the Snowsight view from classic console view. Click Snowsight in the header of the classic console to go back to the Snowsight view.
    Note:


  4. Click Private Sharing to view all the databases that are shared with you in the Snowflake.
    Note: Make sure you select the role as ACCOUNTADMIN.


  5. On the Shared With You tab, all the databases that are shared with you are available in the Direct Shares section. Click Download icon to view and download the database.

  6. On the Get Data pop-up, you can do the following:
    • Rename the database in the Database name field and make a note of the database.
    • Select and add the roles from the drop-down, all the role that you add here can access the database.

  7. Once the database name is updated in the above step, click Get Data.

  8. Once the database created successfully, click View Database.

  9. On the Databases (navigation panel), you can view the database that is downloaded in the above step. Expand the database to view its schema that is shared with you such as EDUCATION_DEGREE, EDUCATION_INSTITUTE etc. Expand the schema and you can view the table of the schema in the Views drop-down.

    Note: The following schema can be shared with you, connect with support@rchilli.com for the purchase of schemas as necessary:
    1. All Taxonomy Dump
    2. SKILL_JOB
    3. EDUCATION_DEGREE
    4. EDUCATION_INSTITUTE
    5. GEOLOCATION
    6. POSTAL_CODE
    7. SKILLS
    8. JOB_PROFILES

Download CSV from Snowflake

Once you got the taxonomies in the snowflake (above step), you can run SQL query for each table of schema and download the CSV file.
  1. Create Warehouse: Follow below steps and create a warehouse.
    • Go to Admin > Warehouses on the navigation panel and click + Warehouse. On the New Warehouse pop-up, do the following:
      • Enter the Name for the warehouse.
      • Enter the Size for the warehouse.
      • Enter Comment as necessary.
      • Click Create Warehouse.

  2. Click Worksheet on the navigation panel, and click + Worksheet to run the SQL query on the taxonomy database.

  3. If there is no warehouse selected for the worksheet, select a warehouse that is created in step-1 above.

  4. On the No Database Selected drop-down, select the Databases (that is shared with you) and the Schema of the database.
    Note: Database is the name that you chosen while downloading the database, see step-5 above.


  5. Enter SQL query using the table name (see below sample query), click Run icon to run the query.
    select * from EDUCATION
    In above query, EDUCATION is the table name in the EDUCATION_DEGREE schema.
    Click Download icon to download the table CSV file.
    Note:
    • Similarly, you have to run SQL query for all the table of all the schema and download the table CSV file (total 39 table CSV you can download). Refer below table for all queries to download all the table of all the schema.
    • Make sure before running SQL query for table CSV, you have selected the related Database and Schema in step-4.


    Schema Name Table SQL query
    SKILL_JOB ABILITIES; select * from ABILITIES;
    EDUCATION; select * from EDUCATION;
    EDUCATIONTYPE; select * from EDUCATIONTYPE;
    EDURELATEDJOBPROFILES ; select * from EDURELATEDJOBPROFILES;
    JOBPORFILESECTORRELATION; select * from JOBPORFILESECTORRELATION;
    JOBPROFILE; select * from JOBPROFILE;
    JOBPROFILEALIAS; select * from JOBPROFILEALIAS;
    KNOWLEDGE; select * from KNOWLEDGE;
    LANGUAGE; select * from LANGUAGE;
    MULTILANGJOBPROFILE; select * from MULTILANGJOBPROFILE;
    MULTILANGJOBPROFILEALIAS; select * from MULTILANGJOBPROFILEALIAS;
    MULTILANGSKILL; select * from MULTILANGSKILL;
    MULTILANGSKILLALIAS; select * from MULTILANGSKILLALIAS;
    NOCDETAIL; select * from NOCDETAIL;
    NOCJOBPROFILEMAP; select * from NOCJOBPROFILEMAP;
    NOCTITLE; select * from NOCTITLE;
    PROFICIENCYLEVEL; select * from PROFICIENCYLEVEL;
    RCHILLISECTORS; select * from RCHILLISECTORS;
    RCHILLISUBSECTORS; select * from RCHILLISUBSECTORS;
    RELATEDJOBPROFILE; select * from RELATEDJOBPROFILE;
    RELATEDSKILLS; select * from RELATEDSKILLS;
    SKILL; select * from SKILL;
    SKILLALIAS; select * from SKILLALIAS;
    SKILLRELATIONTYPE; select * from SKILLRELATIONTYPE;
    SKILLSECTORRELATION; select * from SKILLSECTORRELATION;
    SKILLTYPE; select * from SKILLTYPE;
    TASKS; select * from TASKS;
    WORKACTIVITIES; select * from WORKACTIVITIES;
    SKILL SKILL select * from SKILL;
    SKILLALIAS select * from SKILLALIAS;
    JOB_PROFILES JOBPROFILE select * from JOBPROFILE;
    JOBPROFILEALIAS select * from JOBPROFILEALIAS;
    GEOLOCATION GEOLOCATIONS select * from GEOLOCATIONS;
    EDUCATION_INSTITUTE EDUCATIONINSTITUTE select * from EDUCATIONINSTITUTE;
    INSTITUTEALIAS select * from INSTITUTEALIAS;
    EDUCATION_DEGREE EDUCATION select * from EDUCATION;
    EDUCATIONALIAS select * from EDUCATIONALIAS;
    POSTAL_CODE POSTALCODE_COUNTRYISO select * from POSTALCODE_COUNTRYISO;
    POSTALCODE_ZIP select * from POSTALCODE_ZIP;
    Note:
    1. You can run SQL query on the taxonomy database, refer SQL Queries for Snowflake to know more details on how to run queries and search on the taxonomy database.
    2. You can run the SQL query in your MYSQL/MSSQL and search the taxonomies as necessary, for more details, refer SQL Queries for MySQL and MSSQL.
    3. Contact support@rchilli.com for Taxonomy Entity Relationship Diagram to know more details about the relationship of taxonomy entities.

SQL Queries for Snowflake

Run the following SQL queries in your Snowflake to do search for the taxonomy.
Note: To execute queries in Snowflake, make sure you done the followings:
  1. Set the role with correct permissions that can execute the queries.
  2. Select the database name from where you want to select the data.
  3. Select the warehouse name.
  4. Select the schema.

  1. Skill Search For English Language
    Note: Use SKILL_JOB Schema.
    1. Skillsearch in Skill Table
      First search Skill in the Skill Table with the below SQL Query:
      Note: In below query, Java skill is searched, similarly, you can search taxonomy for the other skill.
      Select Skill.skillId, Skill.skill, Skill.formattedSkill, Skill.description, SkillType.skillType from Skill, SkillType where Skill.skill = 'Java' 
      AND Skill.skillType = SkillType.skillTypeId AND Skill.status = 1
      


      Result: The above query returns skillId, skill, formattedSkill, Description, and skillType details for the searched skill.

    2. Skillsearch in SkillAlias table
      Note: If you do not find skill in skill table (above query), then search the skill in SkillAlias table. For example, if we search for Java8, it returns empty results from the above query as Java8 is not present in Skill table, therefore, search Java8 in the SkillAlias table.

      Run below SQL query to search skill in the SkillAlias table.

      Select Skill.skillId, Skill.skill, Skill.formattedSkill, Skill.description, SkillType.skillType from Skill, SkillType 
      where Skill.skillId = (select top 1 SkillAlias.skillId from SkillAlias 
      where SkillAlias.skillAlias = 'Java8' ) 
      AND Skill.skillType = SkillType.skillTypeId AND Skill.status = 1
      


    3. Skill Search For Multi Language
      1. Skillsearch in MultiLangSkill Table
        First search Skill in the MultiLangSkill Table with the below SQL Query:
        Note: In below query, JaBa skill is searched and language selected is sr. Similarly, you can search taxonomy for the other skill and language as necessary.
        SELECT Skill.skillId, MultiLangSkill.skill, Skill.formattedSkill, Skill.description, SkillType.skillType
        FROM Skill, MultiLangSkill, SkillType WHERE MultiLangSkill.skillId = Skill.skillId AND Skill.skillType = SkillType.skillTypeId
        AND MultiLangSkill.languageId = (SELECT Language.languageId FROM Language WHERE Language.language = 'sr') 
        AND Skill.status = 1 AND MultiLangSkill.skill = 'Јава'
        


        Result: The above query returns skillId, skill, formattedSkill, Description, and skillType details for the searched skill.

      2. Skillsearch in MultiLangSkillAlias table
        Note: If you do not find skill in MultiLangSkill table (above query), then search the skill in MultiLangSkillAlias table. For example, if you search for JaBa1 , it will returns empty results from the above query as JaBa1 is not present in MultiLangSkill table, therefore search JaBa1 in the MultiLangSkillAlias table.

        Run below SQL query to search skill in the MultiLangSkillAlias table.

        SELECT Skill.skillId, Skill.skill, Skill.formattedSkill, Skill.description, SkillType.skillType FROM Skill, MultiLangSkill, SkillType, MultiLangSkillAlias WHERE MultiLangSkill.skillId = Skill.skillId AND Skill.skillType = SkillType.skillTypeId AND MultiLangSkill.languageId = (SELECT Language.languageId FROM Language WHERE Language.language = 'sr') AND Skill.status = 1 AND MultiLangSkill.skillId = MultiLangSkillAlias.skillId AND MultiLangSkillAlias.skillAlias =  'јава 1'


    4. Sector and Subsector of Skills

      To get Sector and Subsector of Skills, run below query:

      Note: In below query, skillId 33140 is used, you can search for different skillId, see above SQL query on how to get the skillId.
      Select RChilliSectors.sectorId, RChilliSectors.sector, RChilliSubSectors.subSectorId, RChilliSubSectors.subSector from SkillSectorRelation, RChilliSubSectors, RChilliSectors  where RChilliSubSectors.subSectorId = SkillSectorRelation.subSectorId AND RChilliSubSectors.sectorId = RChilliSectors.sectorId AND SkillSectorRelation.skillId = 33140


    5. Skill Alias Search

      To search Skill Alias, run below query:

      Note: In below query, skillId 33140 is used, you can search for different skillId, see above SQL query on how to get the skillId.
      select SkillAlias.skillAlias from SkillAlias where SkillAlias.skillId = 33140


    6. Related Skill Search

      To search Related Skill, run below query:

      Note: In below query, skillId 33140 is used, you can search for different skillId, see above SQL query on how to get the skillId.
      select Skill.skill, SkillType.skillType, SkillRelationType.relationName from RelatedSkills, SkillRelationType, Skill, SkillType where (RelatedSkills.parentSkillId = 33140 AND RelatedSkills.relationId = SkillRelationType.skillRelationTypeId) AND Skill.skillId = RelatedSkills.childSkillId AND Skill.skillType = SkillType.skillTypeId AND Skill.status = 1


    7. Related Job Profile Search

      To search Related Job Profile, run below query:

      Note: In below query, skillId 33140 is used, you can search for different skillId, see above SQL query on how to get the skillId.
      select JobProfile.jobProfile, ProficiencyLevel.level from JobProfile, ProficiencyLevel, RelatedJobProfile where (RelatedJobProfile.skillId = 33140 AND RelatedJobProfile.proficiencyId = ProficiencyLevel.proficiencyId) AND JobProfile.jobProfileId = RelatedJobProfile.jobProfileId


  2. Job Profile Search (For English Language)
    Note: Use JOB_PROFILES table.
    1. Job Profile search in JobProfile table
      First search Job Profile in JobProfile table with the below SQL Query:
      Note: In below query, Java Developer is searched, similarly, you can search taxonomy for the other job profiles.
      Select JobProfile.jobProfileId, JobProfile.jobProfile, JobProfile.formattedJobProfile, JobProfile.description from JobProfile where JobProfile.jobProfile = 'Java Developer' AND JobProfile.status = 1


      Result: The above query returns jobProfileId, jobProfile, formattedJobProfileand, Description details for the searched job profile.

    2. Job Profile search in JobProfileAlias table
      Note: If you do not find job profile in job profile table (above query), then search the job profile in JobProfileAlias table. For example, if you search for Developer Java, it returns empty results from the above query as it is not present in the JobProfile table, therefore, search Developer Java in the JobProfileAlias table.

      Run below SQL query to search job profile in JobProfileAlias table.

      select JobProfile.jobProfileId, JobProfile.jobProfile, JobProfile.formattedJobProfile, JobProfile.description from JobProfileAlias, JobProfile where JobProfileAlias.jobProfileId = JobProfile.jobProfileId AND JobProfileAlias.jobProfileAlias = 'Developer Java' AND JobProfile.status = 1


    3. Job Profile Search For Multi Language
      1. Job Profile search in MultiLangJobProfile Table
        First search job profile in the MultiLangJobProfile Table with the below SQL Query:
        Note: In below query, Programista Java is searched and language selected is pl. Similarly, you can search taxonomy for the other job profile and language as necessary.
        Select JobProfile.jobProfileId, MultiLangJobProfile.jobProfile, JobProfile.formattedJobProfile, JobProfile.description from JobProfile, MultiLangJobProfile where MultiLangJobProfile.jobProfileId = JobProfile.jobProfileId AND MultiLangJobProfile.jobProfile = 'Programista Java' AND MultiLangJobProfile.languageId = (SELECT Language.languageId FROM Language WHERE Language.language = 'pl') AND JobProfile.status = 1


      2. Job Profile search in MultiLangJobProfileAlias Table
        Note: If you do not found job profile in MultiLangJobProfile table (above query), then search the job profile in MultiLangJobProfileAlias table. For example, if you search Programista Java Developer, empty results is returned from the above query as it is not present in the MultiLangJobProfile table. Therefore, search Programista Java Developer in the MultiLangJobProfileAlias table.

        Run below SQL query to search job profile in MultiLangJobProfileAlias table.

        select JobProfile.jobProfileId, JobProfile.jobProfile, JobProfile.formattedJobProfile, JobProfile.description from JobProfile, MultiLangJobProfile, MultiLangJobProfileAlias where MultiLangJobProfile.jobProfileId = MultiLangJobProfileAlias.jobProfileId AND MultiLangJobProfile.jobProfileId = JobProfile.jobProfileId AND MultiLangJobProfileAlias.jobProfileAlias = 'Programista Java Developer' AND MultiLangJobProfile.languageId = (SELECT Language.languageId FROM Language WHERE Language.language = 'pl') AND JobProfile.status = 1


    4. Skills search in Job Profile table

      To search Skills in Job Profile table, run below query:

      Note: In below query, jobProfileId 6838 is used, you can search for different jobProfileId, see above SQL query on how to get the jobProfileId.
      select Skill.skill, ProficiencyLevel.level, SkillType.skillType from JobProfile, RelatedJobProfile, Skill, ProficiencyLevel, SkillType where Skill.skillType = SkillType.skillTypeId AND ProficiencyLevel.proficiencyId = RelatedJobProfile.proficiencyId AND Skill.skillId = RelatedJobProfile.skillId AND JobProfile.jobProfileId = RelatedJobProfile.jobProfileId AND JobProfile.jobProfileId = 6838


    5. Job Profile Alias search

      To search Job Profile Alias, run below query:

      Note: In below query, jobProfileId 6838 is used, you can search for different jobProfileId, see above SQL query on how to get the jobProfileId.
      Select jobProfileAlias from JobProfileAlias where JobProfileAlias.jobProfileId =6838


    6. Abilities search

      To search Abilities, run below query:

      Note: In below query, jobProfileId 6838 is used, you can search for different jobProfileId, see above SQL query on how to get the jobProfileId.
      Select ability from Abilities where jobProfileId = 6838


    7. WorkActivities search

      To search WorkActivities, run below query:

      Note: In below query, jobProfileId 6838 is used, you can search for different jobProfileId, see above SQL query on how to get the jobProfileId.
      select activity from WorkActivities where jobProfileId =6838


    8. Knowledge search

      To search Knowledge, run below query:

      Note: In below query, jobProfileId 6838 is used, you can search for different jobProfileId, see above SQL query on how to get the jobProfileId.
      Select knowledge from Knowledge where jobProfileId =6838


    9. Task search

      To search Task, run below query:

      Note: In below query, jobProfileId 6838 is used, you can search for different jobProfileId, see above SQL query on how to get the jobProfileId.
      Select task from Tasks where jobProfileId =6838


    10. Education search

      To search Education, run below query:

      Note: In below query, jobProfileId 6838 is used, you can search for different jobProfileId, see above SQL query on how to get the jobProfileId.
      select Education.name from Education, JobProfile, EduRelatedJobProfiles where EduRelatedJobProfiles.jobProfileId = JobProfile.jobProfileId AND EduRelatedJobProfiles.educationId = Education.educationId AND JobProfile.jobProfileId =6838


  3. Search in the DEGREE table
    Note: Use EDUCATION_DEGREE database.
    1. For Degree:
      To search for Degree, run below query:
      SELECT *  FROM Education c WHERE c.name='Bachelor of Computer Science';


    2. For Degree Alias:
      To search for Degree Alias, run below query:
      SELECT * FROM EducationAlias WHERE educationId=8;


  4. Search in the EDUCATION INSTITUTE table
    Note: Use EDUCATION INSTITUTE database.
    1. For EducationInstitute:
      To search for EducationInstitute, run below query:
      SELECT * FROM EducationInstitute c WHERE c.name='Indian Institute of Technology Bombay';


    2. For EducationInstituteAlias:
      To search for EducationInstituteAlias, run below query:
      SELECT * FROM InstituteAlias WHERE instituteId =85;


  5. Search in the GEOLOCATIONS table
    Note: Use GEOLOCATION database.

    You can search Geolocations from the table in multiple ways

    1. If you know the city, state, and country name, then run the below query.
      Select * from GeoLocations WHERE city ='Austin' and state='Texas' AND  CountryName ='United States of America (the)'


    2. If you know only city and countryName, then run below query:

      SELECT * from GeoLocations WHERE city='Austin' and CountryName ='United States of America (the)'


    3. If you know only country code, use the query below.

      Select top 50 * from GeoLocations WHERE countryCode ='US'
      Note: This below table also contains the CountryCode as in ISOALPHA2 format.


  6. Search in the POSTAL CODES table
    Note: Use POSTAL_CODE database.

    Run below query for POSTAL CODES search

    Select pzip.city,pzip.state,pzip.zip from PostalCode_CountryIso AS piso
    left JOIN PostalCode_Zip AS pzip ON
    piso.CountryCodeAlpha2 = pzip.CountryCodeAlpha2
    where piso.CountryName= 'Denmark'
    


  7. Search in the Skill table
    Note: Use SKILLS database.
    1. For Skill:
      To search for Skill, run below query:
      Select * from SKILL where skill='Java'


    2. For SkillAlias:
      To search for SkillAlias, run below query:
      Select * from SkillAlias where skillid = 33140;


  8. Search in the JobProfile table
    Note: Use JobProfile database.
    1. For JobProfile:
      To search for JobProfile, run below query:
      SELECT * FROM JobProfile where jobprofile ='Java Developer';


    2. For JobProfileAlias:
      To search for JobProfileAlias, run below query:
      Select * from JobProfileALias where JobProfileid =6838 ;


SQL Queries for MySQL and MSSQL

Run the following SQL queries in your MySQL database to do search for the taxonomy.
Note: Similar queries you can run incase of MSSQL database, notes are mentioned for MSSQL wherever applicable.
  1. Skill Search For English Language
    Note: Use SKILL_JOB table.
    1. Skillsearch in Skill Table
      First search Skill in the Skill Table with the below SQL Query:
      Note: In below query, Java skill is searched, similarly, you can search taxonomy for the other skill.
      Select Skill.`skillId`, Skill.`skill`, Skill.`formattedSkill`, Skill.`description`, SkillType.`skillType` from Skill, SkillType where Skill.`skill` = 'Java' AND Skill.`skillType` = SkillType.skillTypeId AND Skill.`status` = 1


      Result: The above query returns skillId, skill, formattedSkill, Description, and skillType details for the searched skill.

    2. Skillsearch in SkillAlias table
      Note: If you do not find skill in skill table (above query), then search the skill in SkillAlias table. For example, if we search for Java8, it returns empty results from the above query as Java8 is not present in Skill table, therefore, search Java8 in the SkillAlias table.

      Run below SQL query to search skill in the SkillAlias table.

      select Skill.`skillId`, Skill.`skill`, Skill.`formattedSkill`, Skill.`description`, SkillType.`skillType` from Skill, SkillType where Skill.skillId = (select SkillAlias.skillId from SkillAlias where SkillAlias.`skillAlias` = 'Java8' limit 1) AND Skill.`skillType` = SkillType.skillTypeId AND Skill.`status` = 1


    3. Skill Search For Multi Language
      1. Skillsearch in MultiLangSkill Table
        First search Skill in the MultiLangSkill Table with the below SQL Query:
        • For MSSQL: For Multilanguage data extraction, you must use the N letter before keyword to get the data; otherwise, you will not get any result.

          For example: in below query, keyword must be N'Јава' in case of MSSQL.

        • In below query, JaBa skill is searched and language selected is sr. Similarly, you can search taxonomy for the other skill and language as necessary.
        SELECT Skill.skillId, MultiLangSkill.skill, Skill.formattedSkill, Skill.description, SkillType.skillType FROM Skill, MultiLangSkill, SkillType WHERE MultiLangSkill.skillId = Skill.skillId AND Skill.skillType = SkillType.skillTypeId AND MultiLangSkill.languageId = (SELECT Language.languageId FROM Language WHERE Language.language = 'sr') AND Skill.status = 1 AND MultiLangSkill.skill = 'Јава'


        Result: The above query returns skillId, skill, formattedSkill, Description, and skillType details for the searched skill.

      2. Skillsearch in MultiLangSkillAlias table
        Note: If you do not find skill in MultiLangSkill table (above query), then search the skill in MultiLangSkillAlias table. For example, if you search for JaBa1 , it will returns empty results from the above query as JaBa1 is not present in MultiLangSkill table, therefore search JaBa1 in the MultiLangSkillAlias table.

        Run below SQL query to search skill in the MultiLangSkillAlias table.

        SELECT Skill.skillId, Skill.skill, Skill.formattedSkill, Skill.description, SkillType.skillType FROM Skill, MultiLangSkill, SkillType, MultiLangSkillAlias WHERE MultiLangSkill.skillId = Skill.skillId AND Skill.skillType = SkillType.skillTypeId AND MultiLangSkill.languageId = (SELECT Language.languageId FROM Language WHERE Language.language = 'sr') AND Skill.status = 1 AND MultiLangSkill.skillId = MultiLangSkillAlias.skillId AND MultiLangSkillAlias.skillAlias =  'јава 1


    4. Sector and Subsector of Skills

      To get Sector and Subsector of Skills, run below query:

      Note: In below query, skillId 33140 is used, you can search for different skillId, see above SQL query on how to get the skillId.
      Select RChilliSectors.sectorId, RChilliSectors.sector, RChilliSubSectors.subSectorId, RChilliSubSectors.subSector from SkillSectorRelation, RChilliSubSectors, RChilliSectors  where RChilliSubSectors.subSectorId = SkillSectorRelation.subSectorId AND RChilliSubSectors.sectorId = RChilliSectors.sectorId AND SkillSectorRelation.skillId = 33140


    5. Skill Alias Search

      To search Skill Alias, run below query:

      Note: In below query, skillId 33140 is used, you can search for different skillId, see above SQL query on how to get the skillId.
      select SkillAlias.skillAlias from SkillAlias where SkillAlias.skillId = 33140


    6. Related SKill Search

      To search Related SKill, run below query:

      Note: In below query, skillId 33140 is used, you can search for different skillId, see above SQL query on how to get the skillId.
      select Skill.skill, SkillType.skillType, SkillRelationType.relationName from RelatedSkills, SkillRelationType, Skill, SkillType where (RelatedSkills.parentSkillId = 33140 AND RelatedSkills.relationId = SkillRelationType.skillRelationTypeId) AND Skill.skillId = RelatedSkills.childSkillId AND Skill.skillType = SkillType.skillTypeId AND Skill.status = 1
      


    7. Related Job Profile Search

      To search Related Job Profile, run below query:

      Note: In below query, skillId 33140 is used, you can search for different skillId, see above SQL query on how to get the skillId.
      select JobProfile.jobProfile, ProficiencyLevel.level from JobProfile, ProficiencyLevel, RelatedJobProfile where (RelatedJobProfile.skillId = 33140 AND RelatedJobProfile.proficiencyId = ProficiencyLevel.proficiencyId) AND JobProfile.jobProfileId = RelatedJobProfile.jobProfileId


  2. Job Profile Search (For English Language)
    Note: Use JOB_PROFILES table.
    1. Job Profile search in JobProfile table
      First search Job Profile in JobProfile table with the below SQL Query:
      Note: In below query, Java Developer is searched, similarly, you can search taxonomy for the other job profiles.
      Select JobProfile.jobProfileId, JobProfile.jobProfile, JobProfile.formattedJobProfile, JobProfile.description from JobProfile where JobProfile.jobProfile = 'Java Developer' AND JobProfile.status = 1


      Result: The above query returns jobProfileId, jobProfile, formattedJobProfileand, Description details for the searched job profile.

    2. Job Profile search in JobProfileAlias table
      Note: If you do not find job profile in job profile table (above query), then search the job profile in JobProfileAlias table. For example, if you search for Developer Java, it returns empty results from the above query as it is not present in the JobProfile table, therefore, search Developer Java in the JobProfileAlias table.

      Run below SQL query to search job profile in JobProfileAlias table.

      select JobProfile.jobProfileId, JobProfile.jobProfile, JobProfile.formattedJobProfile, JobProfile.description from JobProfileAlias, JobProfile where JobProfileAlias.jobProfileId = JobProfile.jobProfileId AND JobProfileAlias.jobProfileAlias = 'Developer Java' AND JobProfile.status = 1


    3. Job Profile Search For Multi Language
      1. Job Profile search in MultiLangJobProfile Table
        First search job profile in the MultiLangJobProfile Table with the below SQL Query:
        Note:
        • For MSSQL: For Multilanguage data extraction, you must use the N letter before keyword to get the data; otherwise, you will not get any result.

          For example: in below query, keyword must be N'Programista Java' in case of MSSQL.

        • In below query, Programista Java is searched and language selected is pl. Similarly, you can search taxonomy for the other job profile and language as necessary.
        Select JobProfile.jobProfileId, MultiLangJobProfile.jobProfile, JobProfile.formattedJobProfile, JobProfile.description from JobProfile, MultiLangJobProfile where MultiLangJobProfile.jobProfileId = JobProfile.jobProfileId AND MultiLangJobProfile.jobProfile = 'Programista Java' AND MultiLangJobProfile.languageId = (SELECT Language.languageId FROM Language WHERE Language.language = 'pl') AND JobProfile.status = 1


      2. Job Profile search in MultiLangJobProfileAlias Table
        Note: If you do not found job profile in MultiLangJobProfile table (above query), then search the job profile in MultiLangJobProfileAlias table. For example, if you search Programista Java Developer, empty results is returned from the above query as it is not present in the MultiLangJobProfile table. Therefore, search Programista Java Developer in the MultiLangJobProfileAlias table.

        Run below SQL query to search job profile in MultiLangJobProfileAlias table.

        select JobProfile.jobProfileId, JobProfile.jobProfile, JobProfile.formattedJobProfile, JobProfile.description from JobProfile, MultiLangJobProfile, MultiLangJobProfileAlias where MultiLangJobProfile.jobProfileId = MultiLangJobProfileAlias.jobProfileId AND MultiLangJobProfile.jobProfileId = JobProfile.jobProfileId AND MultiLangJobProfileAlias.jobProfileAlias = 'Programista Java Developer' AND MultiLangJobProfile.languageId = (SELECT Language.languageId FROM Language WHERE Language.language = 'pl') AND JobProfile.status = 1


    4. Skills search in Job Profile table

      To search Skills in Job Profile table, run below query:

      Note: In below query, jobProfileId 6838 is used, you can search for different jobProfileId, see above SQL query on how to get the jobProfileId.
      select Skill.skill, ProficiencyLevel.level, SkillType.skillType from JobProfile, RelatedJobProfile, Skill, ProficiencyLevel, SkillType where Skill.skillType = SkillType.skillTypeId AND ProficiencyLevel.proficiencyId = RelatedJobProfile.proficiencyId AND Skill.skillId = RelatedJobProfile.skillId AND JobProfile.jobProfileId = RelatedJobProfile.jobProfileId AND JobProfile.jobProfileId = 6838


    5. Job Profile Alias search

      To search Job Profile Alias, run below query:

      Note: In below query, jobProfileId 6838 is used, you can search for different jobProfileId, see above SQL query on how to get the jobProfileId.
      Select jobProfileAlias from JobProfileAlias where JobProfileAlias.jobProfileId =6838


    6. Abilities search

      To search Abilities, run below query:

      Note: In below query, jobProfileId 6838 is used, you can search for different jobProfileId, see above SQL query on how to get the jobProfileId.
      Select ability from Abilities where jobProfileId = 6838


    7. WorkActivities search

      To search WorkActivities, run below query:

      Note: In below query, jobProfileId 6838 is used, you can search for different jobProfileId, see above SQL query on how to get the jobProfileId.
      select activity from WorkActivities where jobProfileId =6838


    8. Knowledge search

      To search Knowledge, run below query:

      Note: In below query, jobProfileId 6838 is used, you can search for different jobProfileId, see above SQL query on how to get the jobProfileId.
      Select knowledge from Knowledge where jobProfileId =6838


    9. Task search

      To search Task, run below query:

      Note: In below query, jobProfileId 6838 is used, you can search for different jobProfileId, see above SQL query on how to get the jobProfileId.
      Select task from Tasks where jobProfileId =6838


    10. Education search

      To search Education, run below query:

      Note: In below query, jobProfileId 6838 is used, you can search for different jobProfileId, see above SQL query on how to get the jobProfileId.
      select Education.name from Education, JobProfile, EduRelatedJobProfiles where EduRelatedJobProfiles.jobProfileId = JobProfile.jobProfileId AND EduRelatedJobProfiles.educationId = Education.educationId AND JobProfile.jobProfileId =6838


  3. Search in the DEGREE table
    Note: Use EDUCATION_DEGREE database.
    1. For Degree:
      To search for Degree, run below query:
      SELECT *  FROM Education c WHERE c.name='Bachelor of Computer Science';


    2. For Degree Alias:
      To search for Degree Alias, run below query:
      SELECT * FROM EducationAlias WHERE educationId=8;


  4. Search in the EDUCATION INSTITUTE table
    Note: Use EDUCATION INSTITUTE database.
    1. For EducationInstitute:
      To search for EducationInstitute, run below query:
      SELECT * FROM EducationInstitute c WHERE c.name='Indian Institute of Technology Bombay';


    2. For EducationInstituteAlias:
      To search for EducationInstituteAlias, run below query:
      SELECT * FROM InstituteAlias WHERE instituteId =85;


  5. Search in the GEOLOCATIONS table
    Note: Use GEOLOCATION database.

    You can search Geolocations from the table in multiple ways

    1. If you know the city, state, and country name, then run the below query.
      Select * from GeoLocations WHERE city ='Austin' and state='Texas' AND  CountryName ='United States of America (the)'


    2. If you know only city and countryName, then run below query:

      SELECT * from GeoLocations WHERE city='Austin' and CountryName ='United States of America (the)'


    3. If you know only country code, use the query below.

      Note: This below table also contains the CountryCode as in ISOALPHA2 format.
      Select  * from GeoLocations WHERE countryCode ='US' limit 50
      Note: For MSSQL: If you are using MSSQL, use below query:
      Select top 50 * from GeoLocations WHERE countryCode ='US'


  6. Search in the POSTAL CODES table
    Note: Use POSTAL_CODE database.

    Run below query for POSTAL CODES search

    Select pzip.city,pzip.state,pzip.zip from PostalCode_CountryIso AS piso
    left JOIN PostalCode_Zip AS pzip ON
    piso.CountryCodeAlpha2 = pzip.CountryCodeAlpha2
    where piso.CountryName= 'Denmark'
    


  7. Search in the Skill table
    Note: Use SKILLS database.
    1. For Skill:
      To search for Skill, run below query:
      Select * from SKILL where skill='Java'


    2. For SkillAlias:
      To search for SkillAlias, run below query:
      Select * from SkillAlias where skillid = 33140;


  8. Search in the JobProfile table
    Note: Use JobProfile database.
    1. For JobProfile:
      To search for JobProfile, run below query:
      SELECT * FROM JobProfile where jobprofile ='Java Developer';


    2. For JobProfileAlias:
      To search for JobProfileAlias, run below query:
      Select * from JobProfileALias where JobProfileid =6838 ;


Taxonomy Using Classic Console View

  1. Sign in to your Reader account in Snowflake, enter your Username, Password, and click Sign in.
    Note: Contact support@rchilli.com for your Snowflake URL, Username, and Password.


  2. Click Remind me later and continue working in the classic console view.
    Note: If you want to work in the Snowsight view, click Go to New UI and refer Taxonomy Using Snowsight View.


  3. On the Snowflake Classic Console page, switch to the account admin role and navigate to SYSADMIN > Switch Role > ACCOUNTADMIN.

  4. Click Shares to view all the databases that are shared with you. On the Inbound tab, select the database you want to create and click Create Database From Secure Share.

  5. On the Create Database From Secure Share page, do the following:
    • Database Name: Enter a name for the database, this name is displayed to your users.
    • Select roles: Select the roles from the drop-down, the user with this role can only access this database.
    • Comment: Enter comment as necessary.
    • Once you have filled the above fields, click Create Database.

  6. On the Database Overview page, review the database details and click OK.

  7. Click Database to view all the databases that you created.

  8. Create Warehouse: Follow below steps and create a warehouse.
    1. Click Warehouse and then click + Create.

    2. On the Create Warehouse pop-up, enter Name, Comment, and click Finish to create a warehouse.

  9. Click Worksheet and select the warehouse in the Context drop-down. Click Context and select the Role, Warehouse, Database, and Schema from the drop-down.
    Note:
    • Select the Warehouse you created in the above step.
    • Select Database and Schema in order to download the table CSV of the schema.


  10. Download table CSV of schema: On the Worksheets tab, expand the taxonomy database to view all the schema. Now, expand the schema to view all its table. Click 3-dot next to the table you want to download and select Place Name in SQL.

    • Once you click Place Name in SQL in the above step, the SQL query in placed in the worksheet for the table.

    • Add Select * from in front of the SQL that is added in the above step.
      • Click Run to view and download the table.
      • Click download icon to download the table in the CSV format.

    • On the Export Results pop-up, you can donwload the table in the TSV or CSV file format, click Export to get the table file.

      Note:
      1. Similarly, you have to download table of all the following schema (total 39 table):
        1. SKILL_JOB
        2. EDUCATION_DEGREE
        3. EDUCATION_INSTITUTE
        4. GEOLOCATION
        5. POSTAL_CODE
        6. SKILLS
        7. JOB_PROFILES
      2. Make sure you are selecting the database and schema correctly in above step-9 before you run SQL query for the table of the schema.
      3. You can run SQL query on the taxonomy database, refer SQL Queries for Snowflake to know more details on how to run queries and search on the taxonomy database.
      4. You can run the SQL query in your MYSQL/MSSQL and search the taxonomies as necessary, for more details, refer SQL Queries for MySQL and MSSQL.

Troubleshooting Data in CSV File

Once you download the table CSV from Snowflake, there are chances that a few data (especially Multilingual data) may display incorrectly in CSV. For example, in the below CSV file, SKILLALIAS for rows 2, 3, 4, 5, and 9 are incorrectly displayed. To resolve this issue with data in a CSV file, follow the below steps:

Note: Below graphics are for reference only and can be different basis on the different versions of MS Office.
  1. Open the downloaded CSV file and click Data and select From Test in the CSV header.

  2. On the Import Test File pop-up, select the CSV file and click Import.

  3. On the Test Import Wizard - Step 1of 3 pop-up, make sure the File origin is selected as 65001:Unicode (UTF-8) and click Next.

  4. On the Import Data pop-up, select New worksheet and click OK.

  5. A new worksheet (Sheet 1) is created with the data in the correct format; now you can save the new worksheet.