The persistent excitement surrounding AI, particularly with the release of ChatGPT and the new AI and LLM models that are released every week, seize the headlines of tech news and has spurred my interest in diving into the AI landscape. With every other company trying to integrate AI in their product, AIOps/LLMOps tools are the need of the hour and MindsDB can make it lot easier, the implementation of realtime analysis of Youtube comments caught my eye. Being a content creator myself on YouTube. This was the perfect use case, there is no popular tool that lets creators understand the general vibe of their audience. Also this would help me a lot as a creator.

Tl;dr*this article is about creating a Realtime Sentiment Analysis for Youtube using MindsDB to predict the general vibe of your audience.*

Reach out

• 📧 Email: 19mandal97@gmail.com

• 📱 LinkedIn: @sourabh-mandal

• 🌐 GitHub: @sourabhmandal

The project

The project frontend is deployed unfortunately I wasn't able to deploy the mindsdb instance in AWS. due to limited functionality in docker container and inherent complexity of AWS.

Setting up the environment

I used docker container of mindsdb and initialized an NextJS project with T3 stack(trpc, tailwind, typescript).

And the project is ready for hacking

How you can setup the project

1. Clone the frontend

   
    git clone https://github.com/sourabhmandal/supareel.git
    cd supareel
    npm install
   

2. Add necessary env variables, you can refer to `.env.example` file in the same project for the environment variables.

3. Once the environment variables are set, start the frontend

   
    npm run dev
   

4. Now, Start the MindsDB container

   
    git clone https://github.com/sourabhmandal/mindsdb-aws.git
    cd mindsdb-aws
    docker compose up
   

Preparing mindsDB

Open the editor and run the following SQL

1. Create a project called Supareel

   
    CREATE PROJECT IF NOT EXISTS supareel;
   

2. Create YouTube integration for mindsDB

   
    CREATE DATABASE supareel_db
    WITH ENGINE = 'youtube',
    PARAMETERS = {
      "youtube_api_token": "<youtube_api_key>"  
    };
   

3. Download the sentiment analysis model

   
    DROP MODEL IF EXISTS supareel.sentiment_classifier;
   
    CREATE MODEL supareel.sentiment_classifier
    PREDICT sentiment
    USING engine='huggingface',
      model_name= 'cardiffnlp/twitter-roberta-base-sentiment',
      input_column = 'comment',
      labels=['negative','neutral','positive'];
   

And now we are ready to interact with supareel frontend

1. Add some Jobs to automate the process. These jobs will be channel specific. new Channel, means new JOBS

    -- [ONCE] JOB for Syncing all youtube videos - everyh our
    CREATE JOB IF NOT EXISTS supareel.sync_comments_UCdOb1Se6eXYpYHD8HWFXtWg AS (
      DELETE FROM planetscale_datasource.YouTubeComments WHERE yt_video_id IN (SELECT yt_video_id FROM planetscale_datasource.YouTubeVideo);
        INSERT INTO planetscale_datasource.YouTubeComments(
          SELECT
            tc.comment_id AS yt_comment_id,
            tc.video_id AS yt_video_id,
            tc.comment AS yt_comment,
            tc.channel_id AS yt_channel_id
          FROM planetscale_datasource.TestComments tc
          LEFT JOIN planetscale_datasource.YouTubeComments yc ON tc.comment_id = yc.yt_comment_id
          WHERE yc.yt_comment_id IS NULL AND tc.channel_id='UCdOb1Se6eXYpYHD8HWFXtWg'
        );
    ) END '2024-02-01 00:00:00' EVERY hour;
   
    -- [ONCE] JOB for Syncing youtube videos - 1week
    CREATE JOB IF NOT EXISTS supareel.sync_videos AS (
      INSERT INTO planetscale_datasource.YouTubeVideo (
        SELECT 
        video_id as yt_video_id, 
        channel_id as yt_channel_id, 
        title AS yt_video_title, 
        description as yt_video_description, 
        thumbnail as yt_video_thumbnail  
        FROM supareel_db.videos 
        WHERE channel_id = (SELECT yt_channel_id FROM planetscale_datasource.YouTubeChannelDetails)
      )
    ) END '2024-02-02 00:00:00' EVERY week;
   

2. Add a trigger to update new once comment are coming

    -- Trigger to do sentiment analysis on any new comment
    CREATE TRIGGER sentiment_analyse_new_comments
    ON planetscale_datasource.YouTubeComments
    COLUMNS yt_comment, sentiment
    (
      UPDATE planetscale_datasource.YouTubeComments
      SET
        sentiment = source.sentiment
      FROM
        (
          SELECT yc.yt_comment, model.sentiment 
          FROM (SELECT yt_comment, yt_video_id AS comment FROM planetscale_datasource.YouTubeComments WHERE yt_video_id IN (
            SELECT yt_video_id FROM planetscale_datasource.YouTubeVideo
          ) AND sentiment='') AS yc
          JOIN supareel.sentiment_classifier AS model 
          ON yc.yt_video_id = model.video_id
        ) AS source
      WHERE source.yt_comment=yt_comment;
    );
   

Video Demo

walkthrough example of supareel

Outcomes

When I started working on this feature, I had the following goals and their respective outcomes at the end

1. Create the YouTube Sentiment Analyses

2. Learn the inner workings of MindsDB

Conclusion

Creating a Real-time YouTube Comment Analyzer was a fun and intriguing experience. Throughout the process, I delved into the inner workings of the MindsDB codebase, mastering the use of YouTube APIs and automation techniques. The sentiment analysis of YouTube comments not only added a captivating element but also boosted my confidence in my content creation journey.

I hope you enjoyed reading the article. If you come across any errors or issues, please feel free to let me know in the comments.