使用JDBC查詢數據庫

磚支持使用JDBC連接到外部數據庫。本文提供了配置和使用這些連接的基本語法的例子在Python中,SQL和Scala。

請注意

你可能更喜歡Lakehouse聯合查詢外部數據庫管理係統。看到使用Lakehouse聯合運行查詢。

合作夥伴連接提供了優化的集成與許多外部外部數據源同步數據。看到磚的合作夥伴的連接是什麼?。

重要的

本文中的示例不包括用戶名和密碼在JDBC url。磚推薦使用秘密數據庫存儲憑證。例如:

              用戶名=dbutils。秘密。得到(範圍=“jdbc”,關鍵=“用戶名”)密碼=dbutils。秘密。得到(範圍=“jdbc”,關鍵=“密碼”)
             

              瓦爾用戶名=dbutils。秘密。得到(範圍=“jdbc”,關鍵=“用戶名”)瓦爾密碼=dbutils。秘密。得到(範圍=“jdbc”,關鍵=“密碼”)
             

與SQL參考數據磚的秘密,你必須配置一個火花在集群initilization配置屬性。

完整的保密管理的例子,請參閱秘密工作流示例。

建立雲連接

磚vpc配置為隻允許火花集群。當連接到另一個基礎設施,最佳實踐是使用VPC凝視。一旦建立了VPC凝視,你可以檢查netcat在集群上的實用性。

% sh數控vz < jdbcHostname > < jdbcPort >

與JDBC讀取數據

您必須配置設置讀取數據使用JDBC。注意,每個數據庫使用不同的格式< jdbc url >。

              employees_table=(火花。讀。格式(“jdbc”)。選項(“url”,“< jdbc url >”)。選項(“數據表”,“<表名稱>”)。選項(“用戶”,“<用戶名>”)。選項(“密碼”,“<密碼>”)。負載())
             

              創建臨時視圖employees_table_vw使用JDBC選項(url“< jdbc url >”,數據表“<表名稱>”,用戶“<用戶名>”,密碼“<密碼>”)
             

              瓦爾employees_table=火花。讀。格式(“jdbc”)。選項(“url”,“< jdbc url >”)。選項(“數據表”,“<表名稱>”)。選項(“用戶”,“<用戶名>”)。選項(“密碼”,“<密碼>”)。負載()
             

火花自動從數據庫表中讀取模式和地圖類型回到火花SQL類型。

              employees_table。printSchema
             

              描述employees_table_vw
             

              employees_table。printSchema
             

您可以運行查詢這個JDBC表:

              顯示(employees_table。選擇(“年齡”,“工資”)。groupBy(“年齡”)。avg(“工資”))
             

              選擇年齡,avg(工資)作為工資從employees_table_vw集團通過年齡
             

              顯示(employees_table。選擇(“年齡”,“工資”)。groupBy(“年齡”)。avg(“工資”))
             

與JDBC寫數據

保存數據表與JDBC使用類似的配置來閱讀。看下麵的例子:

              (employees_table。寫。格式(“jdbc”)。選項(“url”,“< jdbc url >”)。選項(“數據表”,“<新的表名>”)。選項(“用戶”,“<用戶名>”)。選項(“密碼”,“<密碼>”)。保存())
             

              創建表new_employees_table使用JDBC選項(url“< jdbc url >”,數據表“<表名稱>”,用戶“<用戶名>”,密碼“<密碼>”)作為選擇*從employees_table_vw
             

              employees_table。寫。格式(“jdbc”)。選項(“url”,“< jdbc url >”)。選項(“數據表”,“<新的表名>”)。選項(“用戶”,“<用戶名>”)。選項(“密碼”,“<密碼>”)。保存()
             

默認行為試圖創建一個新表和拋出一個錯誤如果一個表的名稱已經存在。

您可以將數據附加到現有表使用下麵的語法:

              (employees_table。寫。格式(“jdbc”)。選項(“url”,“< jdbc url >”)。選項(“數據表”,“<新的表名>”)。選項(“用戶”,“<用戶名>”)。選項(“密碼”,“<密碼>”)。模式(“添加”)。保存())
             

              創建表如果不存在new_employees_table使用JDBC選項(url“< jdbc url >”,數據表“<表名稱>”,用戶“<用戶名>”,密碼“<密碼>”);插入成new_employees_table選擇*從employees_table_vw;
             

              employees_table。寫。格式(“jdbc”)。選項(“url”,“< jdbc url >”)。選項(“數據表”,“<新的表名>”)。選項(“用戶”,“<用戶名>”)。選項(“密碼”,“<密碼>”)。模式(“添加”)。保存()
             

您可以覆蓋現有的表使用下麵的語法:

              (employees_table。寫。格式(“jdbc”)。選項(“url”,“< jdbc url >”)。選項(“數據表”,“<新的表名>”)。選項(“用戶”,“<用戶名>”)。選項(“密碼”,“<密碼>”)。模式(“覆蓋”)。保存())
             

              創建或取代表new_employees_table使用JDBC選項(url“< jdbc url >”,數據表“<表名稱>”,用戶“<用戶名>”,密碼“<密碼>”)作為選擇*從employees_table_vw;
             

              employees_table。寫。格式(“jdbc”)。選項(“url”,“< jdbc url >”)。選項(“數據表”,“<新的表名>”)。選項(“用戶”,“<用戶名>”)。選項(“密碼”,“<密碼>”)。模式(“覆蓋”)。保存()
             

控製並行JDBC查詢

默認情況下,JDBC驅動程序查詢源數據庫隻有一個線程。要提高讀取性能的,您需要指定一些選項來控製多少同時磚對數據庫的查詢。對於小型集群,設置numPartitions選項的數量等於執行人在集群中核心確保所有節點並行查詢數據。

警告

設置numPartitions在大型集群高價值可能導致消極的遠程數據庫的性能,因為太多的同時查詢服務將會瓦解。這是特別麻煩的應用程序數據庫。設定這個值高於50的警惕。

請注意

加快查詢通過選擇一列和索引計算的源數據庫partitionColumn。

下麵的代碼示例演示了配置並行集群的八個核心:

              employees_table=(火花。讀。格式(“jdbc”)。選項(“url”,“< jdbc url >”)。選項(“數據表”,“<表名稱>”)。選項(“用戶”,“<用戶名>”)。選項(“密碼”,“<密碼>”)#一個列,可以使用均勻分布範圍的值,可用於並行化。選項(“partitionColumn”,“<分區鍵>”)# partitionColumn最小值來提取數據。選項(“下界”,“<最小值>”)#最大價值與partitionColumn拉數據。選項(“upperBound”,“<極大值>”)#分區數量將數據分發到。不設置這個非常大的(~數百)。選項(“numPartitions”,8)。負載())
             

              創建臨時視圖employees_table_vw使用JDBC選項(url“< jdbc url >”,數據表“<表名稱>”,用戶“<用戶名>”,密碼“<密碼>”,partitionColumn“<分區鍵>”,下界“<最小值>”,upperBound“<極大值>”,numPartitions8)
             

              瓦爾employees_table=火花。讀。格式(“jdbc”)。選項(“url”,“< jdbc url >”)。選項(“數據表”,“<表名稱>”)。選項(“用戶”,“<用戶名>”)。選項(“密碼”,“<密碼>”)/ /列,可以使用均勻分布範圍的值,可用於並行化。選項(“partitionColumn”,“<分區鍵>”)/ /最小值為與partitionColumn提取數據。選項(“下界”,“<最小值>”)/ /最大價值為與partitionColumn提取數據。選項(“upperBound”,“<極大值>”)/ /將數據分發到數量的分區。不設置這個非常大的(~數百)。選項(“numPartitions”,8)。負載()
             

請注意

磚支持所有Apache的火花選項配置JDBC。

寫入數據庫時使用JDBC, Apache火花在內存中使用分區的數量來控製並行性。你可以重新分配數據之前寫控製並行。避免大量的分區在大型集群,以避免遠程數據庫。下麵的例子演示了實現下筆前8個分區:

              (employees_table。重新分區(8)。寫。格式(“jdbc”)。選項(“url”,“< jdbc url >”)。選項(“數據表”,“<新的表名>”)。選項(“用戶”,“<用戶名>”)。選項(“密碼”,“<密碼>”)。保存())
             

              創建表new_employees_table使用JDBC選項(url“< jdbc url >”,數據表“<表名稱>”,用戶“<用戶名>”,密碼“<密碼>”)作為選擇* / / * +重新分區(8)*從employees_table_vw
             

              employees_table。重新分區(8)。寫。格式(“jdbc”)。選項(“url”,“< jdbc url >”)。選項(“數據表”,“<新的表名>”)。選項(“用戶”,“<用戶名>”)。選項(“密碼”,“<密碼>”)。保存()
             

壓低查詢數據庫引擎

你可以壓低整個查詢到數據庫並返回結果。的表參數識別JDBC表來閱讀。您可以使用任何有效的SQL查詢從條款。

              pushdown_query=”(select *從員工列出< 10008)作為emp_alias”employees_table=(火花。讀。格式(“jdbc”)。選項(“url”,“< jdbc url >”)。選項(“數據表”,pushdown_query)。選項(“用戶”,“<用戶名>”)。選項(“密碼”,“<密碼>”)。負載())
             

              創建臨時視圖employees_table_vw使用JDBC選項(url“< jdbc url >”,數據表”(select *從員工列出< 10008)作為emp_alias”,用戶“<用戶名>”,密碼“<密碼>”)
             

              瓦爾pushdown_query=”(select *從員工列出< 10008)作為emp_alias”瓦爾employees_table=火花。讀。格式(“jdbc”)。選項(“url”,“< jdbc url >”)。選項(“數據表”,pushdown_query)。選項(“用戶”,“<用戶名>”)。選項(“密碼”,“<密碼>”)。負載()
             

控製每個查詢獲取的行數

JDBC驅動程序有一個fetchSize參數控製的行數獲取一次從遠程數據庫。

設置	結果
過低	高延遲由於許多往返(每查詢返回幾行)
太高了	內存不足錯誤(在一個查詢返回太多的數據)

最優值是依賴於工作的。注意事項包括:

查詢返回的列是多少?
返回的數據類型是什麼?
返回的字符串在每一列多久?

係統可能非常小的違約和受益於調優。例如:甲骨文的違約fetchSize是10。增加到100減少總需要執行的查詢的10倍。JDBC結果是網絡流量,所以避免非常大的數字,但最佳值可能是成千上萬的許多數據集。

使用fetchSize選項,如以下示例:

              employees_table=(火花。讀。格式(“jdbc”)。選項(“url”,“< jdbc url >”)。選項(“數據表”,“<表名稱>”)。選項(“用戶”,“<用戶名>”)。選項(“密碼”,“<密碼>”)。選項(“fetchSize”,“100”)。負載())
             

              創建臨時視圖employees_table_vw使用JDBC選項(url“< jdbc url >”,數據表“<表名稱>”,用戶“<用戶名>”,密碼“<密碼>”。fetchSizeOne hundred.)
             

              瓦爾employees_table=火花。讀。格式(“jdbc”)。選項(“url”,“< jdbc url >”)。選項(“數據表”,“<表名稱>”)。選項(“用戶”,“<用戶名>”)。選項(“密碼”,“<密碼>”)。選項(“fetchSize”,“100”)。負載()
             