Interroger Couchbase avec N1QL

1. Vue d'ensemble

Dans cet article, nous allons examiner l'interrogation d'un serveur Couchbase avec N1QL. De manière simplifiée, il s'agit de SQL pour les bases de données NoSQL - dans le but de faciliter la transition des bases de données SQL / relationnelles vers un système de base de données NoSQL.

Il existe plusieurs façons d'interagir avec le serveur Couchbase; ici, nous utiliserons le SDK Java pour interagir avec la base de données - comme c'est typique pour les applications Java.

2. Dépendances de Maven

Nous supposons qu'un serveur Couchbase local a déjà été configuré; si ce n'est pas le cas, ce guide peut vous aider à démarrer.

Ajoutons maintenant la dépendance du SDK Java Couchbase à pom.xml :

 com.couchbase.client java-client 2.5.0 

La dernière version du SDK Java Couchbase est disponible sur Maven Central.

Nous utiliserons également la bibliothèque Jackson pour mapper les résultats renvoyés par les requêtes; ajoutons également sa dépendance à pom.xml :

 com.fasterxml.jackson.core jackson-databind 2.9.1 

La dernière version de la bibliothèque Jackson est disponible sur Maven Central.

3. Connexion à un serveur Couchbase

Maintenant que le projet est configuré avec les bonnes dépendances, connectons-nous à Couchbase Server depuis une application Java.

Tout d'abord, nous devons démarrer le serveur Couchbase - s'il ne fonctionne pas déjà.

Un guide pour démarrer et arrêter un serveur Couchbase peut être trouvé ici.

Connectons à un Couchbase Bucket :

Cluster cluster = CouchbaseCluster.create("localhost"); Bucket bucket = cluster.openBucket("test");

Ce que nous avons fait, c'est de nous connecter au cluster Couchbase , puis d'obtenir l' objet Bucket .

Le nom du bucket dans le cluster Couchbase est test et peut être créé à l'aide de la console Web Couchbase. Lorsque nous avons terminé toutes les opérations de base de données, nous pouvons fermer le compartiment particulier que nous avons ouvert.

D'un autre côté, nous pouvons nous déconnecter du cluster - ce qui finira par fermer tous les buckets:

bucket.close(); cluster.disconnect();

4. Insertion de documents

Couchbase est un système de base de données orienté document. Ajoutons un nouveau document au bucket de test :

JsonObject personObj = JsonObject.create() .put("name", "John") .put("email", "[email protected]") .put("interests", JsonArray.from("Java", "Nigerian Jollof")); String id = UUID.randomUUID().toString(); JsonDocument doc = JsonDocument.create(id, personObj); bucket.insert(doc);

Tout d'abord, nous avons créé un personObj JSON et fourni quelques données initiales. Les clés peuvent être vues comme des colonnes dans un système de base de données relationnelle.

À partir de l'objet personne, nous avons créé un document JSON à l'aide de JsonDocument.create (), que nous insérerons dans le compartiment . Notez que nous générons un identifiant aléatoire en utilisant la classe java.util.UUID .

Le document inséré peut être vu dans la console Web Couchbase à l'adresse // localhost: 8091 ou en appelant le bucket.get () avec son id :

System.out.println(bucket.get(id));

5. Requête de base N1QL SELECT

N1QL est un sur-ensemble de SQL, et sa syntaxe, naturellement, semble similaire.

Par exemple, le N1QL pour sélectionner tous les documents dans le bucket de test est:

SELECT * FROM test

Exécutons cette requête dans l'application:

bucket.bucketManager().createN1qlPrimaryIndex(true, false); N1qlQueryResult result = bucket.query(N1qlQuery.simple("SELECT * FROM test"));

Tout d'abord, nous créons un index primaire à l'aide de createN1qlPrimaryIndex () , il sera ignoré s'il a été créé auparavant; sa création est obligatoire avant qu'une requête puisse être exécutée.

Ensuite, nous utilisons le bucket.query () pour exécuter la requête N1QL.

N1qlQueryResult est un objet Iterable , et ainsi nous pouvons imprimer chaque ligne en utilisant forEach () :

result.forEach(System.out::println);

À partir du résultat renvoyé , nous pouvons obtenir l' objet N1qlMetrics en appelant result.info () . À partir de l'objet de métriques, nous pouvons obtenir des informations sur le résultat renvoyé - par exemple, le résultat et le nombre d'erreurs:

System.out.println("result count: " + result.info().resultCount()); System.out.println("error count: " + result.info().errorCount());

Sur le résultat renvoyé , nous pouvons utiliser result.parseSuccess () pour vérifier si la requête est syntaxiquement correcte et analysée avec succès. Nous pouvons utiliser result.finalSuccess () pour déterminer si l'exécution de la requête a réussi.

6. Instructions de requête N1QL

Jetons un coup d'œil aux différentes instructions N1QL Query et aux différentes manières de les exécuter via le SDK Java.

6.1. SELECT Déclaration

L' instruction SELECT dans NIQL est comme un SQL SELECT standard . Il se compose de trois parties:

  • SELECT - définit la projection des documents à renvoyer
  • FROM describes the keyspace to fetch the documents from; keyspace is synonymous with table name in SQL database systems
  • WHERE specifies the additional filtering criteria

The Couchbase Server comes with some sample buckets (databases). If they were not loaded during initial setup, the Settings section of the Web Console has a dedicated tab for setting them up.

We'll be using the travel-sample bucket. The travel-sample bucket contains data for airlines, landmark, airports, hotels, and routes. The data model can be found here.

Let's select 100 airline records from the travel-sample data:

String query = "SELECT name FROM `travel-sample` " + "WHERE type = 'airport' LIMIT 100"; N1qlQueryResult result1 = bucket.query(N1qlQuery.simple(query));

The N1QL query, as can be seen above, looks very similar to SQL. Note that the keyspace name has to be put in backtick (`) because it contains a hyphen.

N1qlQueryResult is just a wrapper around the raw JSON data returned from the database. It extends Iterable and can be looped over.

Invoking result1.allRows() will return all the rows in a List object. This is useful for processing results with the Stream API and/or accessing each result via index:

N1qlQueryRow row = result1.allRows().get(0); JsonObject rowJson = row.value(); System.out.println("Name in First Row " + rowJson.get("name"));

We got the first row of the returned results, and we use row.value() to get a JsonObject – which maps the row to a key-value pair, and the key corresponds to the column name.

So we got the value of column, name, for the first row using the get(). It's as easy as that.

So far we have been using simple N1QL query. Let's look at the parameterized statement in N1QL.

In this query, we're going to use the wildcard (*) symbol for selecting all the fields in the travel-sample records where type is an airport.

The type will be passed to the statement – as a parameter. Then we process the returned result:

JsonObject pVal = JsonObject.create().put("type", "airport"); String query = "SELECT * FROM `travel-sample` " + "WHERE type = $type LIMIT 100"; N1qlQueryResult r2 = bucket.query(N1qlQuery.parameterized(query, pVal));

We created a JsonObject to hold the parameters as a key-value pair. The value of the key ‘type', in the pVal object, will be used to replace the $type placeholder in the query string.

N1qlQuery.parameterized() accepts a query string that contains one or more placeholders and a JsonObject as demonstrated above.

In the previous sample query above, we only select a column – name. This makes it easy to map the returned result into a JsonObject.

But now that we use the wildcard (*) in the select statement, it is not that simple. The returned result is a raw JSON string:

[ { "travel-sample":{ "airportname":"Calais Dunkerque", "city":"Calais", "country":"France", "faa":"CQF", "geo":{ "alt":12, "lat":50.962097, "lon":1.954764 }, "icao":"LFAC", "id":1254, "type":"airport", "tz":"Europe/Paris" } },

So what we need is a way to map each row to a structure that allows us to access the data by specifying the column name.

Therefore, let's create a method that will accept N1qlQueryResult and then map every row in the result to a JsonNode object.

We choose JsonNode because it can handle a broad range of JSON data structures and we can easily navigate it:

public static List extractJsonResult(N1qlQueryResult result) { return result.allRows().stream() .map(row -> { try { return objectMapper.readTree(row.value().toString()); } catch (IOException e) { logger.log(Level.WARNING, e.getLocalizedMessage()); return null; } }) .filter(Objects::nonNull) .collect(Collectors.toList()); }

We processed each row in the result using the Stream API. We mapped each row to a JsonNode object and then return the result as a List of JsonNodes.

Now we can use the method to process the returned result from the last query:

List list = extractJsonResult(r2); System.out.println( list.get(0).get("travel-sample").get("airportname").asText());

From the example JSON output shown previously, every row has a key the correlates to the keyspace name specified in the SELECT query – which is travel-sample in this case.

So we got the first row in the result, which is a JsonNode. Then we traverse the node to get to the airportname key, that is then printed as a text.

The example raw JSON output shared earlier provides more clarity as per the structure of the returned result.

6.2. SELECT Statement Using N1QL DSL

Other than using raw string literals for building queries we can also use N1QL DSL which comes with the Java SDK we are using.

For example, the above string query can be formulated with the DSL thus:

Statement statement = select("*") .from(i("travel-sample")) .where(x("type").eq(s("airport"))) .limit(100); N1qlQueryResult r3 = bucket.query(N1qlQuery.simple(statement));

The DSL is fluent and can be interpreted easily. The data selection classes and methods are in com.couchbase.client.java.query.Select class.

Expression methods like i(), eq(), x(), s() are in com.couchbase.client.java.query.dsl.Expression class. Read more about the DSL here.

N1QL select statements can also have OFFSET, GROUP BY and ORDER BY clauses. The syntax is pretty much like that of standard SQL, and its reference can be found here.

The WHERE clause of N1QL can take Logical Operators AND, OR, and NOT in its definitions. In addition to this, N1QL has provision for comparison operators like >, ==, !=, IS NULL and others.

There are also other operators that make accessing stored documents easy – the string operators can be used to concatenate fields to form a single string, and the nested operators can be used to slice arrays and cherry pick fields or element.

Let's see these in action.

This query selects the city column, concatenate the airportname and faa columns as portname_faa from the travel-sample bucket where the country column ends with ‘States'‘, and the latitude of the airport is greater than or equal to 70:

String query2 = "SELECT t.city, " + "t.airportname || \" (\" || t.faa || \")\" AS portname_faa " + "FROM `travel-sample` t " + "WHERE t.type=\"airport\"" + "AND t.country LIKE '%States'" + "AND t.geo.lat >= 70 " + "LIMIT 2"; N1qlQueryResult r4 = bucket.query(N1qlQuery.simple(query2)); List list3 = extractJsonResult(r4); System.out.println("First Doc : " + list3.get(0));

We can do the same thing using N1QL DSL:

Statement st2 = select( x("t.city, t.airportname") .concat(s(" (")).concat(x("t.faa")).concat(s(")")).as("portname_faa")) .from(i("travel-sample").as("t")) .where( x("t.type").eq(s("airport")) .and(x("t.country").like(s("%States"))) .and(x("t.geo.lat").gte(70))) .limit(2); N1qlQueryResult r5 = bucket.query(N1qlQuery.simple(st2)); //...

Let's look at other statements in N1QL. We'll be building on the knowledge we've acquired in this section.

6.3. INSERT Statement

The syntax for the insert statement in N1QL is:

INSERT INTO `travel-sample` ( KEY, VALUE ) VALUES("unique_key", { "id": "01", "type": "airline"}) RETURNING META().id as docid, *;

Where travel-sample is the keyspace name, unique_key is the required non-duplicate key for the value object that follows it.

The last segment is the RETURNING statement that specifies what gets returned.

In this case, the id of the inserted document is returned as docid. The wildcard (*) signifies that other attributes of the added document should be returned as well – separately from docid. See the sample result below.

Executing the following statement in the Query tab of Couchbase Web Console will insert a new record into the travel-sample bucket:

INSERT INTO `travel-sample` (KEY, VALUE) VALUES('cust1293', {"id":"1293","name":"Sample Airline", "type":"airline"}) RETURNING META().id as docid, *

Let's do the same thing from a Java app. First, we can use a raw query like this:

String query = "INSERT INTO `travel-sample` (KEY, VALUE) " + " VALUES(" + "\"cust1293\", " + "{\"id\":\"1293\",\"name\":\"Sample Airline\", \"type\":\"airline\"})" + " RETURNING META().id as docid, *"; N1qlQueryResult r1 = bucket.query(N1qlQuery.simple(query)); r1.forEach(System.out::println);

This will return the id of the inserted document as docid separately and the complete document body separately:

{ "docid":"cust1293", "travel-sample":{ "id":"1293", "name":"Sample Airline", "type":"airline" } }

However, since we're using the Java SDK, we can do it the object way by creating a JsonDocument that is then inserted into the bucket via the Bucket API:

JsonObject ob = JsonObject.create() .put("id", "1293") .put("name", "Sample Airline") .put("type", "airline"); bucket.insert(JsonDocument.create("cust1295", ob));

Instead of using the insert() we can use upsert() which will update the document if there is an existing document with the same unique identifier cust1295.

As it is now, using insert() will throw an exception if that same unique id already exists.

The insert(), however, if successful, will return a JsonDocument that contains the unique id and entries of the inserted data.

The syntax for bulk insert using N1QL is:

INSERT INTO `travel-sample` ( KEY, VALUE ) VALUES("unique_key", { "id": "01", "type": "airline"}), VALUES("unique_key", { "id": "01", "type": "airline"}), VALUES("unique_n", { "id": "01", "type": "airline"}) RETURNING META().id as docid, *;

We can perform bulk operations with the Java SDK using Reactive Java that underlines the SDK. Let's add ten documents into a bucket using batch process:

List documents = IntStream.rangeClosed(0,10) .mapToObj( i -> { JsonObject content = JsonObject.create() .put("id", i) .put("type", "airline") .put("name", "Sample Airline " + i); return JsonDocument.create("cust_" + i, content); }).collect(Collectors.toList()); List r5 = Observable .from(documents) .flatMap(doc -> bucket.async().insert(doc)) .toList() .last() .toBlocking() .single(); r5.forEach(System.out::println);

First, we generate ten documents and put them into a List; then we used RxJava to perform the bulk operation.

Finally, we print out the result of each insert – which has been accumulated to form a List.

The reference for performing bulk operations in the Java SDK can be found here. Also, the reference for insert statement can be found here.

6.4. UPDATE Statement

N1QL also has UPDATE statement. It can update documents identified by their unique keys. We can use the update statement to either SET (update) values of an attribute or UNSET (remove) an attribute altogether.

Let's update one of the documents we recently inserted into the travel-sample bucket:

String query2 = "UPDATE `travel-sample` USE KEYS \"cust_1\" " + "SET name=\"Sample Airline Updated\" RETURNING name"; N1qlQueryResult result = bucket.query(N1qlQuery.simple(query2)); result.forEach(System.out::println);

In the above query, we updated the name attribute of a cust_1 entry in the bucket to Sample Airline Updated, and we instruct the query to return the updated name.

As stated earlier, we can also achieve the same thing by constructing a JsonDocument with the same id and use the upsert() of Bucket API to update the document:

JsonObject o2 = JsonObject.create() .put("name", "Sample Airline Updated"); bucket.upsert(JsonDocument.create("cust_1", o2));

In this next query, let's use the UNSET command to remove the name attribute and return the affected document:

String query3 = "UPDATE `travel-sample` USE KEYS \"cust_2\" " + "UNSET name RETURNING *"; N1qlQueryResult result1 = bucket.query(N1qlQuery.simple(query3)); result1.forEach(System.out::println);

The returned JSON string is:

{ "travel-sample":{ "id":2, "type":"airline" } }

Take note of the missing name attribute – it has been removed from the document object. N1QL update syntax reference can be found here.

So we have a look at inserting new documents and updating documents. Now let's look at the final piece of the CRUD acronym – DELETE.

6.5. DELETE Statement

Let's use the DELETE query to delete some of the documents we have created earlier. We'll use the unique id to identify the document with the USE KEYS keyword:

String query4 = "DELETE FROM `travel-sample` USE KEYS \"cust_50\""; N1qlQueryResult result4 = bucket.query(N1qlQuery.simple(query4));

N1QL DELETE statement also takes a WHERE clause. So we can use conditions to select the records to be deleted:

String query5 = "DELETE FROM `travel-sample` WHERE id = 0 RETURNING *"; N1qlQueryResult result5 = bucket.query(N1qlQuery.simple(query5));

We can also use the remove() from the bucket API directly:

bucket.remove("cust_2");

Much simpler right? Yes, but now we also know how to do it using N1QL. The reference doc for DELETE syntax can be found here.

7. N1QL Functions and Sub-Queries

N1QL did not just resemble SQL regarding syntax alone; it goes all the way to some functionalities. In SQL, we've some functions like COUNT() that can be used within the query string.

N1QL, in the same fashion, has its functions that can be used in the query string.

For example, this query will return the total number of landmark records that are in the travel-sample bucket:

SELECT COUNT(*) as landmark_count FROM `travel-sample` WHERE type = 'landmark'

In previous examples above, we've used the META function in UPDATE statement to return the id of updated document.

There are string method that can trim trailing white spaces, make lower and upper case letters and even check if a string contains a token. Let's use some of these functions in a query:

Let's use some of these functions in a query:

INSERT INTO `travel-sample` (KEY, VALUE) VALUES(LOWER(UUID()), {"id":LOWER(UUID()), "name":"Sample Airport Rand", "created_at": NOW_MILLIS()}) RETURNING META().id as docid, *

The query above inserts a new entry into the travel-sample bucket. It uses the UUID() function to generate a unique random id which was converted to lower case using the LOWER() function.

The NOW_MILLIS() method was used to set the current time, in milliseconds, as the value of the created_at attribute. The complete reference of N1QL functions can be found here.

Sub-queries come in handy at times, and N1QL has provision for them. Still using the travel-sample bucket, let's select the destination airport of all routes for a particular airline – and get the country they are located in:

SELECT DISTINCT country FROM `travel-sample` WHERE type = "airport" AND faa WITHIN (SELECT destinationairport FROM `travel-sample` t WHERE t.type = "route" and t.airlineid = "airline_10")

The sub-query in the above query is enclosed within parentheses and returns the destinationairport attribute, of all routes associated with airline_10, as a collection.

The destinationairport attributes correlate to the faa attribute on airport documents in the travel-sample bucket. The WITHIN keyword is part of collection operators in N1QL.

Now, that we've got the country of destination airport of all routes for airline_10. Let's do something interesting by looking for hotels within that country:

SELECT name, price, address, country FROM `travel-sample` h WHERE h.type = "hotel" AND h.country WITHIN (SELECT DISTINCT country FROM `travel-sample` WHERE type = "airport" AND faa WITHIN (SELECT destinationairport FROM `travel-sample` t WHERE t.type = "route" and t.airlineid = "airline_10" ) ) LIMIT 100

La requête précédente a été utilisée comme sous-requête dans la contrainte WHERE de la requête la plus externe. Prenez note du mot clé DISTINCT - il fait la même chose qu'en SQL - renvoie des données non dupliquées.

Tous les exemples de requête ici peuvent être exécutés à l'aide du SDK, comme illustré plus haut dans cet article.

8. Conclusion

N1QL amène le processus d'interrogation de la base de données documentaire comme Couchbase à un autre niveau. Cela ne simplifie pas seulement ce processus, cela facilite également le passage d'un système de base de données relationnelle.

Nous avons examiné la requête N1QL dans cet article; la documentation principale peut être trouvée ici. Et vous pouvez en savoir plus sur Spring Data Couchbase ici.

Comme toujours, le code source complet est disponible sur Github.