Note
Azure Databricks Excel 外掛在 Azure 政府或 Azure 中國地區都無法取得。
Azure Databricks Excel 外掛將你的Azure Databricks工作區連接到 Microsoft Excel,將受管控的 Lakehouse 資料直接帶入試算表。
本頁說明如何使用 Azure Databricks Excel 外掛,從 Excel 匯入並分析 Azure Databricks 的資料。 你可以透過直覺的介面瀏覽並匯入 Azure Databricks 資料表,完全不需要 SQL 知識。 雖然外掛提供執行自訂 SQL 查詢的彈性,但屬於可選功能。
先決條件
- 在使用 Excel 外掛之前,先確認你已經 設定好了。
- 你有 Azure Databricks 的 SQL 存取權限,至少可以對 SQL 倉庫使用權限。
選擇 SQL 倉庫
選擇使用哪個 SQL 倉庫:
- 在 Excel 中 Azure Databricks 外掛窗格的右上角,點擊下拉選單。
- 選擇你想使用的 SQL 倉庫。
從Azure Databricks匯入資料
透過選擇表格、撰寫 SQL 查詢或匯入樞紐分析表,即可在 Excel 中匯入 Azure Databricks 的資料。
Note
你可以透過樞紐分析表、SQL 查詢和自訂函式匯入 Unity Catalog 的度量檢視。
建立樞紐分析表
要從 Unity Catalog 資料表和檢視中在 Excel 中建立樞紐分析表:
在 Azure Databricks Excel 外掛窗格中,於 「新匯入 」標籤下,選擇 「選取資料 」作為 「匯入方法」。
在 目錄中,選擇要用來建立樞紐分析表的資料表,然後點 選擇。
選取 樞紐分析資料 選擇框。
(可選)選擇 即時查詢 以執行查詢,並在建立樞紐分析表時自動更新新工作表。 欲了解更多資訊,請參閱 即時查詢。
透過拖曳每個欄位到正確區域來設定 列、 欄和 值 。
(可選)加裝 過濾器。 欲了解更多有關篩選器的資訊,請參閱 篩選匯入資料。
(可選)為你的匯入設定一個列限制。
匯入你的結果。 請選擇下列一項:
- 點擊 「儲存並匯入 」以儲存查詢,方便在 Excel 工作簿中重複使用並匯入結果。
- 點下箭頭,然後點 匯入結果 ,匯入結果,但不儲存查詢。 當你想繼續編輯匯入時,請使用此選項。
Note
樞紐分析表只能匯入到新的工作表。
在使用樞紐分析表中的 Unity Catalog 指標時,你可能會在結果中看到 Sum(measure)。 這是預期中的行為,且不會發生額外的聚合。 Excel 要求值必須有聚合函數,但因為資料包含唯一值,因此不會發生聚合。
即時查詢
Live query 會執行你的樞紐分析查詢,並在你編輯樞紐時自動更新工作表,所以你在更新欄位時就能看到結果,而不是點 匯入後才看到。 即時查詢會在你更改至少一列或一欄和一個值後,重新執行查詢。 要回到標準流程,請關閉該功能。
啟用即時查詢時,樞紐分析表一律會在新的工作表中建立並更新。 此外,樞軸建構器會顯示「 儲存匯入 」按鈕,取代 匯入。
如果 Excel 在您編輯即時樞紐分析表時關閉,匯入索引標籤會顯示復原選項,讓您可以還原該匯入作業上次儲存的設定。
選擇表格
資料以 Excel 表格物件匯入。 你可以移動資料表或重新命名試算表,Excel 外掛會自動刷新新位置的資料。
要從 Azure Databricks 資料表匯入資料,請執行以下操作:
- 在 Azure Databricks Excel 外掛窗格中,於 「新匯入 」標籤下,選擇 「選取資料 」作為 「匯入方法」。
- 選擇一個表格從目錄檔案總管匯入。 你可以用
依擁有者、認證狀態及其他屬性篩選目錄。
- 按一下 ‹選取›。
- 在 欄位中,點擊向下箭頭,取消選取你不想匯入的欄位,或保留所有欄位以匯入整個表格。
- (可選)加裝 過濾器。 欲了解更多有關篩選器的資訊,請參閱 篩選匯入資料。
- (可選)在 排序中,新增一個或多個欄位以進行排序。 欲了解更多排序資訊,請參閱 「排序匯入資料」。
- (可選)欲查看匯入範例,請點擊 預覽。
- (可選)設定列數限制以限制匯入的列數。
- (可選)要識別你已匯入的資料,請輸入 匯入名稱。
- 在 輸出目的地中,選擇將資料匯入新工作表或目前工作表。 如果你匯入到目前的工作表,資料會從你輸入的格子參考開始(預設為 A1)。
- 匯入你的結果。 請選擇下列一項:
- 點擊 「儲存並匯入 」以儲存查詢,方便在 Excel 工作簿中重複使用並匯入結果。
- 點下箭頭,然後點 匯入結果 ,匯入結果,但不儲存查詢。 當你想繼續編輯匯入時,請使用此選項。
撰寫 SQL 查詢
Write SQL 匯入方法支援 SQL 函式與儲存程序。
要對你的 Azure Databricks 工作區執行自訂 SQL 查詢,請執行以下操作:
在 Azure Databricks Excel 外掛窗格中,在 「新匯入 」標籤下,選擇 「寫入 SQL 」作為 匯入方法。
輸入查詢名稱以便日後辨識。
寫一個新的查詢,或使用你 Azure Databricks 工作空間中的現有查詢。
在編輯器裡寫你的 SQL 查詢。 你可以查詢 Unity 目錄中任何你有權限存取的資料表。
- 點擊
在目錄瀏覽器中查看你的結構和資料表。
- 點擊
若要使用來自 Azure Databricks 工作區的查詢或 Excel 中現有的查詢,請點擊
資料夾。 如果你使用來自 Azure Databricks 工作區的現有查詢,Excel 所做的編輯不會反映在 Azure Databricks 上。
Note
查詢必須先在 Azure Databricks 中,使用查詢編輯器右上角的 Save 按鈕明確儲存,之後才會顯示在 Excel 中。
(可選)要新增查詢參數,請點擊參數旁的 +Add。 點選參數並輸入 參數名稱 與 參數值。
- 參數值可以輸入特定值,或點擊方框與箭頭按鈕指定儲存格參考。 選擇一個或一組儲存格,點擊箭頭即可自動填入參數值。
在 輸出目的地中,選擇將資料匯入新工作表或目前工作表。 如果你匯入到目前的工作表,資料會從你輸入的格子參考開始(預設為 A1)。
要預覽您的查詢結果,請點擊 「執行」。
匯入你的結果。 請選擇下列一項:
- 點擊 「儲存並匯入 」以儲存查詢,方便在 Excel 工作簿中重複使用並匯入結果。
- 點下箭頭,然後點 匯入結果 ,匯入結果,但不儲存查詢。 當你想繼續編輯匯入時,請使用此選項。
你也可以用自訂函式來新增查詢參數。 參見 「寫入 SQL」。
過濾匯入資料
當你選擇表格或建立樞紐分析表匯入資料時,可以套用篩選器來縮小結果範圍。
字串篩選條件不區分大小寫,且可級聯套用。 當你套用多個濾波器時,每個濾波器的可用值會依照前一個濾波器的選擇而有所不同。 例如,如果你依國家篩選,然後在城市加篩選,城市篩選器只會顯示所選國家內的城市。
要設定篩選條件,請點擊 + 「 篩選」旁邊,選擇你想套用篩選器的欄位,然後輸入你的篩選條件。 對於需要數值的篩選器,你可以做以下其中之一:
- 輸入價值。
- 若要產生最多 5,000 個不同濾波器值的清單,可以使用:
- 點選 「數值」,然後 「取得篩選值」。
- 點擊向下箭頭,從列表中選擇一個或多個數值。
- 使用儲存格參考:
- 按一下 儲存格。
- 選擇儲存格或儲存格範圍。
- 按一下游標
。
下表描述每個可用濾波器及其預期輸入。
| Filter | 預期的輸入 | Description |
|---|---|---|
IS NULL |
None | 尋找欄位值為空的列。 |
IS NOT NULL |
None | 尋找欄位值非 null 的列。 |
EQUALS |
一個數字或文字串 | 尋找欄位值與指定值完全相符的列。 |
NOT EQUALS |
一個數字或文字串 | 尋找欄位值與指定值不符的列。 |
STARTS WITH |
一個文字串 | 尋找欄位值以指定文字開頭的列。 |
ENDS WITH |
一個文字串 | 尋找欄位值以指定文字結尾的列。 |
CONTAINS |
一個文字串 | 尋找欄位值在字串中包含指定文字的列。 |
排序匯入資料
Azure Databricks Excel 外掛可依一個或多個欄位,以遞增或遞減順序排序資料表匯入。 樞紐分析表不提供排序功能。 要排序 SQL 查詢匯入的結果,請在查詢中加入一個 ORDER BY 子句。
要新增排序,請點擊「排序」旁邊的,然後選擇欄位。 你可以依表格中的任何欄位排序,包括那些你沒匯入的欄位。 每個欄位只能在排序中出現一次,且新的排序預設是往上升的。
加入排序後,你可以做以下操作:
- 要切換升序與降序,請點擊欄位旁邊的「
」或「
」。
- 要更改排序優先順序,請透過其
控點拖曳欄位。 外掛會依照清單的第一欄排序,然後依其後的每一欄排序。
- 要移除排序,請點擊欄位旁的
。
外掛會在篩選器之後、列限制前套用排序。 如果你設定了資料列限制,匯入會依排序順序包含前幾個資料列。 排序也會在預覽或重新整理匯入時生效,包括 排程刷新。
計算欄位
計算欄位是由現有資料推導出的欄位,例如 profit 由 revenue 和 cost計算出來的。 Excel 外掛不支援使用 Select 資料匯入方法建立計算欄位。 要新增計算欄位,請使用以下方法之一:
- 寫入 SQL:使用 Write SQL 匯入方法計算計算出的欄位,並使用任意 SQL 表達式。 請參見 撰寫 SQL 查詢。
- Genie One:請 Genie One 回傳你所需的計算欄位資料,然後匯入結果。 請參閱 Microsoft Excel 中的 Genie One。
Databricks 建議使用 Genie One 來計算欄位。
在 Excel 中使用 Azure Databricks 自訂函數
Excel 外掛提供了自定義函數,您可以在 Excel 公式中使用這些函數來從 Azure Databricks 匯入資料。
選擇一個資料表
這個 DATABRICKS.Table 函式會從 Unity 目錄資料表匯入資料。
語法:
=DATABRICKS.Table(catalog_name.schema_name.table_name, [column1, ...], [limit])
參數:
-
catalog_name.schema_name.table_name(必備):完全限定的表格名稱。 -
columns(可選):一組欄位名稱陣列以匯入。 匯入所有欄位時,省略此參數。 -
limit(可選):匯入最多列數。 省略此參數以匯入所有資料列,限定為 10 MB 的最大限制。
Example:
=DATABRICKS.Table("main.default.customers", {"customer_id", "customer_name"}, 100)
此公式會從customer_idcustomer_name表格匯入 和 main.default.customers 欄位,限制為 100 列。
撰寫 SQL
這個函式會 DATABRICKS.SQL 執行一個 SQL 查詢,使用查詢參數並回傳結果。
語法:
用數值來指定參數。
=DATABRICKS.SQL("query_text", {parameter1_name, parameter1_value; ...})
用單元範圍來指定參數。 在同一列的儲存格中定義名稱和值參數。
=DATABRICKS.SQL("query_text", {param_name_cell: param_value_cell; ...})
參數:
-
query_text(必要的):要執行的 SQL 查詢。 -
parameters(必填):將參數值映射到查詢中以替換。
Example:
=DATABRICKS.SQL("SELECT * FROM samples.bakehouse.sales_suppliers WHERE longitude > :long_param AND latitude > :lat_param LIMIT 10", {"long_param",20; "lat_param",10})
=DATABRICKS.SQL("SELECT * FROM samples.bakehouse.sales_suppliers WHERE city = :city", M4:N4)
此公式會執行查詢,利用提供的參數值longitude 和latitude來過濾銷售資料。
管理查詢
從匯入頁面管理你現有的匯入。
編輯現有的匯入
要編輯現有的匯入:
- 在 Excel 的 Azure Databricks 外掛窗格中,點選「 匯入」 標籤。
- 找到你想編輯的匯入。
- 點選匯入選項旁邊的三點選單。
- 點擊 編輯 來編輯你的匯入。
刷新資料
Excel 外掛不會自動刷新資料。 你怎麼刷新資料取決於你匯入的方式。 使用匯入方法(選擇資料表、撰寫 SQL 查詢或建立樞紐分析表)匯入的資料會從 匯入 標籤重新整理。使用自訂函式匯入的資料必須重新計算。
使用來自 Azure Databricks 的最新值更新匯入內容。 外掛會重新執行原始查詢或資料表選擇,並更新你的工作表以新增資料:
- 若要重新整理單一匯入:
- 在 Excel 的 Azure Databricks 外掛窗格中,點選「 匯入」 標籤。
- 點選
,在您要重新整理的匯入項目旁邊點選「重新整理」。
- 要刷新所有匯入資料:
- 在Azure Databricks外掛窗格點擊重新整理全部。
Important
在刷新資料時,Excel 外掛會清除指定資料表中所有現有資料,並重新載入 Azure Databricks 中最新的資料。 你新增到表格的任何自訂欄位都會在刷新過程中被刪除。
從自訂函式匯入的資料,如 DATABRICKS.Table 和 DATABRICKS.SQL,在重新開啟工作簿時不會重新整理。 要刷新從自訂函式匯入的資料,請登入 Azure Databricks 外掛,然後重新計算工作簿或更改自訂函式所參考的值。
分享影響
當您分享包含 Azure Databricks 資料的 Excel 工作簿時,請考慮以下資料存取與安全影響:
對匯入資料的可視性
當接收者刷新匯入時,外掛會使用收件人的 Unity 目錄權限。 如果他們無法存取底層資料,刷新就會失敗。
對於資料隱私有疑慮的電子表格,你可以使用以下解決方案:
- 建立一本包含所有必要公式和匯入的活頁簿。
- 刪除從試算表中匯入的資料。
- 將工作簿分享給收件人。
- 讓收件人重新整理資料。
收件人只能根據他們的 Unity 目錄權限看到他們能存取的資料。
存取工作區與資料資產
- 無法存取工作簿中引用的 Unity 目錄物件的使用者無法重新整理資料。 要刷新資料,使用者必須對 Unity 目錄底層的資料表和檢視擁有讀取權限。
- 使用者必須能存取 Azure Databricks 底層的資料表,才能編輯現有匯入資料。
查詢可見性
擁有編輯權限的使用者,即使無法存取 Unity 目錄中的底層資料,也能透過 Azure Databricks 外掛查看產生資料所使用的查詢。
另存為範本的替代方式
Azure Databricks Excel 外掛不支援將工作簿儲存為範本,但你可以分享工作簿,讓其他使用者能看到匯入的查詢。 請參見資料存取與安全考量的 分享影響 。
作為分享工作簿範本的替代方案,可以做以下其中之一:
- 把本地檔案分享給另一個使用者。 收件人可以重新命名檔案並查看已儲存的查詢。
- 在 SharePoint 上,將工作簿分享給其他使用者。 當其他使用者下載檔案時,儲存的匯入檔案會被保留。
Limitations
- 自訂函式:自訂函式的查詢結果因 SQL 執行 API 限制,限制在 25 MiB。
- 資料載入:若工作簿中任何儲存格處於編輯模式,資料載入可能會失敗。
- Excel 桌面列限制:Excel 桌面支援每張工作表最多 1,048,576 列。
- Excel 網頁版檔案大小限制:Excel 網頁版支援最大約 25 MB 的作業簿檔案大小,供檢視與編輯使用。
- 即時查詢效能:開啟 Live 查詢 時,每次 pivot 編輯都會對你的 SQL 倉庫執行一個新查詢。 結果不會在本機快取,因此若經常編輯大型樞紐分析表,可能會增加延遲和資料倉儲成本。