query_run_test_script.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "af8d32d4-9c88-4bb2-9e08-2e3adb1a920b",
   "metadata": {},
   "source": [
    "# TPCDS: Query Run Test Script"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "b008de62-98a8-4de9-a4cd-003e80e6f7e8",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sys, os\n",
    "import psycopg2\n",
    "from psycopg2 import Error\n",
    "from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT\n",
    "from IPython.display import clear_output"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "f1c703bb-49b5-46fc-b585-5eb778a9f272",
   "metadata": {},
   "outputs": [],
   "source": [
    "# set up connection variables\n",
    "db_host = \"localhost\"\n",
    "db_port = \"5432\"\n",
    "db_user = \"postgres\"\n",
    "db_pass = \"password\"\n",
    "db_name = \"tpcds\"\n",
    "\n",
    "# function to connect with postgres\n",
    "def connect_postgres(db_host, db_port, db_user, db_pass, db_name):\n",
    "    try:\n",
    "        # Connect to an existing database\n",
    "        connection = psycopg2.connect(host = db_host,\n",
    "                                      port = db_port,\n",
    "                                      user = db_user,\n",
    "                                      password = db_pass,\n",
    "                                      database = db_name)\n",
    "        # Set auto-commit\n",
    "        connection.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT);\n",
    "        # Create a cursor to perform database operations\n",
    "        cur = connection.cursor()\n",
    "        # Print PostgreSQL details\n",
    "        print(\"PostgreSQL server information\")\n",
    "        print(connection.get_dsn_parameters(), \"\\n\")\n",
    "        # Executing a SQL query\n",
    "        cur.execute(\"SELECT version();\")\n",
    "        # Fetch result\n",
    "        record = cur.fetchone()\n",
    "        print(\"You are connected to - \", record, \"\\n\")\n",
    "\n",
    "    except (Exception, Error) as error:\n",
    "        print(\"Error while connecting to PostgreSQL\", error)\n",
    "    else:\n",
    "        return cur"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "1e736ce3-98e4-4d55-b26e-feae085ecd89",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "PostgreSQL server information\n",
      "{'user': 'postgres', 'dbname': 'tpcds', 'host': 'localhost', 'port': '5432', 'tty': '', 'options': '', 'sslmode': 'prefer', 'sslcompression': '0', 'gssencmode': 'prefer', 'krbsrvname': 'postgres', 'target_session_attrs': 'any'} \n",
      "\n",
      "You are connected to -  ('PostgreSQL 14.5, compiled by Visual C++ build 1914, 64-bit',) \n",
      "\n"
     ]
    }
   ],
   "source": [
    "# connect to postgres\n",
    "cur = connect_postgres(db_host, db_port, db_user, db_pass, db_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "3e7a51e5-6f5c-4cdf-ac2e-4b47a8e5b54d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "C:\\Users\\ahmad\\Desktop\\tpcds_git\\tpc-benchmark\\all_queries\\initial_queries\n"
     ]
    }
   ],
   "source": [
    "# get dir path\n",
    "\n",
    "path = os.getcwd() + '\\\\all_queries\\\\initial_queries'\n",
    "files = os.listdir(path)\n",
    "print(path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "1afcff78-df73-4fdd-b842-80c875e7e80f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# function to get full abosolute path files in directory\n",
    "\n",
    "def get_absolute_path(d):\n",
    "    return [os.path.join(d, f) for f in os.listdir(d)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "d93031fe-af21-4094-9037-048a3e7ff5cd",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total files: 99\n",
      "First few files...\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "['C:/Users/ahmad/Desktop/tpcds_git/tpc-benchmark/all_queries/initial_queries/query-01.sql',\n",
       " 'C:/Users/ahmad/Desktop/tpcds_git/tpc-benchmark/all_queries/initial_queries/query-02.sql',\n",
       " 'C:/Users/ahmad/Desktop/tpcds_git/tpc-benchmark/all_queries/initial_queries/query-03.sql',\n",
       " 'C:/Users/ahmad/Desktop/tpcds_git/tpc-benchmark/all_queries/initial_queries/query-04.sql',\n",
       " 'C:/Users/ahmad/Desktop/tpcds_git/tpc-benchmark/all_queries/initial_queries/query-05.sql']"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get full abosolute path files in directory\n",
    "\n",
    "files_abs_path = [p.replace('\\\\', '/') for p in get_absolute_path(path)]\n",
    "print(\"Total files:\", len(files_abs_path))\n",
    "print(\"First few files...\")\n",
    "files_abs_path[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "1d0d6faa-d108-4fc1-a574-cc3a720ba598",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Run Test Start = 22:29:21\n"
     ]
    }
   ],
   "source": [
    "from datetime import datetime\n",
    "\n",
    "now = datetime.now()\n",
    "current_time = now.strftime(\"%H:%M:%S\")\n",
    "print(\"Run Test Start =\", current_time)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "b6c15c1d-5c7f-40d4-a83f-cb24f95b7687",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "C:/Users/ahmad/Desktop/tpcds_git/tpc-benchmark/all_queries/initial_queries/query-99.sql\n",
      "Success, Message: SELECT 90\n"
     ]
    }
   ],
   "source": [
    "# perform run test on each query\n",
    "# save results in text file\n",
    "\n",
    "script_num = 1\n",
    "script_errors = 0\n",
    "\n",
    "for sql_script in files_abs_path:\n",
    "    textfile = open(\"query_run_test_result.txt\", \"a\")\n",
    "    textfile2 = open(\"query_run_test_query_errors.txt\", \"a\")\n",
    "    clear_output(wait = True)\n",
    "    try:\n",
    "        cur.execute(\n",
    "            open(sql_script, \"r\").read()\n",
    "        )\n",
    "    except Exception as e:\n",
    "        script_errors += 1\n",
    "        outcome = f\"Error, Message: {e}\"\n",
    "        print(sql_script)\n",
    "        print(outcome)\n",
    "        textfile.write(sql_script + \"\\n\")\n",
    "        textfile.write(outcome + \"\\n\\n\")\n",
    "        # for tracking errors alone\n",
    "        textfile2.write(sql_script + \"\\n\")\n",
    "        textfile2.write(outcome + \"\\n\\n\")\n",
    "    else:\n",
    "        outcome = f\"Success, Message: {cur.statusmessage}\"\n",
    "        print(sql_script)\n",
    "        print(outcome)\n",
    "        textfile.write(sql_script + \"\\n\")\n",
    "        textfile.write(outcome + \"\\n\\n\")\n",
    "        \n",
    "    script_num += 1\n",
    "    textfile.close()\n",
    "    textfile2.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "c457b42e-adf1-4621-9cee-5fa507ab681d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Run Test End = 00:55:01\n"
     ]
    }
   ],
   "source": [
    "now = datetime.now()\n",
    "current_time = now.strftime(\"%H:%M:%S\")\n",
    "print(\"Run Test End =\", current_time)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "ba54f295-b4df-4eae-87ce-5cb19774b69d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# close connection to db\n",
    "\n",
    "cur.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "a5674ad0-1f7d-4af0-80b0-6e5e4c6e35f0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "We have a total of 23 queries with error\n"
     ]
    }
   ],
   "source": [
    "# check total amount of query errors\n",
    "\n",
    "print(f\"We have a total of {script_errors} queries with error\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "23cf3748-16e4-4621-8e52-174008329d3b",
   "metadata": {},
   "source": [
    "#### End of script."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}