{
"cells": [
{
"cell_type": "markdown",
"metadata": {
"id": "view-in-github",
"colab_type": "text"
},
"source": [
"![\"Open](\"https://colab.research.google.com/assets/colab-badge.svg\")
"
]
},
{
"cell_type": "markdown",
"id": "d39741e7-daf4-48a0-a7a0-b9bbba800b5b",
"metadata": {
"id": "d39741e7-daf4-48a0-a7a0-b9bbba800b5b"
},
"source": [
"## Cassandra Basics"
]
},
{
"cell_type": "markdown",
"source": [
""
],
"metadata": {
"id": "OgwFx4dz677r"
},
"id": "OgwFx4dz677r"
},
{
"cell_type": "markdown",
"id": "8dc9c780-dde3-40c1-98ee-b9b9954f8037",
"metadata": {
"id": "8dc9c780-dde3-40c1-98ee-b9b9954f8037"
},
"source": [
"## Apache Cassandra is an open source non-relational (or NoSQL) database that enables continuous availability, tremendous scale, and data distribution across multiple data centers and cloud availability zones. It is the database of choice for scalable, highly available, globally-distributed, always-on applications."
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "c517c7ba-c133-4fbd-ac19-5b1d1b2dfefd",
"metadata": {
"id": "c517c7ba-c133-4fbd-ac19-5b1d1b2dfefd"
},
"outputs": [],
"source": [
"# !pip install faker"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "76134251-4e55-4ac1-8824-4900b7226c75",
"metadata": {
"id": "76134251-4e55-4ac1-8824-4900b7226c75"
},
"outputs": [],
"source": [
"from cassandra.cluster import Cluster\n",
"from cassandra.auth import PlainTextAuthProvider"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "356108c1-0276-478b-a835-36d83fbe839c",
"metadata": {
"id": "356108c1-0276-478b-a835-36d83fbe839c"
},
"outputs": [],
"source": [
"# If your Cassandra cluster requires authentication:\n",
"cluster = Cluster(['127.0.0.1'])\n",
"\n",
"# Establish a session\n",
"session = cluster.connect()\n",
"\n",
"# If you're connecting to a specific keyspace:\n",
"# session = cluster.connect('your_keyspace')"
]
},
{
"cell_type": "markdown",
"id": "398f2b92-b753-4727-8ce3-f8e7c27ca408",
"metadata": {
"id": "398f2b92-b753-4727-8ce3-f8e7c27ca408"
},
"source": [
"## In cassandra, keyspaces = database;"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "89794510-50aa-4721-b210-85efe4a1eb45",
"metadata": {
"id": "89794510-50aa-4721-b210-85efe4a1eb45",
"outputId": "d9a074a3-c365-4083-b60b-dcaa19d47075"
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"system\n",
"system_auth\n",
"system_distributed\n",
"system_schema\n",
"system_traces\n",
"system_views\n",
"system_virtual_schema\n"
]
}
],
"source": [
"# Retrieve and print the list of keyspaces\n",
"# keyspaces = session.execute(\"SELECT keyspace_name FROM system_schema.keyspaces;\")\n",
"# for keyspace in keyspaces:\n",
"# print(keyspace.keyspace_name)\n",
"keyspaces = session.execute(\"DESCRIBE keyspaces;\")\n",
"for keyspace in keyspaces:\n",
" print(keyspace.keyspace_name)"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "bfd6b671-5516-4d9f-92ed-39998a962ae8",
"metadata": {
"id": "bfd6b671-5516-4d9f-92ed-39998a962ae8"
},
"outputs": [],
"source": [
"from faker import Faker\n",
"# Initialize Faker\n",
"fake = Faker()"
]
},
{
"cell_type": "markdown",
"id": "64a0bd79-cfeb-4fd7-a48f-c42906fb15e8",
"metadata": {
"id": "64a0bd79-cfeb-4fd7-a48f-c42906fb15e8"
},
"source": [
"### these are default keyspaces; so we would make one for us"
]
},
{
"cell_type": "markdown",
"id": "000243ba-54ad-45e1-a35d-9520d7104315",
"metadata": {
"id": "000243ba-54ad-45e1-a35d-9520d7104315"
},
"source": [
"## Create a Keyspace\n",
"### Create a keyspace for our user data. For simplicity and demonstration, we'll use SimpleStrategy with a replication factor of 1. In a production environment, consider using NetworkTopologyStrategy."
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "cd9d56b1-4774-452b-bef7-d708ba0f1b82",
"metadata": {
"id": "cd9d56b1-4774-452b-bef7-d708ba0f1b82"
},
"outputs": [],
"source": [
"# Create a keyspace\n",
"session.execute(\"\"\"\n",
"CREATE KEYSPACE IF NOT EXISTS user_data\n",
"WITH replication = {'class': 'SimpleStrategy', 'replication_factor': '1'}\n",
"\"\"\")\n",
"session.set_keyspace('user_data')"
]
},
{
"cell_type": "markdown",
"id": "f5329a16-7141-4d13-9005-d750f49a9b58",
"metadata": {
"id": "f5329a16-7141-4d13-9005-d750f49a9b58"
},
"source": [
"## Create Tables\n",
"## Define three tables within our keyspace to hold user profiles, activities, and preferences."
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "9639867f-3aa1-4ee9-838a-669247a1024b",
"metadata": {
"id": "9639867f-3aa1-4ee9-838a-669247a1024b",
"outputId": "5f28f20b-9d96-4bef-96f8-a5271e340d3a"
},
"outputs": [
{
"data": {
"text/plain": [
""
]
},
"execution_count": 17,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# Table for user profiles\n",
"session.execute(\"\"\"\n",
"CREATE TABLE IF NOT EXISTS user_profiles (\n",
" user_id UUID PRIMARY KEY,\n",
" name TEXT,\n",
" email TEXT,\n",
" age INT\n",
")\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "be60ea46-a0cf-473d-8ca9-c2972ce29d32",
"metadata": {
"id": "be60ea46-a0cf-473d-8ca9-c2972ce29d32",
"outputId": "c80846d0-9b9a-4047-d966-83727ebd98ac"
},
"outputs": [
{
"data": {
"text/plain": [
""
]
},
"execution_count": 18,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# Table for user activities\n",
"session.execute(\"\"\"\n",
"CREATE TABLE IF NOT EXISTS user_activities (\n",
" activity_id UUID PRIMARY KEY,\n",
" user_id UUID,\n",
" activity_type TEXT,\n",
" timestamp TIMESTAMP,\n",
" details TEXT\n",
")\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "bdaadcf8-4c32-4aa3-a664-4f42cb94cfd0",
"metadata": {
"id": "bdaadcf8-4c32-4aa3-a664-4f42cb94cfd0",
"outputId": "97a446d6-c157-48a5-c438-20b0e8cf0f22"
},
"outputs": [
{
"data": {
"text/plain": [
""
]
},
"execution_count": 19,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# Table for user preferences\n",
"session.execute(\"\"\"\n",
"CREATE TABLE IF NOT EXISTS preference_data (\n",
" user_id UUID PRIMARY KEY,\n",
" preferences MAP\n",
")\n",
"\"\"\")"
]
},
{
"cell_type": "markdown",
"id": "e14b3c75-571d-4e69-ae41-7aedc9e50d94",
"metadata": {
"id": "e14b3c75-571d-4e69-ae41-7aedc9e50d94"
},
"source": [
"### check for keyspace and tables"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "683114fe-6458-49f3-805f-c5925592c92f",
"metadata": {
"id": "683114fe-6458-49f3-805f-c5925592c92f",
"outputId": "b03aff8f-72a5-43a1-86e9-adaa7cf8314a"
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"system\n",
"system_auth\n",
"system_distributed\n",
"system_schema\n",
"system_traces\n",
"system_views\n",
"system_virtual_schema\n",
"user_data\n"
]
}
],
"source": [
"keyspaces = session.execute(\"DESCRIBE keyspaces;\")\n",
"for keyspace in keyspaces:\n",
" print(keyspace.keyspace_name)"
]
},
{
"cell_type": "markdown",
"id": "44cab5d3-1769-4daf-baf2-1bd708c6f510",
"metadata": {
"id": "44cab5d3-1769-4daf-baf2-1bd708c6f510"
},
"source": [
"## we can verify our user_data keyspace and the tables present;"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "da2b0ed2-ae64-4a8c-80fa-b8d0e051abb0",
"metadata": {
"id": "da2b0ed2-ae64-4a8c-80fa-b8d0e051abb0",
"outputId": "3d5a60f2-c3f9-4aae-8bc0-e5d0eb281079"
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"preference_data\n",
"user_activities\n",
"user_profiles\n"
]
}
],
"source": [
"\n",
"tables = session.execute(\"DESCRIBE KEYSPACE user_data;\")\n",
"for table in tables[1:]:\n",
" print(table.name)"
]
},
{
"cell_type": "markdown",
"id": "cbfdd619-6483-4387-a917-59e53e8871bb",
"metadata": {
"id": "cbfdd619-6483-4387-a917-59e53e8871bb"
},
"source": [
"## query examples"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "e1f9b2d8-57b6-4b01-9207-63c29c77a7f2",
"metadata": {
"id": "e1f9b2d8-57b6-4b01-9207-63c29c77a7f2"
},
"outputs": [],
"source": [
"# Example: Querying user_profiles\n",
"rows = session.execute(\"SELECT * FROM user_profiles LIMIT 10\")\n",
"for row in rows:\n",
" print(row)\n"
]
},
{
"cell_type": "markdown",
"id": "601f9b0e-2fe3-4154-9e92-097555dde870",
"metadata": {
"id": "601f9b0e-2fe3-4154-9e92-097555dde870"
},
"source": [
"### empty because we havn't added any data yet"
]
},
{
"cell_type": "markdown",
"id": "43082e77-2458-4c11-8326-cc2a65a4de81",
"metadata": {
"id": "43082e77-2458-4c11-8326-cc2a65a4de81"
},
"source": [
"## Insert Sample Data\n",
"### Now, let's populate these tables with sample data using Faker\n",
"\n",
"### Insert Data into user_profiles"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "b7e56573-512c-49ab-aeae-19c70260396e",
"metadata": {
"id": "b7e56573-512c-49ab-aeae-19c70260396e"
},
"outputs": [],
"source": [
"import uuid"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "643b9a73-a598-49c3-8244-5642deacc761",
"metadata": {
"id": "643b9a73-a598-49c3-8244-5642deacc761"
},
"outputs": [],
"source": [
"for _ in range(1000):\n",
" session.execute(\n",
" \"\"\"\n",
" INSERT INTO user_profiles (user_id, name, email, age) VALUES (%s, %s, %s, %s)\n",
" \"\"\",\n",
" (uuid.uuid4(), fake.name(), fake.email(), fake.random_int(min=18, max=80))\n",
" )\n"
]
},
{
"cell_type": "markdown",
"id": "0bcf99a9-b49d-46a1-9692-19c20a426b6e",
"metadata": {
"id": "0bcf99a9-b49d-46a1-9692-19c20a426b6e"
},
"source": [
"## lets query"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "235c4eee-734f-460c-ae20-276e784d4829",
"metadata": {
"id": "235c4eee-734f-460c-ae20-276e784d4829",
"outputId": "87cd245b-1ef7-40ac-b3d2-46d1ca5936a7"
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Row(user_id=UUID('866ffe42-6287-4dd3-8f3a-86c2b9dc381a'), age=50, email='baileyapril@example.com', name='Scott Franco')\n",
"Row(user_id=UUID('c564fb6c-4999-464e-9ae6-21e28663acd0'), age=67, email='jonathandavis@example.net', name='Mr. Eric Lewis Jr.')\n",
"Row(user_id=UUID('d830f41f-1b02-4f5d-b163-7784fa1ffb24'), age=41, email='donnaochoa@example.com', name='Billy Evans')\n",
"Row(user_id=UUID('81126ab4-842e-4d38-9a26-10c1f9237dcd'), age=21, email='mendezcassandra@example.org', name='Jonathan Cervantes')\n",
"Row(user_id=UUID('fdc0fa0b-6df8-4823-a2ea-b72235296ae0'), age=42, email='theresacooper@example.org', name='Angela Wright')\n",
"Row(user_id=UUID('082fe019-1dd7-4bea-9d6b-2e15d581e7f5'), age=44, email='alexisturner@example.net', name='Kelly Baker')\n",
"Row(user_id=UUID('1cd1ed49-3375-4e8a-bf33-46b0a4e1c73e'), age=49, email='smithbrittany@example.com', name='Gail Griffin')\n",
"Row(user_id=UUID('7c3c1e81-044c-47f3-a232-14caf878faba'), age=62, email='smithnicholas@example.com', name='Debbie Hughes')\n",
"Row(user_id=UUID('a2d0fa38-790c-4128-87e8-771c5cd82e1f'), age=76, email='vincentfreeman@example.com', name='Zachary Robinson')\n",
"Row(user_id=UUID('0451be3c-76be-43a9-aa73-ad883f99d68e'), age=48, email='sherrigray@example.net', name='Thomas Mitchell')\n",
"Row(user_id=UUID('ec3d870d-3065-4f80-866d-6b3aba76ad21'), age=27, email='christopherwright@example.com', name='Renee Little')\n",
"Row(user_id=UUID('2d18ce6f-6547-435b-9f05-138e3ec04fb5'), age=68, email='elizabeth77@example.org', name='Joshua King')\n",
"Row(user_id=UUID('37b62a60-ceb4-4759-a2a0-aba8990ba786'), age=64, email='mary56@example.org', name='Jeffrey Maldonado')\n",
"Row(user_id=UUID('46259ef1-956e-45e0-9c2d-7fd9e2a1f8d9'), age=33, email='allenalexis@example.com', name='Dominique Farrell')\n",
"Row(user_id=UUID('501a2ced-8d2b-4296-ad07-3ce68258f115'), age=28, email='martinezbrandy@example.org', name='Heather Smith')\n",
"Row(user_id=UUID('f7ec1bae-7063-4a2c-8aa9-b7c4e904843b'), age=41, email='hudsonalexis@example.com', name='Tiffany Smith')\n",
"Row(user_id=UUID('7fd4ee17-2bfb-4eda-846b-2c6695543fb2'), age=40, email='tonywood@example.com', name='Dominic Lee')\n",
"Row(user_id=UUID('c60be302-fdcf-41a7-99c5-9e1a845a5649'), age=70, email='velezdennis@example.net', name='Tristan Pena')\n",
"Row(user_id=UUID('ec2de58a-3427-4332-bad8-c9daf4d427da'), age=80, email='melissabranch@example.org', name='Thomas Jackson')\n",
"Row(user_id=UUID('6f5facac-1c47-454e-84f2-12ad3d644394'), age=44, email='ericjordan@example.net', name='Lindsay Grimes')\n"
]
}
],
"source": [
"# Example: Querying user_profiles\n",
"rows = session.execute(\"SELECT * FROM user_profiles LIMIT 20\")\n",
"for row in rows:\n",
" print(row)"
]
},
{
"cell_type": "markdown",
"id": "1bfd4b04-6f6e-430d-8acd-75dbea539f4c",
"metadata": {
"id": "1bfd4b04-6f6e-430d-8acd-75dbea539f4c"
},
"source": [
"### Insert Data into user_activities"
]
},
{
"cell_type": "markdown",
"id": "b8442e3f-0bae-464e-8f7b-8fabb3b27860",
"metadata": {
"id": "b8442e3f-0bae-464e-8f7b-8fabb3b27860"
},
"source": [
"## We'll simulate 10,000 activities, possibly with multiple entries per user."
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "210b8eac-57a8-496a-b3c3-18b0a2b52b4b",
"metadata": {
"id": "210b8eac-57a8-496a-b3c3-18b0a2b52b4b"
},
"outputs": [],
"source": [
"user_ids = [row.user_id for row in session.execute(\"SELECT user_id FROM user_profiles\")]\n",
"\n",
"for _ in range(10000):\n",
" session.execute(\n",
" \"\"\"\n",
" INSERT INTO user_activities (activity_id, user_id, activity_type, timestamp, details) VALUES (%s, %s, %s, %s, %s)\n",
" \"\"\",\n",
" (uuid.uuid4(), fake.random.choice(user_ids), fake.random.choice(['login', 'logout', 'purchase', 'browse']), fake.date_time_this_year(), fake.sentence())\n",
" )"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "4d6e5cae-cd04-488e-88b4-4fe4a1cf66a4",
"metadata": {
"id": "4d6e5cae-cd04-488e-88b4-4fe4a1cf66a4",
"outputId": "042691c9-e3cb-4d36-c5c2-ebbb2cfa0c35"
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Row(activity_id=UUID('673f4dba-40e2-4218-ae31-43bfe05c25ff'), activity_type='login', details='Sense understand truth positive.', timestamp=datetime.datetime(2024, 1, 4, 7, 46, 34, 847000), user_id=UUID('3f25bac6-a773-44f3-85f3-2be39d141030'))\n",
"Row(activity_id=UUID('0eeb7d45-3a33-4ea4-949f-85545ed21110'), activity_type='login', details='Left nice president tell note operation knowledge least.', timestamp=datetime.datetime(2024, 1, 24, 2, 0, 32, 862000), user_id=UUID('88c19fe2-a3a4-409d-898e-7156efc79cc6'))\n",
"Row(activity_id=UUID('853c2c63-5b0e-492e-8652-0faa0e7065b1'), activity_type='browse', details='Result three civil.', timestamp=datetime.datetime(2024, 2, 24, 3, 12, 26, 624000), user_id=UUID('a57192a5-950b-4755-8e17-f121967d8c65'))\n",
"Row(activity_id=UUID('2aafe27a-9105-4a24-8721-167a3d587f0c'), activity_type='login', details='Baby will others wall thing paper.', timestamp=datetime.datetime(2024, 2, 26, 5, 56, 33, 116000), user_id=UUID('a19f2f99-8319-400e-86cf-7c702b221ca6'))\n",
"Row(activity_id=UUID('2157dcbd-0212-40af-8cda-931c520fa170'), activity_type='login', details='Try nothing different world senior control.', timestamp=datetime.datetime(2024, 2, 27, 20, 25, 37, 604000), user_id=UUID('a61a0be6-277c-4791-8b42-6564898b52c6'))\n",
"Row(activity_id=UUID('94bec722-0e42-445f-bd38-ee2faaa9d4d7'), activity_type='login', details='Seven trial war various easy seven.', timestamp=datetime.datetime(2024, 2, 8, 1, 57, 35, 985000), user_id=UUID('1fca6dca-a7a4-4a79-a871-ce9dcc7c913b'))\n",
"Row(activity_id=UUID('1437e0af-f0e6-4a2b-b762-d1a8135c0f03'), activity_type='purchase', details='Day control maybe only grow.', timestamp=datetime.datetime(2024, 2, 14, 17, 34, 11, 111000), user_id=UUID('a79edaa0-25f1-475a-814e-b244c7d13a63'))\n",
"Row(activity_id=UUID('b1cd6c4c-8b52-4e54-99f7-a483b7cedb85'), activity_type='logout', details='Open stay bank begin really.', timestamp=datetime.datetime(2024, 2, 19, 20, 8, 36, 46000), user_id=UUID('ef55198b-43c0-45d7-99e2-6cbba476ee98'))\n",
"Row(activity_id=UUID('4d8e79df-dc44-44c2-833c-d5cc43c29755'), activity_type='login', details='Already operation well story option.', timestamp=datetime.datetime(2024, 3, 7, 0, 32, 8, 810000), user_id=UUID('b78b7664-eefc-4649-9dcf-6580d4e13342'))\n",
"Row(activity_id=UUID('423255af-96ba-4bda-af89-daaf1646073f'), activity_type='login', details='Thank word big same effort late either someone.', timestamp=datetime.datetime(2024, 2, 13, 13, 55, 18, 165000), user_id=UUID('8eac3acf-6abf-44d6-9e53-cd4be01f9a62'))\n"
]
}
],
"source": [
"# Example: Querying user_activities\n",
"rows = session.execute(\"SELECT * FROM user_activities LIMIT 10\")\n",
"for row in rows:\n",
" print(row)"
]
},
{
"cell_type": "markdown",
"id": "b8ffdb2d-5d2d-4544-ab32-d1057fc7e0fd",
"metadata": {
"id": "b8ffdb2d-5d2d-4544-ab32-d1057fc7e0fd"
},
"source": [
"### Insert Data into preference_data"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "a63c5523-5075-413a-85d2-5fbb355182cc",
"metadata": {
"id": "a63c5523-5075-413a-85d2-5fbb355182cc"
},
"outputs": [],
"source": [
"for user_id in user_ids:\n",
" session.execute(\n",
" \"\"\"\n",
" INSERT INTO preference_data (user_id, preferences) VALUES (%s, %s)\n",
" \"\"\",\n",
" (user_id, {'theme': fake.random.choice(['dark', 'light']), 'language': fake.random.choice(['en', 'es', 'fr', 'de'])})\n",
" )\n"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "f679974c-f5fb-4ba0-a507-c8a5714cce93",
"metadata": {
"id": "f679974c-f5fb-4ba0-a507-c8a5714cce93",
"outputId": "0c8ac3f9-4de1-452b-8a34-a09b3640968d"
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Row(user_id=UUID('866ffe42-6287-4dd3-8f3a-86c2b9dc381a'), preferences=OrderedMapSerializedKey([('language', 'de'), ('theme', 'dark')]))\n",
"Row(user_id=UUID('c564fb6c-4999-464e-9ae6-21e28663acd0'), preferences=OrderedMapSerializedKey([('language', 'fr'), ('theme', 'dark')]))\n",
"Row(user_id=UUID('d830f41f-1b02-4f5d-b163-7784fa1ffb24'), preferences=OrderedMapSerializedKey([('language', 'de'), ('theme', 'light')]))\n",
"Row(user_id=UUID('81126ab4-842e-4d38-9a26-10c1f9237dcd'), preferences=OrderedMapSerializedKey([('language', 'de'), ('theme', 'light')]))\n",
"Row(user_id=UUID('fdc0fa0b-6df8-4823-a2ea-b72235296ae0'), preferences=OrderedMapSerializedKey([('language', 'es'), ('theme', 'light')]))\n",
"Row(user_id=UUID('082fe019-1dd7-4bea-9d6b-2e15d581e7f5'), preferences=OrderedMapSerializedKey([('language', 'en'), ('theme', 'light')]))\n",
"Row(user_id=UUID('1cd1ed49-3375-4e8a-bf33-46b0a4e1c73e'), preferences=OrderedMapSerializedKey([('language', 'fr'), ('theme', 'dark')]))\n",
"Row(user_id=UUID('7c3c1e81-044c-47f3-a232-14caf878faba'), preferences=OrderedMapSerializedKey([('language', 'en'), ('theme', 'dark')]))\n",
"Row(user_id=UUID('a2d0fa38-790c-4128-87e8-771c5cd82e1f'), preferences=OrderedMapSerializedKey([('language', 'es'), ('theme', 'dark')]))\n",
"Row(user_id=UUID('0451be3c-76be-43a9-aa73-ad883f99d68e'), preferences=OrderedMapSerializedKey([('language', 'es'), ('theme', 'light')]))\n"
]
}
],
"source": [
"# Example: Querying user_activities\n",
"rows = session.execute(\"SELECT * FROM preference_data LIMIT 10\")\n",
"for row in rows:\n",
" print(row)"
]
},
{
"cell_type": "markdown",
"id": "5781dded-9a65-45f0-983d-4787ed2575bc",
"metadata": {
"id": "5781dded-9a65-45f0-983d-4787ed2575bc"
},
"source": [
"### consider that we only have two attributes here; later we will update one entry without explicitly defining the schema"
]
},
{
"cell_type": "markdown",
"id": "29ba8931-adc0-4c3f-a6a4-124bccdf7a7c",
"metadata": {
"id": "29ba8931-adc0-4c3f-a6a4-124bccdf7a7c"
},
"source": [
"## more examples of read"
]
},
{
"cell_type": "markdown",
"id": "72e78544-4e28-4427-bb2b-5726c163c027",
"metadata": {
"id": "72e78544-4e28-4427-bb2b-5726c163c027"
},
"source": [
"## Select all profiles:"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "9d44a86b-64bc-4e69-8d0d-903a09450faf",
"metadata": {
"id": "9d44a86b-64bc-4e69-8d0d-903a09450faf"
},
"outputs": [],
"source": [
"# Example: Querying user_profiles\n",
"rows = session.execute(\"SELECT * FROM user_profiles;\") ## we just print the 10 first ten"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "3c921d3b-2987-4c2d-90ec-df1a6853657a",
"metadata": {
"id": "3c921d3b-2987-4c2d-90ec-df1a6853657a",
"outputId": "e39e8e81-8144-4520-ea6a-f21d12c94b4f"
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"1000\n"
]
}
],
"source": [
"print(len(list(rows)))"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "e1cc5f69-3b30-436c-9efb-d136b5c637fe",
"metadata": {
"id": "e1cc5f69-3b30-436c-9efb-d136b5c637fe"
},
"outputs": [],
"source": [
"# Example: Querying user_profiles ; the ResultSet is not in memory so it will not run if we do not call the query again\n",
"rows = session.execute(\"SELECT * FROM user_profiles;\") ## we just print the 10 first ten"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "10667a65-071a-4e10-9e76-897704461af0",
"metadata": {
"id": "10667a65-071a-4e10-9e76-897704461af0",
"outputId": "bbcadfb0-9a95-4361-8ec8-c6bf19fa58a7"
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Row(user_id=UUID('866ffe42-6287-4dd3-8f3a-86c2b9dc381a'), age=50, email='baileyapril@example.com', name='Scott Franco')\n",
"Row(user_id=UUID('c564fb6c-4999-464e-9ae6-21e28663acd0'), age=67, email='jonathandavis@example.net', name='Mr. Eric Lewis Jr.')\n",
"Row(user_id=UUID('d830f41f-1b02-4f5d-b163-7784fa1ffb24'), age=41, email='donnaochoa@example.com', name='Billy Evans')\n",
"Row(user_id=UUID('81126ab4-842e-4d38-9a26-10c1f9237dcd'), age=21, email='mendezcassandra@example.org', name='Jonathan Cervantes')\n",
"Row(user_id=UUID('fdc0fa0b-6df8-4823-a2ea-b72235296ae0'), age=42, email='theresacooper@example.org', name='Angela Wright')\n",
"Row(user_id=UUID('082fe019-1dd7-4bea-9d6b-2e15d581e7f5'), age=44, email='alexisturner@example.net', name='Kelly Baker')\n",
"Row(user_id=UUID('1cd1ed49-3375-4e8a-bf33-46b0a4e1c73e'), age=49, email='smithbrittany@example.com', name='Gail Griffin')\n",
"Row(user_id=UUID('7c3c1e81-044c-47f3-a232-14caf878faba'), age=62, email='smithnicholas@example.com', name='Debbie Hughes')\n",
"Row(user_id=UUID('a2d0fa38-790c-4128-87e8-771c5cd82e1f'), age=76, email='vincentfreeman@example.com', name='Zachary Robinson')\n",
"Row(user_id=UUID('0451be3c-76be-43a9-aa73-ad883f99d68e'), age=48, email='sherrigray@example.net', name='Thomas Mitchell')\n"
]
}
],
"source": [
"for row in rows[:10]: # only print first ten\n",
" print(row)"
]
},
{
"cell_type": "markdown",
"id": "b00ef6b3-c301-42fc-8e69-18105180368d",
"metadata": {
"id": "b00ef6b3-c301-42fc-8e69-18105180368d"
},
"source": [
"## Select a user's profile by ID:"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "10366a85-174e-4d30-b741-98a80bd9c924",
"metadata": {
"id": "10366a85-174e-4d30-b741-98a80bd9c924",
"outputId": "ae72950a-5f13-4040-dbd3-7a2531cb2bd0"
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Row(user_id=UUID('866ffe42-6287-4dd3-8f3a-86c2b9dc381a'), age=50, email='baileyapril@example.com', name='Scott Franco')\n"
]
}
],
"source": [
"# Example: Querying user_profiles\n",
"\n",
"\n",
"rows = session.execute(\"SELECT * FROM user_profiles WHERE user_id = 866ffe42-6287-4dd3-8f3a-86c2b9dc381a;\")\n",
"for row in rows:\n",
" print(row)"
]
},
{
"cell_type": "markdown",
"id": "420ea80a-5d00-47f3-950f-52cfc8360c9c",
"metadata": {
"id": "420ea80a-5d00-47f3-950f-52cfc8360c9c"
},
"source": [
"## when there is a possibility of a lot of data being filtered, cassandra has unpredictable performance if the database is not indexed"
]
},
{
"cell_type": "markdown",
"id": "f9241cd7-d14e-4f05-8a83-79e72cccd8a7",
"metadata": {
"id": "f9241cd7-d14e-4f05-8a83-79e72cccd8a7"
},
"source": [
"## Select activities for a specific user: will give error"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "432ca8e0-2e3f-4cf6-a236-0da637a260b1",
"metadata": {
"id": "432ca8e0-2e3f-4cf6-a236-0da637a260b1",
"outputId": "01bc5738-c59b-44fe-c08a-1e99f48e918a"
},
"outputs": [
{
"ename": "InvalidRequest",
"evalue": "Error from server: code=2200 [Invalid query] message=\"Cannot execute this query as it might involve data filtering and thus may have unpredictable performance. If you want to execute this query despite the performance unpredictability, use ALLOW FILTERING\"",
"output_type": "error",
"traceback": [
"\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
"\u001b[0;31mInvalidRequest\u001b[0m Traceback (most recent call last)",
"Cell \u001b[0;32mIn[51], line 2\u001b[0m\n\u001b[1;32m 1\u001b[0m \u001b[38;5;66;03m# Example: Querying user_profiles\u001b[39;00m\n\u001b[0;32m----> 2\u001b[0m rows \u001b[38;5;241m=\u001b[39m \u001b[43msession\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mexecute\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[38;5;124;43mSELECT * FROM user_activities WHERE user_id = 866ffe42-6287-4dd3-8f3a-86c2b9dc381a;\u001b[39;49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[43m)\u001b[49m\n\u001b[1;32m 3\u001b[0m \u001b[38;5;28;01mfor\u001b[39;00m row \u001b[38;5;129;01min\u001b[39;00m rows:\n\u001b[1;32m 4\u001b[0m \u001b[38;5;28mprint\u001b[39m(row)\n",
"File \u001b[0;32m/opt/conda/lib/python3.11/site-packages/cassandra/cluster.py:2677\u001b[0m, in \u001b[0;36mcassandra.cluster.Session.execute\u001b[0;34m()\u001b[0m\n",
"File \u001b[0;32m/opt/conda/lib/python3.11/site-packages/cassandra/cluster.py:4956\u001b[0m, in \u001b[0;36mcassandra.cluster.ResponseFuture.result\u001b[0;34m()\u001b[0m\n",
"\u001b[0;31mInvalidRequest\u001b[0m: Error from server: code=2200 [Invalid query] message=\"Cannot execute this query as it might involve data filtering and thus may have unpredictable performance. If you want to execute this query despite the performance unpredictability, use ALLOW FILTERING\""
]
}
],
"source": [
"# Example: Querying user_profiles\n",
"rows = session.execute(\"SELECT * FROM user_activities WHERE user_id = 866ffe42-6287-4dd3-8f3a-86c2b9dc381a;\")\n",
"for row in rows:\n",
" print(row)"
]
},
{
"cell_type": "markdown",
"id": "61547ead-d3dd-43ec-8be0-36f3cf60eb36",
"metadata": {
"id": "61547ead-d3dd-43ec-8be0-36f3cf60eb36"
},
"source": [
"### Cassandra restricts certain types of queries that do not directly use the primary key, particularly when you try to perform operations that scan across multiple partitions. This restriction is in place to prevent inefficient full-table scans that can degrade performance, especially in large datasets."
]
},
{
"cell_type": "markdown",
"id": "8668a6a0-4ee2-4615-9c59-5d870259ffc2",
"metadata": {
"id": "8668a6a0-4ee2-4615-9c59-5d870259ffc2"
},
"source": [
"## alternatives\n",
"### Adjust Data Model: model tables based on your query patterns.\n",
"### Secondary Indexes: For filtering on columns that are not part of the primary key and when data model adjustments are not feasible or enough, you might consider using secondary indexes."
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "270465d5-693a-44a9-8f56-a55a5ce48d5f",
"metadata": {
"id": "270465d5-693a-44a9-8f56-a55a5ce48d5f"
},
"outputs": [],
"source": [
"### create secondry index\n",
"session.execute(\"CREATE INDEX ON user_activities(user_id);\")"
]
},
{
"cell_type": "markdown",
"id": "a34d970e-9c7c-4343-99bc-ddc1d3dfdc42",
"metadata": {
"id": "a34d970e-9c7c-4343-99bc-ddc1d3dfdc42"
},
"source": [
"## lets verify the if the index exists"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "af133ff5-3372-467b-9350-ff866c38fe13",
"metadata": {
"id": "af133ff5-3372-467b-9350-ff866c38fe13"
},
"outputs": [],
"source": [
"res = session.execute(\"DESCRIBE TABLE user_data.user_activities;\")\n",
"for row in res:\n",
" print(row)"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "490ef32b-947d-4945-9c25-5e748ff52aae",
"metadata": {
"id": "490ef32b-947d-4945-9c25-5e748ff52aae",
"outputId": "f8f71b9c-7202-42bb-fdde-d3f3dca486cf"
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Row(activity_id=UUID('f0cc1144-5255-4be7-908e-fcda53bd33cc'), activity_type='browse', details='Off news management look official.', timestamp=datetime.datetime(2024, 2, 21, 0, 21, 48, 244000), user_id=UUID('866ffe42-6287-4dd3-8f3a-86c2b9dc381a'))\n",
"Row(activity_id=UUID('a117ffc5-a497-4e51-aa57-5b433cd6b472'), activity_type='browse', details='Long happy rate both carry consider on.', timestamp=datetime.datetime(2024, 2, 2, 16, 2, 25, 398000), user_id=UUID('866ffe42-6287-4dd3-8f3a-86c2b9dc381a'))\n",
"Row(activity_id=UUID('e50632b7-e9ae-4aa0-b039-46580011fc0f'), activity_type='browse', details='She rise development half pay teach tonight.', timestamp=datetime.datetime(2024, 2, 7, 21, 34, 55, 699000), user_id=UUID('866ffe42-6287-4dd3-8f3a-86c2b9dc381a'))\n",
"Row(activity_id=UUID('fcb3c11a-7132-418e-a47d-50d7798b1f9e'), activity_type='purchase', details='Degree themselves set improve.', timestamp=datetime.datetime(2024, 2, 21, 0, 20, 33, 401000), user_id=UUID('866ffe42-6287-4dd3-8f3a-86c2b9dc381a'))\n",
"Row(activity_id=UUID('1f51f067-5dab-4f31-a549-c56e1a82e11f'), activity_type='purchase', details='Worry move perform stay do surface maintain.', timestamp=datetime.datetime(2024, 2, 8, 3, 57, 46, 311000), user_id=UUID('866ffe42-6287-4dd3-8f3a-86c2b9dc381a'))\n",
"Row(activity_id=UUID('f7bd3c0f-a175-48d0-9b44-edc5704f1ae0'), activity_type='browse', details='Past visit western television.', timestamp=datetime.datetime(2024, 2, 27, 6, 7, 18, 28000), user_id=UUID('866ffe42-6287-4dd3-8f3a-86c2b9dc381a'))\n",
"Row(activity_id=UUID('f7f46e2e-d455-45af-ba6b-42caa6aaa102'), activity_type='login', details='Better relate through structure high break.', timestamp=datetime.datetime(2024, 1, 20, 19, 59, 6, 812000), user_id=UUID('866ffe42-6287-4dd3-8f3a-86c2b9dc381a'))\n",
"Row(activity_id=UUID('7f8c067b-0d98-4026-bc57-ac6e7e9ea6ce'), activity_type='logout', details='Population force rich fill end.', timestamp=datetime.datetime(2024, 1, 16, 16, 32, 58, 683000), user_id=UUID('866ffe42-6287-4dd3-8f3a-86c2b9dc381a'))\n",
"Row(activity_id=UUID('a8b8e00e-491f-4e33-be61-4542fe0af870'), activity_type='logout', details='Care property vote glass prevent practice as.', timestamp=datetime.datetime(2024, 1, 18, 1, 36, 53, 751000), user_id=UUID('866ffe42-6287-4dd3-8f3a-86c2b9dc381a'))\n",
"Row(activity_id=UUID('f1e04b87-49b1-40fd-93b2-2a09812ed0de'), activity_type='login', details='Choose first vote population action.', timestamp=datetime.datetime(2024, 1, 1, 5, 2, 54, 457000), user_id=UUID('866ffe42-6287-4dd3-8f3a-86c2b9dc381a'))\n",
"Row(activity_id=UUID('86807f29-d985-45a1-adc2-b6262a58acb6'), activity_type='login', details='Cold development mother.', timestamp=datetime.datetime(2024, 2, 5, 6, 41, 20, 380000), user_id=UUID('866ffe42-6287-4dd3-8f3a-86c2b9dc381a'))\n"
]
}
],
"source": [
"# Example: Querying user_profiles\n",
"rows = session.execute(\"SELECT * FROM user_activities WHERE user_id = 866ffe42-6287-4dd3-8f3a-86c2b9dc381a;\")\n",
"for row in rows:\n",
" print(row)"
]
},
{
"cell_type": "markdown",
"id": "10ff5648-ea80-498b-b692-4e561dca5a10",
"metadata": {
"id": "10ff5648-ea80-498b-b692-4e561dca5a10"
},
"source": [
"## now it ran without any error\n",
"### another alternative is the use of ALLOW FILTERING but it has performance implications"
]
},
{
"cell_type": "markdown",
"id": "d88666a3-3332-4441-9b2c-20ca65e51572",
"metadata": {
"id": "d88666a3-3332-4441-9b2c-20ca65e51572"
},
"source": [
"## Select users within a specific age range: command below should provide an error"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "ce88d263-7ae2-4b6d-a241-088d6c4b45b7",
"metadata": {
"id": "ce88d263-7ae2-4b6d-a241-088d6c4b45b7",
"outputId": "962d232c-10d5-4372-f910-4ec715a7f032"
},
"outputs": [
{
"ename": "InvalidRequest",
"evalue": "Error from server: code=2200 [Invalid query] message=\"Cannot execute this query as it might involve data filtering and thus may have unpredictable performance. If you want to execute this query despite the performance unpredictability, use ALLOW FILTERING\"",
"output_type": "error",
"traceback": [
"\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
"\u001b[0;31mInvalidRequest\u001b[0m Traceback (most recent call last)",
"Cell \u001b[0;32mIn[59], line 2\u001b[0m\n\u001b[1;32m 1\u001b[0m \u001b[38;5;66;03m# Example: Querying user_profiles\u001b[39;00m\n\u001b[0;32m----> 2\u001b[0m rows \u001b[38;5;241m=\u001b[39m \u001b[43msession\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mexecute\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[38;5;124;43mSELECT * FROM user_profiles WHERE age >= 20 AND age <= 30;\u001b[39;49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[43m)\u001b[49m\n\u001b[1;32m 3\u001b[0m \u001b[38;5;28;01mfor\u001b[39;00m row \u001b[38;5;129;01min\u001b[39;00m rows:\n\u001b[1;32m 4\u001b[0m \u001b[38;5;28mprint\u001b[39m(row)\n",
"File \u001b[0;32m/opt/conda/lib/python3.11/site-packages/cassandra/cluster.py:2677\u001b[0m, in \u001b[0;36mcassandra.cluster.Session.execute\u001b[0;34m()\u001b[0m\n",
"File \u001b[0;32m/opt/conda/lib/python3.11/site-packages/cassandra/cluster.py:4956\u001b[0m, in \u001b[0;36mcassandra.cluster.ResponseFuture.result\u001b[0;34m()\u001b[0m\n",
"\u001b[0;31mInvalidRequest\u001b[0m: Error from server: code=2200 [Invalid query] message=\"Cannot execute this query as it might involve data filtering and thus may have unpredictable performance. If you want to execute this query despite the performance unpredictability, use ALLOW FILTERING\""
]
}
],
"source": [
"# Example: Querying user_profiles\n",
"rows = session.execute(\"SELECT * FROM user_profiles WHERE age >= 20 AND age <= 30;\")\n",
"for row in rows:\n",
" print(row)"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "c67e82c9-2597-478c-b027-1ec9d85549bf",
"metadata": {
"id": "c67e82c9-2597-478c-b027-1ec9d85549bf",
"outputId": "5dc074c5-5e40-4ae9-a646-590085d1aa9f"
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Row(user_id=UUID('81126ab4-842e-4d38-9a26-10c1f9237dcd'), age=21, email='mendezcassandra@example.org', name='Jonathan Cervantes')\n",
"Row(user_id=UUID('81d4a39a-9dd8-4c69-bfbd-d4a07ec81bb0'), age=24, email='jason14@example.net', name='Zachary Morrison')\n",
"Row(user_id=UUID('196ceff4-25a5-4d67-87a8-a821f1f908ab'), age=22, email='wilsoncarrie@example.net', name='Ronald Tanner')\n",
"Row(user_id=UUID('81089536-bd93-4fed-a4b6-af0de8e35f6e'), age=21, email='michaelmontoya@example.net', name='Brian Bennett')\n",
"Row(user_id=UUID('bef1a6c5-ade2-4293-bc17-de55831a4ea6'), age=23, email='amy18@example.com', name='Danny Winters DDS')\n",
"Row(user_id=UUID('1fca6dca-a7a4-4a79-a871-ce9dcc7c913b'), age=23, email='james09@example.org', name='Faith Ramirez')\n",
"Row(user_id=UUID('9dfa56dd-97fc-47f1-9d7e-d06325e3b31c'), age=22, email='curtisadam@example.org', name='Mrs. Lisa Young')\n",
"Row(user_id=UUID('49e8cd80-6169-48b8-a16f-c3bd2328bbf6'), age=21, email='lfrench@example.com', name='Kelsey Bowers')\n",
"Row(user_id=UUID('12a161a8-883b-46d8-9bf3-f968b641da0f'), age=24, email='osmith@example.org', name='Edward Miller')\n",
"Row(user_id=UUID('3440e408-7547-4f88-9041-bce166178767'), age=24, email='kennethhancock@example.org', name='Joseph Hansen')\n",
"Row(user_id=UUID('8a8d23cf-ad69-478b-a99e-1e50cdc58e67'), age=21, email='wandaschmidt@example.net', name='David West')\n",
"Row(user_id=UUID('9d07363f-148a-4991-a7f8-7c9d489c7455'), age=24, email='johnny02@example.com', name='Rebecca Brown')\n",
"Row(user_id=UUID('7c76dba6-d395-4aa4-bf2b-d057622afafd'), age=23, email='istephens@example.net', name='Lee Rodriguez')\n",
"Row(user_id=UUID('e18ecc52-48cb-456b-9d60-9a8ddbb9d609'), age=21, email='winterslisa@example.com', name='Jordan Grimes')\n",
"Row(user_id=UUID('31c67594-5f48-4405-bdc1-ede2ba1cc12c'), age=22, email='stevejames@example.net', name='Kayla Hernandez')\n",
"Row(user_id=UUID('aba218ee-81ba-4095-8785-d7bdf9ac92f5'), age=22, email='nancy67@example.com', name='Dylan Gaines')\n",
"Row(user_id=UUID('317ec18e-37a5-445b-aa41-611179b5412a'), age=21, email='adamnorris@example.org', name='Michael Mitchell')\n",
"Row(user_id=UUID('0c6ce149-0b72-4e1b-accf-dab7e28693a1'), age=22, email='vanessa10@example.org', name='Colin Turner')\n",
"Row(user_id=UUID('1c52eaab-1b57-4437-9003-841938b0a4fd'), age=21, email='james66@example.com', name='Jennifer Schmidt')\n",
"Row(user_id=UUID('0e6ce938-bde2-4bf2-acd8-209b21595d4d'), age=22, email='yfernandez@example.org', name='Sheena Long')\n",
"Row(user_id=UUID('6e68472a-8286-493b-9f5f-2fcfa08f8131'), age=22, email='emorton@example.org', name='Abigail Williams')\n",
"Row(user_id=UUID('418ccbef-0ccf-4ed7-aa4d-f1c69bb1059d'), age=22, email='rosehelen@example.com', name='Mark Harris')\n",
"Row(user_id=UUID('8d4476a8-d4be-419d-ae7a-7b0d0fc99e15'), age=22, email='eadams@example.com', name='Mrs. Julia Mcdonald')\n",
"Row(user_id=UUID('70a28a18-b493-4ac8-b6b2-123d27f132f2'), age=21, email='hsnyder@example.net', name='Anna Franco')\n",
"Row(user_id=UUID('d6de9b06-f51e-4928-95d6-e577f15fdad4'), age=22, email='wrightcarla@example.org', name='Amanda Ortiz')\n",
"Row(user_id=UUID('5848207c-9feb-4e62-807c-4b8f2552b468'), age=24, email='anthonycook@example.com', name='Ashley Torres')\n",
"Row(user_id=UUID('55e4e84a-5f4d-4ccd-98af-e069cdfd8e3d'), age=22, email='lmiller@example.org', name='Edward Nguyen')\n",
"Row(user_id=UUID('b25136e7-0834-40e2-b7ab-522201603e68'), age=23, email='rasmussentiffany@example.net', name='Maxwell Ponce')\n",
"Row(user_id=UUID('09a30ac5-4830-473a-8ce6-725dc72058d1'), age=23, email='spage@example.org', name='Raymond Ryan')\n",
"Row(user_id=UUID('e1b6c38e-bb7c-43b1-a594-4981a6801173'), age=22, email='vsmith@example.net', name='Barbara Brown')\n",
"Row(user_id=UUID('1030cb41-9c14-4273-8d28-0613e234971d'), age=23, email='nicole60@example.net', name='Scott Cooke')\n",
"Row(user_id=UUID('de2f5121-8ba2-406e-8030-a85f1bdecc28'), age=22, email='ryanbowers@example.com', name='Melissa Smith')\n",
"Row(user_id=UUID('5f9007a5-e300-4a51-bb63-3b712042853d'), age=24, email='sarahcalhoun@example.org', name='Julie Carlson')\n",
"Row(user_id=UUID('f6f3cf58-014d-48fd-a69f-799976b3da63'), age=22, email='rushtiffany@example.com', name='Mr. Ricardo Ramos DVM')\n",
"Row(user_id=UUID('ab28ad91-414c-45df-8f16-3c836b8237bb'), age=21, email='williamsmichelle@example.net', name='Kelli Miller')\n",
"Row(user_id=UUID('b032f27f-999d-42b8-b3e5-22de2cd6555b'), age=23, email='michaeltran@example.com', name='Anthony Peterson')\n",
"Row(user_id=UUID('c34e1e61-f46b-4d6c-8c52-c9d472387336'), age=22, email='laurenmartin@example.com', name='Christopher Johnson')\n",
"Row(user_id=UUID('f204cdc0-5201-48ca-8288-fec74cc00654'), age=21, email='tristan21@example.com', name='Daniel Cunningham MD')\n",
"Row(user_id=UUID('b0cf1289-811d-4357-8465-00e944f1aa34'), age=22, email='zacharytaylor@example.org', name='Ashley Becker')\n",
"Row(user_id=UUID('ff7569ec-4dde-4706-b75d-d2498b74e7d9'), age=23, email='gilbertalexandra@example.net', name='Mary Johnson')\n",
"Row(user_id=UUID('45b0dc70-29fe-4609-b7dd-4025190edc5b'), age=21, email='jenniferlopez@example.com', name='Jacob Snyder')\n",
"Row(user_id=UUID('49305710-25ee-41d7-b54d-c586424aed3d'), age=21, email='gabriella50@example.net', name='Miranda Dawson')\n",
"Row(user_id=UUID('ffcd5840-a944-4f93-8755-b3189569c313'), age=21, email='anicholson@example.com', name='Molly Fisher')\n",
"Row(user_id=UUID('9ae10fcf-c348-4833-b4fe-882323bd6ad6'), age=23, email='andrewbeasley@example.com', name='Dana Barnes')\n",
"Row(user_id=UUID('910120cd-9fa1-40b7-90ca-9441adea25cc'), age=22, email='ubeasley@example.org', name='Jamie Chavez')\n",
"Row(user_id=UUID('f0756649-6c0e-4722-aa6d-81400d5dd94f'), age=21, email='shannon30@example.net', name='John Jones')\n",
"Row(user_id=UUID('6113c1b1-b662-4b00-a629-18bae47ce6bc'), age=21, email='wdickson@example.net', name='Amanda Ali')\n",
"Row(user_id=UUID('573cf35f-2dcf-4276-99c6-7cead834ba0b'), age=24, email='lewisdiane@example.net', name='Mr. Larry Johnson')\n",
"Row(user_id=UUID('e101ba5c-4046-4f58-9f34-38bf5dde764e'), age=21, email='karen42@example.org', name='Dalton Anderson')\n",
"Row(user_id=UUID('b723aaff-4576-4ead-95c9-769dc32f40cb'), age=23, email='briansmith@example.com', name='Peggy Murphy')\n",
"Row(user_id=UUID('3af5dfb7-9c92-4b14-8ddb-e077979201b2'), age=24, email='michaeljacobs@example.com', name='Zachary Martin')\n",
"Row(user_id=UUID('601caf61-abaa-469e-a741-449793ca4887'), age=24, email='samantharose@example.net', name='Glenn Calderon')\n",
"Row(user_id=UUID('fbd96a3f-6663-40e3-9985-9e30cd740f6b'), age=22, email='williamswilliam@example.net', name='Stacey Hinton')\n",
"Row(user_id=UUID('a84aad20-af40-4c6a-8971-2d04d8d3bb73'), age=23, email='scottjack@example.com', name='Alicia White')\n",
"Row(user_id=UUID('6faadd12-8e41-45c6-a736-4241bc6b4e53'), age=24, email='salinasjohn@example.org', name='Steven Romero')\n",
"Row(user_id=UUID('4c68c740-3de4-44ba-b20f-4592c2f2228f'), age=23, email='emilygrimes@example.org', name='Kristina Rowe')\n",
"Row(user_id=UUID('51a77560-3897-4cc9-b2d6-375fe79aa176'), age=24, email='jose28@example.org', name='Rick Fields')\n",
"Row(user_id=UUID('4edf8ca6-202e-4bf0-b0be-303d5db1dba3'), age=22, email='obryant@example.org', name='Richard Riley')\n",
"Row(user_id=UUID('03dfff1c-cd70-4676-be3e-28b2a89fc687'), age=24, email='maddoxjeffrey@example.net', name='Stephanie Lee')\n",
"Row(user_id=UUID('1b1d86b5-2a75-4209-b769-b4e4b682377f'), age=23, email='diazjohn@example.net', name='Heather Martinez')\n",
"Row(user_id=UUID('427a0c54-650a-43a0-866e-3e8187e2e921'), age=21, email='kaitlinwalker@example.org', name='Misty Hawkins')\n",
"Row(user_id=UUID('a09d1ff9-0cee-42c2-b547-a3338852dc99'), age=22, email='curtisnancy@example.net', name='Angela Hale')\n",
"Row(user_id=UUID('e3190f3e-4cc8-43b2-83e5-772903b43994'), age=24, email='thill@example.com', name='Mr. Todd Castillo')\n",
"Row(user_id=UUID('cf980953-fdfe-4a2e-88a4-64fc83b51088'), age=22, email='jamieadams@example.org', name='Mark Griffin')\n",
"Row(user_id=UUID('bb5dc514-8307-4ede-abe2-484837eabf90'), age=24, email='tami48@example.net', name='Ethan Roy')\n",
"Row(user_id=UUID('ee5832aa-c833-4f38-95d8-8f39ddafcb13'), age=22, email='nguyenpatricia@example.com', name='Judith Sanchez')\n",
"Row(user_id=UUID('c5c9b352-2b3a-434c-addf-aacbc5d321e4'), age=23, email='jennifer21@example.net', name='Linda Huffman')\n",
"Row(user_id=UUID('478402f0-7634-4536-bebf-5af5ef5c84f0'), age=21, email='johnsonjames@example.net', name='Jamie Camacho')\n"
]
}
],
"source": [
"# Example: Querying user_profiles\n",
"rows = session.execute(\"SELECT * FROM user_profiles WHERE age > 20 AND age < 25 ALLOW FILTERING;\")\n",
"for row in rows:\n",
" print(row)"
]
},
{
"cell_type": "markdown",
"id": "648d408c-53d1-446b-87fe-01b869ecd7bb",
"metadata": {
"id": "648d408c-53d1-446b-87fe-01b869ecd7bb"
},
"source": [
"## however ALLOW FILTERING; is not recommended"
]
},
{
"cell_type": "markdown",
"id": "01c902d3-8673-4084-96d7-5d86063ace05",
"metadata": {
"id": "01c902d3-8673-4084-96d7-5d86063ace05"
},
"source": [
"## Count the number of activities for a user:"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "c4c35de3-79d2-4fe5-8c9a-96197bbeae2b",
"metadata": {
"id": "c4c35de3-79d2-4fe5-8c9a-96197bbeae2b",
"outputId": "373f083a-7cef-41e6-9a4b-caec395201d4"
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Row(count=11)\n"
]
}
],
"source": [
"rows = session.execute(\"SELECT COUNT(*) FROM user_activities WHERE user_id = 866ffe42-6287-4dd3-8f3a-86c2b9dc381a;\")\n",
"for row in rows:\n",
" print(row)"
]
},
{
"cell_type": "markdown",
"id": "3823c974-031d-4c61-b585-6f7bbb5321cb",
"metadata": {
"id": "3823c974-031d-4c61-b585-6f7bbb5321cb"
},
"source": [
"## string matching"
]
},
{
"cell_type": "markdown",
"id": "9e83c963-82b8-4732-95b5-5efa96d71456",
"metadata": {
"id": "9e83c963-82b8-4732-95b5-5efa96d71456"
},
"source": [
"## Cassandra does not natively support SQL-like LIKE operations for pattern matching without full table scans, which can be highly inefficient and are not recommended for large datasets."
]
},
{
"cell_type": "markdown",
"id": "12d4fa75-8733-47d7-976d-9e105d7ccd97",
"metadata": {
"id": "12d4fa75-8733-47d7-976d-9e105d7ccd97"
},
"source": [
"## so for ex: you want to know the all the users whose name starts with A, so we can not directly do it, we need to make another table and then use it"
]
},
{
"cell_type": "markdown",
"id": "ab264eca-35fc-43d7-924b-0b5d317aa5c4",
"metadata": {
"id": "ab264eca-35fc-43d7-924b-0b5d317aa5c4"
},
"source": [
"## which suggets that, the keyspace / table schema is governed by the queries, and should be considered while designing"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "2be0c662-e8e1-4bf0-810f-cc9cd3fec7ac",
"metadata": {
"id": "2be0c662-e8e1-4bf0-810f-cc9cd3fec7ac"
},
"outputs": [],
"source": [
"# Assuming the session is already connected to your 'user_data' keyspace\n",
"\n",
"rows = session.execute(\"SELECT user_id, name, email FROM user_profiles\")\n"
]
},
{
"cell_type": "markdown",
"id": "2c8332f2-8877-42f7-b0b5-656f6725afbc",
"metadata": {
"id": "2c8332f2-8877-42f7-b0b5-656f6725afbc"
},
"source": [
"### lets make a new table"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "e5cc3d42-1f3e-4683-a02c-166f8871c8e1",
"metadata": {
"id": "e5cc3d42-1f3e-4683-a02c-166f8871c8e1",
"outputId": "de11c602-703c-4409-ca52-e1baa7cf4be7"
},
"outputs": [
{
"data": {
"text/plain": [
""
]
},
"execution_count": 91,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"session.execute(\"CREATE TABLE IF NOT EXISTS user_profiles_by_initial (name_initial TEXT,user_id UUID, name TEXT, email TEXT, PRIMARY KEY (name_initial, user_id));\")"
]
},
{
"cell_type": "markdown",
"id": "c7f97537-3be4-4e4b-a32e-2fddd11f1f86",
"metadata": {
"id": "c7f97537-3be4-4e4b-a32e-2fddd11f1f86"
},
"source": [
"## add some data to new table"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "6c7c01b8-a25e-49f8-88b7-f042102ce81b",
"metadata": {
"id": "6c7c01b8-a25e-49f8-88b7-f042102ce81b"
},
"outputs": [],
"source": [
"for row in rows:\n",
" name_initial = row.name[0].upper() # Extract the first letter and make it uppercase\n",
" # Insert into the new table\n",
" session.execute(\n",
" \"\"\"\n",
" INSERT INTO user_profiles_by_initial (name_initial, user_id, name, email) VALUES (%s, %s, %s, %s)\n",
" \"\"\",\n",
" (name_initial, row.user_id, row.name, row.email)\n",
" )\n"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "d04bc11f-866b-417a-9b4d-50f969f9357a",
"metadata": {
"id": "d04bc11f-866b-417a-9b4d-50f969f9357a",
"outputId": "c5990cb2-9676-4e11-8579-8984e14c990b"
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Row(name_initial='A', user_id=UUID('0090b257-a978-4a24-af17-77c7aa836c6c'), email='john63@example.com', name='Alexander Thompson')\n",
"Row(name_initial='A', user_id=UUID('00f36699-af3a-4b1a-8440-4ab084c7f70d'), email='teresa79@example.com', name='Amanda Cobb')\n",
"Row(name_initial='A', user_id=UUID('0545c129-092e-4d0b-b1b7-9d8dcfc07b44'), email='whitneywright@example.com', name='April Powell')\n",
"Row(name_initial='A', user_id=UUID('05906baf-064b-4681-a72a-42572606337f'), email='jacksonlori@example.net', name='Andrea Gibson')\n",
"Row(name_initial='A', user_id=UUID('0d44b002-8a3a-409b-890b-06c8bd8942c4'), email='markhill@example.com', name='Albert Brown')\n",
"Row(name_initial='A', user_id=UUID('0e9f8c03-16e7-43e4-8966-40e70e3b5726'), email='ccooper@example.net', name='Andrew Stafford')\n",
"Row(name_initial='A', user_id=UUID('1014f235-38b4-4834-838c-cd5a68b765dc'), email='doughertyanthony@example.com', name='Amber Casey')\n",
"Row(name_initial='A', user_id=UUID('1060e967-dfb8-4485-be34-167484cc7abb'), email='jeffwilliams@example.com', name='Alyssa Howe')\n",
"Row(name_initial='A', user_id=UUID('15a9c58b-1943-4aa6-8a36-dc70544bca1a'), email='yblake@example.com', name='Alyssa Hayden MD')\n",
"Row(name_initial='A', user_id=UUID('1998a2bb-e928-4844-9b79-08e0775a6536'), email='nwallace@example.com', name='Austin Lopez')\n",
"Row(name_initial='A', user_id=UUID('1f6d2aaf-ca25-4e4d-8b73-521c7e123ed2'), email='deannatodd@example.org', name='Anthony Erickson')\n",
"Row(name_initial='A', user_id=UUID('207bf574-0d82-4ed1-957a-04986938325e'), email='yfletcher@example.com', name='Alexander Weber')\n",
"Row(name_initial='A', user_id=UUID('22c3493b-1630-4281-82f4-402319b694b5'), email='michael76@example.net', name='Angela Williams')\n",
"Row(name_initial='A', user_id=UUID('256ceb83-b16c-43fb-8547-03a604473235'), email='omartinez@example.com', name='Anna Saunders')\n",
"Row(name_initial='A', user_id=UUID('2c4cfea1-e113-4193-a638-c04d9fbb93e7'), email='torresjulie@example.com', name='Amanda Ramirez')\n",
"Row(name_initial='A', user_id=UUID('2edf4b93-2a1f-4375-850f-7432efef41ab'), email='jenniferrichmond@example.com', name='Ashley Glover')\n",
"Row(name_initial='A', user_id=UUID('3052f1d2-f9a3-446a-8746-e5e38392ee89'), email='danielle77@example.net', name='Ashley Smith')\n",
"Row(name_initial='A', user_id=UUID('318bb663-05f0-4447-8d60-e01b7c8c55fa'), email='markturner@example.net', name='Ashley Thomas')\n",
"Row(name_initial='A', user_id=UUID('348baa99-dfc7-4d7d-b08b-d54b11c3a00d'), email='leslie48@example.net', name='Alexandra Lee')\n",
"Row(name_initial='A', user_id=UUID('498ff83c-c7b8-4ac1-96ad-c5171fa480e3'), email='jasonhorton@example.org', name='Angel Hurst')\n",
"Row(name_initial='A', user_id=UUID('4a21241d-f74a-4f84-aec0-4f328c8c1631'), email='floresbenjamin@example.com', name='Andrew West')\n",
"Row(name_initial='A', user_id=UUID('4a532c8a-7373-4d98-b9f9-d8da81348926'), email='lhardy@example.org', name='Allen Summers')\n",
"Row(name_initial='A', user_id=UUID('4b102d23-1b52-4ef7-9187-f39de8a807ad'), email='dillonpatricia@example.net', name='Ashlee Archer')\n",
"Row(name_initial='A', user_id=UUID('4b594bd8-3bef-4098-aa91-e9e5d682e778'), email='kmason@example.org', name='Andrew Curry')\n",
"Row(name_initial='A', user_id=UUID('4e2e6304-3ec6-47a2-9f3d-bef95f4c3889'), email='kimberly24@example.org', name='Aaron Williams')\n",
"Row(name_initial='A', user_id=UUID('5316a32e-6303-4a2e-9780-7cd12e7e60d7'), email='carla94@example.org', name='Adam Burke')\n",
"Row(name_initial='A', user_id=UUID('5746cc7e-50e7-4f7d-ab06-a1ae69b9871b'), email='espinozaolivia@example.org', name='Anthony Smith')\n",
"Row(name_initial='A', user_id=UUID('5848207c-9feb-4e62-807c-4b8f2552b468'), email='anthonycook@example.com', name='Ashley Torres')\n",
"Row(name_initial='A', user_id=UUID('5b39ef42-7038-4995-bb44-eb902f3993b2'), email='hmontgomery@example.com', name='Austin Sullivan')\n",
"Row(name_initial='A', user_id=UUID('6113c1b1-b662-4b00-a629-18bae47ce6bc'), email='wdickson@example.net', name='Amanda Ali')\n",
"Row(name_initial='A', user_id=UUID('61b22ed0-dd8d-4a95-842a-7cdd4a50faaf'), email='strongstephen@example.net', name='Amanda Hodge')\n",
"Row(name_initial='A', user_id=UUID('623c1c6c-00f3-4e5c-8c3f-0cbd73823824'), email='ricerichard@example.org', name='Alexandra Beasley')\n",
"Row(name_initial='A', user_id=UUID('63b30a11-4d80-461b-a119-46362fd4dc35'), email='warenicholas@example.net', name='Amanda Cook')\n",
"Row(name_initial='A', user_id=UUID('6a668ee8-3072-4202-afa8-3e82ffa94319'), email='michael03@example.org', name='Amanda Mendoza')\n",
"Row(name_initial='A', user_id=UUID('6c0c7706-b890-4c7b-8658-b28b093f71f8'), email='alexis60@example.net', name='Ashley Phelps')\n",
"Row(name_initial='A', user_id=UUID('6d508069-7a04-48af-ba1d-df22a58c661d'), email='clarkmichael@example.org', name='Amanda Guerrero')\n",
"Row(name_initial='A', user_id=UUID('6e68472a-8286-493b-9f5f-2fcfa08f8131'), email='emorton@example.org', name='Abigail Williams')\n",
"Row(name_initial='A', user_id=UUID('6f2447de-e6a4-4147-b8cc-98eee9badffd'), email='lisa27@example.com', name='Adam Baker')\n",
"Row(name_initial='A', user_id=UUID('6f371430-ef11-4c62-bd97-3db402b55838'), email='edwardwarren@example.net', name='Amanda Reed')\n",
"Row(name_initial='A', user_id=UUID('70a28a18-b493-4ac8-b6b2-123d27f132f2'), email='hsnyder@example.net', name='Anna Franco')\n",
"Row(name_initial='A', user_id=UUID('753acfeb-4d0f-406e-9c35-f2dd1bcefd0e'), email='brownjacob@example.org', name='Ashley Henderson')\n",
"Row(name_initial='A', user_id=UUID('768336be-3aa7-4b73-955d-7957a3d7e797'), email='garciaarthur@example.net', name='Amy Alexander')\n",
"Row(name_initial='A', user_id=UUID('78b002bb-4603-4706-b326-3a5c8c8c19a8'), email='matthewthompson@example.net', name='Amy Decker')\n",
"Row(name_initial='A', user_id=UUID('7fb80938-db2a-4499-b793-d0ea02897980'), email='wadams@example.com', name='Alexis Bates')\n",
"Row(name_initial='A', user_id=UUID('87deb8b8-59fd-480d-92b4-ad5b008a0a2f'), email='erica95@example.net', name='Anita Harrington')\n",
"Row(name_initial='A', user_id=UUID('8ad4efda-c3b0-4607-adb3-c18a58038457'), email='faguirre@example.net', name='Alexander Smith')\n",
"Row(name_initial='A', user_id=UUID('8adf1d8c-22ef-40d8-bf15-18b45f61588b'), email='patricia58@example.org', name='Ana Gordon')\n",
"Row(name_initial='A', user_id=UUID('8b9f55e9-5f50-49a9-8ad7-ab392ce44332'), email='leah31@example.org', name='Amy Campbell')\n",
"Row(name_initial='A', user_id=UUID('8f633b4f-f624-4c32-915b-f8abcb45cc09'), email='bhall@example.com', name='Ariel Robinson')\n",
"Row(name_initial='A', user_id=UUID('9912ad00-c90c-4af1-b949-4ae9cce87c4f'), email='lori52@example.org', name='Ashley Clark')\n",
"Row(name_initial='A', user_id=UUID('9efb27c0-19a5-4dc0-8dea-83518222e103'), email='osbornestephanie@example.net', name='Anthony Davis')\n",
"Row(name_initial='A', user_id=UUID('9f700d40-5b9c-4af4-9ca4-b33d5f963517'), email='qstout@example.org', name='Amy Patterson')\n",
"Row(name_initial='A', user_id=UUID('a09d1ff9-0cee-42c2-b547-a3338852dc99'), email='curtisnancy@example.net', name='Angela Hale')\n",
"Row(name_initial='A', user_id=UUID('a0fb77ef-db49-446e-b4f6-9241f9d116b0'), email='amy41@example.org', name='Alexander Herrera')\n",
"Row(name_initial='A', user_id=UUID('a3a2d1aa-69bd-43db-a80e-5b5dc17c0cb4'), email='joneskathryn@example.org', name='Angela Velasquez')\n",
"Row(name_initial='A', user_id=UUID('a4773ba8-6f2e-421a-8e5c-514ea20d517e'), email='josephjennings@example.com', name='Amy Young')\n",
"Row(name_initial='A', user_id=UUID('a84aad20-af40-4c6a-8971-2d04d8d3bb73'), email='scottjack@example.com', name='Alicia White')\n",
"Row(name_initial='A', user_id=UUID('ac8aa3e5-d721-44f2-bc55-8a8e338c0ffe'), email='abrown@example.com', name='Adrienne Hayes')\n",
"Row(name_initial='A', user_id=UUID('acfe8019-b82d-42b6-a69d-be23ccde1db4'), email='lauren08@example.com', name='Anthony Wong')\n",
"Row(name_initial='A', user_id=UUID('adab8084-5a49-4932-83d7-d3ad45744830'), email='whickman@example.org', name='Amanda Ramsey')\n",
"Row(name_initial='A', user_id=UUID('b0221be7-e57a-4f10-8252-649842340fda'), email='jcurry@example.com', name='Arthur Pena')\n",
"Row(name_initial='A', user_id=UUID('b032f27f-999d-42b8-b3e5-22de2cd6555b'), email='michaeltran@example.com', name='Anthony Peterson')\n",
"Row(name_initial='A', user_id=UUID('b061c064-a15f-4703-9a6d-3e82a120da8d'), email='prussell@example.com', name='Alyssa Haley')\n",
"Row(name_initial='A', user_id=UUID('b0cf1289-811d-4357-8465-00e944f1aa34'), email='zacharytaylor@example.org', name='Ashley Becker')\n",
"Row(name_initial='A', user_id=UUID('b589b47f-6e7f-48e7-adcb-2a782a2516a6'), email='wileybrian@example.org', name='Alyssa Bell')\n",
"Row(name_initial='A', user_id=UUID('b62140cd-728f-4d0a-b9e4-f72b8eb165f0'), email='taylorbrown@example.org', name='Andrew Jones')\n",
"Row(name_initial='A', user_id=UUID('bae23dec-b325-46e9-9972-67dd1dfbcb88'), email='jasontorres@example.com', name='Alexis Martin')\n",
"Row(name_initial='A', user_id=UUID('bcc1fba0-c7c5-48aa-831a-cff91f541a5b'), email='lopezkendra@example.com', name='Aaron Savage')\n",
"Row(name_initial='A', user_id=UUID('bd7db7db-fc13-46ec-a198-db750471ee50'), email='alicia17@example.com', name='Alexander Brewer')\n",
"Row(name_initial='A', user_id=UUID('bf0266d1-077f-4c36-9361-c3324954ea69'), email='gmendez@example.org', name='Alejandra Keller')\n",
"Row(name_initial='A', user_id=UUID('c23372b4-0655-4517-9393-a796fbf488e8'), email='snydertammy@example.com', name='Alisha Taylor')\n",
"Row(name_initial='A', user_id=UUID('c44b6035-2b01-4792-b215-b02266ab6293'), email='fwarren@example.org', name='Anna Scott')\n",
"Row(name_initial='A', user_id=UUID('c4c90f95-13ee-462c-aef3-6817b73b2373'), email='guzmannicholas@example.net', name='Aaron Forbes')\n",
"Row(name_initial='A', user_id=UUID('c77177fb-4192-4e32-93a4-a36ad487a077'), email='brandonhuang@example.org', name='Andrew Miller')\n",
"Row(name_initial='A', user_id=UUID('ca934d20-8db8-4aab-82a6-369d852849e7'), email='sandramercer@example.com', name='Alex Burnett')\n",
"Row(name_initial='A', user_id=UUID('cd13ba1f-c633-4cd2-bea1-3d11979880e9'), email='onichols@example.net', name='Amy Ballard')\n",
"Row(name_initial='A', user_id=UUID('cd2ec4e9-1772-4989-94bd-7b9105133399'), email='sstanley@example.net', name='Amy Allen')\n",
"Row(name_initial='A', user_id=UUID('d141a4dc-cfc3-4470-b859-e95a4bbd2f91'), email='davidcasey@example.net', name='Adam Gonzalez')\n",
"Row(name_initial='A', user_id=UUID('d2b65a4a-7021-41d4-9d64-17e1ba1c0849'), email='qhogan@example.org', name='Ann Bryant')\n",
"Row(name_initial='A', user_id=UUID('d384ae31-7005-43db-8f98-ac4547fe6af6'), email='reyessara@example.com', name='Andre Simpson')\n",
"Row(name_initial='A', user_id=UUID('d6797736-f937-4ea8-bb18-8b39aa0e19d1'), email='hannah22@example.net', name='Albert Smith')\n",
"Row(name_initial='A', user_id=UUID('d6de9b06-f51e-4928-95d6-e577f15fdad4'), email='wrightcarla@example.org', name='Amanda Ortiz')\n",
"Row(name_initial='A', user_id=UUID('d7d8b62d-8401-440d-a100-71e9e0e51119'), email='shannongreene@example.org', name='Ashley Davis')\n",
"Row(name_initial='A', user_id=UUID('dd98f9d6-2e00-47e0-8515-0f0f916383ef'), email='xrichardson@example.org', name='Amanda Cunningham')\n",
"Row(name_initial='A', user_id=UUID('e1d9d169-a591-4b4a-81fa-229ac10ce215'), email='sabrina73@example.org', name='April Johnson')\n",
"Row(name_initial='A', user_id=UUID('e3f75dec-22c4-4a41-bd1d-3827c7f0f891'), email='jenniferolson@example.com', name='Adam Jones')\n",
"Row(name_initial='A', user_id=UUID('e91bbea1-8e5c-41da-abed-a5cc3962cd0b'), email='charles25@example.org', name='Angela Goodwin')\n",
"Row(name_initial='A', user_id=UUID('eb9fb0f9-9d42-4bdd-877e-db0b044dddec'), email='robinsonjoshua@example.org', name='Albert Austin')\n",
"Row(name_initial='A', user_id=UUID('ed8bbcaf-cb08-4f52-b4f7-92ad59e40872'), email='olivia40@example.com', name='Adriana Hunt')\n",
"Row(name_initial='A', user_id=UUID('efe97f55-1afb-48c5-b5e4-b4161f13206e'), email='heatherwilliams@example.com', name='Alan Mathis')\n",
"Row(name_initial='A', user_id=UUID('f185e913-603b-41fc-8c4f-e9e85d767ee8'), email='jenniferrogers@example.net', name='Antonio Miller')\n",
"Row(name_initial='A', user_id=UUID('f28143ee-c15f-498e-b7d5-84c96f0cdd33'), email='hbutler@example.com', name='Andrea Brown')\n",
"Row(name_initial='A', user_id=UUID('f3bbcdda-bbbf-45cf-84fe-a4082458b297'), email='adamsgina@example.net', name='Adam Zamora')\n",
"Row(name_initial='A', user_id=UUID('f8668a34-51f0-4333-9c51-08a8ee4941bd'), email='andrewromero@example.org', name='Anne Matthews')\n",
"Row(name_initial='A', user_id=UUID('f8e1f65b-702b-4f22-bbf8-59ea0fae2bb7'), email='ldavila@example.net', name='Anna Ward')\n",
"Row(name_initial='A', user_id=UUID('fa51de2e-bc86-4137-a6a4-57c182ec70b9'), email='michaelschroeder@example.org', name='Angela Saunders')\n",
"Row(name_initial='A', user_id=UUID('fb0f617d-2562-4314-888e-d3c63e553f2a'), email='spope@example.org', name='Amanda Hall')\n",
"Row(name_initial='A', user_id=UUID('fdc0fa0b-6df8-4823-a2ea-b72235296ae0'), email='theresacooper@example.org', name='Angela Wright')\n"
]
}
],
"source": [
"rows = session.execute(\"SELECT * FROM user_profiles_by_initial WHERE name_initial = 'A';\")\n",
"for row in rows:\n",
" print(row)"
]
},
{
"cell_type": "markdown",
"id": "417cc0b3-78a6-4628-a0ee-8f152fc54c72",
"metadata": {
"id": "417cc0b3-78a6-4628-a0ee-8f152fc54c72"
},
"source": [
"## UPDATE operations"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "ab1f25cb-2d93-4e1f-93df-9d0419747732",
"metadata": {
"id": "ab1f25cb-2d93-4e1f-93df-9d0419747732",
"outputId": "308a05e1-271a-42d5-826c-96c45ce72ba0"
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Row(user_id=UUID('866ffe42-6287-4dd3-8f3a-86c2b9dc381a'), age=50, email='baileyapril@example.com', name='Scott Franco')\n"
]
}
],
"source": [
"rows = session.execute(\"SELECT * FROM user_profiles WHERE user_id = 866ffe42-6287-4dd3-8f3a-86c2b9dc381a;\")\n",
"for row in rows:\n",
" print(row)"
]
},
{
"cell_type": "markdown",
"id": "46701a63-0e14-4d2a-9765-540f37c9b1f9",
"metadata": {
"id": "46701a63-0e14-4d2a-9765-540f37c9b1f9"
},
"source": [
"## we would like to update the email of this person"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "6eee7060-7cfa-4acf-8e55-bd77423502cd",
"metadata": {
"id": "6eee7060-7cfa-4acf-8e55-bd77423502cd",
"outputId": "78c7ffd4-8d01-4ea5-9a05-91f01f33649a"
},
"outputs": [
{
"data": {
"text/plain": [
""
]
},
"execution_count": 87,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"session.execute(\"UPDATE user_profiles SET email = 'scottfranco@example.com' WHERE user_id = 866ffe42-6287-4dd3-8f3a-86c2b9dc381a;\")"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "f0ebc2ef-49e2-4b90-87d9-b99e680df592",
"metadata": {
"id": "f0ebc2ef-49e2-4b90-87d9-b99e680df592",
"outputId": "56a48675-9525-4d3f-907b-48894ce880e6"
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Row(user_id=UUID('866ffe42-6287-4dd3-8f3a-86c2b9dc381a'), age=50, email='scottfranco@example.com', name='Scott Franco')\n"
]
}
],
"source": [
"rows = session.execute(\"SELECT * FROM user_profiles WHERE user_id = 866ffe42-6287-4dd3-8f3a-86c2b9dc381a;\")\n",
"for row in rows:\n",
" print(row)"
]
},
{
"cell_type": "markdown",
"id": "31bc1b66-77ad-4d37-a9ae-e1baab9f4093",
"metadata": {
"id": "31bc1b66-77ad-4d37-a9ae-e1baab9f4093"
},
"source": [
"## Update user preferences:"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "134c6dc7-c8a4-4028-ac18-4b738566cc89",
"metadata": {
"id": "134c6dc7-c8a4-4028-ac18-4b738566cc89",
"outputId": "b959c437-5a1b-4c28-af22-be639cb4aadc"
},
"outputs": [
{
"data": {
"text/plain": [
""
]
},
"execution_count": 95,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"session.execute(\"UPDATE preference_data SET preferences = preferences + {'notifications': 'enabled'} WHERE user_id = 866ffe42-6287-4dd3-8f3a-86c2b9dc381a;\")"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "1917d19e-0d4b-4b6b-832f-efc03630b0aa",
"metadata": {
"id": "1917d19e-0d4b-4b6b-832f-efc03630b0aa",
"outputId": "f3a669b7-4a8f-41af-fdc8-33ecd32f29bd"
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Row(user_id=UUID('866ffe42-6287-4dd3-8f3a-86c2b9dc381a'), preferences=OrderedMapSerializedKey([('language', 'de'), ('notifications', 'enabled'), ('theme', 'dark')]))\n"
]
}
],
"source": [
"rows = session.execute(\"SELECT * FROM preference_data WHERE user_id = 866ffe42-6287-4dd3-8f3a-86c2b9dc381a;\")\n",
"for row in rows:\n",
" print(row)"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "b356247c-09e2-44a4-ab42-7eac4d2051a2",
"metadata": {
"id": "b356247c-09e2-44a4-ab42-7eac4d2051a2",
"outputId": "e4227af6-285e-4670-f402-c385cc432cea"
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Row(user_id=UUID('866ffe42-6287-4dd3-8f3a-86c2b9dc381a'), preferences=OrderedMapSerializedKey([('language', 'de'), ('notifications', 'enabled'), ('theme', 'dark')]))\n",
"Row(user_id=UUID('c564fb6c-4999-464e-9ae6-21e28663acd0'), preferences=OrderedMapSerializedKey([('language', 'fr'), ('theme', 'dark')]))\n",
"Row(user_id=UUID('d830f41f-1b02-4f5d-b163-7784fa1ffb24'), preferences=OrderedMapSerializedKey([('language', 'de'), ('theme', 'light')]))\n",
"Row(user_id=UUID('81126ab4-842e-4d38-9a26-10c1f9237dcd'), preferences=OrderedMapSerializedKey([('language', 'de'), ('theme', 'light')]))\n",
"Row(user_id=UUID('fdc0fa0b-6df8-4823-a2ea-b72235296ae0'), preferences=OrderedMapSerializedKey([('language', 'es'), ('theme', 'light')]))\n",
"Row(user_id=UUID('082fe019-1dd7-4bea-9d6b-2e15d581e7f5'), preferences=OrderedMapSerializedKey([('language', 'en'), ('theme', 'light')]))\n",
"Row(user_id=UUID('1cd1ed49-3375-4e8a-bf33-46b0a4e1c73e'), preferences=OrderedMapSerializedKey([('language', 'fr'), ('theme', 'dark')]))\n",
"Row(user_id=UUID('7c3c1e81-044c-47f3-a232-14caf878faba'), preferences=OrderedMapSerializedKey([('language', 'en'), ('theme', 'dark')]))\n",
"Row(user_id=UUID('a2d0fa38-790c-4128-87e8-771c5cd82e1f'), preferences=OrderedMapSerializedKey([('language', 'es'), ('theme', 'dark')]))\n",
"Row(user_id=UUID('0451be3c-76be-43a9-aa73-ad883f99d68e'), preferences=OrderedMapSerializedKey([('language', 'es'), ('theme', 'light')]))\n"
]
}
],
"source": [
"rows = session.execute(\"SELECT * FROM preference_data LIMIT 10\")\n",
"for row in rows:\n",
" print(row)"
]
},
{
"cell_type": "markdown",
"id": "1e6733fe-9e3f-4e2b-8adc-46902a2b60a5",
"metadata": {
"id": "1e6733fe-9e3f-4e2b-8adc-46902a2b60a5"
},
"source": [
"### the above illustrates no fixed schema"
]
},
{
"cell_type": "markdown",
"id": "181d036a-23b9-462a-8b7b-05fbce168729",
"metadata": {
"id": "181d036a-23b9-462a-8b7b-05fbce168729"
},
"source": [
"## Delete operations"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "08d89f48-6e8b-4679-9413-63e6941744fb",
"metadata": {
"id": "08d89f48-6e8b-4679-9413-63e6941744fb",
"outputId": "9dc5b557-52dd-40ea-a235-e2b7aa9d87e8"
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Row(user_id=UUID('866ffe42-6287-4dd3-8f3a-86c2b9dc381a'), age=50, email='scottfranco@example.com', name='Scott Franco')\n",
"Row(user_id=UUID('c564fb6c-4999-464e-9ae6-21e28663acd0'), age=67, email='jonathandavis@example.net', name='Mr. Eric Lewis Jr.')\n",
"Row(user_id=UUID('d830f41f-1b02-4f5d-b163-7784fa1ffb24'), age=41, email='donnaochoa@example.com', name='Billy Evans')\n",
"Row(user_id=UUID('81126ab4-842e-4d38-9a26-10c1f9237dcd'), age=21, email='mendezcassandra@example.org', name='Jonathan Cervantes')\n",
"Row(user_id=UUID('fdc0fa0b-6df8-4823-a2ea-b72235296ae0'), age=42, email='theresacooper@example.org', name='Angela Wright')\n",
"Row(user_id=UUID('082fe019-1dd7-4bea-9d6b-2e15d581e7f5'), age=44, email='alexisturner@example.net', name='Kelly Baker')\n",
"Row(user_id=UUID('1cd1ed49-3375-4e8a-bf33-46b0a4e1c73e'), age=49, email='smithbrittany@example.com', name='Gail Griffin')\n",
"Row(user_id=UUID('7c3c1e81-044c-47f3-a232-14caf878faba'), age=62, email='smithnicholas@example.com', name='Debbie Hughes')\n",
"Row(user_id=UUID('a2d0fa38-790c-4128-87e8-771c5cd82e1f'), age=76, email='vincentfreeman@example.com', name='Zachary Robinson')\n",
"Row(user_id=UUID('0451be3c-76be-43a9-aa73-ad883f99d68e'), age=48, email='sherrigray@example.net', name='Thomas Mitchell')\n"
]
}
],
"source": [
"rows = session.execute(\"SELECT * FROM user_profiles LIMIT 10\")\n",
"for row in rows:\n",
" print(row)"
]
},
{
"cell_type": "markdown",
"id": "a97a2972-2fea-4dcb-a8ba-529797e2c2a4",
"metadata": {
"id": "a97a2972-2fea-4dcb-a8ba-529797e2c2a4"
},
"source": [
"## Delete a user profile:"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "c1867a51-73e2-4e44-bf89-3edac1da76e5",
"metadata": {
"id": "c1867a51-73e2-4e44-bf89-3edac1da76e5",
"outputId": "1864a72a-6059-4048-f238-2dfd920b1db1"
},
"outputs": [
{
"data": {
"text/plain": [
""
]
},
"execution_count": 99,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"session.execute(\"DELETE FROM user_profiles WHERE user_id = d830f41f-1b02-4f5d-b163-7784fa1ffb24;\")"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "b27bf052-e1d9-4a42-858b-ec9b1decbf03",
"metadata": {
"id": "b27bf052-e1d9-4a42-858b-ec9b1decbf03",
"outputId": "f10dcf9c-1ee0-49aa-fbfb-a4d667e96402"
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Row(user_id=UUID('866ffe42-6287-4dd3-8f3a-86c2b9dc381a'), age=50, email='scottfranco@example.com', name='Scott Franco')\n",
"Row(user_id=UUID('c564fb6c-4999-464e-9ae6-21e28663acd0'), age=67, email='jonathandavis@example.net', name='Mr. Eric Lewis Jr.')\n",
"Row(user_id=UUID('81126ab4-842e-4d38-9a26-10c1f9237dcd'), age=21, email='mendezcassandra@example.org', name='Jonathan Cervantes')\n",
"Row(user_id=UUID('fdc0fa0b-6df8-4823-a2ea-b72235296ae0'), age=42, email='theresacooper@example.org', name='Angela Wright')\n",
"Row(user_id=UUID('082fe019-1dd7-4bea-9d6b-2e15d581e7f5'), age=44, email='alexisturner@example.net', name='Kelly Baker')\n",
"Row(user_id=UUID('1cd1ed49-3375-4e8a-bf33-46b0a4e1c73e'), age=49, email='smithbrittany@example.com', name='Gail Griffin')\n",
"Row(user_id=UUID('7c3c1e81-044c-47f3-a232-14caf878faba'), age=62, email='smithnicholas@example.com', name='Debbie Hughes')\n",
"Row(user_id=UUID('a2d0fa38-790c-4128-87e8-771c5cd82e1f'), age=76, email='vincentfreeman@example.com', name='Zachary Robinson')\n",
"Row(user_id=UUID('0451be3c-76be-43a9-aa73-ad883f99d68e'), age=48, email='sherrigray@example.net', name='Thomas Mitchell')\n",
"Row(user_id=UUID('ec3d870d-3065-4f80-866d-6b3aba76ad21'), age=27, email='christopherwright@example.com', name='Renee Little')\n"
]
}
],
"source": [
"rows = session.execute(\"SELECT * FROM user_profiles LIMIT 10\")\n",
"for row in rows:\n",
" print(row)"
]
},
{
"cell_type": "markdown",
"id": "a7768de4-472f-4eb3-a2fc-a6d1b9135201",
"metadata": {
"id": "a7768de4-472f-4eb3-a2fc-a6d1b9135201"
},
"source": [
"## Delete specific preference for a user:"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "dd966e5a-7f29-4de3-9686-8b7c7a1e21d6",
"metadata": {
"id": "dd966e5a-7f29-4de3-9686-8b7c7a1e21d6",
"outputId": "473d4b04-5921-43af-cd6c-f6e4f6f79728"
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Row(user_id=UUID('866ffe42-6287-4dd3-8f3a-86c2b9dc381a'), preferences=OrderedMapSerializedKey([('language', 'de'), ('notifications', 'enabled'), ('theme', 'dark')]))\n",
"Row(user_id=UUID('c564fb6c-4999-464e-9ae6-21e28663acd0'), preferences=OrderedMapSerializedKey([('language', 'fr'), ('theme', 'dark')]))\n",
"Row(user_id=UUID('d830f41f-1b02-4f5d-b163-7784fa1ffb24'), preferences=OrderedMapSerializedKey([('language', 'de'), ('theme', 'light')]))\n",
"Row(user_id=UUID('81126ab4-842e-4d38-9a26-10c1f9237dcd'), preferences=OrderedMapSerializedKey([('language', 'de'), ('theme', 'light')]))\n",
"Row(user_id=UUID('fdc0fa0b-6df8-4823-a2ea-b72235296ae0'), preferences=OrderedMapSerializedKey([('language', 'es'), ('theme', 'light')]))\n",
"Row(user_id=UUID('082fe019-1dd7-4bea-9d6b-2e15d581e7f5'), preferences=OrderedMapSerializedKey([('language', 'en'), ('theme', 'light')]))\n",
"Row(user_id=UUID('1cd1ed49-3375-4e8a-bf33-46b0a4e1c73e'), preferences=OrderedMapSerializedKey([('language', 'fr'), ('theme', 'dark')]))\n",
"Row(user_id=UUID('7c3c1e81-044c-47f3-a232-14caf878faba'), preferences=OrderedMapSerializedKey([('language', 'en'), ('theme', 'dark')]))\n",
"Row(user_id=UUID('a2d0fa38-790c-4128-87e8-771c5cd82e1f'), preferences=OrderedMapSerializedKey([('language', 'es'), ('theme', 'dark')]))\n",
"Row(user_id=UUID('0451be3c-76be-43a9-aa73-ad883f99d68e'), preferences=OrderedMapSerializedKey([('language', 'es'), ('theme', 'light')]))\n"
]
}
],
"source": [
"rows = session.execute(\"SELECT * FROM preference_data LIMIT 10\")\n",
"for row in rows:\n",
" print(row)"
]
},
{
"cell_type": "markdown",
"id": "b1162b11-c4e6-414d-98a7-6131ed7418a8",
"metadata": {
"id": "b1162b11-c4e6-414d-98a7-6131ed7418a8"
},
"source": [
"## we would like to delete the ('notifications', 'enabled') for user_id 866ffe42-6287-4dd3-8f3a-86c2b9dc381a"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "d9db43ed-c8e3-4750-b370-e226adc28fe9",
"metadata": {
"id": "d9db43ed-c8e3-4750-b370-e226adc28fe9",
"outputId": "8c179331-f2d4-4078-e9c0-c454d6b33838"
},
"outputs": [
{
"data": {
"text/plain": [
""
]
},
"execution_count": 103,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"session.execute(\"UPDATE preference_data SET preferences = preferences - {'notifications'} WHERE user_id = 866ffe42-6287-4dd3-8f3a-86c2b9dc381a;\")\n"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "0059bc1f-30da-4746-926f-e62aa68e2e7a",
"metadata": {
"id": "0059bc1f-30da-4746-926f-e62aa68e2e7a",
"outputId": "3555f455-62e6-45b0-f34a-45a3fc3687dd"
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Row(user_id=UUID('866ffe42-6287-4dd3-8f3a-86c2b9dc381a'), preferences=OrderedMapSerializedKey([('language', 'de'), ('theme', 'dark')]))\n",
"Row(user_id=UUID('c564fb6c-4999-464e-9ae6-21e28663acd0'), preferences=OrderedMapSerializedKey([('language', 'fr'), ('theme', 'dark')]))\n",
"Row(user_id=UUID('d830f41f-1b02-4f5d-b163-7784fa1ffb24'), preferences=OrderedMapSerializedKey([('language', 'de'), ('theme', 'light')]))\n",
"Row(user_id=UUID('81126ab4-842e-4d38-9a26-10c1f9237dcd'), preferences=OrderedMapSerializedKey([('language', 'de'), ('theme', 'light')]))\n",
"Row(user_id=UUID('fdc0fa0b-6df8-4823-a2ea-b72235296ae0'), preferences=OrderedMapSerializedKey([('language', 'es'), ('theme', 'light')]))\n",
"Row(user_id=UUID('082fe019-1dd7-4bea-9d6b-2e15d581e7f5'), preferences=OrderedMapSerializedKey([('language', 'en'), ('theme', 'light')]))\n",
"Row(user_id=UUID('1cd1ed49-3375-4e8a-bf33-46b0a4e1c73e'), preferences=OrderedMapSerializedKey([('language', 'fr'), ('theme', 'dark')]))\n",
"Row(user_id=UUID('7c3c1e81-044c-47f3-a232-14caf878faba'), preferences=OrderedMapSerializedKey([('language', 'en'), ('theme', 'dark')]))\n",
"Row(user_id=UUID('a2d0fa38-790c-4128-87e8-771c5cd82e1f'), preferences=OrderedMapSerializedKey([('language', 'es'), ('theme', 'dark')]))\n",
"Row(user_id=UUID('0451be3c-76be-43a9-aa73-ad883f99d68e'), preferences=OrderedMapSerializedKey([('language', 'es'), ('theme', 'light')]))\n"
]
}
],
"source": [
"rows = session.execute(\"SELECT * FROM preference_data LIMIT 10\")\n",
"for row in rows:\n",
" print(row)"
]
},
{
"cell_type": "markdown",
"id": "860cd787-d44a-439d-be00-0e1191fb2911",
"metadata": {
"id": "860cd787-d44a-439d-be00-0e1191fb2911"
},
"source": [
"### some advanced commands"
]
},
{
"cell_type": "markdown",
"id": "6a3e2234-f734-46f2-b0f8-3830cca722b0",
"metadata": {
"id": "6a3e2234-f734-46f2-b0f8-3830cca722b0"
},
"source": [
"## Defining and Using User-Defined Types (UDTs)"
]
},
{
"cell_type": "markdown",
"id": "31c9906f-cd6c-475f-a46c-231a79d2ef2a",
"metadata": {
"id": "31c9906f-cd6c-475f-a46c-231a79d2ef2a"
},
"source": [
"## UDTs allow you to group related data and define your own types. This can simplify schema design and queries when dealing with complex data."
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "0e2bed8f-c064-4465-b2fc-e93ab8b0550c",
"metadata": {
"id": "0e2bed8f-c064-4465-b2fc-e93ab8b0550c",
"outputId": "79327326-5fff-4b1c-a18d-4abddb05dc93"
},
"outputs": [
{
"data": {
"text/plain": [
""
]
},
"execution_count": 107,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"session.execute(\"\"\"CREATE TYPE user_address (\n",
" street TEXT,\n",
" city TEXT,\n",
" zip_code INT\n",
");\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "8695d8e6-3e08-41c3-aaca-f88cdf6518fb",
"metadata": {
"id": "8695d8e6-3e08-41c3-aaca-f88cdf6518fb",
"outputId": "0114bd2c-93eb-47f9-da27-1a20c4f3d640"
},
"outputs": [
{
"data": {
"text/plain": [
""
]
},
"execution_count": 108,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"session.execute(\"\"\"ALTER TABLE user_profiles ADD address FROZEN;\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "64e15d13-7c0d-4fa5-bc87-b1ad9199ffd6",
"metadata": {
"id": "64e15d13-7c0d-4fa5-bc87-b1ad9199ffd6",
"outputId": "97591b70-0d2f-40ce-f1d0-c1285ee45825"
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Row(user_id=UUID('866ffe42-6287-4dd3-8f3a-86c2b9dc381a'), address=None, age=50, email='scottfranco@example.com', name='Scott Franco')\n",
"Row(user_id=UUID('c564fb6c-4999-464e-9ae6-21e28663acd0'), address=None, age=67, email='jonathandavis@example.net', name='Mr. Eric Lewis Jr.')\n",
"Row(user_id=UUID('81126ab4-842e-4d38-9a26-10c1f9237dcd'), address=None, age=21, email='mendezcassandra@example.org', name='Jonathan Cervantes')\n",
"Row(user_id=UUID('fdc0fa0b-6df8-4823-a2ea-b72235296ae0'), address=None, age=42, email='theresacooper@example.org', name='Angela Wright')\n",
"Row(user_id=UUID('082fe019-1dd7-4bea-9d6b-2e15d581e7f5'), address=None, age=44, email='alexisturner@example.net', name='Kelly Baker')\n",
"Row(user_id=UUID('1cd1ed49-3375-4e8a-bf33-46b0a4e1c73e'), address=None, age=49, email='smithbrittany@example.com', name='Gail Griffin')\n",
"Row(user_id=UUID('7c3c1e81-044c-47f3-a232-14caf878faba'), address=None, age=62, email='smithnicholas@example.com', name='Debbie Hughes')\n",
"Row(user_id=UUID('a2d0fa38-790c-4128-87e8-771c5cd82e1f'), address=None, age=76, email='vincentfreeman@example.com', name='Zachary Robinson')\n",
"Row(user_id=UUID('0451be3c-76be-43a9-aa73-ad883f99d68e'), address=None, age=48, email='sherrigray@example.net', name='Thomas Mitchell')\n",
"Row(user_id=UUID('ec3d870d-3065-4f80-866d-6b3aba76ad21'), address=None, age=27, email='christopherwright@example.com', name='Renee Little')\n"
]
}
],
"source": [
"rows = session.execute(\"SELECT * FROM user_profiles LIMIT 10\")\n",
"for row in rows:\n",
" print(row)"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "1583086a-09cf-4123-a5a2-b1e5ea5e14a2",
"metadata": {
"id": "1583086a-09cf-4123-a5a2-b1e5ea5e14a2",
"outputId": "fbb5ad3f-e339-45f1-bc6c-5e030b137a1d"
},
"outputs": [
{
"data": {
"text/plain": [
""
]
},
"execution_count": 110,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"session.execute(\"UPDATE user_profiles SET address = {street: '123 Cassandra Lane', city: 'Database City', zip_code: 12345} WHERE user_id = 866ffe42-6287-4dd3-8f3a-86c2b9dc381a;\")\n",
"\n",
"\n"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "3f372bc9-e7b3-4162-bc45-42d3288af0ee",
"metadata": {
"id": "3f372bc9-e7b3-4162-bc45-42d3288af0ee",
"outputId": "44bdd8ae-598b-42d9-dc56-2e9c5aef2eab"
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Row(user_id=UUID('866ffe42-6287-4dd3-8f3a-86c2b9dc381a'), address=user_address(street='123 Cassandra Lane', city='Database City', zip_code=12345), age=50, email='scottfranco@example.com', name='Scott Franco')\n",
"Row(user_id=UUID('c564fb6c-4999-464e-9ae6-21e28663acd0'), address=None, age=67, email='jonathandavis@example.net', name='Mr. Eric Lewis Jr.')\n",
"Row(user_id=UUID('81126ab4-842e-4d38-9a26-10c1f9237dcd'), address=None, age=21, email='mendezcassandra@example.org', name='Jonathan Cervantes')\n",
"Row(user_id=UUID('fdc0fa0b-6df8-4823-a2ea-b72235296ae0'), address=None, age=42, email='theresacooper@example.org', name='Angela Wright')\n",
"Row(user_id=UUID('082fe019-1dd7-4bea-9d6b-2e15d581e7f5'), address=None, age=44, email='alexisturner@example.net', name='Kelly Baker')\n",
"Row(user_id=UUID('1cd1ed49-3375-4e8a-bf33-46b0a4e1c73e'), address=None, age=49, email='smithbrittany@example.com', name='Gail Griffin')\n",
"Row(user_id=UUID('7c3c1e81-044c-47f3-a232-14caf878faba'), address=None, age=62, email='smithnicholas@example.com', name='Debbie Hughes')\n",
"Row(user_id=UUID('a2d0fa38-790c-4128-87e8-771c5cd82e1f'), address=None, age=76, email='vincentfreeman@example.com', name='Zachary Robinson')\n",
"Row(user_id=UUID('0451be3c-76be-43a9-aa73-ad883f99d68e'), address=None, age=48, email='sherrigray@example.net', name='Thomas Mitchell')\n",
"Row(user_id=UUID('ec3d870d-3065-4f80-866d-6b3aba76ad21'), address=None, age=27, email='christopherwright@example.com', name='Renee Little')\n"
]
}
],
"source": [
"rows = session.execute(\"SELECT * FROM user_profiles LIMIT 10\")\n",
"for row in rows:\n",
" print(row)"
]
},
{
"cell_type": "markdown",
"id": "757d9b27-377a-4bcc-b6be-058becd8a0c2",
"metadata": {
"id": "757d9b27-377a-4bcc-b6be-058becd8a0c2"
},
"source": [
"## Thank you :)"
]
}
],
"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.11.8"
},
"colab": {
"provenance": [],
"include_colab_link": true
}
},
"nbformat": 4,
"nbformat_minor": 5
}