在Azure Cosmos DB中更新和查询详细信息

最新推荐文章于 2021-03-14 20:04:12 发布

culuo4781

最新推荐文章于 2021-03-14 20:04:12 发布

阅读量633

点赞数 1

文章标签：数据库大数据 python mysql java

原文链接：https://www.sqlshack.com/updating-and-querying-details-in-azure-cosmos-db/

版权

In the first part of this series Getting Started with Azure Cosmos DB, we looked at using Azure Cosmos DB for saving an individual’s fitness routine and why this database structure is better for this data than a SQL database while also showing that we still have to organize our structure like a file system organizes files. In this part of our series, we’ll begin looking at the terminology translation between NoSQL and SQL along with running updates for our documents and queries with filters that return some fields in our document, but not other fields.

在本系列的第一部分“ Azure Cosmos DB入门”中，我们研究了如何使用Azure Cosmos DB保存个人的适应性例程，以及为什么该数据库结构比SQL数据库更适合此数据，同时还说明了我们仍然必须组织我们的结构就像文件系统一样组织文件。在本系列的这一部分中，我们将开始研究NoSQL和SQL之间的术语转换，以及运行文档的更新和具有返回文档中某些字段但不返回其他字段的过滤器的查询。

术语 (Terminology)

In SQL Server, we have a data structure that involves the server, database, table, rows and columns of data along with other database objects, such as stored procedures, views, functions, etc. With Azure Cosmos DB (and MongoDB), we have similar terminology, though an individual’s document structure may use a format that could be another document within it itself. For a helpful understanding of translating between SQL and NoSQL in most cases, we use the below with NoSQL databases:

在SQL Server中，我们具有一个数据结构，其中涉及服务器，数据库，表，数据的行和列以及其他数据库对象，例如存储过程，视图，函数等。使用Azure Cosmos DB（和MongoDB），我们可以具有相似的术语，尽管个人的文档结构可能使用的格式可能是其自身内的另一个文档。为了在大多数情况下对在SQL和NoSQL之间进行转换有帮助的理解，我们将以下内容与NoSQL数据库一起使用：

Collection: similar to a table 收藏：类似于桌子
Document: similar to a row of data 文档：类似于一行数据
Field: similar to a column 现场：类似于列
Document objects: from arrays to subdocuments within a document, these can be similar to documents, table-like structures, etc. 文档对象 ：从数组到文档中的子文档，它们可以类似于文档，类似表格的结构等。

Because documents can store data in many different formats, these terms help us translate what we’re referring to, but are not exact duplicate terms of their SQL counterparts, since NoSQL and SQL fundamentally differ.

由于文档可以以许多不同的格式存储数据，因此这些术语可以帮助我们翻译所指的内容，但它们并不是与它们对应SQL术语的完全重复的术语，因为NoSQL和SQL根本不同。

更新我们的文件 (Updating Our Documents)

We created five documents in our Azure Cosmos DB and we’ll first start by updating these documents with numerical values for measurements so that we can compare them when we add data. We’ll start by querying our first document by using our unique identifier of the date and time of our routine (datetimeRoutine) in the Shell that we’ll want to update.

我们在Azure Cosmos数据库中创建了五个文档，首先我们将使用用于测量的数值更新这些文档开始，以便在添加数据时可以对它们进行比较。首先，我们将使用我们要更新的Shell中例程的日期和时间的唯一标识符（datetimeRoutine）查询第一个文档。

We return the specific document using our unique identifier involving time.

我们使用涉及时间的唯一标识符返回特定文档。

Next, we’ll run our update statement. We first specify the unique identifier of our document – in this case the date and time of the routine – then in the next set of brackets, we update the existing document fields. We only include the fields that we’re updating (in this example 2 fields). If we were to change the name of our fields, these would become additions to our document, not updates to our document. Additions would be another field – it wouldn’t update an existing field. If we want to add another field to a document, we can do so as we see with the field notes that we add to our first document in this Azure Cosmos DB collection.

接下来，我们将运行更新语句。我们首先指定文档的唯一标识符（在本例中为例程的日期和时间），然后在下一组括号中，更新现有文档字段。我们仅包括要更新的字段（在本示例中为2个字段）。如果我们要更改字段的名称，这些将成为我们文档的补充，而不是我们文档的更新。添加将是另一个字段-它不会更新现有字段。如果我们想向文档中添加另一个字段，可以按照添加到此Azure Cosmos DB集合中的第一个文档中的字段注释进行操作。

Similar to a technique we use in SQL Server, with Azure Cosmos DB, we can update a document by first removing the document and adding the document again (notice how the _id field will change – which is automatically created since this is a MongoDB API). This technique becomes incredibly useful when we have a development situation where we work with full documents in an object-level that may have many fields change and we want to simply remove and add the document. Context around performance matters: in some situations, we want to update the document, while in other situations, removing and adding a new document outperforms an update.

与我们在SQL Server中使用Azure Cosmos DB的技术类似，我们可以通过先删除文档并再次添加文档来更新文档（请注意_id字段将如何更改–由于这是MongoDB API，因此会自动创建）。当我们在开发环境中使用对象级别的完整文档（可能会更改许多字段）并且我们想要简单地删除和添加文档时，此技术将变得非常有用。与性能有关的上下文很重要：在某些情况下，我们要更新文档，而在其他情况下，删除和添加新文档的性能要优于更新。

We can remove a document and re-add it, which is a useful development technique when we have many changes rather than performing an update.

我们可以删除文档并重新添加它，当我们进行许多更改而不是执行更新时，这是一种有用的开发技术。

We’ve updated the document by removing and re-adding and we see that the _id field differs.

我们通过删除和重新添加来更新了文档，我们发现_id字段有所不同。

We can also select the document within the collection itself through the interface and update the fields and information directly, which may be useful if we’re using the Azure Cosmos DB as a configuration database or for dynamic meta information related to apps or processes and we have few documents. In the below images, I first select a document that I want to update (datetimeRoutine of 2017-01-04 4:00AM), update the fields, then click update.

我们还可以通过界面在集合本身中选择文档，并直接更新字段和信息，如果我们将Azure Cosmos DB用作配置数据库或用于与应用程序或流程相关的动态元信息，这可能很有用。文件很少。在下面的图像中，我首先选择要更新的文档（datetimeRoutine，2017年1月4日4:00 AM），更新字段，然后单击“更新”。

First we select the document we want to update

首先，我们选择要更新的文档

Second, we make our updates in the document then click “Update.”

其次，我们在文档中进行更新，然后单击“更新”。

Our final document to update is datetimeRoutine of 2017-01-07 4:00AM and in this case, we won’t add a notes field because our first Treadmill Run value had notes explaining how this is formatted. We have this flexibility with NoSQL databases like Azure Cosmos DB where a first value of a similar document has an explanation – and this is entirely optional for our database. We may choose to have no explanation in any document, but since documents aren’t required to have the exact same number of fields, we have this choice.

我们要更新的最终文档是datetimeRoutine，时间为2017-01-07 4:00 AM，在这种情况下，我们不会添加注释字段，因为我们的第一个“跑步机运行”值包含注释，说明了其格式。对于NoSQL数据库（例如Azure Cosmos DB），我们具有这种灵活性，其中类似文档的第一个值有解释-这对于我们的数据库完全是可选的。我们可能选择在任何文档中都没有解释，但是由于不需要文档具有完全相同的字段数，因此我们有此选择。

Our final update.

我们的最终更新。

查询相关文件 (Querying Related Documents)

Before we begin querying related documents in our Azure Cosmos DB, we’ll add five more documents which follow similar structure to the documents that we updated – we can add these directly with the “New Document” option or through the Shell (I’ve formatted it for the Shell, but either will add the document):

在我们开始在Azure Cosmos DB中查询相关文档之前，我们将再添加五个与更新后的文档具有相似结构的文档-我们可以直接使用“新建文档”选项或通过命令行管理程序添加这些文档（我已经将其格式化为Shell格式，但两者都会添加文档）：

db.Routines.insert({"datetimeRoutine" : "2017-01-09 4:00AM", "type" : "Jump Rope/Pushup Circuit", "Jump Rope" : "20", "Pushups" : 290})
  db.Routines.insert({"datetimeRoutine" : "2017-01-12 4:00AM", "type" : "Jump Rope/Pushup Circuit", "Jump Rope" : "21", "Pushups" : 300})
  db.Routines.insert({"datetimeRoutine" : "2017-01-10 4:00AM", "type" : "Treadmill Run", "distance" : "3.1", "time" : "30"})
  db.Routines.insert({"datetimeRoutine" : "2017-01-13 4:00AM", "type" : "Treadmill Run", "distance" : "3.25", "time" : "30"})
  db.Routines.insert({"datetimeRoutine" : "2017-01-11 4:00AM", "type" : "Endurance", "Pushup AMRAP" : 1050})

When we’re done adding the five documents, after refreshing the collection, we’ll see the total of ten documents – the _id field will differ in your Azure Cosmos DB, since this field is automatically generated:

添加完五个文档后，刷新集合后，我们将看到总共十个文档– Azure Cosmos DB中的_id字段将有所不同，因为该字段是自动生成的：

We now have 10 documents in our collection (your _id values will differ).

现在，我们的集合中有10个文档（您的_id值将有所不同）。

Next, we’ll query our database for our Treadmill Runs and we’ll notice that in our output we get a lot of fields that we may not want – such as the _id field and the type field (since it’s already being filtered for in this query). Just like with selecting columns in SQL Server, we may only want to select some fields from a collection. For using documents from an Azure Cosmos DB, if we use documents to have data values extracted into a SQL report or another BI report, we will more than likely want some fields of a document, not the entire document.

接下来，我们将在数据库中查询跑步机的运行次数，我们会注意到在输出中，我们得到了很多我们可能不想要的字段，例如_id字段和type字段（因为已经在其中过滤了该字段）此查询）。就像在SQL Server中选择列一样，我们可能只想从集合中选择某些字段。对于使用来自Azure Cosmos数据库的文档，如果我们使用文档将数据值提取到SQL报表或另一个BI报表中，则我们很可能需要文档的某些字段，而不是整个文档。

While we get the correct result, we may not want every field in these documents.

虽然我们得到正确的结果，但我们可能不希望这些文档中的每个字段。

We’ll specify which columns that we want returned in our query and since our query already is filtering on an exercise (“Treadmill Run”), we’ll leave out this field. What we want is the date, distance and time of the exercise. For this, we’ll specify the fields in our query that we want with the selection of 1 – meaning that we want the field to return (0 would be the opposite, do not return). For developers familiar with MongoDB, this syntax is identical and is part of what makes Azure Cosmos DB running the MongoDB API easy to use.

我们将指定要在查询中返回的列，并且由于我们的查询已经在练习中进行了过滤（“跑步机运行”），因此将省略此字段。我们想要的是锻炼的日期，距离和时间。为此，我们将选择1来指定查询中需要的字段-这意味着我们希望该字段返回（0表示相反，不返回）。对于熟悉MongoDB的开发人员，此语法是相同的，并且是使运行MongoDB API的Azure Cosmos DB易于使用的一部分。

We still see the _id field, even though we didn’t specify it.

即使未指定_id字段，我们仍然会看到它。

The documents return and we see that they all have the _id field that’s automatically generated on each document. The reason for this is because the _id field is default and we can remove it by specifying it with the option of 0:

文档返回，我们看到它们都有在每个文档上自动生成的_id字段。原因是因为_id字段是默认字段，我们可以通过将其指定为0来删除它：

The _id field is now removed with our specifications of 0.

_id字段现在已被我们的规范0删除。

The reason I mentioned we can specify 0 is because we can write this same query in a different way – setting the two fields we don’t want to 0 since there are fewer fields that we don’t want than what we want – and notice that we get the same results and our query was faster to write:

我提到我们可以指定0的原因是因为我们可以用不同的方式编写同一查询-将我们不希望的两个字段设置为0，因为我们不想要的字段比我们想要的要少-并且注意我们得到相同的结果，并且查询的编写速度更快：

We return the same data by specifying the columns we don’t want.

我们通过指定不需要的列来返回相同的数据。

Imagine that we had 100 fields and we wanted 97 of them – being able to specify returning all fields except 3 in this case would save us a lot of time. Sometimes we work with data sets like this – we have fields for order and querying, but we don’t want those particular fields returning because they are unnecessary, like the field “type” in the above example – we’re already filtering by it, so we don’t need it to return.

想象一下，我们有100个字段，而我们想要其中的97个–在这种情况下，能够指定返回除3个字段之外的所有其他字段将为我们节省大量时间。有时我们使用这样的数据集-我们有用于订单和查询的字段，但我们不希望这些特殊字段返回，因为它们是不必要的，例如上例中的“ type”字段-我们已经对其进行了过滤，因此我们不需要它返回。

摘要 (Summary)

As we see with Azure Cosmos DB, it’s helpful to understand the terminology such as collections, documents and fields and see how they have similar patterns to what we find in SQL with tables, rows and columns. We also saw how to run updates on our documents and like with SQL Server, we always want to verify that a CRUD operation such as update processed correctly. Finally, we looked at querying our data by returning some fields, but not other fields. We will use data sets where we want everything, except for a few fields, and we see that we can conveniently run these types of queries to save development time.

正如我们在Azure Cosmos DB中看到的那样，有助于理解诸如集合，文档和字段之类的术语，并了解它们与SQL在表，行和列中所具有的相似模式。我们还看到了如何在文档上运行更新，并且像使用SQL Server一样，我们始终希望验证CRUD操作（例如更新）是否正确处理。最后，我们着眼于通过返回某些字段（而不是其他字段）来查询数据。我们将在需要所有数据的地方使用数据集，除了几个字段，而且我们可以方便地运行这些类型的查询以节省开发时间。