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

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

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

　　2)Python 3

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

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

 

　　調(diào)查表
 

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

　　此時(shí)，我們只關(guān)心數(shù)據(jù)庫中表的名稱。看一下上面的表描述，有一列table_name公開了此信息。讓我們查詢該列，看看我們正在處理什么。

　　使用模式
 

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

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

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

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

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

　　描述表格
 

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

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

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

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

　　類型代碼映射
 

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

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

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

　　可讀的描述類型
 

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

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

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

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

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

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

　　行數(shù)
 

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

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

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

　　樣本行
 

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

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

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

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

　　下一步
 

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