使用JDBC查詢數據庫

Databricks支持使用JDBC連接外部數據庫。本文通過Python、SQL和Scala中的示例提供了配置和使用這些連接的基本語法。

Partner Connect為與許多外部數據源同步數據提供了優化的集成。看到什麼是Databricks Partner Connect?．

重要的

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

              用戶名＝dbutils．秘密．得到（範圍＝“jdbc”，關鍵＝“用戶名”）密碼＝dbutils．秘密．得到（範圍＝“jdbc”，關鍵＝“密碼”）
             

              瓦爾用戶名＝dbutils．秘密．得到（範圍＝“jdbc”，關鍵＝“用戶名”）瓦爾密碼＝dbutils．秘密．得到（範圍＝“jdbc”，關鍵＝“密碼”）
             

要用SQL引用Databricks的秘密，您必須在初始化集群時配置Spark配置屬性．

有關秘密管理的完整示例，請參見秘密工作流示例．

建立雲連接

數據庫vpc配置為隻支持Spark集群。當連接到另一個基礎設施時，最佳實踐是使用VPC凝視．VPC對等連接建立後，可以使用netcat集群上的實用程序。

%sh nc -vz   . sh

用JDBC讀取數據

您必須配置許多設置來使用JDBC讀取數據。類型使用不同的格式< jdbc_url >．

              employees_table＝（火花．讀．格式（“jdbc”）．選項（“url”，“< jdbc_url >”）．選項（“數據表”，“< table_name >”）．選項（“用戶”，“<用戶名>”）．選項（“密碼”，“<密碼>”）．負載()）
             

              創建臨時視圖employees_table_vw使用JDBC選項（url“< jdbc_url >”，數據表“< table_name >”，用戶“<用戶名>”，密碼' <密碼> '）
             

              瓦爾employees_table＝火花．讀．格式（“jdbc”）．選項（“url”，“< jdbc_url >”）．選項（“數據表”，“< table_name >”）．選項（“用戶”，“<用戶名>”）．選項（“密碼”，“<密碼>”）．負載()
             

Spark自動從數據庫表中讀取模式，並將其類型映射回Spark 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_table_name >”）．選項（“用戶”，“<用戶名>”）．選項（“密碼”，“<密碼>”）．保存()）
             

              創建表格new_employees_table使用JDBC選項（url“< jdbc_url >”，數據表“< table_name >”，用戶“<用戶名>”，密碼' <密碼> '）作為選擇＊從employees_table_vw
             

              employees_table．寫．格式（“jdbc”）．選項（“url”，“< jdbc_url >”）．選項（“數據表”，“< new_table_name >”）．選項（“用戶”，“<用戶名>”）．選項（“密碼”，“<密碼>”）．保存()
             

默認行為嚐試創建一個新表，如果具有該名稱的表已經存在，則拋出錯誤。

您可以使用以下語法向現有表追加數據:

              （employees_table．寫．格式（“jdbc”）．選項（“url”，“< jdbc_url >”）．選項（“數據表”，“< new_table_name >”）．選項（“用戶”，“<用戶名>”）．選項（“密碼”，“<密碼>”）．模式（“添加”）．保存()）
             

              創建表格如果不存在new_employees_table使用JDBC選項（url“< jdbc_url >”，數據表“< table_name >”，用戶“<用戶名>”，密碼' <密碼> ');插入成new_employees_table選擇＊從employees_table_vw；
             

              employees_table．寫．格式（“jdbc”）．選項（“url”，“< jdbc_url >”）．選項（“數據表”，“< new_table_name >”）．選項（“用戶”，“<用戶名>”）．選項（“密碼”，“<密碼>”）．模式（“添加”）．保存()
             

您可以使用以下語法覆蓋已存在的表:

              （employees_table．寫．格式（“jdbc”）．選項（“url”，“< jdbc_url >”）．選項（“數據表”，“< new_table_name >”）．選項（“用戶”，“<用戶名>”）．選項（“密碼”，“<密碼>”）．模式（“覆蓋”）．保存()）
             

              創建或取代表格new_employees_table使用JDBC選項（url“< jdbc_url >”，數據表“< table_name >”，用戶“<用戶名>”，密碼' <密碼> '）作為選擇＊從employees_table_vw；
             

              employees_table．寫．格式（“jdbc”）．選項（“url”，“< jdbc_url >”）．選項（“數據表”，“< new_table_name >”）．選項（“用戶”，“<用戶名>”）．選項（“密碼”，“<密碼>”）．模式（“覆蓋”）．保存()
             

控製JDBC查詢的並行性

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

警告

設置numPartitions在大型集群上設置過高的值可能會導致遠程數據庫的性能下降，因為太多同時進行的查詢可能會使服務不堪重負。這對於應用程序數據庫來說尤其麻煩。注意不要將這個值設置為50以上。

請注意

控件選擇具有在源數據庫中計算的索引的列，從而加快查詢速度partitionColumn．

下麵的代碼示例演示了為8核集群配置並行性:

              employees_table＝（火花．讀．格式（“jdbc”）．選項（“url”，“< jdbc_url >”）．選項（“數據表”，“< table_name >”）．選項（“用戶”，“<用戶名>”）．選項（“密碼”，“<密碼>”）#可以使用的列，該列具有均勻分布的可用於並行化的值範圍．選項（“partitionColumn”，“< partition_key >”）使用partitionColumn來獲取數據．選項（“下界”，“< min_value >”）# max值為partitionColumn提取數據．選項（“upperBound”，“< max_value >”）#要將數據分布到的分區數量。不要設置很大(~數百)．選項（“numPartitions”，8）．負載()）
             

              創建臨時視圖employees_table_vw使用JDBC選項（url“< jdbc_url >”，數據表“< table_name >”，用戶“<用戶名>”，密碼' <密碼> '，partitionColumn“< partition_key >”，下界“< min_value >”，upperBound“< max_value >”，numPartitions8）
             

              瓦爾employees_table＝火花．讀．格式（“jdbc”）．選項（“url”，“< jdbc_url >”）．選項（“數據表”，“< table_name >”）．選項（“用戶”，“<用戶名>”）．選項（“密碼”，“<密碼>”）//可以使用的列，該列具有均勻分布的可用於並行化的值範圍．選項（“partitionColumn”，“< partition_key >”）//使用partitionColumn提取數據的最小值．選項（“下界”，“< min_value >”）//使用partitionColumn獲取數據．選項（“upperBound”，“< max_value >”）//分配數據的分區數。不要設置很大(~數百)．選項（“numPartitions”，8）．負載()
             

請注意

Databricks支持所有Apache Spark配置JDBC的選項．

當使用JDBC寫入數據庫時，Apache Spark使用內存中的分區數量來控製並行性。可以在寫入數據之前對數據重新分區，以控製並行性。避免在大型集群上使用大量分區，以避免使遠程數據庫不堪重負。下麵的示例演示了在寫入之前將分區重新劃分到八個分區:

              （employees_table．重新分區（8）．寫．格式（“jdbc”）．選項（“url”，“< jdbc_url >”）．選項（“數據表”，“< new_table_name >”）．選項（“用戶”，“<用戶名>”）．選項（“密碼”，“<密碼>”）．保存()）
             

              創建表格new_employees_table使用JDBC選項（url“< jdbc_url >”，數據表“< table_name >”，用戶“<用戶名>”，密碼' <密碼> '）作為選擇/*+重分區(8)*/＊從employees_table_vw
             

              employees_table．重新分區（8）．寫．格式（“jdbc”）．選項（“url”，“< jdbc_url >”）．選項（“數據表”，“< new_table_name >”）．選項（“用戶”，“<用戶名>”）．選項（“密碼”，“<密碼>”）．保存()
             

下推一個查詢到數據庫引擎

您可以將整個查詢下推到數據庫，隻返回結果。的表格參數標識要讀取的JDBC表。您可以使用SQL查詢中任何有效的內容從條款。

              pushdown_query＝"(select * from employees where emp_no < 10008) as emp_alias"employees_table＝（火花．讀．格式（“jdbc”）．選項（“url”，“< jdbc_url >”）．選項（“數據表”，pushdown_query）．選項（“用戶”，“<用戶名>”）．選項（“密碼”，“<密碼>”）．負載()）
             

              創建臨時視圖employees_table_vw使用JDBC選項（url“< jdbc_url >”，數據表"(select * from employees where emp_no < 10008) as emp_alias"，用戶“<用戶名>”，密碼' <密碼> '）
             

              瓦爾pushdown_query＝"(select * from employees where emp_no < 10008) as emp_alias"瓦爾employees_table＝火花．讀．格式（“jdbc”）．選項（“url”，“< jdbc_url >”）．選項（“數據表”，pushdown_query）．選項（“用戶”，“<用戶名>”）．選項（“密碼”，“<密碼>”）．負載()
             

控製每個查詢獲取的行數

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

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

最佳值取決於工作負載。注意事項包括:

查詢返回多少列?
返回什麼數據類型?
每列返回的字符串有多長?

係統可能有非常小的默認值，並受益於調優。例如:Oracle的默認值fetchSize是10。將其增加到100可以將需要執行的查詢總數減少到原來的1 / 10。JDBC結果是網絡流量，因此要避免非常大的數字，但對於許多數據集，最佳值可能是數千。

使用fetchSize選項，如下例所示:

              employees_table＝（火花．讀．格式（“jdbc”）．選項（“url”，“< jdbc_url >”）．選項（“數據表”，“< table_name >”）．選項（“用戶”，“<用戶名>”）．選項（“密碼”，“<密碼>”）．選項（“fetchSize”，“100”）．負載()）
             

              創建臨時視圖employees_table_vw使用JDBC選項（url“< jdbc_url >”，數據表“< table_name >”，用戶“<用戶名>”，密碼' <密碼> '．fetchSizeOne hundred.）
             

              瓦爾employees_table＝火花．讀．格式（“jdbc”）．選項（“url”，“< jdbc_url >”）．選項（“數據表”，“< table_name >”）．選項（“用戶”，“<用戶名>”）．選項（“密碼”，“<密碼>”）．選項（“fetchSize”，“100”）．負載()
             