http://git-wip-us.apache.org/repos/asf/asterixdb-site/blob/bf2c56b0/docs/0.9.0/sqlpp/primer-sqlpp.html ---------------------------------------------------------------------- diff --git a/docs/0.9.0/sqlpp/primer-sqlpp.html b/docs/0.9.0/sqlpp/primer-sqlpp.html new file mode 100644 index 0000000..3e4ac57 --- /dev/null +++ b/docs/0.9.0/sqlpp/primer-sqlpp.html @@ -0,0 +1,941 @@ +<!DOCTYPE html> +<!-- + | Generated by Apache Maven Doxia at 2017-01-24 + | Rendered using Apache Maven Fluido Skin 1.3.0 +--> +<html xmlns="http://www.w3.org/1999/xhtml"; xml:lang="en" lang="en"> + <head> + <meta charset="UTF-8" /> + <meta name="viewport" content="width=device-width, initial-scale=1.0" /> + <meta name="Date-Revision-yyyymmdd" content="20170124" /> + <meta http-equiv="Content-Language" content="en" /> + <title>AsterixDB – AsterixDB 101: An ADM and SQL++ Primer</title> + <link rel="stylesheet" href="../css/apache-maven-fluido-1.3.0.min.css" /> + <link rel="stylesheet" href="../css/site.css" /> + <link rel="stylesheet" href="../css/print.css" media="print" /> + + + <script type="text/javascript" src="../js/apache-maven-fluido-1.3.0.min.js"></script> + + + +<script>(function(i,s,o,g,r,a,m){i['GoogleAnalyticsObject']=r;i[r]=i[r]||function(){ + (i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new Date();a=s.createElement(o), + m=s.getElementsByTagName(o)[0];a.async=1;a.src=g;m.parentNode.insertBefore(a,m) + })(window,document,'script','//www.google-analytics.com/analytics.js','ga'); + + ga('create', 'UA-41536543-1', 'uci.edu'); + ga('send', 'pageview');</script> + + </head> + <body class="topBarDisabled"> + + + + + <div class="container-fluid"> + <div id="banner"> + <div class="pull-left"> + <a href=".././" id="bannerLeft"> + <img src="../images/asterixlogo.png" alt="AsterixDB"/> + </a> + </div> + <div class="pull-right"> </div> + <div class="clear"><hr/></div> + </div> + + <div id="breadcrumbs"> + <ul class="breadcrumb"> + + + <li id="publishDate">Last Published: 2017-01-24</li> + + + + <li id="projectVersion" class="pull-right">Version: 0.9.0</li> + + <li class="divider pull-right">|</li> + + <li class="pull-right"> <a href="../index.html" title="Documentation Home"> + Documentation Home</a> + </li> + + </ul> + </div> + + + <div class="row-fluid"> + <div id="leftColumn" class="span3"> + <div class="well sidebar-nav"> + + + <ul class="nav nav-list"> + <li class="nav-header">Get Started - Installation</li> + + <li> + + <a href="../ncservice.html" title="Option 1: using NCService"> + <i class="none"></i> + Option 1: using NCService</a> + </li> + + <li> + + <a href="../install.html" title="Option 2: using Managix"> + <i class="none"></i> + Option 2: using Managix</a> + </li> + + <li> + + <a href="../yarn.html" title="Option 3: using YARN"> + <i class="none"></i> + Option 3: using YARN</a> + </li> + <li class="nav-header">AsterixDB Primer</li> + + <li class="active"> + + <a href="#"><i class="none"></i>Option 1: using SQL++</a> + </li> + + <li> + + <a href="../aql/primer.html" title="Option 2: using AQL"> + <i class="none"></i> + Option 2: using AQL</a> + </li> + <li class="nav-header">Data Model</li> + + <li> + + <a href="../datamodel.html" title="The Asterix Data Model"> + <i class="none"></i> + The Asterix Data Model</a> + </li> + <li class="nav-header">Queries - SQL++</li> + + <li> + + <a href="../sqlpp/manual.html" title="The SQL++ Query Language"> + <i class="none"></i> + The SQL++ Query Language</a> + </li> + + <li> + + <a href="../sqlpp/builtins.html" title="Builtin Functions"> + <i class="none"></i> + Builtin Functions</a> + </li> + <li class="nav-header">Queries - AQL</li> + + <li> + + <a href="../aql/manual.html" title="The Asterix Query Language (AQL)"> + <i class="none"></i> + The Asterix Query Language (AQL)</a> + </li> + + <li> + + <a href="../aql/builtins.html" title="Builtin Functions"> + <i class="none"></i> + Builtin Functions</a> + </li> + <li class="nav-header">Advanced Features</li> + + <li> + + <a href="../aql/similarity.html" title="Support of Similarity Queries"> + <i class="none"></i> + Support of Similarity Queries</a> + </li> + + <li> + + <a href="../aql/fulltext.html" title="Support of Full-text Queries"> + <i class="none"></i> + Support of Full-text Queries</a> + </li> + + <li> + + <a href="../aql/externaldata.html" title="Accessing External Data"> + <i class="none"></i> + Accessing External Data</a> + </li> + + <li> + + <a href="../feeds/tutorial.html" title="Support for Data Ingestion"> + <i class="none"></i> + Support for Data Ingestion</a> + </li> + + <li> + + <a href="../udf.html" title="User Defined Functions"> + <i class="none"></i> + User Defined Functions</a> + </li> + + <li> + + <a href="../aql/filters.html" title="Filter-Based LSM Index Acceleration"> + <i class="none"></i> + Filter-Based LSM Index Acceleration</a> + </li> + <li class="nav-header">API/SDK</li> + + <li> + + <a href="../api.html" title="HTTP API"> + <i class="none"></i> + HTTP API</a> + </li> + </ul> + + + + <hr class="divider" /> + + <div id="poweredBy"> + <div class="clear"></div> + <div class="clear"></div> + <div class="clear"></div> + <a href=".././" title="AsterixDB" class="builtBy"> + <img class="builtBy" alt="AsterixDB" src="../images/asterixlogo.png" /> + </a> + </div> + </div> + </div> + + + <div id="bodyColumn" class="span9" > + + <!-- ! Licensed to the Apache Software Foundation (ASF) under one + ! or more contributor license agreements. See the NOTICE file + ! distributed with this work for additional information + ! regarding copyright ownership. The ASF licenses this file + ! to you under the Apache License, Version 2.0 (the + ! "License"); you may not use this file except in compliance + ! with the License. You may obtain a copy of the License at + ! + ! http://www.apache.org/licenses/LICENSE-2.0 + ! + ! Unless required by applicable law or agreed to in writing, + ! software distributed under the License is distributed on an + ! "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + ! KIND, either express or implied. See the License for the + ! specific language governing permissions and limitations + ! under the License. + ! --><h1>AsterixDB 101: An ADM and SQL++ Primer</h1> +<div class="section"> +<h2><a name="Welcome_to_AsterixDB"></a>Welcome to AsterixDB!</h2> +<p>This document introduces the main features of AsterixDB’s data model (ADM) and its new SQL-like query language (SQL++) by example. The example is a simple scenario involving (synthetic) sample data modeled after data from the social domain. This document describes a set of sample datasets, together with a set of illustrative queries, to introduce you to the “AsterixDB user experience”. The complete set of steps required to create and load a handful of sample datasets, along with runnable queries and the expected results for each query, are included.</p> +<p>This document assumes that you are at least vaguely familiar with AsterixDB and why you might want to use it. Most importantly, it assumes you already have a running instance of AsterixDB and that you know how to query it using AsterixDB’s basic web interface. For more information on these topics, you should go through the steps in <a href="../install.html">Installing Asterix Using Managix</a> before reading this document and make sure that you have a running AsterixDB instance ready to go. To get your feet wet, you should probably start with a simple local installation of AsterixDB on your favorite machine, accepting all of the default settings that Managix offers. Later you can graduate to trying AsterixDB on a cluster, its real intended home (since it targets Big Data). (Note: With the exception of specifying the correct locations where you put the source data for this example, there should no changes needed in the SQL++ statements to run the examples locally and/or to run them on a cluster when you are ready to take that step.)</p> +<p>As you read through this document, you should try each step for yourself on your own AsterixDB instance. You will use the AsterixDB web interface to do this, and for SQL++ you will need to select SQL++ instead of AQL as your language of choice in the Query Language box that sits underneath the UI’s query entry area. Once you have reached the end of this tutorial, you will be fully armed and dangerous, with all the basic AsterixDB knowledge that you’ll need to start down the path of modeling, storing, and querying your own semistructured data.</p></div> +<div class="section"> +<h2><a name="ADM:_Modeling_Semistructed_Data_in_AsterixDB"></a>ADM: Modeling Semistructed Data in AsterixDB</h2> +<p>In this section you will learn all about modeling Big Data using ADM, the data model of the AsterixDB BDMS.</p> +<div class="section"> +<h3><a name="Dataverses_Datatypes_and_Datasets"></a>Dataverses, Datatypes, and Datasets</h3> +<p>The top-level organizing concept in the AsterixDB world is the <i>dataverse</i>. A dataverse—short for “data universe”—is a place (similar to a database in a relational DBMS) in which to create and manage the types, datasets, functions, and other artifacts for a given AsterixDB application. When you start using an AsterixDB instance for the first time, it starts out “empty”; it contains no data other than the AsterixDB system catalogs (which live in a special dataverse called the Metadata dataverse). To store your data in AsterixDB, you will first create a dataverse and then you use it for the <i>datatypes</i> and <i>datasets</i> for managing your own data. A datatype tells AsterixDB what you know (or more accurately, what you want it to know) a priori about one of the kinds of data instances that you want AsterixDB to hold for you. A dataset is a collection of data instances of a datatype, and AsterixDB makes sure that the data instances that you put in it conform to its specified type. Since AsterixDB targets semistructured data, you can use <i>open</i> datatypes and tell it as little or as much as you wish about your data up front; the more you tell it up front, the less information it will have to store repeatedly in the individual data instances that you give it. Instances of open datatypes are permitted to have additional content, beyond what the datatype says, as long as they at least contain the information prescribed by the datatype definition. Open typing allows data to vary from one instance to another and it leaves wiggle room for application evolution in terms of what might need to be stored in the future. If you want to restrict data instances in a dataset to have only what the datatype says, and nothing extra, you can define a <i>closed</i> datatype for that dataset and AsterixDB will keep users from storing objects that have extra data in them. Datatypes are open by default unless you tell AsterixDB otherwise. Let’s put these concepts to work.</p> +<p>Our little sample scenario involves information about users of two hypothetical social networks, Gleambook and Chirp, and their messages. We’ll start by defining a dataverse called “TinySocial” to hold our datatypes and datasets. The AsterixDB data model (ADM) is essentially a superset of JSON—it’s what you get by extending JSON with more data types and additional data modeling constructs borrowed from object databases. The following shows how we can create the TinySocial dataverse plus a set of ADM types for modeling Chirp users, their Chirps, Gleambook users, their users’ employment information, and their messages. (Note: Keep in mind that this is just a tiny and somewhat silly example intended for illustrating some of the key features of AsterixDB. :-)) As a point of information, SQL++ is case-insensitive for both keywords and built-in type names, so the exact style of the examples below is just one of a number of possibilities.</p> + +<div class="source"> +<div class="source"> +<pre> DROP DATAVERSE TinySocial IF EXISTS; + CREATE DATAVERSE TinySocial; + USE TinySocial; + + CREATE TYPE ChirpUserType AS { + screenName: string, + lang: string, + friendsCount: int, + statusesCount: int, + name: string, + followersCount: int + }; + + CREATE TYPE ChirpMessageType AS closed { + chirpId: string, + user: ChirpUserType, + senderLocation: point?, + sendTime: datetime, + referredTopics: {{ string }}, + messageText: string + }; + + CREATE TYPE EmploymentType AS { + organizationName: string, + startDate: date, + endDate: date? + }; + + CREATE TYPE GleambookUserType AS { + id: int, + alias: string, + name: string, + userSince: datetime, + friendIds: {{ int }}, + employment: [EmploymentType] + }; + + CREATE TYPE GleambookMessageType AS { + messageId: int, + authorId: int, + inResponseTo: int?, + senderLocation: point?, + message: string + }; +</pre></div></div> +<p>The first three lines above tell AsterixDB to drop the old TinySocial dataverse, if one already exists, and then to create a brand new one and make it the focus of the statements that follow. The first <i>CREATE TYPE</i> statement creates a datatype for holding information about Chirp users. It is a object type with a mix of integer and string data, very much like a (flat) relational tuple. The indicated fields are all mandatory, but because the type is open, additional fields are welcome. The second statement creates a datatype for Chirp messages; this shows how to specify a closed type. Interestingly (based on one of Chirp’s APIs), each Chirp message actually embeds an instance of the sending user’s information (current as of when the message was sent), so this is an example of a nested object in ADM. Chirp messages can optionally contain the sender’s location, which is modeled via the senderLocation field of spatial type <i>point</i>; the question mark fol lowing the field type indicates its optionality. An optional field is like a nullable field in SQL—it may be present or missing, but when it’s present, its value’s data type will conform to the datatype’s specification. The sendTime field illustrates the use of a temporal primitive type, <i>datetime</i>. Lastly, the referredTopics field illustrates another way that ADM is richer than the relational model; this field holds a bag (<i>a.k.a.</i> an unordered list) of strings. Since the overall datatype definition for Chirp messages says “closed”, the fields that it lists are the only fields that instances of this type will be allowed to contain. The next two <i>CREATE TYPE</i> statements create a object type for holding information about one component of the employment history of a Gleambook user and then a object type for holding the user information itself. The Gleambook user type highlights a few additional ADM data model features. Its frien dIds field is a bag of integers, presumably the Gleambook user ids for this user’s friends, and its employment field is an ordered list of employment objects. The final <i>CREATE TYPE</i> statement defines a type for handling the content of a Gleambook message in our hypothetical social data storage scenario.</p> +<p>Before going on, we need to once again emphasize the idea that AsterixDB is aimed at storing and querying not just Big Data, but Big <i>Semistructured</i> Data. This means that most of the fields listed in the <i>CREATE TYPE</i> statements above could have been omitted without changing anything other than the resulting size of stored data instances on disk. AsterixDB stores its information about the fields defined a priori as separate metadata, whereas the information about other fields that are “just there” in instances of open datatypes is stored with each instance—making for more bits on disk and longer times for operations affected by data size (e.g., dataset scans). The only fields that <i>must</i> be specified a priori are the primary key fields of each dataset.</p></div> +<div class="section"> +<h3><a name="Creating_Datasets_and_Indexes"></a>Creating Datasets and Indexes</h3> +<p>Now that we have defined our datatypes, we can move on and create datasets to store the actual data. (If we wanted to, we could even have several named datasets based on any one of these datatypes.) We can do this as follows, utilizing the SQL++ DDL capabilities of AsterixDB.</p> + +<div class="source"> +<div class="source"> +<pre> USE TinySocial; + + CREATE DATASET GleambookUsers(GleambookUserType) + PRIMARY KEY id; + + CREATE DATASET GleambookMessages(GleambookMessageType) + PRIMARY KEY messageId; + + CREATE DATASET ChirpUsers(ChirpUserType) + PRIMARY KEY screenName; + + CREATE DATASET ChirpMessages(ChirpMessageType) + PRIMARY KEY chirpId + hints(cardinality=100); + + CREATE INDEX gbUserSinceIdx on GleambookUsers(userSince); + CREATE INDEX gbAuthorIdx on GleambookMessages(authorId) TYPE btree; + CREATE INDEX gbSenderLocIndex on GleambookMessages(senderLocation) TYPE rtree; + CREATE INDEX gbMessageIdx on GleambookMessages(message) TYPE keyword; + + SELECT VALUE ds FROM Metadata.`Dataset` ds; + SELECT VALUE ix FROM Metadata.`Index` ix; +</pre></div></div> +<p>The SQL++ DDL statements above create four datasets for holding our social data in the TinySocial dataverse: GleambookUsers, GleambookMessages, ChirpUsers, and ChirpMessages. The first <i>CREATE DATASET</i> statement creates the GleambookUsers data set. It specifies that this dataset will store data instances conforming to GleambookUserType and that it has a primary key which is the id field of each instance. The primary key information is used by AsterixDB to uniquely identify instances for the purpose of later lookup and for use in secondary indexes. Each AsterixDB dataset is stored (and indexed) in the form of a B+ tree on primary key; secondary indexes point to their indexed data by primary key. In AsterixDB clusters, the primary key is also used to hash-partition (<i>a.k.a.</i> shard) the dataset across the nodes of the cluster. The next three <i>CREATE DATASET</i> statements are similar. The last one illustrates an optional clause for providing useful hints to AsterixDB. In this case, the hint tells AsterixDB that the dataset definer is anticipating that the ChirpMessages dataset will contain roughly 100 objects; knowing this can help AsterixDB to more efficiently manage and query this dataset. (AsterixDB does not yet gather and maintain data statistics; it will currently, abitrarily, assume a cardinality of one million objects per dataset in the absence of such an optional definition-time hint.)</p> +<p>The <i>CREATE DATASET</i> statements above are followed by four more DDL statements, each of which creates a secondary index on a field of one of the datasets. The first one indexes the GleambookUsers dataset on its user-since field. This index will be a B+ tree index; its type is unspecified and <i>btree</i> is the default type. The other three illustrate how you can explicitly specify the desired type of index. In addition to btree, <i>rtree</i> and inverted <i>keyword</i> indexes are supported by AsterixDB. Indexes can also have composite keys, and more advanced text indexing is available as well (ngram(k), where k is the desired gram length).</p></div> +<div class="section"> +<h3><a name="Querying_the_Metadata_Dataverse"></a>Querying the Metadata Dataverse</h3> +<p>The last two statements above show how you can use queries in SQL++ to examine the AsterixDB system catalogs and tell what artifacts you have created. Just as relational DBMSs use their own tables to store their catalogs, AsterixDB uses its own datasets to persist descriptions of its datasets, datatypes, indexes, and so on. Running the first of the two queries above will list all of your newly created datasets, and it will also show you a full list of all the metadata datasets. (You can then explore from there on your own if you are curious) These last two queries also illustrate a few other factoids worth knowing: First, AsterixDB allows queries to span dataverses via the use of fully-qualified dataset names (i.e., <i>dataversename.datasetname</i>) to reference datasets that live in a dataverse other than the one referenced in the most recently executed <i>USE</i> directive. Second, they show how to escape SQL++ keywords (or other special names) in object names by using backquot es. Last but not least, they show that SQL++ supports a <i>SELECT VALUE</i> variation of SQL’s traditional <i>SELECT</i> statement that returns a single value (or element) from a query instead of constructing a new object as the query’s result like <i>SELECT</i> does; here, the returned value is an entire object from the dataset being queried (e.g., <i>SELECT VALUE ds</i> in the first statement returns the entire object from the metadata dataset containing the descriptions of all datasets.</p></div></div> +<div class="section"> +<h2><a name="Loading_Data_Into_AsterixDB"></a>Loading Data Into AsterixDB</h2> +<p>Okay, so far so good—AsterixDB is now ready for data, so let’s give it some data to store. Our next task will be to load some sample data into the four datasets that we just defined. Here we will load a tiny set of objects, defined in ADM format (a superset of JSON), into each dataset. In the boxes below you can see the actual data instances contained in each of the provided sample files. In order to load this data yourself, you should first store the four corresponding <tt>.adm</tt> files (whose URLs are indicated on top of each box below) into a filesystem directory accessible to your running AsterixDB instance. Take a few minutes to look carefully at each of the sample data sets. This will give you a better sense of the nature of the data that we are about to load and query. We should note that ADM format is a textual serialization of what AsterixDB will actually store; when persisted in AsterixDB, the data format will be binary and the data in the predefined fie lds of the data instances will be stored separately from their associated field name and type metadata.</p> +<p><a href="../data/chu.adm">Chirp Users</a></p> + +<div class="source"> +<div class="source"> +<pre> {"screenName":"NathanGiesen@211","lang":"en","friendsCount":18,"statusesCount":473,"name":"Nathan Giesen","followersCount":49416} + {"screenName":"ColineGeyer@63","lang":"en","friendsCount":121,"statusesCount":362,"name":"Coline Geyer","followersCount":17159} + {"screenName":"NilaMilliron_tw","lang":"en","friendsCount":445,"statusesCount":164,"name":"Nila Milliron","followersCount":22649} + {"screenName":"ChangEwing_573","lang":"en","friendsCount":182,"statusesCount":394,"name":"Chang Ewing","followersCount":32136} +</pre></div></div> +<p><a href="../data/chm.adm">Chirp Messages</a></p> + +<div class="source"> +<div class="source"> +<pre> {"chirpId":"1","user":{"screenName":"NathanGiesen@211","lang":"en","friendsCount":39339,"statusesCount":473,"name":"Nathan Giesen","followersCount":49416},"senderLocation":point("47.44,80.65"),"sendTime":datetime("2008-04-26T10:10:00"),"referredTopics":{{"t-mobile","customization"}},"messageText":" love t-mobile its customization is good:)"} + {"chirpId":"2","user":{"screenName":"ColineGeyer@63","lang":"en","friendsCount":121,"statusesCount":362,"name":"Coline Geyer","followersCount":17159},"senderLocation":point("32.84,67.14"),"sendTime":datetime("2010-05-13T10:10:00"),"referredTopics":{{"verizon","shortcut-menu"}},"messageText":" like verizon its shortcut-menu is awesome:)"} + {"chirpId":"3","user":{"screenName":"NathanGiesen@211","lang":"en","friendsCount":39339,"statusesCount":473,"name":"Nathan Giesen","followersCount":49416},"senderLocation":point("29.72,75.8"),"sendTime":datetime("2006-11-04T10:10:00"),"referredTopics":{{"motorola","speed"}},"messageText":" like motorola the speed is good:)"} + {"chirpId":"4","user":{"screenName":"NathanGiesen@211","lang":"en","friendsCount":39339,"statusesCount":473,"name":"Nathan Giesen","followersCount":49416},"senderLocation":point("39.28,70.48"),"sendTime":datetime("2011-12-26T10:10:00"),"referredTopics":{{"sprint","voice-command"}},"messageText":" like sprint the voice-command is mind-blowing:)"} + {"chirpId":"5","user":{"screenName":"NathanGiesen@211","lang":"en","friendsCount":39339,"statusesCount":473,"name":"Nathan Giesen","followersCount":49416},"senderLocation":point("40.09,92.69"),"sendTime":datetime("2006-08-04T10:10:00"),"referredTopics":{{"motorola","speed"}},"messageText":" can't stand motorola its speed is terrible:("} + {"chirpId":"6","user":{"screenName":"ColineGeyer@63","lang":"en","friendsCount":121,"statusesCount":362,"name":"Coline Geyer","followersCount":17159},"senderLocation":point("47.51,83.99"),"sendTime":datetime("2010-05-07T10:10:00"),"referredTopics":{{"iphone","voice-clarity"}},"messageText":" like iphone the voice-clarity is good:)"} + {"chirpId":"7","user":{"screenName":"ChangEwing_573","lang":"en","friendsCount":182,"statusesCount":394,"name":"Chang Ewing","followersCount":32136},"senderLocation":point("36.21,72.6"),"sendTime":datetime("2011-08-25T10:10:00"),"referredTopics":{{"samsung","platform"}},"messageText":" like samsung the platform is good"} + {"chirpId":"8","user":{"screenName":"NathanGiesen@211","lang":"en","friendsCount":39339,"statusesCount":473,"name":"Nathan Giesen","followersCount":49416},"senderLocation":point("46.05,93.34"),"sendTime":datetime("2005-10-14T10:10:00"),"referredTopics":{{"t-mobile","shortcut-menu"}},"messageText":" like t-mobile the shortcut-menu is awesome:)"} + {"chirpId":"9","user":{"screenName":"NathanGiesen@211","lang":"en","friendsCount":39339,"statusesCount":473,"name":"Nathan Giesen","followersCount":49416},"senderLocation":point("36.86,74.62"),"sendTime":datetime("2012-07-21T10:10:00"),"referredTopics":{{"verizon","voicemail-service"}},"messageText":" love verizon its voicemail-service is awesome"} + {"chirpId":"10","user":{"screenName":"ColineGeyer@63","lang":"en","friendsCount":121,"statusesCount":362,"name":"Coline Geyer","followersCount":17159},"senderLocation":point("29.15,76.53"),"sendTime":datetime("2008-01-26T10:10:00"),"referredTopics":{{"verizon","voice-clarity"}},"messageText":" hate verizon its voice-clarity is OMG:("} + {"chirpId":"11","user":{"screenName":"NilaMilliron_tw","lang":"en","friendsCount":445,"statusesCount":164,"name":"Nila Milliron","followersCount":22649},"senderLocation":point("37.59,68.42"),"sendTime":datetime("2008-03-09T10:10:00"),"referredTopics":{{"iphone","platform"}},"messageText":" can't stand iphone its platform is terrible"} + {"chirpId":"12","user":{"screenName":"OliJackson_512","lang":"en","friendsCount":445,"statusesCount":164,"name":"Oli Jackson","followersCount":22649},"senderLocation":point("24.82,94.63"),"sendTime":datetime("2010-02-13T10:10:00"),"referredTopics":{{"samsung","voice-command"}},"messageText":" like samsung the voice-command is amazing:)"} +</pre></div></div> +<p><a href="../data/gbu.adm">Gleambook Users</a></p> + +<div class="source"> +<div class="source"> +<pre> {"id":1,"alias":"Margarita","name":"MargaritaStoddard","nickname":"Mags","userSince":datetime("2012-08-20T10:10:00"),"friendIds":{{2,3,6,10}},"employment":[{"organizationName":"Codetechno","startDate":date("2006-08-06")},{"organizationName":"geomedia","startDate":date("2010-06-17"),"endDate":date("2010-01-26")}],"gender":"F"} + {"id":2,"alias":"Isbel","name":"IsbelDull","nickname":"Izzy","userSince":datetime("2011-01-22T10:10:00"),"friendIds":{{1,4}},"employment":[{"organizationName":"Hexviafind","startDate":date("2010-04-27")}]} + {"id":3,"alias":"Emory","name":"EmoryUnk","userSince":datetime("2012-07-10T10:10:00"),"friendIds":{{1,5,8,9}},"employment":[{"organizationName":"geomedia","startDate":date("2010-06-17"),"endDate":date("2010-01-26")}]} + {"id":4,"alias":"Nicholas","name":"NicholasStroh","userSince":datetime("2010-12-27T10:10:00"),"friendIds":{{2}},"employment":[{"organizationName":"Zamcorporation","startDate":date("2010-06-08")}]} + {"id":5,"alias":"Von","name":"VonKemble","userSince":datetime("2010-01-05T10:10:00"),"friendIds":{{3,6,10}},"employment":[{"organizationName":"Kongreen","startDate":date("2010-11-27")}]} + {"id":6,"alias":"Willis","name":"WillisWynne","userSince":datetime("2005-01-17T10:10:00"),"friendIds":{{1,3,7}},"employment":[{"organizationName":"jaydax","startDate":date("2009-05-15")}]} + {"id":7,"alias":"Suzanna","name":"SuzannaTillson","userSince":datetime("2012-08-07T10:10:00"),"friendIds":{{6}},"employment":[{"organizationName":"Labzatron","startDate":date("2011-04-19")}]} + {"id":8,"alias":"Nila","name":"NilaMilliron","userSince":datetime("2008-01-01T10:10:00"),"friendIds":{{3}},"employment":[{"organizationName":"Plexlane","startDate":date("2010-02-28")}]} + {"id":9,"alias":"Woodrow","name":"WoodrowNehling","nickname":"Woody","userSince":datetime("2005-09-20T10:10:00"),"friendIds":{{3,10}},"employment":[{"organizationName":"Zuncan","startDate":date("2003-04-22"),"endDate":date("2009-12-13")}]} + {"id":10,"alias":"Bram","name":"BramHatch","userSince":datetime("2010-10-16T10:10:00"),"friendIds":{{1,5,9}},"employment":[{"organizationName":"physcane","startDate":date("2007-06-05"),"endDate":date("2011-11-05")}]} +</pre></div></div> +<p><a href="../data/gbm.adm">Gleambook Messages</a></p> + +<div class="source"> +<div class="source"> +<pre> {"messageId":1,"authorId":3,"inResponseTo":2,"senderLocation":point("47.16,77.75"),"message":" love sprint its shortcut-menu is awesome:)"} + {"messageId":2,"authorId":1,"inResponseTo":4,"senderLocation":point("41.66,80.87"),"message":" dislike iphone its touch-screen is horrible"} + {"messageId":3,"authorId":2,"inResponseTo":4,"senderLocation":point("48.09,81.01"),"message":" like samsung the plan is amazing"} + {"messageId":4,"authorId":1,"inResponseTo":2,"senderLocation":point("37.73,97.04"),"message":" can't stand at&t the network is horrible:("} + {"messageId":5,"authorId":6,"inResponseTo":2,"senderLocation":point("34.7,90.76"),"message":" love sprint the customization is mind-blowing"} + {"messageId":6,"authorId":2,"inResponseTo":1,"senderLocation":point("31.5,75.56"),"message":" like t-mobile its platform is mind-blowing"} + {"messageId":7,"authorId":5,"inResponseTo":15,"senderLocation":point("32.91,85.05"),"message":" dislike sprint the speed is horrible"} + {"messageId":8,"authorId":1,"inResponseTo":11,"senderLocation":point("40.33,80.87"),"message":" like verizon the 3G is awesome:)"} + {"messageId":9,"authorId":3,"inResponseTo":12,"senderLocation":point("34.45,96.48"),"message":" love verizon its wireless is good"} + {"messageId":10,"authorId":1,"inResponseTo":12,"senderLocation":point("42.5,70.01"),"message":" can't stand motorola the touch-screen is terrible"} + {"messageId":11,"authorId":1,"inResponseTo":1,"senderLocation":point("38.97,77.49"),"message":" can't stand at&t its plan is terrible"} + {"messageId":12,"authorId":10,"inResponseTo":6,"senderLocation":point("42.26,77.76"),"message":" can't stand t-mobile its voicemail-service is OMG:("} + {"messageId":13,"authorId":10,"inResponseTo":4,"senderLocation":point("42.77,78.92"),"message":" dislike iphone the voice-command is bad:("} + {"messageId":14,"authorId":9,"inResponseTo":12,"senderLocation":point("41.33,85.28"),"message":" love at&t its 3G is good:)"} + {"messageId":15,"authorId":7,"inResponseTo":11,"senderLocation":point("44.47,67.11"),"message":" like iphone the voicemail-service is awesome"} +</pre></div></div> +<p>It’s loading time! We can use SQL++ <i>LOAD</i> statements to populate our datasets with the sample objects shown above. The following shows how loading can be done for data stored in <tt>.adm</tt> files in your local filesystem. <i>Note:</i> You <i>MUST</i> replace the <tt><Host Name></tt> and <tt><Absolute File Path></tt> placeholders in each load statement below with valid values based on the host IP address (or host name) for the machine and directory that you have downloaded the provided <tt>.adm</tt> files to. As you do so, be very, very careful to retain the two slashes in the load statements, i.e., do not delete the two slashes that appear in front of the absolute path to your <tt>.adm</tt> files. (This will lead to a three-slash character sequence at the start of each load statement’s file input path specification.)</p> + +<div class="source"> +<div class="source"> +<pre> USE TinySocial; + + LOAD DATASET GleambookUsers USING localfs + (("path"="<Host Name>://<Absolute File Path>/gbu.adm"),("format"="adm")); + + LOAD DATASET GleambookMessages USING localfs + (("path"="<Host Name>://<Absolute File Path>/gbm.adm"),("format"="adm")); + + LOAD DATASET ChirpUsers USING localfs + (("path"="<Host Name>://<Absolute File Path>/chu.adm"),("format"="adm")); + + LOAD DATASET ChirpMessages USING localfs + (("path"="<Host Name>://<Absolute File Path>/chm.adm"),("format"="adm")); +</pre></div></div></div> +<div class="section"> +<h2><a name="SQL:_Querying_Your_AsterixDB_Data"></a>SQL++: Querying Your AsterixDB Data</h2> +<p>Congratulations! You now have sample social data stored (and indexed) in AsterixDB. (You are part of an elite and adventurous group of individuals. :-)) Now that you have successfully loaded the provided sample data into the datasets that we defined, you can start running queries against them.</p> +<p>AsterixDB currently supports two query languages. The first—AsterixDB’s original query language—is AQL (the Asterix Query Language). The AQL language was inspired by XQuery, the W3C standard language for querying XML data. (There is a version of this tutorial for AQL if you would like to learn more about it.) The query language described in the remainder of this tutorial is SQL++, a SQL-inspired language designed (as AQL was) for working with semistructured data. SQL++ has much in common with SQL, but there are differences due to the data model that SQL++ is designed to serve. SQL was designed in the 1970’s to interact with the flat, schema-ified world of relational databases. SQL++ is designed for the nested, schema-less (or schema-optional, in AsterixDB) world of NoSQL systems. While SQL++ has the same expressive power as AQL, it offers a more familar paradigm for experienced SQL users to use to query and manipulate data in AsterixDB.</p> +<p>In this section we introduce SQL++ via a set of example queries, along with their expected results, based on the data above, to help you get started. Many of the most important features of SQL++ are presented in this set of representative queries. You can find more details in the document on the <a href="datamodel.html">Asterix Data Model (ADM)</a>, in the <a href="manual-sqlpp.html">SQL++ Reference Manual</a>, and a complete list of built-in functions is available in the <a href="functions-sqlpp.html">SQL++ Functions</a> document.</p> +<p>SQL++ is an expression language. Even the simple expression 1+1 is a valid SQL++ query that evaluates to 2. (Try it for yourself! Okay, maybe that’s <i>not</i> the best use of a 512-node shared-nothing compute cluster.) But enough talk! Let’s go ahead and try writing some queries and see about learning SQL++ by example. (Again, don’t forget to choose SQL++ as the query language in the web interface!)</p> +<div class="section"> +<h3><a name="Query_0-A_-_Exact-Match_Lookup"></a>Query 0-A - Exact-Match Lookup</h3> +<p>For our first query, let’s find a Gleambook user based on his or her user id. Suppose the user we want is the user whose id is 8:</p> + +<div class="source"> +<div class="source"> +<pre> USE TinySocial; + + SELECT VALUE user + FROM GleambookUsers user + WHERE user.id = 8; +</pre></div></div> +<p>As in SQL, the query’s <i>FROM</i> clause binds the variable <tt>user</tt> incrementally to the data instances residing in the dataset named GleambookUsers. Its <i>WHERE</i> clause selects only those bindings having a user id of interest, filtering out the rest. The <i>SELECT</i> <i>VALUE</i> clause returns the (entire) data value (a Gleambook user object in this case) for each binding that satisfies the predicate. Since this dataset is indexed on user id (its primary key), this query will be done via a quick index lookup.</p> +<p>The expected result for our sample data is as follows:</p> + +<div class="source"> +<div class="source"> +<pre> { "id": 8, "alias": "Nila", "name": "NilaMilliron", "userSince": datetime("2008-01-01T10:10:00.000Z"), "friendIds": {{ 3 }}, "employment": [ { "organizationName": "Plexlane", "startDate": date("2010-02-28") } ] } +</pre></div></div></div> +<div class="section"> +<h3><a name="Query_0-B_-_Range_Scan"></a>Query 0-B - Range Scan</h3> +<p>SQL++, like SQL, supports a variety of different predicates. For example, for our next query, let’s find the Gleambook users whose ids are in the range between 2 and 4:</p> + +<div class="source"> +<div class="source"> +<pre> USE TinySocial; + + SELECT VALUE user + FROM GleambookUsers user + WHERE user.id >= 2 AND user.id <= 4; +</pre></div></div> +<p>This query’s expected result, also evaluable using the primary index on user id, is:</p> + +<div class="source"> +<div class="source"> +<pre> { "id": 2, "alias": "Isbel", "name": "IsbelDull", "userSince": datetime("2011-01-22T10:10:00.000Z"), "friendIds": {{ 1, 4 }}, "employment": [ { "organizationName": "Hexviafind", "startDate": date("2010-04-27") } ], "nickname": "Izzy" } + { "id": 3, "alias": "Emory", "name": "EmoryUnk", "userSince": datetime("2012-07-10T10:10:00.000Z"), "friendIds": {{ 1, 5, 8, 9 }}, "employment": [ { "organizationName": "geomedia", "startDate": date("2010-06-17"), "endDate": date("2010-01-26") } ] } + { "id": 4, "alias": "Nicholas", "name": "NicholasStroh", "userSince": datetime("2010-12-27T10:10:00.000Z"), "friendIds": {{ 2 }}, "employment": [ { "organizationName": "Zamcorporation", "startDate": date("2010-06-08") } ] } +</pre></div></div></div> +<div class="section"> +<h3><a name="Query_1_-_Other_Query_Filters"></a>Query 1 - Other Query Filters</h3> +<p>SQL++ can do range queries on any data type that supports the appropriate set of comparators. As an example, this next query retrieves the Gleambook users who joined between July 22, 2010 and July 29, 2012:</p> + +<div class="source"> +<div class="source"> +<pre> USE TinySocial; + + SELECT VALUE user + FROM GleambookUsers user + WHERE user.userSince >= datetime('2010-07-22T00:00:00') + AND user.userSince <= datetime('2012-07-29T23:59:59'); +</pre></div></div> +<p>The expected result for this query, also an indexable query, is as follows:</p> + +<div class="source"> +<div class="source"> +<pre> { "id": 10, "alias": "Bram", "name": "BramHatch", "userSince": datetime("2010-10-16T10:10:00.000Z"), "friendIds": {{ 1, 5, 9 }}, "employment": [ { "organizationName": "physcane", "startDate": date("2007-06-05"), "endDate": date("2011-11-05") } ] } + { "id": 2, "alias": "Isbel", "name": "IsbelDull", "userSince": datetime("2011-01-22T10:10:00.000Z"), "friendIds": {{ 1, 4 }}, "employment": [ { "organizationName": "Hexviafind", "startDate": date("2010-04-27") } ], "nickname": "Izzy" } + { "id": 3, "alias": "Emory", "name": "EmoryUnk", "userSince": datetime("2012-07-10T10:10:00.000Z"), "friendIds": {{ 1, 5, 8, 9 }}, "employment": [ { "organizationName": "geomedia", "startDate": date("2010-06-17"), "endDate": date("2010-01-26") } ] } + { "id": 4, "alias": "Nicholas", "name": "NicholasStroh", "userSince": datetime("2010-12-27T10:10:00.000Z"), "friendIds": {{ 2 }}, "employment": [ { "organizationName": "Zamcorporation", "startDate": date("2010-06-08") } ] } +</pre></div></div></div> +<div class="section"> +<h3><a name="Query_2-A_-_Equijoin"></a>Query 2-A - Equijoin</h3> +<p>In addition to simply binding variables to data instances and returning them “whole”, an SQL++ query can construct new ADM instances to return based on combinations of its variable bindings. This gives SQL++ the power to do projections and joins much like those done using multi-table <i>FROM</i> clauses in SQL. For example, suppose we wanted a list of all Gleambook users paired with their associated messages, with the list enumerating the author name and the message text associated with each Gleambook message. We could do this as follows in SQL++:</p> + +<div class="source"> +<div class="source"> +<pre> USE TinySocial; + + SELECT user.name AS uname, msg.message AS message + FROM GleambookUsers user, GleambookMessages msg + WHERE msg.authorId = user.id; +</pre></div></div> +<p>The result of this query is a sequence of new ADM instances, one for each author/message pair. Each instance in the result will be an ADM object containing two fields, “uname” and “message”, containing the user’s name and the message text, respectively, for each author/message pair. Notice how the use of a traditional SQL-style <i>SELECT</i> clause, as opposed to the new SQL++ <i>SELECT VALUE</i> clause, automatically results in the construction of a new object value for each result.</p> +<p>The expected result of this example SQL++ join query for our sample data set is:</p> + +<div class="source"> +<div class="source"> +<pre> { "uname": "WillisWynne", "message": " love sprint the customization is mind-blowing" } + { "uname": "WoodrowNehling", "message": " love at&t its 3G is good:)" } + { "uname": "BramHatch", "message": " can't stand t-mobile its voicemail-service is OMG:(" } + { "uname": "BramHatch", "message": " dislike iphone the voice-command is bad:(" } + { "uname": "MargaritaStoddard", "message": " like verizon the 3G is awesome:)" } + { "uname": "MargaritaStoddard", "message": " can't stand motorola the touch-screen is terrible" } + { "uname": "MargaritaStoddard", "message": " can't stand at&t its plan is terrible" } + { "uname": "MargaritaStoddard", "message": " dislike iphone its touch-screen is horrible" } + { "uname": "MargaritaStoddard", "message": " can't stand at&t the network is horrible:(" } + { "uname": "IsbelDull", "message": " like t-mobile its platform is mind-blowing" } + { "uname": "IsbelDull", "message": " like samsung the plan is amazing" } + { "uname": "EmoryUnk", "message": " love verizon its wireless is good" } + { "uname": "EmoryUnk", "message": " love sprint its shortcut-menu is awesome:)" } + { "uname": "VonKemble", "message": " dislike sprint the speed is horrible" } + { "uname": "SuzannaTillson", "message": " like iphone the voicemail-service is awesome" } +</pre></div></div> +<p>If we were feeling lazy, we might use <i>SELECT *</i> in SQL++ to return all of the matching user/message data:</p> + +<div class="source"> +<div class="source"> +<pre> USE TinySocial; + + SELECT * + FROM GleambookUsers user, GleambookMessages msg + WHERE msg.authorId = user.id; +</pre></div></div> +<p>In SQL++, this <i>SELECT *</i> query will produce a new nested object for each user/message pair. Each result object contains one field (named after the “user” variable) to hold the user object and another field (named after the “msg” variable) to hold the matching message object. Note that the nested nature of this SQL++ <i>SELECT *</i> result is different than traditional SQL, as SQL was not designed to handle the richer, nested data model that underlies the design of SQL++.</p> +<p>The expected result of this version of the SQL++ join query for our sample data set is:</p> + +<div class="source"> +<div class="source"> +<pre> { "user": { "id": 6, "alias": "Willis", "name": "WillisWynne", "userSince": datetime("2005-01-17T10:10:00.000Z"), "friendIds": {{ 1, 3, 7 }}, "employment": [ { "organizationName": "jaydax", "startDate": date("2009-05-15") } ] }, "msg": { "messageId": 5, "authorId": 6, "inResponseTo": 2, "senderLocation": point("34.7,90.76"), "message": " love sprint the customization is mind-blowing" } } + { "user": { "id": 9, "alias": "Woodrow", "name": "WoodrowNehling", "userSince": datetime("2005-09-20T10:10:00.000Z"), "friendIds": {{ 3, 10 }}, "employment": [ { "organizationName": "Zuncan", "startDate": date("2003-04-22"), "endDate": date("2009-12-13") } ], "nickname": "Woody" }, "msg": { "messageId": 14, "authorId": 9, "inResponseTo": 12, "senderLocation": point("41.33,85.28"), "message": " love at&t its 3G is good:)" } } + { "user": { "id": 10, "alias": "Bram", "name": "BramHatch", "userSince": datetime("2010-10-16T10:10:00.000Z"), "friendIds": {{ 1, 5, 9 }}, "employment": [ { "organizationName": "physcane", "startDate": date("2007-06-05"), "endDate": date("2011-11-05") } ] }, "msg": { "messageId": 12, "authorId": 10, "inResponseTo": 6, "senderLocation": point("42.26,77.76"), "message": " can't stand t-mobile its voicemail-service is OMG:(" } } + { "user": { "id": 10, "alias": "Bram", "name": "BramHatch", "userSince": datetime("2010-10-16T10:10:00.000Z"), "friendIds": {{ 1, 5, 9 }}, "employment": [ { "organizationName": "physcane", "startDate": date("2007-06-05"), "endDate": date("2011-11-05") } ] }, "msg": { "messageId": 13, "authorId": 10, "inResponseTo": 4, "senderLocation": point("42.77,78.92"), "message": " dislike iphone the voice-command is bad:(" } } + { "user": { "id": 1, "alias": "Margarita", "name": "MargaritaStoddard", "userSince": datetime("2012-08-20T10:10:00.000Z"), "friendIds": {{ 2, 3, 6, 10 }}, "employment": [ { "organizationName": "Codetechno", "startDate": date("2006-08-06") }, { "organizationName": "geomedia", "startDate": date("2010-06-17"), "endDate": date("2010-01-26") } ], "nickname": "Mags", "gender": "F" }, "msg": { "messageId": 8, "authorId": 1, "inResponseTo": 11, "senderLocation": point("40.33,80.87"), "message": " like verizon the 3G is awesome:)" } } + { "user": { "id": 1, "alias": "Margarita", "name": "MargaritaStoddard", "userSince": datetime("2012-08-20T10:10:00.000Z"), "friendIds": {{ 2, 3, 6, 10 }}, "employment": [ { "organizationName": "Codetechno", "startDate": date("2006-08-06") }, { "organizationName": "geomedia", "startDate": date("2010-06-17"), "endDate": date("2010-01-26") } ], "nickname": "Mags", "gender": "F" }, "msg": { "messageId": 10, "authorId": 1, "inResponseTo": 12, "senderLocation": point("42.5,70.01"), "message": " can't stand motorola the touch-screen is terrible" } } + { "user": { "id": 1, "alias": "Margarita", "name": "MargaritaStoddard", "userSince": datetime("2012-08-20T10:10:00.000Z"), "friendIds": {{ 2, 3, 6, 10 }}, "employment": [ { "organizationName": "Codetechno", "startDate": date("2006-08-06") }, { "organizationName": "geomedia", "startDate": date("2010-06-17"), "endDate": date("2010-01-26") } ], "nickname": "Mags", "gender": "F" }, "msg": { "messageId": 11, "authorId": 1, "inResponseTo": 1, "senderLocation": point("38.97,77.49"), "message": " can't stand at&t its plan is terrible" } } + { "user": { "id": 1, "alias": "Margarita", "name": "MargaritaStoddard", "userSince": datetime("2012-08-20T10:10:00.000Z"), "friendIds": {{ 2, 3, 6, 10 }}, "employment": [ { "organizationName": "Codetechno", "startDate": date("2006-08-06") }, { "organizationName": "geomedia", "startDate": date("2010-06-17"), "endDate": date("2010-01-26") } ], "nickname": "Mags", "gender": "F" }, "msg": { "messageId": 2, "authorId": 1, "inResponseTo": 4, "senderLocation": point("41.66,80.87"), "message": " dislike iphone its touch-screen is horrible" } } + { "user": { "id": 1, "alias": "Margarita", "name": "MargaritaStoddard", "userSince": datetime("2012-08-20T10:10:00.000Z"), "friendIds": {{ 2, 3, 6, 10 }}, "employment": [ { "organizationName": "Codetechno", "startDate": date("2006-08-06") }, { "organizationName": "geomedia", "startDate": date("2010-06-17"), "endDate": date("2010-01-26") } ], "nickname": "Mags", "gender": "F" }, "msg": { "messageId": 4, "authorId": 1, "inResponseTo": 2, "senderLocation": point("37.73,97.04"), "message": " can't stand at&t the network is horrible:(" } } + { "user": { "id": 2, "alias": "Isbel", "name": "IsbelDull", "userSince": datetime("2011-01-22T10:10:00.000Z"), "friendIds": {{ 1, 4 }}, "employment": [ { "organizationName": "Hexviafind", "startDate": date("2010-04-27") } ], "nickname": "Izzy" }, "msg": { "messageId": 6, "authorId": 2, "inResponseTo": 1, "senderLocation": point("31.5,75.56"), "message": " like t-mobile its platform is mind-blowing" } } + { "user": { "id": 2, "alias": "Isbel", "name": "IsbelDull", "userSince": datetime("2011-01-22T10:10:00.000Z"), "friendIds": {{ 1, 4 }}, "employment": [ { "organizationName": "Hexviafind", "startDate": date("2010-04-27") } ], "nickname": "Izzy" }, "msg": { "messageId": 3, "authorId": 2, "inResponseTo": 4, "senderLocation": point("48.09,81.01"), "message": " like samsung the plan is amazing" } } + { "user": { "id": 3, "alias": "Emory", "name": "EmoryUnk", "userSince": datetime("2012-07-10T10:10:00.000Z"), "friendIds": {{ 1, 5, 8, 9 }}, "employment": [ { "organizationName": "geomedia", "startDate": date("2010-06-17"), "endDate": date("2010-01-26") } ] }, "msg": { "messageId": 9, "authorId": 3, "inResponseTo": 12, "senderLocation": point("34.45,96.48"), "message": " love verizon its wireless is good" } } + { "user": { "id": 3, "alias": "Emory", "name": "EmoryUnk", "userSince": datetime("2012-07-10T10:10:00.000Z"), "friendIds": {{ 1, 5, 8, 9 }}, "employment": [ { "organizationName": "geomedia", "startDate": date("2010-06-17"), "endDate": date("2010-01-26") } ] }, "msg": { "messageId": 1, "authorId": 3, "inResponseTo": 2, "senderLocation": point("47.16,77.75"), "message": " love sprint its shortcut-menu is awesome:)" } } + { "user": { "id": 5, "alias": "Von", "name": "VonKemble", "userSince": datetime("2010-01-05T10:10:00.000Z"), "friendIds": {{ 3, 6, 10 }}, "employment": [ { "organizationName": "Kongreen", "startDate": date("2010-11-27") } ] }, "msg": { "messageId": 7, "authorId": 5, "inResponseTo": 15, "senderLocation": point("32.91,85.05"), "message": " dislike sprint the speed is horrible" } } + { "user": { "id": 7, "alias": "Suzanna", "name": "SuzannaTillson", "userSince": datetime("2012-08-07T10:10:00.000Z"), "friendIds": {{ 6 }}, "employment": [ { "organizationName": "Labzatron", "startDate": date("2011-04-19") } ] }, "msg": { "messageId": 15, "authorId": 7, "inResponseTo": 11, "senderLocation": point("44.47,67.11"), "message": " like iphone the voicemail-service is awesome" } } +</pre></div></div> +<p>Finally (for now :-)), another less lazy and more explicit SQL++ way of achieving the result shown above is:</p> + +<div class="source"> +<div class="source"> +<pre> USE TinySocial; + + SELECT VALUE {"user": user, "message": msg} + FROM GleambookUsers user, GleambookMessages msg + WHERE msg.authorId = user.id; +</pre></div></div> +<p>This version of the query uses an explicit object constructor to build each result object. (Note that “uname” and “message” are both simple SQL++ expressions themselves—so in the most general case, even the resulting field names can be computed as part of the query, making SQL++ a very powerful tool for slicing and dicing semistructured data.)</p></div> +<div class="section"> +<h3><a name="Query_2-B_-_Index_join"></a>Query 2-B - Index join</h3> +<p>By default, AsterixDB evaluates equijoin queries using hash-based join methods that work well for doing ad hoc joins of very large data sets (<a class="externalLink" href="http://en.wikipedia.org/wiki/Hash_join";>http://en.wikipedia.org/wiki/Hash_join</a>). On a cluster, hash partitioning is employed as AsterixDB’s divide-and-conquer strategy for computing large parallel joins. AsterixDB includes other join methods, but in the absence of data statistics and selectivity estimates, it doesn’t (yet) have the know-how to intelligently choose among its alternatives. We therefore asked ourselves the classic question—WWOD?—What Would Oracle Do?—and in the interim, SQL++ includes a clunky (but useful) hint-based mechanism for addressing the occasional need to suggest to AsterixDB which join method it should use for a particular SQL++ query.</p> +<p>The following query is similar to the first version of Query 2-A but includes a suggestion to AsterixDB that it should consider employing an index-based nested-loop join technique to process the query:</p> + +<div class="source"> +<div class="source"> +<pre> USE TinySocial; + + SELECT user.name AS uname, msg.message AS message + FROM GleambookUsers user, GleambookMessages msg + WHERE msg.authorId /*+ indexnl */ = user.id; +</pre></div></div> +<p>In addition to illustrating the use of a hint, the query also shows how to achieve the same result object format using <i>SELECT</i> and <i>AS</i> instead of using an explicit object constructor. The expected result is (of course) the same as before, modulo the order of the instances. Result ordering is (intentionally) undefined in SQL++ in the absence of an <i>ORDER BY</i> clause. The query result for our sample data in this case is:</p> + +<div class="source"> +<div class="source"> +<pre> { "uname": "IsbelDull", "message": " like t-mobile its platform is mind-blowing" } + { "uname": "MargaritaStoddard", "message": " like verizon the 3G is awesome:)" } + { "uname": "EmoryUnk", "message": " love verizon its wireless is good" } + { "uname": "MargaritaStoddard", "message": " can't stand motorola the touch-screen is terrible" } + { "uname": "MargaritaStoddard", "message": " can't stand at&t its plan is terrible" } + { "uname": "BramHatch", "message": " can't stand t-mobile its voicemail-service is OMG:(" } + { "uname": "WoodrowNehling", "message": " love at&t its 3G is good:)" } + { "uname": "EmoryUnk", "message": " love sprint its shortcut-menu is awesome:)" } + { "uname": "MargaritaStoddard", "message": " dislike iphone its touch-screen is horrible" } + { "uname": "IsbelDull", "message": " like samsung the plan is amazing" } + { "uname": "MargaritaStoddard", "message": " can't stand at&t the network is horrible:(" } + { "uname": "WillisWynne", "message": " love sprint the customization is mind-blowing" } + { "uname": "VonKemble", "message": " dislike sprint the speed is horrible" } + { "uname": "BramHatch", "message": " dislike iphone the voice-command is bad:(" } + { "uname": "SuzannaTillson", "message": " like iphone the voicemail-service is awesome" } +</pre></div></div> +<p>(It is worth knowing, with respect to influencing AsterixDB’s query evaluation, that <i>FROM</i> clauses—<i>a.k.a.</i> joins— are currently evaluated in order, with the “left” clause probing the data of the “right” clause. SQL++ also supports SQL-style <i>JOIN</i> clauses, and the same is true for those.)</p></div> +<div class="section"> +<h3><a name="Query_3_-_Nested_Outer_Join"></a>Query 3 - Nested Outer Join</h3> +<p>In order to support joins between tables with missing/dangling join tuples, the designers of SQL ended up shoe-horning a subset of the relational algebra into SQL’s <i>FROM</i> clause syntax—and providing a variety of join types there for users to choose from (which SQL++ supports for SQL compatibility). Left outer joins are particularly important in SQL, e.g., to print a summary of customers and orders, grouped by customer, without omitting those customers who haven’t placed any orders yet.</p> +<p>The SQL++ language supports nesting, both of queries and of query results, and the combination allows for an arguably cleaner/more natural approach to such queries. As an example, supposed we wanted, for each Gleambook user, to produce a object that has his/her name plus a list of the messages written by that user. In SQL, this would involve a left outer join between users and messages, grouping by user, and having the user name repeated along side each message. In SQL++, this sort of use case can be handled (more naturally) as follows:</p> + +<div class="source"> +<div class="source"> +<pre> USE TinySocial; + + SELECT user.name AS uname, + (SELECT VALUE msg.message + FROM GleambookMessages msg + WHERE msg.authorId = user.id) AS messages + FROM GleambookUsers user; +</pre></div></div> +<p>This SQL++ query binds the variable <tt>user</tt> to the data instances in GleambookUsers; for each user, it constructs a result object containing a “uname” field with the user’s name and a “messages” field with a nested collection of all messages for that user. The nested collection for each user is specified by using a correlated subquery. (Note: While it looks like nested loops could be involved in computing the result, AsterixDB recogizes the equivalence of such a query to an outerjoin, and it will use an efficient hash-based strategy when actually computing the query’s result.)</p> +<p>Here is this example query’s expected output:</p> + +<div class="source"> +<div class="source"> +<pre> { "uname": "WillisWynne", "messages": [ " love sprint the customization is mind-blowing" ] } + { "uname": "NilaMilliron", "messages": [ ] } + { "uname": "WoodrowNehling", "messages": [ " love at&t its 3G is good:)" ] } + { "uname": "BramHatch", "messages": [ " dislike iphone the voice-command is bad:(", " can't stand t-mobile its voicemail-service is OMG:(" ] } + { "uname": "MargaritaStoddard", "messages": [ " dislike iphone its touch-screen is horrible", " can't stand at&t the network is horrible:(", " like verizon the 3G is awesome:)", " can't stand motorola the touch-screen is terrible", " can't stand at&t its plan is terrible" ] } + { "uname": "IsbelDull", "messages": [ " like samsung the plan is amazing", " like t-mobile its platform is mind-blowing" ] } + { "uname": "EmoryUnk", "messages": [ " love sprint its shortcut-menu is awesome:)", " love verizon its wireless is good" ] } + { "uname": "NicholasStroh", "messages": [ ] } + { "uname": "VonKemble", "messages": [ " dislike sprint the speed is horrible" ] } + { "uname": "SuzannaTillson", "messages": [ " like iphone the voicemail-service is awesome" ] } +</pre></div></div></div> +<div class="section"> +<h3><a name="Query_4_-_Theta_Join"></a>Query 4 - Theta Join</h3> +<p>Not all joins are expressible as equijoins and computable using equijoin-oriented algorithms. The join predicates for some use cases involve predicates with functions; AsterixDB supports the expression of such queries and will still evaluate them as best it can using nested loop based techniques (and broadcast joins in the parallel case).</p> +<p>As an example of such a use case, suppose that we wanted, for each chirp message C, to find all of the other chirp messages that originated from within a circle of radius of 1 surrounding chirp C’s location. In SQL++, this can be specified in a manner similar to the previous query using one of the built-in functions on the spatial data type instead of id equality in the correlated query’s <i>WHERE</i> clause:</p> + +<div class="source"> +<div class="source"> +<pre> USE TinySocial; + + SELECT cm1.messageText AS message, + (SELECT VALUE cm2.messageText + FROM ChirpMessages cm2 + WHERE `spatial-distance`(cm1.senderLocation, cm2.senderLocation) <= 1 + AND cm2.chirpId < cm1.chirpId) AS nearbyMessages + FROM ChirpMessages cm1; +</pre></div></div> +<p>Here is the expected result for this query:</p> + +<div class="source"> +<div class="source"> +<pre> { "message": " can't stand iphone its platform is terrible", "nearbyMessages": [ ] } + { "message": " like verizon its shortcut-menu is awesome:)", "nearbyMessages": [ ] } + { "message": " like motorola the speed is good:)", "nearbyMessages": [ " hate verizon its voice-clarity is OMG:(" ] } + { "message": " like sprint the voice-command is mind-blowing:)", "nearbyMessages": [ ] } + { "message": " like iphone the voice-clarity is good:)", "nearbyMessages": [ ] } + { "message": " like samsung the platform is good", "nearbyMessages": [ ] } + { "message": " love verizon its voicemail-service is awesome", "nearbyMessages": [ ] } + { "message": " love t-mobile its customization is good:)", "nearbyMessages": [ ] } + { "message": " hate verizon its voice-clarity is OMG:(", "nearbyMessages": [ ] } + { "message": " like samsung the voice-command is amazing:)", "nearbyMessages": [ ] } + { "message": " can't stand motorola its speed is terrible:(", "nearbyMessages": [ ] } + { "message": " like t-mobile the shortcut-menu is awesome:)", "nearbyMessages": [ ] } +</pre></div></div></div> +<div class="section"> +<h3><a name="Query_5_-_Fuzzy_Join"></a>Query 5 - Fuzzy Join</h3> +<p>As another example of a non-equijoin use case, we could ask AsterixDB to find, for each Gleambook user, all Chirp users with names “similar” to their name. AsterixDB supports a variety of “fuzzy match” functions for use with textual and set-based data. As one example, we could choose to use edit distance with a threshold of 3 as the definition of name similarity, in which case we could write the following query using SQL++’s operator-based syntax (~=) for testing whether or not two values are similar:</p> + +<div class="source"> +<div class="source"> +<pre> USE TinySocial; + SET simfunction "edit-distance"; + SET simthreshold "3"; + + SELECT gbu.id AS id, gbu.name AS name, + (SELECT cm.user.screenName AS chirpScreenname, + cm.user.name AS chirpName + FROM ChirpMessages cm + WHERE cm.user.name ~= gbu.name) AS similarUsers + FROM GleambookUsers gbu; +</pre></div></div> +<p>The expected result for this query against our sample data is:</p> + +<div class="source"> +<div class="source"> +<pre> { "id": 6, "name": "WillisWynne", "similarUsers": [ ] } + { "id": 8, "name": "NilaMilliron", "similarUsers": [ { "chirpScreenname": "NilaMilliron_tw", "chirpName": "Nila Milliron" } ] } + { "id": 9, "name": "WoodrowNehling", "similarUsers": [ ] } + { "id": 10, "name": "BramHatch", "similarUsers": [ ] } + { "id": 1, "name": "MargaritaStoddard", "similarUsers": [ ] } + { "id": 2, "name": "IsbelDull", "similarUsers": [ ] } + { "id": 3, "name": "EmoryUnk", "similarUsers": [ ] } + { "id": 4, "name": "NicholasStroh", "similarUsers": [ ] } + { "id": 5, "name": "VonKemble", "similarUsers": [ ] } + { "id": 7, "name": "SuzannaTillson", "similarUsers": [ ] } +</pre></div></div></div> +<div class="section"> +<h3><a name="Query_6_-_Existential_Quantification"></a>Query 6 - Existential Quantification</h3> +<p>The expressive power of SQL++ includes support for queries involving “some” (existentially quantified) and “all” (universally quantified) query semantics. As an example of an existential SQL++ query, here we show a query to list the Gleambook users who are currently employed. Such employees will have an employment history containing a object in which the end-date field is <i>MISSING</i> (or it could be there but have the value <i>NULL</i>, as JSON unfortunately provides two ways to represent unknown values). This leads us to the following SQL++ query:</p> + +<div class="source"> +<div class="source"> +<pre> USE TinySocial; + + SELECT VALUE gbu + FROM GleambookUsers gbu + WHERE (SOME e IN gbu.employment SATISFIES e.endDate IS UNKNOWN); +</pre></div></div> +<p>The expected result in this case is:</p> + +<div class="source"> +<div class="source"> +<pre> { "id": 6, "alias": "Willis", "name": "WillisWynne", "userSince": datetime("2005-01-17T10:10:00.000Z"), "friendIds": {{ 1, 3, 7 }}, "employment": [ { "organizationName": "jaydax", "startDate": date("2009-05-15") } ] } + { "id": 8, "alias": "Nila", "name": "NilaMilliron", "userSince": datetime("2008-01-01T10:10:00.000Z"), "friendIds": {{ 3 }}, "employment": [ { "organizationName": "Plexlane", "startDate": date("2010-02-28") } ] } + { "id": 1, "alias": "Margarita", "name": "MargaritaStoddard", "userSince": datetime("2012-08-20T10:10:00.000Z"), "friendIds": {{ 2, 3, 6, 10 }}, "employment": [ { "organizationName": "Codetechno", "startDate": date("2006-08-06") }, { "organizationName": "geomedia", "startDate": date("2010-06-17"), "endDate": date("2010-01-26") } ], "nickname": "Mags", "gender": "F" } + { "id": 2, "alias": "Isbel", "name": "IsbelDull", "userSince": datetime("2011-01-22T10:10:00.000Z"), "friendIds": {{ 1, 4 }}, "employment": [ { "organizationName": "Hexviafind", "startDate": date("2010-04-27") } ], "nickname": "Izzy" } + { "id": 4, "alias": "Nicholas", "name": "NicholasStroh", "userSince": datetime("2010-12-27T10:10:00.000Z"), "friendIds": {{ 2 }}, "employment": [ { "organizationName": "Zamcorporation", "startDate": date("2010-06-08") } ] } + { "id": 5, "alias": "Von", "name": "VonKemble", "userSince": datetime("2010-01-05T10:10:00.000Z"), "friendIds": {{ 3, 6, 10 }}, "employment": [ { "organizationName": "Kongreen", "startDate": date("2010-11-27") } ] } + { "id": 7, "alias": "Suzanna", "name": "SuzannaTillson", "userSince": datetime("2012-08-07T10:10:00.000Z"), "friendIds": {{ 6 }}, "employment": [ { "organizationName": "Labzatron", "startDate": date("2011-04-19") } ] } +</pre></div></div></div> +<div class="section"> +<h3><a name="Query_7_-_Universal_Quantification"></a>Query 7 - Universal Quantification</h3> +<p>As an example of a universal SQL++ query, here we show a query to list the Gleambook users who are currently unemployed. Such employees will have an employment history containing no objects with unknown end-date field values, leading us to the following SQL++ query:</p> + +<div class="source"> +<div class="source"> +<pre> USE TinySocial; + + SELECT VALUE gbu + FROM GleambookUsers gbu + WHERE (EVERY e IN gbu.employment SATISFIES e.endDate IS NOT UNKNOWN); +</pre></div></div> +<p>Here is the expected result for our sample data:</p> + +<div class="source"> +<div class="source"> +<pre> { "id": 9, "alias": "Woodrow", "name": "WoodrowNehling", "userSince": datetime("2005-09-20T10:10:00.000Z"), "friendIds": {{ 3, 10 }}, "employment": [ { "organizationName": "Zuncan", "startDate": date("2003-04-22"), "endDate": date("2009-12-13") } ], "nickname": "Woody" } + { "id": 10, "alias": "Bram", "name": "BramHatch", "userSince": datetime("2010-10-16T10:10:00.000Z"), "friendIds": {{ 1, 5, 9 }}, "employment": [ { "organizationName": "physcane", "startDate": date("2007-06-05"), "endDate": date("2011-11-05") } ] } + { "id": 3, "alias": "Emory", "name": "EmoryUnk", "userSince": datetime("2012-07-10T10:10:00.000Z"), "friendIds": {{ 1, 5, 8, 9 }}, "employment": [ { "organizationName": "geomedia", "startDate": date("2010-06-17"), "endDate": date("2010-01-26") } ] } +</pre></div></div></div> +<div class="section"> +<h3><a name="Query_8_-_Simple_Aggregation"></a>Query 8 - Simple Aggregation</h3> +<p>Like SQL, the SQL++ language of AsterixDB provides support for computing aggregates over large amounts of data. As a very simple example, the following SQL++ query computes the total number of Gleambook users in a SQL-like way:</p> + +<div class="source"> +<div class="source"> +<pre> USE TinySocial; + + SELECT COUNT(gbu) AS numUsers FROM GleambookUsers gbu; +</pre></div></div> +<p>This query’s result will be:</p> + +<div class="source"> +<div class="source"> +<pre> { "numUsers": 10 } +</pre></div></div> +<p>If an “unwrapped” value is preferred, the following variant could be used instead:</p> + +<div class="source"> +<div class="source"> +<pre> SELECT VALUE COUNT(gbu) FROM GleambookUsers gbu; +</pre></div></div> +<p>This time the result will simply be:</p> + +<div class="source"> +<div class="source"> +<pre> 10 +</pre></div></div> +<p>In SQL++, aggregate functions can be applied to arbitrary collections, including subquery results. To illustrate, here is a less SQL-like—and also more explicit—way to express the query above:</p> + +<div class="source"> +<div class="source"> +<pre> SELECT VALUE ARRAY_COUNT((SELECT gbu FROM GleambookUsers gbu)); +</pre></div></div> +<p>For each traditional SQL aggregate function <i>F</i>, SQL++ has a corresponding function _ARRAY<i>F</i> that can be used to perform the desired aggregate calculation. Each such function is a regular function that takes a collection-valued argument to aggregate over. Thus, the query above counts the results produced by the GleambookUsers subquery, and the previous, more SQL-like versions are just syntactic sugar for SQL++ queries that use _ARRAY<i>COUNT</i>. (Note: Subqueries in SQL++ must always be parenthesized.)</p></div> +<div class="section"> +<h3><a name="Query_9-A_-_Grouping_and_Aggregation"></a>Query 9-A - Grouping and Aggregation</h3> +<p>Also like SQL, SQL++ supports grouped aggregation. For every Chirp user, the following group-by/aggregate query counts the number of chirps sent by that user:</p> + +<div class="source"> +<div class="source"> +<pre> USE TinySocial; + + SELECT uid AS user, COUNT(cm) AS count + FROM ChirpMessages cm + GROUP BY cm.user.screenName AS uid; +</pre></div></div> +<p>The <i>FROM</i> clause incrementally binds the variable <i>cm</i> to chirps, and the <i>GROUP BY</i> clause groups the chirps by their issuer’s Chirp screen-name. Unlike SQL, where data is tabular—flat—the data model underlying SQL++ allows for nesting. Thus, due to the <i>GROUP BY</i> clause, the <i>SELECT</i> clause in this query sees a sequence of <i>cm</i> groups, with each such group having an associated <i>uid</i> variable value (i.e., the chirping user’s screen name). In the context of the <i>SELECT</i> clause, <i>uid</i> is bound to the chirper’s id and <i>cm</i> is now re-bound (due to grouping) to the <i>set</i> of chirps issued by that chirper. The <i>SELECT</i> clause yields a result object containing the chirper’s user id and the count of the items in the associated chirp set. The query result will contain one such object per screen name. This query also illustrates another feature of SQL++; notice how each user’s scre en name is accessed via a path syntax that traverses each chirp’s nested object structure.</p> +<p>Here is the expected result for this query over the sample data:</p> + +<div class="source"> +<div class="source"> +<pre> { "user": "ChangEwing_573", "count": 1 } + { "user": "OliJackson_512", "count": 1 } + { "user": "ColineGeyer@63", "count": 3 } + { "user": "NathanGiesen@211", "count": 6 } + { "user": "NilaMilliron_tw", "count": 1 } +</pre></div></div></div> +<div class="section"> +<h3><a name="Query_9-B_-_Hash-Based_Grouping_and_Aggregation"></a>Query 9-B - (Hash-Based) Grouping and Aggregation</h3> +<p>As for joins, AsterixDB has multiple evaluation strategies available for processing grouped aggregate queries. For grouped aggregation, the system knows how t
<TRUNCATED>
