Blame - aql/manual.html - incubator-asterixdb-site

<h2><a name="Introduction" id="Introduction">1. Introduction</a><font size="4"> <a href="#toc">[Back to TOC]</a></font><a name="a1._Introduction_Back_to_TOC"></a></h2>

213

<p>This document is intended as a reference guide to the full syntax and semantics of the Asterix Query Language (AQL), the language for talking to AsterixDB. This guide covers both the data manipulation language (DML) aspects of AQL, including its support for queries and data modification, as well as its data definition language (DDL) aspects. New AsterixDB users are encouraged to read and work through the (friendlier) guide “AsterixDB 101: An ADM and AQL Primer” before attempting to make use of this document. In addition, readers are advised to read and understand the Asterix Data Model (ADM) reference guide since a basic understanding of ADM concepts is a prerequisite to understanding AQL.<br />In what follows, we detail the features of the AQL language in a grammar-guided manner: We list and briefly explain each of the productions in the AQL grammar, offering examples for clarity in cases where doing so seems needed or helpful.</p></div>

214

215

<h2><a name="Expressions" id="Expressions">2. Expressions</a> <font size="4"><a href="#toc">[Back to TOC]</a></font><a name="a2._Expressions_Back_to_TOC"></a></h2>

216

217

218

<pre>Query ::= Expression

219

</pre></div>

220

<p>An AQL query can be any legal AQL expression.</p>

221

222

223

<pre>Expression ::= ( OperatorExpr | IfThenElse | FLWOR | QuantifiedExpression )

224

</pre></div>

225

<p>AQL is a fully composable expression language. Each AQL expression returns zero or more Asterix Data Model (ADM) instances. There are four major kinds of expressions in AQL. At the topmost level, an AQL expression can be an OperatorExpr (similar to a mathematical expression), an IfThenElse (to choose between two alternative values), a FLWOR expression (the heart of AQL, pronounced “flower expression”), or a QuantifiedExpression (which yields a boolean value). Each will be detailed as we explore the full AQL grammar.</p>

226

227

<h3>Primary Expressions<a name="Primary_Expressions"></a></h3>

228

229

230

<pre>PrimaryExpr ::= Literal

231

| VariableRef

232

| ParenthesizedExpression

233

| FunctionCallExpr

234

| DatasetAccessExpression

| ListConstructor

| RecordConstructor

</pre></div>

<p>The most basic building block for any AQL expression is the PrimaryExpr. This can be a simple literal (constant) value, a reference to a query variable that is in scope, a parenthesized expression, a function call, an expression accessing the ADM contents of a dataset, a newly constructed list of ADM instances, or a newly constructed ADM record.</p>

239

240

<h4>Literals<a name="Literals"></a></h4>

241

242

243

<pre>Literal ::= StringLiteral

| IntegerLiteral

| FloatLiteral

| DoubleLiteral

| "null"

| "true"

| "false"

StringLiteral ::= ("\"" (<ESCAPE_QUOT> | ~["\""])* "\"")

251

| ("\'" (<ESCAPE_APOS> | ~["\'"])* "\'")

252

<ESCAPE_QUOT> ::= "\\\""

253

<ESCAPE_APOS> ::= "\\\'"

254

IntegerLiteral ::= <DIGITS>

255

<DIGITS> ::= ["0" - "9"]+

256

FloatLiteral ::= <DIGITS> ( "f" | "F" )

257

| <DIGITS> ( "." <DIGITS> ( "f" | "F" ) )?

258

| "." <DIGITS> ( "f" | "F" )

259

DoubleLiteral ::= <DIGITS>

260

| <DIGITS> ( "." <DIGITS> )?

261

| "." <DIGITS>

262

</pre></div>

263

<p>Literals (constants) in AQL can be strings, integers, floating point values, double values, boolean constants, or the constant value null. The null value in AQL has “unknown” or “missing” value semantics, similar to (though not identical to) nulls in the relational query language SQL.</p>

264

<p>The following are some simple examples of AQL literals. Since AQL is an expression language, each example is also a complete, legal AQL query (!).</p>

265

266

<h5>Examples<a name="Examples"></a></h5>

<pre>"a string"

42

</pre></div></div></div>

272

273

<h4>Variable References<a name="Variable_References"></a></h4>

274

275

276

<pre>VariableRef ::= <VARIABLE>

277

<VARIABLE> ::= "$" <LETTER> (<LETTER> | <DIGIT> | "_")*

278

<LETTER> ::= ["A" - "Z", "a" - "z"]

279

</pre></div>

280

<p>A variable in AQL can be bound to any legal ADM value. A variable reference refers to the value to which an in-scope variable is bound. (E.g., a variable binding may originate from one of the for or let clauses of a FLWOR expression or from an input parameter in the context of an AQL function body.)</p>

281

282

<h5>Examples<a name="Examples"></a></h5>

<pre>$tweet

$id

</pre></div></div></div>

288

289

<h4>Parenthesized Expressions<a name="Parenthesized_Expressions"></a></h4>

290

291

292

<pre>ParenthesizedExpression ::= "(" Expression ")"

293

</pre></div>

294

<p>As in most languages, an expression may be parenthesized.</p>

295

<p>Since AQL is an expression language, the following example expression is actually also a complete, legal AQL query whose result is the value 2. (As such, you can have Big Fun explaining to your boss how AsterixDB and AQL can turn your 1000-node shared-nothing Big Data cluster into a $5M calculator in its spare time.)</p>

296

297

<h5>Example<a name="Example"></a></h5>

<pre>( 1 + 1 )

</pre></div></div></div>

302

303

<h4>Function Calls<a name="Function_Calls"></a></h4>

304

305

306

<pre>FunctionCallExpr ::= FunctionOrTypeName "(" ( Expression ( "," Expression )* )? ")"

307

</pre></div>

308

<p>Functions are included in AQL, like most languages, as a way to package useful functionality or to componentize complicated or reusable AQL computations. A function call is a legal AQL query expression that represents the ADM value resulting from the evaluation of its body expression with the given parameter bindings; the parameter value bindings can themselves be any AQL expressions.</p>

309

<p>The following example is a (built-in) function call expression whose value is 8.</p>

310

311

<h5>Example<a name="Example"></a></h5>

312

313

314

<pre>string-length("a string")

315

</pre></div></div></div>

316

317

<h4>Dataset Access<a name="Dataset_Access"></a></h4>

318

319

320

<pre>DatasetAccessExpression ::= "dataset" ( ( Identifier ( "." Identifier )? )

321

| ( "(" Expression ")" ) )

322

Identifier ::= <IDENTIFIER> | StringLiteral

323

<IDENTIFIER> ::= <LETTER> (<LETTER> | <DIGIT> | <SPECIALCHARS>)*

324

<SPECIALCHARS> ::= ["$", "_", "-"]

325

</pre></div>

326

<p>Querying Big Data is the main point of AsterixDB and AQL. Data in AsterixDB reside in datasets (collections of ADM records), each of which in turn resides in some namespace known as a dataverse (data universe). Data access in a query expression is accomplished via a DatasetAccessExpression. Dataset access expressions are most commonly used in FLWOR expressions, where variables are bound to their contents.</p>

327

<p>Note that the Identifier that identifies a dataset (or any other Identifier in AQL) can also be a StringLiteral. This is especially useful to avoid conficts with AQL keywords (e.g. “dataset”, “null”, or “type”).</p>

328

<p>The following are three examples of legal dataset access expressions. The first one accesses a dataset called Customers in the dataverse called SalesDV. The second one accesses the Customers dataverse in whatever the current dataverse is. The third one does the same thing as the second but uses a slightly older AQL syntax.</p>

329

330

<h5>Examples<a name="Examples"></a></h5>

331

332

333

<pre>dataset SalesDV.Customers

334

dataset Customers

335

dataset("Customers")

336

</pre></div></div></div>

337

338

<h4>Constructors<a name="Constructors"></a></h4>

339

340

341

<pre>ListConstructor ::= ( OrderedListConstructor | UnorderedListConstructor )

342

OrderedListConstructor ::= "[" ( Expression ( "," Expression )* )? "]"

343

UnorderedListConstructor ::= "{{" ( Expression ( "," Expression )* )? "}}"

344

RecordConstructor ::= "{" ( FieldBinding ( "," FieldBinding )* )? "}"

345

FieldBinding ::= Expression ":" Expression

346

</pre></div>

347

<p>A major feature of AQL is its ability to construct new ADM data instances. This is accomplished using its constructors for each of the major ADM complex object structures, namely lists (ordered or unordered) and records. Ordered lists are like JSON arrays, while unordered lists have bag (multiset) semantics. Records are built from attributes that are field-name/field-value pairs, again like JSON. (See the AsterixDB Data Model document for more details on each.)</p>

348

<p>The following examples illustrate how to construct a new ordered list with 3 items, a new unordered list with 4 items, and a new record with 2 fields, respectively. List elements can be homogeneous (as in the first example), which is the common case, or they may be heterogeneous (as in the second example). The data values and field name values used to construct lists and records in constructors are all simply AQL expressions. Thus the list elements, field names, and field values used in constructors can be simple literals (as in these three examples) or they can come from query variable references or even arbitrarily complex AQL expressions.</p>

349

350

<h5>Examples<a name="Examples"></a></h5>

351

352

353

<pre>[ "a", "b", "c" ]

{

"project name": "AsterixDB"

359

"project members": {{ "vinayakb", "dtabass", "chenli" }}

}

</pre></div></div>

<p>When constructing nested records there needs to be a space between the closing braces to avoid confusion with the <tt>}}</tt> token that ends an unordered list constructor: <tt>{ "a" : { "b" : "c" }}</tt> will fail to parse while <tt>{ "a" : { "b" : "c" } }</tt> will work.</p></div></div></div>

365

366

<h3>Path Expressions<a name="Path_Expressions"></a></h3>

367

368

369

<pre>ValueExpr ::= PrimaryExpr ( Field | Index )*

370

Field ::= "." Identifier

371

Index ::= "[" ( Expression | "?" ) "]"

372

</pre></div>

373

<p>Components of complex types in ADM are accessed via path expressions. Path access can be applied to the result of an AQL expression that yields an instance of such a type, e.g., a record or list instance. For records, path access is based on field names. For ordered lists, path access is based on (zero-based) array-style indexing. AQL also supports an “I’m feeling lucky” style index accessor, [?], for selecting an arbitrary element from an ordered list. Attempts to access non-existent fields or list elements produce a null (i.e., missing information) result as opposed to signaling a runtime error.</p>

374

<p>The following examples illustrate field access for a record, index-based element access for an ordered list, and also a composition thereof.</p>

375

376

377

<h5>Examples<a name="Examples"></a></h5>

378

379

380

<pre>({"list": [ "a", "b", "c"]}).list

(["a", "b", "c"])[2]

({ "list": [ "a", "b", "c"]}).list[2]

385

</pre></div></div></div></div>

386

387

<h3>Logical Expressions<a name="Logical_Expressions"></a></h3>

388

389

390

<pre>OperatorExpr ::= AndExpr ( "or" AndExpr )*

391

AndExpr ::= RelExpr ( "and" RelExpr )*

392

</pre></div>

393

<p>As in most languages, boolean expressions can be built up from smaller expressions by combining them with the logical connectives and/or. Legal boolean values in AQL are true, false, and null. (Nulls in AQL are treated much like SQL treats its unknown truth value in boolean expressions.)</p>

394

<p>The following is an example of a conjuctive range predicate in AQL. It will yield true if $a is bound to 4, null if $a is bound to null, and false otherwise.</p>

395

396

397

<h5>Example<a name="Example"></a></h5>

398

399

400

<pre>$a > 3 and $a < 5

401

</pre></div></div></div></div>

402

403

<h3>Comparison Expressions<a name="Comparison_Expressions"></a></h3>

404

405

406

<pre>RelExpr ::= AddExpr ( ( "<" | ">" | "<=" | ">=" | "=" | "!=" | "~=" ) AddExpr )?

407

</pre></div>

408

<p>AQL has the usual list of suspects, plus one, for comparing pairs of atomic values. The “plus one” is the last operator listed above, which is the “roughly equal” operator provided for similarity queries. (See the separate document on <a href="similarity.html">AsterixDB Similarity Queries</a> for more details on similarity matching.)</p>

409

<p>An example comparison expression (which yields the boolean value true) is shown below.</p>

410

411

412

<h5>Example<a name="Example"></a></h5>

<pre>5 > 3

</pre></div></div></div></div>

417

418

<h3>Arithmetic Expressions<a name="Arithmetic_Expressions"></a></h3>

419

420

421

<pre>AddExpr ::= MultExpr ( ( "+" | "-" ) MultExpr )*

422

MultExpr ::= UnaryExpr ( ( "*" | "/" | "%" | "^"| "idiv" ) UnaryExpr )*

423

UnaryExpr ::= ( ( "+" | "-" ) )? ValueExpr

424

</pre></div>

425

<p>AQL also supports the usual cast of characters for arithmetic expressions. The example below evaluates to 25.</p>

426

427

428

<h5>Example<a name="Example"></a></h5>

<pre>3 ^ 2 + 4 ^ 2

</pre></div></div></div></div>

433

434

<h3>FLWOR Expression<a name="FLWOR_Expression"></a></h3>

435

436

437

<pre>FLWOR ::= ( ForClause | LetClause ) ( Clause )* ("return"|"select") Expression

438

Clause ::= ForClause | LetClause | WhereClause | OrderbyClause

439

| GroupClause | LimitClause | DistinctClause

440

ForClause ::= ("for"|"from") Variable ( "at" Variable )? "in" ( Expression )

441

LetClause ::= ("let"|"with") Variable ":=" Expression

442

WhereClause ::= "where" Expression

443

OrderbyClause ::= "order" "by" Expression ( ( "asc" ) | ( "desc" ) )?

444

( "," Expression ( ( "asc" ) | ( "desc" ) )? )*

445

GroupClause ::= "group" "by" ( Variable ":=" )? Expression ( "," ( Variable ":=" )? Expression )*

446

("with"|"keeping") VariableRef ( "," VariableRef )*

447

LimitClause ::= "limit" Expression ( "offset" Expression )?

448

DistinctClause ::= "distinct" "by" Expression ( "," Expression )*

449

Variable ::= <VARIABLE>

450

</pre></div>

451

<p>The heart of AQL is the FLWOR (for-let-where-orderby-return) expression. The roots of this expression were borrowed from the expression of the same name in XQuery. A FLWOR expression starts with one or more clauses that establish variable bindings. A <tt>for</tt> clause binds a variable incrementally to each element of its associated expression; it includes an optional positional variable for counting/numbering the bindings. By default no ordering is implied or assumed by a <tt>for</tt> clause. A <tt>let</tt> clause binds a variable to the collection of elements computed by its associated expression.</p>

452

<p>Following the initial <tt>for</tt> or <tt>let</tt> clause(s), a FLWOR expression may contain an arbitrary sequence of other clauses. The <tt>where</tt> clause in a FLWOR expression filters the preceding bindings via a boolean expression, much like a <tt>where</tt> clause does in a SQL query. The <tt>order by</tt> clause in a FLWOR expression induces an ordering on the data. The <tt>group by</tt> clause, discussed further below, forms groups based on its group by expressions, optionally naming the expressions’ values (which together form the grouping key for the expression). The <tt>with</tt> subclause of a <tt>group by</tt> clause specifies the variable(s) whose values should be grouped based on the grouping key(s); following the grouping clause, only the grouping key(s) and the variables named in the with subclause remain in scope, and the named grouping variables now contain lists formed from their input values. The <tt>limit</tt> clause caps the number of values returned, optionally starting its result count from a specified offset. (Web applications can use this feature for doing pagination.) The <tt>distinct</tt> clause is similar to the <tt>group-by</tt> clause, but it forms no groups; it serves only to eliminate duplicate values. As indicated by the grammar, the clauses in an AQL query can appear in any order. To interpret a query, one can think of data as flowing down through the query from the first clause to the <tt>return</tt> clause.</p>

453

<p>The following example shows a FLWOR expression that selects and returns one user from the dataset FacebookUsers.</p>

454

455

456

<h5>Example<a name="Example"></a></h5>

457

458

459

<pre>for $user in dataset FacebookUsers

where $user.id = 8

return $user

</pre></div>

<p>The next example shows a FLWOR expression that joins two datasets, FacebookUsers and FacebookMessages, returning user/message pairs. The results contain one record per pair, with result records containing the user’s name and an entire message.</p></div>

464

465

<h5>Example<a name="Example"></a></h5>

466

467

468

<pre>for $user in dataset FacebookUsers

469

for $message in dataset FacebookMessages

470

where $message.author-id = $user.id

return

{

"uname": $user.name,

"message": $message.message

475

};

476

</pre></div>

477

<p>In the next example, a <tt>let</tt> clause is used to bind a variable to all of a user’s FacebookMessages. The query returns one record per user, with result records containing the user’s name and the set of all messages by that user.</p></div>

478

479

<h5>Example<a name="Example"></a></h5>

480

481

482

<pre>for $user in dataset FacebookUsers

483

let $messages :=

484

for $message in dataset FacebookMessages

485

where $message.author-id = $user.id

486

return $message.message

return

{

"uname": $user.name,

"messages": $messages

491

};

492

</pre></div>

493

<p>The following example returns all TwitterUsers ordered by their followers count (most followers first) and language. When ordering <tt>null</tt> is treated as being smaller than any other value if <tt>null</tt>s are encountered in the ordering key(s).</p></div>

494

495

<h5>Example<a name="Example"></a></h5>

496

497

498

<pre> for $user in dataset TwitterUsers

499

order by $user.followers_count desc, $user.lang asc

500

return $user

501

</pre></div>

502

<p>The next example illustrates the use of the <tt>group by</tt> clause in AQL. After the <tt>group by</tt> clause in the query, only variables that are either in the <tt>group by</tt> list or in the <tt>with</tt> list are in scope. The variables in the clause’s <tt>with</tt> list will each contain a collection of items following the <tt>group by</tt> clause; the collected items are the values that the source variable was bound to in the tuples that formed the group. For grouping <tt>null</tt> is handled as a single value.</p></div>

503

504

<h5>Example<a name="Example"></a></h5>

505

506

507

<pre> for $x in dataset FacebookMessages

508

let $messages := $x.message

509

group by $loc := $x.sender-location with $messages

return

{

"location" : $loc,

"message" : $messages

514

}

515

</pre></div>

516

<p>The use of the <tt>limit</tt> clause is illustrated in the next example.</p></div>

517

518

<h5>Example<a name="Example"></a></h5>

519

520

521

<pre> for $user in dataset TwitterUsers

522

order by $user.followers_count desc

limit 2

return $user

</pre></div>

<p>The final example shows how AQL’s <tt>distinct by</tt> clause works. Each variable in scope before the distinct clause is also in scope after the <tt>distinct by</tt> clause. This clause works similarly to <tt>group by</tt>, but for each variable that contains more than one value after the <tt>distinct by</tt> clause, one value is picked nondeterministically. (If the variable is in the <tt>distinct by</tt> list, then its value will be deterministic.) Nulls are treated as a single value when they occur in a grouping field.</p></div>

527

528

<h5>Example<a name="Example"></a></h5>

529

530

531

<pre> for $x in dataset FacebookMessages

532

distinct by $x.sender-location

533

return

534

{

535

"location" : $x.sender-location,

536

"message" : $x.message

537

}

538

</pre></div>

539

<p>In order to allow SQL fans to write queries in their favored ways, AQL provides synonyms: <i>from</i> for <i>for</i>, <i>select</i> for <i>return</i>, <i>with</i> for <i>let</i>, and <i>keeping</i> for <i>with</i> in the group by clause. The following query is such an example.</p></div>

540

541

<h5>Example<a name="Example"></a></h5>

542

543

544

<pre> from $x in dataset FacebookMessages

545

with $messages := $x.message

546

group by $loc := $x.sender-location keeping $messages

select

{

"location" : $loc,

"message" : $messages

551

}

552

</pre></div></div></div></div>

553

554

<h3>Conditional Expression<a name="Conditional_Expression"></a></h3>

555

556

557

<pre>IfThenElse ::= "if" "(" Expression ")" "then" Expression "else" Expression

558

</pre></div>

559

<p>A conditional expression is useful for choosing between two alternative values based on a boolean condition. If its first (<tt>if</tt>) expression is true, its second (<tt>then</tt>) expression’s value is returned, and otherwise its third (<tt>else</tt>) expression is returned.</p>

560

<p>The following example illustrates the form of a conditional expression.</p>

561

562

563

<h5>Example<a name="Example"></a></h5>

564

565

566

<pre>if (2 < 3) then "yes" else "no"

567

</pre></div></div></div></div>

568

569

<h3>Quantified Expressions<a name="Quantified_Expressions"></a></h3>

570

571

572

<pre>QuantifiedExpression ::= ( ( "some" ) | ( "every" ) ) Variable "in" Expression

573

( "," Variable "in" Expression )* "satisfies" Expression

574

</pre></div>

575

<p>Quantified expressions are used for expressing existential or universal predicates involving the elements of a collection.</p>

576

<p>The following pair of examples illustrate the use of a quantified expression to test that every (or some) element in the set [1, 2, 3] of integers is less than three. The first example yields <tt>false</tt> and second example yields <tt>true</tt>.</p>

577

<p>It is useful to note that if the set were instead the empty set, the first expression would yield <tt>true</tt> (“every” value in an empty set satisfies the condition) while the second expression would yield <tt>false</tt> (since there isn’t “some” value, as there are no values in the set, that satisfies the condition).</p>

578

579

580

<h5>Examples<a name="Examples"></a></h5>

581

582

583

<pre>every $x in [ 1, 2, 3 ] satisfies $x < 3

584

some $x in [ 1, 2, 3 ] satisfies $x < 3

585

</pre></div></div></div></div></div>

586

587

<h2><a name="Statements" id="Statements">3. Statements</a> <font size="4"><a href="#toc">[Back to TOC]</a></font><a name="a3._Statements_Back_to_TOC"></a></h2>

588

589

590

<pre>Statement ::= ( SingleStatement ( ";" )? )* <EOF>

591

SingleStatement ::= DataverseDeclaration

592

| FunctionDeclaration

| CreateStatement

| DropStatement

| LoadStatement

| SetStatement

| InsertStatement

| DeleteStatement

| Query

</pre></div>

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

602

<p>This section details the statements supported in the AQL language.</p>

603

604

<h3>Declarations<a name="Declarations"></a></h3>

605

606

607

<pre>DataverseDeclaration ::= "use" "dataverse" Identifier

608

</pre></div>

609

<p>The world of data in an AsterixDB cluster is organized into data namespaces called dataverses. To set the default dataverse for a series of statements, the use dataverse statement is provided.</p>

610

<p>As an example, the following statement sets the default dataverse to be TinySocial.</p>

611

612

613

<h5>Example<a name="Example"></a></h5>

614

615

616

<pre>use dataverse TinySocial;

617

</pre></div>

618

<p>The set statement in AQL is used to control aspects of the expression evalation context for queries.</p>

619

620

621

<pre>SetStatement ::= "set" Identifier StringLiteral

622

</pre></div>

623

<p>As an example, the following set statements request that Jaccard similarity with a similarity threshold 0.6 be used for set similarity matching when the ~= operator is used in a query expression.</p></div>

624

625

<h5>Example<a name="Example"></a></h5>

626

627

628

<pre>set simfunction "jaccard";

629

set simthreshold "0.6f";

630

</pre></div>

631

<p>When writing a complex AQL query, it can sometimes be helpful to define one or more auxilliary functions that each address a sub-piece of the overall query. The declare function statement supports the creation of such helper functions.</p>

632

633

634

<pre>FunctionDeclaration ::= "declare" "function" Identifier ParameterList "{" Expression "}"

635

ParameterList ::= "(" ( <VARIABLE> ( "," <VARIABLE> )* )? ")"

636

</pre></div>

637

<p>The following is a very simple example of a temporary AQL function definition.</p></div>

638

639

<h5>Example<a name="Example"></a></h5>

640

641

642

<pre>declare function add($a, $b) {

643

$a + $b

644

};

645

</pre></div></div></div></div>

646

647

<h3>Lifecycle Management Statements<a name="Lifecycle_Management_Statements"></a></h3>

648

649

650

<pre>CreateStatement ::= "create" ( DataverseSpecification

651

| TypeSpecification

652

| DatasetSpecification

653

| IndexSpecification

654

| FunctionSpecification )

655

656

QualifiedName ::= Identifier ( "." Identifier )?

657

DoubleQualifiedName ::= Identifier "." Identifier ( "." Identifier )?

658

</pre></div>

659

<p>The create statement in AQL is used for creating persistent artifacts in the context of dataverses. It can be used to create new dataverses, datatypes, datasets, indexes, and user-defined AQL functions.</p>

660

661

<h4>Dataverses<a name="Dataverses"></a></h4>

662

663

664

<pre>DataverseSpecification ::= "dataverse" Identifier IfNotExists ( "with format" StringLiteral )?

665

</pre></div>

666

<p>The create dataverse statement is used to create new dataverses. To ease the authoring of reusable AQL scripts, its optional IfNotExists clause allows creation to be requested either unconditionally or only if the the dataverse does not already exist. If this clause is absent, an error will be returned if the specified dataverse already exists. The <tt>with format</tt> clause is a placeholder for future functionality that can safely be ignored.</p>

667

<p>The following example creates a dataverse named TinySocial.</p>

668

669

<h5>Example<a name="Example"></a></h5>

670

671

672

<pre>create dataverse TinySocial;

673

</pre></div></div></div>

674

675

<h4>Types<a name="Types"></a></h4>

676

677

678

<pre>TypeSpecification ::= "type" FunctionOrTypeName IfNotExists "as" TypeExpr

679

FunctionOrTypeName ::= QualifiedName

680

IfNotExists ::= ( "if not exists" )?

681

TypeExpr ::= RecordTypeDef | TypeReference | OrderedListTypeDef | UnorderedListTypeDef

682

RecordTypeDef ::= ( "closed" | "open" )? "{" ( RecordField ( "," RecordField )* )? "}"

683

RecordField ::= Identifier ":" ( TypeExpr ) ( "?" )?

684

NestedField ::= Identifier ( "." Identifier )*

685

OpenField ::= NestedField ( ":" TypeReference )?

686

TypeReference ::= Identifier

687

OrderedListTypeDef ::= "[" ( TypeExpr ) "]"

688

UnorderedListTypeDef ::= "{{" ( TypeExpr ) "}}"

689

</pre></div>

690

<p>The create type statement is used to create a new named ADM datatype. This type can then be used to create datasets or utilized when defining one or more other ADM datatypes. Much more information about the Asterix Data Model (ADM) is available in the <a href="datamodel.html">data model reference guide</a> to ADM. A new type can be a record type, a renaming of another type, an ordered list type, or an unordered list type. A record type can be defined as being either open or closed. Instances of a closed record type are not permitted to contain fields other than those specified in the create type statement. Instances of an open record type may carry additional fields, and open is the default for a new type (if neither option is specified).</p>

691

<p>The following example creates a new ADM record type called FacebookUser type. Since it is closed, its instances will contain only what is specified in the type definition. The first four fields are traditional typed name/value pairs. The friend-ids field is an unordered list of 32-bit integers. The employment field is an ordered list of instances of another named record type, EmploymentType.</p>

692

693

<h5>Example<a name="Example"></a></h5>

694

695

696

<pre>create type FacebookUserType as closed {

"id" : int32,

"alias" : string,

"name" : string,

"user-since" : datetime,

701

"friend-ids" : {{ int32 }},

702

"employment" : [ EmploymentType ]

703

}

704

</pre></div></div></div>

705

706

<h4>Datasets<a name="Datasets"></a></h4>

707

708

709

<pre>DatasetSpecification ::= "internal"? "dataset" QualifiedName "(" Identifier ")" IfNotExists

710

PrimaryKey ( "on" Identifier )? ( "hints" Properties )?

711

( "using" "compaction" "policy" CompactionPolicy ( Configuration )? )?

712

( "with filter on" Identifier )?

713

| "external" "dataset" QualifiedName "(" Identifier ")" IfNotExists

714

"using" AdapterName Configuration ( "hints" Properties )?

715

( "using" "compaction" "policy" CompactionPolicy ( Configuration )? )?

716

AdapterName ::= Identifier

717

Configuration ::= "(" ( KeyValuePair ( "," KeyValuePair )* )? ")"

718

KeyValuePair ::= "(" StringLiteral "=" StringLiteral ")"

719

Properties ::= ( "(" Property ( "," Property )* ")" )?

720

Property ::= Identifier "=" ( StringLiteral | IntegerLiteral )

721

FunctionSignature ::= FunctionOrTypeName "@" IntegerLiteral

722

PrimaryKey ::= "primary" "key" NestedField ( "," NestedField )*

723

CompactionPolicy ::= Identifier

724

PrimaryKey ::= "primary" "key" Identifier ( "," Identifier )*

725

</pre></div>

726

<p>The create dataset statement is used to create a new dataset. Datasets are named, unordered collections of ADM record instances; they are where data lives persistently and are the targets for queries in AsterixDB. Datasets are typed, and AsterixDB will ensure that their contents conform to their type definitions. An Internal dataset (the default) is a dataset that is stored in and managed by AsterixDB. It must have a specified unique primary key that can be used to partition data across nodes of an AsterixDB cluster. The primary key is also used in secondary indexes to uniquely identify the indexed primary data records. Optionally, a filter can be created on a field to further optimize range queries with predicates on the filter’s field. (Refer to <a href="filters.html">Filter-Based LSM Index Acceleration</a> for more information about filters.)</p>

727

<p>An External dataset is stored outside of AsterixDB (currently datasets in HDFS or on the local filesystem(s) of the cluster’s nodes are supported). External dataset support allows AQL queries to treat external data as though it were stored in AsterixDB, making it possible to query “legacy” file data (e.g., Hive data) without having to physically import it into AsterixDB. For an external dataset, an appropriate adapter must be selected to handle the nature of the desired external data. (See the <a href="externaldata.html">guide to external data</a> for more information on the available adapters.)</p>

728

<p>When creating a dataset, it is possible to choose a merge policy that controls which of the underlaying LSM storage components to be merged. Currently, AsterixDB provides four different merge policies that can be configured per dataset: no-merge, constant, prefix, and correlated-prefix. The no-merge policy simply never merges disk components. While the constant policy merges disk components when the number of components reaches some constant number k, which can be configured by the user. The prefix policy relies on component sizes and the number of components to decide which components to merge. Specifically, it works by first trying to identify the smallest ordered (oldest to newest) sequence of components such that the sequence does not contain a single component that exceeds some threshold size M and that either the sum of the component’s sizes exceeds M or the number of components in the sequence exceeds another threshold C. If such a sequence of components exists, then each of the components in the sequence are merged together to form a single component. Finally, the correlated-prefix is similar to the prefix policy but it delegates the decision of merging the disk components of all the indexes in a dataset to the primary index. When the policy decides that the primary index needs to be merged (using the same decision criteria as for the prefix policy), then it will issue successive merge requests on behalf of all other indexes associated with the same dataset. The default policy for AsterixDB is the prefix policy except when there is a filter on a dataset, where the preferred policy for filters is the correlated-prefix.</p>

729

<p>The following example creates an internal dataset for storing FacefookUserType records. It specifies that their id field is their primary key.</p>

730

731

<h5>Example<a name="Example"></a></h5>

732

733

734

<pre>create internal dataset FacebookUsers(FacebookUserType) primary key id;

735

</pre></div>

736

<p>The next example creates an external dataset for storing LineitemType records. The choice of the <tt>hdfs</tt> adapter means that its data will reside in HDFS. The create statement provides parameters used by the hdfs adapter: the URL and path needed to locate the data in HDFS and a description of the data format.</p></div>

737

738

<h5>Example<a name="Example"></a></h5>

739

740

741

<pre>create external dataset Lineitem('LineitemType) using hdfs (

742

("hdfs"="hdfs://HOST:PORT"),

743

("path"="HDFS_PATH"),

744

("input-format"="text-input-format"),

745

("format"="delimited-text"),

746

("delimiter"="|"));

747

</pre></div></div></div>

748

749

<h4>Indices<a name="Indices"></a></h4>

750

751

752

<pre>IndexSpecification ::= "index" Identifier IfNotExists "on" QualifiedName

753

"(" ( OpenField ) ( "," OpenField )* ")" ( "type" IndexType )? ( "enforced" )?

754

IndexType ::= "btree"

755

| "rtree"

756

| "keyword"

757

| "ngram" "(" IntegerLiteral ")"

758

</pre></div>

759

<p>The create index statement creates a secondary index on one or more fields of a specified dataset. Supported index types include <tt>btree</tt> for totally ordered datatypes, <tt>rtree</tt> for spatial data, and <tt>keyword</tt> and <tt>ngram</tt> for textual (string) data. Index could be created on arbitrary nested fields by providing valid path expression as an indexed field identifier. An index field is not required to be part of the datatype associated with a dataset if that datatype is declared as open, field type is provided along with it’s type and <tt>enforced</tt> keyword is specified in the end of index definition. <tt>Enforcing</tt> an open field will introduce a load-time check, which will make sure that the actual type of an indexed field (if such field exists in the record) matches the specified field type.</p>

760

<p>The following example creates a btree index called fbAuthorIdx on the author-id field of the FacebookMessages dataset. This index can be useful for accelerating exact-match queries, range search queries, and joins involving the author-id field.</p>

761

762

<h5>Example<a name="Example"></a></h5>

763

764

765

<pre>create index fbAuthorIdx on FacebookMessages(author-id) type btree enforced;

766

</pre></div>

767

<p>The following example creates an open btree index called fbSendTimeIdx on the open send-time field of the FacebookMessages dataset having datetime type. This index can be useful for accelerating exact-match queries, range search queries, and joins involving the send-time field.</p></div>

768

769

<h5>Example<a name="Example"></a></h5>

770

771

772

<pre>create index fbSendTimeIdx on FacebookMessages(send-time:datetime) type btree;

773

</pre></div>

774

<p>The following example creates a btree index called twUserScrNameIdx on the screen-name field, which is a nested field of the user field in the TweetMessages dataset. This index can be useful for accelerating exact-match queries, range search queries, and joins involving the screen-name field.</p></div>

775

776

<h5>Example<a name="Example"></a></h5>

777

778

779

<pre>create index twUserScrNameIdx on TweetMessages(user.screen-name) type btree;

780

</pre></div>

781

<p>The following example creates an rtree index called fbSenderLocIdx on the sender-location field of the FacebookMessages dataset. This index can be useful for accelerating queries that use the <a href="functions.html#spatial-intersect"><tt>spatial-intersect</tt> function</a> in a predicate involving the sender-location field.</p></div>

782

783

<h5>Example<a name="Example"></a></h5>

784

785

786

<pre>create index fbSenderLocIndex on FacebookMessages(sender-location) type rtree;

787

</pre></div>

788

<p>The following example creates a 3-gram index called fbUserIdx on the name field of the FacebookUsers dataset. This index can be used to accelerate some similarity or substring maching queries on the name field. For details refer to the <a href="similarity.html#NGram_Index">document on similarity queries</a>.</p></div>

789

790

<h5>Example<a name="Example"></a></h5>

791

792

793

<pre>create index fbUserIdx on FacebookUsers(name) type ngram(3);

794

</pre></div>

795

<p>The following example creates a keyword index called fbMessageIdx on the message field of the FacebookMessages dataset. This keyword index can be used to optimize queries with token-based similarity predicates on the message field. For details refer to the <a href="similarity.html#Keyword_Index">document on similarity queries</a>.</p></div>

796

797

<h5>Example<a name="Example"></a></h5>

798

799

800

<pre>create index fbMessageIdx on FacebookMessages(message) type keyword;

801

</pre></div></div></div>

802

803

<h4>Functions<a name="Functions"></a></h4>

804

<p>The create function statement creates a named function that can then be used and reused in AQL queries. The body of a function can be any AQL expression involving the function’s parameters.</p>

805

806

807

<pre>FunctionSpecification ::= "function" FunctionOrTypeName IfNotExists ParameterList "{" Expression "}"

808

</pre></div>

809

<p>The following is a very simple example of a create function statement. It differs from the declare function example shown previously in that it results in a function that is persistently registered by name in the specified dataverse.</p>

810

811

<h5>Example<a name="Example"></a></h5>

812

813

814

<pre>create function add($a, $b) {

815

$a + $b

816

};

817

</pre></div></div></div>

818

819

<h4>Removal<a name="Removal"></a></h4>

820

821

822

<pre>DropStatement ::= "drop" ( "dataverse" Identifier IfExists

823

| "type" FunctionOrTypeName IfExists

824

| "dataset" QualifiedName IfExists

825

| "index" DoubleQualifiedName IfExists

826

| "function" FunctionSignature IfExists )

827

IfExists ::= ( "if" "exists" )?

828

</pre></div>

829

<p>The drop statement in AQL is the inverse of the create statement. It can be used to drop dataverses, datatypes, datasets, indexes, and functions.</p>

830

<p>The following examples illustrate uses of the drop statement.</p>

831

832

<h5>Example<a name="Example"></a></h5>

833

834

835

<pre>drop dataset FacebookUsers if exists;

836

837

drop index fbSenderLocIndex;

838

839

drop type FacebookUserType;

840

841

drop dataverse TinySocial;

842

843

drop function add;

844

</pre></div></div></div></div>

845

846

<h3>Import/Export Statements<a name="ImportExport_Statements"></a></h3>

847

848

849

<pre>LoadStatement ::= "load" "dataset" QualifiedName "using" AdapterName Configuration ( "pre-sorted" )?

850

</pre></div>

851

<p>The load statement is used to initially populate a dataset via bulk loading of data from an external file. An appropriate adapter must be selected to handle the nature of the desired external data. The load statement accepts the same adapters and the same parameters as external datasets. (See the <a href="externaldata.html">guide to external data</a> for more information on the available adapters.)</p>

852

<p>The following example shows how to bulk load the FacebookUsers dataset from an external file containing data that has been prepared in ADM format.</p>

853

854

855

<h5>Example<a name="Example"></a></h5>

856

857

858

<pre>load dataset FacebookUsers using localfs

859

(("path"="localhost:///Users/zuck/AsterixDB/load/fbu.adm"),("format"="adm"));

860

</pre></div></div></div></div>

861

862

<h3>Modification Statements<a name="Modification_Statements"></a></h3>

863

864

<h4>Insert<a name="Insert"></a></h4>

865

866

867

<pre>InsertStatement ::= "insert" "into" "dataset" QualifiedName Query

868

</pre></div>

869

<p>The AQL insert statement is used to insert data into a dataset. The data to be inserted 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. Inserts in AsterixDB are processed transactionally, with the scope of each insert transaction being the insertion of a single object plus its affiliated secondary index entries (if any). If the query part of an insert returns a single object, then the insert statement itself will be a single, atomic transaction. If the query part returns multiple objects, then each object inserted will be handled independently as a tranaction.</p>

870

<p>The following example illustrates a query-based insertion.</p>

871

872

<h5>Example<a name="Example"></a></h5>

873

874

875

<pre>insert into dataset UsersCopy (for $user in dataset FacebookUsers return $user)

876

</pre></div></div></div>

877

878

<h4>Delete<a name="Delete"></a></h4>

879

880

881

<pre>DeleteStatement ::= "delete" Variable "from" "dataset" QualifiedName ( "where" Expression )?

882

</pre></div>

883

<p>The AQL delete statement is used to delete data from a target dataset. The data to be deleted is identified by a boolean expression involving the variable bound to the target dataset in the delete statement. Deletes in AsterixDB are processed transactionally, with the scope of each delete transaction being the deletion of a single object plus its affiliated secondary index entries (if any). If the boolean expression for a delete identifies a single object, then the delete statement itself will be a single, atomic transaction. If the expression identifies multiple objects, then each object deleted will be handled independently as a transaction.</p>

884

<p>The following example illustrates a single-object deletion.</p>

885

886

<h5>Example<a name="Example"></a></h5>

887

888

889

<pre>delete $user from dataset FacebookUsers where $user.id = 8;

890

</pre></div>

891