New

Resume Evaluator

Notebook

SingleStore Notebooks

Resume Evaluator

Note

This notebook can be run on a Free Starter Workspace. To create a Free Starter Workspace navigate to Start using the left nav. You can also use your existing Standard or Premium workspace with this Notebook.

In this notebook, we will showcase a practical use-case of evaluating resumes leveraging the combined powers of SingleStoreDB and OpenAI. Moving beyond traditional resume-matching techniques, we introduce a method that dives deeper into the nuances of resume content and the specific needs of a job description.

With SingleStoreDB's innate ability to swiftly manage and query data, we begin by ingesting a resume PDF, extracting its textual content, and storing it in a dedicated database table. But we don’t stop there! The resume's text is then processed by OpenAI's LLM, summarizing its key points. This summarized version, along with its vector representation, is saved back to our SingleStoreDB table.

When it's time to match a resume to a job description, we translate the latter into its vector form. Using a dot_product operation, we search against the table housing the resume summaries' embeddings. This provides us with resumes most aligned with the job description. To add another layer of precision, the matched resumes are further evaluated alongside the job description using OpenAI's LLM, offering a comprehensive assessment.

Join us on this journey to redefine resume evaluations using SQL, SingleStoreDB, and OpenAI for better, more insightful matches!

1. Create a workspace in your workspace group

S-00 is sufficient.

Action Required

If you have a Free Starter Workspace deployed already, select the database from drop-down menu at the top of this notebook. It updates the connection_url to connect to that database.

2. Create a database named resume_evaluator

In [1]:

shared_tier_check = %sql show variables like 'is_shared_tier'
if not shared_tier_check or shared_tier_check[0][1] == 'OFF':
    %sql DROP DATABASE IF EXISTS resume_evaluator;
    %sql CREATE DATABASE resume_evaluator;

Action Required

Make sure to select the resume_evaluator database from the drop-down menu at the top of this notebook. It updates the connection_url which is used by the %%sql magic command and SQLAlchemy to make connections to the selected database.

In [2]:

%%sql

CREATE TABLE IF NOT EXISTS resumes_profile_data /* Creating table for sample data. */(
    names text,
    email text,
    phone_no text,
    years_of_experience text,
    skills text,
    profile_name text,
    resume_summary text,
    resume_embeddings blob
);

3. Install and import required libraries

In this section, we will set up the necessary environment by installing some crucial libraries. For our task of extracting text from resume PDFs, we'll be using pdfminer.six. To interact with the OpenAI's LLM and manage our data efficiently, openai will be instrumental.

The install process may take a couple minutes.

In [3]:

!pip install -q pdfminer.six openai==1.3.3

In [4]:

import getpass
import numpy as np
import openai
import os
import pandas as pd
import requests
import re
from openai import OpenAI
from pdfminer.high_level import extract_text
from singlestoredb import create_engine
from sqlalchemy import text

In [5]:

os.environ['OPENAI_API_KEY'] = getpass.getpass('OpenAI API Key: ')
client = openai.OpenAI()

4. Create a function called `get_embedding()`

In our workflow, we need a consistent way to transform textual content into vector embeddings. To achieve this, we introduce the get_embedding() function.

This function takes in a piece of text and, by default, uses the "text-embedding-ada-002" model to produce embeddings. We ensure that any newline characters in the text are replaced with spaces to maintain the integrity of the input. The function then leverages OpenAI's API to generate and retrieve the embedding for the given text.

In [6]:

def get_embedding(text, model="text-embedding-ada-002"):
    text = text.replace("\n", " ")
    response = openai.embeddings.create(input=[text], model=model)
    return response.data[0].embedding

5. Create a function called `print_pdf_text()`

This function is designed to extract and clean the text from a provided PDF, either from a web URL or a local file path.

Input:

url: Web URL of the PDF (optional).
file_path: Local path of the PDF (optional).

Functionality:

Source Determination:
- Fetches PDF from url or uses the local file_path.
- Raises an error if neither is provided.
Text Extraction: Extracts text from the PDF using pdfminer.
Text Cleaning:
- Removes special characters, retaining only "@", "+", ".", and "/".
- Improves formatting by handling newline characters.
Cleanup: If a temporary file was created from a URL, it gets deleted post-processing.

Output:

Returns the cleaned and formatted text from the PDF.

In [7]:

def print_pdf_text(url=None, file_path=None):
    # Determine the source of the PDF (URL or local file)
    if url:
        response = requests.get(url)
        response.raise_for_status()  # Ensure the request was successful
        temp_file_path = "temp_pdf_file.pdf"
        with open(temp_file_path, 'wb') as temp_file:
            temp_file.write(response.content)  # Save the PDF to a temporary file
        pdf_source = temp_file_path
    elif file_path:
        pdf_source = file_path  # Set the source to the provided local file path
    else:
        raise ValueError("Either url or file_path must be provided.")

    # Extract text using pdfminer
    text = extract_text(pdf_source)

    # Remove special characters except "@", "+", ".", and "/"
    cleaned_text = re.sub(r"[^a-zA-Z0-9\s@+./:,]", "", text)

    # Format the text for better readability
    cleaned_text = cleaned_text.replace("\n\n", " ").replace("\n", " ")
    # If a temporary file was used, delete it
    if url and os.path.exists(temp_file_path):
        os.remove(temp_file_path)

    return cleaned_text

6. Create a function called `pinfo_extractor()`

This function is tailored to extract specific details from a candidate's resume text.

Input:

resume_text: The text extracted from a candidate's resume.

Functionality:

Prompt Creation:
- A context is formed using the provided resume text.
- A detailed question prompt is generated to guide the extraction of desired details from the resume.
OpenAI API Interaction:
- Uses the gpt-3.5-turbo model to process the prompt and generate a detailed extraction.
- Extracts relevant sections like Name, Email, Phone Number, and more from the generated response.
Data Structuring:
- The extracted details are organized into a dictionary.

Output:

Returns a dictionary with keys such as 'name', 'email', 'phone_no', and more, containing extracted information from the resume.

In [8]:

def pinfo_extractor(resume_text):
    context = f"Resume text: {resume_text}"
    question = """ From above candidate's resume text, extract the only following details:
                Name: (Find the candidate's full name. If not available, specify "not available.")
                Email: (Locate the candidate's email address. If not available, specify "not available.")
                Phone Number: (Identify the candidate's phone number. If not found, specify "not available.")
                Years of Experience: (If not explicitly mentioned, calculate the years of experience by analyzing the time durations at each company or position listed. Sum up the total durations to estimate the years of experience. If not determinable, write "not available.")
                Skills Set: Extract the skills which are purely technical and represent them as: [skill1, skill2,... <other skills from resume>]. If no skills are provided, state "not available."
                Profile: (Identify the candidate's job profile or designation. If not mentioned, specify "not available.")
                Summary: provide a brief summary of the candidate's profile without using more than one newline to segregate sections.
                """

    prompt = f"""
        Based on the below given candidate information, only answer asked question:
        {context}
        Question: {question}
    """
    # print(prompt)
    response = client.chat.completions.create(
        model="gpt-3.5-turbo",
        messages=[
            {"role": "system", "content": "You are a helpful HR recruiter."},
            {"role": "user", "content": prompt}
        ],
        max_tokens=700,
        temperature=0.5,
        n=1  # assuming you want one generation per document
    )
    # Extract the generated response
    response_text = response.choices[0].message.content # response['choices'][0]['message']['content']
    print(response_text)
    # Split the response_text into lines
    lines = response_text.strip().split('\n')

    # Now, split each line on the colon to separate the labels from the values
    # Extract the values
    name = lines[0].split(': ')[1]
    email = lines[1].split(': ')[1]
    phone_no = lines[2].split(': ')[1]
    years_of_expiernce = lines[3].split(': ')[1]
    skills = lines[4].split(': ')[1]
    profile = lines[5].split(': ')[1]
    summary = lines[6].split(': ')[1]
    data_dict = {
        'name': name,
        'email': email,
        'phone_no': phone_no,
        'years_of_expiernce': years_of_expiernce,
        'skills': skills,
        'profile': profile,
        'summary': summary
    }
    print(data_dict, "\n")
    return data_dict;

7. Create a function called `add_data_to_db()`

This function is designed to add extracted resume details into a database.

Input:

input_dict: Dictionary containing details like 'name', 'email', 'phone_no', and more extracted from a resume.

Functionality:

Database Connection:
- Establishes a connection to the database using SQLAlchemy's create_engine with the given connection URL.
Embedding Creation:
- Calls the get_embedding() function to generate an embedding for the resume summary.
SQL Query Formation:
- Crafts an SQL query to insert the provided data (from the input dictionary) into the resumes_profile_data table in the database.
Data Insertion:
- Opens a connection, executes the SQL query, commits the changes, and then closes the connection.

Output:

Prints a confirmation message upon successful data insertion.

In [9]:

def add_data_to_db(input_dict):
    # Create the SQLAlchemy engine
    # engine = create_engine(f'mysql+pymysql://{user}:{password}@{host}:{port}/{database_name}')

    engine = create_engine(connection_url)
    # Get the embedding for the summary text
    summary = input_dict['summary']
    embedding = get_embedding(summary)
    # Create the SQL query for inserting the data
    query_sql = f"""
        INSERT INTO resumes_profile_data (names, email, phone_no, years_of_experience, skills, profile_name, resume_summary, resume_embeddings)
        VALUES ("{input_dict['name']}", "{input_dict['email']}", "{input_dict['phone_no']}", "{input_dict['years_of_expiernce']}",
        "{input_dict['skills']}", "{input_dict['profile']}", "{summary}", JSON_ARRAY_PACK('{embedding}'));
    """
    with engine.connect() as connection:
        connection.execute(text(query_sql))
        connection.commit()
    print("\nData Written to resumes_profile_data_2 table")

8. Create a function called `search_resumes()`

This function facilitates the search for resumes that are most similar to a given query, leveraging embeddings and database operations.

Input:

query: A string that represents the job description or any other search criteria.

Functionality:

Embedding Creation:
- Converts the given query into its corresponding embedding using the get_embedding() function.
SQL Query Formation:
- Creates an SQL query to search for the top 5 resumes in the resumes_profile_data table that have the highest similarity (dot product) to the query embedding.
Database Operations:
- Opens a connection to the database, runs the SQL query to fetch the results, and then closes the connection.

Output:

Returns a list of the top 5 most relevant resumes based on the given query.

In [10]:

def search_resumes(query):
    query_embed = get_embedding(query)
    query_sql = f"""
            SELECT names, resume_summary, dot_product(
                    JSON_ARRAY_PACK('{query_embed}'),
                    resume_embeddings
                ) AS similarity
                FROM resumes_profile_data
                ORDER BY similarity DESC
                LIMIT 5;
    """
    # print(query_sql,"\n")
    # engine = create_engine(f'mysql+pymysql://{user}:{password}@{host}:{port}/{database_name}')
    engine = create_engine(connection_url)
    connection = engine.connect()
    result = connection.execute(text(query_sql)).fetchall()
    connection.close()
    engine.dispose()
    return result

8. Create a function called `evaluate_candidates()`

This function is geared towards evaluating the compatibility of candidates' resumes in relation to a specific job description.

Input:

query: A string that represents the job description against which candidates' resumes will be assessed.

Functionality:

Resume Retrieval:
- Utilizes the search_resumes() function to get the top matching resumes based on the job description.
OpenAI API Interaction:
- For each retrieved resume, a prompt is crafted, asking to evaluate how well the candidate fits the job description.
- Interacts with the gpt-3.5-turbo model to process this prompt and receive an efficient, concise response.
Data Aggregation:
- Collects the model's evaluation responses for each candidate in a list.

Output:

Returns a list of tuples, where each tuple contains:

Candidate's name.
Evaluation response from the model, describing the compatibility of the candidate with the given job description.

In [11]:

def evaluate_candidates(query):
    result = search_resumes(query)
    responses = []  # List to store responses for each candidate
    for resume_str in result:
        name = resume_str[0]
        context = f"Resume text: {resume_str[1]}"
        question = f"What percentage of the job requirements does the candidate meet for the following job description? answer in 3 lines only and be effcient while answering: {query}."
        prompt = f"""
            Read below candidate information about the candidate:
            {context}
            Question: {question}
        """
        response = client.chat.completions.create(
            model="gpt-3.5-turbo",
            messages=[
                {"role": "system", "content": "You are a expert HR analyst and recuriter."},
                {"role": "user", "content": prompt}
            ],
            max_tokens=100,
            temperature=0.2,
            n=1  # assuming you want one generation per document
        )
        # Extract the generated response
        response_text = response.choices[0].message.content # response['choices'][0]['message']['content']
        responses.append((name, response_text))  # Append the name and response_text to the responses list
    return responses

In [12]:

urls = [
    "https://github.com/vishwajeetdabholkar/vishwajeet-resume/raw/main/Vishwajeet_Dabholkar_Resume_June_2023.pdf"
]

for url in urls:
    resume_text = print_pdf_text(url=url).replace('\n',' ')
    print("Resume Text extracted\n")
    ip_data_dict = pinfo_extractor(resume_text)
    print("Information extracted\n")
    add_data_to_db(ip_data_dict)
    print("\n")

In [13]:

%%sql

SELECT * FROM resumes_profile_data;

In [14]:

job_description = input("Enter Job description : \n")
evaluate_candidates(job_description)

Cleanup

Action Required

If you created a new database in your Standard or Premium Workspace, you can drop the database by running the cell below. Note: this will not drop your database for Free Starter Workspaces. To drop a Free Starter Workspace, terminate the Workspace using the UI.

In [15]:

shared_tier_check = %sql show variables like 'is_shared_tier'
if not shared_tier_check or shared_tier_check[0][1] == 'OFF':
    %sql DROP DATABASE IF EXISTS resume_evaluator;

Details

About this Template

Resume Evaluator using vectors stored in SingleStore to find sutiable resume for your job description.

This Notebook can be run in Shared Tier, Standard and Enterprise deployments.

License

This Notebook has been released under the Apache 2.0 open source license.

Resume Evaluator

Notebook

Resume Evaluator

1. Create a workspace in your workspace group

2. Create a database named resume_evaluator

3. Install and import required libraries

4. Create a function called get_embedding()

5. Create a function called print_pdf_text()

Input:

Functionality:

Output:

6. Create a function called pinfo_extractor()

Input:

Functionality:

Output:

7. Create a function called add_data_to_db()

Input:

Functionality:

Output:

8. Create a function called search_resumes()

Input:

Functionality:

Output:

8. Create a function called evaluate_candidates()

Input:

Functionality:

Output:

Cleanup

Details

About this Template

Tags

License

4. Create a function called `get_embedding()`

5. Create a function called `print_pdf_text()`

6. Create a function called `pinfo_extractor()`

7. Create a function called `add_data_to_db()`

8. Create a function called `search_resumes()`

8. Create a function called `evaluate_candidates()`