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

Query a DynamoDB table by using batches of PartiQL statements and an AWS 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
AWS SDK for .NET
Note

There's more on GitHub. Find the complete example and learn how to set up and run in the AWS Code Examples Repository.

// 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> /// Gets movies from the movie table by /// using an Amazon DynamoDB PartiQL SELECT statement. /// </summary> /// <param name="tableName">The name of the table.</param> /// <param name="title1">The title of the first movie.</param> /// <param name="title2">The title of the second movie.</param> /// <param name="year1">The year of the first movie.</param> /// <param name="year2">The year of the second movie.</param> /// <returns>True if successful.</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; }
C++
SDK for C++
Note

There's more on GitHub. Find the complete example and learn how to set up and run in the AWS Code Examples Repository.

Aws::Client::ClientConfiguration clientConfig; // 1. Create a table. (CreateTable) if (AwsDoc::DynamoDB::createMoviesDynamoDBTable(clientConfig)) { AwsDoc::DynamoDB::partiqlBatchExecuteScenario(clientConfig); // 7. Delete the table. (DeleteTable) AwsDoc::DynamoDB::deleteMoviesDynamoDBTable(clientConfig); } //! Scenario to modify and query a DynamoDB table using PartiQL batch statements. /*! \sa partiqlBatchExecuteScenario() \param clientConfiguration: AWS client configuration. \return bool: Function succeeded. */ bool AwsDoc::DynamoDB::partiqlBatchExecuteScenario( const Aws::Client::ClientConfiguration &clientConfiguration) { // 2. Add multiple movies using "Insert" statements. (BatchExecuteStatement) Aws::DynamoDB::DynamoDBClient dynamoClient(clientConfiguration); std::vector<Aws::String> titles; std::vector<float> ratings; std::vector<int> years; std::vector<Aws::String> plots; Aws::String doAgain = "n"; do { Aws::String aTitle = askQuestion( "Enter the title of a movie you want to add to the table: "); titles.push_back(aTitle); int aYear = askQuestionForInt("What year was it released? "); years.push_back(aYear); float aRating = askQuestionForFloatRange( "On a scale of 1 - 10, how do you rate it? ", 1, 10); ratings.push_back(aRating); Aws::String aPlot = askQuestion("Summarize the plot for me: "); plots.push_back(aPlot); doAgain = askQuestion(Aws::String("Would you like to add more movies? (y/n) ")); } while (doAgain == "y"); std::cout << "Adding " << titles.size() << (titles.size() == 1 ? " movie " : " movies ") << "to the table using a batch \"INSERT\" statement." << std::endl; { Aws::Vector<Aws::DynamoDB::Model::BatchStatementRequest> statements( titles.size()); std::stringstream sqlStream; sqlStream << "INSERT INTO \"" << MOVIE_TABLE_NAME << "\" VALUE {'" << TITLE_KEY << "': ?, '" << YEAR_KEY << "': ?, '" << INFO_KEY << "': ?}"; std::string sql(sqlStream.str()); for (size_t i = 0; i < statements.size(); ++i) { statements[i].SetStatement(sql); Aws::Vector<Aws::DynamoDB::Model::AttributeValue> attributes; attributes.push_back( Aws::DynamoDB::Model::AttributeValue().SetS(titles[i])); attributes.push_back(Aws::DynamoDB::Model::AttributeValue().SetN(years[i])); // Create attribute for the info map. Aws::DynamoDB::Model::AttributeValue infoMapAttribute; std::shared_ptr<Aws::DynamoDB::Model::AttributeValue> ratingAttribute = Aws::MakeShared<Aws::DynamoDB::Model::AttributeValue>( ALLOCATION_TAG.c_str()); ratingAttribute->SetN(ratings[i]); infoMapAttribute.AddMEntry(RATING_KEY, ratingAttribute); std::shared_ptr<Aws::DynamoDB::Model::AttributeValue> plotAttribute = Aws::MakeShared<Aws::DynamoDB::Model::AttributeValue>( ALLOCATION_TAG.c_str()); plotAttribute->SetS(plots[i]); infoMapAttribute.AddMEntry(PLOT_KEY, plotAttribute); attributes.push_back(infoMapAttribute); statements[i].SetParameters(attributes); } Aws::DynamoDB::Model::BatchExecuteStatementRequest request; request.SetStatements(statements); Aws::DynamoDB::Model::BatchExecuteStatementOutcome outcome = dynamoClient.BatchExecuteStatement( request); if (!outcome.IsSuccess()) { std::cerr << "Failed to add the movies: " << outcome.GetError().GetMessage() << std::endl; return false; } } std::cout << "Retrieving the movie data with a batch \"SELECT\" statement." << std::endl; // 3. Get the data for multiple movies using "Select" statements. (BatchExecuteStatement) { Aws::Vector<Aws::DynamoDB::Model::BatchStatementRequest> statements( titles.size()); std::stringstream sqlStream; sqlStream << "SELECT * FROM \"" << MOVIE_TABLE_NAME << "\" WHERE " << TITLE_KEY << "=? and " << YEAR_KEY << "=?"; std::string sql(sqlStream.str()); for (size_t i = 0; i < statements.size(); ++i) { statements[i].SetStatement(sql); Aws::Vector<Aws::DynamoDB::Model::AttributeValue> attributes; attributes.push_back( Aws::DynamoDB::Model::AttributeValue().SetS(titles[i])); attributes.push_back(Aws::DynamoDB::Model::AttributeValue().SetN(years[i])); statements[i].SetParameters(attributes); } Aws::DynamoDB::Model::BatchExecuteStatementRequest request; request.SetStatements(statements); Aws::DynamoDB::Model::BatchExecuteStatementOutcome outcome = dynamoClient.BatchExecuteStatement( request); if (outcome.IsSuccess()) { const Aws::DynamoDB::Model::BatchExecuteStatementResult &result = outcome.GetResult(); const Aws::Vector<Aws::DynamoDB::Model::BatchStatementResponse> &responses = result.GetResponses(); for (const Aws::DynamoDB::Model::BatchStatementResponse &response: responses) { const Aws::Map<Aws::String, Aws::DynamoDB::Model::AttributeValue> &item = response.GetItem(); printMovieInfo(item); } } else { std::cerr << "Failed to retrieve the movie information: " << outcome.GetError().GetMessage() << std::endl; return false; } } // 4. Update the data for multiple movies using "Update" statements. (BatchExecuteStatement) for (size_t i = 0; i < titles.size(); ++i) { ratings[i] = askQuestionForFloatRange( Aws::String("\nLet's update your the movie, \"") + titles[i] + ".\nYou rated it " + std::to_string(ratings[i]) + ", what new rating would you give it? ", 1, 10); } std::cout << "Updating the movie with a batch \"UPDATE\" statement." << std::endl; { Aws::Vector<Aws::DynamoDB::Model::BatchStatementRequest> statements( titles.size()); std::stringstream sqlStream; sqlStream << "UPDATE \"" << MOVIE_TABLE_NAME << "\" SET " << INFO_KEY << "." << RATING_KEY << "=? WHERE " << TITLE_KEY << "=? AND " << YEAR_KEY << "=?"; std::string sql(sqlStream.str()); for (size_t i = 0; i < statements.size(); ++i) { statements[i].SetStatement(sql); Aws::Vector<Aws::DynamoDB::Model::AttributeValue> attributes; attributes.push_back( Aws::DynamoDB::Model::AttributeValue().SetN(ratings[i])); attributes.push_back( Aws::DynamoDB::Model::AttributeValue().SetS(titles[i])); attributes.push_back(Aws::DynamoDB::Model::AttributeValue().SetN(years[i])); statements[i].SetParameters(attributes); } Aws::DynamoDB::Model::BatchExecuteStatementRequest request; request.SetStatements(statements); Aws::DynamoDB::Model::BatchExecuteStatementOutcome outcome = dynamoClient.BatchExecuteStatement( request); if (!outcome.IsSuccess()) { std::cerr << "Failed to update movie information: " << outcome.GetError().GetMessage() << std::endl; return false; } } std::cout << "Retrieving the updated movie data with a batch \"SELECT\" statement." << std::endl; // 5. Get the updated data for multiple movies using "Select" statements. (BatchExecuteStatement) { Aws::Vector<Aws::DynamoDB::Model::BatchStatementRequest> statements( titles.size()); std::stringstream sqlStream; sqlStream << "SELECT * FROM \"" << MOVIE_TABLE_NAME << "\" WHERE " << TITLE_KEY << "=? and " << YEAR_KEY << "=?"; std::string sql(sqlStream.str()); for (size_t i = 0; i < statements.size(); ++i) { statements[i].SetStatement(sql); Aws::Vector<Aws::DynamoDB::Model::AttributeValue> attributes; attributes.push_back( Aws::DynamoDB::Model::AttributeValue().SetS(titles[i])); attributes.push_back(Aws::DynamoDB::Model::AttributeValue().SetN(years[i])); statements[i].SetParameters(attributes); } Aws::DynamoDB::Model::BatchExecuteStatementRequest request; request.SetStatements(statements); Aws::DynamoDB::Model::BatchExecuteStatementOutcome outcome = dynamoClient.BatchExecuteStatement( request); if (outcome.IsSuccess()) { const Aws::DynamoDB::Model::BatchExecuteStatementResult &result = outcome.GetResult(); const Aws::Vector<Aws::DynamoDB::Model::BatchStatementResponse> &responses = result.GetResponses(); for (const Aws::DynamoDB::Model::BatchStatementResponse &response: responses) { const Aws::Map<Aws::String, Aws::DynamoDB::Model::AttributeValue> &item = response.GetItem(); printMovieInfo(item); } } else { std::cerr << "Failed to retrieve the movies information: " << outcome.GetError().GetMessage() << std::endl; return false; } } std::cout << "Deleting the movie data with a batch \"DELETE\" statement." << std::endl; // 6. Delete multiple movies using "Delete" statements. (BatchExecuteStatement) { Aws::Vector<Aws::DynamoDB::Model::BatchStatementRequest> statements( titles.size()); std::stringstream sqlStream; sqlStream << "DELETE FROM \"" << MOVIE_TABLE_NAME << "\" WHERE " << TITLE_KEY << "=? and " << YEAR_KEY << "=?"; std::string sql(sqlStream.str()); for (size_t i = 0; i < statements.size(); ++i) { statements[i].SetStatement(sql); Aws::Vector<Aws::DynamoDB::Model::AttributeValue> attributes; attributes.push_back( Aws::DynamoDB::Model::AttributeValue().SetS(titles[i])); attributes.push_back(Aws::DynamoDB::Model::AttributeValue().SetN(years[i])); statements[i].SetParameters(attributes); } Aws::DynamoDB::Model::BatchExecuteStatementRequest request; request.SetStatements(statements); Aws::DynamoDB::Model::BatchExecuteStatementOutcome outcome = dynamoClient.BatchExecuteStatement( request); if (!outcome.IsSuccess()) { std::cerr << "Failed to delete the movies: " << outcome.GetError().GetMessage() << std::endl; return false; } } return true; } //! Create a DynamoDB table to be used in sample code scenarios. /*! \sa createMoviesDynamoDBTable() \param clientConfiguration: AWS client configuration. \return bool: Function succeeded. */ bool AwsDoc::DynamoDB::createMoviesDynamoDBTable( const Aws::Client::ClientConfiguration &clientConfiguration) { Aws::DynamoDB::DynamoDBClient dynamoClient(clientConfiguration); bool movieTableAlreadyExisted = false; { Aws::DynamoDB::Model::CreateTableRequest request; Aws::DynamoDB::Model::AttributeDefinition yearAttributeDefinition; yearAttributeDefinition.SetAttributeName(YEAR_KEY); yearAttributeDefinition.SetAttributeType( Aws::DynamoDB::Model::ScalarAttributeType::N); request.AddAttributeDefinitions(yearAttributeDefinition); Aws::DynamoDB::Model::AttributeDefinition titleAttributeDefinition; yearAttributeDefinition.SetAttributeName(TITLE_KEY); yearAttributeDefinition.SetAttributeType( Aws::DynamoDB::Model::ScalarAttributeType::S); request.AddAttributeDefinitions(yearAttributeDefinition); Aws::DynamoDB::Model::KeySchemaElement yearKeySchema; yearKeySchema.WithAttributeName(YEAR_KEY).WithKeyType( Aws::DynamoDB::Model::KeyType::HASH); request.AddKeySchema(yearKeySchema); Aws::DynamoDB::Model::KeySchemaElement titleKeySchema; yearKeySchema.WithAttributeName(TITLE_KEY).WithKeyType( Aws::DynamoDB::Model::KeyType::RANGE); request.AddKeySchema(yearKeySchema); Aws::DynamoDB::Model::ProvisionedThroughput throughput; throughput.WithReadCapacityUnits( PROVISIONED_THROUGHPUT_UNITS).WithWriteCapacityUnits( PROVISIONED_THROUGHPUT_UNITS); request.SetProvisionedThroughput(throughput); request.SetTableName(MOVIE_TABLE_NAME); std::cout << "Creating table '" << MOVIE_TABLE_NAME << "'..." << std::endl; const Aws::DynamoDB::Model::CreateTableOutcome &result = dynamoClient.CreateTable( request); if (!result.IsSuccess()) { if (result.GetError().GetErrorType() == Aws::DynamoDB::DynamoDBErrors::RESOURCE_IN_USE) { std::cout << "Table already exists." << std::endl; movieTableAlreadyExisted = true; } else { std::cerr << "Failed to create table: " << result.GetError().GetMessage(); return false; } } } // Wait for table to become active. if (!movieTableAlreadyExisted) { std::cout << "Waiting for table '" << MOVIE_TABLE_NAME << "' to become active...." << std::endl; if (!AwsDoc::DynamoDB::waitTableActive(MOVIE_TABLE_NAME, clientConfiguration)) { return false; } std::cout << "Table '" << MOVIE_TABLE_NAME << "' created and active." << std::endl; } return true; } //! Delete the DynamoDB table used for sample code scenarios. /*! \sa deleteMoviesDynamoDBTable() \param clientConfiguration: AWS client configuration. \return bool: Function succeeded. */ bool AwsDoc::DynamoDB::deleteMoviesDynamoDBTable( const Aws::Client::ClientConfiguration &clientConfiguration) { Aws::DynamoDB::DynamoDBClient dynamoClient(clientConfiguration); Aws::DynamoDB::Model::DeleteTableRequest request; request.SetTableName(MOVIE_TABLE_NAME); const Aws::DynamoDB::Model::DeleteTableOutcome &result = dynamoClient.DeleteTable( request); if (result.IsSuccess()) { std::cout << "Your table \"" << result.GetResult().GetTableDescription().GetTableName() << " was deleted.\n"; } else { std::cerr << "Failed to delete table: " << result.GetError().GetMessage() << std::endl; } return result.IsSuccess(); } //! Query a newly created DynamoDB table until it is active. /*! \sa waitTableActive() \param waitTableActive: The DynamoDB table's name. \param dynamoClient: A DynamoDB client. \return bool: Function succeeded. */ bool AwsDoc::DynamoDB::waitTableActive(const Aws::String &tableName, const Aws::DynamoDB::DynamoDBClient &dynamoClient) { // Repeatedly call DescribeTable until table is ACTIVE. const int MAX_QUERIES = 20; Aws::DynamoDB::Model::DescribeTableRequest request; request.SetTableName(tableName); int count = 0; while (count < MAX_QUERIES) { const Aws::DynamoDB::Model::DescribeTableOutcome &result = dynamoClient.DescribeTable( request); if (result.IsSuccess()) { Aws::DynamoDB::Model::TableStatus status = result.GetResult().GetTable().GetTableStatus(); if (Aws::DynamoDB::Model::TableStatus::ACTIVE != status) { std::this_thread::sleep_for(std::chrono::seconds(1)); } else { return true; } } else { std::cerr << "Error DynamoDB::waitTableActive " << result.GetError().GetMessage() << std::endl; return false; } count++; } return false; }
Go
SDK for Go V2
Note

There's more on GitHub. Find the complete example and learn how to set up and run in the AWS Code Examples Repository.

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

import ( "context" "fmt" "log" "strings" "time" "github.com/aws/aws-sdk-go-v2/aws" "github.com/aws/aws-sdk-go-v2/service/dynamodb" "github.com/awsdocs/aws-doc-sdk-examples/gov2/dynamodb/actions" ) // 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(ctx context.Context, 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(ctx) if err != nil { panic(err) } if !exists { log.Printf("Creating table %v...\n", tableName) _, err = tableBasics.CreateMovieTable(ctx) 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(ctx, 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(ctx, 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(ctx, 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.") log.Println("Using a page size of 2 to demonstrate paging.") projections, err := runner.GetAllMovies(ctx, 2) if err == nil { log.Println("All movies:") for _, projection := range projections { log.Println(projection) } } log.Println(strings.Repeat("-", 88)) log.Println("Deleting a batch of movies.") err = runner.DeleteMovieBatch(ctx, customMovies) if err == nil { log.Printf("Deleted %v movies.\n", len(customMovies)) } err = tableBasics.DeleteTable(ctx) 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)) }

Define a Movie struct that is used in this example.

import ( "archive/zip" "bytes" "encoding/json" "fmt" "io" "log" "net/http" "github.com/aws/aws-sdk-go-v2/feature/dynamodb/attributevalue" "github.com/aws/aws-sdk-go-v2/service/dynamodb/types" ) // Movie encapsulates data about a movie. Title and Year are the composite primary key // of the movie in Amazon DynamoDB. Title is the sort key, Year is the partition key, // and Info is additional data. type Movie struct { Title string `dynamodbav:"title"` Year int `dynamodbav:"year"` Info map[string]interface{} `dynamodbav:"info"` } // GetKey returns the composite primary key of the movie in a format that can be // sent to DynamoDB. func (movie Movie) GetKey() map[string]types.AttributeValue { title, err := attributevalue.Marshal(movie.Title) if err != nil { panic(err) } year, err := attributevalue.Marshal(movie.Year) if err != nil { panic(err) } return map[string]types.AttributeValue{"title": title, "year": year} } // String returns the title, year, rating, and plot of a movie, formatted for the example. func (movie Movie) String() string { return fmt.Sprintf("%v\n\tReleased: %v\n\tRating: %v\n\tPlot: %v\n", movie.Title, movie.Year, movie.Info["rating"], movie.Info["plot"]) }

Create a struct and methods that run PartiQL statements.

import ( "context" "fmt" "log" "github.com/aws/aws-sdk-go-v2/aws" "github.com/aws/aws-sdk-go-v2/feature/dynamodb/attributevalue" "github.com/aws/aws-sdk-go-v2/service/dynamodb" "github.com/aws/aws-sdk-go-v2/service/dynamodb/types" ) // 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(ctx context.Context, 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(ctx, &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(ctx context.Context, 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(ctx, &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. // pageSize is not typically required and is used to show how to paginate the results. // The results are projected to return only the title and rating of each movie. func (runner PartiQLRunner) GetAllMovies(ctx context.Context, pageSize int32) ([]map[string]interface{}, error) { var output []map[string]interface{} var response *dynamodb.ExecuteStatementOutput var err error var nextToken *string for moreData := true; moreData; { response, err = runner.DynamoDbClient.ExecuteStatement(ctx, &dynamodb.ExecuteStatementInput{ Statement: aws.String( fmt.Sprintf("SELECT title, info.rating FROM \"%v\"", runner.TableName)), Limit: aws.Int32(pageSize), NextToken: nextToken, }) if err != nil { log.Printf("Couldn't get movies. Here's why: %v\n", err) moreData = false } else { var pageOutput []map[string]interface{} err = attributevalue.UnmarshalListOfMaps(response.Items, &pageOutput) if err != nil { log.Printf("Couldn't unmarshal response. Here's why: %v\n", err) } else { log.Printf("Got a page of length %v.\n", len(response.Items)) output = append(output, pageOutput...) } nextToken = response.NextToken moreData = nextToken != nil } } 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(ctx context.Context, 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(ctx, &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(ctx context.Context, 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(ctx, &dynamodb.BatchExecuteStatementInput{ Statements: statementRequests, }) if err != nil { log.Printf("Couldn't delete the batch of movies. Here's why: %v\n", err) } return err }
Java
SDK for Java 2.x
Note

There's more on GitHub. Find the complete example and learn how to set up and run in the AWS Code Examples Repository.

public class ScenarioPartiQLBatch { public static void main(String[] args) throws IOException { String tableName = "MoviesPartiQBatch"; Region region = Region.US_EAST_1; DynamoDbClient ddb = DynamoDbClient.builder() .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); // Update multiple movies by using the BatchExecute statement. String title1 = "Star Wars"; int year1 = 1977; String title2 = "Wizard of Oz"; int year2 = 1939; System.out.println("Query two movies."); getBatch(ddb, tableName, title1, title2, year1, year2); 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 boolean getBatch(DynamoDbClient ddb, String tableName, String title1, String title2, int year1, int year2) { String getBatch = "SELECT * FROM " + tableName + " WHERE title = ? AND year = ?"; List<BatchStatementRequest> statements = new ArrayList<>(); statements.add(BatchStatementRequest.builder() .statement(getBatch) .parameters(AttributeValue.builder().s(title1).build(), AttributeValue.builder().n(String.valueOf(year1)).build()) .build()); statements.add(BatchStatementRequest.builder() .statement(getBatch) .parameters(AttributeValue.builder().s(title2).build(), AttributeValue.builder().n(String.valueOf(year2)).build()) .build()); BatchExecuteStatementRequest batchExecuteStatementRequest = BatchExecuteStatementRequest.builder() .statements(statements) .build(); try { BatchExecuteStatementResponse response = ddb.batchExecuteStatement(batchExecuteStatementRequest); if (!response.responses().isEmpty()) { response.responses().forEach(r -> { System.out.println(r.item().get("title") + "\\t" + r.item().get("year")); }); return true; } else { System.out.println("Couldn't find either " + title1 + " or " + title2 + "."); return false; } } catch (DynamoDbException e) { System.err.println(e.getMessage()); return false; } } 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(10L) .writeCapacityUnits(10L) .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("1977") .build(); AttributeValue att2 = AttributeValue.builder() .s("Star Wars") .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("1939") .build(); AttributeValue attMovie2A = AttributeValue.builder() .s("Wizard of Oz") .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)
Note

There's more on GitHub. Find the complete example and learn how to set up and run in the AWS Code Examples Repository.

Execute batch PartiQL statements.

import { BillingMode, CreateTableCommand, DeleteTableCommand, DescribeTableCommand, DynamoDBClient, waitUntilTableExists, } from "@aws-sdk/client-dynamodb"; import { DynamoDBDocumentClient, BatchExecuteStatementCommand, } from "@aws-sdk/lib-dynamodb"; import { ScenarioInput } from "@aws-doc-sdk-examples/lib/scenario"; const client = new DynamoDBClient({}); const docClient = DynamoDBDocumentClient.from(client); const log = (msg) => console.log(`[SCENARIO] ${msg}`); const tableName = "Cities"; export const main = async (confirmAll = false) => { /** * Delete table if it exists. */ try { await client.send(new DescribeTableCommand({ TableName: tableName })); // If no error was thrown, the table exists. const input = new ScenarioInput( "deleteTable", `A table named ${tableName} already exists. If you choose not to delete this table, the scenario cannot continue. Delete it?`, { type: "confirm", confirmAll }, ); const deleteTable = await input.handle({}, { confirmAll }); if (deleteTable) { await client.send(new DeleteTableCommand({ tableName })); } else { console.warn( "Scenario could not run. Either delete ${tableName} or provide a unique table name.", ); return; } } catch (caught) { if ( caught instanceof Error && caught.name === "ResourceNotFoundException" ) { // Do nothing. This means the table is not there. } else { throw caught; } } /** * Create a table. */ log("Creating a table."); const createTableCommand = new CreateTableCommand({ TableName: tableName, // This example performs a large write to the database. // Set the billing mode to PAY_PER_REQUEST to // avoid throttling the large write. BillingMode: BillingMode.PAY_PER_REQUEST, // Define the attributes that are necessary for the key schema. AttributeDefinitions: [ { AttributeName: "name", // 'S' is a data type descriptor that represents a number type. // For a list of all data type descriptors, see the following link. // https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Programming.LowLevelAPI.html#Programming.LowLevelAPI.DataTypeDescriptors AttributeType: "S", }, ], // The KeySchema defines the primary key. The primary key can be // a partition key, or a combination of a partition key and a sort key. // Key schema design is important. For more info, see // https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/best-practices.html KeySchema: [{ AttributeName: "name", KeyType: "HASH" }], }); await client.send(createTableCommand); log(`Table created: ${tableName}.`); /** * Wait until the table is active. */ // This polls with DescribeTableCommand until the requested table is 'ACTIVE'. // You can't write to a table before it's active. log("Waiting for the table to be active."); await waitUntilTableExists({ client }, { TableName: tableName }); log("Table active."); /** * Insert items. */ log("Inserting cities into the table."); const addItemsStatementCommand = new BatchExecuteStatementCommand({ // https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.insert.html Statements: [ { Statement: `INSERT INTO ${tableName} value {'name':?, 'population':?}`, Parameters: ["Alachua", 10712], }, { Statement: `INSERT INTO ${tableName} value {'name':?, 'population':?}`, Parameters: ["High Springs", 6415], }, ], }); await docClient.send(addItemsStatementCommand); log("Cities inserted."); /** * Select items. */ log("Selecting cities from the table."); const selectItemsStatementCommand = new BatchExecuteStatementCommand({ // https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.select.html Statements: [ { Statement: `SELECT * FROM ${tableName} WHERE name=?`, Parameters: ["Alachua"], }, { Statement: `SELECT * FROM ${tableName} WHERE name=?`, Parameters: ["High Springs"], }, ], }); const selectItemResponse = await docClient.send(selectItemsStatementCommand); log( `Got cities: ${selectItemResponse.Responses.map( (r) => `${r.Item.name} (${r.Item.population})`, ).join(", ")}`, ); /** * Update items. */ log("Modifying the populations."); const updateItemStatementCommand = new BatchExecuteStatementCommand({ // https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.update.html Statements: [ { Statement: `UPDATE ${tableName} SET population=? WHERE name=?`, Parameters: [10, "Alachua"], }, { Statement: `UPDATE ${tableName} SET population=? WHERE name=?`, Parameters: [5, "High Springs"], }, ], }); await docClient.send(updateItemStatementCommand); log("Updated cities."); /** * Delete the items. */ log("Deleting the cities."); const deleteItemStatementCommand = new BatchExecuteStatementCommand({ // https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.delete.html Statements: [ { Statement: `DELETE FROM ${tableName} WHERE name=?`, Parameters: ["Alachua"], }, { Statement: `DELETE FROM ${tableName} WHERE name=?`, Parameters: ["High Springs"], }, ], }); await docClient.send(deleteItemStatementCommand); log("Cities deleted."); /** * Delete the table. */ log("Deleting the table."); const deleteTableCommand = new DeleteTableCommand({ TableName: tableName }); await client.send(deleteTableCommand); log("Table deleted."); };
Kotlin
SDK for Kotlin
Note

There's more on GitHub. Find the complete example and learn how to set up and run in the AWS Code Examples Repository.

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
Note

There's more on GitHub. Find the complete example and learn how to set up and run in the AWS Code Examples Repository.

namespace DynamoDb\PartiQL_Basics; use Aws\DynamoDb\Marshaler; use DynamoDb; use DynamoDb\DynamoDBAttribute; use function AwsUtilities\loadMovieData; use function AwsUtilities\testable_readline; class GettingStartedWithPartiQLBatch { public function run() { echo("\n"); 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)
Note

There's more on GitHub. Find the complete example and learn how to set up and run in the AWS Code Examples Repository.

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}")
Ruby
SDK for Ruby
Note

There's more on GitHub. Find the complete example and learn how to set up and run in the AWS Code Examples Repository.

Run a scenario that creates a table and runs batch PartiQL queries.

table_name = "doc-example-table-movies-partiql-#{rand(10**4)}" scaffold = Scaffold.new(table_name) sdk = DynamoDBPartiQLBatch.new(table_name) new_step(1, 'Create a new DynamoDB table if none already exists.') unless scaffold.exists?(table_name) puts("\nNo such table: #{table_name}. Creating it...") scaffold.create_table(table_name) print "Done!\n".green end new_step(2, 'Populate DynamoDB table with movie data.') download_file = 'moviedata.json' puts("Downloading movie database to #{download_file}...") movie_data = scaffold.fetch_movie_data(download_file) puts("Writing movie data from #{download_file} into your table...") scaffold.write_batch(movie_data) puts("Records added: #{movie_data.length}.") print "Done!\n".green new_step(3, 'Select a batch of items from the movies table.') puts "Let's select some popular movies for side-by-side comparison." response = sdk.batch_execute_select([['Mean Girls', 2004], ['Goodfellas', 1977], ['The Prancing of the Lambs', 2005]]) puts("Items selected: #{response['responses'].length}\n") print "\nDone!\n".green new_step(4, 'Delete a batch of items from the movies table.') sdk.batch_execute_write([['Mean Girls', 2004], ['Goodfellas', 1977], ['The Prancing of the Lambs', 2005]]) print "\nDone!\n".green new_step(5, 'Delete the table.') return unless scaffold.exists?(table_name) scaffold.delete_table end

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