CtrlK
BlogDocsLog inGet started
Tessl Logo

tessl/maven-software-amazon-awssdk--dynamodb

AWS SDK for Java v2 DynamoDB client library for interacting with Amazon DynamoDB NoSQL database service

Overview
Eval results
Files

partiql.mddocs/

PartiQL

SQL-compatible query language support for DynamoDB with batch execution capabilities. PartiQL provides a familiar SQL-like syntax for querying and manipulating DynamoDB data while maintaining DynamoDB's performance characteristics and data model.

Capabilities

Execute Statement

Execute a single PartiQL statement against DynamoDB.

/**
 * Executes a single PartiQL statement and returns the result
 * @param request - The request containing PartiQL statement and parameters
 * @return Response containing query results and consumed capacity
 */
ExecuteStatementResponse executeStatement(ExecuteStatementRequest request);

class ExecuteStatementRequest {
    static Builder builder();
    
    /** The PartiQL statement representing the operation to run */
    String statement();
    Builder statement(String statement);
    
    /** The parameters for the PartiQL statement, if any */
    List<AttributeValue> parameters();
    Builder parameters(Collection<AttributeValue> parameters);
    
    /** The consistency of a read operation */
    Boolean consistentRead();
    Builder consistentRead(Boolean consistentRead);
    
    /** Set this value to get remaining results, if NextToken was returned in the previous response */
    String nextToken();
    Builder nextToken(String nextToken);
    
    /** The maximum number of items to return in the response */
    Integer limit();
    Builder limit(Integer limit);
    
    /** Determines the level of detail about consumed capacity returned */
    ReturnConsumedCapacity returnConsumedCapacity();
    Builder returnConsumedCapacity(ReturnConsumedCapacity returnConsumedCapacity);
}

class ExecuteStatementResponse {
    /** The response to a PartiQL statement */
    List<Map<String, AttributeValue>> items();
    
    /** If a read operation was used, this property will contain the NextToken */
    String nextToken();
    
    /** The capacity units consumed by an operation */
    ConsumedCapacity consumedCapacity();
    
    /** The primary key of the item where the operation stopped for pagination */
    Map<String, AttributeValue> lastEvaluatedKey();
}

Usage Examples:

import software.amazon.awssdk.services.dynamodb.DynamoDbClient;
import software.amazon.awssdk.services.dynamodb.model.*;
import java.util.List;
import java.util.Map;

DynamoDbClient client = DynamoDbClient.builder().build();

// SELECT query with parameters
ExecuteStatementResponse response = client.executeStatement(
    ExecuteStatementRequest.builder()
        .statement("SELECT * FROM \"Users\" WHERE userId = ? AND age > ?")
        .parameters(List.of(
            AttributeValue.builder().s("user123").build(),
            AttributeValue.builder().n("18").build()
        ))
        .consistentRead(true)
        .limit(10)
        .build()
);

// Process results
List<Map<String, AttributeValue>> items = response.items();
for (Map<String, AttributeValue> item : items) {
    String name = item.get("name").s();
    String email = item.get("email").s();
    System.out.println("User: " + name + " (" + email + ")");
}

// INSERT statement
client.executeStatement(
    ExecuteStatementRequest.builder()
        .statement("INSERT INTO \"Users\" VALUE {'userId': ?, 'name': ?, 'email': ?, 'age': ?}")
        .parameters(List.of(
            AttributeValue.builder().s("user456").build(),
            AttributeValue.builder().s("Jane Doe").build(),
            AttributeValue.builder().s("jane@example.com").build(),
            AttributeValue.builder().n("25").build()
        ))
        .build()
);

// UPDATE statement
client.executeStatement(
    ExecuteStatementRequest.builder()
        .statement("UPDATE \"Users\" SET email = ? WHERE userId = ?")
        .parameters(List.of(
            AttributeValue.builder().s("newemail@example.com").build(),
            AttributeValue.builder().s("user123").build()
        ))
        .build()
);

// DELETE statement
client.executeStatement(
    ExecuteStatementRequest.builder()
        .statement("DELETE FROM \"Users\" WHERE userId = ?")
        .parameters(List.of(
            AttributeValue.builder().s("user789").build()
        ))
        .build()
);

Batch Execute Statement

Execute multiple PartiQL statements in a batch for improved performance.

/**
 * Executes multiple PartiQL statements in a batch
 * @param request - The request containing multiple PartiQL statements
 * @return Response containing results for each statement
 */
BatchExecuteStatementResponse batchExecuteStatement(BatchExecuteStatementRequest request);

class BatchExecuteStatementRequest {
    static Builder builder();
    
    /** The list of PartiQL statements representing the batch to run */
    List<BatchStatementRequest> statements();
    Builder statements(Collection<BatchStatementRequest> statements);
    
    /** Determines the level of detail about consumed capacity returned */
    ReturnConsumedCapacity returnConsumedCapacity();
    Builder returnConsumedCapacity(ReturnConsumedCapacity returnConsumedCapacity);
}

class BatchStatementRequest {
    static Builder builder();
    
    /** A valid PartiQL statement */
    String statement();
    Builder statement(String statement);
    
    /** The parameters associated with a PartiQL statement in the batch request */
    List<AttributeValue> parameters();
    Builder parameters(Collection<AttributeValue> parameters);
    
    /** The read consistency model for the batch operation */
    Boolean consistentRead();
    Builder consistentRead(Boolean consistentRead);
    
    /** An optional parameter for the ReturnValues on the PartiQL statement */
    ReturnValuesOnConditionCheckFailure returnValuesOnConditionCheckFailure();
    Builder returnValuesOnConditionCheckFailure(ReturnValuesOnConditionCheckFailure returnValuesOnConditionCheckFailure);
}

class BatchExecuteStatementResponse {
    /** The response to each PartiQL statement in the batch */
    List<BatchStatementResponse> responses();
    
    /** The capacity units consumed by the batch operation */
    List<ConsumedCapacity> consumedCapacity();
}

class BatchStatementResponse {
    /** The error associated with the failed PartiQL batch statement */
    BatchStatementError error();
    
    /** The table name associated with a failed PartiQL batch statement */
    String tableName();
    
    /** A PartiQL batch statement response */
    Map<String, AttributeValue> item();
}

class BatchStatementError {
    /** The error code associated with the failed PartiQL batch statement */
    BatchStatementErrorCodeEnum code();
    
    /** The error message associated with the failed PartiQL batch statement */
    String message();
    
    /** The item which caused the condition check to fail */
    Map<String, AttributeValue> item();
}

Usage Examples:

// Batch read operations
List<BatchStatementRequest> statements = List.of(
    BatchStatementRequest.builder()
        .statement("SELECT * FROM \"Users\" WHERE userId = ?")
        .parameters(List.of(AttributeValue.builder().s("user1").build()))
        .build(),
    
    BatchStatementRequest.builder()
        .statement("SELECT * FROM \"Users\" WHERE userId = ?")
        .parameters(List.of(AttributeValue.builder().s("user2").build()))
        .build(),
    
    BatchStatementRequest.builder()
        .statement("SELECT * FROM \"Orders\" WHERE orderId = ?")
        .parameters(List.of(AttributeValue.builder().s("order123").build()))
        .build()
);

BatchExecuteStatementResponse response = client.batchExecuteStatement(
    BatchExecuteStatementRequest.builder()
        .statements(statements)
        .returnConsumedCapacity(ReturnConsumedCapacity.TOTAL)
        .build()
);

// Process results
List<BatchStatementResponse> responses = response.responses();
for (int i = 0; i < responses.size(); i++) {
    BatchStatementResponse statementResponse = responses.get(i);
    
    if (statementResponse.error() != null) {
        System.err.println("Statement " + i + " failed: " + 
            statementResponse.error().message());
    } else if (statementResponse.item() != null) {
        Map<String, AttributeValue> item = statementResponse.item();
        System.out.println("Statement " + i + " returned item: " + item);
    }
}

// Batch write operations (must be all reads or all writes)
List<BatchStatementRequest> writeStatements = List.of(
    BatchStatementRequest.builder()
        .statement("INSERT INTO \"Products\" VALUE {'productId': ?, 'name': ?, 'price': ?}")
        .parameters(List.of(
            AttributeValue.builder().s("prod1").build(),
            AttributeValue.builder().s("Widget A").build(),
            AttributeValue.builder().n("29.99").build()
        ))
        .build(),
    
    BatchStatementRequest.builder()
        .statement("UPDATE \"Inventory\" SET quantity = quantity - ? WHERE productId = ?")
        .parameters(List.of(
            AttributeValue.builder().n("5").build(),
            AttributeValue.builder().s("prod1").build()
        ))
        .build()
);

client.batchExecuteStatement(
    BatchExecuteStatementRequest.builder()
        .statements(writeStatements)
        .build()
);

Execute Transaction

Execute multiple PartiQL statements within a single atomic transaction (covered in detail in the Transactions document).

/**
 * Executes multiple PartiQL statements within a transaction block
 * @param request - The request containing PartiQL statements and options
 * @return Response containing statement results and consumed capacity
 */
ExecuteTransactionResponse executeTransaction(ExecuteTransactionRequest request);

PartiQL Syntax and Features

Supported Operations

PartiQL in DynamoDB supports the following SQL-like operations:

SELECT Statements

// Basic SELECT
"SELECT * FROM \"TableName\" WHERE pk = ?"

// SELECT with projection
"SELECT userId, name, email FROM \"Users\" WHERE userId = ?"

// SELECT with conditions
"SELECT * FROM \"Orders\" WHERE customerId = ? AND orderDate BETWEEN ? AND ?"

// SELECT with LIMIT
"SELECT * FROM \"Products\" WHERE category = ? LIMIT 10"

INSERT Statements

// INSERT with VALUE
"INSERT INTO \"Users\" VALUE {'userId': ?, 'name': ?, 'email': ?}"

// INSERT with explicit attributes
"INSERT INTO \"Users\" (userId, name, email) VALUES (?, ?, ?)"

UPDATE Statements

// SET attributes
"UPDATE \"Users\" SET email = ?, lastLogin = ? WHERE userId = ?"

// ADD to numeric attributes
"UPDATE \"Users\" SET loginCount = loginCount + ? WHERE userId = ?"

// REMOVE attributes
"UPDATE \"Users\" REMOVE tempData WHERE userId = ?"

DELETE Statements

// DELETE entire item
"DELETE FROM \"Users\" WHERE userId = ?"

// Conditional DELETE
"DELETE FROM \"Users\" WHERE userId = ? AND lastLogin < ?"

Data Types and Literals

// String literals
"'string value'"
"\"double-quoted string\""

// Number literals  
"123"
"123.45"
"-42"

// Boolean literals
"true"
"false"

// NULL literal
"null"

// Binary data (base64 encoded)
"'binary data'"

// List literals
"[1, 2, 3]"
"['a', 'b', 'c']"

// Map literals  
"{'key1': 'value1', 'key2': 123}"

Operators and Functions

Comparison Operators

"="     // Equal
"<>"    // Not equal  
"<"     // Less than
"<="    // Less than or equal
">"     // Greater than
">="    // Greater than or equal
"BETWEEN ? AND ?"   // Range comparison
"IN (?, ?, ?)"      // Set membership

Logical Operators

"AND"   // Logical AND
"OR"    // Logical OR  
"NOT"   // Logical NOT

Conditional Functions

// attribute_exists function
"SELECT * FROM \"Users\" WHERE attribute_exists(email)"

// attribute_not_exists function  
"UPDATE \"Users\" SET email = ? WHERE userId = ? AND attribute_not_exists(email)"

// attribute_type function
"SELECT * FROM \"Users\" WHERE attribute_type(age, 'N')"

// size function
"SELECT * FROM \"Users\" WHERE size(tags) > ?"

// begins_with function
"SELECT * FROM \"Users\" WHERE begins_with(name, ?)"

// contains function
"SELECT * FROM \"Users\" WHERE contains(tags, ?)"

Working with Complex Data Types

Maps and Lists

// Access nested map attributes
"SELECT user.profile.name FROM \"Users\" WHERE userId = ?"

// Access list elements by index
"SELECT tags[0] FROM \"Users\" WHERE userId = ?"

// Update nested attributes
"UPDATE \"Users\" SET profile.email = ? WHERE userId = ?"

// Add to list
"UPDATE \"Users\" SET tags = list_append(tags, ?) WHERE userId = ?"

Sets

// String sets
"UPDATE \"Users\" SET skills = ? WHERE userId = ?"  // Parameters: SS attribute

// Number sets  
"UPDATE \"Products\" SET sizes = ? WHERE productId = ?"  // Parameters: NS attribute

// Add to set
"UPDATE \"Users\" SET skills = skills + ? WHERE userId = ?"

Pagination

Handle large result sets with pagination:

String nextToken = null;
List<Map<String, AttributeValue>> allItems = new ArrayList<>();

do {
    ExecuteStatementRequest.Builder requestBuilder = ExecuteStatementRequest.builder()
        .statement("SELECT * FROM \"LargeTable\" WHERE category = ?")
        .parameters(List.of(AttributeValue.builder().s("electronics").build()))
        .limit(100);
    
    if (nextToken != null) {
        requestBuilder.nextToken(nextToken);
    }
    
    ExecuteStatementResponse response = client.executeStatement(requestBuilder.build());
    allItems.addAll(response.items());
    nextToken = response.nextToken();
    
} while (nextToken != null);

System.out.println("Retrieved " + allItems.size() + " total items");

Error Handling

try {
    ExecuteStatementResponse response = client.executeStatement(request);
    // Process successful response
    
} catch (ValidationException e) {
    System.err.println("Invalid PartiQL syntax: " + e.getMessage());
    
} catch (ResourceNotFoundException e) {
    System.err.println("Table or index not found: " + e.getMessage());
    
} catch (ConditionalCheckFailedException e) {
    System.err.println("Condition expression failed: " + e.getMessage());
    
} catch (ProvisionedThroughputExceededException e) {
    System.err.println("Throughput exceeded, implement retry with backoff");
    
} catch (DynamoDbException e) {
    System.err.println("DynamoDB error: " + e.getMessage());
}

PartiQL Best Practices

Performance Considerations

  1. Use Parameters: Always use parameterized queries to avoid SQL injection and improve performance:
// Good - parameterized
"SELECT * FROM \"Users\" WHERE userId = ?"

// Avoid - string concatenation
"SELECT * FROM \"Users\" WHERE userId = '" + userId + "'"
  1. Efficient Queries: Structure queries to use primary keys and indexes:
// Efficient - uses primary key
"SELECT * FROM \"Orders\" WHERE customerId = ? AND orderDate = ?"

// Less efficient - requires full table scan
"SELECT * FROM \"Orders\" WHERE status = ?"
  1. Batch Operations: Use batch execution for multiple related operations:
// Better - batch multiple reads
List<BatchStatementRequest> batchReads = ...;
client.batchExecuteStatement(...);

// Less efficient - multiple individual calls
for (String userId : userIds) {
    client.executeStatement(...);
}

Data Modeling

  1. Table and Attribute Names: Always quote table and reserved word attribute names:
"SELECT * FROM \"Users\" WHERE \"name\" = ?"  // 'name' is reserved
  1. Consistent Data Types: Ensure parameters match expected data types:
// Correct type matching
.parameters(List.of(
    AttributeValue.builder().s("string_value").build(),    // String
    AttributeValue.builder().n("123").build(),             // Number
    AttributeValue.builder().bool(true).build()            // Boolean
))
  1. Conditional Logic: Use condition expressions for data integrity:
"UPDATE \"Users\" SET email = ? WHERE userId = ? AND attribute_exists(userId)"

Install with Tessl CLI

npx tessl i tessl/maven-software-amazon-awssdk--dynamodb

docs

backup-restore.md

data-operations.md

global-tables.md

import-export.md

index.md

partiql.md

table-management.md

transactions.md

tile.json