Run batches of PartiQL statements on a DynamoDB table using an Amazon SDK - Amazon DynamoDB
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China.

Run batches of PartiQL statements on a DynamoDB table using an Amazon SDK

The following code examples show how to run batches of PartiQL statements on a DynamoDB table.

.NET
Amazon SDK for .NET
Tip

To learn how to set up and run this example, see GitHub.

Use batches of INSERT statements to add items.

/// <summary> /// /// </summary> /// <param name="tableName"></param> /// <param name="title1"></param> /// <param name="title2"></param> /// <param name="year1"></param> /// <param name="year2"></param> /// <returns></returns> public static async Task<bool> GetBatch( string tableName, string title1, string title2, int year1, int year2) { var getBatch = $"SELECT FROM {tableName} WHERE title = ? AND year = ?"; var statements = new List<BatchStatementRequest> { new BatchStatementRequest { Statement = getBatch, Parameters = new List<AttributeValue> { new AttributeValue { S = title1 }, new AttributeValue { N = year1.ToString() }, }, }, new BatchStatementRequest { Statement = getBatch, Parameters = new List<AttributeValue> { new AttributeValue { S = title2 }, new AttributeValue { N = year2.ToString() }, }, } }; var response = await Client.BatchExecuteStatementAsync(new BatchExecuteStatementRequest { Statements = statements, }); if (response.Responses.Count > 0) { response.Responses.ForEach(r => { Console.WriteLine($"{r.Item["title"]}\t{r.Item["year"]}"); }); return true; } else { Console.WriteLine($"Couldn't find either {title1} or {title2}."); return false; } }

Use batches of SELECT statements to get items.

/// <summary> /// Inserts movies imported from a JSON file into the movie table by /// using an Amazon DynamoDB PartiQL INSERT statement. /// </summary> /// <param name="tableName">The name of the table into which the movie /// information will be inserted.</param> /// <param name="movieFileName">The name of the JSON file that contains /// movie information.</param> /// <returns>A Boolean value that indicates the success or failure of /// the insert operation.</returns> public static async Task<bool> InsertMovies(string tableName, string movieFileName) { // Get the list of movies from the JSON file. var movies = ImportMovies(movieFileName); var success = false; if (movies is not null) { // Insert the movies in a batch using PartiQL. Because the // batch can contain a maximum of 25 items, insert 25 movies // at a time. string insertBatch = $"INSERT INTO {tableName} VALUE {{'title': ?, 'year': ?}}"; var statements = new List<BatchStatementRequest>(); try { for (var indexOffset = 0; indexOffset < 250; indexOffset += 25) { for (var i = indexOffset; i < indexOffset + 25; i++) { statements.Add(new BatchStatementRequest { Statement = insertBatch, Parameters = new List<AttributeValue> { new AttributeValue { S = movies[i].Title }, new AttributeValue { N = movies[i].Year.ToString() }, }, }); } var response = await Client.BatchExecuteStatementAsync(new BatchExecuteStatementRequest { Statements = statements, }); // Wait between batches for movies to be successfully added. System.Threading.Thread.Sleep(3000); success = response.HttpStatusCode == System.Net.HttpStatusCode.OK; // Clear the list of statements for the next batch. statements.Clear(); } } catch (AmazonDynamoDBException ex) { Console.WriteLine(ex.Message); } } return success; } /// <summary> /// Loads the contents of a JSON file into a list of movies to be /// added to the DynamoDB table. /// </summary> /// <param name="movieFileName">The full path to the JSON file.</param> /// <returns>A generic list of movie objects.</returns> public static List<Movie> ImportMovies(string movieFileName) { if (!File.Exists(movieFileName)) { return null; } using var sr = new StreamReader(movieFileName); string json = sr.ReadToEnd(); var allMovies = JsonConvert.DeserializeObject<List<Movie>>(json); if (allMovies is not null) { // Return the first 250 entries. return allMovies.GetRange(0, 250); } else { return null; } }

Use batches of UPDATE statements to update items.

/// <summary> /// Updates information for multiple movies. /// </summary> /// <param name="tableName">The name of the table containing the /// movies to be updated.</param> /// <param name="producer1">The producer name for the first movie /// to update.</param> /// <param name="title1">The title of the first movie.</param> /// <param name="year1">The year that the first movie was released.</param> /// <param name="producer2">The producer name for the second /// movie to update.</param> /// <param name="title2">The title of the second movie.</param> /// <param name="year2">The year that the second movie was released.</param> /// <returns>A Boolean value that indicates the success of the update.</returns> public static async Task<bool> UpdateBatch( string tableName, string producer1, string title1, int year1, string producer2, string title2, int year2) { string updateBatch = $"UPDATE {tableName} SET Producer=? WHERE title = ? AND year = ?"; var statements = new List<BatchStatementRequest> { new BatchStatementRequest { Statement = updateBatch, Parameters = new List<AttributeValue> { new AttributeValue { S = producer1 }, new AttributeValue { S = title1 }, new AttributeValue { N = year1.ToString() }, }, }, new BatchStatementRequest { Statement = updateBatch, Parameters = new List<AttributeValue> { new AttributeValue { S = producer2 }, new AttributeValue { S = title2 }, new AttributeValue { N = year2.ToString() }, }, } }; var response = await Client.BatchExecuteStatementAsync(new BatchExecuteStatementRequest { Statements = statements, }); return response.HttpStatusCode == System.Net.HttpStatusCode.OK; }

Use batches of DELETE statements to delete items.

/// <summary> /// Deletes multiple movies using a PartiQL BatchExecuteAsync /// statement. /// </summary> /// <param name="tableName">The name of the table containing the /// moves that will be deleted.</param> /// <param name="title1">The title of the first movie.</param> /// <param name="year1">The year the first movie was released.</param> /// <param name="title2">The title of the second movie.</param> /// <param name="year2">The year the second movie was released.</param> /// <returns>A Boolean value indicating the success of the operation.</returns> public static async Task<bool> DeleteBatch( string tableName, string title1, int year1, string title2, int year2) { string updateBatch = $"DELETE FROM {tableName} WHERE title = ? AND year = ?"; var statements = new List<BatchStatementRequest> { new BatchStatementRequest { Statement = updateBatch, Parameters = new List<AttributeValue> { new AttributeValue { S = title1 }, new AttributeValue { N = year1.ToString() }, }, }, new BatchStatementRequest { Statement = updateBatch, Parameters = new List<AttributeValue> { new AttributeValue { S = title2 }, new AttributeValue { N = year2.ToString() }, }, } }; var response = await Client.BatchExecuteStatementAsync(new BatchExecuteStatementRequest { Statements = statements, }); return response.HttpStatusCode == System.Net.HttpStatusCode.OK; }
Go
SDK for Go V2
Tip

To learn how to set up and run this example, see GitHub.

Use batches of INSERT statements to add items.

// AddMovieBatch runs a batch of PartiQL INSERT statements to add multiple movies to the // DynamoDB table. func (runner PartiQLRunner) AddMovieBatch(movies []Movie) error { statementRequests := make([]types.BatchStatementRequest, len(movies)) for index, movie := range movies { params, err := attributevalue.MarshalList([]interface{}{movie.Title, movie.Year, movie.Info}) if err != nil { panic(err) } statementRequests[index] = types.BatchStatementRequest{ Statement: aws.String(fmt.Sprintf( "INSERT INTO \"%v\" VALUE {'title': ?, 'year': ?, 'info': ?}", runner.TableName)), Parameters: params, } } _, err := runner.DynamoDbClient.BatchExecuteStatement(context.TODO(), &dynamodb.BatchExecuteStatementInput{ Statements: statementRequests, }) if err != nil { log.Printf("Couldn't insert a batch of items with PartiQL. Here's why: %v\n", err) } return err }

Use batches of SELECT statements to get items.

// GetMovieBatch runs a batch of PartiQL SELECT statements to get multiple movies from // the DynamoDB table by title and year. func (runner PartiQLRunner) GetMovieBatch(movies []Movie) ([]Movie, error) { statementRequests := make([]types.BatchStatementRequest, len(movies)) for index, movie := range movies { params, err := attributevalue.MarshalList([]interface{}{movie.Title, movie.Year}) if err != nil { panic(err) } statementRequests[index] = types.BatchStatementRequest{ Statement: aws.String( fmt.Sprintf("SELECT * FROM \"%v\" WHERE title=? AND year=?", runner.TableName)), Parameters: params, } } output, err := runner.DynamoDbClient.BatchExecuteStatement(context.TODO(), &dynamodb.BatchExecuteStatementInput{ Statements: statementRequests, }) var outMovies []Movie if err != nil { log.Printf("Couldn't get a batch of items with PartiQL. Here's why: %v\n", err) } else { for _, response := range output.Responses { var movie Movie err = attributevalue.UnmarshalMap(response.Item, &movie) if err != nil { log.Printf("Couldn't unmarshal response. Here's why: %v\n", err) } else { outMovies = append(outMovies, movie) } } } return outMovies, err }

Use batches of UPDATE statements to update items.

// UpdateMovieBatch runs a batch of PartiQL UPDATE statements to update the rating of // multiple movies that already exist in the DynamoDB table. func (runner PartiQLRunner) UpdateMovieBatch(movies []Movie, ratings []float64) error { statementRequests := make([]types.BatchStatementRequest, len(movies)) for index, movie := range movies { params, err := attributevalue.MarshalList([]interface{}{ratings[index], movie.Title, movie.Year}) if err != nil { panic(err) } statementRequests[index] = types.BatchStatementRequest{ Statement: aws.String( fmt.Sprintf("UPDATE \"%v\" SET info.rating=? WHERE title=? AND year=?", runner.TableName)), Parameters: params, } } _, err := runner.DynamoDbClient.BatchExecuteStatement(context.TODO(), &dynamodb.BatchExecuteStatementInput{ Statements: statementRequests, }) if err != nil { log.Printf("Couldn't update the batch of movies. Here's why: %v\n", err) } return err }

Use batches of DELETE statements to delete items.

// DeleteMovieBatch runs a batch of PartiQL DELETE statements to remove multiple movies // from the DynamoDB table. func (runner PartiQLRunner) DeleteMovieBatch(movies []Movie) error { statementRequests := make([]types.BatchStatementRequest, len(movies)) for index, movie := range movies { params, err := attributevalue.MarshalList([]interface{}{movie.Title, movie.Year}) if err != nil { panic(err) } statementRequests[index] = types.BatchStatementRequest{ Statement: aws.String( fmt.Sprintf("DELETE FROM \"%v\" WHERE title=? AND year=?", runner.TableName)), Parameters: params, } } _, err := runner.DynamoDbClient.BatchExecuteStatement(context.TODO(), &dynamodb.BatchExecuteStatementInput{ Statements: statementRequests, }) if err != nil { log.Printf("Couldn't delete the batch of movies. Here's why: %v\n", err) } return err }
JavaScript
SDK for JavaScript V3
Tip

To learn how to set up and run this example, see GitHub.

Create the client.

// Create the DynamoDB service client module using ES6 syntax. import { DynamoDBClient } from "@aws-sdk/client-dynamodb"; // Set the AWS Region. export const REGION = "eu-west-1"; // For example, "us-east-1". // Create an Amazon DynamoDB service client object. export const ddbClient = new DynamoDBClient({ region: REGION });

Create the DynamoDB document client.

// Create a service client module using ES6 syntax. import { DynamoDBDocumentClient } from "@aws-sdk/lib-dynamodb"; import { ddbClient } from "./ddbClient.js"; // Set the AWS Region. const REGION = "eu-west-1"; // For example, "us-east-1". const marshallOptions = { // Whether to automatically convert empty strings, blobs, and sets to `null`. convertEmptyValues: false, // false, by default. // Whether to remove undefined values while marshalling. removeUndefinedValues: false, // false, by default. // Whether to convert typeof object to map attribute. convertClassInstanceToMap: false, // false, by default. }; const unmarshallOptions = { // Whether to return numbers as a string instead of converting them to native JavaScript numbers. wrapNumbers: false, // false, by default. }; const translateConfig = { marshallOptions, unmarshallOptions }; // Create the DynamoDB document client. const ddbDocClient = DynamoDBDocumentClient.from(ddbClient, translateConfig); export { ddbDocClient };

Create a batch of items using PartiQL.

// Import required AWS SDK clients and commands for Node.js. import { BatchExecuteStatementCommand } from "@aws-sdk/client-dynamodb"; import { ddbDocClient } from "../libs/ddbDocClient.js"; const tableName = process.argv[2]; const movieYear1 = process.argv[3]; const movieTitle1 = process.argv[4]; const movieYear2 = process.argv[5]; const movieTitle2 = process.argv[6]; export const run = async ( tableName, movieYear1, movieTitle1, movieYear2, movieTitle2 ) => { const params = { Statements: [ { Statement: "INSERT INTO " + tableName + " value {'title':?, 'year':?}", Parameters: [{ S: movieTitle1 }, { N: movieYear1 }], }, { Statement: "INSERT INTO " + tableName + " value {'title':?, 'year':?}", Parameters: [{ S: movieTitle2 }, { N: movieYear2 }], }, ], }; try { const data = await ddbDocClient.send( new BatchExecuteStatementCommand(params) ); console.log("Success. Items added."); return "Run successfully"; // For unit tests. } catch (err) { console.error(err); } }; run(tableName, movieYear1, movieTitle1, movieYear2, movieTitle2);

Get a batch of items using PartiQL.

// Import required AWS SDK clients and commands for Node.js. import { BatchExecuteStatementCommand } from "@aws-sdk/client-dynamodb"; import { ddbDocClient } from "../libs/ddbDocClient.js"; const tableName = process.argv[2]; const movieYear1 = process.argv[3]; const movieTitle1 = process.argv[4]; const movieYear2 = process.argv[5]; const movieTitle2 = process.argv[6]; export const run = async ( tableName, movieYear1, movieTitle1, movieYear2, movieTitle2 ) => { const params = { Statements: [ { Statement: "SELECT * FROM " + tableName + " where title=? and year=?", Parameters: [{ S: movieTitle1 }, { N: movieYear1 }], }, { Statement: "SELECT * FROM " + tableName + " where title=? and year=?", Parameters: [{ S: movieTitle2 }, { N: movieYear2 }], }, ], }; try { const data = await ddbDocClient.send( new BatchExecuteStatementCommand(params) ); for (let i = 0; i < data.Responses.length; i++) { console.log(data.Responses[i].Item.year); console.log(data.Responses[i].Item.title); } return "Run successfully"; // For unit tests. } catch (err) { console.error(err); } }; run(tableName, movieYear1, movieTitle1, movieYear2, movieTitle2);

Update a batch of items using PartiQL.

// Import required AWS SDK clients and commands for Node.js. import { BatchExecuteStatementCommand } from "@aws-sdk/client-dynamodb"; import { ddbDocClient } from "../libs/ddbDocClient.js"; const tableName = process.argv[2]; const movieYear1 = process.argv[3]; const movieTitle1 = process.argv[4]; const movieYear2 = process.argv[6]; const movieTitle2 = process.argv[7]; const producer1 = process.argv[5]; const producer2 = process.argv[8]; export const run = async ( tableName, movieYear1, movieTitle1, movieYear2, movieTitle2, producer1, producer2 ) => { const params = { Statements: [ { Statement: "UPDATE " + tableName + " SET producer=? where title=? and year=?", Parameters: [{ S: producer1 }, { S: movieTitle1 }, { N: movieYear1 }], }, { Statement: "UPDATE " + tableName + " SET producer=? where title=? and year=?", Parameters: [{ S: producer2 }, { S: movieTitle2 }, { N: movieYear2 }], }, ], }; try { const data = await ddbDocClient.send( new BatchExecuteStatementCommand(params) ); console.log("Success. Items updated."); return "Run successfully"; // For unit tests. } catch (err) { console.error(err); } }; run( tableName, movieYear1, movieTitle1, movieYear2, movieTitle2, producer1, producer2 );

Delete a batch of items using PartiQL.

// Import required AWS SDK clients and commands for Node.js. import { BatchExecuteStatementCommand } from "@aws-sdk/client-dynamodb"; import { ddbDocClient } from "../libs/ddbDocClient.js"; const tableName = process.argv[2]; const movieYear1 = process.argv[3]; const movieTitle1 = process.argv[4]; const movieYear2 = process.argv[5]; const movieTitle2 = process.argv[6]; export const run = async ( tableName, movieYear1, movieTitle1, movieYear2, movieTitle2 ) => { try { const params = { Statements: [ { Statement: "DELETE FROM " + tableName + " where year=? and title=?", Parameters: [{ N: movieYear1 }, { S: movieTitle1 }], }, { Statement: "DELETE FROM " + tableName + " where year=? and title=?", Parameters: [{ N: movieYear2 }, { S: movieTitle2 }], }, ], }; const data = await ddbDocClient.send( new BatchExecuteStatementCommand(params) ); console.log("Success. Items deleted.", data); return "Run successfully"; // For unit tests. } catch (err) { console.error(err); } }; run(tableName, movieYear1, movieTitle1, movieYear2, movieTitle2);
PHP
SDK for PHP
Tip

To learn how to set up and run this example, see GitHub.

public function getItemByPartiQLBatch(string $tableName, array $keys): Result { $statements = []; foreach ($keys as $key) { list($statement, $parameters) = $this->buildStatementAndParameters("SELECT", $tableName, $key['Item']); $statements[] = [ 'Statement' => "$statement", 'Parameters' => $parameters, ]; } return $this->dynamoDbClient->batchExecuteStatement([ 'Statements' => $statements, ]); } public function insertItemByPartiQLBatch(string $statement, array $parameters) { $this->dynamoDbClient->batchExecuteStatement([ 'Statements' => [ [ 'Statement' => "$statement", 'Parameters' => $parameters, ], ], ]); } public function updateItemByPartiQLBatch(string $statement, array $parameters) { $this->dynamoDbClient->batchExecuteStatement([ 'Statements' => [ [ 'Statement' => "$statement", 'Parameters' => $parameters, ], ], ]); } public function deleteItemByPartiQLBatch(string $statement, array $parameters) { $this->dynamoDbClient->batchExecuteStatement([ 'Statements' => [ [ 'Statement' => "$statement", 'Parameters' => $parameters, ], ], ]); }
Python
SDK for Python (Boto3)
Tip

To learn how to set up and run this example, see GitHub.

class PartiQLBatchWrapper: """ Encapsulates a DynamoDB resource to run PartiQL statements. """ def __init__(self, dyn_resource): """ :param dyn_resource: A Boto3 DynamoDB resource. """ self.dyn_resource = dyn_resource def run_partiql(self, statements, param_list): """ Runs a PartiQL statement. A Boto3 resource is used even though `execute_statement` is called on the underlying `client` object because the resource transforms input and output from plain old Python objects (POPOs) to the DynamoDB format. If you create the client directly, you must do these transforms yourself. :param statements: The batch of PartiQL statements. :param param_list: The batch of PartiQL parameters that are associated with each statement. This list must be in the same order as the statements. :return: The responses returned from running the statements, if any. """ try: output = self.dyn_resource.meta.client.batch_execute_statement( Statements=[{ 'Statement': statement, 'Parameters': params } for statement, params in zip(statements, param_list)]) except ClientError as err: if err.response['Error']['Code'] == 'ResourceNotFoundException': logger.error( "Couldn't execute batch of PartiQL statements because the table " "does not exist.") else: logger.error( "Couldn't execute batch of PartiQL statements. Here's why: %s: %s", err.response['Error']['Code'], err.response['Error']['Message']) raise else: return output

For a complete list of Amazon SDK developer guides and code examples, see Using DynamoDB with an Amazon SDK. This topic also includes information about getting started and details about previous SDK versions.