cassandra-scripts.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: cassandra-driver in c:\\users\\saritajoshi\\documents\\repos\\credit-card-fraud-detection-spark\\venv\\lib\\site-packages (3.29.1)\n",
      "Requirement already satisfied: geomet<0.3,>=0.1 in c:\\users\\saritajoshi\\documents\\repos\\credit-card-fraud-detection-spark\\venv\\lib\\site-packages (from cassandra-driver) (0.2.1.post1)\n",
      "Requirement already satisfied: click in c:\\users\\saritajoshi\\documents\\repos\\credit-card-fraud-detection-spark\\venv\\lib\\site-packages (from geomet<0.3,>=0.1->cassandra-driver) (8.1.7)\n",
      "Requirement already satisfied: six in c:\\users\\saritajoshi\\documents\\repos\\credit-card-fraud-detection-spark\\venv\\lib\\site-packages (from geomet<0.3,>=0.1->cassandra-driver) (1.16.0)\n",
      "Requirement already satisfied: colorama in c:\\users\\saritajoshi\\documents\\repos\\credit-card-fraud-detection-spark\\venv\\lib\\site-packages (from click->geomet<0.3,>=0.1->cassandra-driver) (0.4.6)\n",
      "Note: you may need to restart the kernel to use updated packages.\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\n",
      "[notice] A new release of pip is available: 23.1.2 -> 24.0\n",
      "[notice] To update, run: python.exe -m pip install --upgrade pip\n"
     ]
    }
   ],
   "source": [
    "pip install cassandra-driver"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from cassandra.cluster import Cluster\n",
    "\n",
    "# Connect to the local Cassandra instance\n",
    "cluster = Cluster(['127.0.0.1'])  \n",
    "session = cluster.connect()\n",
    "session.set_keyspace(\"bigdata\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "####### Testing insert query\n",
    "\n",
    "from cassandra.cluster import Cluster\n",
    "import json\n",
    "# JSON data to insert\n",
    "json_data = {\n",
    "    \"id\": \"123e4567-e89b-12d3-a456-426614174000\",\n",
    "    \"amt\": 100.50\n",
    "}\n",
    "\n",
    "json_string = json.dumps(json_data)\n",
    "\n",
    "insert_query = \"INSERT INTO test JSON %s\"\n",
    "session.execute(insert_query, [json_string])\n",
    "\n",
    "# Close the connection\n",
    "session.shutdown()\n",
    "cluster.shutdown()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Row(id=UUID('123e4567-e89b-12d3-a456-426614174000'), amt=100.5)\n"
     ]
    }
   ],
   "source": [
    "######### Testing select query\n",
    "\n",
    "select_query = \"SELECT * FROM test\"\n",
    "result_set = session.execute(select_query)\n",
    "\n",
    "for row in result_set:\n",
    "    print(row)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<cassandra.cluster.ResultSet at 0x1bd934181d0>"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from cassandra.cluster import Cluster\n",
    "\n",
    "create_table_query = \"\"\"\n",
    "    CREATE TABLE transaction_data (\n",
    "    trans_date_trans_time text,\n",
    "    cc_num bigint,\n",
    "    merchant text,\n",
    "    category text,\n",
    "    amt decimal,\n",
    "    first text,\n",
    "    last text,\n",
    "    gender text,\n",
    "    street text,\n",
    "    city text,\n",
    "    state text,\n",
    "    zip int,\n",
    "    lat decimal,\n",
    "    long decimal,\n",
    "    city_pop int,\n",
    "    job text,\n",
    "    dob date,\n",
    "    trans_num text,\n",
    "    unix_time bigint,\n",
    "    merch_lat decimal,\n",
    "    merch_long decimal,\n",
    "    merchant_label int,\n",
    "    category_label int,\n",
    "    gender_label int,\n",
    "    job_label int,\n",
    "    is_fraud int,\n",
    "    is_fraud_prediction int,\n",
    "    inserted_at timestamp,\n",
    "    PRIMARY KEY (trans_num)\n",
    "    \n",
    ");\n",
    "\n",
    "\"\"\"\n",
    "drop_query = 'drop table transaction_data'\n",
    "\n",
    "session.execute(create_table_query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "# ########### testing bank csv\n",
    "# import csv\n",
    "# import json\n",
    "# import datetime\n",
    "# import time\n",
    "# from cassandra.cluster import Cluster\n",
    "\n",
    "# def insert_data_from_csv(csv_file, keyspace, table):\n",
    "#     with open(csv_file, 'r') as file:\n",
    "#         reader = csv.DictReader(file)\n",
    "#         for i, row in enumerate(reader):\n",
    "#             if i >= 100:\n",
    "#                 break\n",
    "#             # Convert row to JSON format\n",
    "#             row['ts'] = int(time.time() * 1000)\n",
    "#             json_row = json.dumps(row)\n",
    "#             # Define CQL query to insert JSON data into Cassandra\n",
    "#             insert_query = f\"INSERT INTO {keyspace}.{table} JSON '{json_row}';\"\n",
    "#             # Execute the insert query\n",
    "#             session.execute(insert_query)\n",
    "\n",
    "# # Specify CSV file path, keyspace, and table name\n",
    "# csv_file_path = 'bank.csv'\n",
    "# keyspace_name = 'bigdata'\n",
    "# table_name = 'bank'\n",
    "\n",
    "# # Call the function to insert data from CSV into Cassandra\n",
    "# insert_data_from_csv(csv_file_path, keyspace_name, table_name)\n",
    "\n",
    "# # # Close the connection\n",
    "# # session.shutdown()\n",
    "# # cluster.shutdown()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import pandas as pd\n",
    "\n",
    "# def insert_data(df, keyspace, table):\n",
    "#     for row in df.to_dict(orient='records'):\n",
    "#         json_row = json.dumps(row)\n",
    "#         # Define CQL query to insert JSON data into Cassandra\n",
    "#         insert_query = f\"INSERT INTO {keyspace}.{table} JSON '{json_row}';\"\n",
    "#         # Execute the insert query\n",
    "#         session.execute(insert_query)\n",
    "\n",
    "# # Specify CSV file path, keyspace, and table name\n",
    "# df = pd.read_csv('bank.csv')\n",
    "# keyspace_name = 'bigdata'\n",
    "# table_name = 'bank'\n",
    "\n",
    "# for i in range(20):\n",
    "#     random_rows = df.sample(n=20)\n",
    "#     random_rows['ts'] = int(time.time() * 1000)\n",
    "#     insert_data(random_rows, keyspace_name, table_name)\n",
    "#     time.sleep(5)\n",
    "# # # Close the connection\n",
    "# # session.shutdown()\n",
    "# # cluster.shutdown()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"SELECT * FROM transaction_data;\"\n",
    "rows = session.execute(query)\n",
    "import pandas as pd\n",
    "df = pd.DataFrame(rows)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.set_option('display.max_column', None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Close the connection\n",
    "session.shutdown()\n",
    "cluster.shutdown()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "ename": "TypeError",
     "evalue": "<class 'cassandra.util.Date'> is not convertible to datetime, at position 0",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mTypeError\u001b[0m                                 Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[15], line 1\u001b[0m\n\u001b[1;32m----> 1\u001b[0m \u001b[43mpd\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mto_datetime\u001b[49m\u001b[43m(\u001b[49m\u001b[43mdf\u001b[49m\u001b[43m[\u001b[49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mdob\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m]\u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[1;32mc:\\Users\\saritajoshi\\Documents\\Repos\\Credit-Card-Fraud-Detection-Spark\\venv\\Lib\\site-packages\\pandas\\core\\tools\\datetimes.py:1067\u001b[0m, in \u001b[0;36mto_datetime\u001b[1;34m(arg, errors, dayfirst, yearfirst, utc, format, exact, unit, infer_datetime_format, origin, cache)\u001b[0m\n\u001b[0;32m   1065\u001b[0m         result \u001b[38;5;241m=\u001b[39m arg\u001b[38;5;241m.\u001b[39mmap(cache_array)\n\u001b[0;32m   1066\u001b[0m     \u001b[38;5;28;01melse\u001b[39;00m:\n\u001b[1;32m-> 1067\u001b[0m         values \u001b[38;5;241m=\u001b[39m \u001b[43mconvert_listlike\u001b[49m\u001b[43m(\u001b[49m\u001b[43marg\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_values\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;28;43mformat\u001b[39;49m\u001b[43m)\u001b[49m\n\u001b[0;32m   1068\u001b[0m         result \u001b[38;5;241m=\u001b[39m arg\u001b[38;5;241m.\u001b[39m_constructor(values, index\u001b[38;5;241m=\u001b[39marg\u001b[38;5;241m.\u001b[39mindex, name\u001b[38;5;241m=\u001b[39marg\u001b[38;5;241m.\u001b[39mname)\n\u001b[0;32m   1069\u001b[0m \u001b[38;5;28;01melif\u001b[39;00m \u001b[38;5;28misinstance\u001b[39m(arg, (ABCDataFrame, abc\u001b[38;5;241m.\u001b[39mMutableMapping)):\n",
      "File \u001b[1;32mc:\\Users\\saritajoshi\\Documents\\Repos\\Credit-Card-Fraud-Detection-Spark\\venv\\Lib\\site-packages\\pandas\\core\\tools\\datetimes.py:435\u001b[0m, in \u001b[0;36m_convert_listlike_datetimes\u001b[1;34m(arg, format, name, utc, unit, errors, dayfirst, yearfirst, exact)\u001b[0m\n\u001b[0;32m    432\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mformat\u001b[39m \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m \u001b[38;5;129;01mand\u001b[39;00m \u001b[38;5;28mformat\u001b[39m \u001b[38;5;241m!=\u001b[39m \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mmixed\u001b[39m\u001b[38;5;124m\"\u001b[39m:\n\u001b[0;32m    433\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m _array_strptime_with_fallback(arg, name, utc, \u001b[38;5;28mformat\u001b[39m, exact, errors)\n\u001b[1;32m--> 435\u001b[0m result, tz_parsed \u001b[38;5;241m=\u001b[39m \u001b[43mobjects_to_datetime64\u001b[49m\u001b[43m(\u001b[49m\n\u001b[0;32m    436\u001b[0m \u001b[43m    \u001b[49m\u001b[43marg\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    437\u001b[0m \u001b[43m    \u001b[49m\u001b[43mdayfirst\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mdayfirst\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    438\u001b[0m \u001b[43m    \u001b[49m\u001b[43myearfirst\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43myearfirst\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    439\u001b[0m \u001b[43m    \u001b[49m\u001b[43mutc\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mutc\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    440\u001b[0m \u001b[43m    \u001b[49m\u001b[43merrors\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43merrors\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    441\u001b[0m \u001b[43m    \u001b[49m\u001b[43mallow_object\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;28;43;01mTrue\u001b[39;49;00m\u001b[43m,\u001b[49m\n\u001b[0;32m    442\u001b[0m \u001b[43m\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m    444\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m tz_parsed \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m:\n\u001b[0;32m    445\u001b[0m     \u001b[38;5;66;03m# We can take a shortcut since the datetime64 numpy array\u001b[39;00m\n\u001b[0;32m    446\u001b[0m     \u001b[38;5;66;03m# is in UTC\u001b[39;00m\n\u001b[0;32m    447\u001b[0m     out_unit \u001b[38;5;241m=\u001b[39m np\u001b[38;5;241m.\u001b[39mdatetime_data(result\u001b[38;5;241m.\u001b[39mdtype)[\u001b[38;5;241m0\u001b[39m]\n",
      "File \u001b[1;32mc:\\Users\\saritajoshi\\Documents\\Repos\\Credit-Card-Fraud-Detection-Spark\\venv\\Lib\\site-packages\\pandas\\core\\arrays\\datetimes.py:2398\u001b[0m, in \u001b[0;36mobjects_to_datetime64\u001b[1;34m(data, dayfirst, yearfirst, utc, errors, allow_object, out_unit)\u001b[0m\n\u001b[0;32m   2395\u001b[0m \u001b[38;5;66;03m# if str-dtype, convert\u001b[39;00m\n\u001b[0;32m   2396\u001b[0m data \u001b[38;5;241m=\u001b[39m np\u001b[38;5;241m.\u001b[39masarray(data, dtype\u001b[38;5;241m=\u001b[39mnp\u001b[38;5;241m.\u001b[39mobject_)\n\u001b[1;32m-> 2398\u001b[0m result, tz_parsed \u001b[38;5;241m=\u001b[39m \u001b[43mtslib\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43marray_to_datetime\u001b[49m\u001b[43m(\u001b[49m\n\u001b[0;32m   2399\u001b[0m \u001b[43m    \u001b[49m\u001b[43mdata\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   2400\u001b[0m \u001b[43m    \u001b[49m\u001b[43merrors\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43merrors\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   2401\u001b[0m \u001b[43m    \u001b[49m\u001b[43mutc\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mutc\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   2402\u001b[0m \u001b[43m    \u001b[49m\u001b[43mdayfirst\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mdayfirst\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   2403\u001b[0m \u001b[43m    \u001b[49m\u001b[43myearfirst\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43myearfirst\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   2404\u001b[0m \u001b[43m    \u001b[49m\u001b[43mcreso\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mabbrev_to_npy_unit\u001b[49m\u001b[43m(\u001b[49m\u001b[43mout_unit\u001b[49m\u001b[43m)\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   2405\u001b[0m \u001b[43m\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m   2407\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m tz_parsed \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m:\n\u001b[0;32m   2408\u001b[0m     \u001b[38;5;66;03m# We can take a shortcut since the datetime64 numpy array\u001b[39;00m\n\u001b[0;32m   2409\u001b[0m     \u001b[38;5;66;03m#  is in UTC\u001b[39;00m\n\u001b[0;32m   2410\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m result, tz_parsed\n",
      "File \u001b[1;32mtslib.pyx:414\u001b[0m, in \u001b[0;36mpandas._libs.tslib.array_to_datetime\u001b[1;34m()\u001b[0m\n",
      "File \u001b[1;32mtslib.pyx:596\u001b[0m, in \u001b[0;36mpandas._libs.tslib.array_to_datetime\u001b[1;34m()\u001b[0m\n",
      "File \u001b[1;32mtslib.pyx:588\u001b[0m, in \u001b[0;36mpandas._libs.tslib.array_to_datetime\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;31mTypeError\u001b[0m: <class 'cassandra.util.Date'> is not convertible to datetime, at position 0"
     ]
    }
   ],
   "source": [
    "pd.to_datetime(df['dob'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "venv",
   "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.11.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}