{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Bot deteciton Notebook" ] }, { "cell_type": "code", "execution_count": 1, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:11:04.757824Z", "start_time": "2024-05-26T00:11:04.555293Z" } }, "outputs": [], "source": [ "# setup tailscale if you haven't\n", "# https://tailscale.com/kb/1031/install-linux\n", "# !sudo tailscale up --accept-routes=true\n", "\n", "# setup autoload\n", "%load_ext autoreload\n", "%autoreload 2" ] }, { "cell_type": "code", "execution_count": 2, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:11:08.392310Z", "start_time": "2024-05-26T00:11:04.759383Z" } }, "outputs": [], "source": [ "# pip install psycopg2-binary\n", "# make sure sqlalchemy is >=2\n", "# pip install \"sqlalchemy>=2\"\n", "import os\n", "import datetime\n", "from collections import defaultdict, Counter\n", "import json\n", "from urllib.parse import quote\n", "import sys\n", "\n", "\n", "\n", "import boto3\n", "import matplotlib.pyplot as plt\n", "import numpy as np\n", "import pandas as pd\n", "import sqlalchemy\n", "import tqdm\n", "from botocore.exceptions import ClientError\n", "\n", "# Add the parent directory of the current notebook to the Python path\n", "notebook_dir = os.path.dirname(os.path.abspath('__file__'))\n", "parent_dir = os.path.dirname(os.path.dirname(notebook_dir))\n", "sys.path.append(parent_dir + \"/suno_analytics\")\n", "\n", "from suno_analytics.preference_helper import get_preference_counts\n", "from suno_analytics.preference_data_selection import (\n", " gather_data,\n", " plot_clip_distribution,\n", " parse_metadata_for_basics,\n", " get_concat_clip_ids,\n", " validate_preference_data,\n", " run_bot_detection,\n", " print_out_value_counts_nicely,\n", " merge_concat_clips_with_reactions,\n", " plot_clip_basic_distributions,\n", ")\n", "import sys\n", "import os\n", "\n", "\n", "\n", "# setup some pandas display stuff\n", "pd.set_option(\"display.max_rows\", 500)\n", "pd.set_option(\"display.max_columns\", 500)\n", "pd.set_option(\"display.width\", 1000)\n", "\n", "\n", "def get_secret():\n", " secret_name = \"app-user-main-db-secret\"\n", " region_name = \"us-east-2\"\n", " # Create a Secrets Manager client\n", " session = boto3.session.Session()\n", " client = session.client(service_name=\"secretsmanager\", region_name=region_name)\n", " try:\n", " get_secret_value_response = client.get_secret_value(SecretId=secret_name)\n", " except ClientError as e:\n", " raise e\n", " secret = get_secret_value_response[\"SecretString\"]\n", " return json.loads(secret)\n", "\n", "\n", "my_secrets = get_secret()\n", "\n", "# alternative...\n", "engine = sqlalchemy.create_engine(\n", " \"postgresql://suno:%s@suno-main-postgres-prod-analytics.cnfvffydbwvc.us-east-2.rds.amazonaws.com/suno_main\"\n", " % quote(my_secrets[\"password\"]),\n", ")\n", "\n", "\n", "home_dir = os.path.expanduser(\"~\")\n", "snow_password_path = os.path.join(home_dir, \".aws\", \"snow_pw.txt\")\n", "if os.path.exists(snow_password_path):\n", " # !pip install snowflake\n", " from snowflake.core import Root\n", " from snowflake.snowpark import Session\n", "\n", " with open(snow_password_path, \"r\") as fp:\n", " fp_lines = fp.readlines()\n", " snow_password = fp_lines[0].strip()\n", " snow_username = fp_lines[1].strip()\n", "\n", " CONNECTION_PARAMETERS = {\n", " \"account\": \"fu90569.us-east-2.aws\",\n", " \"user\": snow_username,\n", " \"password\": snow_password,\n", " \"role\": \"ACCOUNTADMIN\",\n", " \"database\": \"SUNO_PROD\",\n", " \"warehouse\": \"SUNO_PROD_LARGE\",\n", " \"schema\": \"PROD\",\n", " }" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Validate some info" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:11:08.550447Z", "start_time": "2024-05-26T00:11:08.397196Z" } }, "outputs": [], "source": [ "# there are 4 hr time difference between eastern time and utc\n", "# cutoff_date = \"2024-09-11 00:00:00\" # t3-9 out\n", "cutoff_date = (datetime.datetime.now() - datetime.timedelta(hours=1)).astimezone(datetime.timezone.utc).strftime(\"%Y-%m-%d %H:%M:%S\")\n", "print(cutoff_date)\n", "\n", "target_model_name = \"chirp-v3p5-engine-t-3\"" ] }, { "cell_type": "code", "execution_count": 4, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:11:09.349857Z", "start_time": "2024-05-26T00:11:08.551408Z" } }, "outputs": [], "source": [ "df_all_tables = pd.read_sql_query(\n", " \"SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'\",\n", " engine,\n", ")\n", "# should have all the basic table names here\n", "assert df_all_tables[\"table_name\"].nunique() >= 61" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Query the DB" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "gathered_data = gather_data(engine, cutoff_date) # filter_model_name=target_model_name)" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [], "source": [ "# unpack the information\n", "bots_action_df = gathered_data[\"bots_action_df\"]\n", "reaction_df = gathered_data[\"reaction_df\"]\n", "total_clip_df = gathered_data[\"total_clip_df\"]\n", "playlist_clip_df = gathered_data[\"playlist_clip_df\"]\n", "auth_user_df = gathered_data[\"auth_user_df\"]\n", "discord_info_df = gathered_data[\"discord_info_df\"]" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# this is probably the right way to figure out the pro user group\n", "pro_users = set(discord_info_df[\"user_id\"].unique())\n", "reaction_df[\"is_pro_user\"] = reaction_df[\"user_id\"].isin(pro_users)\n", "total_clip_df[\"is_pro_user\"] = total_clip_df[\"user_id\"].isin(pro_users)\n", "\n", "# this is very interesting....\n", "# reaction check\n", "print(\"Reactions fraction by pro user:\")\n", "print_out_value_counts_nicely(reaction_df, \"is_pro_user\")\n", "# clip check\n", "print(\"Reactions fraction by pro user:\")\n", "print_out_value_counts_nicely(total_clip_df, \"is_pro_user\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# if you write to file it will dump the two jsons\n", "bot_detection_result = run_bot_detection(total_clip_df, reaction_df, write_to_file=False, cut_off_freq=0.95, min_generations_for_no_reaction=10, return_bad_user_ids=True)\n", "bad_user_ids = bot_detection_result[\"bad_user_ids\"]\n", "bad_pro_user_ids = bot_detection_result[\"bad_pro_user_ids\"]\n", "print(len(bad_user_ids), len(bad_pro_user_ids))\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "suno_env", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.10.12" }, "toc": { "base_numbering": 1, "nav_menu": {}, "number_sections": true, "sideBar": true, "skip_h1_title": false, "title_cell": "Table of Contents", "title_sidebar": "Contents", "toc_cell": false, "toc_position": {}, "toc_section_display": true, "toc_window_display": false } }, "nbformat": 4, "nbformat_minor": 4 }