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/.

Posted by Don Pinto

Don Pinto is a Principal Product Manager at Couchbase and is currently focused on advancing the capabilities of Couchbase Server. He is extremely passionate about data technology, and in the past has authored several articles on Couchbase Server including technical blogs and white papers. Prior to joining Couchbase, Don spent several years at IBM where he maintained the role of software developer in the DB2 information management group and most recently as a program manager on the SQL Server team at Microsoft. Don holds a master's degree in computer science and a bachelor's in computer engineering from the University of Toronto, Canada.

Leave a reply