azure db 设置时区
In this article, we will discuss why we need to use Azure Cosmos DB and how to configure it to store and query our data.
在本文中,我们将讨论为什么需要使用Azure Cosmos DB以及如何配置它以存储和查询我们的数据。
Before that, let us differentiate between the relational and non-relational database engines.
在此之前,让我们区分关系数据库引擎和非关系数据库引擎。
关系与非关系 (Relational vs Non- relational)
When working in the database world, you need to distinguish between the relational and non-relational database engines.
在数据库世界中工作时,您需要区分关系数据库引擎和非关系数据库引擎。
In relational database engines, such as Microsoft SQL Server, MySQL, PostgreSQL, and SQLite3, the data will be stored in tables in the shape of columns and rows, with the ability to link data from different tables using foreign key constraints. Besides, the schema of the relational database tables, which defines the name of the columns, the type of data that can be stored in these columns and the constraints on the table columns, should be defined.
在关系数据库引擎(例如Microsoft SQL Server,MySQL,PostgreSQL和SQLite3)中,数据将以列和行的形式存储在表中,并能够使用外键约束链接来自不同表的数据。 此外,应该定义关系数据库表的架构,该架构定义列的名称,可以存储在这些列中的数据的类型以及对表列的约束。
In the non-relational database engines, also called NoSQL database engines, such as MongoDB, DocumentDB, Cassandra, Coachbase and HBase, the data will NOT be stored in tables with defined schemas. Instead, the data will be stored in an optimized storage model, such as Key-value stores, Graph stores, stores, and Document stores.
在非关系数据库引擎(也称为NoSQL数据库引擎)(例如MongoDB,DocumentDB,Cassandra,Coachbase和HBase)中,数据不会存储在具有已定义架构的表中。 而是将数据存储在优化的存储模型中,例如键值存储,图存储,存储和文档存储。
为什么选择Azure Cosmos DB? (Why Azure Cosmos DB?)
Azure Cosmos DB is a Microsoft Azure database service, fastest-growing Azure service that is available in all Azure regions, that fits any web application, mobile application, gaming or IoT application that requires processing, reading and writing a massive amount of data.
Azure Cosmos DB是Microsoft Azure数据库服务,是增长最快的Azure服务,可在所有Azure区域中使用,适用于需要处理,读取和写入大量数据的任何Web应用程序,移动应用程序,游戏或IoT应用程序。
Azure Cosmos DB provides the applications with:
Azure Cosmos DB为应用程序提供:
- least possible low latency, less than 10-ms latencies for both reads and writes workloads due to using SSD backed storage and the multi-master replication for your data wherever your users are, allowing the users to connect to the closest replica to them 尽可能低,延迟小于10毫秒,从而使用户可以连接到与其最近的副本
- highest possible availability, up to 99.999% high availability of the data stored in the Cosmos DB due to the ability to perform regional failover for your Cosmos DB account databases 可以实现最高的可用性 ,Cosmos DB中存储的数据的高可用性高达99.999%
- highest possible throughput 最高的吞吐量
- Variant consistency options, with the five well-defined consistency options, include strong, bounded staleness, session, consistent prefix, and eventual that provides full flexibility and low cost-to-performance ratio 变体一致性选项以及五个定义良好的一致性选项,包括强壮的,有界的陈旧性,会话,一致的前缀以及最终提供完全灵活性和低性价比的最终选择
- Advanced security options, where the Cosmos DB data will be encrypted at rest and in motion, in addition to the row-level authorization 进阶安全性选项 ,除了行级授权外,Cosmos DB数据还将在静态和动态状态下进行加密
- Elastically and globally scalable for the read and write operations, that helps in dealing with unexpected workload spikes, with the ability to modify the current structure anytime by adding or remove any of the Azure regions to your Cosmos account 弹性和全局可扩展的读写操作,有助于应对意外的工作负载,并可以通过向您的Cosmos帐户添加或删除任何Azure区域随时修改当前结构
- multiple APIs to work with your data that is stored in the Cosmos database, such as the SQL Core API, Cassandra, MongoDB, Gremlin and Azure Table Storage, providing you with an easy way to migrate the application to Cosmos DB without the need to perform significant changes. It requires no schema or index management is, as all data will be automatically indexed, and requires no complex multi-datacenter management or database software deployments or upgrade 多种API来处理存储在Cosmos数据库中的数据,例如SQL Core API,Cassandra,MongoDB,Gremlin和Azure Table Storage,为您提供了一种简便的方法,无需将应用程序迁移到Cosmos DB执行重大更改。 它不需要架构或索引管理,因为所有数据都将自动建立索引,并且不需要复杂的多数据中心管理或数据库软件部署或升级
For now, we are familiar with the Azure Cosmos DB concept and why we need to use it to host the data used by the critical applications. Let us now see how to create a new database account.
目前,我们熟悉Azure Cosmos DB概念以及为什么我们需要使用它来承载关键应用程序使用的数据。 现在让我们看看如何创建一个新的数据库帐户。
创建一个Azure Cosmos数据库帐户 (Create an Azure Cosmos DB Account)
In order to create an Azure Cosmos DB account, you need to have an Azure subscription or create a free Azure Cosmos DB trial account.
为了创建Azure Cosmos DB帐户,您需要具有Azure订阅或创建免费的Azure Cosmos DB试用帐户。
When your subscription or free account is ready, open the Azure portal site, https://portal.azure.com/, in your Internet browser and login using the subscription account.
准备好订阅或免费帐户后, 请在Internet浏览器中打开Azure门户网站https://portal.azure.com/并使用订阅帐户登录。
From the Microsoft Azure portal, search for the Azure Cosmos DB server, or click on the icon directly if you can detect it, as below:
在Microsoft Azure门户中,搜索Azure Cosmos DB服务器,或者,如果可以检测到它,则直接单击该图标,如下所示:
In the opened page, click on the Create Azure Cosmos DB account option, to create a new Cosmos DB account, as shown below:
在打开的页面中,单击“ 创建Azure Cosmos DB帐户”选项,以创建一个新的Cosmos DB帐户,如下所示:
From the Create Azure Cosmos DB Account page, select the Azure subscription under which you plan to create the Cosmos DB account and the resource group, the container of resources for that Azure service that is used to manage all resources, that will be used for the Cosmos DB account. You can use an existing resource group or create a new resource group, as shown below:
在“创建Azure Cosmos DB帐户”页面中,选择计划在其下创建Cosmos DB帐户和资源组的Azure订阅,该资源组是用于管理所有资源的Azure服务的资源容器,该资源将用于Cosmos DB帐户。 您可以使用现有资源组或创建新资源组,如下所示:
After that, you will be requested to provide all details for the Cosmos DB instance that will be created include a unique Account name that will be used to identify the Azure Cosmos account. You can use only lowercase letters, numbers, and the hyphen character to in the account name.
此后,将要求您提供将要创建的Cosmos数据库实例的所有详细信息,包括用于标识Azure Cosmos帐户的唯一帐户名 。 您只能在帐户名称中使用小写字母,数字和连字符。
The API is the type of database account that will be created. You can choose from the previously mentioned five API supported by the Cosmos DB. In this demo, we will use the Core (SQL) API.
API是将创建的数据库帐户的类型。 您可以从前面提到的Cosmos DB支持的五个API中进行选择。 在此演示中,我们将使用Core(SQL)API。
You need also to provide the Location, which describes the nearest datacenter to your current location in order to provide the fastest access to the data.
您还需要提供Location ,它描述离您当前位置最近的数据中心,以便提供对数据的最快访问。
You can also enable the Multi-region write option to replicate your data to multiple selected regions. For now, we can create it in one region and add more regions later.
您还可以启用“ 多区域写入”选项,以将数据复制到多个选定区域。 现在,我们可以在一个区域中创建它,以后再添加更多区域。
After providing your selections click on Review + Create option as below:
提供选择后,单击“ 查看+创建”选项,如下所示:
In the Review + Create page, review all the selections then click on the Create option to create your Azure Cosmos DB account, as shown below:
在“审阅+创建”页面中,审阅所有选择,然后单击“ 创建”选项以创建您的Azure Cosmos DB帐户,如下所示:
After a few minutes, the database account will be created and deployed completely. Click on the Go to resource option to start working in the database account, as shown below:
几分钟后,将完全创建并部署数据库帐户。 单击转到资源选项以开始使用数据库帐户,如下所示:
The first opened page is the Quickstart page that allows you to select from the available platforms and the next step to proceed with the Azure Cosmos DB account. Click on the Overview page from the below:
打开的第一个页面是“快速入门”页面,该页面使您可以从可用平台中进行选择,而下一步则可以继续使用Azure Cosmos DB帐户。 从下面单击概述页面:
The Overview page shows the general information about the database account, the list of containers available under the database account and finally the monitoring dashboard for the Azure Cosmos DB account.
“概述”页面显示有关数据库帐户的常规信息,该数据库帐户下可用的容器列表,最后显示Azure Cosmos DB帐户的监视仪表板。
You can see the URL that is used to connect to the database account will be like https://aycosmos.documents.azure.com:443/
您可以看到用于连接到数据库帐户的URL类似于https:// aycosmos .documents.azure.com:443 /
As it is clear below:
如下所示:
If you enable the Multi-region option, you can easily move to the Replicate data globally page and select the regions that you plan to replicate your data to, in order to provide faster access to the data to your global customers, as below:
如果启用了“多区域”选项,则可以轻松地移至“ 全局复制数据”页面,并选择计划将数据复制到的区域,以便为全球客户提供对数据的更快访问,如下所示:
创建新容器 (Create New Container)
Now the Azure Cosmos DB account is ready, and we can create a database under that account and add data containers.
现在,Azure Cosmos DB帐户已准备就绪,我们可以在该帐户下创建数据库并添加数据容器。
To achieve that, move to the Data Explorer page and click on the New Container option.
为此,请转到“ 数据资源管理器”页面,然后单击“ 新建容器”选项。
In the Add Container page, provide the name of the database under which the container will be created, with the ability to create more than one container per database, or select an existing database if available, with the ability to share the throughput provisioned to the database across all the containers within the database, to save the cost.
在“添加容器”页面中,提供将在其下创建容器的数据库的名称,每个数据库可以创建一个以上的容器,或者选择一个现有数据库(如果可用),并能够共享提供给服务器的吞吐量。跨数据库中所有容器的数据库,以节省成本。
In this demo, we will keep the throughput value at 400 request units per second (RU/s).
在此演示中,我们将吞吐量值保持在每秒400个请求单位(RU / s)。
Also, provide a unique name for that container, whether to automatically index all data to gain the best data access performance and the partition key that will be used for dividing the data into logical partitions within the same container, then click OK to create the database and the container as shown below:
另外,为该容器提供唯一的名称,是否自动索引所有数据以获得最佳的数据访问性能,以及将用于将数据分为同一容器内的逻辑分区的分区键,然后单击“ 确定”以创建数据库和容器如下图所示:
新增项目 (Add New Item)
To add a new data item to your container, move to the Data Explorer page, expand the database and browse for the container under which you want to add the data.
要将新数据项添加到容器中,请移至“数据资源管理器”页面,展开数据库并浏览要在其下添加数据的容器。
Under your container, click on Items then choose New Item option to add a new employee data. After adding the EmpID, EmpName, EmpDep and IsActive columns values, click on Save, where new six metadata fields will be added to the item, as shown below:
在您的容器下,单击项目,然后选择新建项目选项以添加新的员工数据。 添加EmpID,EmpName,EmpDep和IsActive列值后,单击Save ,新的六个元数据字段将被添加到该项,如下所示:
In this demo, we will repeat this process three times to add new items.
在此演示中,我们将重复此过程3次以添加新项目。
查询数据 (Query Data)
To query the inserted data, you can easily click on the Edit Filter option in the Items page, where you can edit the existing query by adding a filter or order the data in a specific order. In this example, we will sort the items based on the employee ID, as shown below:
要查询插入的数据,您可以轻松地单击“项目”页面中的“ 编辑过滤器”选项,您可以在其中通过添加过滤器或按特定顺序对数据进行排序来编辑现有查询。 在此示例中,我们将根据员工ID对项目进行排序,如下所示:
A more flexible option is to open a query window and write your SQL query to select specific columns and retrieve the items that meet the provided condition.
更为灵活的选项是打开查询窗口并编写SQL查询以选择特定的列并检索满足所提供条件的项目。
In the below example, we will use the SQL query below to retrieve the name and department for the active employees. After writing the query, click on Execute Query button, and the result will be displayed, as shown below:
在下面的示例中,我们将使用下面SQL查询来检索在职员工的姓名和部门。 编写查询后,单击“ 执行查询”按钮,将显示结果,如下所示:
You can use your SQL development skills to write more complex queries, such as selecting specific columns, provide a condition to filter the data in WHERE clause and sort the data using the ORDER BY clause, as shown below:
您可以使用您SQL开发技能来编写更复杂的查询,例如选择特定的列,提供条件以过滤WHERE子句中的数据并使用ORDER BY子句对数据进行排序,如下所示:
Besides the result of the executed query, you can review the execution performance statistics of that query, such as the number of RU consumed, storage and time, that helps in comparing the different queries and enhance the queries performance, as shown below:
除了执行查询的结果外,您还可以查看该查询的执行性能统计信息,例如消耗的RU数量,存储和时间,这有助于比较不同的查询并提高查询性能,如下所示:
结论 (Conclusion)
It is clear from the previous examples how easy we can configure and use the Azure Cosmos DB to store and query the data and take advantage of the great performance provided by it in processing and storing your data.
从前面的示例可以清楚地看出,我们可以轻松地配置和使用Azure Cosmos DB来存储和查询数据,并利用它提供的出色性能来处理和存储数据。
翻译自: https://www.sqlshack.com/start-your-journey-with-azure-cosmos-db/
azure db 设置时区