{
"cells": [
{
"cell_type": "code",
"execution_count": 1,
"id": "11035b24-6195-412f-af98-50e41ce8b3d0",
"metadata": {},
"outputs": [],
"source": [
"from cassandra.cluster import Cluster\n",
"cluster = Cluster([\"p6-db-1\", \"p6-db-2\", \"p6-db-3\"])\n",
"cass = cluster.connect()"
]
},
{
"cell_type": "code",
"execution_count": 2,
"id": "480162ed-d618-4dde-bda6-03249f609a69",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"<cassandra.cluster.ResultSet at 0x70c6d9b05330>"
]
},
"execution_count": 2,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"cass.execute(\"use banking\")"
]
},
{
"cell_type": "code",
"execution_count": 3,
"id": "ec630031-d518-4747-ac1f-8ec40aa43251",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"<cassandra.cluster.ResultSet at 0x70c6d9b06500>"
]
},
"execution_count": 3,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"cass.execute(\"\"\"\n",
"create table loans (\n",
" bank_id int,\n",
" bank_name text STATIC,\n",
" loan_id UUID,\n",
" amount int,\n",
" state text,\n",
" PRIMARY KEY ((bank_id), amount, loan_id)\n",
") WITH CLUSTERING ORDER BY (amount DESC)\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 12,
"id": "286ead09-fbf5-491d-831e-fb0056edd134",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"CREATE TABLE banking.loans (\n",
" bank_id int,\n",
" amount int,\n",
" loan_id uuid,\n",
" bank_name text static,\n",
" state text,\n",
" PRIMARY KEY (bank_id, amount, loan_id)\n",
") WITH CLUSTERING ORDER BY (amount DESC, loan_id ASC)\n",
" AND additional_write_policy = '99p'\n",
" AND allow_auto_snapshot = true\n",
" AND bloom_filter_fp_chance = 0.01\n",
" AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}\n",
" AND cdc = false\n",
" AND comment = ''\n",
" AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}\n",
" AND compression = {'chunk_length_in_kb': '16', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}\n",
" AND memtable = 'default'\n",
" AND crc_check_chance = 1.0\n",
" AND default_time_to_live = 0\n",
" AND extensions = {}\n",
" AND gc_grace_seconds = 864000\n",
" AND incremental_backups = true\n",
" AND max_index_interval = 2048\n",
" AND memtable_flush_period_in_ms = 0\n",
" AND min_index_interval = 128\n",
" AND read_repair = 'BLOCKING'\n",
" AND speculative_retry = '99p';\n"
]
}
],
"source": [
"print(cass.execute(\"describe table loans\").one().create_statement)"
]
},
{
"cell_type": "code",
"execution_count": 19,
"id": "c4f6335b-6340-455e-921c-4b6ac2e1d67b",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"<cassandra.cluster.ResultSet at 0x70c6d17263e0>"
]
},
"execution_count": 19,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"cass.execute(\"\"\"\n",
"INSERT INTO loans (bank_id, bank_name)\n",
"VALUES (544, 'test')\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 21,
"id": "55c10edd-126d-4a10-98c8-708f2b337f99",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"<cassandra.cluster.ResultSet at 0x70c6d9b056c0>"
]
},
"execution_count": 21,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# INSERT is actually UPSERT (insert or update)\n",
"cass.execute(\"\"\"\n",
"INSERT INTO loans (bank_id, bank_name)\n",
"VALUES (544, 'test2')\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 22,
"id": "99035291-59eb-4c00-99a1-9737d4e406d9",
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>bank_id</th>\n",
" <th>amount</th>\n",
" <th>loan_id</th>\n",
" <th>bank_name</th>\n",
" <th>state</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>544</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>test2</td>\n",
" <td>None</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" bank_id amount loan_id bank_name state\n",
"0 544 None None test2 None"
]
},
"execution_count": 22,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"import pandas as pd\n",
"pd.DataFrame(cass.execute(\"SELECT * FROM loans\"))"
]
},
{
"cell_type": "code",
"execution_count": 24,
"id": "896b8215-7282-4b73-9ad2-8f0761879253",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"<cassandra.cluster.ResultSet at 0x70c6d1726830>"
]
},
"execution_count": 24,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"cass.execute(\"\"\"\n",
"INSERT INTO loans (bank_id, bank_name, loan_id, amount)\n",
"VALUES (544, 'test2', UUID(), 300)\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 25,
"id": "b8e2a46a-6e86-49ef-9d1c-0da26eec0a53",
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>bank_id</th>\n",
" <th>amount</th>\n",
" <th>loan_id</th>\n",
" <th>bank_name</th>\n",
" <th>state</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>544</td>\n",
" <td>300</td>\n",
" <td>01976a29-7d80-435f-ba6b-e22abc9d10f3</td>\n",
" <td>test2</td>\n",
" <td>None</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" bank_id amount loan_id bank_name state\n",
"0 544 300 01976a29-7d80-435f-ba6b-e22abc9d10f3 test2 None"
]
},
"execution_count": 25,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"pd.DataFrame(cass.execute(\"SELECT * FROM loans\"))"
]
},
{
"cell_type": "code",
"execution_count": 32,
"id": "c87c69d1-4a9e-4065-9a95-8e47a08755c1",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"<cassandra.cluster.ResultSet at 0x70c6d3f9eec0>"
]
},
"execution_count": 32,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# NOW and UUID both generate UUIDs.\n",
"# NOW is stronge because it uses MAC addresses and timestamps\n",
"\n",
"# this is both an INSERT (inserting a row) and UPDATE (on the partition)\n",
"cass.execute(\"\"\"\n",
"INSERT INTO loans (bank_id, bank_name, loan_id, amount, state)\n",
"VALUES (544, 'mybank2', NOW(), 350, 'wi')\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 33,
"id": "032cc612-c753-4bbe-bac3-ca5a8df6cd02",
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>bank_id</th>\n",
" <th>amount</th>\n",
" <th>loan_id</th>\n",
" <th>bank_name</th>\n",
" <th>state</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>544</td>\n",
" <td>400</td>\n",
" <td>2b644550-0674-11f0-9e42-b531eb6d9b34</td>\n",
" <td>mybank2</td>\n",
" <td>wi</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>544</td>\n",
" <td>350</td>\n",
" <td>5c852cd0-0674-11f0-8b0a-b3bc8dc2bdb9</td>\n",
" <td>mybank2</td>\n",
" <td>wi</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>544</td>\n",
" <td>300</td>\n",
" <td>aebdb7c0-0673-11f0-8b0a-b3bc8dc2bdb9</td>\n",
" <td>mybank2</td>\n",
" <td>wi</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>544</td>\n",
" <td>300</td>\n",
" <td>f8c80870-0673-11f0-8acf-b5f913312dcb</td>\n",
" <td>mybank2</td>\n",
" <td>wi</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>544</td>\n",
" <td>300</td>\n",
" <td>01976a29-7d80-435f-ba6b-e22abc9d10f3</td>\n",
" <td>mybank2</td>\n",
" <td>None</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" bank_id amount loan_id bank_name state\n",
"0 544 400 2b644550-0674-11f0-9e42-b531eb6d9b34 mybank2 wi\n",
"1 544 350 5c852cd0-0674-11f0-8b0a-b3bc8dc2bdb9 mybank2 wi\n",
"2 544 300 aebdb7c0-0673-11f0-8b0a-b3bc8dc2bdb9 mybank2 wi\n",
"3 544 300 f8c80870-0673-11f0-8acf-b5f913312dcb mybank2 wi\n",
"4 544 300 01976a29-7d80-435f-ba6b-e22abc9d10f3 mybank2 None"
]
},
"execution_count": 33,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"pd.DataFrame(cass.execute(\"SELECT * FROM loans\"))"
]
},
{
"cell_type": "code",
"execution_count": 34,
"id": "d8142b23-c6c4-4b77-b5dd-8684c47c05c8",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"<cassandra.cluster.ResultSet at 0x70c6ad556e30>"
]
},
"execution_count": 34,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"cass.execute(\"\"\"\n",
"INSERT INTO loans (bank_id, bank_name, loan_id, amount, state)\n",
"VALUES (999, 'uwcu', NOW(), 500, 'il')\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 35,
"id": "b3b1ef23-db1a-43c8-b572-09a48f4aba41",
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>bank_id</th>\n",
" <th>amount</th>\n",
" <th>loan_id</th>\n",
" <th>bank_name</th>\n",
" <th>state</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>544</td>\n",
" <td>400</td>\n",
" <td>2b644550-0674-11f0-9e42-b531eb6d9b34</td>\n",
" <td>mybank2</td>\n",
" <td>wi</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>544</td>\n",
" <td>350</td>\n",
" <td>5c852cd0-0674-11f0-8b0a-b3bc8dc2bdb9</td>\n",
" <td>mybank2</td>\n",
" <td>wi</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>544</td>\n",
" <td>300</td>\n",
" <td>aebdb7c0-0673-11f0-8b0a-b3bc8dc2bdb9</td>\n",
" <td>mybank2</td>\n",
" <td>wi</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>544</td>\n",
" <td>300</td>\n",
" <td>f8c80870-0673-11f0-8acf-b5f913312dcb</td>\n",
" <td>mybank2</td>\n",
" <td>wi</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>544</td>\n",
" <td>300</td>\n",
" <td>01976a29-7d80-435f-ba6b-e22abc9d10f3</td>\n",
" <td>mybank2</td>\n",
" <td>None</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>999</td>\n",
" <td>500</td>\n",
" <td>78e4a9f0-0674-11f0-8acf-b5f913312dcb</td>\n",
" <td>uwcu</td>\n",
" <td>il</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" bank_id amount loan_id bank_name state\n",
"0 544 400 2b644550-0674-11f0-9e42-b531eb6d9b34 mybank2 wi\n",
"1 544 350 5c852cd0-0674-11f0-8b0a-b3bc8dc2bdb9 mybank2 wi\n",
"2 544 300 aebdb7c0-0673-11f0-8b0a-b3bc8dc2bdb9 mybank2 wi\n",
"3 544 300 f8c80870-0673-11f0-8acf-b5f913312dcb mybank2 wi\n",
"4 544 300 01976a29-7d80-435f-ba6b-e22abc9d10f3 mybank2 None\n",
"5 999 500 78e4a9f0-0674-11f0-8acf-b5f913312dcb uwcu il"
]
},
"execution_count": 35,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"pd.DataFrame(cass.execute(\"SELECT * FROM loans\"))"
]
}
],
"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.10.12"
}
},
"nbformat": 4,
"nbformat_minor": 5
}