使用 PartiQL 和软件开发工具包查询 DynamoDB 表 Amazon - Amazon DynamoDB
Amazon Web Services 文档中描述的 Amazon Web Services 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅 中国的 Amazon Web Services 服务入门 (PDF)

本文属于机器翻译版本。若本译文内容与英语原文存在差异,则一律以英文原文为准。

使用 PartiQL 和软件开发工具包查询 DynamoDB 表 Amazon

以下代码示例显示了如何:

  • 通过运行一个 SELECT 语句获取一个项目。

  • 通过运行 INSERT 语句来添加项目。

  • 通过运行 UPDATE 语句来更新项目。

  • 通过运行 DELETE 语句来删除项目。

.NET
Amazon SDK for .NET
注意

还有更多相关信息 GitHub。在 Amazon 代码示例存储库中查找完整示例,了解如何进行设置和运行。

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; }
  • 有关 API 的详细信息,请参阅 Amazon SDK for .NET API 参考ExecuteStatement中的。

C++
适用于 C++ 的 SDK
注意

还有更多相关信息 GitHub。在 Amazon 代码示例存储库中查找完整示例,了解如何进行设置和运行。

// 1. Create a table. (CreateTable) if (AwsDoc::DynamoDB::createMoviesDynamoDBTable(clientConfig)) { AwsDoc::DynamoDB::partiqlExecuteScenario(clientConfig); // 7. Delete the table. (DeleteTable) AwsDoc::DynamoDB::deleteMoviesDynamoDBTable(clientConfig); } //! Scenario to modify and query a DynamoDB table using single PartiQL statements. /*! \sa partiqlExecuteScenario() \param clientConfiguration: AWS client configuration. \return bool: Function succeeded. */ bool AwsDoc::DynamoDB::partiqlExecuteScenario( const Aws::Client::ClientConfiguration &clientConfiguration) { Aws::DynamoDB::DynamoDBClient dynamoClient(clientConfiguration); // 2. Add a new movie using an "Insert" statement. (ExecuteStatement) Aws::String title; float rating; int year; Aws::String plot; { title = askQuestion( "Enter the title of a movie you want to add to the table: "); year = askQuestionForInt("What year was it released? "); rating = askQuestionForFloatRange("On a scale of 1 - 10, how do you rate it? ", 1, 10); plot = askQuestion("Summarize the plot for me: "); Aws::DynamoDB::Model::ExecuteStatementRequest request; std::stringstream sqlStream; sqlStream << "INSERT INTO \"" << MOVIE_TABLE_NAME << "\" VALUE {'" << TITLE_KEY << "': ?, '" << YEAR_KEY << "': ?, '" << INFO_KEY << "': ?}"; request.SetStatement(sqlStream.str()); // Create the parameter attributes. Aws::Vector<Aws::DynamoDB::Model::AttributeValue> attributes; attributes.push_back(Aws::DynamoDB::Model::AttributeValue().SetS(title)); attributes.push_back(Aws::DynamoDB::Model::AttributeValue().SetN(year)); 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(rating); 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(plot); infoMapAttribute.AddMEntry(PLOT_KEY, plotAttribute); attributes.push_back(infoMapAttribute); request.SetParameters(attributes); Aws::DynamoDB::Model::ExecuteStatementOutcome outcome = dynamoClient.ExecuteStatement( request); if (!outcome.IsSuccess()) { std::cerr << "Failed to add a movie: " << outcome.GetError().GetMessage() << std::endl; return false; } } std::cout << "\nAdded '" << title << "' to '" << MOVIE_TABLE_NAME << "'." << std::endl; // 3. Get the data for the movie using a "Select" statement. (ExecuteStatement) { Aws::DynamoDB::Model::ExecuteStatementRequest request; std::stringstream sqlStream; sqlStream << "SELECT * FROM \"" << MOVIE_TABLE_NAME << "\" WHERE " << TITLE_KEY << "=? and " << YEAR_KEY << "=?"; request.SetStatement(sqlStream.str()); Aws::Vector<Aws::DynamoDB::Model::AttributeValue> attributes; attributes.push_back(Aws::DynamoDB::Model::AttributeValue().SetS(title)); attributes.push_back(Aws::DynamoDB::Model::AttributeValue().SetN(year)); request.SetParameters(attributes); Aws::DynamoDB::Model::ExecuteStatementOutcome outcome = dynamoClient.ExecuteStatement( request); if (!outcome.IsSuccess()) { std::cerr << "Failed to retrieve movie information: " << outcome.GetError().GetMessage() << std::endl; return false; } else { // Print the retrieved movie information. const Aws::DynamoDB::Model::ExecuteStatementResult &result = outcome.GetResult(); const Aws::Vector<Aws::Map<Aws::String, Aws::DynamoDB::Model::AttributeValue>> &items = result.GetItems(); if (items.size() == 1) { printMovieInfo(items[0]); } else { std::cerr << "Error: " << items.size() << " movies were retrieved. " << " There should be only one movie." << std::endl; } } } // 4. Update the data for the movie using an "Update" statement. (ExecuteStatement) { rating = askQuestionForFloatRange( Aws::String("\nLet's update your movie.\nYou rated it ") + std::to_string(rating) + ", what new rating would you give it? ", 1, 10); Aws::DynamoDB::Model::ExecuteStatementRequest request; std::stringstream sqlStream; sqlStream << "UPDATE \"" << MOVIE_TABLE_NAME << "\" SET " << INFO_KEY << "." << RATING_KEY << "=? WHERE " << TITLE_KEY << "=? AND " << YEAR_KEY << "=?"; request.SetStatement(sqlStream.str()); Aws::Vector<Aws::DynamoDB::Model::AttributeValue> attributes; attributes.push_back(Aws::DynamoDB::Model::AttributeValue().SetN(rating)); attributes.push_back(Aws::DynamoDB::Model::AttributeValue().SetS(title)); attributes.push_back(Aws::DynamoDB::Model::AttributeValue().SetN(year)); request.SetParameters(attributes); Aws::DynamoDB::Model::ExecuteStatementOutcome outcome = dynamoClient.ExecuteStatement( request); if (!outcome.IsSuccess()) { std::cerr << "Failed to update a movie: " << outcome.GetError().GetMessage(); return false; } } std::cout << "\nUpdated '" << title << "' with new attributes:" << std::endl; // 5. Get the updated data for the movie using a "Select" statement. (ExecuteStatement) { Aws::DynamoDB::Model::ExecuteStatementRequest request; std::stringstream sqlStream; sqlStream << "SELECT * FROM \"" << MOVIE_TABLE_NAME << "\" WHERE " << TITLE_KEY << "=? and " << YEAR_KEY << "=?"; request.SetStatement(sqlStream.str()); Aws::Vector<Aws::DynamoDB::Model::AttributeValue> attributes; attributes.push_back(Aws::DynamoDB::Model::AttributeValue().SetS(title)); attributes.push_back(Aws::DynamoDB::Model::AttributeValue().SetN(year)); request.SetParameters(attributes); Aws::DynamoDB::Model::ExecuteStatementOutcome outcome = dynamoClient.ExecuteStatement( request); if (!outcome.IsSuccess()) { std::cerr << "Failed to retrieve the movie information: " << outcome.GetError().GetMessage() << std::endl; return false; } else { const Aws::DynamoDB::Model::ExecuteStatementResult &result = outcome.GetResult(); const Aws::Vector<Aws::Map<Aws::String, Aws::DynamoDB::Model::AttributeValue>> &items = result.GetItems(); if (items.size() == 1) { printMovieInfo(items[0]); } else { std::cerr << "Error: " << items.size() << " movies were retrieved. " << " There should be only one movie." << std::endl; } } } std::cout << "Deleting the movie" << std::endl; // 6. Delete the movie using a "Delete" statement. (ExecuteStatement) { Aws::DynamoDB::Model::ExecuteStatementRequest request; std::stringstream sqlStream; sqlStream << "DELETE FROM \"" << MOVIE_TABLE_NAME << "\" WHERE " << TITLE_KEY << "=? and " << YEAR_KEY << "=?"; request.SetStatement(sqlStream.str()); Aws::Vector<Aws::DynamoDB::Model::AttributeValue> attributes; attributes.push_back(Aws::DynamoDB::Model::AttributeValue().SetS(title)); attributes.push_back(Aws::DynamoDB::Model::AttributeValue().SetN(year)); request.SetParameters(attributes); Aws::DynamoDB::Model::ExecuteStatementOutcome outcome = dynamoClient.ExecuteStatement( request); if (!outcome.IsSuccess()) { std::cerr << "Failed to delete the movie: " << outcome.GetError().GetMessage() << std::endl; return false; } } std::cout << "Movie successfully deleted." << std::endl; 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 clientConfiguration: AWS client configuration. \return bool: Function succeeded. */ bool AwsDoc::DynamoDB::waitTableActive(const Aws::String &tableName, const Aws::Client::ClientConfiguration &clientConfiguration) { Aws::DynamoDB::DynamoDBClient dynamoClient(clientConfiguration); // 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; }
  • 有关 API 的详细信息,请参阅 Amazon SDK for C++ API 参考ExecuteStatement中的。

Go
适用于 Go V2 的 SDK
注意

还有更多相关信息 GitHub。在 Amazon 代码示例存储库中查找完整示例,了解如何进行设置和运行。

运行创建表并运行 PartiQL 查询的场景。

// 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)) }

定义本示例中使用的 Movie 结构。

// 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"]) }

创建运行 PartiQL 语句的结构和方法。

// 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 }
  • 有关 API 的详细信息,请参阅 Amazon SDK for Go API 参考ExecuteStatement中的。

Java
适用于 Java 2.x 的 SDK
注意

还有更多相关信息 GitHub。在 Amazon 代码示例存储库中查找完整示例,了解如何进行设置和运行。

public class ScenarioPartiQ { public static void main(String[] args) throws IOException { final String usage = """ Usage: <fileName> Where: fileName - The path to the moviedata.json file that you can download from the Amazon DynamoDB Developer Guide. """; if (args.length != 1) { System.out.println(usage); System.exit(1); } String fileName = args[0]; String tableName = "MoviesPartiQ"; Region region = Region.US_EAST_1; DynamoDbClient ddb = DynamoDbClient.builder() .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()); } }
  • 有关 API 的详细信息,请参阅 Amazon SDK for Java 2.x API 参考ExecuteStatement中的。

JavaScript
适用于 JavaScript (v3) 的软件开发工具包
注意

还有更多相关信息 GitHub。在 Amazon 代码示例存储库中查找完整示例,了解如何进行设置和运行。

执行单个 PartiQL 语句。

import { BillingMode, CreateTableCommand, DeleteTableCommand, DynamoDBClient, waitUntilTableExists, } from "@aws-sdk/client-dynamodb"; import { DynamoDBDocumentClient, ExecuteStatementCommand, } from "@aws-sdk/lib-dynamodb"; const client = new DynamoDBClient({}); const docClient = DynamoDBDocumentClient.from(client); const log = (msg) => console.log(`[SCENARIO] ${msg}`); const tableName = "SingleOriginCoffees"; export const main = async () => { /** * 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: "varietal", // '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: "varietal", 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 an item. */ log("Inserting a coffee into the table."); const addItemStatementCommand = new ExecuteStatementCommand({ // https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.insert.html Statement: `INSERT INTO ${tableName} value {'varietal':?, 'profile':?}`, Parameters: ["arabica", ["chocolate", "floral"]], }); await client.send(addItemStatementCommand); log(`Coffee inserted.`); /** * Select an item. */ log("Selecting the coffee from the table."); const selectItemStatementCommand = new ExecuteStatementCommand({ // https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.select.html Statement: `SELECT * FROM ${tableName} WHERE varietal=?`, Parameters: ["arabica"], }); const selectItemResponse = await docClient.send(selectItemStatementCommand); log(`Got coffee: ${JSON.stringify(selectItemResponse.Items[0])}`); /** * Update the item. */ log("Add a flavor profile to the coffee."); const updateItemStatementCommand = new ExecuteStatementCommand({ // https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.update.html Statement: `UPDATE ${tableName} SET profile=list_append(profile, ?) WHERE varietal=?`, Parameters: [["fruity"], "arabica"], }); await client.send(updateItemStatementCommand); log(`Updated coffee`); /** * Delete the item. */ log("Deleting the coffee."); const deleteItemStatementCommand = new ExecuteStatementCommand({ // https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.delete.html Statement: `DELETE FROM ${tableName} WHERE varietal=?`, Parameters: ["arabica"], }); await docClient.send(deleteItemStatementCommand); log("Coffee deleted."); /** * Delete the table. */ log("Deleting the table."); const deleteTableCommand = new DeleteTableCommand({ TableName: tableName }); await client.send(deleteTableCommand); log("Table deleted."); };
  • 有关 API 的详细信息,请参阅 Amazon SDK for JavaScript API 参考ExecuteStatement中的。

Kotlin
适用于 Kotlin 的 SDK
注意

还有更多相关信息 GitHub。在 Amazon 代码示例存储库中查找完整示例,了解如何进行设置和运行。

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) }
  • 有关 API 的详细信息,请参阅适用ExecuteStatement于 K otlin 的 Amazon SDK API 参考

PHP
适用于 PHP 的 SDK
注意

还有更多相关信息 GitHub。在 Amazon 代码示例存储库中查找完整示例,了解如何进行设置和运行。

namespace DynamoDb\PartiQL_Basics; use Aws\DynamoDb\Marshaler; use DynamoDb; use DynamoDb\DynamoDBAttribute; use function AwsUtilities\testable_readline; use function AwsUtilities\loadMovieData; class GettingStartedWithPartiQL { 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->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, ]); }
  • 有关 API 的详细信息,请参阅 Amazon SDK for PHP API 参考ExecuteStatement中的。

Python
适用于 Python (Boto3) 的 SDK
注意

还有更多相关信息 GitHub。在 Amazon 代码示例存储库中查找完整示例,了解如何进行设置和运行。

创建一个可以运行 PartiQL 语句的类。

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

运行创建表并运行 PartiQL 查询的场景。

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}")
  • 有关 API 的详细信息,请参阅适用ExecuteStatementPython 的 Amazon SDK (Boto3) API 参考

Ruby
适用于 Ruby 的 SDK
注意

还有更多相关信息 GitHub。在 Amazon 代码示例存储库中查找完整示例,了解如何进行设置和运行。

运行创建表并运行 PartiQL 查询的场景。

table_name = "doc-example-table-movies-partiql-#{rand(10**8)}" scaffold = Scaffold.new(table_name) sdk = DynamoDBPartiQLSingle.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 single item from the movies table.") response = sdk.select_item_by_title("Star Wars") puts("Items selected for title 'Star Wars': #{response.items.length}\n") print "#{response.items.first}".yellow print "\n\nDone!\n".green new_step(4, "Update a single item from the movies table.") puts "Let's correct the rating on The Big Lebowski to 10.0." sdk.update_rating_by_title("The Big Lebowski", 1998, 10.0) print "\nDone!\n".green new_step(5, "Delete a single item from the movies table.") puts "Let's delete The Silence of the Lambs because it's just too scary." sdk.delete_item_by_title("The Silence of the Lambs", 1991) print "\nDone!\n".green new_step(6, "Insert a new item into the movies table.") puts "Let's create a less-scary movie called The Prancing of the Lambs." sdk.insert_item("The Prancing of the Lambs", 2005, "A movie about happy livestock.", 5.0) print "\nDone!\n".green new_step(7, "Delete the table.") if scaffold.exists?(table_name) scaffold.delete_table end end
  • 有关 API 的详细信息,请参阅 Amazon SDK for Ruby API 参考ExecuteStatement中的。

Rust
适用于 Rust 的 SDK
注意

还有更多相关信息 GitHub。在 Amazon 代码示例存储库中查找完整示例,了解如何进行设置和运行。

async fn make_table( client: &Client, table: &str, key: &str, ) -> Result<(), SdkError<CreateTableError>> { let ad = AttributeDefinition::builder() .attribute_name(key) .attribute_type(ScalarAttributeType::S) .build() .expect("creating AttributeDefinition"); let ks = KeySchemaElement::builder() .attribute_name(key) .key_type(KeyType::Hash) .build() .expect("creating KeySchemaElement"); let pt = ProvisionedThroughput::builder() .read_capacity_units(10) .write_capacity_units(5) .build() .expect("creating ProvisionedThroughput"); match client .create_table() .table_name(table) .key_schema(ks) .attribute_definitions(ad) .provisioned_throughput(pt) .send() .await { Ok(_) => Ok(()), Err(e) => Err(e), } } async fn add_item(client: &Client, item: Item) -> Result<(), SdkError<ExecuteStatementError>> { match client .execute_statement() .statement(format!( r#"INSERT INTO "{}" VALUE {{ "{}": ?, "acount_type": ?, "age": ?, "first_name": ?, "last_name": ? }} "#, item.table, item.key )) .set_parameters(Some(vec![ AttributeValue::S(item.utype), AttributeValue::S(item.age), AttributeValue::S(item.first_name), AttributeValue::S(item.last_name), ])) .send() .await { Ok(_) => Ok(()), Err(e) => Err(e), } } async fn query_item(client: &Client, item: Item) -> bool { match client .execute_statement() .statement(format!( r#"SELECT * FROM "{}" WHERE "{}" = ?"#, item.table, item.key )) .set_parameters(Some(vec![AttributeValue::S(item.value)])) .send() .await { Ok(resp) => { if !resp.items().is_empty() { println!("Found a matching entry in the table:"); println!("{:?}", resp.items.unwrap_or_default().pop()); true } else { println!("Did not find a match."); false } } Err(e) => { println!("Got an error querying table:"); println!("{}", e); process::exit(1); } } } async fn remove_item(client: &Client, table: &str, key: &str, value: String) -> Result<(), Error> { client .execute_statement() .statement(format!(r#"DELETE FROM "{table}" WHERE "{key}" = ?"#)) .set_parameters(Some(vec![AttributeValue::S(value)])) .send() .await?; println!("Deleted item."); Ok(()) } async fn remove_table(client: &Client, table: &str) -> Result<(), Error> { client.delete_table().table_name(table).send().await?; Ok(()) }
  • 有关 API 的详细信息,请参阅适用ExecuteStatementRust 的 Amazon SDK API 参考

有关 S Amazon DK 开发者指南和代码示例的完整列表,请参阅结合使用 DynamoDB 与 Amazon SDK。本主题还包括有关入门的信息以及有关先前的 SDK 版本的详细信息。