notebook.bin 0100644 0000000 0000000 00000413047 13625045620 012133 0 ustar 00 0000000 0000000 json_notebook_v1 {"1":"cdd709c8-1ef8-4d54-bfea-60b81094363c","10":"dd031ba4-30eb-4dbf-9000-4df5fac6ead5","11":"Notebook 3 - Coding with Cassandra","12":{"1":1582150472,"2":66000000},"13":{"1":1582582599,"2":849000000},"14":false,"15":[{"1":"3b20d584-2c46-49d6-931e-5350825125a7","10":4,"11":"
,\n added_date timestamp,\n PRIMARY KEY (videoid)\n);\n\n// One-to-many from user point of view (lookup table)\nCREATE TABLE IF NOT EXISTS user_videos (\n userid uuid,\n added_date timestamp,\n videoid uuid,\n name text,\n preview_image_location text,\n PRIMARY KEY (userid, added_date, videoid)\n) WITH CLUSTERING ORDER BY (added_date DESC, videoid ASC);\n\n// Track latest videos, grouped by day (if we ever develop a bad hotspot from the daily grouping here, we could mitigate by\n// splitting the row using an arbitrary group number, making the partition key (yyyymmdd, group_number))\nCREATE TABLE IF NOT EXISTS latest_videos (\n yyyymmdd text,\n added_date timestamp,\n videoid uuid,\n userid uuid,\n name text,\n preview_image_location text,\n PRIMARY KEY (yyyymmdd, added_date, videoid)\n) WITH CLUSTERING ORDER BY (added_date DESC, videoid ASC);\n\n// Video ratings (counter table)\nCREATE TABLE IF NOT EXISTS video_ratings (\n videoid uuid,\n rating_counter counter,\n rating_total counter,\n PRIMARY KEY (videoid)\n);\n\n// Video ratings by user (to try and mitigate voting multiple times)\nCREATE TABLE IF NOT EXISTS video_ratings_by_user (\n videoid uuid,\n userid uuid,\n rating int,\n PRIMARY KEY (videoid, userid)\n);\n\n// Records the number of views/playbacks of a video\nCREATE TABLE IF NOT EXISTS video_playback_stats (\n videoid uuid,\n views counter,\n PRIMARY KEY (videoid)\n);\n\n// Recommendations by user (powered by Spark), with the newest videos added to the site always first\nCREATE TABLE IF NOT EXISTS video_recommendations ( \n userid uuid,\n added_date timestamp,\n videoid uuid,\n rating float,\n authorid uuid,\n name text,\n preview_image_location text,\n PRIMARY KEY(userid, added_date, videoid)\n) WITH CLUSTERING ORDER BY (added_date DESC, videoid ASC);\n\n// Recommendations by video (powered by Spark)\nCREATE TABLE IF NOT EXISTS video_recommendations_by_video (\n videoid uuid,\n userid uuid,\n rating float,\n added_date timestamp STATIC,\n authorid uuid STATIC,\n name text STATIC,\n preview_image_location text STATIC,\n PRIMARY KEY(videoid, userid)\n);\n\n// Index for tag keywords\nCREATE TABLE IF NOT EXISTS videos_by_tag (\n tag text,\n videoid uuid,\n added_date timestamp,\n userid uuid,\n name text,\n preview_image_location text,\n tagged_date timestamp,\n PRIMARY KEY (tag, videoid)\n);\n\n// Index for tags by first letter in the tag\nCREATE TABLE IF NOT EXISTS tags_by_letter (\n first_letter text,\n tag text,\n PRIMARY KEY (first_letter, tag)\n);\n\n// Comments for a given video\nCREATE TABLE IF NOT EXISTS comments_by_video (\n videoid uuid,\n commentid timeuuid,\n userid uuid,\n comment text,\n PRIMARY KEY (videoid, commentid)\n) WITH CLUSTERING ORDER BY (commentid DESC);\n\n// Comments for a given user\nCREATE TABLE IF NOT EXISTS comments_by_user (\n userid uuid,\n commentid timeuuid,\n videoid uuid,\n comment text,\n PRIMARY KEY (userid, commentid)\n) WITH CLUSTERING ORDER BY (commentid DESC);\n\nINSERT INTO killrvideo.users (userid, created_date, firstname, lastname, email)\n VALUES(11111111-1111-1111-1111-111111111111, toTimestamp(now()), 'Jeff', 'Carpenter', 'jc@datastax.com');\nINSERT INTO killrvideo.user_credentials (userid, email, password)\n VALUES(11111111-1111-1111-1111-111111111111, 'jc@datastax.com', 'J3ffL0v3$C@ss@ndr@');\n \nINSERT INTO killrvideo.users (userid, created_date, firstname, lastname, email)\n VALUES(22222222-2222-2222-2222-222222222222, toTimestamp(now()), 'Eric', 'Zietlow', 'ez@datastax.com');\nINSERT INTO killrvideo.user_credentials (userid, email, password)\n VALUES(22222222-2222-2222-2222-222222222222, 'ez@datastax.com', 'C@ss@ndr@R0ck$');\n\nINSERT INTO killrvideo.users (userid, created_date, firstname, lastname, email)\n VALUES(33333333-3333-3333-3333-333333333333, toTimestamp(now()), 'Cedrick', 'Lunven', 'cl@datastax.com');\nINSERT INTO killrvideo.user_credentials (userid, email, password)\n VALUES(33333333-3333-3333-3333-333333333333, 'cl@datastax.com', 'Fr@nc3L0v3$C@ss@ndr@');\n\nINSERT INTO killrvideo.users (userid, created_date, firstname, lastname, email)\n VALUES(44444444-4444-4444-4444-444444444444, toTimestamp(now()), 'David', 'Gilardi', 'dg@datastax.com');\nINSERT INTO killrvideo.user_credentials (userid, email, password)\n VALUES(44444444-4444-4444-4444-444444444444, 'dg@datastax.com', 'H@t$0ff2C@ss@ndr@');\n\n//INSERT INTO killrvideo.users (userid, created_date, firstname, lastname, email)\n// VALUES(55555555-5555-5555-5555-555555555555, toTimestamp(now()), 'Cristina', 'Veale', 'cv@datastax.com');\n//INSERT INTO killrvideo.user_credentials (userid, email, password)\n// VALUES(55555555-5555-5555-5555-555555555555, 'cv@datastax.com', '3@$tC0@$tC@ss@ndr@');\n\nINSERT INTO killrvideo.users (userid, created_date, firstname, lastname, email)\n VALUES(66666666-6666-6666-6666-666666666666, toTimestamp(now()), 'Adron', 'Hall', 'ah@datastax.com');\nINSERT INTO killrvideo.user_credentials (userid, email, password)\n VALUES(66666666-6666-6666-6666-666666666666, 'ah@datastax.com', 'C@ss@ndr@43v3r');\n\nINSERT INTO killrvideo.users (userid, created_date, firstname, lastname, email)\n VALUES(77777777-7777-7777-7777-777777777777, toTimestamp(now()), 'Aleks', 'volochnev', 'av@datastax.com');\nINSERT INTO killrvideo.user_credentials (userid, email, password)\n VALUES(77777777-7777-7777-7777-777777777777, 'av@datastax.com', 'C@ss@ndr@3v3rywh3r3');\n","12":"cql","16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"1b315f9c-4919-4291-a1a6-9b12ab79f465","10":4,"11":"![line](https://s3.amazonaws.com/datastaxtraining/CaaS/line.png \"line\" )\n# Install Credentials\n\n### In this section, you will do the following things:\n- #### Launch the Theia IDE\n- #### Install the credentials file provided by Astra\n\n
\n#### One of the great things about Astra is it's secure, but that doesn't mean it's hard to use.\n#### Astra provides you with a .zip
file that contains everything you need to connect to Astra, and you don't even have to unzip it!\n\n---\n\n Important Note:
\nIf you completed the Bonus Challenge in the CQL unit, then you have already Installed the credentials. If so, peruse the instructions in this section and make sure what you did was consistent and then move to the next section.
\n\n---\n#### Step 1: Open a new tab in your browser for Theia. Go to the course landing page and click on the _Eclipse Theia IDE_ link.\n![LaunchTheia](https://s3.amazonaws.com/datastaxtraining/CaaS/LaunchTheia.png)\n","12":"markdown","13":{"1":"1ed6f5e9-13f8-4103-887d-ad1fc0a05427","10":{"9":"\nInstall Credentials
\nIn this section, you will do the following things:
\n\nLaunch the Theia IDE
\n \nInstall the credentials file provided by Astra
\n \n
\n
\nOne of the great things about Astra is it's secure, but that doesn't mean it's hard to use.
\nAstra provides you with a .zip
file that contains everything you need to connect to Astra, and you don't even have to unzip it!
\n
\n Important Note:
\nIf you completed the Bonus Challenge in the CQL unit, then you have already Installed the credentials. If so, peruse the instructions in this section and make sure what you did was consistent and then move to the next section.
\n
\nStep 1: Open a new tab in your browser for Theia. Go to the course landing page and click on the Eclipse Theia IDE link.
\n\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"6be6b3ae-33aa-46af-b22d-53df11b35aa1","10":4,"11":"#### Step 2: If prompted, enter the IDE credentials. We gave you these credentials along with the URL for the course landing page.\n![EnterIDECreds](https://s3.amazonaws.com/datastaxtraining/CaaS/EnterIDECreds.png)\n","12":"markdown","13":{"1":"7ffd9770-4ba9-4fc4-be0b-6153472b8749","10":{"9":"Step 2: If prompted, enter the IDE credentials. We gave you these credentials along with the URL for the course landing page.
\n\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"8eb9a349-0220-43f0-be92-0146b81d3149","10":4,"11":"#### Step 3: Open a terminal in the IDE - we will use the terminal to download the credentials file.\n![OpenIDETerminal](https://s3.amazonaws.com/datastaxtraining/CaaS/OpenIDETerminal.png)\n","12":"markdown","13":{"1":"d3200e78-92e0-4ebe-9d3b-941e70661730","10":{"9":"Step 3: Open a terminal in the IDE - we will use the terminal to download the credentials file.
\n\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"9ac1616d-8370-4195-87f4-55c2c0bd564a","10":4,"11":"#### Step 4: Use the pwd
command to take note of the current directory. This is where we will place the credentials file.\n![PWD](https://s3.amazonaws.com/datastaxtraining/CaaS/PWD.png)\n","12":"markdown","13":{"1":"65b50681-53c9-4cfe-ac3a-963682c8ac9e","10":{"9":"Step 4: Use the pwd
command to take note of the current directory. This is where we will place the credentials file.
\n\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"d641f8d9-190d-4d21-adb9-d768096cbab5","10":4,"11":"#### Step 5: Back in the Constellation tab of your browser, copy the credentials file link address to your clipboard as shown.\n#### **Note:** This link will expire after five minutes, so don't delay the next few steps which will require this link.\n![CopyLink](https://s3.amazonaws.com/datastaxtraining/CaaS/CopyLink.png)\n","12":"markdown","13":{"1":"46fd596c-a5f5-4e75-8036-9c3597ba848d","10":{"9":"Step 5: Back in the Constellation tab of your browser, copy the credentials file link address to your clipboard as shown.
\nNote: This link will expire after five minutes, so don't delay the next few steps which will require this link.
\n\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"319a6e1b-6068-47de-a849-d5de37e92cd0","10":4,"11":"#### Step 6: Back in the IDE terminal, create a curl
command to download the credentials file. The command looks like curl \"paste_the_link_here\" > creds.zip
. Note that the double quotes are part of the command and surround the contents you paste from your clipboard. The right angle-bracket redirects the output from the curl
command to create the creds.zip
file.\n![CurlCommand](https://s3.amazonaws.com/datastaxtraining/CaaS/CurlCommand.png)\n","12":"markdown","13":{"1":"6d531882-08da-4251-9be5-9038e9c09736","10":{"9":"Step 6: Back in the IDE terminal, create a curl
command to download the credentials file. The command looks like curl “paste_the_link_here” > creds.zip
. Note that the double quotes are part of the command and surround the contents you paste from your clipboard. The right angle-bracket redirects the output from the curl
command to create the creds.zip
file.
\n\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"2cbba2eb-7807-461c-8d60-3b916be6f396","10":4,"11":"#### Step 7: Review the contents of the directory to make sure curl
worked as expected. Use the ls -al
command. You should see a file named creds.zip
with a size similar to that shown:\n![DirContents](https://s3.amazonaws.com/datastaxtraining/CaaS/DirContents.png)\n
\n#### That's it! Now your credentials file is in place and ready for use!\n","12":"markdown","13":{"1":"63fcff0e-c83c-4779-b722-4296b24f7a79","10":{"9":"Step 7: Review the contents of the directory to make sure curl
worked as expected. Use the ls -al
command. You should see a file named creds.zip
with a size similar to that shown:
\n\n
\nThat's it! Now your credentials file is in place and ready for use!
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"922ff2e6-fd7d-4399-b3b4-433c7402a679","10":4,"11":"![line](https://s3.amazonaws.com/datastaxtraining/CaaS/line.png \"line\" )\n# `INSERT` is the _C_ in CRUD\n\n### In this section, you will do the following things:\n- #### Review a stand-alone program for inserting a row into the `user_credentials` table\n\n
\n#### When KillrVideo wants to authenticate a user, the app accesses the `user_credentials` table.\n#### Given an email address, KillrVideo retrieves the password hash and the user ID value.\n
\n\n#### Step 1: Review the following table definition:\n```\nCREATE TABLE IF NOT EXISTS user_credentials (\n email text,\n password text,\n userid uuid,\n PRIMARY KEY ((email))\n);\n```\n\n#### The setup script you ran at the beginning of the notebook populated this table with some rows.\n\n","12":"markdown","13":{"1":"32f570a6-a5bb-437e-82d3-2c887074bfc1","10":{"9":"\nINSERT
is the C in CRUD
\nIn this section, you will do the following things:
\n\nReview a stand-alone program for inserting a row into the user_credentials
table
\n \n
\n
\nWhen KillrVideo wants to authenticate a user, the app accesses the user_credentials
table.
\nGiven an email address, KillrVideo retrieves the password hash and the user ID value.
\n
\nStep 1: Review the following table definition:
\nCREATE TABLE IF NOT EXISTS user_credentials (\n email text,\n password text,\n userid uuid,\n PRIMARY KEY ((email))\n);\n
\nThe setup script you ran at the beginning of the notebook populated this table with some rows.
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.ONE"},{"1":"b1262b77-d548-4838-8acc-b5d3e5cbbc20","10":4,"11":"#### Step 2: Review the current `user_credentials` contents by executing the following cell.\n","12":"markdown","13":{"1":"ccfeee60-2ed5-48d5-b684-7d44e215c3cb","10":{"9":"Step 2: Review the current user_credentials
contents by executing the following cell.
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"7c8d963f-a7bc-4246-9806-6f6de85fb620","11":"// Execute this cell to see the contents of the user_credentials table\n// Remember, to execute the cell, click in the cell and press SHIFT+ENTER (or just click on the Run button in the top-right corner of the cell)\nSELECT * FROM killrvideo.user_credentials;","12":"cql","16":true,"17":false,"18":{},"22":364,"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"7b62ba69-74f0-4f64-bf68-721464308da5","10":4,"11":"#### If you look at the `user_credentials` table carefully, you may notice two things:\n- #### We are not using real `uuid` values; Instead, we are using contrived values to keep things simple (we would not use contrived values in a production system)\n- #### Cristina Veale's credentials are missing from the table\n\n
\n#### In this section, we will see a program to insert Cristina's credentials into the table.\n
\n#### Step 3: Install the driver - Actually, we've already done this, so let's review what we did. Click on your language of choice and review the installation process.\n\n\nJava - click here.
\n> In this example, we are using Maven.\n> So, we update our `pom.xml` file, and let Maven do the heavy lifting for us.\n> In Theia, within the crud-java project, click to open the `pom.xml` file (`~/workspace/crud-java/pom.xml`).\n> You'll find it here:\n> ![PomFile](https://s3.amazonaws.com/datastaxtraining/CaaS/PomFile.png)\n> You will notice that we have updated the `pom.xml` file with the necessary repositories and dependencies.\n```\n \n \n com.datastax.dse\n dse-java-driver-core\n 2.3.0\n \n \n ch.qos.logback\n logback-classic\n 1.2.3\n \n \n```\n> No need to change anything here, we just wanted to show you what we had done to get you set up!\n \n\n\nNode.js - click here.
\n> For Node.js, use `npm` to install the driver with this command: `npm install dse-driver`.\n> You don't need to run this command - we've done it for you!\n>\n> You will also notice the `package-lock.json` and `package.json` files.\n> These are dependency files that the `npm` command creates for you.\n> We'll leave these files in the project so we connect using the correct driver.\n \n\n\nPython - click here.
\n> For Python, use `pip` to install the driver with this command: `pip install dse-driver`.\n> You don't need to run this command - we've done it for you!\n \n","12":"markdown","13":{"1":"3fe7773a-e5dd-4bb8-8afd-c881635a8228","10":{"9":"If you look at the user_credentials
table carefully, you may notice two things:
\n\nWe are not using real uuid
values; Instead, we are using contrived values to keep things simple (we would not use contrived values in a production system)
\n \nCristina Veale's credentials are missing from the table
\n \n
\n
\nIn this section, we will see a program to insert Cristina's credentials into the table.
\n
\nStep 3: Install the driver - Actually, we've already done this, so let's review what we did. Click on your language of choice and review the installation process.
\n\n
Java - click here.
\nIn this example, we are using Maven.\n
So, we update our pom.xml
file, and let Maven do the heavy lifting for us.\n
In Theia, within the crud-java project, click to open the pom.xml
file (~/workspace/crud-java/pom.xml
).\n
You'll find it here:\n
\n
You will notice that we have updated the pom.xml
file with the necessary repositories and dependencies.\n <dependencies>\n <dependency>\n <groupId>com.datastax.dse</groupId>\n <artifactId>dse-java-driver-core</artifactId>\n <version>2.3.0</version>\n </dependency>\n <dependency>\n <groupId>ch.qos.logback</groupId>\n <artifactId>logback-classic</artifactId>\n <version>1.2.3</version>\n </dependency>\n </dependencies>\n
\nNo need to change anything here, we just wanted to show you what we had done to get you set up!\n
\n
\n\n
Node.js - click here.
\nFor Node.js, use npm
to install the driver with this command: npm install dse-driver
.\n
You don't need to run this command - we've done it for you!
\nYou will also notice the package-lock.json
and package.json
files.\n
These are dependency files that the npm
command creates for you.\n
We'll leave these files in the project so we connect using the correct driver.\n
\n
\n\n
Python - click here.
\nFor Python, use pip
to install the driver with this command: pip install dse-driver
.\n
You don't need to run this command - we've done it for you!\n
\n
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.ONE"},{"1":"1f03a2dc-0992-4ef3-9f83-8f2617f7182e","10":4,"11":"#### Step 4: Open the insert example code file.\n- #### Check out your language of choice\n\n\nJava - click here.
\n> You will find the example code in the `crud-java` project (`~/workspace/crud-java/src/main/java/Insert.java`).\n> ![JavaInsertPath](https://s3.amazonaws.com/datastaxtraining/CaaS/JavaInsertPath.png \"JavaInsertPath\" )\n> Click on the file name to open the file.\n \n\n\nNode.js - click here.
\n> You will find the example code in the `crud-node-js` project (`~/workspace/crud-node-js/insert.js`).\n> ![NodeInsertPath](https://s3.amazonaws.com/datastaxtraining/CaaS/NodeInsertPath.png \"NodeInsertPath\" )\n> Click on the file name to open the file.\n \n\n\nPython - click here.
\n> You will find the example code in the `crud-python` project (`~/workspace/crud-python/insert.py`).\n> ![PythonInsertPath](https://s3.amazonaws.com/datastaxtraining/CaaS/PythonInsertPath.png \"PythonInsertPath\" )\n> Click on the file name to open the file.\n \n","12":"markdown","13":{"1":"eca63ce9-a6d6-4e06-ae7c-9266f1c349b8","10":{"9":"Step 4: Open the insert example code file.
\n\nCheck out your language of choice
\n \n
\n\n
Java - click here.
\nYou will find the example code in the crud-java
project (~/workspace/crud-java/src/main/java/Insert.java
).\n
\n
Click on the file name to open the file.\n
\n
\n\n
Node.js - click here.
\nYou will find the example code in the crud-node-js
project (~/workspace/crud-node-js/insert.js
).\n
\n
Click on the file name to open the file.\n
\n
\n\n
Python - click here.
\nYou will find the example code in the crud-python
project (~/workspace/crud-python/insert.py
).\n
\n
Click on the file name to open the file.\n
\n
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"a516a0bc-cc6e-48df-862f-154f573df978","10":4,"11":"#### Step 5: Review the code.\n- #### Check out your language of choice\n\n\nJava - click here.
\n> First, the code creates a session:\n```\ntry (DseSession session = DseSession.builder().withCloudSecureConnectBundle(DBConnection.getConnectionPath())\n .withAuthCredentials(DBConnection.getUsername(), DBConnection.getPassword())\n .build()) {\n```\n> Notice that `session` exists within the `try` clause.\n> `DseSession` implements the `java.io.Closeable` interface, so you don't have to close the session explicitly.\n> Instead the `try` clause takes care of that for you.\n> This is especially useful for handling exceptions, etc.\n>\n> You see that we retrieve the connection information from the `DBConnection` class.\n> We created the `DBConnection` class so we could centralize the connection information in one place.\n> We have hard-coded the connection information in the `DBConnection` class.\n> This would be a security problem in a production app, but we allow this usage here just to keep things simple.\n>\n> Also, in a real app, you wouldn't want to open and close a session for each operation.\n> That would cause too much overhead.\n> But we show the session creation and closing here to illustrate the complete session lifecycle.\n>\n> Here's the code that uses the session to execute the command.\n```\nsession.execute(\n SimpleStatement.builder( \"INSERT INTO killrvideo.user_credentials (email, password, userid) VALUES (?,?,?)\")\n .addPositionalValues(\"cv@datastax.com\", \"3@$tC0@$tC@ss@ndr@\", UUID.fromString(\"55555555-5555-5555-5555-555555555555\"))\n .build());\n```\n> It is easy to pick out the CQL string as an argument to the `SimpleStatement.builder()` method call.\n> You can also see the question marks (`?`) that serve as place holders for value substitution.\n> The values are on the next line in the call to the `addPositionalValues()` method.\n> Three question marks, three values!\n>\n> As you will see, all the CRUD operations follow this same pattern.\n \n\n\n\nNode.js - click here.
\n> First, the code creates a session using the code in `db_connection.js` (we will discuss this code in the next step).\n```\nconst connection = require('./db_connection')\n```\n> Here's the code that uses the session to execute the command.\n```\nconst insert = 'INSERT INTO killrvideo.user_credentials (email, password, userid) VALUES (?,?,?)';\nconst params = ['cv@datastax.com', '3@$tC0@$tC@ss@ndr@', Uuid.fromString('55555555-5555-5555-5555-555555555555')];\nconnection.client.execute(insert, params)\n.then(function (result){\n console.log('Success')\n connection.client.shutdown()\n})\n.catch(function (error){\n console.log(error.message)\n connection.client.shutdown()\n});\n```\n> It is easy to pick out the CQL string assigned to the `insert` variable.\n> You can also see the question marks (`?`) that serve as place holders for value substitution.\n> The values are on the next line assigned to the `params` variable.\n> The `execute()` method inserts the values into the statement.\n> Three question marks, three values!\n>\n> As you will see, all the CRUD operations follow this same pattern.\n>\n> Also notice that the `execute()` method call has a `then` clause and `catch` clause.\n> These additional clauses are important to make sure the code closes the session.\n>\n> In a real app, you wouldn't want to open and close a session for each operation.\n> That would cause too much overhead.\n> But we show the session creation and closing here to illustrate the complete session lifecycle.\n \n\n\nPython - click here.
\n> First, the code creates a session using the code in `db_connection.py` (we will discuss this code in the next step).\n```\nfrom db_connection import Connection\nconnection = Connection()\n```\n> Here's the code that uses the session to execute the command.\n```\noutput = connection.session.execute(\n \"INSERT INTO killrvideo.user_credentials (email, password, userid) VALUES (%s, %s, %s)\",\n ('cv@datastax.com', '3@$tC0@$tC@ss@ndr@', uuid.UUID('{55555555-5555-5555-5555-555555555555}'))\n)\n```\n> It is easy to pick out the CQL string as an argument to the `execute()` method call.\n> You can also see the `%s`s that serve as place holders for value substitution.\n> The values are on the next line as a second argument to the method call.\n> Three `%s`, three values!\n>\n> As you will see, all the CRUD operations follow this same pattern.\n>\n> When the session goes out of scope there are context management functions that handle the session clean-up for you.\n>\n> In a real app, you wouldn't want to open and close a session for each operation.\n> That would cause too much overhead.\n> But we show the session creation and closing here to illustrate the complete session lifecycle.\n \n\n","12":"markdown","13":{"1":"67ae38cc-f65e-4938-a25f-474264a0ccb3","10":{"9":"Step 5: Review the code.
\n\nCheck out your language of choice
\n \n
\n\n
Java - click here.
\nFirst, the code creates a session:
\ntry (DseSession session = DseSession.builder().withCloudSecureConnectBundle(DBConnection.getConnectionPath())\n .withAuthCredentials(DBConnection.getUsername(), DBConnection.getPassword())\n .build()) {\n
\nNotice that session
exists within the try
clause.\n
DseSession
implements the java.io.Closeable
interface, so you don't have to close the session explicitly.\n
Instead the try
clause takes care of that for you.\n
This is especially useful for handling exceptions, etc.
\nYou see that we retrieve the connection information from the DBConnection
class.\n
We created the DBConnection
class so we could centralize the connection information in one place.\n
We have hard-coded the connection information in the DBConnection
class.\n
This would be a security problem in a production app, but we allow this usage here just to keep things simple.
\nAlso, in a real app, you wouldn't want to open and close a session for each operation.\n
That would cause too much overhead.\n
But we show the session creation and closing here to illustrate the complete session lifecycle.
\nHere's the code that uses the session to execute the command.
\nsession.execute(\n SimpleStatement.builder( \"INSERT INTO killrvideo.user_credentials (email, password, userid) VALUES (?,?,?)\")\n .addPositionalValues(\"cv@datastax.com\", \"3@$tC0@$tC@ss@ndr@\", UUID.fromString(\"55555555-5555-5555-5555-555555555555\"))\n .build());\n
\nIt is easy to pick out the CQL string as an argument to the SimpleStatement.builder()
method call.\n
You can also see the question marks (?
) that serve as place holders for value substitution.\n
The values are on the next line in the call to the addPositionalValues()
method.\n
Three question marks, three values!
\nAs you will see, all the CRUD operations follow this same pattern.\n
\n
\n\n
Node.js - click here.
\nFirst, the code creates a session using the code in db_connection.js
(we will discuss this code in the next step).
\nconst connection = require('./db_connection')\n
\nHere's the code that uses the session to execute the command.
\nconst insert = 'INSERT INTO killrvideo.user_credentials (email, password, userid) VALUES (?,?,?)';\nconst params = ['cv@datastax.com', '3@$tC0@$tC@ss@ndr@', Uuid.fromString('55555555-5555-5555-5555-555555555555')];\nconnection.client.execute(insert, params)\n.then(function (result){\n console.log('Success')\n connection.client.shutdown()\n})\n.catch(function (error){\n console.log(error.message)\n connection.client.shutdown()\n});\n
\nIt is easy to pick out the CQL string assigned to the insert
variable.\n
You can also see the question marks (?
) that serve as place holders for value substitution.\n
The values are on the next line assigned to the params
variable.\n
The execute()
method inserts the values into the statement.\n
Three question marks, three values!
\nAs you will see, all the CRUD operations follow this same pattern.
\nAlso notice that the execute()
method call has a then
clause and catch
clause.\n
These additional clauses are important to make sure the code closes the session.
\nIn a real app, you wouldn't want to open and close a session for each operation.\n
That would cause too much overhead.\n
But we show the session creation and closing here to illustrate the complete session lifecycle.\n
\n
\n\n
Python - click here.
\nFirst, the code creates a session using the code in db_connection.py
(we will discuss this code in the next step).
\nfrom db_connection import Connection\nconnection = Connection()\n
\nHere's the code that uses the session to execute the command.
\noutput = connection.session.execute(\n \"INSERT INTO killrvideo.user_credentials (email, password, userid) VALUES (%s, %s, %s)\",\n ('cv@datastax.com', '3@$tC0@$tC@ss@ndr@', uuid.UUID('{55555555-5555-5555-5555-555555555555}'))\n)\n
\nIt is easy to pick out the CQL string as an argument to the execute()
method call.\n
You can also see the %s
s that serve as place holders for value substitution.\n
The values are on the next line as a second argument to the method call.\n
Three %s
, three values!
\nAs you will see, all the CRUD operations follow this same pattern.
\nWhen the session goes out of scope there are context management functions that handle the session clean-up for you.
\nIn a real app, you wouldn't want to open and close a session for each operation.\n
That would cause too much overhead.\n
But we show the session creation and closing here to illustrate the complete session lifecycle.\n
\n
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"8c91c151-310d-4bd6-a89f-e2f225925be7","10":4,"11":"\n#### Step 6: Review the connection information.\n- #### We connect using the credentials file we downloaded earlier, but we need to tell the app where that file is\n- #### Also, we need to supply the credentials for the database access - these are not included in `creds.zip` as that would constitute a security risk\n- #### Check out your language of choice\n\n\nJava - click here.
\n> Open the DBConnection.java file in Theia (`~/workspace/crud-java/src/main/java/DBConnection.java`).\n> You'll find it in the _crud-java_ project.\n> ![JavaDBConnectionPath](https://s3.amazonaws.com/datastaxtraining/CaaS/JavaDBConnectionPath.png)\n> Notice that static strings contain the `connectionPath`, `username` and `password` (although we wrap the `connectionPath` with the `Path` interface).\n> The class uses getters to access these values.\n> The point to this review is that these values are just simple strings that you could store in a configuration file - nothing fancy.\n \n\n\nNode.js - click here.
\n> Open the `db_connection.js` file in Theia (`~/workspace/crud-node-js/db_connection.js`).\n> You'll find it in the _crud-node-js_ project.\n> ![NodeDBConnectionPath](https://s3.amazonaws.com/datastaxtraining/CaaS/NodeDBConnectionPath.png)\n> Notice that this code contains the path to the credentials file as well as the database username and password values.\n> These are simple strings that could (and probably should) be stored in a configuration file, but we have them in code in this example to keep things simple.\n \n\n\nPython - click here.
\n> Open the db_connection.py file in Theia (`~/workspace/crud-python/db_connection.py`).\n> You'll find it in the _crud-python_ project.\n> ![PythonDBConnectionPath](https://s3.amazonaws.com/datastaxtraining/CaaS/PythonDBConnectionPath.png)\n> Notice that this code contains the path to the credentials file as well as the database username and password values.\n> These are simple strings that could (and probably should) be stored in a configuration file, but we have them in code in this example to keep things simple.\n \n","12":"markdown","13":{"1":"8518a41e-904c-4921-86fd-e38f2ffef21e","10":{"9":"Step 6: Review the connection information.
\n\nWe connect using the credentials file we downloaded earlier, but we need to tell the app where that file is
\n \nAlso, we need to supply the credentials for the database access - these are not included in creds.zip
as that would constitute a security risk
\n \nCheck out your language of choice
\n \n
\n\n
Java - click here.
\nOpen the DBConnection.java file in Theia (~/workspace/crud-java/src/main/java/DBConnection.java
).\n
You'll find it in the crud-java project.\n
\n
Notice that static strings contain the connectionPath
, username
and password
(although we wrap the connectionPath
with the Path
interface).\n
The class uses getters to access these values.\n
The point to this review is that these values are just simple strings that you could store in a configuration file - nothing fancy.\n
\n
\n\n
Node.js - click here.
\nOpen the db_connection.js
file in Theia (~/workspace/crud-node-js/db_connection.js
).\n
You'll find it in the crud-node-js project.\n
\n
Notice that this code contains the path to the credentials file as well as the database username and password values.\n
These are simple strings that could (and probably should) be stored in a configuration file, but we have them in code in this example to keep things simple.\n
\n
\n\n
Python - click here.
\nOpen the db_connection.py file in Theia (~/workspace/crud-python/db_connection.py
).\n
You'll find it in the crud-python project.\n
\n
Notice that this code contains the path to the credentials file as well as the database username and password values.\n
These are simple strings that could (and probably should) be stored in a configuration file, but we have them in code in this example to keep things simple.\n
\n
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"ffa533a9-96eb-454d-befd-0944311e1b45","10":4,"11":"\n#### Step 7: Run the code.\n- #### We've created a language-specific run command\n- #### Check out your language of choice\n\n\nJava - click here.
\n> Run the _Java-Insert_ command as shown.\n> ![JavaInsertCommand](https://s3.amazonaws.com/datastaxtraining/CaaS/JavaInsertCommand.png \"JavaInsertCommand\" )\n> You'll find your results in a tab near the bottom.\n> ![JavaInsertResults](https://s3.amazonaws.com/datastaxtraining/CaaS/JavaInsertResults.png \"JavaInsertResults\" )\n \n\n\nNode.js - click here.
\n> Run the _Node-Insert_ command as shown.\n> ![NodeInsertCommand](https://s3.amazonaws.com/datastaxtraining/CaaS/NodeInsertCommand.png \"NodeInsertCommand\" )\n> You'll find your results in a tab near the bottom.\n> ![NodeInsertResults](https://s3.amazonaws.com/datastaxtraining/CaaS/NodeInsertResults.png \"NodeInsertResults\" )\n \n\n\nPython - click here.
\n> Run the _Python-Insert_ command as shown.\n> ![PythonInsertCommand](https://s3.amazonaws.com/datastaxtraining/CaaS/PythonInsertCommand.png \"PythonInsertCommand\" )\n> You'll find your results in a tab near the bottom.\n> ![PythonInsertResults](https://s3.amazonaws.com/datastaxtraining/CaaS/PythonInsertResults.png \"PythonInsertResults\" )\n \n","12":"markdown","13":{"1":"002a93dc-22ff-41c4-ab49-87f3e960c6a6","10":{"9":"Step 7: Run the code.
\n\nWe've created a language-specific run command
\n \nCheck out your language of choice
\n \n
\n\n
Java - click here.
\nRun the Java-Insert command as shown.\n
\n
You'll find your results in a tab near the bottom.\n
\n
\n
\n\n
Node.js - click here.
\nRun the Node-Insert command as shown.\n
\n
You'll find your results in a tab near the bottom.\n
\n
\n
\n\n
Python - click here.
\nRun the Python-Insert command as shown.\n
\n
You'll find your results in a tab near the bottom.\n
\n
\n
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"26be7823-c565-49c6-8110-f34aff01ec4a","10":4,"11":"#### Step 8: Finally, review the contents of the table to verify the insert worked correctly. Execute the next cell.","12":"markdown","13":{"1":"41cc1064-8d1c-49fe-aee3-01088a1e5763","10":{"9":"Step 8: Finally, review the contents of the table to verify the insert worked correctly. Execute the next cell.
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"42cc14f9-8529-4a7c-87a3-4923e70fe94a","11":"// Execute this cell to see the contents of the user_credentials table after the INSERT\n// Remember, to execute the cell, click in the cell and press SHIFT+ENTER (or just click on the Run button in the top-right corner of the cell)\nSELECT * FROM killrvideo.user_credentials;","12":"cql","16":true,"17":false,"18":{},"22":366,"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"d273b654-5bc0-45cc-b12b-fdc688f563a8","10":4,"11":"![line](https://s3.amazonaws.com/datastaxtraining/CaaS/line.png \"line\" )\n# Example READ operation\n\n### In this section, you will do the following things:\n- #### Review the code that queries the `user_credentials` table\n- #### Execute a stand-alone program to query the `user_credentials` table\n\n
\n#### Step 1: Open the example `SELECT` code file.\n\nJava - click here.
\n> Open the file in Theia and inspect the code (`~/workspace/crud-java/src/main/java/SelectRows.java`).\n> ![JavaSelectPath](https://s3.amazonaws.com/datastaxtraining/CaaS/JavaSelectPath.png \"JavaSelectPath\" )\n \n\n\nNode.js - click here.
\n> Open the file in Theia and inspect the code (`~/workspace/crud-node-js/select_rows.js`).\n> ![NodeSelectPath](https://s3.amazonaws.com/datastaxtraining/CaaS/NodeSelectPath.png \"NodeSelectPath\" )\n \n\n\nPython - click here.
\n> Open the file in Theia and inspect the code (`~/workspace/crud-python/select_rows.py`).\n> ![PythonSelectPath](https://s3.amazonaws.com/datastaxtraining/CaaS/PythonSelectPath.png \"PythonSelectPath\" )\n \n\n","12":"markdown","13":{"1":"ae45fcad-ceb0-4b64-a238-959bf8564004","10":{"9":"\nExample READ operation
\nIn this section, you will do the following things:
\n\nReview the code that queries the user_credentials
table
\n \nExecute a stand-alone program to query the user_credentials
table
\n \n
\n
\nStep 1: Open the example SELECT
code file.
\n\n
Java - click here.
\nOpen the file in Theia and inspect the code (~/workspace/crud-java/src/main/java/SelectRows.java
).\n
\n
\n
\n\n
Node.js - click here.
\nOpen the file in Theia and inspect the code (~/workspace/crud-node-js/select_rows.js
).\n
\n
\n
\n\n
Python - click here.
\nOpen the file in Theia and inspect the code (~/workspace/crud-python/select_rows.py
).\n
\n
\n
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.ONE"},{"1":"871232da-5c10-479d-86ab-4bd24ee4914a","10":4,"11":"\n#### Step 2: Review the example `SELECT` program.\n#### This code reads the row we inserted in the previous section. Notice that the outline of the code looks like the `INSERT` code example:\n- #### Create a connection\n- #### Perform the CQL\n- #### Close the connection\n\n\nJava - click here.
\n> Notice that the code creates a connection to the database just like in the `INSERT` example above.\n```\ntry (DseSession session = DseSession.builder().withCloudSecureConnectBundle(DBConnection.getConnectionPath())\n .withAuthCredentials(DBConnection.getUsername(), DBConnection.getPassword())\n .build()) {\n```\n> Next, the code performs the query - once again, it's easy to pick out the CQL.\n> You can also see the argument value substitution:\n```\nResultSet results = session.execute(\n SimpleStatement.builder( \"SELECT * FROM killrvideo.user_credentials WHERE email = ?\")\n .addPositionalValues(\"cv@datastax.com\")\n .build());\n```\n> Finally, the code prints the results.\n> Since we know we are only getting a single row, the code uses `results.one()`.\n> Then, the code uses the `row.getString()` and `row.getUuid()` methods to retrieve the various column values from the row.\n> Note that there are other methods for various data types such as `getInt()`, `getFloat()`, etc.\n```\nSystem.out.println(\"***************************************************************************************\");\nif (row == null) {\n System.out.println(\"No row selected\");\n} else {\n System.out.format(\"%s %s %s\\n\",\n row.getString(\"email\"),\n row.getString(\"password\"),\n row.getUuid(\"userid\"));\n}\nSystem.out.println(\"***************************************************************************************\");\n```\n \n\n\nNode.js - click here.
\n> Notice that the code creates a connection to the database just like in the `INSERT` example above:\n```\nconst connection = require('./db_connection')\n```\n> Then, the program uses the connection to execute the CQL `SELECT` statement.\nNotice the use of argument value substitution.\n```\nconnection.client\n.execute('SELECT * FROM killrvideo.user_credentials WHERE email = ?',\n['cv@datastax.com'])\n```\n> Finally, the program either logs the results of the query, or handles any exceptions.\n> In either case, notice that the program is careful to close the connection.\n```\n.then(function(result){\n result.rows.forEach(row => {\n console.log(row)\n })\n connection.client.shutdown()\n})\n.catch(function(error){\n console.log(error.message)\n connection.client.shutdown()\n});\n```\n \n\n\nPython - click here.
\n> Notice that the program creates a connection to the database just like in the `INSERT` example above.\n```\nconnection = Connection()\n```\n> Then, the program uses the connection to execute the CQL `SELECT` statement.\nNotice the use of argument value substitution:\n```\noutput = connection.session.execute(\"SELECT * FROM killrvideo.user_credentials WHERE email = %s\",\n['cv@datastax.com'])\n```\n> Next, the program prints out each resulting row (we only have one for this query).\n```\nfor row in output:\n print(row)\n```\n> Finally, the program closes the connection.\n```\nconnection.close()\n```\n \n\n","12":"markdown","13":{"1":"422b7c4e-b76b-4d9a-a970-9cb4614bb8c9","10":{"9":"Step 2: Review the example SELECT
program.
\nThis code reads the row we inserted in the previous section. Notice that the outline of the code looks like the INSERT
code example:
\n\nCreate a connection
\n \nPerform the CQL
\n \nClose the connection
\n \n
\n\n
Java - click here.
\nNotice that the code creates a connection to the database just like in the INSERT
example above.
\ntry (DseSession session = DseSession.builder().withCloudSecureConnectBundle(DBConnection.getConnectionPath())\n .withAuthCredentials(DBConnection.getUsername(), DBConnection.getPassword())\n .build()) {\n
\nNext, the code performs the query - once again, it's easy to pick out the CQL.\n
You can also see the argument value substitution:
\nResultSet results = session.execute(\n SimpleStatement.builder( \"SELECT * FROM killrvideo.user_credentials WHERE email = ?\")\n .addPositionalValues(\"cv@datastax.com\")\n .build());\n
\nFinally, the code prints the results.\n
Since we know we are only getting a single row, the code uses results.one()
.\n
Then, the code uses the row.getString()
and row.getUuid()
methods to retrieve the various column values from the row.\n
Note that there are other methods for various data types such as getInt()
, getFloat()
, etc.
\nSystem.out.println(\"***************************************************************************************\");\nif (row == null) {\n System.out.println(\"No row selected\");\n} else {\n System.out.format(\"%s %s %s\\n\",\n row.getString(\"email\"),\n row.getString(\"password\"),\n row.getUuid(\"userid\"));\n}\nSystem.out.println(\"***************************************************************************************\");\n
\n\n
\n\n
Node.js - click here.
\nNotice that the code creates a connection to the database just like in the INSERT
example above:
\nconst connection = require('./db_connection')\n
\nThen, the program uses the connection to execute the CQL SELECT
statement.\n
Notice the use of argument value substitution.
\nconnection.client\n.execute('SELECT * FROM killrvideo.user_credentials WHERE email = ?',\n['cv@datastax.com'])\n
\nFinally, the program either logs the results of the query, or handles any exceptions.\n
In either case, notice that the program is careful to close the connection.
\n.then(function(result){\n result.rows.forEach(row => {\n console.log(row)\n })\n connection.client.shutdown()\n})\n.catch(function(error){\n console.log(error.message)\n connection.client.shutdown()\n});\n
\n\n
\n\n
Python - click here.
\nNotice that the program creates a connection to the database just like in the INSERT
example above.
\nconnection = Connection()\n
\nThen, the program uses the connection to execute the CQL SELECT
statement.\n
Notice the use of argument value substitution:
\noutput = connection.session.execute(\"SELECT * FROM killrvideo.user_credentials WHERE email = %s\",\n['cv@datastax.com'])\n
\nNext, the program prints out each resulting row (we only have one for this query).
\nfor row in output:\n print(row)\n
\nFinally, the program closes the connection.
\nconnection.close()\n
\n\n
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"f023ad0f-6b7e-4478-8e48-4c4f62015a05","10":4,"11":"\n#### Step 3: Run the code.\n\n\nJava - click here.
\n> Under the _Terminal_ dropdown menu, click _Run Task..._ and select _Java-Select_.\n> ![JavaSelectCommand](https://s3.amazonaws.com/datastaxtraining/CaaS/JavaSelectCommand.png \"JavaSelectCommand\" )\n> You'll find your results in a tab near the bottom of Theia.\n```\n***************************************************************************************\ncv@datastax.com 3@$tC0@$tC@ss@ndr@ 55555555-5555-5555-5555-555555555555\n***************************************************************************************\n```\n \n\n\nNode.js - click here.
\n> Under the _Terminal_ dropdown menu, click _Run Task..._ and select _Node-Select_.\n> ![NodeSelectCommand](https://s3.amazonaws.com/datastaxtraining/CaaS/NodeSelectCommand.png \"NodeSelectCommand\" )\n> You'll find your results in a tab near the bottom of Theia.\n```\nRow {\n email: 'cv@datastax.com',\n password: '3@$tC0@$tC@ss@ndr@',\n userid: Uuid: 55555555-5555-5555-5555-555555555555 }\n```\n \n\n\nPython - click here.
\n> Under the _Terminal_ dropdown menu, click _Run Task..._ and select _Python-Select_.\n> ![PythonSelectCommand](https://s3.amazonaws.com/datastaxtraining/CaaS/PythonSelectCommand.png \"PythonSelectCommand\" )\n> You'll find your results in a tab near the bottom of Theia.\n```\nRow(email='cv@datastax.com', password='3@$tC0@$tC@ss@ndr@', userid=UUID('55555555-5555-5555-5555-555555555555'))\n```\n ","12":"markdown","13":{"1":"43b6219b-5b43-4587-85d3-9b87213e7c93","10":{"9":"Step 3: Run the code.
\n\n
Java - click here.
\nUnder the Terminal dropdown menu, click Run Task… and select Java-Select.\n
\n
You'll find your results in a tab near the bottom of Theia.\n***************************************************************************************\ncv@datastax.com 3@$tC0@$tC@ss@ndr@ 55555555-5555-5555-5555-555555555555\n***************************************************************************************\n
\n\n
\n\n
Node.js - click here.
\nUnder the Terminal dropdown menu, click Run Task… and select Node-Select.\n
\n
You'll find your results in a tab near the bottom of Theia.\nRow {\n email: 'cv@datastax.com',\n password: '3@$tC0@$tC@ss@ndr@',\n userid: Uuid: 55555555-5555-5555-5555-555555555555 }\n
\n\n
\n\n
Python - click here.
\nUnder the Terminal dropdown menu, click Run Task… and select Python-Select.\n
\n
You'll find your results in a tab near the bottom of Theia.\nRow(email='cv@datastax.com', password='3@$tC0@$tC@ss@ndr@', userid=UUID('55555555-5555-5555-5555-555555555555'))\n
\n\n
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"ccef674e-e207-4fad-abbd-389bb16236b1","10":4,"11":"![line](https://s3.amazonaws.com/datastaxtraining/CaaS/line.png \"line\" )\n# Example UPDATE operation\n\n### In this section, you will do the following things:\n- #### Review the example code that updates a row in the `user_credentials` table\n- #### Run this example code and verify the results\n\n
\n#### Step 1: Open the example code file.\n\nJava - click here.
\n> Open the file in Theia and inspect the code (`~/workspace/crud-java/src/main/java/Update.java`).\n> ![JavaUpdatePath](https://s3.amazonaws.com/datastaxtraining/CaaS/JavaUpdatePath.png \"JavaUpdatePath\" )\n \n\n\nNode.js - click here.
\n> Open the file in Theia and inspect the code (`~/workspace/crud-node-js/update.js`).\n> ![NodeUpdatePath](https://s3.amazonaws.com/datastaxtraining/CaaS/NodeUpdatePath.png \"NodeUpdatePath\" )\n \n\n\nPython - click here.
\n> Open the file in Theia and inspect the code (`~/workspace/crud-python/update.py`).\n> ![PythonUpdatePath](https://s3.amazonaws.com/datastaxtraining/CaaS/PythonUpdatePath.png \"PythonUpdatePath\" )\n \n\n
\n#### Notice that this code updates Cristina's password by (this should sound familiar):\n- #### Creating a connection to the database\n- #### Performing the CQL statement\n- #### Closing the connection\n\n","12":"markdown","13":{"1":"01c01362-20d0-4044-a933-09edde02804d","10":{"9":"\nExample UPDATE operation
\nIn this section, you will do the following things:
\n\nReview the example code that updates a row in the user_credentials
table
\n \nRun this example code and verify the results
\n \n
\n
\nStep 1: Open the example code file.
\n\n
Java - click here.
\nOpen the file in Theia and inspect the code (~/workspace/crud-java/src/main/java/Update.java
).\n
\n
\n
\n\n
Node.js - click here.
\nOpen the file in Theia and inspect the code (~/workspace/crud-node-js/update.js
).\n
\n
\n
\n\n
Python - click here.
\nOpen the file in Theia and inspect the code (~/workspace/crud-python/update.py
).\n
\n
\n
\n
\nNotice that this code updates Cristina's password by (this should sound familiar):
\n\nCreating a connection to the database
\n \nPerforming the CQL statement
\n \nClosing the connection
\n \n
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.ONE"},{"1":"b3efe068-bdbb-467c-803e-ea76fceb6297","10":4,"11":"#### Step 2: Review the code.\n\n\nJava - click here.
\n> Once again, the program creates a connection to the database.\n```\ntry (DseSession session = DseSession.builder().withCloudSecureConnectBundle(DBConnection.getConnectionPath())\n .withAuthCredentials(DBConnection.getUsername(), DBConnection.getPassword())\n .build()) {\n```\n> Then, the code performs the `UPDATE`.\n> You can see the CQL and the arguments.\n```\nsession.execute(\n SimpleStatement.builder( \"UPDATE killrvideo.user_credentials SET password = ? WHERE email = ?\")\n .addPositionalValues(\"Cr1st1n@sN3wP@ssW0rd\", \"cv@datastax.com\")\n .build());\n```\n \n\n\nNode.js - click here.
\n> Again, the program creates a connection to the database.\n```\nconst connection = require('./db_connection')\n```\n> Then, the program uses the connection to execute the CQL `UPDATE` statement.\nNotice the use of argument value substitution.\n```\nconnection.client.execute(\n 'UPDATE killrvideo.user_credentials SET password = ? WHERE email = ?',\n ['Cr1st1n@sN3wP@ssW0rd', 'cv@datastax.com'],\n { prepare : true }\n)\n```\n> Finally, the program either prints _Success_, or handles any exceptions and closes the connection.\n```\n.then(function (result){\n console.log('Success')\n connection.client.shutdown()\n})\n.catch(function (error){\n console.log(error.message)\n connection.client.shutdown()\n});\n```\n \n\n\nPython - click here.
\n> Again, the program creates a connection to the database.\n```\nconnection = Connection()\n```\n> Then, the program uses the connection to execute the CQL `UPDATE` statement.\n```\nconnection.session.execute(\n \"UPDATE killrvideo.user_credentials SET password = %s WHERE email = %s\",\n ['Cr1st1n@sN3wP@ssW0rd', 'cv@datastax.com']\n)\n```\n> Finally, the program prints the result and closes the connection.\n```\nexcept:\n print('Failure')\nelse:\n print('Success')\nfinally:\n connection.close()\n```\n \n\n","12":"markdown","13":{"1":"8f674696-74c7-484a-9f9d-75841c291823","10":{"9":"Step 2: Review the code.
\n\n
Java - click here.
\nOnce again, the program creates a connection to the database.
\ntry (DseSession session = DseSession.builder().withCloudSecureConnectBundle(DBConnection.getConnectionPath())\n .withAuthCredentials(DBConnection.getUsername(), DBConnection.getPassword())\n .build()) {\n
\nThen, the code performs the UPDATE
.\n
You can see the CQL and the arguments.
\nsession.execute(\n SimpleStatement.builder( \"UPDATE killrvideo.user_credentials SET password = ? WHERE email = ?\")\n .addPositionalValues(\"Cr1st1n@sN3wP@ssW0rd\", \"cv@datastax.com\")\n .build());\n
\n\n
\n\n
Node.js - click here.
\nAgain, the program creates a connection to the database.
\nconst connection = require('./db_connection')\n
\nThen, the program uses the connection to execute the CQL UPDATE
statement.\n
Notice the use of argument value substitution.
\nconnection.client.execute(\n 'UPDATE killrvideo.user_credentials SET password = ? WHERE email = ?',\n ['Cr1st1n@sN3wP@ssW0rd', 'cv@datastax.com'],\n { prepare : true }\n)\n
\nFinally, the program either prints Success, or handles any exceptions and closes the connection.
\n.then(function (result){\n console.log('Success')\n connection.client.shutdown()\n})\n.catch(function (error){\n console.log(error.message)\n connection.client.shutdown()\n});\n
\n\n
\n\n
Python - click here.
\nAgain, the program creates a connection to the database.
\nconnection = Connection()\n
\nThen, the program uses the connection to execute the CQL UPDATE
statement.
\nconnection.session.execute(\n \"UPDATE killrvideo.user_credentials SET password = %s WHERE email = %s\",\n ['Cr1st1n@sN3wP@ssW0rd', 'cv@datastax.com']\n)\n
\nFinally, the program prints the result and closes the connection.
\nexcept:\n print('Failure')\nelse:\n print('Success')\nfinally:\n connection.close()\n
\n\n
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"bf0fe6a7-8304-4a86-a228-d71072c93246","10":4,"11":"#### Step 3: Run the code.\n\n\nJava - click here.
\n> Under the _Terminal_ dropdown menu, click _Run Task..._ and select _Java-Update_.\n> ![JavaUpdateCommand](https://s3.amazonaws.com/datastaxtraining/CaaS/JavaUpdateCommand.png \"JavaUpdateCommand\" )\n \n\n\nNode.js - click here.
\n> Under the _Terminal_ dropdown menu, click _Run Task..._ and select _Node-Update_.\n> ![NodeUpdateCommand](https://s3.amazonaws.com/datastaxtraining/CaaS/NodeUpdateCommand.png \"NodeUpdateCommand\" )\n \n\n\nPython - click here.
\n> Under the _Terminal_ dropdown menu, click _Run Task..._ and select _Python-Update_.\n> ![PythonUpdateCommand](https://s3.amazonaws.com/datastaxtraining/CaaS/PythonUpdateCommand.png \"PythonUpdateCommand\" )\n \n","12":"markdown","13":{"1":"3753f6bc-597e-494b-ba4a-c77cbe305f0a","10":{"9":"Step 3: Run the code.
\n\n
Java - click here.
\nUnder the Terminal dropdown menu, click Run Task… and select Java-Update.\n
\n
\n
\n\n
Node.js - click here.
\nUnder the Terminal dropdown menu, click Run Task… and select Node-Update.\n
\n
\n
\n\n
Python - click here.
\nUnder the Terminal dropdown menu, click Run Task… and select Python-Update.\n
\n
\n
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"83439cff-245f-47dd-86a1-bb89500a044c","10":4,"11":"\n#### Step 4: Check out the results of the `UPDATE` by executing the following cell.\n","12":"markdown","13":{"1":"2ae654ba-da72-438a-bf2b-de2fef1a5408","10":{"9":"Step 4: Check out the results of the UPDATE
by executing the following cell.
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"cf0215c0-b673-4f1b-b7c8-60cd29b7f11a","11":"// Execute this cell to see the results of the updated row:\n// Remember, to execute the cell, click in the cell and press SHIFT+ENTER (or just click on the Run button in the top-right corner of the cell)\nSELECT * FROM killrvideo.user_credentials WHERE email = 'cv@datastax.com';","12":"cql","16":true,"17":false,"18":{},"22":12,"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"b3545810-968c-45c0-a387-9ec10d3e024e","10":4,"11":"![line](https://s3.amazonaws.com/datastaxtraining/CaaS/line.png \"line\" )\n# Example DELETE operation\n\n### In this section, you will do the following things:\n- #### Review the example code that deletes a row from `user_credentials`\n- #### Run the example code and verify the results\n\n
\n#### Step 1: Open the example code file.\n\nJava - click here.
\n> Click on the example code file (`~/workspace/crud-java/src/main/java/Delete.java`).\n> ![JavaDeletePath](https://s3.amazonaws.com/datastaxtraining/CaaS/JavaDeletePath.png \"JavaDeletePath\" )\n \n\n\nNode.js - click here.
\n> Click on the example code file (`~/workspace/crud-node-js/delete.js`).\n> ![NodeDeletePath](https://s3.amazonaws.com/datastaxtraining/CaaS/NodeDeletePath.png \"NodeDeletePath\" )\n \n\n\nPython - click here.
\n> Click on the example code file (`~/workspace/crud-python/delete.py`).\n> ![PythonDeletePath](https://s3.amazonaws.com/datastaxtraining/CaaS/PythonDeletePath.png \"PythonDeletePath\" )\n \n","12":"markdown","13":{"1":"4fcc201e-8360-4a10-b5bf-5b6010595f49","10":{"9":"\nExample DELETE operation
\nIn this section, you will do the following things:
\n\nReview the example code that deletes a row from user_credentials
\n \nRun the example code and verify the results
\n \n
\n
\nStep 1: Open the example code file.
\n\n
Java - click here.
\nClick on the example code file (~/workspace/crud-java/src/main/java/Delete.java
).\n
\n
\n
\n\n
Node.js - click here.
\nClick on the example code file (~/workspace/crud-node-js/delete.js
).\n
\n
\n
\n\n
Python - click here.
\nClick on the example code file (~/workspace/crud-python/delete.py
).\n
\n
\n
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.ONE"},{"1":"19f439c7-e7a7-4dfb-809e-8e5952b75881","10":4,"11":"#### Step 2: Review the code.\n\n\nJava - click here.
\n> Again, the program creates a connection to the database.\n```\ntry (DseSession session = DseSession.builder().withCloudSecureConnectBundle(DBConnection.getConnectionPath())\n .withAuthCredentials(DBConnection.getUsername(), DBConnection.getPassword())\n .build()) {\n```\n> Then, the code performs the `DELETE`.\n> This is the CQL and the arguments.\n```\nsession.execute(\n SimpleStatement.builder( \"DELETE FROM killrvideo.user_credentials WHERE email = ?\")\n .addPositionalValues(\"cv@datastax.com\")\n .build());\n```\n \n\n\nNode.js - click here.
\n> Again, the program creates a connection to the database.\n```\nconst connection = require('./db_connection')\n```\n> Then, the program uses the connection to execute the CQL `DELETE` statement.\n```\nconnection.client.execute(\n 'DELETE FROM killrvideo.user_credentials WHERE email = ?',\n ['cv@datastax.com']\n)\n```\n> Finally, the program either prints _Success_, or handles any exceptions and closes the connection.\n```\n.then(function (result){\n console.log('Success')\n connection.client.shutdown()\n})\n.catch(function (error){\n console.log(error.message)\n connection.client.shutdown()\n});\n```\n \n\n\nPython - click here.
\n> Again, the program creates a connection to the database.\n```\nconnection = Connection()\n```\n> Then, the program uses the connection to execute the CQL `DELETE` statement.\n```\noutput = connection.session.execute(\n \"DELETE FROM killrvideo.user_credentials WHERE email = %s\",\n ['cv@datastax.com']\n)\n```\n> Finally, the program prints the result and closes the connection.\n```\nexcept:\n print('Failure')\nelse:\n print('Success')\nfinally:\n connection.close()\n```\n \n","12":"markdown","13":{"1":"9b019df4-085a-448a-aa8d-e22167cedb59","10":{"9":"Step 2: Review the code.
\n\n
Java - click here.
\nAgain, the program creates a connection to the database.
\ntry (DseSession session = DseSession.builder().withCloudSecureConnectBundle(DBConnection.getConnectionPath())\n .withAuthCredentials(DBConnection.getUsername(), DBConnection.getPassword())\n .build()) {\n
\nThen, the code performs the DELETE
.\n
This is the CQL and the arguments.
\nsession.execute(\n SimpleStatement.builder( \"DELETE FROM killrvideo.user_credentials WHERE email = ?\")\n .addPositionalValues(\"cv@datastax.com\")\n .build());\n
\n\n
\n\n
Node.js - click here.
\nAgain, the program creates a connection to the database.
\nconst connection = require('./db_connection')\n
\nThen, the program uses the connection to execute the CQL DELETE
statement.
\nconnection.client.execute(\n 'DELETE FROM killrvideo.user_credentials WHERE email = ?',\n ['cv@datastax.com']\n)\n
\nFinally, the program either prints Success, or handles any exceptions and closes the connection.
\n.then(function (result){\n console.log('Success')\n connection.client.shutdown()\n})\n.catch(function (error){\n console.log(error.message)\n connection.client.shutdown()\n});\n
\n\n
\n\n
Python - click here.
\nAgain, the program creates a connection to the database.
\nconnection = Connection()\n
\nThen, the program uses the connection to execute the CQL DELETE
statement.
\noutput = connection.session.execute(\n \"DELETE FROM killrvideo.user_credentials WHERE email = %s\",\n ['cv@datastax.com']\n)\n
\nFinally, the program prints the result and closes the connection.
\nexcept:\n print('Failure')\nelse:\n print('Success')\nfinally:\n connection.close()\n
\n\n
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"ba2aac96-5c44-4f03-959f-fc259f202cbd","10":4,"11":"#### Step 3: Run the code.\n\n\nJava - click here.
\n> Under the _Terminal_ dropdown menu, click _Run Task..._ and select _Java-Delete_.\n> ![JavaDeleteCommand](https://s3.amazonaws.com/datastaxtraining/CaaS/JavaDeleteCommand.png \"JavaDeleteCommand\" )\n \n\n\nNode.js - click here.
\n> Under the _Terminal_ dropdown menu, click _Run Task..._ and select _Node-Delete_.\n> ![NodeDeleteCommand](https://s3.amazonaws.com/datastaxtraining/CaaS/NodeDeleteCommand.png \"NodeDeleteCommand\" )\n \n\n\nPython - click here.
\n> Under the _Terminal_ dropdown menu, click _Run Task..._ and select _Python-Delete_.\n> ![PythonDeleteCommand](https://s3.amazonaws.com/datastaxtraining/CaaS/PythonDeleteCommand.png \"PythonDeleteCommand\" )\n \n","12":"markdown","13":{"1":"b57cb3bf-d233-4dcb-9eef-f6aa4fad1891","10":{"9":"Step 3: Run the code.
\n\n
Java - click here.
\nUnder the Terminal dropdown menu, click Run Task… and select Java-Delete.\n
\n
\n
\n\n
Node.js - click here.
\nUnder the Terminal dropdown menu, click Run Task… and select Node-Delete.\n
\n
\n
\n\n
Python - click here.
\nUnder the Terminal dropdown menu, click Run Task… and select Python-Delete.\n
\n
\n
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"3c26f9e1-dd40-4829-889c-71df68bea1ee","10":4,"11":"#### Step 4: Verify the results of running the code by executing the following cell.\n","12":"markdown","13":{"1":"dddefea7-c03f-423a-8bf1-213917335661","10":{"9":"Step 4: Verify the results of running the code by executing the following cell.
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"834fb0f2-97fb-4233-b2e9-126893cbf6fd","11":"// Execute this cell to see the contents of the user_credentials table after the DELETE\n// Remember, to execute the cell, click in the cell and press SHIFT+ENTER (or just click on the Run button in the top-right corner of the cell)\nSELECT * FROM killrvideo.user_credentials;","12":"cql","16":true,"17":false,"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"9ea82ecb-bd20-4d76-a914-5168c7e05bf4","10":4,"11":"![line](https://s3.amazonaws.com/datastaxtraining/CaaS/line.png \"line\" )\n# Congratulations!!!!\n\n#### If you have made it to the end of this notebook successfully, your KillrVideo website is fully functional!\n- #### Even better, you have learned the skills you need to use Apache Cassandra\n- #### Of course, there is still a lot that we haven't covered, but you can learn more at DataStax Academy
\n\n#### Throw a KillrVideo CRUD party!\n\n\n\nDon't want to throw the party by yourself? Click here, and we will party with you.
\n\n ","12":"markdown","13":{"1":"997b661d-e6e9-4c3a-b0b6-7432a15b05ba","10":{"9":"\nCongratulations!!!!
\nIf you have made it to the end of this notebook successfully, your KillrVideo website is fully functional!
\n\nThrow a KillrVideo CRUD party!
\n\n
Don't want to throw the party by yourself? Click here, and we will party with you.
\n\n\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.ONE"},{"1":"18cd5147-a3d0-456d-8b79-2959f75ea2ef","10":4,"11":"![line](https://s3.amazonaws.com/datastaxtraining/CaaS/line.png \"line\" )\n# Bonus Challenge: Create your own `INSERT` code\n### If you got done early and want something to do while you wait for others, here's a bonus challenge\n\n### In this section, you will do the following things:\n- #### Create code to insert a user into the `users` table\n\n\n
\n#### Since this is a bonus exercise, we won't hold your hand. If you need help, refer back to the previous exercise as a pattern.\n\n#### Step 1: Back-up your CRUD projects.\n- #### Open a terminal window in Theia\n- #### In the terminal window execute the following command `tar -cvf crud.tar crud-*`. This will create a file named `crud.tar` that is a back-up copy of all your CRUD projects.\n- #### If you ever need to restore your CRUD projects, close any open files and use the command `tar -xvf crud.tar`. This will overwrite the current files with the backed-up files.\n\n","12":"markdown","13":{"1":"da567511-3f1c-4528-8b2f-e4fd1a77d08f","10":{"9":"\nBonus Challenge: Create your own INSERT
code
\nIf you got done early and want something to do while you wait for others, here's a bonus challenge
\nIn this section, you will do the following things:
\n\nCreate code to insert a user into the users
table
\n \n
\n
\nSince this is a bonus exercise, we won't hold your hand. If you need help, refer back to the previous exercise as a pattern.
\nStep 1: Back-up your CRUD projects.
\n\nOpen a terminal window in Theia
\n \nIn the terminal window execute the following command tar -cvf crud.tar crud-*
. This will create a file named crud.tar
that is a back-up copy of all your CRUD projects.
\n \nIf you ever need to restore your CRUD projects, close any open files and use the command tar -xvf crud.tar
. This will overwrite the current files with the backed-up files.
\n \n
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"27020d00-37ff-4ece-afd5-a37df993463d","10":4,"11":"#### Step 2: Modify the `INSERT` CRUD operation file to insert into the `users` table.\n- #### In the CRUD project for the language of your choice, open the `INSERT` CRUD file.\n- #### Modify the code to insert the following user into the `users` table.\n\n\n \n \n First Name | \n Last Name | \n Email | \n userid | \n
\n \n Cristina | \n Veale | \n cv@datastax.com | \n 55555555-5555-5555-5555-555555555555 | \n
\n
\n
\n\n- #### Here is the `users` table definition:\n```\nCREATE TABLE IF NOT EXISTS users (\n userid uuid,\n firstname text,\n lastname text,\n email text,\n created_date timestamp,\n PRIMARY KEY (userid)\n);\n```\n- #### If you do not know how to handle the `created_date` column value, you can just omit that column in the insert (remember, Cassandra supports sparse columns). We will show you how to create dates later.\n\n\n\nJava Solution - click here.
\n```\nimport com.datastax.dse.driver.api.core.DseSession;\nimport com.datastax.oss.driver.api.core.cql.*;\nimport java.time.Instant;\nimport java.util.UUID;\n\npublic class Insert {\n \n public static void main(String[] args) {\n\n try (DseSession session = DseSession.builder()\n .withCloudSecureConnectBundle(DBConnection.getConnectionPath())\n .withAuthCredentials(DBConnection.getUsername(), DBConnection.getPassword())\n .build()) {\n\n session.execute(\n SimpleStatement.builder( \"INSERT INTO killrvideo.users (userid, firstname, lastname, email, created_date) VALUES (?,?,?,?,?)\")\n .addPositionalValues(UUID.fromString(\"55555555-5555-5555-5555-555555555555\"), \"Cristina\", \"Veale\", \"cv@datastax.com\", Instant.now())\n .build());\n System.out.println(\"Successful Insert\");\n }\n catch(Throwable t) {\n System.out.println(\"Failed Insert\");\n t.printStackTrace();\n }\n }\n}\n```\n \n\n\nNode.js Solution - click here.
\n```\nconst connection = require('./db_connection')\nconst Uuid = require('cassandra-driver').types.Uuid;\n\n// this is an insert statement in nodejs\nlet createdDate = new Date(Date.now());\nconst insert = 'INSERT INTO killrvideo.users (userid, firstname, lastname, email, created_date) VALUES (?,?,?,?,?)';\nconst params = [Uuid.fromString('55555555-5555-5555-5555-555555555555'), 'Cristina', 'Veale', 'cv@datastax.com', createdDate];\nconnection.client.execute(insert, params)\n.then(function (result){\n console.log('Success')\n connection.client.shutdown()\n})\n.catch(function (error){\n console.log(error.message)\n connection.client.shutdown()\n});\n```\n \n\n\nPython Solution - click here.
\n```\n#!/usr/bin/env python3\nfrom datetime import datetime\nfrom db_connection import Connection\nimport uuid\n\n# this is a insert statement in python\ntry:\n connection = Connection()\n output = connection.session.execute(\n \"INSERT INTO killrvideo.users (userid, firstname, lastname, email, created_date) VALUES (%s, %s, %s, %s, %s)\", \n (uuid.UUID('{55555555-5555-5555-5555-555555555555}'), 'Cristina', 'Veale', 'cv@datastax.com', datetime.now())\n )\nexcept:\n print('Failure')\n print(\"Unexpected error:\", sys.exc_info()[0])\nelse:\n print('Success')\nfinally:\n connection.close()\n```\n \n","12":"markdown","13":{"1":"2bc56ffe-d117-431c-bfd7-a5656ccfadcb","10":{"9":"Step 2: Modify the INSERT
CRUD operation file to insert into the users
table.
\n\nIn the CRUD project for the language of your choice, open the INSERT
CRUD file.
\n \nModify the code to insert the following user into the users
table.
\n \n
\n\n \n \n First Name | \n Last Name | \n Email | \n userid | \n
\n \n Cristina | \n Veale | \n cv@datastax.com | \n 55555555-5555-5555-5555-555555555555 | \n
\n
\n
\n\nHere is the users
table definition:
\nCREATE TABLE IF NOT EXISTS users (\nuserid uuid,\nfirstname text,\nlastname text,\nemail text,\ncreated_date timestamp,\nPRIMARY KEY (userid)\n);\n
\n \nIf you do not know how to handle the created_date
column value, you can just omit that column in the insert (remember, Cassandra supports sparse columns). We will show you how to create dates later.
\n \n
\n\n
Java Solution - click here.
\nimport com.datastax.dse.driver.api.core.DseSession;\nimport com.datastax.oss.driver.api.core.cql.*;\nimport java.time.Instant;\nimport java.util.UUID;\n\npublic class Insert {\n\n public static void main(String[] args) {\n\n try (DseSession session = DseSession.builder()\n .withCloudSecureConnectBundle(DBConnection.getConnectionPath())\n .withAuthCredentials(DBConnection.getUsername(), DBConnection.getPassword())\n .build()) {\n\n session.execute(\n SimpleStatement.builder( \"INSERT INTO killrvideo.users (userid, firstname, lastname, email, created_date) VALUES (?,?,?,?,?)\")\n .addPositionalValues(UUID.fromString(\"55555555-5555-5555-5555-555555555555\"), \"Cristina\", \"Veale\", \"cv@datastax.com\", Instant.now())\n .build());\n System.out.println(\"Successful Insert\");\n }\n catch(Throwable t) {\n System.out.println(\"Failed Insert\");\n t.printStackTrace();\n }\n }\n}\n
\n\n\n
Node.js Solution - click here.
\nconst connection = require('./db_connection')\nconst Uuid = require('cassandra-driver').types.Uuid;\n\n// this is an insert statement in nodejs\nlet createdDate = new Date(Date.now());\nconst insert = 'INSERT INTO killrvideo.users (userid, firstname, lastname, email, created_date) VALUES (?,?,?,?,?)';\nconst params = [Uuid.fromString('55555555-5555-5555-5555-555555555555'), 'Cristina', 'Veale', 'cv@datastax.com', createdDate];\nconnection.client.execute(insert, params)\n.then(function (result){\n console.log('Success')\n connection.client.shutdown()\n})\n.catch(function (error){\n console.log(error.message)\n connection.client.shutdown()\n});\n
\n\n\n
Python Solution - click here.
\n#!/usr/bin/env python3\nfrom datetime import datetime\nfrom db_connection import Connection\nimport uuid\n\n# this is a insert statement in python\ntry:\n connection = Connection()\n output = connection.session.execute(\n \"INSERT INTO killrvideo.users (userid, firstname, lastname, email, created_date) VALUES (%s, %s, %s, %s, %s)\", \n (uuid.UUID('{55555555-5555-5555-5555-555555555555}'), 'Cristina', 'Veale', 'cv@datastax.com', datetime.now())\n )\nexcept:\n print('Failure')\n print(\"Unexpected error:\", sys.exc_info()[0])\nelse:\n print('Success')\nfinally:\n connection.close()\n
\n\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"c062f8df-f069-4b68-a7e1-412506b4d972","10":4,"11":"#### Step 3: Execute the following cell to see the current contents of the `users` table _before_ you insert the additional row. Verify Cristina's row is not yet in the table.","12":"markdown","13":{"1":"5d724353-9cb0-47ce-bde8-f000a1169328","10":{"9":"Step 3: Execute the following cell to see the current contents of the users
table before you insert the additional row. Verify Cristina's row is not yet in the table.
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"059d7cc4-ccab-4f16-9f0a-dda0b4d1dae0","11":"// Execute this cell to see the contents of the users table\n// Remember, to execute the cell, click in the cell and press SHIFT+ENTER (or just click on the Run button in the top-right corner of the cell)\nSELECT * FROM killrvideo.users;","12":"cql","16":true,"17":false,"18":{},"22":355,"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"2bfe3957-8f4f-428b-b8c2-ee666300a491","10":4,"11":"#### Step 4: Execute the `INSERT` code using _Run Task..._ command under the _Terminal_ menu item.\n","12":"markdown","13":{"1":"616b3c70-3394-4445-9bf0-b10785012814","10":{"9":"Step 4: Execute the INSERT
code using Run Task… command under the Terminal menu item.
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"a99d6075-1377-4d46-afc4-2ace1eb61906","10":4,"11":"#### Step 5: Execute the following cell to see the current contents of the users table _after_ you have inserted the additional row. Verify Cristina's row is now in the table.\n","12":"markdown","13":{"1":"03567374-1d3c-4620-b98b-eab487b003fb","10":{"9":"Step 5: Execute the following cell to see the current contents of the users table after you have inserted the additional row. Verify Cristina's row is now in the table.
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"dee1c88b-6d97-4277-948c-0394a8ea9a17","11":"// Execute this cell to see the contents of the users table\n// Remember, to execute the cell, click in the cell and press SHIFT+ENTER (or just click on the Run button in the top-right corner of the cell)\nSELECT * FROM killrvideo.users;","12":"cql","16":true,"17":false,"18":{},"22":396,"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"c9cebd4c-6b02-454a-b9df-278f25c01053","10":4,"11":"![line](https://s3.amazonaws.com/datastaxtraining/CaaS/line.png \"line\" )\n# Bonus Challenge: Create your own `SELECT` code\n### If you got done early and want something to do while you wait for others, here's another bonus challenge\n\n### In this section, you will do the following things:\n- #### Create code to retrieve a user from the `users` table\n\n\n
\n#### Again, since this is a bonus exercise, you're on your own to figure it out. If you need help, refer back to the previous exercises as a pattern.\n
\n#### Step 1: Modify the `SELECT` CRUD operation file to retrieve a row from the `users` table.\n- #### In the CRUD project for the language of your choice, open the `SELECT` CRUD file.\n- #### Modify the code to retrieve the row from the `users` table with a `userid` of `55555555-5555-5555-5555-555555555555`.\n\n\n\nJava Solution - click here.
\n```\nimport com.datastax.dse.driver.api.core.DseSession;\nimport com.datastax.oss.driver.api.core.cql.*;\nimport java.util.UUID;\n\npublic class SelectRows {\n \n public static void main(String args[]) {\n try (DseSession session = DseSession.builder().withCloudSecureConnectBundle(DBConnection.getConnectionPath())\n .withAuthCredentials(DBConnection.getUsername(), DBConnection.getPassword())\n .build()) {\n\n ResultSet results = session.execute(\n SimpleStatement.builder( \"SELECT * FROM killrvideo.users WHERE userid = ?\")\n .addPositionalValues(UUID.fromString(\"55555555-5555-5555-5555-555555555555\"))\n .build());\n Row row = results.one();\n System.out.println(\"***************************************************************************************\");\n if (row == null) {\n System.out.println(\"No row selected\");\n } else {\n System.out.format(\"%s %s %s %s %s\\n\",\n row.getString(\"firstname\"),\n row.getString(\"lastname\"),\n row.getString(\"email\"),\n row.getInstant(\"created_date\"),\n row.getUuid(\"userid\"));\n }\n System.out.println(\"***************************************************************************************\");\n }\n catch(Throwable t) {\n System.out.println(\"Failed Select\");\n t.printStackTrace(); \n }\n }\n}\n\n```\n \n\n\nNode.js Solution - click here.
\n```\nconst connection = require('./db_connection')\nconst Uuid = require('cassandra-driver').types.Uuid;\n\n// this is a select statement in nodejs\nconnection.client\n.execute('SELECT * FROM killrvideo.users WHERE userid = ?',\n[Uuid.fromString('55555555-5555-5555-5555-555555555555')])\n.then(function(result){\n result.rows.forEach(row => {\n console.log(row)\n })\n connection.client.shutdown()\n})\n.catch(function(error){\n console.log(error.message)\n connection.client.shutdown()\n});\n```\n \n\n\nPython Solution - click here.
\n```\n#!/usr/bin/env python3\nfrom db_connection import Connection\nimport uuid\n\n# this is a select statement in python\nconnection = Connection()\noutput = connection.session.execute(\"SELECT * FROM killrvideo.users WHERE userid = %s\",\n[uuid.UUID('{55555555-5555-5555-5555-555555555555}')])\nfor row in output:\n print(row)\nconnection.close()\n```\n \n\n","12":"markdown","13":{"1":"8b401df7-3958-411c-ace3-d48dce444542","10":{"9":"\nBonus Challenge: Create your own SELECT
code
\nIf you got done early and want something to do while you wait for others, here's another bonus challenge
\nIn this section, you will do the following things:
\n\nCreate code to retrieve a user from the users
table
\n \n
\n
\nAgain, since this is a bonus exercise, you're on your own to figure it out. If you need help, refer back to the previous exercises as a pattern.
\n
\nStep 1: Modify the SELECT
CRUD operation file to retrieve a row from the users
table.
\n\nIn the CRUD project for the language of your choice, open the SELECT
CRUD file.
\n \nModify the code to retrieve the row from the users
table with a userid
of 55555555-5555-5555-5555-555555555555
.
\n \n
\n\n
Java Solution - click here.
\nimport com.datastax.dse.driver.api.core.DseSession;\nimport com.datastax.oss.driver.api.core.cql.*;\nimport java.util.UUID;\n\npublic class SelectRows {\n\n public static void main(String args[]) {\n try (DseSession session = DseSession.builder().withCloudSecureConnectBundle(DBConnection.getConnectionPath())\n .withAuthCredentials(DBConnection.getUsername(), DBConnection.getPassword())\n .build()) {\n\n ResultSet results = session.execute(\n SimpleStatement.builder( \"SELECT * FROM killrvideo.users WHERE userid = ?\")\n .addPositionalValues(UUID.fromString(\"55555555-5555-5555-5555-555555555555\"))\n .build());\n Row row = results.one();\n System.out.println(\"***************************************************************************************\");\n if (row == null) {\n System.out.println(\"No row selected\");\n } else {\n System.out.format(\"%s %s %s %s %s\\n\",\n row.getString(\"firstname\"),\n row.getString(\"lastname\"),\n row.getString(\"email\"),\n row.getInstant(\"created_date\"),\n row.getUuid(\"userid\"));\n }\n System.out.println(\"***************************************************************************************\");\n }\n catch(Throwable t) {\n System.out.println(\"Failed Select\");\n t.printStackTrace(); \n }\n }\n}\n
\n\n\n
Node.js Solution - click here.
\nconst connection = require('./db_connection')\nconst Uuid = require('cassandra-driver').types.Uuid;\n\n// this is a select statement in nodejs\nconnection.client\n.execute('SELECT * FROM killrvideo.users WHERE userid = ?',\n[Uuid.fromString('55555555-5555-5555-5555-555555555555')])\n.then(function(result){\n result.rows.forEach(row => {\n console.log(row)\n })\n connection.client.shutdown()\n})\n.catch(function(error){\n console.log(error.message)\n connection.client.shutdown()\n});\n
\n\n\n
Python Solution - click here.
\n#!/usr/bin/env python3\nfrom db_connection import Connection\nimport uuid\n\n# this is a select statement in python\nconnection = Connection()\noutput = connection.session.execute(\"SELECT * FROM killrvideo.users WHERE userid = %s\",\n[uuid.UUID('{55555555-5555-5555-5555-555555555555}')])\nfor row in output:\n print(row)\nconnection.close()\n
\n\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"5523dde3-bdda-40f9-a3db-2dce1476f60a","10":4,"11":"\n#### Step 2: Execute the `SELECT` code using _Run Task..._ command under the _Terminal_ menu item and verify your results.","12":"markdown","13":{"1":"f77eecf7-f115-4b9a-918e-23ebbf845bbd","10":{"9":"Step 2: Execute the SELECT
code using Run Task… command under the Terminal menu item and verify your results.
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"dae09d52-0e28-407a-86f1-40a59a042e85","10":4,"11":"![line](https://s3.amazonaws.com/datastaxtraining/CaaS/line.png \"line\" )\n# Bonus Challenge: Create your own `UPDATE` code\n### If you got done early and want something to do while you wait for others, here's yet another bonus challenge\n\n### In this section, you will do the following things:\n- #### Create code to update a row in the `users` table\n\n
\n#### Again, since this is a bonus exercise, you get to figure it out. If you need help, refer back to the previous exercises as a pattern.\n
\n#### Step 1: Modify the `UPDATE` CRUD operation file to change a row from the `users` table.\n- #### In the CRUD project for the language of your choice, open the `UPDATE` CRUD file.\n- #### Modify the code to change Cristina's email address in the `users` table to `cveale@datastax.com`. Remember, her `userid` is `55555555-5555-5555-5555-555555555555`.\n\n\n\nJava Solution - click here.
\n```\nimport com.datastax.dse.driver.api.core.DseSession;\nimport com.datastax.oss.driver.api.core.cql.*;\nimport java.util.UUID;\n\npublic class Update {\n \n public static void main(String[] args) {\n\n try (DseSession session = DseSession.builder().withCloudSecureConnectBundle(DBConnection.getConnectionPath())\n .withAuthCredentials(DBConnection.getUsername(), DBConnection.getPassword())\n .build()) {\n\n session.execute(\n SimpleStatement.builder( \"UPDATE killrvideo.users SET email = ? WHERE userid = ?\")\n .addPositionalValues(\"cveale@datastax.com\", UUID.fromString(\"55555555-5555-5555-5555-555555555555\"))\n .build());\n System.out.println(\"Update Succeeded\");\n }\n catch(Throwable t) {\n System.out.println(\"Failed Update\");\n t.printStackTrace();\n }\n }\n}\n```\n \n\n\nNode.js Solution - click here.
\n```\nconst connection = require('./db_connection')\nconst Uuid = require('cassandra-driver').types.Uuid;\n\n// this is a update statement in nodejs\nconnection.client.execute(\n 'UPDATE killrvideo.users SET email = ? WHERE userid = ?',\n ['cveale@datastax.com', Uuid.fromString('55555555-5555-5555-5555-555555555555')],\n { prepare : true }\n)\n.then(function (result){\n console.log('Success')\n connection.client.shutdown()\n})\n.catch(function (error){\n console.log(error.message)\n connection.client.shutdown()\n});\n```\n \n\n\nPython Solution - click here.
\n```\n#!/usr/bin/env python3\nfrom db_connection import Connection\nimport uuid\n\n# this is a update statement in python\ntry:\n connection = Connection()\n connection.session.execute(\n \"UPDATE killrvideo.users SET email = %s WHERE userid = %s\",\n ['cveale@datastax.com', uuid.UUID('{55555555-5555-5555-5555-555555555555}')]\n )\nexcept:\n print('Failure')\nelse:\n print('Success')\nfinally:\n connection.close()\n```\n \n\n","12":"markdown","13":{"1":"c895c24e-d19f-43e2-a7cb-507fb3e6f859","10":{"9":"\nBonus Challenge: Create your own UPDATE
code
\nIf you got done early and want something to do while you wait for others, here's yet another bonus challenge
\nIn this section, you will do the following things:
\n\nCreate code to update a row in the users
table
\n \n
\n
\nAgain, since this is a bonus exercise, you get to figure it out. If you need help, refer back to the previous exercises as a pattern.
\n
\nStep 1: Modify the UPDATE
CRUD operation file to change a row from the users
table.
\n\nIn the CRUD project for the language of your choice, open the UPDATE
CRUD file.
\n \nModify the code to change Cristina's email address in the users
table to cveale@datastax.com
. Remember, her userid
is 55555555-5555-5555-5555-555555555555
.
\n \n
\n\n
Java Solution - click here.
\nimport com.datastax.dse.driver.api.core.DseSession;\nimport com.datastax.oss.driver.api.core.cql.*;\nimport java.util.UUID;\n\npublic class Update {\n\n public static void main(String[] args) {\n\n try (DseSession session = DseSession.builder().withCloudSecureConnectBundle(DBConnection.getConnectionPath())\n .withAuthCredentials(DBConnection.getUsername(), DBConnection.getPassword())\n .build()) {\n\n session.execute(\n SimpleStatement.builder( \"UPDATE killrvideo.users SET email = ? WHERE userid = ?\")\n .addPositionalValues(\"cveale@datastax.com\", UUID.fromString(\"55555555-5555-5555-5555-555555555555\"))\n .build());\n System.out.println(\"Update Succeeded\");\n }\n catch(Throwable t) {\n System.out.println(\"Failed Update\");\n t.printStackTrace();\n }\n }\n}\n
\n\n\n
Node.js Solution - click here.
\nconst connection = require('./db_connection')\nconst Uuid = require('cassandra-driver').types.Uuid;\n\n// this is a update statement in nodejs\nconnection.client.execute(\n 'UPDATE killrvideo.users SET email = ? WHERE userid = ?',\n ['cveale@datastax.com', Uuid.fromString('55555555-5555-5555-5555-555555555555')],\n { prepare : true }\n)\n.then(function (result){\n console.log('Success')\n connection.client.shutdown()\n})\n.catch(function (error){\n console.log(error.message)\n connection.client.shutdown()\n});\n
\n\n\n
Python Solution - click here.
\n#!/usr/bin/env python3\nfrom db_connection import Connection\nimport uuid\n\n# this is a update statement in python\ntry:\n connection = Connection()\n connection.session.execute(\n \"UPDATE killrvideo.users SET email = %s WHERE userid = %s\",\n ['cveale@datastax.com', uuid.UUID('{55555555-5555-5555-5555-555555555555}')]\n )\nexcept:\n print('Failure')\nelse:\n print('Success')\nfinally:\n connection.close()\n
\n\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"8911ca43-a9dc-4a6c-ad1d-ef18c8d65c03","10":4,"11":"\n#### Step 2: Execute the following cell to see Cristina's row _before_ you update it.","12":"markdown","13":{"1":"8a84c677-5301-4104-9267-daa4eaa648fb","10":{"9":"Step 2: Execute the following cell to see Cristina's row before you update it.
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"60f925dc-1818-467c-a718-c76a94e59225","11":"// Execute this cell to see Cristina's row\n// Remember, to execute the cell, click in the cell and press SHIFT+ENTER (or just click on the Run button in the top-right corner of the cell)\nSELECT * FROM killrvideo.users WHERE userid = 55555555-5555-5555-5555-555555555555;","12":"cql","16":true,"17":false,"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"05cba529-20be-46da-b81f-4265b848dd38","10":4,"11":"#### Step 3: Execute the `UPDATE` code using _Run Task..._ command under the _Terminal_ menu item and verify your results.","12":"markdown","13":{"1":"6a7cd79d-6a71-4ef4-8ee9-1e232a552afd","10":{"9":"Step 3: Execute the UPDATE
code using Run Task… command under the Terminal menu item and verify your results.
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"4f2cbf30-bdad-46df-813f-e88d7d22800a","10":4,"11":"#### Step 4: Execute the following cell to see Cristina's row _after_ you have updated it.","12":"markdown","13":{"1":"183b0935-ab09-4c26-949f-dd4584e85b54","10":{"9":"Step 4: Execute the following cell to see Cristina's row after you have updated it.
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"c4a8202d-f875-4208-a1e9-0806a4169e43","11":"// Execute this cell to see Cristina's row after the update\n// Remember, to execute the cell, click in the cell and press SHIFT+ENTER (or just click on the Run button in the top-right corner of the cell)\nSELECT * FROM killrvideo.users WHERE userid = 55555555-5555-5555-5555-555555555555;","12":"cql","16":true,"17":false,"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"8ca0587b-5c9a-4ed2-a498-f4162a1593ed","10":4,"11":"![line](https://s3.amazonaws.com/datastaxtraining/CaaS/line.png \"line\" )\n# Bonus Challenge: Create your own `DELETE` code\n### If you got done early and want something to do while you wait for others, here's a final bonus challenge\n\n### In this section, you will do the following things:\n- #### Create code to delete a row from the `users` table\n\n
\n#### You know the drill. If you need help, refer back to the previous exercises as a pattern.\n
\n#### Step 1: Modify the `DELETE` CRUD operation file to delete a row from the `users` table.\n- #### In the CRUD project for the language of your choice, open the `DELETE` CRUD file.\n- #### Modify the code to delete the row with a `userid` of `55555555-5555-5555-5555-555555555555`.\n\n\n\nJava Solution - click here.
\n```\nimport com.datastax.dse.driver.api.core.DseSession;\nimport com.datastax.oss.driver.api.core.cql.*;\nimport java.util.UUID;\n\npublic class Delete {\n \n public static void main(String[] args) {\n\n try (DseSession session = DseSession.builder().withCloudSecureConnectBundle(DBConnection.getConnectionPath())\n .withAuthCredentials(DBConnection.getUsername(), DBConnection.getPassword())\n .build()) {\n\n session.execute(\n SimpleStatement.builder( \"DELETE FROM killrvideo.users WHERE userid = ?\")\n .addPositionalValues(UUID.fromString(\"55555555-5555-5555-5555-555555555555\"))\n .build());\n System.out.println(\"Successful Delete\");\n }\n catch(Throwable t) {\n System.out.println(\"Failed Delete\");\n t.printStackTrace();\n }\n }\n}\n```\n \n\n\nNode.js Solution - click here.
\n```\nconst connection = require('./db_connection')\nconst Uuid = require('cassandra-driver').types.Uuid;\n\n// this is a delete statement in nodejs\nconnection.client.execute(\n 'DELETE FROM killrvideo.users WHERE userid = ?',\n [Uuid.fromString('55555555-5555-5555-5555-555555555555')]\n)\n.then(function (result){\n console.log('Success')\n connection.client.shutdown()\n})\n.catch(function (error){\n console.log(error.message)\n connection.client.shutdown()\n});\n```\n \n\n\nPython Solution - click here.
\n```\n#!/usr/bin/env python3\nfrom db_connection import Connection\nimport uuid\n\n# this is a delete statement in python\ntry:\n connection = Connection()\n output = connection.session.execute(\n \"DELETE FROM killrvideo.users WHERE userid = %s\",\n [uuid.UUID('{55555555-5555-5555-5555-555555555555}')]\n )\nexcept:\n print('Failure')\nelse:\n print('Success')\nfinally:\n connection.close()\n```\n \n","12":"markdown","13":{"1":"a35bef10-32b9-41e1-9aed-0988519f30c0","10":{"9":"\nBonus Challenge: Create your own DELETE
code
\nIf you got done early and want something to do while you wait for others, here's a final bonus challenge
\nIn this section, you will do the following things:
\n\nCreate code to delete a row from the users
table
\n \n
\n
\nYou know the drill. If you need help, refer back to the previous exercises as a pattern.
\n
\nStep 1: Modify the DELETE
CRUD operation file to delete a row from the users
table.
\n\nIn the CRUD project for the language of your choice, open the DELETE
CRUD file.
\n \nModify the code to delete the row with a userid
of 55555555-5555-5555-5555-555555555555
.
\n \n
\n\n
Java Solution - click here.
\nimport com.datastax.dse.driver.api.core.DseSession;\nimport com.datastax.oss.driver.api.core.cql.*;\nimport java.util.UUID;\n\npublic class Delete {\n\n public static void main(String[] args) {\n\n try (DseSession session = DseSession.builder().withCloudSecureConnectBundle(DBConnection.getConnectionPath())\n .withAuthCredentials(DBConnection.getUsername(), DBConnection.getPassword())\n .build()) {\n\n session.execute(\n SimpleStatement.builder( \"DELETE FROM killrvideo.users WHERE userid = ?\")\n .addPositionalValues(UUID.fromString(\"55555555-5555-5555-5555-555555555555\"))\n .build());\n System.out.println(\"Successful Delete\");\n }\n catch(Throwable t) {\n System.out.println(\"Failed Delete\");\n t.printStackTrace();\n }\n }\n}\n
\n\n\n
Node.js Solution - click here.
\nconst connection = require('./db_connection')\nconst Uuid = require('cassandra-driver').types.Uuid;\n\n// this is a delete statement in nodejs\nconnection.client.execute(\n 'DELETE FROM killrvideo.users WHERE userid = ?',\n [Uuid.fromString('55555555-5555-5555-5555-555555555555')]\n)\n.then(function (result){\n console.log('Success')\n connection.client.shutdown()\n})\n.catch(function (error){\n console.log(error.message)\n connection.client.shutdown()\n});\n
\n\n\n
Python Solution - click here.
\n#!/usr/bin/env python3\nfrom db_connection import Connection\nimport uuid\n\n# this is a delete statement in python\ntry:\n connection = Connection()\n output = connection.session.execute(\n \"DELETE FROM killrvideo.users WHERE userid = %s\",\n [uuid.UUID('{55555555-5555-5555-5555-555555555555}')]\n )\nexcept:\n print('Failure')\nelse:\n print('Success')\nfinally:\n connection.close()\n
\n\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"d1a9801c-3388-4ffc-8b1f-cfa89f65d419","10":4,"11":"#### Step 2: Execute the `DELETE` code using _Run Task..._ command under the _Terminal_ menu item and verify your results.","12":"markdown","13":{"1":"ff980bcb-2526-4049-b9c1-7e7f4e1c79da","10":{"9":"Step 2: Execute the DELETE
code using Run Task… command under the Terminal menu item and verify your results.
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"885d56b6-89fe-46cd-9229-da321a8254a5","10":4,"11":"#### Step 3: Execute the following cell to see the entire contents of the `users` table. Verify Cristina's row is gone.","12":"markdown","13":{"1":"95f7f444-c5b4-46b4-af2a-053c0e97588a","10":{"9":"Step 3: Execute the following cell to see the entire contents of the users
table. Verify Cristina's row is gone.
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"7d54d7a4-5dfb-442c-ab1e-ed339898bdee","11":"// Execute this cell to see the contents of the users table\n// Remember, to execute the cell, click in the cell and press SHIFT+ENTER (or just click on the Run button in the top-right corner of the cell)\nSELECT * FROM killrvideo.users;","12":"cql","16":true,"17":false,"18":{},"22":369,"24":"killrvideo","25":"LOCAL.QUORUM"}],"16":{"1":{}},"17":"","19":false} code.txt 0100644 0000000 0000000 00000177221 13625045620 011275 0 ustar 00 0000000 0000000 --------------------NOTEBOOK_Notebook 3 - Coding with Cassandra--------------------
--------------------CELL_MARKDOWN_1--------------------
![CodingSplash](https://s3.amazonaws.com/datastaxtraining/CaaS/CodingSplash.png "CodingSplash" )
### **Welcome to the Coding with Cassandra notebook!**
- #### If you are familiar with CQL, accessing your Cassandra database from code is pretty intuitive!
- #### In this notebook, we'll cover accessing Cassandra from Java, Node.js and Python
- #### In the last notebook in the series, you'll use the skills you learn here to complete your instance of KillrVideo
--------------------CELL_MARKDOWN_2--------------------
![line](https://s3.amazonaws.com/datastaxtraining/CaaS/line.png "line" )
# Set Up the Notebook
### In this section, you will do the following things:
- #### Execute a CQL script to initialize the KillrVideo database for this notebook
#### Step 1: Execute the following cell to initialize this notebook. Hover over the right-hand corner of the cell and click the _Run_ button.
![line](https://s3.amazonaws.com/datastaxtraining/CaaS/ExecuteCell.png "line" )
#### **Note:** You don't see the CQL script because the code editor is hidden, but you can still run the cell.
--------------------CELL_CQL_3--------------------
// Coding with Cassandra notebook initialization script
//CREATE KEYSPACE IF NOT EXISTS killrvideo WITH REPLICATION = { 'class' : 'SimpleStrategy', 'replication_factor' : 1 };
use killrvideo;
// Remove this section after DROP bug is fixed (https://datastax.jira.com/browse/CP-3499)
// Note we create the tables and then drop them so we can recreate them.
// This assures us the tables have the correct configuration.
// If we only did the CREATE TABLE IF NOT EXISTS, we might end up with a table with the wrong columns or something...
CREATE TABLE IF NOT EXISTS user_credentials (key text, PRIMARY KEY(key));
CREATE TABLE IF NOT EXISTS users (key text, PRIMARY KEY(key));
CREATE TABLE IF NOT EXISTS videos (key text, PRIMARY KEY(key));
CREATE TABLE IF NOT EXISTS user_videos (key text, PRIMARY KEY(key));
CREATE TABLE IF NOT EXISTS latest_videos (key text, PRIMARY KEY(key));
CREATE TABLE IF NOT EXISTS video_ratings (key text, PRIMARY KEY(key));
CREATE TABLE IF NOT EXISTS video_ratings_by_user (key text, PRIMARY KEY(key));
CREATE TABLE IF NOT EXISTS video_playback_stats (key text, PRIMARY KEY(key));
CREATE TABLE IF NOT EXISTS video_recommendations (key text, PRIMARY KEY(key));
CREATE TABLE IF NOT EXISTS video_recommendations_by_video (key text, PRIMARY KEY(key));
CREATE TABLE IF NOT EXISTS videos_by_tag (key text, PRIMARY KEY(key));
CREATE TABLE IF NOT EXISTS tags_by_letter (key text, PRIMARY KEY(key));
CREATE TABLE IF NOT EXISTS comments_by_video (key text, PRIMARY KEY(key));
CREATE TABLE IF NOT EXISTS comments_by_user (key text, PRIMARY KEY(key));
// END removable section
DROP TABLE IF EXISTS user_credentials;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS videos;
DROP TABLE IF EXISTS user_videos;
DROP TABLE IF EXISTS latest_videos;
DROP TABLE IF EXISTS video_ratings;
DROP TABLE IF EXISTS video_ratings_by_user;
DROP TABLE IF EXISTS video_playback_stats;
DROP TABLE IF EXISTS video_recommendations;
DROP TABLE IF EXISTS video_recommendations_by_video;
DROP TABLE IF EXISTS videos_by_tag;
DROP TABLE IF EXISTS tags_by_letter;
DROP TABLE IF EXISTS comments_by_video;
DROP TABLE IF EXISTS comments_by_user;
// User credentials, keyed by email address so we can authenticate
CREATE TABLE IF NOT EXISTS user_credentials (
email text,
password text,
userid uuid,
PRIMARY KEY (email)
);
// Users keyed by id
CREATE TABLE IF NOT EXISTS users (
userid uuid,
firstname text,
lastname text,
email text,
created_date timestamp,
PRIMARY KEY (userid)
);
// Videos by id
CREATE TABLE IF NOT EXISTS videos (
videoid uuid,
userid uuid,
name text,
description text,
location text,
location_type int,
preview_image_location text,
tags set,
added_date timestamp,
PRIMARY KEY (videoid)
);
// One-to-many from user point of view (lookup table)
CREATE TABLE IF NOT EXISTS user_videos (
userid uuid,
added_date timestamp,
videoid uuid,
name text,
preview_image_location text,
PRIMARY KEY (userid, added_date, videoid)
) WITH CLUSTERING ORDER BY (added_date DESC, videoid ASC);
// Track latest videos, grouped by day (if we ever develop a bad hotspot from the daily grouping here, we could mitigate by
// splitting the row using an arbitrary group number, making the partition key (yyyymmdd, group_number))
CREATE TABLE IF NOT EXISTS latest_videos (
yyyymmdd text,
added_date timestamp,
videoid uuid,
userid uuid,
name text,
preview_image_location text,
PRIMARY KEY (yyyymmdd, added_date, videoid)
) WITH CLUSTERING ORDER BY (added_date DESC, videoid ASC);
// Video ratings (counter table)
CREATE TABLE IF NOT EXISTS video_ratings (
videoid uuid,
rating_counter counter,
rating_total counter,
PRIMARY KEY (videoid)
);
// Video ratings by user (to try and mitigate voting multiple times)
CREATE TABLE IF NOT EXISTS video_ratings_by_user (
videoid uuid,
userid uuid,
rating int,
PRIMARY KEY (videoid, userid)
);
// Records the number of views/playbacks of a video
CREATE TABLE IF NOT EXISTS video_playback_stats (
videoid uuid,
views counter,
PRIMARY KEY (videoid)
);
// Recommendations by user (powered by Spark), with the newest videos added to the site always first
CREATE TABLE IF NOT EXISTS video_recommendations (
userid uuid,
added_date timestamp,
videoid uuid,
rating float,
authorid uuid,
name text,
preview_image_location text,
PRIMARY KEY(userid, added_date, videoid)
) WITH CLUSTERING ORDER BY (added_date DESC, videoid ASC);
// Recommendations by video (powered by Spark)
CREATE TABLE IF NOT EXISTS video_recommendations_by_video (
videoid uuid,
userid uuid,
rating float,
added_date timestamp STATIC,
authorid uuid STATIC,
name text STATIC,
preview_image_location text STATIC,
PRIMARY KEY(videoid, userid)
);
// Index for tag keywords
CREATE TABLE IF NOT EXISTS videos_by_tag (
tag text,
videoid uuid,
added_date timestamp,
userid uuid,
name text,
preview_image_location text,
tagged_date timestamp,
PRIMARY KEY (tag, videoid)
);
// Index for tags by first letter in the tag
CREATE TABLE IF NOT EXISTS tags_by_letter (
first_letter text,
tag text,
PRIMARY KEY (first_letter, tag)
);
// Comments for a given video
CREATE TABLE IF NOT EXISTS comments_by_video (
videoid uuid,
commentid timeuuid,
userid uuid,
comment text,
PRIMARY KEY (videoid, commentid)
) WITH CLUSTERING ORDER BY (commentid DESC);
// Comments for a given user
CREATE TABLE IF NOT EXISTS comments_by_user (
userid uuid,
commentid timeuuid,
videoid uuid,
comment text,
PRIMARY KEY (userid, commentid)
) WITH CLUSTERING ORDER BY (commentid DESC);
INSERT INTO killrvideo.users (userid, created_date, firstname, lastname, email)
VALUES(11111111-1111-1111-1111-111111111111, toTimestamp(now()), 'Jeff', 'Carpenter', 'jc@datastax.com');
INSERT INTO killrvideo.user_credentials (userid, email, password)
VALUES(11111111-1111-1111-1111-111111111111, 'jc@datastax.com', 'J3ffL0v3$C@ss@ndr@');
INSERT INTO killrvideo.users (userid, created_date, firstname, lastname, email)
VALUES(22222222-2222-2222-2222-222222222222, toTimestamp(now()), 'Eric', 'Zietlow', 'ez@datastax.com');
INSERT INTO killrvideo.user_credentials (userid, email, password)
VALUES(22222222-2222-2222-2222-222222222222, 'ez@datastax.com', 'C@ss@ndr@R0ck$');
INSERT INTO killrvideo.users (userid, created_date, firstname, lastname, email)
VALUES(33333333-3333-3333-3333-333333333333, toTimestamp(now()), 'Cedrick', 'Lunven', 'cl@datastax.com');
INSERT INTO killrvideo.user_credentials (userid, email, password)
VALUES(33333333-3333-3333-3333-333333333333, 'cl@datastax.com', 'Fr@nc3L0v3$C@ss@ndr@');
INSERT INTO killrvideo.users (userid, created_date, firstname, lastname, email)
VALUES(44444444-4444-4444-4444-444444444444, toTimestamp(now()), 'David', 'Gilardi', 'dg@datastax.com');
INSERT INTO killrvideo.user_credentials (userid, email, password)
VALUES(44444444-4444-4444-4444-444444444444, 'dg@datastax.com', 'H@t$0ff2C@ss@ndr@');
//INSERT INTO killrvideo.users (userid, created_date, firstname, lastname, email)
// VALUES(55555555-5555-5555-5555-555555555555, toTimestamp(now()), 'Cristina', 'Veale', 'cv@datastax.com');
//INSERT INTO killrvideo.user_credentials (userid, email, password)
// VALUES(55555555-5555-5555-5555-555555555555, 'cv@datastax.com', '3@$tC0@$tC@ss@ndr@');
INSERT INTO killrvideo.users (userid, created_date, firstname, lastname, email)
VALUES(66666666-6666-6666-6666-666666666666, toTimestamp(now()), 'Adron', 'Hall', 'ah@datastax.com');
INSERT INTO killrvideo.user_credentials (userid, email, password)
VALUES(66666666-6666-6666-6666-666666666666, 'ah@datastax.com', 'C@ss@ndr@43v3r');
INSERT INTO killrvideo.users (userid, created_date, firstname, lastname, email)
VALUES(77777777-7777-7777-7777-777777777777, toTimestamp(now()), 'Aleks', 'volochnev', 'av@datastax.com');
INSERT INTO killrvideo.user_credentials (userid, email, password)
VALUES(77777777-7777-7777-7777-777777777777, 'av@datastax.com', 'C@ss@ndr@3v3rywh3r3');
--------------------CELL_MARKDOWN_4--------------------
![line](https://s3.amazonaws.com/datastaxtraining/CaaS/line.png "line" )
# Install Credentials
### In this section, you will do the following things:
- #### Launch the Theia IDE
- #### Install the credentials file provided by Astra
#### One of the great things about Astra is it's secure, but that doesn't mean it's hard to use.
#### Astra provides you with a .zip
file that contains everything you need to connect to Astra, and you don't even have to unzip it!
---
Important Note:
If you completed the Bonus Challenge in the CQL unit, then you have already Installed the credentials. If so, peruse the instructions in this section and make sure what you did was consistent and then move to the next section.
---
#### Step 1: Open a new tab in your browser for Theia. Go to the course landing page and click on the _Eclipse Theia IDE_ link.
![LaunchTheia](https://s3.amazonaws.com/datastaxtraining/CaaS/LaunchTheia.png)
--------------------CELL_MARKDOWN_5--------------------
#### Step 2: If prompted, enter the IDE credentials. We gave you these credentials along with the URL for the course landing page.
![EnterIDECreds](https://s3.amazonaws.com/datastaxtraining/CaaS/EnterIDECreds.png)
--------------------CELL_MARKDOWN_6--------------------
#### Step 3: Open a terminal in the IDE - we will use the terminal to download the credentials file.
![OpenIDETerminal](https://s3.amazonaws.com/datastaxtraining/CaaS/OpenIDETerminal.png)
--------------------CELL_MARKDOWN_7--------------------
#### Step 4: Use the pwd
command to take note of the current directory. This is where we will place the credentials file.
![PWD](https://s3.amazonaws.com/datastaxtraining/CaaS/PWD.png)
--------------------CELL_MARKDOWN_8--------------------
#### Step 5: Back in the Constellation tab of your browser, copy the credentials file link address to your clipboard as shown.
#### **Note:** This link will expire after five minutes, so don't delay the next few steps which will require this link.
![CopyLink](https://s3.amazonaws.com/datastaxtraining/CaaS/CopyLink.png)
--------------------CELL_MARKDOWN_9--------------------
#### Step 6: Back in the IDE terminal, create a curl
command to download the credentials file. The command looks like curl "paste_the_link_here" > creds.zip
. Note that the double quotes are part of the command and surround the contents you paste from your clipboard. The right angle-bracket redirects the output from the curl
command to create the creds.zip
file.
![CurlCommand](https://s3.amazonaws.com/datastaxtraining/CaaS/CurlCommand.png)
--------------------CELL_MARKDOWN_10--------------------
#### Step 7: Review the contents of the directory to make sure curl
worked as expected. Use the ls -al
command. You should see a file named creds.zip
with a size similar to that shown:
![DirContents](https://s3.amazonaws.com/datastaxtraining/CaaS/DirContents.png)
#### That's it! Now your credentials file is in place and ready for use!
--------------------CELL_MARKDOWN_11--------------------
![line](https://s3.amazonaws.com/datastaxtraining/CaaS/line.png "line" )
# `INSERT` is the _C_ in CRUD
### In this section, you will do the following things:
- #### Review a stand-alone program for inserting a row into the `user_credentials` table
#### When KillrVideo wants to authenticate a user, the app accesses the `user_credentials` table.
#### Given an email address, KillrVideo retrieves the password hash and the user ID value.
#### Step 1: Review the following table definition:
```
CREATE TABLE IF NOT EXISTS user_credentials (
email text,
password text,
userid uuid,
PRIMARY KEY ((email))
);
```
#### The setup script you ran at the beginning of the notebook populated this table with some rows.
--------------------CELL_MARKDOWN_12--------------------
#### Step 2: Review the current `user_credentials` contents by executing the following cell.
--------------------CELL_CQL_13--------------------
// Execute this cell to see the contents of the user_credentials table
// Remember, to execute the cell, click in the cell and press SHIFT+ENTER (or just click on the Run button in the top-right corner of the cell)
SELECT * FROM killrvideo.user_credentials;
--------------------CELL_MARKDOWN_14--------------------
#### If you look at the `user_credentials` table carefully, you may notice two things:
- #### We are not using real `uuid` values; Instead, we are using contrived values to keep things simple (we would not use contrived values in a production system)
- #### Cristina Veale's credentials are missing from the table
#### In this section, we will see a program to insert Cristina's credentials into the table.
#### Step 3: Install the driver - Actually, we've already done this, so let's review what we did. Click on your language of choice and review the installation process.
Java - click here.
> In this example, we are using Maven.
> So, we update our `pom.xml` file, and let Maven do the heavy lifting for us.
> In Theia, within the crud-java project, click to open the `pom.xml` file (`~/workspace/crud-java/pom.xml`).
> You'll find it here:
> ![PomFile](https://s3.amazonaws.com/datastaxtraining/CaaS/PomFile.png)
> You will notice that we have updated the `pom.xml` file with the necessary repositories and dependencies.
```
com.datastax.dse
dse-java-driver-core
2.3.0
ch.qos.logback
logback-classic
1.2.3
```
> No need to change anything here, we just wanted to show you what we had done to get you set up!
Node.js - click here.
> For Node.js, use `npm` to install the driver with this command: `npm install dse-driver`.
> You don't need to run this command - we've done it for you!
>
> You will also notice the `package-lock.json` and `package.json` files.
> These are dependency files that the `npm` command creates for you.
> We'll leave these files in the project so we connect using the correct driver.
Python - click here.
> For Python, use `pip` to install the driver with this command: `pip install dse-driver`.
> You don't need to run this command - we've done it for you!
--------------------CELL_MARKDOWN_15--------------------
#### Step 4: Open the insert example code file.
- #### Check out your language of choice
Java - click here.
> You will find the example code in the `crud-java` project (`~/workspace/crud-java/src/main/java/Insert.java`).
> ![JavaInsertPath](https://s3.amazonaws.com/datastaxtraining/CaaS/JavaInsertPath.png "JavaInsertPath" )
> Click on the file name to open the file.
Node.js - click here.
> You will find the example code in the `crud-node-js` project (`~/workspace/crud-node-js/insert.js`).
> ![NodeInsertPath](https://s3.amazonaws.com/datastaxtraining/CaaS/NodeInsertPath.png "NodeInsertPath" )
> Click on the file name to open the file.
Python - click here.
> You will find the example code in the `crud-python` project (`~/workspace/crud-python/insert.py`).
> ![PythonInsertPath](https://s3.amazonaws.com/datastaxtraining/CaaS/PythonInsertPath.png "PythonInsertPath" )
> Click on the file name to open the file.
--------------------CELL_MARKDOWN_16--------------------
#### Step 5: Review the code.
- #### Check out your language of choice
Java - click here.
> First, the code creates a session:
```
try (DseSession session = DseSession.builder().withCloudSecureConnectBundle(DBConnection.getConnectionPath())
.withAuthCredentials(DBConnection.getUsername(), DBConnection.getPassword())
.build()) {
```
> Notice that `session` exists within the `try` clause.
> `DseSession` implements the `java.io.Closeable` interface, so you don't have to close the session explicitly.
> Instead the `try` clause takes care of that for you.
> This is especially useful for handling exceptions, etc.
>
> You see that we retrieve the connection information from the `DBConnection` class.
> We created the `DBConnection` class so we could centralize the connection information in one place.
> We have hard-coded the connection information in the `DBConnection` class.
> This would be a security problem in a production app, but we allow this usage here just to keep things simple.
>
> Also, in a real app, you wouldn't want to open and close a session for each operation.
> That would cause too much overhead.
> But we show the session creation and closing here to illustrate the complete session lifecycle.
>
> Here's the code that uses the session to execute the command.
```
session.execute(
SimpleStatement.builder( "INSERT INTO killrvideo.user_credentials (email, password, userid) VALUES (?,?,?)")
.addPositionalValues("cv@datastax.com", "3@$tC0@$tC@ss@ndr@", UUID.fromString("55555555-5555-5555-5555-555555555555"))
.build());
```
> It is easy to pick out the CQL string as an argument to the `SimpleStatement.builder()` method call.
> You can also see the question marks (`?`) that serve as place holders for value substitution.
> The values are on the next line in the call to the `addPositionalValues()` method.
> Three question marks, three values!
>
> As you will see, all the CRUD operations follow this same pattern.
Node.js - click here.
> First, the code creates a session using the code in `db_connection.js` (we will discuss this code in the next step).
```
const connection = require('./db_connection')
```
> Here's the code that uses the session to execute the command.
```
const insert = 'INSERT INTO killrvideo.user_credentials (email, password, userid) VALUES (?,?,?)';
const params = ['cv@datastax.com', '3@$tC0@$tC@ss@ndr@', Uuid.fromString('55555555-5555-5555-5555-555555555555')];
connection.client.execute(insert, params)
.then(function (result){
console.log('Success')
connection.client.shutdown()
})
.catch(function (error){
console.log(error.message)
connection.client.shutdown()
});
```
> It is easy to pick out the CQL string assigned to the `insert` variable.
> You can also see the question marks (`?`) that serve as place holders for value substitution.
> The values are on the next line assigned to the `params` variable.
> The `execute()` method inserts the values into the statement.
> Three question marks, three values!
>
> As you will see, all the CRUD operations follow this same pattern.
>
> Also notice that the `execute()` method call has a `then` clause and `catch` clause.
> These additional clauses are important to make sure the code closes the session.
>
> In a real app, you wouldn't want to open and close a session for each operation.
> That would cause too much overhead.
> But we show the session creation and closing here to illustrate the complete session lifecycle.
Python - click here.
> First, the code creates a session using the code in `db_connection.py` (we will discuss this code in the next step).
```
from db_connection import Connection
connection = Connection()
```
> Here's the code that uses the session to execute the command.
```
output = connection.session.execute(
"INSERT INTO killrvideo.user_credentials (email, password, userid) VALUES (%s, %s, %s)",
('cv@datastax.com', '3@$tC0@$tC@ss@ndr@', uuid.UUID('{55555555-5555-5555-5555-555555555555}'))
)
```
> It is easy to pick out the CQL string as an argument to the `execute()` method call.
> You can also see the `%s`s that serve as place holders for value substitution.
> The values are on the next line as a second argument to the method call.
> Three `%s`, three values!
>
> As you will see, all the CRUD operations follow this same pattern.
>
> When the session goes out of scope there are context management functions that handle the session clean-up for you.
>
> In a real app, you wouldn't want to open and close a session for each operation.
> That would cause too much overhead.
> But we show the session creation and closing here to illustrate the complete session lifecycle.
--------------------CELL_MARKDOWN_17--------------------
#### Step 6: Review the connection information.
- #### We connect using the credentials file we downloaded earlier, but we need to tell the app where that file is
- #### Also, we need to supply the credentials for the database access - these are not included in `creds.zip` as that would constitute a security risk
- #### Check out your language of choice
Java - click here.
> Open the DBConnection.java file in Theia (`~/workspace/crud-java/src/main/java/DBConnection.java`).
> You'll find it in the _crud-java_ project.
> ![JavaDBConnectionPath](https://s3.amazonaws.com/datastaxtraining/CaaS/JavaDBConnectionPath.png)
> Notice that static strings contain the `connectionPath`, `username` and `password` (although we wrap the `connectionPath` with the `Path` interface).
> The class uses getters to access these values.
> The point to this review is that these values are just simple strings that you could store in a configuration file - nothing fancy.
Node.js - click here.
> Open the `db_connection.js` file in Theia (`~/workspace/crud-node-js/db_connection.js`).
> You'll find it in the _crud-node-js_ project.
> ![NodeDBConnectionPath](https://s3.amazonaws.com/datastaxtraining/CaaS/NodeDBConnectionPath.png)
> Notice that this code contains the path to the credentials file as well as the database username and password values.
> These are simple strings that could (and probably should) be stored in a configuration file, but we have them in code in this example to keep things simple.
Python - click here.
> Open the db_connection.py file in Theia (`~/workspace/crud-python/db_connection.py`).
> You'll find it in the _crud-python_ project.
> ![PythonDBConnectionPath](https://s3.amazonaws.com/datastaxtraining/CaaS/PythonDBConnectionPath.png)
> Notice that this code contains the path to the credentials file as well as the database username and password values.
> These are simple strings that could (and probably should) be stored in a configuration file, but we have them in code in this example to keep things simple.
--------------------CELL_MARKDOWN_18--------------------
#### Step 7: Run the code.
- #### We've created a language-specific run command
- #### Check out your language of choice
Java - click here.
> Run the _Java-Insert_ command as shown.
> ![JavaInsertCommand](https://s3.amazonaws.com/datastaxtraining/CaaS/JavaInsertCommand.png "JavaInsertCommand" )
> You'll find your results in a tab near the bottom.
> ![JavaInsertResults](https://s3.amazonaws.com/datastaxtraining/CaaS/JavaInsertResults.png "JavaInsertResults" )
Node.js - click here.
> Run the _Node-Insert_ command as shown.
> ![NodeInsertCommand](https://s3.amazonaws.com/datastaxtraining/CaaS/NodeInsertCommand.png "NodeInsertCommand" )
> You'll find your results in a tab near the bottom.
> ![NodeInsertResults](https://s3.amazonaws.com/datastaxtraining/CaaS/NodeInsertResults.png "NodeInsertResults" )
Python - click here.
> Run the _Python-Insert_ command as shown.
> ![PythonInsertCommand](https://s3.amazonaws.com/datastaxtraining/CaaS/PythonInsertCommand.png "PythonInsertCommand" )
> You'll find your results in a tab near the bottom.
> ![PythonInsertResults](https://s3.amazonaws.com/datastaxtraining/CaaS/PythonInsertResults.png "PythonInsertResults" )
--------------------CELL_MARKDOWN_19--------------------
#### Step 8: Finally, review the contents of the table to verify the insert worked correctly. Execute the next cell.
--------------------CELL_CQL_20--------------------
// Execute this cell to see the contents of the user_credentials table after the INSERT
// Remember, to execute the cell, click in the cell and press SHIFT+ENTER (or just click on the Run button in the top-right corner of the cell)
SELECT * FROM killrvideo.user_credentials;
--------------------CELL_MARKDOWN_21--------------------
![line](https://s3.amazonaws.com/datastaxtraining/CaaS/line.png "line" )
# Example READ operation
### In this section, you will do the following things:
- #### Review the code that queries the `user_credentials` table
- #### Execute a stand-alone program to query the `user_credentials` table
#### Step 1: Open the example `SELECT` code file.
Java - click here.
> Open the file in Theia and inspect the code (`~/workspace/crud-java/src/main/java/SelectRows.java`).
> ![JavaSelectPath](https://s3.amazonaws.com/datastaxtraining/CaaS/JavaSelectPath.png "JavaSelectPath" )
Node.js - click here.
> Open the file in Theia and inspect the code (`~/workspace/crud-node-js/select_rows.js`).
> ![NodeSelectPath](https://s3.amazonaws.com/datastaxtraining/CaaS/NodeSelectPath.png "NodeSelectPath" )
Python - click here.
> Open the file in Theia and inspect the code (`~/workspace/crud-python/select_rows.py`).
> ![PythonSelectPath](https://s3.amazonaws.com/datastaxtraining/CaaS/PythonSelectPath.png "PythonSelectPath" )
--------------------CELL_MARKDOWN_22--------------------
#### Step 2: Review the example `SELECT` program.
#### This code reads the row we inserted in the previous section. Notice that the outline of the code looks like the `INSERT` code example:
- #### Create a connection
- #### Perform the CQL
- #### Close the connection
Java - click here.
> Notice that the code creates a connection to the database just like in the `INSERT` example above.
```
try (DseSession session = DseSession.builder().withCloudSecureConnectBundle(DBConnection.getConnectionPath())
.withAuthCredentials(DBConnection.getUsername(), DBConnection.getPassword())
.build()) {
```
> Next, the code performs the query - once again, it's easy to pick out the CQL.
> You can also see the argument value substitution:
```
ResultSet results = session.execute(
SimpleStatement.builder( "SELECT * FROM killrvideo.user_credentials WHERE email = ?")
.addPositionalValues("cv@datastax.com")
.build());
```
> Finally, the code prints the results.
> Since we know we are only getting a single row, the code uses `results.one()`.
> Then, the code uses the `row.getString()` and `row.getUuid()` methods to retrieve the various column values from the row.
> Note that there are other methods for various data types such as `getInt()`, `getFloat()`, etc.
```
System.out.println("***************************************************************************************");
if (row == null) {
System.out.println("No row selected");
} else {
System.out.format("%s %s %s\n",
row.getString("email"),
row.getString("password"),
row.getUuid("userid"));
}
System.out.println("***************************************************************************************");
```
Node.js - click here.
> Notice that the code creates a connection to the database just like in the `INSERT` example above:
```
const connection = require('./db_connection')
```
> Then, the program uses the connection to execute the CQL `SELECT` statement.
Notice the use of argument value substitution.
```
connection.client
.execute('SELECT * FROM killrvideo.user_credentials WHERE email = ?',
['cv@datastax.com'])
```
> Finally, the program either logs the results of the query, or handles any exceptions.
> In either case, notice that the program is careful to close the connection.
```
.then(function(result){
result.rows.forEach(row => {
console.log(row)
})
connection.client.shutdown()
})
.catch(function(error){
console.log(error.message)
connection.client.shutdown()
});
```
Python - click here.
> Notice that the program creates a connection to the database just like in the `INSERT` example above.
```
connection = Connection()
```
> Then, the program uses the connection to execute the CQL `SELECT` statement.
Notice the use of argument value substitution:
```
output = connection.session.execute("SELECT * FROM killrvideo.user_credentials WHERE email = %s",
['cv@datastax.com'])
```
> Next, the program prints out each resulting row (we only have one for this query).
```
for row in output:
print(row)
```
> Finally, the program closes the connection.
```
connection.close()
```
--------------------CELL_MARKDOWN_23--------------------
#### Step 3: Run the code.
Java - click here.
> Under the _Terminal_ dropdown menu, click _Run Task..._ and select _Java-Select_.
> ![JavaSelectCommand](https://s3.amazonaws.com/datastaxtraining/CaaS/JavaSelectCommand.png "JavaSelectCommand" )
> You'll find your results in a tab near the bottom of Theia.
```
***************************************************************************************
cv@datastax.com 3@$tC0@$tC@ss@ndr@ 55555555-5555-5555-5555-555555555555
***************************************************************************************
```
Node.js - click here.
> Under the _Terminal_ dropdown menu, click _Run Task..._ and select _Node-Select_.
> ![NodeSelectCommand](https://s3.amazonaws.com/datastaxtraining/CaaS/NodeSelectCommand.png "NodeSelectCommand" )
> You'll find your results in a tab near the bottom of Theia.
```
Row {
email: 'cv@datastax.com',
password: '3@$tC0@$tC@ss@ndr@',
userid: Uuid: 55555555-5555-5555-5555-555555555555 }
```
Python - click here.
> Under the _Terminal_ dropdown menu, click _Run Task..._ and select _Python-Select_.
> ![PythonSelectCommand](https://s3.amazonaws.com/datastaxtraining/CaaS/PythonSelectCommand.png "PythonSelectCommand" )
> You'll find your results in a tab near the bottom of Theia.
```
Row(email='cv@datastax.com', password='3@$tC0@$tC@ss@ndr@', userid=UUID('55555555-5555-5555-5555-555555555555'))
```
--------------------CELL_MARKDOWN_24--------------------
![line](https://s3.amazonaws.com/datastaxtraining/CaaS/line.png "line" )
# Example UPDATE operation
### In this section, you will do the following things:
- #### Review the example code that updates a row in the `user_credentials` table
- #### Run this example code and verify the results
#### Step 1: Open the example code file.
Java - click here.
> Open the file in Theia and inspect the code (`~/workspace/crud-java/src/main/java/Update.java`).
> ![JavaUpdatePath](https://s3.amazonaws.com/datastaxtraining/CaaS/JavaUpdatePath.png "JavaUpdatePath" )
Node.js - click here.
> Open the file in Theia and inspect the code (`~/workspace/crud-node-js/update.js`).
> ![NodeUpdatePath](https://s3.amazonaws.com/datastaxtraining/CaaS/NodeUpdatePath.png "NodeUpdatePath" )
Python - click here.
> Open the file in Theia and inspect the code (`~/workspace/crud-python/update.py`).
> ![PythonUpdatePath](https://s3.amazonaws.com/datastaxtraining/CaaS/PythonUpdatePath.png "PythonUpdatePath" )
#### Notice that this code updates Cristina's password by (this should sound familiar):
- #### Creating a connection to the database
- #### Performing the CQL statement
- #### Closing the connection
--------------------CELL_MARKDOWN_25--------------------
#### Step 2: Review the code.
Java - click here.
> Once again, the program creates a connection to the database.
```
try (DseSession session = DseSession.builder().withCloudSecureConnectBundle(DBConnection.getConnectionPath())
.withAuthCredentials(DBConnection.getUsername(), DBConnection.getPassword())
.build()) {
```
> Then, the code performs the `UPDATE`.
> You can see the CQL and the arguments.
```
session.execute(
SimpleStatement.builder( "UPDATE killrvideo.user_credentials SET password = ? WHERE email = ?")
.addPositionalValues("Cr1st1n@sN3wP@ssW0rd", "cv@datastax.com")
.build());
```
Node.js - click here.
> Again, the program creates a connection to the database.
```
const connection = require('./db_connection')
```
> Then, the program uses the connection to execute the CQL `UPDATE` statement.
Notice the use of argument value substitution.
```
connection.client.execute(
'UPDATE killrvideo.user_credentials SET password = ? WHERE email = ?',
['Cr1st1n@sN3wP@ssW0rd', 'cv@datastax.com'],
{ prepare : true }
)
```
> Finally, the program either prints _Success_, or handles any exceptions and closes the connection.
```
.then(function (result){
console.log('Success')
connection.client.shutdown()
})
.catch(function (error){
console.log(error.message)
connection.client.shutdown()
});
```
Python - click here.
> Again, the program creates a connection to the database.
```
connection = Connection()
```
> Then, the program uses the connection to execute the CQL `UPDATE` statement.
```
connection.session.execute(
"UPDATE killrvideo.user_credentials SET password = %s WHERE email = %s",
['Cr1st1n@sN3wP@ssW0rd', 'cv@datastax.com']
)
```
> Finally, the program prints the result and closes the connection.
```
except:
print('Failure')
else:
print('Success')
finally:
connection.close()
```
--------------------CELL_MARKDOWN_26--------------------
#### Step 3: Run the code.
Java - click here.
> Under the _Terminal_ dropdown menu, click _Run Task..._ and select _Java-Update_.
> ![JavaUpdateCommand](https://s3.amazonaws.com/datastaxtraining/CaaS/JavaUpdateCommand.png "JavaUpdateCommand" )
Node.js - click here.
> Under the _Terminal_ dropdown menu, click _Run Task..._ and select _Node-Update_.
> ![NodeUpdateCommand](https://s3.amazonaws.com/datastaxtraining/CaaS/NodeUpdateCommand.png "NodeUpdateCommand" )
Python - click here.
> Under the _Terminal_ dropdown menu, click _Run Task..._ and select _Python-Update_.
> ![PythonUpdateCommand](https://s3.amazonaws.com/datastaxtraining/CaaS/PythonUpdateCommand.png "PythonUpdateCommand" )
--------------------CELL_MARKDOWN_27--------------------
#### Step 4: Check out the results of the `UPDATE` by executing the following cell.
--------------------CELL_CQL_28--------------------
// Execute this cell to see the results of the updated row:
// Remember, to execute the cell, click in the cell and press SHIFT+ENTER (or just click on the Run button in the top-right corner of the cell)
SELECT * FROM killrvideo.user_credentials WHERE email = 'cv@datastax.com';
--------------------CELL_MARKDOWN_29--------------------
![line](https://s3.amazonaws.com/datastaxtraining/CaaS/line.png "line" )
# Example DELETE operation
### In this section, you will do the following things:
- #### Review the example code that deletes a row from `user_credentials`
- #### Run the example code and verify the results
#### Step 1: Open the example code file.
Java - click here.
> Click on the example code file (`~/workspace/crud-java/src/main/java/Delete.java`).
> ![JavaDeletePath](https://s3.amazonaws.com/datastaxtraining/CaaS/JavaDeletePath.png "JavaDeletePath" )
Node.js - click here.
> Click on the example code file (`~/workspace/crud-node-js/delete.js`).
> ![NodeDeletePath](https://s3.amazonaws.com/datastaxtraining/CaaS/NodeDeletePath.png "NodeDeletePath" )
Python - click here.
> Click on the example code file (`~/workspace/crud-python/delete.py`).
> ![PythonDeletePath](https://s3.amazonaws.com/datastaxtraining/CaaS/PythonDeletePath.png "PythonDeletePath" )
--------------------CELL_MARKDOWN_30--------------------
#### Step 2: Review the code.
Java - click here.
> Again, the program creates a connection to the database.
```
try (DseSession session = DseSession.builder().withCloudSecureConnectBundle(DBConnection.getConnectionPath())
.withAuthCredentials(DBConnection.getUsername(), DBConnection.getPassword())
.build()) {
```
> Then, the code performs the `DELETE`.
> This is the CQL and the arguments.
```
session.execute(
SimpleStatement.builder( "DELETE FROM killrvideo.user_credentials WHERE email = ?")
.addPositionalValues("cv@datastax.com")
.build());
```
Node.js - click here.
> Again, the program creates a connection to the database.
```
const connection = require('./db_connection')
```
> Then, the program uses the connection to execute the CQL `DELETE` statement.
```
connection.client.execute(
'DELETE FROM killrvideo.user_credentials WHERE email = ?',
['cv@datastax.com']
)
```
> Finally, the program either prints _Success_, or handles any exceptions and closes the connection.
```
.then(function (result){
console.log('Success')
connection.client.shutdown()
})
.catch(function (error){
console.log(error.message)
connection.client.shutdown()
});
```
Python - click here.
> Again, the program creates a connection to the database.
```
connection = Connection()
```
> Then, the program uses the connection to execute the CQL `DELETE` statement.
```
output = connection.session.execute(
"DELETE FROM killrvideo.user_credentials WHERE email = %s",
['cv@datastax.com']
)
```
> Finally, the program prints the result and closes the connection.
```
except:
print('Failure')
else:
print('Success')
finally:
connection.close()
```
--------------------CELL_MARKDOWN_31--------------------
#### Step 3: Run the code.
Java - click here.
> Under the _Terminal_ dropdown menu, click _Run Task..._ and select _Java-Delete_.
> ![JavaDeleteCommand](https://s3.amazonaws.com/datastaxtraining/CaaS/JavaDeleteCommand.png "JavaDeleteCommand" )
Node.js - click here.
> Under the _Terminal_ dropdown menu, click _Run Task..._ and select _Node-Delete_.
> ![NodeDeleteCommand](https://s3.amazonaws.com/datastaxtraining/CaaS/NodeDeleteCommand.png "NodeDeleteCommand" )
Python - click here.
> Under the _Terminal_ dropdown menu, click _Run Task..._ and select _Python-Delete_.
> ![PythonDeleteCommand](https://s3.amazonaws.com/datastaxtraining/CaaS/PythonDeleteCommand.png "PythonDeleteCommand" )
--------------------CELL_MARKDOWN_32--------------------
#### Step 4: Verify the results of running the code by executing the following cell.
--------------------CELL_CQL_33--------------------
// Execute this cell to see the contents of the user_credentials table after the DELETE
// Remember, to execute the cell, click in the cell and press SHIFT+ENTER (or just click on the Run button in the top-right corner of the cell)
SELECT * FROM killrvideo.user_credentials;
--------------------CELL_MARKDOWN_34--------------------
![line](https://s3.amazonaws.com/datastaxtraining/CaaS/line.png "line" )
# Congratulations!!!!
#### If you have made it to the end of this notebook successfully, your KillrVideo website is fully functional!
- #### Even better, you have learned the skills you need to use Apache Cassandra
- #### Of course, there is still a lot that we haven't covered, but you can learn more at DataStax Academy
#### Throw a KillrVideo CRUD party!
Don't want to throw the party by yourself? Click here, and we will party with you.
--------------------CELL_MARKDOWN_35--------------------
![line](https://s3.amazonaws.com/datastaxtraining/CaaS/line.png "line" )
# Bonus Challenge: Create your own `INSERT` code
### If you got done early and want something to do while you wait for others, here's a bonus challenge
### In this section, you will do the following things:
- #### Create code to insert a user into the `users` table
#### Since this is a bonus exercise, we won't hold your hand. If you need help, refer back to the previous exercise as a pattern.
#### Step 1: Back-up your CRUD projects.
- #### Open a terminal window in Theia
- #### In the terminal window execute the following command `tar -cvf crud.tar crud-*`. This will create a file named `crud.tar` that is a back-up copy of all your CRUD projects.
- #### If you ever need to restore your CRUD projects, close any open files and use the command `tar -xvf crud.tar`. This will overwrite the current files with the backed-up files.
--------------------CELL_MARKDOWN_36--------------------
#### Step 2: Modify the `INSERT` CRUD operation file to insert into the `users` table.
- #### In the CRUD project for the language of your choice, open the `INSERT` CRUD file.
- #### Modify the code to insert the following user into the `users` table.
First Name |
Last Name |
Email |
userid |
Cristina |
Veale |
cv@datastax.com |
55555555-5555-5555-5555-555555555555 |
- #### Here is the `users` table definition:
```
CREATE TABLE IF NOT EXISTS users (
userid uuid,
firstname text,
lastname text,
email text,
created_date timestamp,
PRIMARY KEY (userid)
);
```
- #### If you do not know how to handle the `created_date` column value, you can just omit that column in the insert (remember, Cassandra supports sparse columns). We will show you how to create dates later.
Java Solution - click here.
```
import com.datastax.dse.driver.api.core.DseSession;
import com.datastax.oss.driver.api.core.cql.*;
import java.time.Instant;
import java.util.UUID;
public class Insert {
public static void main(String[] args) {
try (DseSession session = DseSession.builder()
.withCloudSecureConnectBundle(DBConnection.getConnectionPath())
.withAuthCredentials(DBConnection.getUsername(), DBConnection.getPassword())
.build()) {
session.execute(
SimpleStatement.builder( "INSERT INTO killrvideo.users (userid, firstname, lastname, email, created_date) VALUES (?,?,?,?,?)")
.addPositionalValues(UUID.fromString("55555555-5555-5555-5555-555555555555"), "Cristina", "Veale", "cv@datastax.com", Instant.now())
.build());
System.out.println("Successful Insert");
}
catch(Throwable t) {
System.out.println("Failed Insert");
t.printStackTrace();
}
}
}
```
Node.js Solution - click here.
```
const connection = require('./db_connection')
const Uuid = require('cassandra-driver').types.Uuid;
// this is an insert statement in nodejs
let createdDate = new Date(Date.now());
const insert = 'INSERT INTO killrvideo.users (userid, firstname, lastname, email, created_date) VALUES (?,?,?,?,?)';
const params = [Uuid.fromString('55555555-5555-5555-5555-555555555555'), 'Cristina', 'Veale', 'cv@datastax.com', createdDate];
connection.client.execute(insert, params)
.then(function (result){
console.log('Success')
connection.client.shutdown()
})
.catch(function (error){
console.log(error.message)
connection.client.shutdown()
});
```
Python Solution - click here.
```
#!/usr/bin/env python3
from datetime import datetime
from db_connection import Connection
import uuid
# this is a insert statement in python
try:
connection = Connection()
output = connection.session.execute(
"INSERT INTO killrvideo.users (userid, firstname, lastname, email, created_date) VALUES (%s, %s, %s, %s, %s)",
(uuid.UUID('{55555555-5555-5555-5555-555555555555}'), 'Cristina', 'Veale', 'cv@datastax.com', datetime.now())
)
except:
print('Failure')
print("Unexpected error:", sys.exc_info()[0])
else:
print('Success')
finally:
connection.close()
```
--------------------CELL_MARKDOWN_37--------------------
#### Step 3: Execute the following cell to see the current contents of the `users` table _before_ you insert the additional row. Verify Cristina's row is not yet in the table.
--------------------CELL_CQL_38--------------------
// Execute this cell to see the contents of the users table
// Remember, to execute the cell, click in the cell and press SHIFT+ENTER (or just click on the Run button in the top-right corner of the cell)
SELECT * FROM killrvideo.users;
--------------------CELL_MARKDOWN_39--------------------
#### Step 4: Execute the `INSERT` code using _Run Task..._ command under the _Terminal_ menu item.
--------------------CELL_MARKDOWN_40--------------------
#### Step 5: Execute the following cell to see the current contents of the users table _after_ you have inserted the additional row. Verify Cristina's row is now in the table.
--------------------CELL_CQL_41--------------------
// Execute this cell to see the contents of the users table
// Remember, to execute the cell, click in the cell and press SHIFT+ENTER (or just click on the Run button in the top-right corner of the cell)
SELECT * FROM killrvideo.users;
--------------------CELL_MARKDOWN_42--------------------
![line](https://s3.amazonaws.com/datastaxtraining/CaaS/line.png "line" )
# Bonus Challenge: Create your own `SELECT` code
### If you got done early and want something to do while you wait for others, here's another bonus challenge
### In this section, you will do the following things:
- #### Create code to retrieve a user from the `users` table
#### Again, since this is a bonus exercise, you're on your own to figure it out. If you need help, refer back to the previous exercises as a pattern.
#### Step 1: Modify the `SELECT` CRUD operation file to retrieve a row from the `users` table.
- #### In the CRUD project for the language of your choice, open the `SELECT` CRUD file.
- #### Modify the code to retrieve the row from the `users` table with a `userid` of `55555555-5555-5555-5555-555555555555`.
Java Solution - click here.
```
import com.datastax.dse.driver.api.core.DseSession;
import com.datastax.oss.driver.api.core.cql.*;
import java.util.UUID;
public class SelectRows {
public static void main(String args[]) {
try (DseSession session = DseSession.builder().withCloudSecureConnectBundle(DBConnection.getConnectionPath())
.withAuthCredentials(DBConnection.getUsername(), DBConnection.getPassword())
.build()) {
ResultSet results = session.execute(
SimpleStatement.builder( "SELECT * FROM killrvideo.users WHERE userid = ?")
.addPositionalValues(UUID.fromString("55555555-5555-5555-5555-555555555555"))
.build());
Row row = results.one();
System.out.println("***************************************************************************************");
if (row == null) {
System.out.println("No row selected");
} else {
System.out.format("%s %s %s %s %s\n",
row.getString("firstname"),
row.getString("lastname"),
row.getString("email"),
row.getInstant("created_date"),
row.getUuid("userid"));
}
System.out.println("***************************************************************************************");
}
catch(Throwable t) {
System.out.println("Failed Select");
t.printStackTrace();
}
}
}
```
Node.js Solution - click here.
```
const connection = require('./db_connection')
const Uuid = require('cassandra-driver').types.Uuid;
// this is a select statement in nodejs
connection.client
.execute('SELECT * FROM killrvideo.users WHERE userid = ?',
[Uuid.fromString('55555555-5555-5555-5555-555555555555')])
.then(function(result){
result.rows.forEach(row => {
console.log(row)
})
connection.client.shutdown()
})
.catch(function(error){
console.log(error.message)
connection.client.shutdown()
});
```
Python Solution - click here.
```
#!/usr/bin/env python3
from db_connection import Connection
import uuid
# this is a select statement in python
connection = Connection()
output = connection.session.execute("SELECT * FROM killrvideo.users WHERE userid = %s",
[uuid.UUID('{55555555-5555-5555-5555-555555555555}')])
for row in output:
print(row)
connection.close()
```
--------------------CELL_MARKDOWN_43--------------------
#### Step 2: Execute the `SELECT` code using _Run Task..._ command under the _Terminal_ menu item and verify your results.
--------------------CELL_MARKDOWN_44--------------------
![line](https://s3.amazonaws.com/datastaxtraining/CaaS/line.png "line" )
# Bonus Challenge: Create your own `UPDATE` code
### If you got done early and want something to do while you wait for others, here's yet another bonus challenge
### In this section, you will do the following things:
- #### Create code to update a row in the `users` table
#### Again, since this is a bonus exercise, you get to figure it out. If you need help, refer back to the previous exercises as a pattern.
#### Step 1: Modify the `UPDATE` CRUD operation file to change a row from the `users` table.
- #### In the CRUD project for the language of your choice, open the `UPDATE` CRUD file.
- #### Modify the code to change Cristina's email address in the `users` table to `cveale@datastax.com`. Remember, her `userid` is `55555555-5555-5555-5555-555555555555`.
Java Solution - click here.
```
import com.datastax.dse.driver.api.core.DseSession;
import com.datastax.oss.driver.api.core.cql.*;
import java.util.UUID;
public class Update {
public static void main(String[] args) {
try (DseSession session = DseSession.builder().withCloudSecureConnectBundle(DBConnection.getConnectionPath())
.withAuthCredentials(DBConnection.getUsername(), DBConnection.getPassword())
.build()) {
session.execute(
SimpleStatement.builder( "UPDATE killrvideo.users SET email = ? WHERE userid = ?")
.addPositionalValues("cveale@datastax.com", UUID.fromString("55555555-5555-5555-5555-555555555555"))
.build());
System.out.println("Update Succeeded");
}
catch(Throwable t) {
System.out.println("Failed Update");
t.printStackTrace();
}
}
}
```
Node.js Solution - click here.
```
const connection = require('./db_connection')
const Uuid = require('cassandra-driver').types.Uuid;
// this is a update statement in nodejs
connection.client.execute(
'UPDATE killrvideo.users SET email = ? WHERE userid = ?',
['cveale@datastax.com', Uuid.fromString('55555555-5555-5555-5555-555555555555')],
{ prepare : true }
)
.then(function (result){
console.log('Success')
connection.client.shutdown()
})
.catch(function (error){
console.log(error.message)
connection.client.shutdown()
});
```
Python Solution - click here.
```
#!/usr/bin/env python3
from db_connection import Connection
import uuid
# this is a update statement in python
try:
connection = Connection()
connection.session.execute(
"UPDATE killrvideo.users SET email = %s WHERE userid = %s",
['cveale@datastax.com', uuid.UUID('{55555555-5555-5555-5555-555555555555}')]
)
except:
print('Failure')
else:
print('Success')
finally:
connection.close()
```
--------------------CELL_MARKDOWN_45--------------------
#### Step 2: Execute the following cell to see Cristina's row _before_ you update it.
--------------------CELL_CQL_46--------------------
// Execute this cell to see Cristina's row
// Remember, to execute the cell, click in the cell and press SHIFT+ENTER (or just click on the Run button in the top-right corner of the cell)
SELECT * FROM killrvideo.users WHERE userid = 55555555-5555-5555-5555-555555555555;
--------------------CELL_MARKDOWN_47--------------------
#### Step 3: Execute the `UPDATE` code using _Run Task..._ command under the _Terminal_ menu item and verify your results.
--------------------CELL_MARKDOWN_48--------------------
#### Step 4: Execute the following cell to see Cristina's row _after_ you have updated it.
--------------------CELL_CQL_49--------------------
// Execute this cell to see Cristina's row after the update
// Remember, to execute the cell, click in the cell and press SHIFT+ENTER (or just click on the Run button in the top-right corner of the cell)
SELECT * FROM killrvideo.users WHERE userid = 55555555-5555-5555-5555-555555555555;
--------------------CELL_MARKDOWN_50--------------------
![line](https://s3.amazonaws.com/datastaxtraining/CaaS/line.png "line" )
# Bonus Challenge: Create your own `DELETE` code
### If you got done early and want something to do while you wait for others, here's a final bonus challenge
### In this section, you will do the following things:
- #### Create code to delete a row from the `users` table
#### You know the drill. If you need help, refer back to the previous exercises as a pattern.
#### Step 1: Modify the `DELETE` CRUD operation file to delete a row from the `users` table.
- #### In the CRUD project for the language of your choice, open the `DELETE` CRUD file.
- #### Modify the code to delete the row with a `userid` of `55555555-5555-5555-5555-555555555555`.
Java Solution - click here.
```
import com.datastax.dse.driver.api.core.DseSession;
import com.datastax.oss.driver.api.core.cql.*;
import java.util.UUID;
public class Delete {
public static void main(String[] args) {
try (DseSession session = DseSession.builder().withCloudSecureConnectBundle(DBConnection.getConnectionPath())
.withAuthCredentials(DBConnection.getUsername(), DBConnection.getPassword())
.build()) {
session.execute(
SimpleStatement.builder( "DELETE FROM killrvideo.users WHERE userid = ?")
.addPositionalValues(UUID.fromString("55555555-5555-5555-5555-555555555555"))
.build());
System.out.println("Successful Delete");
}
catch(Throwable t) {
System.out.println("Failed Delete");
t.printStackTrace();
}
}
}
```
Node.js Solution - click here.
```
const connection = require('./db_connection')
const Uuid = require('cassandra-driver').types.Uuid;
// this is a delete statement in nodejs
connection.client.execute(
'DELETE FROM killrvideo.users WHERE userid = ?',
[Uuid.fromString('55555555-5555-5555-5555-555555555555')]
)
.then(function (result){
console.log('Success')
connection.client.shutdown()
})
.catch(function (error){
console.log(error.message)
connection.client.shutdown()
});
```
Python Solution - click here.
```
#!/usr/bin/env python3
from db_connection import Connection
import uuid
# this is a delete statement in python
try:
connection = Connection()
output = connection.session.execute(
"DELETE FROM killrvideo.users WHERE userid = %s",
[uuid.UUID('{55555555-5555-5555-5555-555555555555}')]
)
except:
print('Failure')
else:
print('Success')
finally:
connection.close()
```
--------------------CELL_MARKDOWN_51--------------------
#### Step 2: Execute the `DELETE` code using _Run Task..._ command under the _Terminal_ menu item and verify your results.
--------------------CELL_MARKDOWN_52--------------------
#### Step 3: Execute the following cell to see the entire contents of the `users` table. Verify Cristina's row is gone.
--------------------CELL_CQL_53--------------------
// Execute this cell to see the contents of the users table
// Remember, to execute the cell, click in the cell and press SHIFT+ENTER (or just click on the Run button in the top-right corner of the cell)
SELECT * FROM killrvideo.users;
versions-info.txt 0100644 0000000 0000000 00000000055 13625045620 013152 0 ustar 00 0000000 0000000 Studio Version: 6.8.0-20191105-CLOUD-9e8a234