asterixdb-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
Subject asterixdb git commit: ASTERIXDB-1286 Add User Documentation for Upsert
Date Fri, 24 Jun 2016 10:47:07 GMT
Repository: asterixdb
Updated Branches:
  refs/heads/master cf7c4526a -> 7f3fb7925

ASTERIXDB-1286 Add User Documentation for Upsert

Documentation for upsert has been added. It includes multiple examples:
1. Example with a single record upsert.
2. Example with a query based upsert.
3. Example of an upsert with self query.
The documentation explains the transaction properties of upserts as well.

Change-Id: I677dbeb5cbc7e8a83aa50db0c3cd59f91f6a2e69
Reviewed-by: Jenkins <>
Tested-by: Jenkins <>
Reviewed-by: Till Westmann <>


Branch: refs/heads/master
Commit: 7f3fb79259276731f0bbd28dbf80876a9710d993
Parents: cf7c452
Author: Abdullah Alamoudi <>
Authored: Fri Jun 24 01:54:41 2016 +0300
Committer: Till Westmann <>
Committed: Fri Jun 24 03:46:37 2016 -0700

 .../asterix-doc/src/site/markdown/aql/ | 24 +++++++++-
 .../asterix-doc/src/site/markdown/aql/ | 49 ++++++++++++++++++++
 2 files changed, 72 insertions(+), 1 deletion(-)
diff --git a/asterixdb/asterix-doc/src/site/markdown/aql/ b/asterixdb/asterix-doc/src/site/markdown/aql/
index a7d1669..510bc83 100644
--- a/asterixdb/asterix-doc/src/site/markdown/aql/
+++ b/asterixdb/asterix-doc/src/site/markdown/aql/
@@ -480,11 +480,12 @@ It is useful to note that if the set were instead the empty set, the
first expre
                       | SetStatement
                       | InsertStatement
                       | DeleteStatement
+                      | UpsertStatement
                       | Query
 In addition to expresssions for queries, AQL supports a variety of statements for data
 definition and manipulation purposes as well as controlling the context to be used in
-evaluating AQL expressions. AQL supports record-level ACID transactions that begin and terminate
implicitly for each record inserted, deleted, or searched while a given AQL statement is being
+evaluating AQL expressions. AQL supports record-level ACID transactions that begin and terminate
implicitly for each record inserted, deleted, upserted, or searched while a given AQL statement
is being executed.
 This section details the statements supported in the AQL language.
@@ -861,6 +862,27 @@ The following example illustrates a single-object deletion.
     delete $user from dataset FacebookUsers where $ = 8;
+#### Upsert
+    UpsertStatement ::= "upsert" "into" "dataset" QualifiedName Query
+The AQL upsert statement is used to couple delete (if found) with insert data into a dataset.
+The data to be upserted comes from an AQL query expression.
+The expression can be as simple as a constant expression, or in general it can be any legal
AQL query.
+Upserts in AsterixDB are processed transactionally, with the scope of each upsert transaction
+being the upsertion (deletion if found + insertion) of a single object plus its affiliated
+secondary index entries (if any).
+If the query part of an upsert returns a single object, then the upsert statement itself
+be a single, atomic transaction.
+If the query part returns multiple objects, then each object upserted will be handled independently
+as a tranaction.
+The following example illustrates a query-based upsertion.
+##### Example
+    upsert into dataset Users (for $user in dataset FacebookUsers return $user)
 We close this guide to AQL with one final example of a query expression.
 ##### Example
diff --git a/asterixdb/asterix-doc/src/site/markdown/aql/ b/asterixdb/asterix-doc/src/site/markdown/aql/
index b1ffec7..d99ba6d 100644
--- a/asterixdb/asterix-doc/src/site/markdown/aql/
+++ b/asterixdb/asterix-doc/src/site/markdown/aql/
@@ -900,6 +900,55 @@ To achieve the effect of an update, two statements are currently needed---one
 dataset where it resides, and another to insert the new replacement record (with the same
primary key but with
 different field values for some of the associated data content).
+### Upserting Data  ###
+In addition to loading, querying, inserting, and deleting data, AsterixDB supports upserting
+records using the AQL _upsert_ statement.
+The following example deletes the tweet with the tweetid = 20 (if exists) and inserts the
+new tweet with tweetid=20 and the user "SwanSmitty" to the TweetMessages dataset. The two
+operations (delete if found and insert) are performed as an atomic operation that is either
+performed completely or not at all.
+        use dataverse TinySocial;
+        upsert into dataset TweetMessages
+        (
+           {"tweetid":"20",
+            "user":
+                {"screen-name":"SwanSmitty",
+                 "lang":"en",
+                 "friends_count":91345,
+                 "statuses_count":4079,
+                 "name":"Swanson Smith",
+                 "followers_count":50420
+                },
+            "sender-location":point("47.44,80.65"),
+            "send-time":datetime("2008-04-26T10:10:35"),
+            "referred-topics":{{"football"}},
+            "message-text":"football is the best sport, period.!"
+           }
+        );
+The data to be upserted may be specified using any valid AQL query expression.
+For example, the following statement might be used to double the followers count of all existing
+        use dataverse TinySocial;
+        upsert into dataset TweetUsers
+        (
+           for $user in dataset TweetUsers
+           return {
+            "screen-name":$user.screen-name,
+            "lang":$user.lang,
+            "friends_count":$user.friends_count,
+            "statuses_count":$user.statuses_count,
+            "name":$,
+            "followers_count":$user.followers_count*2
+           }
+        );
+Note that an upsert operation is executed in two steps, the query is performed,query locks
+are released, and then its result is upserted into the dataset. This means that the record
+can be modified between computing the query result and performing the upsert.
 ### Transaction Support
 AsterixDB supports record-level ACID transactions that begin and terminate implicitly for
each record inserted, deleted, or searched while a given AQL statement is being executed.
This is quite similar to the level of transaction support found in today's NoSQL stores. AsterixDB
does not support multi-statement transactions, and in fact an AQL statement that involves
multiple records can itself involve multiple independent record-level transactions. An example
consequence of this is that, when an AQL statement attempts to insert 1000 records, it is
possible that the first 800 records could end up being committed while the remaining 200 records
fail to be inserted. This situation could happen, for example, if a duplicate key exception
occurs as the 801st insertion is attempted. If this happens, AsterixDB will report the error
(e.g., a duplicate key exception) as the result of the offending AQL insert statement, and
the application logic above will need to take the appropriate action(s
 ) needed to assess the resulting state and to clean up and/or continue as appropriate.

View raw message