大数据分析培训课程PostgreSQL内部存储原理

　　PostgreSQL内部存储原理是什么？在以前的文章中，我们已经描述了Postgres数据库以及使用Python与之交互的方式。这些帖子提供了基础知识，但是如果您想在生产系统中使用数据库，则必须知道如何使查询更快，更高效。要了解效率在Postgres中意味着什么，重要的是要了解Postgres的工作原理。在大数据分析培训课程PostgreSQL内部存储原理中，我们将重点介绍Postgres和关系数据库的更高级概念。首先，我们将学习Postgres如何存储自己的内部数据以描述，调试和识别系统中的瓶颈。然后，我们将利用对Postgres内部数据的了解，使用Python构建我们自己的数据库描述工具版本。像我们以前的文章一样，我们将使用以下工具：
 

　　1)Postgres的本地版本(v9.2或更高版本)

　　2)Python 3

　　3)Postgres的Python驱动程序， psycopg2
 

　　我们将使用的数据集来自美国住房和城市发展部(也称为HUD)。我们已经将文件打包到一个zip文件中，该文件包含CSV格式的数据，还有一个Python 3脚本(load_hud_tables.py)，它将CSV文件复制到本地运行的Postgres中。如果您运行的是没有默认连接的Postgres服务器，则需要更新脚本中的连接字符串。使用HUD的数据集，我们将使用每个Postgres引擎中的可用命令来处理真实数据示例。从空白开始，我们将研究表及其数据类型。然后，我们将使用内部Postgres表探索HUD 表为我们提供有关数据库内容的详细说明。首先，下载并解压缩dq_postgres_internals.zip 文件。进入dq_postgres_internals/目录，在中更改连接参数load_hud_tables.py，然后运行脚本。这会将CSV文件加载到您的本地Postgres实例中。将文件加载到Postgres服务器后，我们可以通过连接数据库开始。如果本地Postgres实例与默认实例不同，请更改连接值。在整个这篇文章中，以后对该对象的任何引用cur将是以下连接的游标：

 

　　调查表
 

　　现在我们已经建立了连接，是时候开始探索我们可以使用的表了。首先，我们将检查Postgres内部表，这些表向我们提供了有关数据库的详细信息。让我们从描述这些内部表开始。在每个Postgres引擎中，都有一组内部表，Postgres使用这些内部表来管理其整个结构。这些表作为组information_schema和系统目录位于Postgres文档中。这些包含有关存储在Postgres数据库中的数据，表名和类型的所有信息。例如，当我们使用属性时，cur.description，它将从内部表中提取信息以显示给用户。不幸的是，没有数据集的详细架构。因此，我们需要为包含的内容创建自己的详细描述。让我们使用中的内部表information_schema来获得有关数据库中存储哪些表的高级概述。内部表称为information_schema.tables，从文档中我们可以看到有很多列可供选择：

　　此时，我们只关心数据库中表的名称。看一下上面的表描述，有一列table_name公开了此信息。让我们查询该列，看看我们正在处理什么。

　　使用模式
 

　　当您运行前面的命令时，您会注意到输出中包含大量的表。这些表中的每个表都可以在postgres数据库中找到。问题是：我们在结果中包括内部表。在输出中，您会注意到许多表都是以prefix开头的pg_*。这些表中的每一个都是pg_catalog内部表组的一部分。这些是我们前面介绍的系统目录表。然后，您可能已经猜到了，该information_schema名称下还有一组其他表。但是，这些表很难找到，因为它们没有明显的前缀模式。我们如何知道哪些表是内部的，哪些表是用户创建的?我们需要描述模式解释以上问题。在处理关系数据库时，模式一词被概括为具有多种含义的术语。您可能听说过架构被用来描述表(它们的数据类型，名称，列等)或架构作为数据库的蓝图。模式的含糊含义只会使我们感到困惑。但是，对于Postgres，术语“架构”已保留用于特定目的。在Postgres中，模式用作表的命名空间，其独特目的是将它们分成单个数据库内的隔离组或集合。

　　让我们进一步分解。Postgres使用数据库的概念在Postgres服务器中分离用户和数据。创建数据库时，您正在创建一个隔离的环境，用户可以在其中查询只能在该特定数据库中找到的表。这是一个示例：假设国土安全部(DHS)和HUD共享相同的政府Postgres数据库，但他们希望将其用户和数据分开。然后，他们将使用数据库通过每个代理商的单独数据库将他们的数据和用户分开。现在，当用户想要连接到他们的数据时，他们需要指定将要连接到那里的数据库，并且只有在那里，他们才能使用他们的表。但是，假设有些分析师想对公民数据(citizens表)和城市住房发展(developments表)进行横断面分析。好吧，那么他们将想同时查询dhs数据库中的表和hud数据库。但是，对于Postgres，这是不可能的。

　　如果我们想将表分成不同的组，但仍然允许跨表查询怎么办?这是架构的完美用例。代替数据库，对每个代理机构使用不同的架构将使用名称空间分隔表，但仍允许分析人员查询两个表。

　　这就是Postgres划分其内部表(以及用户创建的表!)的方式。创建数据库时，实例化了3种模式：(pg_catalog对于系统目录表)，information_schema(对于信息模式表)和public(对于用户创建的表的默认模式)。每次您CREATE TABLE在数据库中发出命令时，默认情况下Postgres都会将该表分配给public模式。现在，回到以前的问题，如何将用户创建的表与内部表分开?information_schema.tables再次查看各列。现在，检查是否存在可将用户创建的表与内部表分开的列。

　　有一个名为的列table_schema将符合我们的要求。我们可以过滤此列以选择所有公共表。这是我们编写查询的方法：

　　描述表格
 

　　从输出中，我们将只使用三个表。这些是：

　　使用表名，我们可以调用cur.description属性以详细查看每个表的列，类型和任何其他元信息。在此之前，我们了解到可以发出SELECT查询，然后调用description属性以获取表信息。但是，如果我们想在for每个表的循环中执行该操作怎么办?希望您的第一个想法是不要使用.format()。在之前的文章中，我们已经提到了围绕字符串内插的问题.format()。答案是使用方法或该方法中的第二个位置参数对字符串进行Mogirfy处理。不幸的是，事情并不是那么容易。尝试插入表名–使用mogrify()execute()mogirfy()–导致错误，而不是列名，过滤键或分组键。这是此错误的示例：
 

　　从代码片段中，您可能已经注意到，mogrify()在表名上使用会将该名称"state_info"转换为Postgres字符串。这对于列名或过滤器查询是必需的，但对于表名则不是必需的。相反，您必须使用psycopg2.extensions名为AsIs的模块中的类。

　　SELECT查询中的表名不需要用字符串引起来。因此，AsIs将其保留为带引号的有效SQL表示形式，而不是对其进行转换。使用AsIs，我们可以检查每个表的描述，而不必写下每个请求!

　　类型代码映射
 

　　在打印完每个描述之后，我们现在将详细研究将要使用的表。这是homeless_by_coc描述的输出的片段：

　　了解该description属性后，您应该对可用的元数据感到满意。但是，我们再次面对整数type_code而不是人类可读的类型。有太多的心理开销记得什么时候人类可读的类型，他们代表(即TEXT，INTEGER或BOOLEAN)。您可以使用psycopg2类型值来查找每一列的近似类型，但是我们可以做得比近似那些值更好。使用内部表，我们可以准确地映射HUD表中每一列的类型。我们将使用的内部表来自系统目录架构，pg_catalog并被正确命名pg_type。我们建议您检查文档中的表说明，因为本节中要添加的行太多。您可以在此处找到表格说明。在此表中，有很多已定义的列–您无需担心许多列。但是，关于此表需要注意的一件事是它可用于从头开始创建自己的Postgres类型。例如，使用此表，您可以创建一个HEX只能用于在列中存储十六进制字符的类型。让我们循环遍历返回的SELECT查询，并将整数类型代码映射到字符串。它看起来应该类似于以下内容：

　　使用字典理解，我们可以编写以下内容：

　　可读的描述类型
 

　　现在，我们将所有类型代码映射到其类型名称。使用type_mappings字典可以提供类型，而无需在文档中查找它们。让我们将所有这些放在一起并创建我们自己的表描述。我们想description从元组列表中将属性重写为易于阅读的属性。我们将把先前练习的输出组装到该字典中：

　　使用type_mappings和table_names将为我们提供所需结果的步骤是：

　　1)table_names使用table变量 循环遍历。

　　2)获取description给定的属性table。

　　3)table使用columns键 将的名称映射到字典。

　　4)columns通过遍历description并映射适当的类型，从屏幕示例重新创建列表。
 

　　行数
 

　　事情开始融合在一起。现在，为了完成我们的调查，我们想提供有关表中行的其他信息。让我们用表中的行数提供描述。我们可以使用COUNT()聚合函数找到行数。这与SQLite的aggreggate函数以及其他SQL语法实现极为相似。如果您想了解有关Postgres聚合函数的更多信息，它们都在pg_catalog.pg_aggregate内部表中定义。提醒一下，这是COUNT()在Postgres中使用该功能的方式：

　　我们希望描述表如下所示：

　　而不是遍历table_names列表，我们将遍历readable_description字典键：

　　样本行
 

　　最后，让我们在readable_description字典中添加一些示例行。由于该homeless_by_coc表有很多行，因此我们应该为每个查询增加一个限制。即使您添加的限制比可用行高，查询仍将执行。

　　我们将在检索计数的同一循环中添加限制查询。无需在键上重复两次，我们可以在同一循环中执行这两个操作。但是，我们应注意的呼叫顺序cur.fetchall()。如果我们无法立即获取查询结果，则可以覆盖查询执行。该cur.execute()命令不返回读取结果，并且由用户负责请求读取结果。例如，下面的查询只返回的结果LIMIT，而不是COUNT：

　　让我们将这两个查询添加到单个代码块中：

　　综上所述，我们现在有了一个通用脚本，该脚本将返回数据库中所有用户创建的表的人类可读字典。

　　下一步
 

　　在大数据分析培训课程PostgreSQL内部存储原理中，我们从对数据库及其表一无所知，到我们将要使用的表的可读描述。首先，我们了解了Postgres的内部表和模式背后的概念。然后，我们运用我们的知识从头开始构建自己的描述词典。这篇帖子改编自我们数据工程领域的一项任务。该任务是“ 优化Postgres数据库 ”课程的一部分，我们将扩展对Postgres内部表的了解，以优化HUD表及其查询。大数据分析培训课程PostgreSQL内部存储原理着重于解决在生产级数据分析系统中会遇到的实际场景。