Query a DynamoDB table using PartiQL 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 using PartiQL and an Amazon SDK

The following code examples show how to:

  • Get an item by running a SELECT statement.

  • Add an item by running an INSERT statement.

  • Update an item by running an UPDATE statement.

  • Delete an item by running a DELETE statement.

.NET
Amazon SDK for .NET
Tip

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

namespace PartiQL_Basics_Scenario { public class PartiQLMethods { private static readonly AmazonDynamoDBClient Client = new AmazonDynamoDBClient(); /// <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 where 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> /// Uses a PartiQL SELECT statement to retrieve a single movie from the /// movie database. /// </summary> /// <param name="tableName">The name of the movie table.</param> /// <param name="movieTitle">The title of the movie to retrieve.</param> /// <returns>A list of movie data. If no movie matches the supplied /// title, the list is empty.</returns> public static async Task<List<Dictionary<string, AttributeValue>>> GetSingleMovie(string tableName, string movieTitle) { string selectSingle = $"SELECT * FROM {tableName} WHERE title = ?"; var parameters = new List<AttributeValue> { new AttributeValue { S = movieTitle }, }; var response = await Client.ExecuteStatementAsync(new ExecuteStatementRequest { Statement = selectSingle, Parameters = parameters, }); return response.Items; } /// <summary> /// Retrieve multiple movies by year using a SELECT statement. /// </summary> /// <param name="tableName">The name of the movie table.</param> /// <param name="year">The year the movies were released.</param> /// <returns></returns> public static async Task<List<Dictionary<string, AttributeValue>>> GetMovies(string tableName, int year) { string selectSingle = $"SELECT * FROM {tableName} WHERE year = ?"; var parameters = new List<AttributeValue> { new AttributeValue { N = year.ToString() }, }; var response = await Client.ExecuteStatementAsync(new ExecuteStatementRequest { Statement = selectSingle, Parameters = parameters, }); return response.Items; } /// <summary> /// Inserts a single movie into the movies table. /// </summary> /// <param name="tableName">The name of the table.</param> /// <param name="movieTitle">The title of the movie to insert.</param> /// <param name="year">The year that the movie was released.</param> /// <returns>A Boolean value that indicates the success or failure of /// the INSERT operation.</returns> public static async Task<bool> InsertSingleMovie(string tableName, string movieTitle, int year) { string insertBatch = $"INSERT INTO {tableName} VALUE {{'title': ?, 'year': ?}}"; var response = await Client.ExecuteStatementAsync(new ExecuteStatementRequest { Statement = insertBatch, Parameters = new List<AttributeValue> { new AttributeValue { S = movieTitle }, new AttributeValue { N = year.ToString() }, }, }); return response.HttpStatusCode == System.Net.HttpStatusCode.OK; } /// <summary> /// Updates a single movie in the table, adding information for the /// producer. /// </summary> /// <param name="tableName">the name of the table.</param> /// <param name="producer">The name of the producer.</param> /// <param name="movieTitle">The movie title.</param> /// <param name="year">The year the movie was released.</param> /// <returns>A Boolean value that indicates the success of the /// UPDATE operation.</returns> public static async Task<bool> UpdateSingleMovie(string tableName, string producer, string movieTitle, int year) { string insertSingle = $"UPDATE {tableName} SET Producer=? WHERE title = ? AND year = ?"; var response = await Client.ExecuteStatementAsync(new ExecuteStatementRequest { Statement = insertSingle, Parameters = new List<AttributeValue> { new AttributeValue { S = producer }, new AttributeValue { S = movieTitle }, new AttributeValue { N = year.ToString() }, }, }); return response.HttpStatusCode == System.Net.HttpStatusCode.OK; } /// <summary> /// Deletes a single movie from the table. /// </summary> /// <param name="tableName">The name of the table.</param> /// <param name="movieTitle">The title of the movie to delete.</param> /// <param name="year">The year that the movie was released.</param> /// <returns>A Boolean value that indicates the success of the /// DELETE operation.</returns> public static async Task<bool> DeleteSingleMovie(string tableName, string movieTitle, int year) { var deleteSingle = $"DELETE FROM {tableName} WHERE title = ? AND year = ?"; var response = await Client.ExecuteStatementAsync(new ExecuteStatementRequest { Statement = deleteSingle, Parameters = new List<AttributeValue> { new AttributeValue { S = movieTitle }, new AttributeValue { N = year.ToString() }, }, }); return response.HttpStatusCode == System.Net.HttpStatusCode.OK; } /// <summary> /// Displays the list of movies returned from a database query. /// </summary> /// <param name="items">The list of movie information to display.</param> private static void DisplayMovies(List<Dictionary<string, AttributeValue>> items) { if (items.Count > 0) { Console.WriteLine($"Found {items.Count} movies."); items.ForEach(item => Console.WriteLine($"{item["year"].N}\t{item["title"].S}")); } else { Console.WriteLine($"Didn't find a movie that matched the supplied criteria."); } } } } /// <summary> /// Uses a PartiQL SELECT statement to retrieve a single movie from the /// movie database. /// </summary> /// <param name="tableName">The name of the movie table.</param> /// <param name="movieTitle">The title of the movie to retrieve.</param> /// <returns>A list of movie data. If no movie matches the supplied /// title, the list is empty.</returns> public static async Task<List<Dictionary<string, AttributeValue>>> GetSingleMovie(string tableName, string movieTitle) { string selectSingle = $"SELECT * FROM {tableName} WHERE title = ?"; var parameters = new List<AttributeValue> { new AttributeValue { S = movieTitle }, }; var response = await Client.ExecuteStatementAsync(new ExecuteStatementRequest { Statement = selectSingle, Parameters = parameters, }); return response.Items; } /// <summary> /// Inserts a single movie into the movies table. /// </summary> /// <param name="tableName">The name of the table.</param> /// <param name="movieTitle">The title of the movie to insert.</param> /// <param name="year">The year that the movie was released.</param> /// <returns>A Boolean value that indicates the success or failure of /// the INSERT operation.</returns> public static async Task<bool> InsertSingleMovie(string tableName, string movieTitle, int year) { string insertBatch = $"INSERT INTO {tableName} VALUE {{'title': ?, 'year': ?}}"; var response = await Client.ExecuteStatementAsync(new ExecuteStatementRequest { Statement = insertBatch, Parameters = new List<AttributeValue> { new AttributeValue { S = movieTitle }, new AttributeValue { N = year.ToString() }, }, }); return response.HttpStatusCode == System.Net.HttpStatusCode.OK; } /// <summary> /// Updates a single movie in the table, adding information for the /// producer. /// </summary> /// <param name="tableName">the name of the table.</param> /// <param name="producer">The name of the producer.</param> /// <param name="movieTitle">The movie title.</param> /// <param name="year">The year the movie was released.</param> /// <returns>A Boolean value that indicates the success of the /// UPDATE operation.</returns> public static async Task<bool> UpdateSingleMovie(string tableName, string producer, string movieTitle, int year) { string insertSingle = $"UPDATE {tableName} SET Producer=? WHERE title = ? AND year = ?"; var response = await Client.ExecuteStatementAsync(new ExecuteStatementRequest { Statement = insertSingle, Parameters = new List<AttributeValue> { new AttributeValue { S = producer }, new AttributeValue { S = movieTitle }, new AttributeValue { N = year.ToString() }, }, }); return response.HttpStatusCode == System.Net.HttpStatusCode.OK; } /// <summary> /// Deletes a single movie from the table. /// </summary> /// <param name="tableName">The name of the table.</param> /// <param name="movieTitle">The title of the movie to delete.</param> /// <param name="year">The year that the movie was released.</param> /// <returns>A Boolean value that indicates the success of the /// DELETE operation.</returns> public static async Task<bool> DeleteSingleMovie(string tableName, string movieTitle, int year) { var deleteSingle = $"DELETE FROM {tableName} WHERE title = ? AND year = ?"; var response = await Client.ExecuteStatementAsync(new ExecuteStatementRequest { Statement = deleteSingle, Parameters = new List<AttributeValue> { new AttributeValue { S = movieTitle }, new AttributeValue { N = year.ToString() }, }, }); 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 } // AddMovie runs a PartiQL INSERT statement to add a movie to the DynamoDB table. func (runner PartiQLRunner) AddMovie(movie Movie) error { params, err := attributevalue.MarshalList([]interface{}{movie.Title, movie.Year, movie.Info}) if err != nil { panic(err) } _, err = runner.DynamoDbClient.ExecuteStatement(context.TODO(), &dynamodb.ExecuteStatementInput{ Statement: aws.String( fmt.Sprintf("INSERT INTO \"%v\" VALUE {'title': ?, 'year': ?, 'info': ?}", runner.TableName)), Parameters: params, }) if err != nil { log.Printf("Couldn't insert an item with PartiQL. Here's why: %v\n", err) } return err } // GetMovie runs a PartiQL SELECT statement to get a movie from the DynamoDB table by // title and year. func (runner PartiQLRunner) GetMovie(title string, year int) (Movie, error) { var movie Movie params, err := attributevalue.MarshalList([]interface{}{title, year}) if err != nil { panic(err) } response, err := runner.DynamoDbClient.ExecuteStatement(context.TODO(), &dynamodb.ExecuteStatementInput{ Statement: aws.String( fmt.Sprintf("SELECT * FROM \"%v\" WHERE title=? AND year=?", runner.TableName)), Parameters: params, }) if err != nil { log.Printf("Couldn't get info about %v. Here's why: %v\n", title, err) } else { err = attributevalue.UnmarshalMap(response.Items[0], &movie) if err != nil { log.Printf("Couldn't unmarshal response. Here's why: %v\n", err) } } return movie, err } // UpdateMovie runs a PartiQL UPDATE statement to update the rating of a movie that // already exists in the DynamoDB table. func (runner PartiQLRunner) UpdateMovie(movie Movie, rating float64) error { params, err := attributevalue.MarshalList([]interface{}{rating, movie.Title, movie.Year}) if err != nil { panic(err) } _, err = runner.DynamoDbClient.ExecuteStatement(context.TODO(), &dynamodb.ExecuteStatementInput{ Statement: aws.String( fmt.Sprintf("UPDATE \"%v\" SET info.rating=? WHERE title=? AND year=?", runner.TableName)), Parameters: params, }) if err != nil { log.Printf("Couldn't update movie %v. Here's why: %v\n", movie.Title, err) } return err } // DeleteMovie runs a PartiQL DELETE statement to remove a movie from the DynamoDB table. func (runner PartiQLRunner) DeleteMovie(movie Movie) error { params, err := attributevalue.MarshalList([]interface{}{movie.Title, movie.Year}) if err != nil { panic(err) } _, err = runner.DynamoDbClient.ExecuteStatement(context.TODO(), &dynamodb.ExecuteStatementInput{ Statement: aws.String( fmt.Sprintf("DELETE FROM \"%v\" WHERE title=? AND year=?", runner.TableName)), Parameters: params, }) if err != nil { log.Printf("Couldn't delete %v from the table. Here's why: %v\n", movie.Title, err) } return err }

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

// RunPartiQLSingleScenario shows you how to use the AWS SDK for Go // to use PartiQL to query a table that stores data about movies. // // * Use 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 RunPartiQLSingleScenario(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 single action 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() customMovie := actions.Movie{ Title: "24 Hour PartiQL People", Year: currentYear, Info: map[string]interface{}{ "plot": "A group of data developers discover a new query language they can't stop using.", "rating": 9.9, }, } log.Printf("Inserting movie '%v' released in %v.", customMovie.Title, customMovie.Year) err = runner.AddMovie(customMovie) if err == nil { log.Printf("Added %v to the movie table.\n", customMovie.Title) } log.Println(strings.Repeat("-", 88)) log.Printf("Getting data for movie '%v' released in %v.", customMovie.Title, customMovie.Year) movie, err := runner.GetMovie(customMovie.Title, customMovie.Year) if err == nil { log.Println(movie) } log.Println(strings.Repeat("-", 88)) newRating := 6.6 log.Printf("Updating movie '%v' with a rating of %v.", customMovie.Title, newRating) err = runner.UpdateMovie(customMovie, newRating) if err == nil { log.Printf("Updated %v with a new rating.\n", customMovie.Title) } log.Println(strings.Repeat("-", 88)) log.Printf("Getting data again to verify the update.") movie, err = runner.GetMovie(customMovie.Title, customMovie.Year) if err == nil { log.Println(movie) } log.Println(strings.Repeat("-", 88)) log.Printf("Deleting movie '%v'.\n", customMovie.Title) err = runner.DeleteMovie(customMovie) if err == nil { log.Printf("Deleted %v.\n", customMovie.Title) } 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 ScenarioPartiQ { public static void main(String [] args) throws IOException { final String usage = "\n" + "Usage:\n" + " <fileName>\n\n" + "Where:\n" + " fileName - The path to the moviedata.json file that you can download from the Amazon DynamoDB Developer Guide.\n" ; if (args.length != 1) { System.out.println(usage); System.exit(1); } String fileName = args[0]; String tableName = "MoviesPartiQ"; 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 MoviesPartiQ with a key named year and a sort key named title."); createTable(ddb, tableName); System.out.println("******* Loading data into the MoviesPartiQ table."); loadData(ddb, fileName); System.out.println("******* Getting data from the MoviesPartiQ table."); getItem(ddb); System.out.println("******* Putting a record into the MoviesPartiQ table."); putRecord(ddb); System.out.println("******* Updating a record."); updateTableItem(ddb); System.out.println("******* Querying the movies released in 2013."); queryTable(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); } } // Load data into the table. public static void loadData(DynamoDbClient ddb, String fileName) throws IOException { String sqlStatement = "INSERT INTO MoviesPartiQ VALUE {'year':?, 'title' : ?, 'info' : ?}"; JsonParser parser = new JsonFactory().createParser(new File(fileName)); com.fasterxml.jackson.databind.JsonNode rootNode = new ObjectMapper().readTree(parser); Iterator<JsonNode> iter = rootNode.iterator(); ObjectNode currentNode; int t = 0 ; List<AttributeValue> parameters = new ArrayList<>(); while (iter.hasNext()) { // Add 200 movies to the table. if (t == 200) break ; currentNode = (ObjectNode) iter.next(); int year = currentNode.path("year").asInt(); String title = currentNode.path("title").asText(); String info = currentNode.path("info").toString(); AttributeValue att1 = AttributeValue.builder() .n(String.valueOf(year)) .build(); AttributeValue att2 = AttributeValue.builder() .s(title) .build(); AttributeValue att3 = AttributeValue.builder() .s(info) .build(); parameters.add(att1); parameters.add(att2); parameters.add(att3); // Insert the movie into the Amazon DynamoDB table. executeStatementRequest(ddb, sqlStatement, parameters); System.out.println("Added Movie " +title); parameters.remove(att1); parameters.remove(att2); parameters.remove(att3); t++; } } public static void getItem(DynamoDbClient ddb) { String sqlStatement = "SELECT * FROM MoviesPartiQ where year=? and title=?"; List<AttributeValue> parameters = new ArrayList<>(); AttributeValue att1 = AttributeValue.builder() .n("2012") .build(); AttributeValue att2 = AttributeValue.builder() .s("The Perks of Being a Wallflower") .build(); parameters.add(att1); parameters.add(att2); try { ExecuteStatementResponse response = executeStatementRequest(ddb, sqlStatement, parameters); System.out.println("ExecuteStatement successful: "+ response.toString()); } catch (DynamoDbException e) { System.err.println(e.getMessage()); System.exit(1); } } public static void putRecord(DynamoDbClient ddb) { String sqlStatement = "INSERT INTO MoviesPartiQ VALUE {'year':?, 'title' : ?, 'info' : ?}"; try { List<AttributeValue> parameters = new ArrayList<>(); AttributeValue att1 = AttributeValue.builder() .n(String.valueOf("2020")) .build(); AttributeValue att2 = AttributeValue.builder() .s("My Movie") .build(); AttributeValue att3 = AttributeValue.builder() .s("No Information") .build(); parameters.add(att1); parameters.add(att2); parameters.add(att3); executeStatementRequest(ddb, sqlStatement, parameters); System.out.println("Added new movie."); } catch (DynamoDbException e) { System.err.println(e.getMessage()); System.exit(1); } } public static void updateTableItem(DynamoDbClient ddb){ String sqlStatement = "UPDATE MoviesPartiQ SET info = 'directors\":[\"Merian C. Cooper\",\"Ernest B. Schoedsack' where year=? and title=?"; List<AttributeValue> parameters = new ArrayList<>(); AttributeValue att1 = AttributeValue.builder() .n(String.valueOf("2013")) .build(); AttributeValue att2 = AttributeValue.builder() .s("The East") .build(); parameters.add(att1); parameters.add(att2); try { executeStatementRequest(ddb, sqlStatement, parameters); } catch (DynamoDbException e) { System.err.println(e.getMessage()); System.exit(1); } System.out.println("Item was updated!"); } // Query the table where the year is 2013. public static void queryTable(DynamoDbClient ddb) { String sqlStatement = "SELECT * FROM MoviesPartiQ where year = ? ORDER BY year"; try { List<AttributeValue> parameters = new ArrayList<>(); AttributeValue att1 = AttributeValue.builder() .n(String.valueOf("2013")) .build(); parameters.add(att1); // Get items in the table and write out the ID value. ExecuteStatementResponse response = executeStatementRequest(ddb, sqlStatement, parameters); System.out.println("ExecuteStatement successful: "+ response.toString()); } 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); } private static void processResults(ExecuteStatementResponse executeStatementResult) { System.out.println("ExecuteStatement successful: "+ executeStatementResult.toString()); } }
  • For API details, see ExecuteStatement in Amazon SDK for Java 2.x API Reference.

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 single items.

*/ 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, ExecuteStatementCommand, } from "@aws-sdk/client-dynamodb"; if (process.argv.length < 6) { console.log( "Usage: node partiQL_basics.js <tableName> <movieYear1> <movieTitle1> <producer1>\n" + "Example: node partiQL_basics.js Movies 2006 'The Departed' 'New View Films'" ); } // 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(); } } const tableName = process.argv[2]; const movieTitle1 = process.argv[3]; const movieYear1 = process.argv[4]; const producer1 = process.argv[5]; export const run = async (tableName, movieYear1, movieTitle1, producer1) => { 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(20000); console.log("Success, movies written to table."); try { const params = { Statement: "SELECT * FROM " + tableName + " where title=?", Parameters: [{ S: movieTitle1 }], }; console.log("Getting movie...."); console.log("Statement", params.Statement); const data = await ddbDocClient.send( new ExecuteStatementCommand(params) ); for (let i = 0; i < data.Items.length; i++) { console.log( "Success. The query return the following data. Item " + i, data.Items[i].year, data.Items[i].title, data.Items[i].info ); } try { const params = { Statement: "DELETE FROM " + tableName + " where title=? and year=?", Parameters: [{ S: movieTitle1 }, { N: movieYear1 }], }; const data = await ddbDocClient.send( new ExecuteStatementCommand(params) ); console.log("Success. Item deleted."); try { const params = { Statement: "INSERT INTO " + tableName + " value {'title':?, 'year':?}", Parameters: [{ S: movieTitle1 }, { N: movieYear1 }], }; const data = await ddbDocClient.send( new ExecuteStatementCommand(params) ); console.log("Success. Item added."); try { const params = { Statement: "UPDATE " + tableName + " SET Producer=? where title=? and year=?", Parameters: [ { S: producer1 }, { S: movieTitle1 }, { N: movieYear1 }, ], }; console.log("Updating a single movie..."); const data = await ddbDocClient.send( new ExecuteStatementCommand(params) ); console.log("Success. Item updated."); return "Run successfully"; // For unit tests. } catch (err) { console.log("Error updating item. ", err); } } catch (err) { console.log("Error adding items to table. ", err); } } catch (err) { console.log("Error deleting movie. ", err); } } catch (err) { console.log("Error getting movie. ", err); } } catch (err) { console.log("Error adding movies by batch. ", err); } } catch (err) { console.log("Error creating table. ", err); } }; run(tableName, movieYear1, movieTitle1, producer1);
  • For API details, see ExecuteStatement in Amazon SDK for JavaScript API Reference.

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(args: Array<String>) { val usage = """ Usage: <fileName> Where: fileName - The path to the moviedata.json you can download from the Amazon DynamoDB Developer Guide. """ if (args.size != 1) { println(usage) exitProcess(1) } val ddb = DynamoDbClient { region = "us-east-1" } val tableName = "MoviesPartiQ" // Get the moviedata.json from the Amazon DynamoDB Developer Guide. val fileName = args[0] println("Creating an Amazon DynamoDB table named MoviesPartiQ with a key named id and a sort key named title.") createTablePartiQL(ddb, tableName, "year") loadDataPartiQL(ddb, fileName) println("******* Getting data from the MoviesPartiQ table.") getMoviePartiQL(ddb) println("******* Putting a record into the MoviesPartiQ table.") putRecordPartiQL(ddb) println("******* Updating a record.") updateTableItemPartiQL(ddb) println("******* Querying the movies released in 2013.") queryTablePartiQL(ddb) println("******* Deleting the MoviesPartiQ table.") deleteTablePartiQL(tableName) } suspend fun createTablePartiQL(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 loadDataPartiQL(ddb: DynamoDbClient, fileName: String) { val sqlStatement = "INSERT INTO MoviesPartiQ VALUE {'year':?, 'title' : ?, 'info' : ?}" val parser = JsonFactory().createParser(File(fileName)) val rootNode = ObjectMapper().readTree<JsonNode>(parser) val iter: Iterator<JsonNode> = rootNode.iterator() var currentNode: ObjectNode var t = 0 while (iter.hasNext()) { if (t == 200) break currentNode = iter.next() as ObjectNode val year = currentNode.path("year").asInt() val title = currentNode.path("title").asText() val info = currentNode.path("info").toString() val parameters: MutableList<AttributeValue> = ArrayList<AttributeValue>() parameters.add(AttributeValue.N(year.toString())) parameters.add(AttributeValue.S(title)) parameters.add(AttributeValue.S(info)) executeStatementPartiQL(ddb, sqlStatement, parameters) println("Added Movie $title") parameters.clear() t++ } } suspend fun getMoviePartiQL(ddb: DynamoDbClient) { val sqlStatement = "SELECT * FROM MoviesPartiQ where year=? and title=?" val parameters: MutableList<AttributeValue> = ArrayList<AttributeValue>() parameters.add(AttributeValue.N("2012")) parameters.add(AttributeValue.S("The Perks of Being a Wallflower")) val response = executeStatementPartiQL(ddb, sqlStatement, parameters) println("ExecuteStatement successful: $response") } suspend fun putRecordPartiQL(ddb: DynamoDbClient) { val sqlStatement = "INSERT INTO MoviesPartiQ VALUE {'year':?, 'title' : ?, 'info' : ?}" val parameters: MutableList<AttributeValue> = java.util.ArrayList() parameters.add(AttributeValue.N("2020")) parameters.add(AttributeValue.S("My Movie")) parameters.add(AttributeValue.S("No Info")) executeStatementPartiQL(ddb, sqlStatement, parameters) println("Added new movie.") } suspend fun updateTableItemPartiQL(ddb: DynamoDbClient) { val sqlStatement = "UPDATE MoviesPartiQ SET info = 'directors\":[\"Merian C. Cooper\",\"Ernest B. Schoedsack\' where year=? and title=?" val parameters: MutableList<AttributeValue> = java.util.ArrayList() parameters.add(AttributeValue.N("2013")) parameters.add(AttributeValue.S("The East")) executeStatementPartiQL(ddb, sqlStatement, parameters) println("Item was updated!") } // Query the table where the year is 2013. suspend fun queryTablePartiQL(ddb: DynamoDbClient) { val sqlStatement = "SELECT * FROM MoviesPartiQ where year = ?" val parameters: MutableList<AttributeValue> = java.util.ArrayList() parameters.add(AttributeValue.N("2013")) val response = executeStatementPartiQL(ddb, sqlStatement, parameters) println("ExecuteStatement successful: $response") } suspend fun deleteTablePartiQL(tableNameVal: String) { val request = DeleteTableRequest { tableName = tableNameVal } DynamoDbClient { region = "us-east-1" }.use { ddb -> ddb.deleteTable(request) println("$tableNameVal was deleted") } } suspend fun executeStatementPartiQL( ddb: DynamoDbClient, statementVal: String, parametersVal: List<AttributeValue> ): ExecuteStatementResponse { val request = ExecuteStatementRequest { statement = statementVal parameters = parametersVal } return ddb.executeStatement(request) }
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 GettingStartedWithPartiQL { 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->insertItemByPartiQL($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->updateItemByPartiQL($statement, $parameters); echo "Movie added and updated.\n"; $batch = json_decode(loadMovieData()); $service->writeBatch($tableName, $batch); $movie = $service->getItemByPartiQL($tableName, $key); echo "\nThe movie {$movie['Items'][0]['title']['S']} was released in {$movie['Items'][0]['year']['N']}.\n"; echo "What rating would you like to give {$movie['Items'][0]['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->updateItemByPartiQL($statement, $parameters); $movie = $service->getItemByPartiQL($tableName, $key); echo "Okay, you have rated {$movie['Items'][0]['title']['S']} as a {$movie['Items'][0]['rating']['N']}\n"; $service->deleteItemByPartiQL($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 insertItemByPartiQL(string $statement, array $parameters) { $this->dynamoDbClient->executeStatement([ 'Statement' => "$statement", 'Parameters' => $parameters, ]); } public function getItemByPartiQL(string $tableName, array $key): Result { list($statement, $parameters) = $this->buildStatementAndParameters("SELECT", $tableName, $key['Item']); return $this->dynamoDbClient->executeStatement([ 'Parameters' => $parameters, 'Statement' => $statement, ]); } public function updateItemByPartiQL(string $statement, array $parameters) { $this->dynamoDbClient->executeStatement([ 'Statement' => $statement, 'Parameters' => $parameters, ]); } public function deleteItemByPartiQL(string $statement, array $parameters) { $this->dynamoDbClient->executeStatement([ '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 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 PartiQLWrapper: """ 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, statement, params): """ 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 statement: The PartiQL statement. :param params: The list of PartiQL parameters. These are applied to the statement in the order they are listed. :return: The items returned from the statement, if any. """ try: output = self.dyn_resource.meta.client.execute_statement( Statement=statement, Parameters=params) except ClientError as err: if err.response['Error']['Code'] == 'ResourceNotFoundException': logger.error( "Couldn't execute PartiQL '%s' because the table does not exist.", statement) else: logger.error( "Couldn't execute PartiQL '%s'. Here's why: %s: %s", statement, err.response['Error']['Code'], err.response['Error']['Message']) raise else: return output

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

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 single statement demo.") print('-'*88) print(f"Creating table '{table_name}' for the demo...") scaffold.create_table(table_name) print('-'*88) title = "24 Hour PartiQL People" year = datetime.now().year plot = "A group of data developers discover a new query language they can't stop using." rating = Decimal('9.9') print(f"Inserting movie '{title}' released in {year}.") wrapper.run_partiql( f"INSERT INTO \"{table_name}\" VALUE {{'title': ?, 'year': ?, 'info': ?}}", [title, year, {'plot': plot, 'rating': rating}]) print("Success!") print('-'*88) print(f"Getting data for movie '{title}' released in {year}.") output = wrapper.run_partiql( f"SELECT * FROM \"{table_name}\" WHERE title=? AND year=?", [title, year]) for item in output['Items']: print(f"\n{item['title']}, {item['year']}") pprint(output['Items']) print('-'*88) rating = Decimal('2.4') print(f"Updating movie '{title}' with a rating of {float(rating)}.") wrapper.run_partiql( f"UPDATE \"{table_name}\" SET info.rating=? WHERE title=? AND year=?", [rating, title, year]) print("Success!") print('-'*88) print(f"Getting data again to verify our update.") output = wrapper.run_partiql( f"SELECT * FROM \"{table_name}\" WHERE title=? AND year=?", [title, year]) for item in output['Items']: print(f"\n{item['title']}, {item['year']}") pprint(output['Items']) print('-'*88) print(f"Deleting movie '{title}' released in {year}.") wrapper.run_partiql( f"DELETE FROM \"{table_name}\" WHERE title=? AND year=?", [title, year]) 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 = PartiQLWrapper(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 API details, see ExecuteStatement in Amazon SDK for Python (Boto3) API Reference.

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.