在Azure Synapse Analytics中查詢數據

您可以使用Azure Synapse連接器從Databricks訪問Azure Synapse，該連接器使用複製語句，使用Azure data Lake Storage Gen2存儲帳戶臨時暫存，在Databricks集群和Azure Synapse實例之間有效傳輸大量數據。

Azure Synapse Analytics是一個基於雲計算的企業數據倉庫，它利用大規模並行處理(MPP)跨pb級數據快速運行複雜查詢。

重要的

此連接器僅用於Synapse專用池實例，與其他Synapse組件不兼容。

請注意

複製僅在Azure數據湖存儲Gen2實例上可用。如果您正在尋找使用Polybase的詳細信息，請參見使用PolyBase連接Databricks和Azure Synapse(遺留)．

Synapse的語法示例

您可以在Scala、Python、SQL和r中查詢Synapse。下麵的代碼示例使用存儲帳戶密鑰並將存儲憑據從Databricks轉發到Synapse。

請注意

使用Azure門戶提供的連接字符串，該連接為Spark驅動程序和Azure Synapse實例之間通過JDBC連接發送的所有數據啟用安全套接字層(SSL)加密。如果需要驗證SSL加密是否已啟用，可以搜索加密= true在連接字符串中。

//在notebook會話conf中設置存儲帳戶訪問密鑰。火花．相依．集（“fs.azure.account.key。< your-storage-account-name > .dfs.core.windows.net”，“< your-storage-account-access-key >”）//從Azure Synapse表中獲取數據瓦爾df：DataFrame＝火花．讀．格式（“com.databricks.spark.sqldw”）．選項（“url”，" jdbc::狀態"置疑" / / < the-rest-of-the-connection-string >”）．選項（“tempDir”，“abfss: / / < your-container-name > @ < your-storage-account-name > .dfs.core.windows.net/ < your-directory-name >”）．選項（“forwardSparkAzureStorageCredentials”，“真正的”）．選項（“數據表”，“< your-table-name >”）．負載（）//從Azure Synapse查詢中加載數據。瓦爾df：DataFrame＝火花．讀．格式（“com.databricks.spark.sqldw”）．選項（“url”，" jdbc::狀態"置疑" / / < the-rest-of-the-connection-string >”）．選項（“tempDir”，“abfss: / / < your-container-name > @ < your-storage-account-name > .dfs.core.windows.net/ < your-directory-name >”）．選項（“forwardSparkAzureStorageCredentials”，“真正的”）．選項（“查詢”，select x, count(*) as CNT from table group by x）．負載（）//對數據應用一些轉換，然後使用//將數據寫回Azure Synapse中的另一個表的數據源API。df．寫．格式（“com.databricks.spark.sqldw”）．選項（“url”，" jdbc::狀態"置疑" / / < the-rest-of-the-connection-string >”）．選項（“forwardSparkAzureStorageCredentials”，“真正的”）．選項（“數據表”，“< your-table-name >”）．選項（“tempDir”，“abfss: / / < your-container-name > @ < your-storage-account-name > .dfs.core.windows.net/ < your-directory-name >”）．保存（）
在notebook會話conf中設置存儲帳戶訪問密鑰。火花．相依．集（“fs.azure.account.key。< your-storage-account-name > .dfs.core.windows.net”，“< your-storage-account-access-key >”）#從Azure Synapse表中獲取一些數據。df＝火花．讀＼．格式（“com.databricks.spark.sqldw”）＼．選項（“url”，" jdbc::狀態"置疑" / / < the-rest-of-the-connection-string >”）＼．選項（“tempDir”，“abfss: / / < your-container-name > @ < your-storage-account-name > .dfs.core.windows.net/ < your-directory-name >”）＼．選項（“forwardSparkAzureStorageCredentials”，“真正的”）＼．選項（“數據表”，“< your-table-name >”）＼．負載（）#從Azure Synapse查詢中加載數據df＝火花．讀＼．格式（“com.databricks.spark.sqldw”）＼．選項（“url”，" jdbc::狀態"置疑" / / < the-rest-of-the-connection-string >”）＼．選項（“tempDir”，“abfss: / / < your-container-name > @ < your-storage-account-name > .dfs.core.windows.net/ < your-directory-name >”）＼．選項（“forwardSparkAzureStorageCredentials”，“真正的”）＼．選項（“查詢”，select x, count(*) as CNT from table group by x）＼．負載（）對數據應用一些轉換，然後使用#數據源API將數據寫回Azure Synapse中的另一個表。df．寫＼．格式（“com.databricks.spark.sqldw”）＼．選項（“url”，" jdbc::狀態"置疑" / / < the-rest-of-the-connection-string >”）＼．選項（“forwardSparkAzureStorageCredentials”，“真正的”）＼．選項（“數據表”，“< your-table-name >”）＼．選項（“tempDir”，“abfss: / / < your-container-name > @ < your-storage-account-name > .dfs.core.windows.net/ < your-directory-name >”）＼．保存（）
——在筆記本會話配置文件中設置存儲帳戶訪問密鑰。集fs．azure．賬戶．關鍵．<你的-存儲-賬戶-的名字>．dfs．核心．窗戶．網= <你的-存儲-賬戶-訪問-關鍵>；——使用SQL讀取數據。創建表格example_table_in_spark_read使用com．磚．火花．sqldw選項（url" jdbc::狀態"置疑" / / < the-rest-of-the-connection-string >”，forwardSparkAzureStorageCredentials“真正的”，數據表' < your-table-name >”，tempDir“abfss: / / < your-container-name > @ < your-storage-account-name > .dfs.core.windows.net/ < your-directory-name >”）;——使用SQL寫數據。——創建一個新表，如果已經存在同名的表，則拋出一個錯誤:創建表格example_table_in_spark_write使用com．磚．火花．sqldw選項（url" jdbc::狀態"置疑" / / < the-rest-of-the-connection-string >”，forwardSparkAzureStorageCredentials“真正的”，數據表' < your-table-name >”，tempDir“abfss: / / < your-container-name > @ < your-storage-account-name > .dfs.core.windows.net/ < your-directory-name >”）作為選擇＊從table_to_save_in_spark；
#加載SparkR圖書館（SparkR）在notebook會話conf中設置存儲帳戶訪問密鑰。相依<-sparkR.callJMethod（sparkR.session（），“配置”）sparkR.callJMethod（相依，“設置”，“fs.azure.account.key。< your-storage-account-name > .dfs.core.windows.net”，“< your-storage-account-access-key >”）#從Azure Synapse表中獲取一些數據。df<-read.df（源＝“com.databricks.spark.sqldw”，url＝" jdbc::狀態"置疑" / / < the-rest-of-the-connection-string >”，forward_spark_azure_storage_credentials＝“真正的”，數據表＝“< your-table-name >”，tempDir＝“abfss: / / < your-container-name > @ < your-storage-account-name > .dfs.core.windows.net/ < your-directory-name >”）#從Azure Synapse查詢中加載數據df<-read.df（源＝“com.databricks.spark.sqldw”，url＝" jdbc::狀態"置疑" / / < the-rest-of-the-connection-string >”，forward_spark_azure_storage_credentials＝“真正的”，查詢＝select x, count(*) as CNT from table group by x，tempDir＝“abfss: / / < your-container-name > @ < your-storage-account-name > .dfs.core.windows.net/ < your-directory-name >”）對數據應用一些轉換，然後使用#數據源API將數據寫回Azure Synapse中的另一個表。write.df（df，源＝“com.databricks.spark.sqldw”，url＝" jdbc::狀態"置疑" / / < the-rest-of-the-connection-string >”，forward_spark_azure_storage_credentials＝“真正的”，數據表＝“< your-table-name >”，tempDir＝“abfss: / / < your-container-name > @ < your-storage-account-name > .dfs.core.windows.net/ < your-directory-name >”）

如何在Databricks和Synapse之間進行身份驗證?

Azure Synapse連接器使用三種類型的網絡連接:

  • Spark驅動程序到Azure Synapse

  • Spark集群到Azure存儲帳戶

  • Azure Synapse到Azure存儲帳戶

配置對Azure存儲的訪問

Databricks和Synapse都需要對Azure存儲帳戶的特權訪問，以便用於臨時數據存儲。

Azure Synapse不支持使用SAS進行存儲帳戶訪問。您可以通過執行以下操作之一來配置兩個服務的訪問:

需要Azure Synapse權限

因為它使用了複製在後台，Azure Synapse連接器要求JDBC連接用戶有權限在連接的Azure Synapse實例中運行以下命令:

如果目標表在Azure Synapse中不存在，除了上麵的命令外，還需要運行以下命令的權限:

下表總結了寫入所需的權限複製：

權限(插入到現有表中)

權限(插入到新表中)

管理數據庫批量操作

插入

管理數據庫批量操作

插入

創建表

ALTER ON SCHEMA:: dbo

網絡配置

如果在Azure Synapse上配置防火牆，則必須配置網絡設置以允許Databricks訪問Azure Synapse。首先，確保Databricks工作空間部署在您自己的虛擬網絡中＿．然後，您可以在Azure Synpase上配置IP防火牆規則，以允許從子網連接到Synpase帳戶。看到Azure Synapse Analytics IP防火牆規則．

使用服務主體配置從Databricks到Synapse的OAuth 2.0連接

您可以使用具有訪問底層存儲帳戶權限的服務主體對Azure Synapse Analytics進行身份驗證。有關使用服務主體憑據訪問Azure存儲帳戶的詳細信息，請參見訪問Azure數據湖存儲Gen2和Blob存儲．您必須設置enableServicePrincipalAuth選項真正的在連接配置中Databricks突觸連接器選項參考使連接器能夠使用服務主體進行身份驗證。

您可以選擇為Azure Synapse Analytics連接使用不同的服務主體。以下示例為存儲帳戶配置服務主體憑據，為Synapse配置可選服務主體憑據:

；為Azure存儲帳戶定義服務主體憑據fs.azure.account.auth.type OAuthfs.azure.account.oauth.provider.type org.apache.hadoop.fs.azurebfs.oauth2.ClientCredsTokenProviderfs.azure.account.oauth2.client。id <應用程序id >fs.azure.account.oauth2.client。秘密< service-credential >fs.azure.account.oauth2.client。端點https://login.microsoftonline.com/ < directory-id > / oauth2 /令牌；為Azure Synapse Analytics定義一組單獨的服務主體憑證(如果沒有定義，連接器將使用Azure存儲帳戶憑證)spark.databricks.sqldw.jdbc.service.principal.client.id <應用程序id >spark.databricks.sqldw.jdbc.service.principal.client.secret < service-credential >
//為Azure存儲帳戶定義Service Principal憑據火花．相依．集（“fs.azure.account.auth.type”，“OAuth”）火花．相依．集（“fs.azure.account.oauth.provider.type”，“org.apache.hadoop.fs.azurebfs.oauth2.ClientCredsTokenProvider”）火花．相依．集（“fs.azure.account.oauth2.client.id”，“<應用程序id >”）火花．相依．集（“fs.azure.account.oauth2.client.secret”，“< service-credential >”）火花．相依．集（“fs.azure.account.oauth2.client.endpoint”，“https://login.microsoftonline.com/ < directory-id > / oauth2 /令牌”）//為Azure Synapse Analytics定義一個單獨的服務主體憑證集(如果沒有定義，連接器將使用Azure存儲帳戶憑證)火花．相依．集（“spark.databricks.sqldw.jdbc.service.principal.client.id”，“<應用程序id >”）火花．相依．集（“spark.databricks.sqldw.jdbc.service.principal.client.secret”，“< service-credential >”）
#為Azure存儲帳戶定義服務主體憑證火花．相依．集（“fs.azure.account.auth.type”，“OAuth”）火花．相依．集（“fs.azure.account.oauth.provider.type”，“org.apache.hadoop.fs.azurebfs.oauth2.ClientCredsTokenProvider”）火花．相依．集（“fs.azure.account.oauth2.client.id”，“<應用程序id >”）火花．相依．集（“fs.azure.account.oauth2.client.secret”，“< service-credential >”）火花．相依．集（“fs.azure.account.oauth2.client.endpoint”，“https://login.microsoftonline.com/ < directory-id > / oauth2 /令牌”）#為Azure Synapse Analytics定義一組單獨的服務主體憑證(如果沒有定義，連接器將使用Azure存儲帳戶憑證)火花．相依．集（“spark.databricks.sqldw.jdbc.service.principal.client.id”，“<應用程序id >”）火花．相依．集（“spark.databricks.sqldw.jdbc.service.principal.client.secret”，“< service-credential >”）
#加載SparkR圖書館（SparkR）相依<-sparkR.callJMethod（sparkR.session（），“配置”）#為Azure存儲帳戶定義服務主體憑證sparkR.callJMethod（相依，“設置”，“fs.azure.account.auth.type”，“OAuth”）sparkR.callJMethod（相依，“設置”，“fs.azure.account.oauth.provider.type”，“org.apache.hadoop.fs.azurebfs.oauth2.ClientCredsTokenProvider”）sparkR.callJMethod（相依，“設置”，“fs.azure.account.oauth2.client.id”，“<應用程序id >”）sparkR.callJMethod（相依，“設置”，“fs.azure.account.oauth2.client.secret”，“< service-credential >”）sparkR.callJMethod（相依，“設置”，“fs.azure.account.oauth2.client.endpoint”，“https://login.microsoftonline.com/ < directory-id > / oauth2 /令牌”）#為Azure Synapse Analytics定義一組單獨的服務主體憑證(如果沒有定義，連接器將使用Azure存儲帳戶憑證)sparkR.callJMethod（相依，“設置”，“spark.databricks.sqldw.jdbc.service.principal.client.id”，“<應用程序id >”）sparkR.callJMethod（相依，“設置”，“spark.databricks.sqldw.jdbc.service.principal.client.secret”，“< service-credential >”）

支持批量寫入的保存模式

Azure Synapse連接器支持ErrorIfExists，忽略，附加,覆蓋保存模式，默認模式為ErrorIfExists．有關Apache Spark中支持的保存模式的更多信息，請參見Spark SQL文檔中的保存模式．

Databricks突觸連接器選項參考

的選項Spark SQL中提供的支持以下設置:

參數

要求

默認的

筆記

數據表

是的,除非查詢指定

沒有默認的

在Azure Synapse中要創建或讀取的表。當將數據保存回Azure Synapse時，此參數是必需的。

你也可以使用{模式名稱},{表名稱}訪問給定模式中的表。如果沒有提供模式名，則使用與JDBC用戶關聯的默認模式。

以前支持的數據表Variant已棄用，在將來的版本中將被忽略。使用“駱駝案例”的名稱。

查詢

是的,除非數據表指定

沒有默認的

在Azure Synapse中讀取的查詢。

對於查詢中引用的表，也可以使用{模式名稱},{表名稱}訪問給定模式中的表。如果沒有提供模式名，則使用與JDBC用戶關聯的默認模式。

用戶

沒有

沒有默認的

Azure Synapse用戶名。必須配合使用嗎密碼選擇。隻有在URL中沒有傳遞用戶和密碼時才能使用。同時傳遞兩者將導致錯誤。

密碼

沒有

沒有默認的

Azure Synapse密碼。必須配合使用嗎用戶選擇。隻有在URL中沒有傳遞用戶和密碼時才能使用。同時傳遞兩者將導致錯誤。

url

是的

沒有默認的

JDBC URLsqlserver設置為子協議。建議使用Azure門戶提供的連接字符串。設置加密= true強烈推薦，因為它支持JDBC連接的SSL加密。如果用戶而且密碼是分開設置的，你不需要在URL中包含它們。

jdbcDriver

沒有

由JDBC URL的子協議決定

要使用的JDBC驅動程序的類名。這個類必須在類路徑上。在大多數情況下，應該沒有必要指定這個選項，因為適當的驅動程序類名應該由JDBC URL的子協議自動確定。

以前支持的jdbc_driverVariant已棄用，在將來的版本中將被忽略。使用“駱駝案例”的名稱。

tempDir

是的

沒有默認的

一個abfssURI。我們建議您為Azure Synapse使用專用的Blob存儲容器。

以前支持的tempdirVariant已棄用，在將來的版本中將被忽略。使用“駱駝案例”的名稱。

tempCompression

沒有

時髦的

Spark和Azure Synapse用於臨時編碼/解碼的壓縮算法。目前支持的值為:未壓縮的，時髦的而且GZIP．

forwardSparkAzureStorageCredentials

沒有

假

如果真正的，庫會自動發現Spark用於連接到Blob存儲容器的憑據，並通過JDBC將這些憑據轉發給Azure Synapse。這些憑證作為JDBC查詢的一部分發送。因此，強烈建議在使用此選項時啟用JDBC連接的SSL加密。

配置存儲鑒權時，必須設置其中之一useAzureMSI而且forwardSparkAzureStorageCredentials來真正的．或者，您可以設置enableServicePrincipalAuth來真正的並將服務原則用於JDBC和存儲身份驗證。

以前支持的forward_spark_azure_storage_credentialsVariant已棄用，在將來的版本中將被忽略。使用“駱駝案例”的名稱。

useAzureMSI

沒有

假

如果真正的，庫將指定身份＝的管理服務身份的也沒有秘密對於它創建的數據庫範圍的憑據。

配置存儲鑒權時，必須設置其中之一useAzureMSI而且forwardSparkAzureStorageCredentials來真正的．或者，您可以設置enableServicePrincipalAuth來真正的並將服務原則用於JDBC和存儲身份驗證。

enableServicePrincipalAuth

沒有

假

如果真正的，庫將使用提供的服務主體憑據通過JDBC連接到Azure存儲帳戶和Azure Synapse Analytics。

如果任何一forward_spark_azure_storage_credentials或useAzureMSI設置為真正的在存儲鑒權時，該選項優先於業務原則。

tableOptions

沒有

集群COLUMNSTORE指數，分布＝ROUND_ROBIN

用於指定的字符串表選項在創建Azure Synapse表時設置數據表．字符串按字麵意義傳遞給與的條款創建表格針對Azure Synapse發出的SQL語句。

以前支持的table_optionsVariant已棄用，在將來的版本中將被忽略。使用“駱駝案例”的名稱。

預作用

沒有

無默認值(空字符串)

一個；在將數據寫入Azure Synapse實例之前，要在Azure Synapse中執行的SQL命令的分離列表。這些SQL命令必須是Azure Synapse接受的有效命令。

如果這些命令中的任何一個失敗，都將被視為錯誤，並且不會執行寫操作。

postActions

沒有

無默認值(空字符串)

一個；在連接器成功地將數據寫入Azure Synapse實例後，將在Azure Synapse中執行的SQL命令的分離列表。這些SQL命令必須是Azure Synapse接受的有效命令。

如果這些命令中的任何一個失敗，它將被視為一個錯誤，並且在成功地將數據寫入Azure Synapse實例後，您將得到一個異常。

maxStrLength

沒有

256

StringType中映射到NVARCHAR (maxStrLength)輸入Azure Synapse。你可以使用maxStrLength為所有對象設置字符串長度NVARCHAR (maxStrLength)鍵入表中具有name的列數據表在Azure Synapse中。

以前支持的maxstrlengthVariant已棄用，在將來的版本中將被忽略。使用“駱駝案例”的名稱。

applicationName

沒有

Databricks-User-Query

每個查詢的連接標記。如果未指定或該值為空字符串，則將標記的默認值添加到JDBC URL中。默認值防止Azure DB監控工具對查詢發出虛假的SQL注入警報。

maxbinlength

沒有

沒有默認的

的列長度BinaryType列。此參數轉換為VARBINARY (maxbinlength)．

identityInsert

沒有

假

設置為真正的使IDENTITY_INSERT模式，它在Azure Synapse表的標識列中插入一個DataFrame提供的值。

看到顯式地將值插入IDENTITY列．

externalDataSource

沒有

沒有默認的

預先配置的外部數據源，用於從Azure Synapse讀取數據。外部數據源隻能與PolyBase一起使用，並刪除CONTROL權限要求，因為連接器不需要創建作用域憑據和外部數據源來加載數據。

有關使用外部數據源時所需的用法和權限列表，請參見需要Azure Synapse權限的PolyBase與外部數據源選項．

maxErrors

沒有

0

在取消加載操作之前，在讀寫期間可以拒絕的最大行數。被拒絕的行將被忽略。例如，如果10個記錄中有2個有錯誤，那麼將隻處理8個記錄。

看到創建外部表中的REJECT_VALUE文檔而且COPY中的MAXERRORS文檔．

inferTimestampNTZType

沒有

假

如果真正的， Azure Synapse類型的值時間戳被解釋為TimestampNTZType(時間戳，不帶時區)。否則，所有時間戳都被解釋為TimestampType不管底層Azure Synapse表中的類型是什麼。

請注意

  • tableOptions，預作用，postActions,maxStrLength隻有在將數據從Databricks寫入Azure Synapse中的新表時才相關。

  • 盡管所有數據源選項名稱都不區分大小寫，但為了清晰起見，我們建議您使用“駝峰大小寫”指定它們。

查詢下推到Azure Synapse

Azure Synapse連接器實現了一組優化規則，將以下操作符推入Azure Synapse:

  • 過濾器

  • 項目

  • 限製

的項目而且過濾器操作符支持以下表達式:

  • 大多數布爾邏輯運算符

  • 比較

  • 基本算術運算

  • 數字和字符串類型轉換

為限製操作符，隻有在沒有指定順序時才支持下推。例如:

選擇(10)＊從表格，但不是選擇(10)＊從表格訂單通過上校．

請注意

Azure Synapse連接器不會下推操作字符串、日期或時間戳的表達式。

默認情況下，使用Azure Synapse連接器構建的查詢下推是啟用的。您可以通過設置禁用它spark.databricks.sqldw.pushdown來假．

臨時數據管理

Azure Synapse連接器不刪除它在Azure存儲容器中創建的臨時文件。Databricks建議定期刪除用戶提供的臨時文件tempDir的位置。

為了方便數據清理，Azure Synapse連接器不直接在下麵存儲數據文件tempDir，而是創建表單的子目錄:< tempDir > / < yyyy-MM-dd > / < HH-mm-ss-SSS > / < randomUUID > /．您可以設置周期作業(使用Databricks . properties)工作功能或其他)遞歸刪除任何子目錄的曆史超過給定的閾值(例如，2天)，假設沒有Spark作業運行的時間超過該閾值。

一個更簡單的替代方法是定期刪除整個容器，並創建一個具有相同名稱的新容器。這要求您為Azure Synapse連接器生成的臨時數據使用專用容器，並且您可以找到一個時間窗口，在該時間窗口中可以保證沒有涉及該連接器的查詢正在運行。

臨時對象管理

Azure Synapse連接器自動在Databricks集群和Azure Synapse實例之間傳輸數據。要從Azure Synapse表讀取數據或查詢數據，或將數據寫入Azure Synapse表，Azure Synapse連接器將創建臨時對象，包括數據庫作用域憑證，外部數據源，外部文件格式,外部表格幕後。這些對象隻在相應的Spark作業期間存在，並且會自動刪除。

當集群使用Azure Synapse連接器運行查詢時，如果Spark驅動程序進程崩潰或強製重新啟動，或者集群強製終止或重新啟動，則可能不會刪除臨時對象。為了便於識別和手動刪除這些對象，Azure Synapse連接器在Azure Synapse實例中創建的所有中間臨時對象的名稱前加上一個這樣的標記:tmp_databricks_ < yyyy_MM_dd_HH_mm_ss_SSS > _ < randomUUID > _ < internalObject >．

我們建議您定期使用以下查詢查找泄漏對象:

  • 選擇＊從sys.database_scoped_credentials在哪裏的名字就像“tmp_databricks_ %”

  • 選擇＊從sys.external_data_sources在哪裏的名字就像“tmp_databricks_ %”

  • 選擇＊從sys.external_file_formats在哪裏的名字就像“tmp_databricks_ %”

  • 選擇＊從sys.external_tables在哪裏的名字就像“tmp_databricks_ %”