Note: this is a guest post by Mike Howard of Simba Technologies.
N1QL DML
N1QL data manipulation support makes it easy to insert, update, and delete JSON data, without the overhead of having to read the document first. These N1QL statements can perform an operation across an entire bucket with a single network request. N1QL queries are not bound by schema, and therefore can be used to manipulate data outside of schema constraints. Also, N1QL queries are not restricted to a single table, and are perfect for manipulating data across multiple tables simultaneously. The Simba Couchbase Driver allows you to execute these queries directly through the driver.
INSERT
N1QL INSERT allows for the insert of a new JSON Document. While this method could be used for the simple task of adding a row to an existing table, it is particularly useful when adding data that does not match an existing schema. The ability to add non-schema data enables you to manipulate the schema: Add a document of existing type with new attributes, or add a document of new type to create new tables. And you’ll get new columns in the schema. However, you’ll have to resample the database (using SQL) to view any new non-schema data.
Using the gamesim-sample bucket as an example, say we wanted to add a “Bosses” feature to our game. Use a N1QL INSERT to create a Bosses table by adding a new document of type “boss”:
INSERT INTO gamesim-sample
(KEY, VALUE) VALUES (“Blue Dragon”, {“type”:”boss”, “hitpoints”: 70000})
Resample with the driver to create a new boss table with this information. If we choose to extend our boss table to include experience points similar to the monster table, we can use N1QL INSERT to insert new documents into an existing table that do not match the table’s current schema:
INSERT INTO gamesim-sample
(KEY, VALUE) VALUES (“Golden Griffin”, {“type”:”boss”, “hitpoints”: 80000, “experienceWhenKilled”: 37000})
As in the first case above, this schema change will not take effect until the table is resampled. However, any values that match the existing schema will still be available:
Boss Table after INSERT before Resample
PK | hitpoints |
Blue Dragon | 70000 |
Golden Griffin | 80000 |
Boss Table after Resample
PK | hitpoints | experienceWhenKilled |
Blue Dragon | 70000 | NULL |
Golden Griffin | 80000 | 37000 |
UPDATE
You can use N1QL UPDATE to modify JSON documents directly. Like SQL UPDATE, this operation can be used to modify individual attributes in a table, however the true power of UPDATE comes from its bucket-wide scope. Using N1QL UPDATE, you can modify every matching document in the bucket via a single query – in a single network request, without having to read-in all of the data first.
Here’s an example scenario: Update a video game to increase the hitpoints of all players and monsters by a fixed multiplier. Normally this would require two queries – One to update the players, and one for the monsters:
Player Table before UPDATE
PK | hitpoints |
Aaron0 | 20210 |
Aaron1 | 23832 |
Aaron2 | 10 |
Aliaksey0 | 10 |
Monster Table before UPDATE
PK | hitpoints |
Bauchan0 | 3754 |
Bauchan1 | 3627 |
Bauchan2 | 4999 |
Bauchan3 | 4699 |
Bauchan4 | 3070 |
But with N1QL, we can issue a single bucket wide update to complete this task:
UPDATE gamesim-sample
SET hitpoints = hitpoints*2
This query doubles the value of every hitpoints attribute in the bucket in a single operation.
Player Table after UPDATE
PK | hitpoints |
Aaron0 | 40420 |
Aaron1 | 47664 |
Aaron2 | 20 |
Aliaksey0 | 20 |
Monster Table after UPDATE
PK | hitpoints |
Bauchan0 | 7508 |
Bauchan1 | 7254 |
Bauchan2 | 9998 |
Bauchan3 | 9398 |
Bauchan4 | 6140 |
DELETE
N1QL delete removes all documents in the bucket that match a specified condition. This can be useful to perform batch deletes. An example operation could be removing a brewery in the beer-sample bucket. To remove a brewery and all beers associated with it, we could issue the following query:
DELETE FROM beer-sample
WHERE name = “21st Amendment Brewery Cafe” OR brewery_id = “21st_amendment_brewery_cafe”
This removes all the data related to the “21st Amendment Brewery Cafe” in the bucket in a single operation. Using SQL this would require three queries, one to remove the brewery from the brewery table, a second to remove the address from the brewery_address table and a third to remove the beers associated with the brewery from the beer table.
SQL DML
The Simba Couchbase Driver also enables you to manipulate tables using SQL syntax. You can connect your Couchbase Driver via ODBC or JDBC to SQL-based write tools such as ETL applications. It also allows you to more easily issue queries that are scoped to the table level.
INSERT
SQL INSERT adds a document to a bucket that matches the schema of the table to which it is being added. Unlike N1QL, SQL INSERT will actively enforce the schema defined in the schema map. For performing insert operations that are intended to match the current schema, SQL INSERT is preferred as it prevents accidental schema modification.
To continue the Boss Table example, let’s add a new boss to the table. To start, we issue the SQL command:
INSERT INTO ‘gamesim-sample’.boss (PK, hitpoints, experienceWhenKilled) VALUES (“Grey Wolf”, 22000, 12000)
This adds the Wolf row to the Boss table. Notice how the type attribute does not need to be specified as it is implied by the table name:
Boss Table after SQL INSERT
PK | Hitpoints | experienceWhenKilled |
Blue Dragon | 70000 | NULL |
Golden Griffin | 80000 | 37000 |
Grey Wolf | 22000 | 12000 |
UPDATE
SQL UPDATE lets you update a single table. This is useful if you want to restrict the scope of the update to that single table. For updates to specific tables, SQL UPDATE is preferred to N1QL as its narrow scope prevents the unintentional modification of other documents.
For example, imagine if we wanted to increase the hitpoints of the monsters. We can do this by issuing the SQL UPDATE command:
UPDATE ‘gamesim-sample’.monster SET hitpoints=hitpoints*1.5
This updates just the monster hitpoints, leaving the player table untouched.
Monster Table After Update
PK | hitpoints |
Bauchan0 | 5631 |
Bauchan1 | 5440 |
Bauchan2 | 7498 |
Bauchan3 | 7048 |
Bauchan4 | 4605 |
DELETE
SQL DELETE deletes a single row from a table. Similar to SQL UPDATE, this is useful for limiting the operation to a single table. Similar to insert and update, SQL DELETE is the preferred operation for removing documents from a single table, as it prevents the accidental removal of other document.
For example, we can delete all players with less than 100 hit points. We issue the SQL DELETE command:
DELETE FROM ‘gamesim-sample’.player WHERE hitpoints < 100
This removes all players who have less than 100 hitpoints, while leaving the monsters intact.
Summary: flexible bucket- or table-level N1QL/SQL data manipulation
The Simba Couchbase driver gives users the flexibility to manipulate their data at either the bucket or table level using either N1QL or SQL. Using N1QL lets you perform bucket-wide operations such as schema manipulation, mass updates, or mass deletes in a single operation. Using SQL gives you access to traditional ETL tools as well as table-level data manipulation. You can download and try out simba drivers at http://www.simba.com/drivers/couchbase-odbc-jdbc/.