Query a DynamoDB table by using batches of PartiQL statements and 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.

Query a DynamoDB table by using batches of PartiQL statements and an Amazon SDK

The following code examples show how to:

  • Get a batch of items by running multiple SELECT statements.

  • Add a batch of items by running multiple INSERT statements.

  • Update a batch of items by running multiple UPDATE statements.

  • Delete a batch of items by running multiple DELETE statements.

.NET
Amazon SDK for .NET
Tip

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

// Before you run this example, download 'movies.json' from // https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/GettingStarted.Js.02.html, // and put it in the same folder as the example. // Separator for the console display. var SepBar = new string('-', 80); const string tableName = "movie_table"; const string movieFileName = "moviedata.json"; DisplayInstructions(); // Create the table and wait for it to be active. Console.WriteLine($"Creating the movie table: {tableName}"); var success = await DynamoDBMethods.CreateMovieTableAsync(tableName); if (success) { Console.WriteLine($"Successfully created table: {tableName}."); } WaitForEnter(); // Add movie information to the table from moviedata.json. See the // instructions at the top of this file to download the JSON file. Console.WriteLine($"Inserting movies into the new table. Please wait..."); success = await PartiQLBatchMethods.InsertMovies(tableName, movieFileName); if (success) { Console.WriteLine("Movies successfully added to the table."); } else { Console.WriteLine("Movies could not be added to the table."); } WaitForEnter(); // Update multiple movies by using the BatchExecute statement. var title1 = "Star Wars"; var year1 = 1977; var title2 = "Wizard of Oz"; var year2 = 1939; Console.WriteLine($"Updating two movies with producer information: {title1} and {title2}."); success = await PartiQLBatchMethods.GetBatch(tableName, title1, title2, year1, year2); if (success) { Console.WriteLine($"Successfully retrieved {title1} and {title2}."); } else { Console.WriteLine("Select statement failed."); } WaitForEnter(); // Update multiple movies by using the BatchExecute statement. var producer1 = "LucasFilm"; var producer2 = "MGM"; Console.WriteLine($"Updating two movies with producer information: {title1} and {title2}."); success = await PartiQLBatchMethods.UpdateBatch(tableName, producer1, title1, year1, producer2, title2, year2); if (success) { Console.WriteLine($"Successfully updated {title1} and {title2}."); } else { Console.WriteLine("Update failed."); } WaitForEnter(); // Delete multiple movies by using the BatchExecute statement. Console.WriteLine($"Now we will delete {title1} and {title2} from the table."); success = await PartiQLBatchMethods.DeleteBatch(tableName, title1, year1, title2, year2); if (success) { Console.WriteLine($"Deleted {title1} and {title2}"); } else { Console.WriteLine($"could not delete {title1} or {title2}"); } WaitForEnter(); // DNow that the PartiQL Batch scenario is complete, delete the movie table. success = await DynamoDBMethods.DeleteTableAsync(tableName); if (success) { Console.WriteLine($"Successfully deleted {tableName}"); } else { Console.WriteLine($"Could not delete {tableName}"); } /// <summary> /// Displays the description of the application on the console. /// </summary> void DisplayInstructions() { Console.Clear(); Console.WriteLine(); Console.Write(new string(' ', 24)); Console.WriteLine("DynamoDB PartiQL Basics Example"); Console.WriteLine(SepBar); Console.WriteLine("This demo application shows the basics of using Amazon DynamoDB with the AWS SDK for"); Console.WriteLine(".NET version 3.7 and .NET 6."); Console.WriteLine(SepBar); Console.WriteLine("Creates a table by using the CreateTable method."); Console.WriteLine("Gets multiple movies by using a PartiQL SELECT statement."); Console.WriteLine("Updates multiple movies by using the ExecuteBatch method."); Console.WriteLine("Deletes multiple movies by using a PartiQL DELETE statement."); Console.WriteLine("Cleans up the resources created for the demo by deleting the table."); Console.WriteLine(SepBar); WaitForEnter(); } /// <summary> /// Simple method to wait for the <Enter> key to be pressed. /// </summary> void WaitForEnter() { Console.WriteLine("\nPress <Enter> to continue."); Console.Write(SepBar); _ = Console.ReadLine(); } /// <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; } } /// <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; } } /// <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; } /// <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.

Create a struct that is a receiver for methods that can run PartiQL statements.

// PartiQLRunner encapsulates the Amazon DynamoDB service actions used in the // PartiQL examples. It contains a DynamoDB service client that is used to act on the // specified table. type PartiQLRunner struct { DynamoDbClient *dynamodb.Client TableName string } // 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 } // 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 } // GetAllMovies runs a PartiQL SELECT statement to get all movies from the DynamoDB table. // The results are projected to return only the title and rating of each movie. func (runner PartiQLRunner) GetAllMovies() ([]map[string]interface{}, error) { var output []map[string]interface{} response, err := runner.DynamoDbClient.ExecuteStatement(context.TODO(), &dynamodb.ExecuteStatementInput{ Statement: aws.String( fmt.Sprintf("SELECT title, info.rating FROM \"%v\"", runner.TableName)), }) if err != nil { log.Printf("Couldn't get movies. Here's why: %v\n", err) } else { err = attributevalue.UnmarshalListOfMaps(response.Items, &output) if err != nil { log.Printf("Couldn't unmarshal response. Here's why: %v\n", err) } } return output, err } // 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 } // 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 }

Run a scenario that creates a table and runs batches of PartiQL queries.

// RunPartiQLBatchScenario shows you how to use the AWS SDK for Go // to run batches of PartiQL statements to query a table that stores data about movies. // // * Use batches of PartiQL statements to add, get, update, and delete data for // individual movies. // // This example creates an Amazon DynamoDB service client from the specified sdkConfig so that // you can replace it with a mocked or stubbed config for unit testing. // // This example creates and deletes a DynamoDB table to use during the scenario. func RunPartiQLBatchScenario(sdkConfig aws.Config, tableName string) { defer func() { if r := recover(); r != nil { fmt.Printf("Something went wrong with the demo.") } }() log.Println(strings.Repeat("-", 88)) log.Println("Welcome to the Amazon DynamoDB PartiQL batch demo.") log.Println(strings.Repeat("-", 88)) tableBasics := actions.TableBasics{ DynamoDbClient: dynamodb.NewFromConfig(sdkConfig), TableName: tableName, } runner := actions.PartiQLRunner{ DynamoDbClient: dynamodb.NewFromConfig(sdkConfig), TableName: tableName, } exists, err := tableBasics.TableExists() if err != nil { panic(err) } if !exists { log.Printf("Creating table %v...\n", tableName) _, err = tableBasics.CreateMovieTable() if err != nil { panic(err) } else { log.Printf("Created table %v.\n", tableName) } } else { log.Printf("Table %v already exists.\n", tableName) } log.Println(strings.Repeat("-", 88)) currentYear, _, _ := time.Now().Date() customMovies := []actions.Movie{{ Title: "House PartiQL", Year: currentYear - 5, Info: map[string]interface{}{ "plot": "Wacky high jinks result from querying a mysterious database.", "rating": 8.5}}, { Title: "House PartiQL 2", Year: currentYear - 3, Info: map[string]interface{}{ "plot": "Moderate high jinks result from querying another mysterious database.", "rating": 6.5}}, { Title: "House PartiQL 3", Year: currentYear - 1, Info: map[string]interface{}{ "plot": "Tepid high jinks result from querying yet another mysterious database.", "rating": 2.5}, }, } log.Printf("Inserting a batch of movies into table '%v'.\n", tableName) err = runner.AddMovieBatch(customMovies) if err == nil { log.Printf("Added %v movies to the table.\n", len(customMovies)) } log.Println(strings.Repeat("-", 88)) log.Println("Getting data for a batch of movies.") movies, err := runner.GetMovieBatch(customMovies) if err == nil { for _, movie := range movies { log.Println(movie) } } log.Println(strings.Repeat("-", 88)) newRatings := []float64{7.7, 4.4, 1.1} log.Println("Updating a batch of movies with new ratings.") err = runner.UpdateMovieBatch(customMovies, newRatings) if err == nil { log.Printf("Updated %v movies with new ratings.\n", len(customMovies)) } log.Println(strings.Repeat("-", 88)) log.Println("Getting projected data from the table to verify our update.") projections, err := runner.GetAllMovies() if err == nil { for _, projection := range projections { log.Println(projection) } } log.Println(strings.Repeat("-", 88)) log.Println("Deleting a batch of movies.") err = runner.DeleteMovieBatch(customMovies) if err == nil { log.Printf("Deleted %v movies.\n", len(customMovies)) } err = tableBasics.DeleteTable() if err == nil { log.Printf("Deleted table %v.\n", tableBasics.TableName) } log.Println(strings.Repeat("-", 88)) log.Println("Thanks for watching!") log.Println(strings.Repeat("-", 88)) }
Java
SDK for Java 2.x
Tip

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

public class ScenarioPartiQLBatch { public static void main(String [] args) throws IOException { String tableName = "MoviesPartiQBatch"; ProfileCredentialsProvider credentialsProvider = ProfileCredentialsProvider.create(); Region region = Region.US_EAST_1; DynamoDbClient ddb = DynamoDbClient.builder() .credentialsProvider(credentialsProvider) .region(region) .build(); System.out.println("******* Creating an Amazon DynamoDB table named "+tableName +" with a key named year and a sort key named title."); createTable(ddb, tableName); System.out.println("******* Adding multiple records into the "+ tableName +" table using a batch command."); putRecordBatch(ddb); System.out.println("******* Updating multiple records using a batch command."); updateTableItemBatch(ddb); System.out.println("******* Deleting multiple records using a batch command."); deleteItemBatch(ddb); System.out.println("******* Deleting the Amazon DynamoDB table."); deleteDynamoDBTable(ddb, tableName); ddb.close(); } public static void createTable(DynamoDbClient ddb, String tableName) { DynamoDbWaiter dbWaiter = ddb.waiter(); ArrayList<AttributeDefinition> attributeDefinitions = new ArrayList<>(); // Define attributes. attributeDefinitions.add(AttributeDefinition.builder() .attributeName("year") .attributeType("N") .build()); attributeDefinitions.add(AttributeDefinition.builder() .attributeName("title") .attributeType("S") .build()); ArrayList<KeySchemaElement> tableKey = new ArrayList<>(); KeySchemaElement key = KeySchemaElement.builder() .attributeName("year") .keyType(KeyType.HASH) .build(); KeySchemaElement key2 = KeySchemaElement.builder() .attributeName("title") .keyType(KeyType.RANGE) // Sort .build(); // Add KeySchemaElement objects to the list. tableKey.add(key); tableKey.add(key2); CreateTableRequest request = CreateTableRequest.builder() .keySchema(tableKey) .provisionedThroughput(ProvisionedThroughput.builder() .readCapacityUnits(new Long(10)) .writeCapacityUnits(new Long(10)) .build()) .attributeDefinitions(attributeDefinitions) .tableName(tableName) .build(); try { CreateTableResponse response = ddb.createTable(request); DescribeTableRequest tableRequest = DescribeTableRequest.builder() .tableName(tableName) .build(); // Wait until the Amazon DynamoDB table is created. WaiterResponse<DescribeTableResponse> waiterResponse = dbWaiter.waitUntilTableExists(tableRequest); waiterResponse.matched().response().ifPresent(System.out::println); String newTable = response.tableDescription().tableName(); System.out.println("The " +newTable + " was successfully created."); } catch (DynamoDbException e) { System.err.println(e.getMessage()); System.exit(1); } } public static void putRecordBatch(DynamoDbClient ddb) { String sqlStatement = "INSERT INTO MoviesPartiQBatch VALUE {'year':?, 'title' : ?, 'info' : ?}"; try { // Create three movies to add to the Amazon DynamoDB table. // Set data for Movie 1. List<AttributeValue> parameters = new ArrayList<>(); AttributeValue att1 = AttributeValue.builder() .n(String.valueOf("2022")) .build(); AttributeValue att2 = AttributeValue.builder() .s("My Movie 1") .build(); AttributeValue att3 = AttributeValue.builder() .s("No Information") .build(); parameters.add(att1); parameters.add(att2); parameters.add(att3); BatchStatementRequest statementRequestMovie1 = BatchStatementRequest.builder() .statement(sqlStatement) .parameters(parameters) .build(); // Set data for Movie 2. List<AttributeValue> parametersMovie2 = new ArrayList<>(); AttributeValue attMovie2 = AttributeValue.builder() .n(String.valueOf("2022")) .build(); AttributeValue attMovie2A = AttributeValue.builder() .s("My Movie 2") .build(); AttributeValue attMovie2B = AttributeValue.builder() .s("No Information") .build(); parametersMovie2.add(attMovie2); parametersMovie2.add(attMovie2A); parametersMovie2.add(attMovie2B); BatchStatementRequest statementRequestMovie2 = BatchStatementRequest.builder() .statement(sqlStatement) .parameters(parametersMovie2) .build(); // Set data for Movie 3. List<AttributeValue> parametersMovie3 = new ArrayList<>(); AttributeValue attMovie3 = AttributeValue.builder() .n(String.valueOf("2022")) .build(); AttributeValue attMovie3A = AttributeValue.builder() .s("My Movie 3") .build(); AttributeValue attMovie3B = AttributeValue.builder() .s("No Information") .build(); parametersMovie3.add(attMovie3); parametersMovie3.add(attMovie3A); parametersMovie3.add(attMovie3B); BatchStatementRequest statementRequestMovie3 = BatchStatementRequest.builder() .statement(sqlStatement) .parameters(parametersMovie3) .build(); // Add all three movies to the list. List<BatchStatementRequest> myBatchStatementList = new ArrayList<>(); myBatchStatementList.add(statementRequestMovie1); myBatchStatementList.add(statementRequestMovie2); myBatchStatementList.add(statementRequestMovie3); BatchExecuteStatementRequest batchRequest = BatchExecuteStatementRequest.builder() .statements(myBatchStatementList) .build(); BatchExecuteStatementResponse response = ddb.batchExecuteStatement(batchRequest); System.out.println("ExecuteStatement successful: "+ response.toString()); System.out.println("Added new movies using a batch command."); } catch (DynamoDbException e) { System.err.println(e.getMessage()); System.exit(1); } } public static void updateTableItemBatch(DynamoDbClient ddb){ String sqlStatement = "UPDATE MoviesPartiQBatch SET info = 'directors\":[\"Merian C. Cooper\",\"Ernest B. Schoedsack' where year=? and title=?"; List<AttributeValue> parametersRec1 = new ArrayList<>(); // Update three records. AttributeValue att1 = AttributeValue.builder() .n(String.valueOf("2022")) .build(); AttributeValue att2 = AttributeValue.builder() .s("My Movie 1") .build(); parametersRec1.add(att1); parametersRec1.add(att2); BatchStatementRequest statementRequestRec1 = BatchStatementRequest.builder() .statement(sqlStatement) .parameters(parametersRec1) .build(); // Update record 2. List<AttributeValue> parametersRec2 = new ArrayList<>(); AttributeValue attRec2 = AttributeValue.builder() .n(String.valueOf("2022")) .build(); AttributeValue attRec2a = AttributeValue.builder() .s("My Movie 2") .build(); parametersRec2.add(attRec2); parametersRec2.add(attRec2a); BatchStatementRequest statementRequestRec2 = BatchStatementRequest.builder() .statement(sqlStatement) .parameters(parametersRec2) .build(); // Update record 3. List<AttributeValue> parametersRec3 = new ArrayList<>(); AttributeValue attRec3 = AttributeValue.builder() .n(String.valueOf("2022")) .build(); AttributeValue attRec3a = AttributeValue.builder() .s("My Movie 3") .build(); parametersRec3.add(attRec3); parametersRec3.add(attRec3a); BatchStatementRequest statementRequestRec3 = BatchStatementRequest.builder() .statement(sqlStatement) .parameters(parametersRec3) .build(); // Add all three movies to the list. List<BatchStatementRequest> myBatchStatementList = new ArrayList<>(); myBatchStatementList.add(statementRequestRec1); myBatchStatementList.add(statementRequestRec2); myBatchStatementList.add(statementRequestRec3); BatchExecuteStatementRequest batchRequest = BatchExecuteStatementRequest.builder() .statements(myBatchStatementList) .build(); try { BatchExecuteStatementResponse response = ddb.batchExecuteStatement(batchRequest); System.out.println("ExecuteStatement successful: "+ response.toString()); System.out.println("Updated three movies using a batch command."); } catch (DynamoDbException e) { System.err.println(e.getMessage()); System.exit(1); } System.out.println("Item was updated!"); } public static void deleteItemBatch(DynamoDbClient ddb){ String sqlStatement = "DELETE FROM MoviesPartiQBatch WHERE year = ? and title=?"; List<AttributeValue> parametersRec1 = new ArrayList<>(); // Specify three records to delete. AttributeValue att1 = AttributeValue.builder() .n(String.valueOf("2022")) .build(); AttributeValue att2 = AttributeValue.builder() .s("My Movie 1") .build(); parametersRec1.add(att1); parametersRec1.add(att2); BatchStatementRequest statementRequestRec1 = BatchStatementRequest.builder() .statement(sqlStatement) .parameters(parametersRec1) .build(); // Specify record 2. List<AttributeValue> parametersRec2 = new ArrayList<>(); AttributeValue attRec2 = AttributeValue.builder() .n(String.valueOf("2022")) .build(); AttributeValue attRec2a = AttributeValue.builder() .s("My Movie 2") .build(); parametersRec2.add(attRec2); parametersRec2.add(attRec2a); BatchStatementRequest statementRequestRec2 = BatchStatementRequest.builder() .statement(sqlStatement) .parameters(parametersRec2) .build(); // Specify record 3. List<AttributeValue> parametersRec3 = new ArrayList<>(); AttributeValue attRec3 = AttributeValue.builder() .n(String.valueOf("2022")) .build(); AttributeValue attRec3a = AttributeValue.builder() .s("My Movie 3") .build(); parametersRec3.add(attRec3); parametersRec3.add(attRec3a); BatchStatementRequest statementRequestRec3 = BatchStatementRequest.builder() .statement(sqlStatement) .parameters(parametersRec3) .build(); // Add all three movies to the list. List<BatchStatementRequest> myBatchStatementList = new ArrayList<>(); myBatchStatementList.add(statementRequestRec1); myBatchStatementList.add(statementRequestRec2); myBatchStatementList.add(statementRequestRec3); BatchExecuteStatementRequest batchRequest = BatchExecuteStatementRequest.builder() .statements(myBatchStatementList) .build(); try { ddb.batchExecuteStatement(batchRequest); System.out.println("Deleted three movies using a batch command."); } catch (DynamoDbException e) { System.err.println(e.getMessage()); System.exit(1); } } public static void deleteDynamoDBTable(DynamoDbClient ddb, String tableName) { DeleteTableRequest request = DeleteTableRequest.builder() .tableName(tableName) .build(); try { ddb.deleteTable(request); } catch (DynamoDbException e) { System.err.println(e.getMessage()); System.exit(1); } System.out.println(tableName +" was successfully deleted!"); } private static ExecuteStatementResponse executeStatementRequest(DynamoDbClient ddb, String statement, List<AttributeValue> parameters ) { ExecuteStatementRequest request = ExecuteStatementRequest.builder() .statement(statement) .parameters(parameters) .build(); return ddb.executeStatement(request); } }
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 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 };

Query items by batch.

*/ import fs from "fs"; // A practical functional library used to split the data into segments. import * as R from "ramda"; import { ddbClient } from "../libs/ddbClient.js"; import { ddbDocClient } from "../libs/ddbDocClient.js"; import { BatchWriteCommand } from "@aws-sdk/lib-dynamodb"; import { CreateTableCommand, BatchExecuteStatementCommand, } from "@aws-sdk/client-dynamodb"; if (process.argv.length < 6) { console.log( "Usage: node partiQL_basics.js <tableName> <movieTitle1> <movieYear1> <movieTitle1> <movieYear1> <producer1> <producer2> \n" + "Example: node partiQL_basics.js Movies_batch 2006 'The Departed' 2013 '2 Guns' 'New View Films' 'Old Thyme Films'" ); } const tableName = process.argv[2]; const movieYear1 = parseInt(process.argv[3]); const movieTitle1 = process.argv[4]; const movieYear2 = parseInt(process.argv[5]); const movieTitle2 = process.argv[6]; const producer1 = process.argv[7]; const producer2 = process.argv[8]; // Helper function to delay running the code while the AWS service calls wait for responses. function wait(ms) { var start = Date.now(); var end = start; while (end < start + ms) { end = Date.now(); } } // Set the parameters. export const run = async ( tableName, movieYear1, movieTitle1, movieYear2, movieTitle2, producer1, producer2 ) => { try { console.log("Creating table ..."); // Set the parameters. const params = { AttributeDefinitions: [ { AttributeName: "title", AttributeType: "S", }, { AttributeName: "year", AttributeType: "N", }, ], KeySchema: [ { AttributeName: "title", KeyType: "HASH", }, { AttributeName: "year", KeyType: "RANGE", }, ], ProvisionedThroughput: { ReadCapacityUnits: 5, WriteCapacityUnits: 5, }, TableName: tableName, }; const data = await ddbClient.send(new CreateTableCommand(params)); console.log("Waiting for table to be created..."); wait(10000); console.log( "Table created. Table name is ", data.TableDescription.TableName ); try { // Before you run this example, download 'movies.json' from https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/GettingStarted.Js.02.html, // and put it in the same folder as the example. // Get the movie data parse to convert into a JSON object. const allMovies = JSON.parse(fs.readFileSync("moviedata.json", "utf8")); // Split the table into segments of 25. const dataSegments = R.splitEvery(25, allMovies); // Loop batch write operation 10 times to upload 250 items. console.log("Writing movies in batch to table..."); for (let i = 0; i < 10; i++) { const segment = dataSegments[i]; for (let j = 0; j < 25; j++) { const params = { RequestItems: { [tableName]: [ { // Destination Amazon DynamoDB table name. PutRequest: { Item: { year: segment[j].year, title: segment[j].title, info: segment[j].info, }, }, }, ], }, }; const data = ddbDocClient.send(new BatchWriteCommand(params)); } } wait(10000); console.log("Success, movies written to table."); try { console.log("Getting movie...."); 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 }], }, ], }; const data = await ddbDocClient.send( new BatchExecuteStatementCommand(params) ); console.log("Success. The query return the following data.", data); for (let i = 0; i < data.Responses.length; i++) { console.log(data.Responses[i].Item.year); console.log(data.Responses[i].Item.title); } try { const params = { Statements: [ { Statement: "DELETE FROM " + tableName + " where title=? and year=?", Parameters: [{ S: movieTitle1 }, { N: movieYear1 }], }, { Statement: "DELETE FROM " + tableName + " where title=? and year=?", Parameters: [{ S: movieTitle2 }, { N: movieYear2 }], }, ], }; const data = await ddbDocClient.send( new BatchExecuteStatementCommand(params) ); console.log("Success. Items deleted by batch."); try { 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 }], }, ], }; const data = await ddbDocClient.send( new BatchExecuteStatementCommand(params) ); console.log("Success. Items added by batch."); try { 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 }, ], }, ], }; console.log("Updating movies..."); const data = await ddbDocClient.send( new BatchExecuteStatementCommand(params) ); console.log("Success. Items updated by batch."); return "Run successfully"; // For unit tests. } catch (err) { console.log("Error updating items by batch. ", err); } } catch (err) { console.log("Error adding items to table by batch. ", err); } } catch (err) { console.log("Error deleting movies by batch. ", err); } } catch (err) { console.log("Error getting movies by batch. ", err); } } catch (err) { console.log("Error adding movies by batch. ", err); } } catch (err) { console.log("Error creating table. ", err); } }; run( tableName, movieYear1, movieTitle1, movieYear2, movieTitle2, producer1, producer2 );
Kotlin
SDK for Kotlin
Note

This is prerelease documentation for a feature in preview release. It is subject to change.

Tip

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

suspend fun main() { val ddb = DynamoDbClient { region = "us-east-1" } val tableName = "MoviesPartiQBatch" println("Creating an Amazon DynamoDB table named $tableName with a key named id and a sort key named title.") createTablePartiQLBatch(ddb, tableName, "year") putRecordBatch(ddb) updateTableItemBatchBatch(ddb) deleteItemsBatch(ddb) deleteTablePartiQLBatch(tableName) } suspend fun createTablePartiQLBatch(ddb: DynamoDbClient, tableNameVal: String, key: String) { val attDef = AttributeDefinition { attributeName = key attributeType = ScalarAttributeType.N } val attDef1 = AttributeDefinition { attributeName = "title" attributeType = ScalarAttributeType.S } val keySchemaVal = KeySchemaElement { attributeName = key keyType = KeyType.Hash } val keySchemaVal1 = KeySchemaElement { attributeName = "title" keyType = KeyType.Range } val provisionedVal = ProvisionedThroughput { readCapacityUnits = 10 writeCapacityUnits = 10 } val request = CreateTableRequest { attributeDefinitions = listOf(attDef, attDef1) keySchema = listOf(keySchemaVal, keySchemaVal1) provisionedThroughput = provisionedVal tableName = tableNameVal } val response = ddb.createTable(request) ddb.waitUntilTableExists { // suspend call tableName = tableNameVal } println("The table was successfully created ${response.tableDescription?.tableArn}") } suspend fun putRecordBatch(ddb: DynamoDbClient) { val sqlStatement = "INSERT INTO MoviesPartiQBatch VALUE {'year':?, 'title' : ?, 'info' : ?}" // Create three movies to add to the Amazon DynamoDB table. val parametersMovie1 = mutableListOf<AttributeValue>() parametersMovie1.add(AttributeValue.N("2022")) parametersMovie1.add(AttributeValue.S("My Movie 1")) parametersMovie1.add(AttributeValue.S("No Information")) val statementRequestMovie1 = BatchStatementRequest { statement = sqlStatement parameters = parametersMovie1 } // Set data for Movie 2. val parametersMovie2 = mutableListOf<AttributeValue>() parametersMovie2.add(AttributeValue.N("2022")) parametersMovie2.add(AttributeValue.S("My Movie 2")) parametersMovie2.add(AttributeValue.S("No Information")) val statementRequestMovie2 = BatchStatementRequest { statement = sqlStatement parameters = parametersMovie2 } // Set data for Movie 3. val parametersMovie3 = mutableListOf<AttributeValue>() parametersMovie3.add(AttributeValue.N("2022")) parametersMovie3.add(AttributeValue.S("My Movie 3")) parametersMovie3.add(AttributeValue.S("No Information")) val statementRequestMovie3 = BatchStatementRequest { statement = sqlStatement parameters = parametersMovie3 } // Add all three movies to the list. val myBatchStatementList = mutableListOf<BatchStatementRequest>() myBatchStatementList.add(statementRequestMovie1) myBatchStatementList.add(statementRequestMovie2) myBatchStatementList.add(statementRequestMovie3) val batchRequest = BatchExecuteStatementRequest { statements = myBatchStatementList } val response = ddb.batchExecuteStatement(batchRequest) println("ExecuteStatement successful: " + response.toString()) println("Added new movies using a batch command.") } suspend fun updateTableItemBatchBatch(ddb: DynamoDbClient) { val sqlStatement = "UPDATE MoviesPartiQBatch SET info = 'directors\":[\"Merian C. Cooper\",\"Ernest B. Schoedsack' where year=? and title=?" val parametersRec1 = mutableListOf<AttributeValue>() parametersRec1.add(AttributeValue.N("2022")) parametersRec1.add(AttributeValue.S("My Movie 1")) val statementRequestRec1 = BatchStatementRequest { statement = sqlStatement parameters = parametersRec1 } // Update record 2. val parametersRec2 = mutableListOf<AttributeValue>() parametersRec2.add(AttributeValue.N("2022")) parametersRec2.add(AttributeValue.S("My Movie 2")) val statementRequestRec2 = BatchStatementRequest { statement = sqlStatement parameters = parametersRec2 } // Update record 3. val parametersRec3 = mutableListOf<AttributeValue>() parametersRec3.add(AttributeValue.N("2022")) parametersRec3.add(AttributeValue.S("My Movie 3")) val statementRequestRec3 = BatchStatementRequest { statement = sqlStatement parameters = parametersRec3 } // Add all three movies to the list. val myBatchStatementList = mutableListOf<BatchStatementRequest>() myBatchStatementList.add(statementRequestRec1) myBatchStatementList.add(statementRequestRec2) myBatchStatementList.add(statementRequestRec3) val batchRequest = BatchExecuteStatementRequest { statements = myBatchStatementList } val response = ddb.batchExecuteStatement(batchRequest) println("ExecuteStatement successful: $response") println("Updated three movies using a batch command.") println("Items were updated!") } suspend fun deleteItemsBatch(ddb: DynamoDbClient) { // Specify three records to delete. val sqlStatement = "DELETE FROM MoviesPartiQBatch WHERE year = ? and title=?" val parametersRec1 = mutableListOf<AttributeValue>() parametersRec1.add(AttributeValue.N("2022")) parametersRec1.add(AttributeValue.S("My Movie 1")) val statementRequestRec1 = BatchStatementRequest { statement = sqlStatement parameters = parametersRec1 } // Specify record 2. val parametersRec2 = mutableListOf<AttributeValue>() parametersRec2.add(AttributeValue.N("2022")) parametersRec2.add(AttributeValue.S("My Movie 2")) val statementRequestRec2 = BatchStatementRequest { statement = sqlStatement parameters = parametersRec2 } // Specify record 3. val parametersRec3 = mutableListOf<AttributeValue>() parametersRec3.add(AttributeValue.N("2022")) parametersRec3.add(AttributeValue.S("My Movie 3")) val statementRequestRec3 = BatchStatementRequest { statement = sqlStatement parameters = parametersRec3 } // Add all three movies to the list. val myBatchStatementList = mutableListOf<BatchStatementRequest>() myBatchStatementList.add(statementRequestRec1) myBatchStatementList.add(statementRequestRec2) myBatchStatementList.add(statementRequestRec3) val batchRequest = BatchExecuteStatementRequest { statements = myBatchStatementList } ddb.batchExecuteStatement(batchRequest) println("Deleted three movies using a batch command.") } suspend fun deleteTablePartiQLBatch(tableNameVal: String) { val request = DeleteTableRequest { tableName = tableNameVal } DynamoDbClient { region = "us-east-1" }.use { ddb -> ddb.deleteTable(request) println("$tableNameVal was deleted") } }
PHP
SDK for PHP
Tip

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

namespace DynamoDb\PartiQL_Basics; use Aws\DynamoDb\Marshaler; use DynamoDb; use DynamoDb\DynamoDBAttribute; class GettingStartedWithPartiQLBatch { public function run() { echo("--------------------------------------\n"); print("Welcome to the Amazon DynamoDB - PartiQL getting started demo using PHP!\n"); echo("--------------------------------------\n"); $uuid = uniqid(); $service = new DynamoDb\DynamoDBService(); $tableName = "partiql_demo_table_$uuid"; $service->createTable( $tableName, [ new DynamoDBAttribute('year', 'N', 'HASH'), new DynamoDBAttribute('title', 'S', 'RANGE') ] ); echo "Waiting for table..."; $service->dynamoDbClient->waitUntil("TableExists", ['TableName' => $tableName]); echo "table $tableName found!\n"; echo "What's the name of the last movie you watched?\n"; while (empty($movieName)) { $movieName = testable_readline("Movie name: "); } echo "And what year was it released?\n"; $movieYear = "year"; while (!is_numeric($movieYear) || intval($movieYear) != $movieYear) { $movieYear = testable_readline("Year released: "); } $key = [ 'Item' => [ 'year' => [ 'N' => "$movieYear", ], 'title' => [ 'S' => $movieName, ], ], ]; list($statement, $parameters) = $service->buildStatementAndParameters("INSERT", $tableName, $key); $service->insertItemByPartiQLBatch($statement, $parameters); echo "How would you rate the movie from 1-10?\n"; $rating = 0; while (!is_numeric($rating) || intval($rating) != $rating || $rating < 1 || $rating > 10) { $rating = testable_readline("Rating (1-10): "); } echo "What was the movie about?\n"; while (empty($plot)) { $plot = testable_readline("Plot summary: "); } $attributes = [ new DynamoDBAttribute('rating', 'N', 'HASH', $rating), new DynamoDBAttribute('plot', 'S', 'RANGE', $plot), ]; list($statement, $parameters) = $service->buildStatementAndParameters("UPDATE", $tableName, $key, $attributes); $service->updateItemByPartiQLBatch($statement, $parameters); echo "Movie added and updated.\n"; $batch = json_decode(loadMovieData()); $service->writeBatch($tableName, $batch); $movie = $service->getItemByPartiQLBatch($tableName, [$key]); echo "\nThe movie {$movie['Responses'][0]['Item']['title']['S']} was released in {$movie['Responses'][0]['Item']['year']['N']}.\n"; echo "What rating would you like to give {$movie['Responses'][0]['Item']['title']['S']}?\n"; $rating = 0; while (!is_numeric($rating) || intval($rating) != $rating || $rating < 1 || $rating > 10) { $rating = testable_readline("Rating (1-10): "); } $attributes = [ new DynamoDBAttribute('rating', 'N', 'HASH', $rating), new DynamoDBAttribute('plot', 'S', 'RANGE', $plot) ]; list($statement, $parameters) = $service->buildStatementAndParameters("UPDATE", $tableName, $key, $attributes); $service->updateItemByPartiQLBatch($statement, $parameters); $movie = $service->getItemByPartiQLBatch($tableName, [$key]); echo "Okay, you have rated {$movie['Responses'][0]['Item']['title']['S']} as a {$movie['Responses'][0]['Item']['rating']['N']}\n"; $service->deleteItemByPartiQLBatch($statement, $parameters); echo "But, bad news, this was a trap. That movie has now been deleted because of your rating...harsh.\n"; echo "That's okay though. The book was better. Now, for something lighter, in what year were you born?\n"; $birthYear = "not a number"; while (!is_numeric($birthYear) || $birthYear >= date("Y")) { $birthYear = testable_readline("Birth year: "); } $birthKey = [ 'Key' => [ 'year' => [ 'N' => "$birthYear", ], ], ]; $result = $service->query($tableName, $birthKey); $marshal = new Marshaler(); echo "Here are the movies in our collection released the year you were born:\n"; $oops = "Oops! There were no movies released in that year (that we know of).\n"; $display = ""; foreach ($result['Items'] as $movie) { $movie = $marshal->unmarshalItem($movie); $display .= $movie['title'] . "\n"; } echo ($display) ?: $oops; $yearsKey = [ 'Key' => [ 'year' => [ 'N' => [ 'minRange' => 1990, 'maxRange' => 1999, ], ], ], ]; $filter = "year between 1990 and 1999"; echo "\nHere's a list of all the movies released in the 90s:\n"; $result = $service->scan($tableName, $yearsKey, $filter); foreach ($result['Items'] as $movie) { $movie = $marshal->unmarshalItem($movie); echo $movie['title'] . "\n"; } echo "\nCleaning up this demo by deleting table $tableName...\n"; $service->deleteTable($tableName); } } public function insertItemByPartiQLBatch(string $statement, array $parameters) { $this->dynamoDbClient->batchExecuteStatement([ 'Statements' => [ [ 'Statement' => "$statement", 'Parameters' => $parameters, ], ], ]); } 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 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.

Create a class that can run batches of PartiQL statements.

from datetime import datetime from decimal import Decimal import logging from pprint import pprint import boto3 from botocore.exceptions import ClientError from scaffold import Scaffold logger = logging.getLogger(__name__) 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

Run a scenario that creates a table and runs PartiQL queries in batches.

def run_scenario(scaffold, wrapper, table_name): logging.basicConfig(level=logging.INFO, format='%(levelname)s: %(message)s') print('-'*88) print("Welcome to the Amazon DynamoDB PartiQL batch statement demo.") print('-'*88) print(f"Creating table '{table_name}' for the demo...") scaffold.create_table(table_name) print('-'*88) movie_data = [{ 'title': f"House PartiQL", 'year': datetime.now().year - 5, 'info': { 'plot': "Wacky high jinks result from querying a mysterious database.", 'rating': Decimal('8.5')}}, { 'title': f"House PartiQL 2", 'year': datetime.now().year - 3, 'info': { 'plot': "Moderate high jinks result from querying another mysterious database.", 'rating': Decimal('6.5')}}, { 'title': f"House PartiQL 3", 'year': datetime.now().year - 1, 'info': { 'plot': "Tepid high jinks result from querying yet another mysterious database.", 'rating': Decimal('2.5')}}] print(f"Inserting a batch of movies into table '{table_name}.") statements = [ f"INSERT INTO \"{table_name}\" " f"VALUE {{'title': ?, 'year': ?, 'info': ?}}"] * len(movie_data) params = [list(movie.values()) for movie in movie_data] wrapper.run_partiql(statements, params) print("Success!") print('-'*88) print(f"Getting data for a batch of movies.") statements = [ f"SELECT * FROM \"{table_name}\" WHERE title=? AND year=?"] * len(movie_data) params = [[movie['title'], movie['year']] for movie in movie_data] output = wrapper.run_partiql(statements, params) for item in output['Responses']: print(f"\n{item['Item']['title']}, {item['Item']['year']}") pprint(item['Item']) print('-'*88) ratings = [Decimal('7.7'), Decimal('5.5'), Decimal('1.3')] print(f"Updating a batch of movies with new ratings.") statements = [ f"UPDATE \"{table_name}\" SET info.rating=? " f"WHERE title=? AND year=?"] * len(movie_data) params = [ [rating, movie['title'], movie['year']] for rating, movie in zip(ratings, movie_data)] wrapper.run_partiql(statements, params) print("Success!") print('-'*88) print(f"Getting projected data from the table to verify our update.") output = wrapper.dyn_resource.meta.client.execute_statement( Statement=f'SELECT title, info.rating FROM "{table_name}"') pprint(output['Items']) print('-'*88) print(f"Deleting a batch of movies from the table.") statements = [ f"DELETE FROM \"{table_name}\" WHERE title=? AND year=?"] * len(movie_data) params = [[movie['title'], movie['year']] for movie in movie_data] wrapper.run_partiql(statements, params) print("Success!") print('-'*88) print(f"Deleting table '{table_name}'...") scaffold.delete_table() print('-'*88) print("\nThanks for watching!") print('-'*88) if __name__ == '__main__': try: dyn_res = boto3.resource('dynamodb') scaffold = Scaffold(dyn_res) movies = PartiQLBatchWrapper(dyn_res) run_scenario(scaffold, movies, 'doc-example-table-partiql-movies') except Exception as e: print(f"Something went wrong with the demo! Here's what: {e}")

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.