Azure SQL Database 可以讀取Azure Blob 的檔案內容

Edward Kuo
May 10 · 7 min read
Image for post
Image for post

通常我們都會直接把資料送進SQL Database中,但是有時候,有些資料是會直接存在Azure Blob內,當有需要的時候,就會想要把在Blob內的檔案給匯入到SQL Database裡面。要達到這方式很多種,可以像似:

  • 使用Azure Data Factory
  • 使用Application 抓取內容匯入

而另一個方法就是直接在Azure SQL Database直接去抓取檔案內容,並匯入到資料庫的資料表裡面。另外一個應用方式在於,由於,本身要查詢Blob內的檔案內容其實並不容易,雖然可以開發Application讀取,不過,有時候,臨時要做資料分析時候,也可以透過這方式做到資料分析。

建立EXTERNAL DATA SOURCE

  • 建立MASTER KEY
  • 建立DATABASE SCOPED CREDENTIAL
  • 建立EXTERNAL DATA SOURCE

下面是完整建立的SQL語法,這三段是有順序性,必須依次建立完成

  • 建立Master Key的密碼可以自己定義
  • 這裡與blob連線溝通,需要採用是共用存取簽章 (SAS),取得SAS字串必須從問號之後開始取得,這一點要特別注意,這一段需要修改只有Secret那邊要換成自己的SAS Key
Image for post
Image for post
  • SAS授予的權限最少要如下圖,才可以讀取檔案,不然會出現存取被拒
Image for post
Image for post
  • 建立外部資源部分,在Location設定該Blob URL,如果檔案是放在Blob 容器內,則可以自己加入容器名稱,如: https://XXX.blob.core.windows.net/data

這三個資源一旦被建立後,如果沒有刪除,再建立則會顯示錯誤,說已經有該資源被建立,因此,必須把這資源給刪除才可以建立相同的資源,刪除的方式則和建立方式的順序要相反才可以,不然會無法刪除

  • 刪除EXTERNAL DATA SOURCE
  • 刪除DATABASE SCOPED CREDENTIAL
  • 刪除MASTER KEY

讀取Blob Json檔案

主要是透過openrowset方式把檔案資料給讀取進來,DataSource部分就是要讀取的檔案名稱,如果在先前設定Blob URL中,沒有多設定容器路徑,在這裡就要加上容器的路徑,不然會找不到檔案

例如: data/XXX.json

而這邊我設定讀取資料方式是SINGLE_CLOB,因此,讀進來的資料會長成下圖這樣

Image for post
Image for post

不過,這樣也沒辦法查詢或是匯入資料表啊?這時候就要透過openjson方式,把這一欄的Json字串轉換成表格。

藉由這個方式,出來的資料就會是資料表的樣子,後續要做其他處理都是可以,但是,有一點需要注意地方,Json內容每筆資料如果是行列格式而不是一個集合格式,這樣做法只會讀出一筆資料。所以,要解決這個問題,就是讀取內容的同時必須把格式轉換成Json Array的格式

因為在SQL語法中,要取代\r\n,不是直接用replace '\r' 或是 '\n' ,而必須要用CHAR(13)跟CHAR(10),前者代表'\r' 後者代表'\n' ,另外,如果又用@Result變數來接回傳字串又會發生一個問題,就是檔案如果太大,會超過變數長度限制,所以,可以改寫成這樣

就可以不需要擔心變數長度限制,導致轉換的資料不完整了

關於SINGLE_CLOB、SINGLE_NCLOB和SINGLE_BLOB相關介紹

SINGLE_BLOB :將 data_file 的內容當作 varbinary(max) 類型的單一資料列、單一資料行資料列集加以傳回。

SINGLE_NCLOB:以 UNICODE 格式讀取 data_file,並且使用目前資料庫的定序,將內容當做 varchar(max) 類型的單一資料列、單一資料行資料列集加以傳回

SINGLE_CLOB: 以 ASCII 格式讀取 data_file,並且使用目前資料庫的定序,將內容當做 varchar(max) 類型的單一資料列、單一資料行資料列集加以傳回。

讀取Blob CSV檔案

這個方式主要目的就是把CSV內資料每個欄位都要能Mapping到資料表中,切記資料表的欄位數量,都必須和CSV內的欄位數量一致才可以,最好連欄位順序設計時,也要跟原本CSV內的欄位一致,後續才可以比較好比對。

要是不想先用Bulk方式將資料放到資料表中,就必須建立格式檔案,像是.fmt檔案或是XML檔案,放在FORMATFILE 這邊,範例如下:

SELECT Name, Color, Price, Size, Quantity, Data, TagsFROM OPENROWSET(BULK ‘product.bcp’, DATA_SOURCE = ‘MyAzureBlobStorage’, FORMATFILE=’product.fmt’, FORMATFILE_DATA_SOURCE = ‘MyAzureBlobStorage’) as products;

不過,要建立fmt檔案也不一定會比較簡單,是需要一些步驟才有辦法建立的。所以,那一種比較好,就可能要看當下情境來決定

因此,透過這樣方式就能快速讀取這些外部資料的檔案,並匯入到資料表

參考資料

EK.Technology Learn

Design,Thinking,Coding & have fun every thing

Edward Kuo

Written by

Enterprise IT Manager / Microsoft Regional Director / Microsoft MVP / DevOps Expert / Speaker, About me: https://profile.edwardkuo.dev/about/

EK.Technology Learn

Design,Thinking,Coding & have fun every thing

Edward Kuo

Written by

Enterprise IT Manager / Microsoft Regional Director / Microsoft MVP / DevOps Expert / Speaker, About me: https://profile.edwardkuo.dev/about/

EK.Technology Learn

Design,Thinking,Coding & have fun every thing

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store