大數(shù)據(jù)分析培訓(xùn)課程PostgreSQL內(nèi)部存儲(chǔ)原理

　　PostgreSQL內(nèi)部存儲(chǔ)原理是什么？在以前的文章中，我們已經(jīng)描述了Postgres數(shù)據(jù)庫(kù)以及使用Python與之交互的方式。這些帖子提供了基礎(chǔ)知識(shí)，但是如果您想在生產(chǎn)系統(tǒng)中使用數(shù)據(jù)庫(kù)，則必須知道如何使查詢(xún)更快，更高效。要了解效率在Postgres中意味著什么，重要的是要了解Postgres的工作原理。在大數(shù)據(jù)分析培訓(xùn)課程PostgreSQL內(nèi)部存儲(chǔ)原理中，我們將重點(diǎn)介紹Postgres和關(guān)系數(shù)據(jù)庫(kù)的更高級(jí)概念。首先，我們將學(xué)習(xí)Postgres如何存儲(chǔ)自己的內(nèi)部數(shù)據(jù)以描述，調(diào)試和識(shí)別系統(tǒng)中的瓶頸。然后，我們將利用對(duì)Postgres內(nèi)部數(shù)據(jù)的了解，使用Python構(gòu)建我們自己的數(shù)據(jù)庫(kù)描述工具版本。像我們以前的文章一樣，我們將使用以下工具：
 

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

　　2)Python 3

　　3)Postgres的Python驅(qū)動(dòng)程序， psycopg2
 

　　我們將使用的數(shù)據(jù)集來(lái)自美國(guó)住房和城市發(fā)展部(也稱(chēng)為HUD)。我們已經(jīng)將文件打包到一個(gè)zip文件中，該文件包含CSV格式的數(shù)據(jù)，還有一個(gè)Python 3腳本(load_hud_tables.py)，它將CSV文件復(fù)制到本地運(yùn)行的Postgres中。如果您運(yùn)行的是沒(méi)有默認(rèn)連接的Postgres服務(wù)器，則需要更新腳本中的連接字符串。使用HUD的數(shù)據(jù)集，我們將使用每個(gè)Postgres引擎中的可用命令來(lái)處理真實(shí)數(shù)據(jù)示例。從空白開(kāi)始，我們將研究表及其數(shù)據(jù)類(lèi)型。然后，我們將使用內(nèi)部Postgres表探索HUD 表為我們提供有關(guān)數(shù)據(jù)庫(kù)內(nèi)容的詳細(xì)說(shuō)明。首先，下載并解壓縮dq_postgres_internals.zip 文件。進(jìn)入dq_postgres_internals/目錄，在中更改連接參數(shù)load_hud_tables.py，然后運(yùn)行腳本。這會(huì)將CSV文件加載到您的本地Postgres實(shí)例中。將文件加載到Postgres服務(wù)器后，我們可以通過(guò)連接數(shù)據(jù)庫(kù)開(kāi)始。如果本地Postgres實(shí)例與默認(rèn)實(shí)例不同，請(qǐng)更改連接值。在整個(gè)這篇文章中，以后對(duì)該對(duì)象的任何引用cur將是以下連接的游標(biāo)：

 

　　調(diào)查表
 

　　現(xiàn)在我們已經(jīng)建立了連接，是時(shí)候開(kāi)始探索我們可以使用的表了。首先，我們將檢查Postgres內(nèi)部表，這些表向我們提供了有關(guān)數(shù)據(jù)庫(kù)的詳細(xì)信息。讓我們從描述這些內(nèi)部表開(kāi)始。在每個(gè)Postgres引擎中，都有一組內(nèi)部表，Postgres使用這些內(nèi)部表來(lái)管理其整個(gè)結(jié)構(gòu)。這些表作為組information_schema和系統(tǒng)目錄位于Postgres文檔中。這些包含有關(guān)存儲(chǔ)在Postgres數(shù)據(jù)庫(kù)中的數(shù)據(jù)，表名和類(lèi)型的所有信息。例如，當(dāng)我們使用屬性時(shí)，cur.description，它將從內(nèi)部表中提取信息以顯示給用戶(hù)。不幸的是，沒(méi)有數(shù)據(jù)集的詳細(xì)架構(gòu)。因此，我們需要為包含的內(nèi)容創(chuàng)建自己的詳細(xì)描述。讓我們使用中的內(nèi)部表information_schema來(lái)獲得有關(guān)數(shù)據(jù)庫(kù)中存儲(chǔ)哪些表的高級(jí)概述。內(nèi)部表稱(chēng)為information_schema.tables，從文檔中我們可以看到有很多列可供選擇：

　　此時(shí)，我們只關(guān)心數(shù)據(jù)庫(kù)中表的名稱(chēng)?？匆幌律厦娴谋砻枋?，有一列table_name公開(kāi)了此信息。讓我們查詢(xún)?cè)摿?，看看我們正在處理什么?/p>

　　使用模式
 

　　當(dāng)您運(yùn)行前面的命令時(shí)，您會(huì)注意到輸出中包含大量的表。這些表中的每個(gè)表都可以在postgres數(shù)據(jù)庫(kù)中找到。問(wèn)題是：我們?cè)诮Y(jié)果中包括內(nèi)部表。在輸出中，您會(huì)注意到許多表都是以prefix開(kāi)頭的pg_*。這些表中的每一個(gè)都是pg_catalog內(nèi)部表組的一部分。這些是我們前面介紹的系統(tǒng)目錄表。然后，您可能已經(jīng)猜到了，該information_schema名稱(chēng)下還有一組其他表。但是，這些表很難找到，因?yàn)樗鼈儧](méi)有明顯的前綴模式。我們?nèi)绾沃滥男┍硎莾?nèi)部的，哪些表是用戶(hù)創(chuàng)建的?我們需要描述模式解釋以上問(wèn)題。在處理關(guān)系數(shù)據(jù)庫(kù)時(shí)，模式一詞被概括為具有多種含義的術(shù)語(yǔ)。您可能聽(tīng)說(shuō)過(guò)架構(gòu)被用來(lái)描述表(它們的數(shù)據(jù)類(lèi)型，名稱(chēng)，列等)或架構(gòu)作為數(shù)據(jù)庫(kù)的藍(lán)圖。模式的含糊含義只會(huì)使我們感到困惑。但是，對(duì)于Postgres，術(shù)語(yǔ)“架構(gòu)”已保留用于特定目的。在Postgres中，模式用作表的命名空間，其獨(dú)特目的是將它們分成單個(gè)數(shù)據(jù)庫(kù)內(nèi)的隔離組或集合。

　　讓我們進(jìn)一步分解。Postgres使用數(shù)據(jù)庫(kù)的概念在Postgres服務(wù)器中分離用戶(hù)和數(shù)據(jù)。創(chuàng)建數(shù)據(jù)庫(kù)時(shí)，您正在創(chuàng)建一個(gè)隔離的環(huán)境，用戶(hù)可以在其中查詢(xún)只能在該特定數(shù)據(jù)庫(kù)中找到的表。這是一個(gè)示例：假設(shè)國(guó)土安全部(DHS)和HUD共享相同的政府Postgres數(shù)據(jù)庫(kù)，但他們希望將其用戶(hù)和數(shù)據(jù)分開(kāi)。然后，他們將使用數(shù)據(jù)庫(kù)通過(guò)每個(gè)代理商的單獨(dú)數(shù)據(jù)庫(kù)將他們的數(shù)據(jù)和用戶(hù)分開(kāi)?，F(xiàn)在，當(dāng)用戶(hù)想要連接到他們的數(shù)據(jù)時(shí)，他們需要指定將要連接到那里的數(shù)據(jù)庫(kù)，并且只有在那里，他們才能使用他們的表。但是，假設(shè)有些分析師想對(duì)公民數(shù)據(jù)(citizens表)和城市住房發(fā)展(developments表)進(jìn)行橫斷面分析。好吧，那么他們將想同時(shí)查詢(xún)dhs數(shù)據(jù)庫(kù)中的表和hud數(shù)據(jù)庫(kù)。但是，對(duì)于Postgres，這是不可能的。

　　如果我們想將表分成不同的組，但仍然允許跨表查詢(xún)?cè)趺崔k?這是架構(gòu)的完美用例。代替數(shù)據(jù)庫(kù)，對(duì)每個(gè)代理機(jī)構(gòu)使用不同的架構(gòu)將使用名稱(chēng)空間分隔表，但仍允許分析人員查詢(xún)兩個(gè)表。

　　這就是Postgres劃分其內(nèi)部表(以及用戶(hù)創(chuàng)建的表!)的方式。創(chuàng)建數(shù)據(jù)庫(kù)時(shí)，實(shí)例化了3種模式：(pg_catalog對(duì)于系統(tǒng)目錄表)，information_schema(對(duì)于信息模式表)和public(對(duì)于用戶(hù)創(chuàng)建的表的默認(rèn)模式)。每次您CREATE TABLE在數(shù)據(jù)庫(kù)中發(fā)出命令時(shí)，默認(rèn)情況下Postgres都會(huì)將該表分配給public模式?，F(xiàn)在，回到以前的問(wèn)題，如何將用戶(hù)創(chuàng)建的表與內(nèi)部表分開(kāi)?information_schema.tables再次查看各列?，F(xiàn)在，檢查是否存在可將用戶(hù)創(chuàng)建的表與內(nèi)部表分開(kāi)的列。

　　有一個(gè)名為的列table_schema將符合我們的要求。我們可以過(guò)濾此列以選擇所有公共表。這是我們編寫(xiě)查詢(xún)的方法：

　　描述表格
 

　　從輸出中，我們將只使用三個(gè)表。這些是：

　　使用表名，我們可以調(diào)用cur.description屬性以詳細(xì)查看每個(gè)表的列，類(lèi)型和任何其他元信息。在此之前，我們了解到可以發(fā)出SELECT查詢(xún)，然后調(diào)用description屬性以獲取表信息。但是，如果我們想在for每個(gè)表的循環(huán)中執(zhí)行該操作怎么辦?希望您的第一個(gè)想法是不要使用.format()。在之前的文章中，我們已經(jīng)提到了圍繞字符串內(nèi)插的問(wèn)題.format()。答案是使用方法或該方法中的第二個(gè)位置參數(shù)對(duì)字符串進(jìn)行Mogirfy處理。不幸的是，事情并不是那么容易。嘗試插入表名–使用mogrify()execute()mogirfy()–導(dǎo)致錯(cuò)誤，而不是列名，過(guò)濾鍵或分組鍵。這是此錯(cuò)誤的示例：
 

　　從代碼片段中，您可能已經(jīng)注意到，mogrify()在表名上使用會(huì)將該名稱(chēng)"state_info"轉(zhuǎn)換為Postgres字符串。這對(duì)于列名或過(guò)濾器查詢(xún)是必需的，但對(duì)于表名則不是必需的。相反，您必須使用psycopg2.extensions名為AsIs的模塊中的類(lèi)。

　　SELECT查詢(xún)中的表名不需要用字符串引起來(lái)。因此，AsIs將其保留為帶引號(hào)的有效SQL表示形式，而不是對(duì)其進(jìn)行轉(zhuǎn)換。使用AsIs，我們可以檢查每個(gè)表的描述，而不必寫(xiě)下每個(gè)請(qǐng)求!

　　類(lèi)型代碼映射
 

　　在打印完每個(gè)描述之后，我們現(xiàn)在將詳細(xì)研究將要使用的表。這是homeless_by_coc描述的輸出的片段：

　　了解該description屬性后，您應(yīng)該對(duì)可用的元數(shù)據(jù)感到滿(mǎn)意。但是，我們?cè)俅蚊鎸?duì)整數(shù)type_code而不是人類(lèi)可讀的類(lèi)型。有太多的心理開(kāi)銷(xiāo)記得什么時(shí)候人類(lèi)可讀的類(lèi)型，他們代表(即TEXT，INTEGER或BOOLEAN)。您可以使用psycopg2類(lèi)型值來(lái)查找每一列的近似類(lèi)型，但是我們可以做得比近似那些值更好。使用內(nèi)部表，我們可以準(zhǔn)確地映射HUD表中每一列的類(lèi)型。我們將使用的內(nèi)部表來(lái)自系統(tǒng)目錄架構(gòu)，pg_catalog并被正確命名pg_type。我們建議您檢查文檔中的表說(shuō)明，因?yàn)楸竟?jié)中要添加的行太多。您可以在此處找到表格說(shuō)明。在此表中，有很多已定義的列–您無(wú)需擔(dān)心許多列。但是，關(guān)于此表需要注意的一件事是它可用于從頭開(kāi)始創(chuàng)建自己的Postgres類(lèi)型。例如，使用此表，您可以創(chuàng)建一個(gè)HEX只能用于在列中存儲(chǔ)十六進(jìn)制字符的類(lèi)型。讓我們循環(huán)遍歷返回的SELECT查詢(xún)，并將整數(shù)類(lèi)型代碼映射到字符串。它看起來(lái)應(yīng)該類(lèi)似于以下內(nèi)容：

　　使用字典理解，我們可以編寫(xiě)以下內(nèi)容：

　　可讀的描述類(lèi)型
 

　　現(xiàn)在，我們將所有類(lèi)型代碼映射到其類(lèi)型名稱(chēng)。使用type_mappings字典可以提供類(lèi)型，而無(wú)需在文檔中查找它們。讓我們將所有這些放在一起并創(chuàng)建我們自己的表描述。我們想description從元組列表中將屬性重寫(xiě)為易于閱讀的屬性。我們將把先前練習(xí)的輸出組裝到該字典中：

　　使用type_mappings和table_names將為我們提供所需結(jié)果的步驟是：

　　1)table_names使用table變量 循環(huán)遍歷。

　　2)獲取description給定的屬性table。

　　3)table使用columns鍵 將的名稱(chēng)映射到字典。

　　4)columns通過(guò)遍歷description并映射適當(dāng)?shù)念?lèi)型，從屏幕示例重新創(chuàng)建列表。
 

　　行數(shù)
 

　　事情開(kāi)始融合在一起?，F(xiàn)在，為了完成我們的調(diào)查，我們想提供有關(guān)表中行的其他信息。讓我們用表中的行數(shù)提供描述。我們可以使用COUNT()聚合函數(shù)找到行數(shù)。這與SQLite的aggreggate函數(shù)以及其他SQL語(yǔ)法實(shí)現(xiàn)極為相似。如果您想了解有關(guān)Postgres聚合函數(shù)的更多信息，它們都在pg_catalog.pg_aggregate內(nèi)部表中定義。提醒一下，這是COUNT()在Postgres中使用該功能的方式：

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

　　而不是遍歷table_names列表，我們將遍歷readable_description字典鍵：

　　樣本行
 

　　最后，讓我們?cè)趓eadable_description字典中添加一些示例行。由于該homeless_by_coc表有很多行，因此我們應(yīng)該為每個(gè)查詢(xún)?cè)黾右粋€(gè)限制。即使您添加的限制比可用行高，查詢(xún)?nèi)詫?zhí)行。

　　我們將在檢索計(jì)數(shù)的同一循環(huán)中添加限制查詢(xún)。無(wú)需在鍵上重復(fù)兩次，我們可以在同一循環(huán)中執(zhí)行這兩個(gè)操作。但是，我們應(yīng)注意的呼叫順序cur.fetchall()。如果我們無(wú)法立即獲取查詢(xún)結(jié)果，則可以覆蓋查詢(xún)執(zhí)行。該cur.execute()命令不返回讀取結(jié)果，并且由用戶(hù)負(fù)責(zé)請(qǐng)求讀取結(jié)果。例如，下面的查詢(xún)只返回的結(jié)果LIMIT，而不是COUNT：

　　讓我們將這兩個(gè)查詢(xún)添加到單個(gè)代碼塊中：

　　綜上所述，我們現(xiàn)在有了一個(gè)通用腳本，該腳本將返回?cái)?shù)據(jù)庫(kù)中所有用戶(hù)創(chuàng)建的表的人類(lèi)可讀字典。

　　下一步
 

　　在大數(shù)據(jù)分析培訓(xùn)課程PostgreSQL內(nèi)部存儲(chǔ)原理中，我們從對(duì)數(shù)據(jù)庫(kù)及其表一無(wú)所知，到我們將要使用的表的可讀描述。首先，我們了解了Postgres的內(nèi)部表和模式背后的概念。然后，我們運(yùn)用我們的知識(shí)從頭開(kāi)始構(gòu)建自己的描述詞典。這篇帖子改編自我們數(shù)據(jù)工程領(lǐng)域的一項(xiàng)任務(wù)。該任務(wù)是“ 優(yōu)化Postgres數(shù)據(jù)庫(kù) ”課程的一部分，我們將擴(kuò)展對(duì)Postgres內(nèi)部表的了解，以?xún)?yōu)化HUD表及其查詢(xún)。大數(shù)據(jù)分析培訓(xùn)課程PostgreSQL內(nèi)部存儲(chǔ)原理著重于解決在生產(chǎn)級(jí)數(shù)據(jù)分析系統(tǒng)中會(huì)遇到的實(shí)際場(chǎng)景。