FILESTREAM、FileTable、JSON與R語言 19 FILESTREAM、FileTable、JSON與R語言 19-1 FILESTREAM檔案資料流 19-2 FileTable檔案資料表 19-3 記憶體中OLTP 19-4 使用JSON格式的資料 19-5 SQL Server與R語言

19-1 FILESTREAM檔案資料流 19-1-1 FILESTREAM檔案資料流的基礎 19-1-3 建立FILESTREAM檔案群組

19-1-1 FILESTREAM檔案資料流的基礎-說明 SQL Server可以使用varbinary(max)資料類型儲存「二進位大型物件」（Binary Large Object，BLOB），這些資料主要是文件、影像和視訊等非結構化資料。但是使用上仍然有一些限制。 SQL Server提供FILESTREAM檔案資料流來幫助我們儲存外部BLOB資料的檔案，換句話說，對於BLOB資料來說，可以選擇使用標準varbinary(max)資料類型，儲存在SQL Server資料庫，或使用FILESTREAM檔案資料流，將BOLB資料儲存成檔案系統的檔案，讓SQL Server自動幫助我們維護之間的關聯性。

19-1-1 FILESTREAM檔案資料流的基礎-準則 我們可以使用下列準則來決定是否使用FILESTREAM檔案資料流來儲存BLOB資料，如下所示： 平均的BLOB資料尺寸大於1MB。 儲存的BLOB資料尺寸可能大於2GB。 非常重視資料讀取的速度。 當需要使用中介層程式碼存取BLOB資料時。

19-1-2 啟用FILESTREAM檔案資料流的支援-步驟一 請在SQL Server組態管理員的SQL Server服務上執行【右】鍵快顯功能表的【內容】指令，選【FILESTREAM】標籤，勾選【啟用FILESTREAM的Transact-SQL存取】和【啟用FILESTREAM的檔案I/O存取】後，按【確定】鈕啟用FILESTREAM。

19-1-2 啟用FILESTREAM檔案資料流的支援-步驟二 在使用SQL Server組態管理員啟用FILESTREAM後，接著我們需要使用sp_configure系統預存程序指定FILESTREAM的存取層級。 SQL指令碼檔：Ch19_1_2.sql 請使用系統預存程序變更組態選項來指定FILESTREAM的存取層級，如下所示： EXEC sp_configure filestream_access_level, 2 RECONFIGURE GO

19-1-3 建立FILESTREAM檔案群組- 說明 當SQL Server執行個體啟用FILESTREAM檔案資料流後，就可以使用CREATE DATABASE指令或ALTER DATABASE指令建立與更改資料庫，在FILEGROUP子句加上CONTAINS FILESTREAM選項新增檔案群組來儲存BLOB檔案。

19-1-3 建立FILESTREAM檔案群組- 範例1 SQL指令碼檔：Ch19_1_3_01.sql 請在【教務系統】資料庫新增FILESTREAM檔案群組MyFileStreamGroup，如下所示： ALTER DATABASE 教務系統 ADD FILEGROUP MyFIleStreamGroup CONTAINS FILESTREAM

19-1-3 建立FILESTREAM檔案群組- 範例2 SQL指令碼檔：Ch19_1_3_02.sql 請在【教務系統】資料庫的MyFileStreamGroup檔案群組，新增名為SchoolFileStream的資料檔，如下所示： ALTER DATABASE 教務系統 ADD FILE ( NAME = 'SchoolFileStream', FILENAME = 'C:\Data\SchoolFileStream.ndf' ) TO FILEGROUP MyFileStreamGroup

19-1-4 建立FILESTREAM資料表-說明 當啟用FILESTREAM支援且建立FILESTREAM檔案群組後，我們就可以建立擁有FILESTREAM欄位的資料表，即在資料類型varbinary(max)的欄位加上FILESTREAM屬性。 如果SQL Server資料表擁有FILESTREAM欄位，此時還需要新增uniqueidentifier資料類型加上ROWGUIDCOL屬性的欄位，而且是NOT NULL和UNIQUE。

19-1-4 建立FILESTREAM資料表-範例 SQL指令碼檔：Ch19_1_4.sql 請在【教務系統】資料庫建立擁有FILESTREAM欄位的【文件檔案資料流】資料表，如下所示： CREATE TABLE 文件檔案資料流 ( 文件編號 char(10) NOT NULL PRIMARY KEY, 檔案名稱 varchar(64), 文件 varbinary(max) FILESTREAM NULL, [rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE )

19-1-5 存取FILESTREAM資料-說明 SQL Server可以使用T-SQL語言的SELECT、INSERT、UPDATE和DELETE指令來查詢、新增、更新和刪除擁有FILESTREAM欄位的記錄資料。

19-1-5 存取FILESTREAM資料-新增 SQL指令碼檔：Ch19_1_5_01.sql 請使用INSERT INTO指令在【文件檔案資料流】資料表新增4筆記錄，如下所示： INSERT INTO 文件檔案資料流 VALUES('F001', 'TestNullFile', null, NEWID()) VALUES('F002', 'TestEmptyFile', CAST('' AS varbinary(max)), NEWID()) VALUES('F003', 'VBFile', CAST('Visual Basic' AS varbinary(max)), NEWID()) VALUES('F004', 'NETFile', CAST('.NET Framework' AS varbinary(max)), NEWID())

19-1-5 存取FILESTREAM資料-查詢 SQL指令碼檔：Ch19_1_5_02.sql 請查詢【文件檔案資料流】資料表中【文件】欄位不是NULL的記錄資料，可以顯示BLOB檔案的完整路徑，如下所示： SELECT 文件編號, 文件.PathName() AS 路徑, 檔案名稱 FROM 文件檔案資料流 WHERE 文件 IS NOT NULL

19-2 FileTable檔案資料表 19-2-1 FileTable檔案資料表的基礎 19-2-2 建立FileTable檔案資料表

19-2-1 FileTable檔案資料表的基礎- 說明 FileTable檔案資料表擁有預先建立的欄位定義資料，包含FILESTREAM和檔案屬性，可以讓我們使用SQL Server儲存和管理目前儲存在檔案伺服器中的檔案，換句話說，就是使用SQL Server來管理以檔案儲存的非結構化資料，我們可以將這些資料從檔案伺服器導入SQL Server的FileTable，讓SQL Server提供整合的管理和服務，其特點如下所示： FileTable代表指定路徑的目錄和檔案階層架構，它是從建立FileTable指定的根目錄開始，儲存各階層所有的檔案和子目錄。 FileTable資料表的每一筆記錄是一個檔案或目錄。 SQL Server可以設定FILESTREAM公開FileTable代表的檔案和目錄階層，以便提供Windows應用程式的檔案系統來進行存取。

19-2-1 FileTable檔案資料表的基礎-欄位 欄位名稱 資料類型 說明 stream_id uniqueidentifier ROWGUIDCOL FILESTREAM資料的唯一識別碼 file_stream varbinary(max) FILESTREAM FILESTREAM資料 name nvarchar(255) 檔案或目錄名稱 path_locator hierarchyid 此路徑在階層中的位置 creation_time datetimeoffset(7) 建立檔案的日期與時間 last_write_time 上次更新檔案的日期與時間 last_access_time 上次存取檔案的日期與時間 is_directory bit 是否是目錄 is_offline 是否是離線檔案 is_hidden 是否是隱藏檔案 is_readonly 是否是唯讀檔案 is_archive 是否是封存檔案 is_system 是否是系統檔案 is_temporary 是否是暫存檔案 FileTable檔案資料表欄位的定義資料是固定結構，可以對應NTFS檔案系統的相關屬性，其說明如下表所示：

19-2-2 建立FileTable檔案資料表-說明 在SQL Server資料庫建立FileTable檔案資料表前，我們需要在SQL Server執行個體啟用FILESTREAM檔案資料流的支援，和建立擁有FILESTREAM檔案群組的資料庫，並且啟用FILESTREAM完整非交易存取和指定目錄名稱。

19-2-2 建立FileTable檔案資料表- 建立資料庫 SQL指令碼檔：Ch19_2_2_01.sql 請建立【檔案資料庫】資料庫，包含FILESTREAM檔案群組，如下所示： CREATE DATABASE 檔案資料庫 ON PRIMARY (NAME = 'MyFileTableDB', FILENAME = 'C:\Data\MyFileTableDB.mdf'), FILEGROUP MyFIleTableGroup CONTAINS FILESTREAM (NAME = 'MyFileTableFS', FILENAME = 'C:\Data\MyFileTableFS') LOG ON (NAME = 'MyFileTableDBLog', FILENAME = 'C:\Data\MyFileTableDB_Log.ldf') WITH FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'MyFileTableDB')

19-2-2 建立FileTable檔案資料表- 建立FileTable檔案資料表 SQL指令碼檔：Ch19_2_2_03.sql 請在【檔案資料庫】資料庫建立名為【檔案資料表】的FileTable檔案資料表，如下所示： CREATE TABLE 檔案資料表 AS FILETABLE WITH (FileTable_Directory = 'FileTableTb_Dir') CREATE TABLE指令使用AS FILETABLE子句表示建立FileTable檔案資料表，FileTable_Directory可以指定FileTable資料表的目錄名稱，沒有指定，預設使用FileTable名稱。

19-2-3 在FileTable檔案資料表新增資料-說明 在成功建立FileTable檔案資料表後，我們有2種方式新增儲存的檔案資料，一是直接拖拉複製檔案資料；一是使用T-SQL指令。

19-2-3 在FileTable檔案資料表新增資料-方法一 請在Management Studio展開【檔案資料庫】下，位在【FileTable】下的FileTable資料表，在【檔案資料表】上執行【右】鍵快顯功能表的【瀏覽FileTable目錄】指令，可以開啟FileTable資料表的目錄，請直接複製檔案至此目錄，以此例是FileTable03.txt，就可以新增【檔案資料表】資料表的記錄資料。

19-2-3 在FileTable檔案資料表新增資料-方法二(a) SQL指令碼檔：Ch19_2_3_01.sql 請在【檔案資料表】的FileTable檔案資料表新增2筆記錄資料，分別是FileTable01.txt和FileTable02.txt檔案，如下所示： INSERT INTO 檔案資料表 ( name, file_stream ) SELECT 'FileTable01.txt', * FROM OPENROWSET( BULK N'D:\SQLServer2012\Ch19\FileTable01.txt', SINGLE_BLOB) AS FILEDATA GO

19-2-3 在FileTable檔案資料表新增資料-方法二(b) INSERT INTO 檔案資料表 ( name, file_stream ) SELECT 'FileTable02.txt', * FROM OPENROWSET( BULK N'D:\SQLServer2012\Ch19\FileTable02.txt', SINGLE_BLOB) AS FILEDATA GO

19-2-3 在FileTable檔案資料表新增資料-查詢 SQL指令碼檔：Ch19_2_3_02.sql 請查詢【檔案資料表】資料表的內容，可以顯示儲存檔案的內容和路徑，如下所示： SELECT [name], [file_type], CAST([file_stream] AS VARCHAR) AS FileContent, [cached_file_size], [file_stream].PathName() AS 路徑 FROM 檔案資料表

19-3 記憶體中OLTP 19-3-1 建立記憶體中OLTP的資料庫 19-3-2 原生編譯的預存程序

19-3-1 建立記憶體中OLTP的資料庫 - 說明 OLTP（On-line Transaction Processing）線上交易處理系統是一種處理每天大量交易資料的資料庫系統。因為企業或組織使用資料庫系統的主要目的，就是處理每天大量的交易資料，傳統正規化的關聯式資料庫能夠最佳化交易處理，即更有效率的執行插入、更新和刪除操作。 記憶體中OLTP是使用記憶體來最佳化線上交易處理系統的資料庫，將記錄資料儲存在記憶體空間，而不是儲存在硬碟磁區的分頁。換句話說，記錄資料的分頁不用預先載入資料庫緩衝區，因為這些資料是長存在記憶體中最佳化的資料表，可以大幅提昇系統的執行效能。 SQL Server記憶體中OLTP引擎是在FILESTREAM檔案群組建立一組檢查點的檔案，可以用來追縱資料的改變，因為儲存的變更資料只能新增，可以在之後發生問題時，用來還原和回復資料庫的資料。

19-3-1 建立記憶體中OLTP的資料庫 - 建立記憶體最佳化資料庫（1） 在建立記憶體最佳化資料表之前，我們需要在FILESTREAM檔案群組建立資料庫，和在CREATE DATABASE指令加上CONTAINS MEMORY_OPTIMIZED DATA。 SQL指令碼檔：Ch19_3_1_01.sql 請建立名為【產品記憶體內OLTP】的記憶體最佳化資料庫，擁有2個檔案群組、3個資料檔和1個交易記錄檔，如下所示： CREATE DATABASE 產品記憶體內OLTP ON PRIMARY ( NAME='產品記憶體內OLTP', FILENAME= 'C:\Data\產品記憶體內OLTP.mdf', SIZE=5MB ), FILEGROUP 產品記憶體內OLTP_群組 CONTAINS MEMORY_OPTIMIZED_DATA

19-3-1 建立記憶體中OLTP的資料庫 - 建立記憶體最佳化資料庫（2） ( NAME = '產品記憶體內OLTP_群組_11', FILENAME = 'C:\Data\產品記憶體內OLTP_群組_11.ndf', MAXSIZE=10MB ), ( NAME = '產品記憶體內OLTP_群組_12', FILENAME = 'C:\Data\產品記憶體內OLTP_群組_12.ndf', MAXSIZE=10MB ) LOG ON ( NAME='產品記憶體內OLTP_log', FILENAME = 'C:\Data\產品記憶體內OLTP_log.ldf', SIZE=1MB, MAXSIZE=10MB, FILEGROWTH=10% )

19-3-1 建立記憶體中OLTP的資料庫 - 建立記憶體最佳化資料庫（3） 在建立【產品記憶體內OLTP】資料庫後，請開啟資料庫的屬性視窗，並切換至【檔案群組】頁面，就可以在右下方「記憶體最佳化資料」區段看到建立的FILESTREAM檔案群組，如右圖所示：

19-3-1 建立記憶體中OLTP的資料庫 -建立記憶體最佳化資料表（1） 當記憶體最佳化資料庫擁有FILESTREAM檔案群組後，就可以建立記憶體最佳化資料表，同樣也是使用CREATE TABLE指令。請注意！因為記憶體最佳化資料表並不能使用CREATE INDEX指令建立索引資料，所以在建立資料表的同時就需要在欄位建立索引。 SQL指令碼檔：Ch19_3_1_02.sql 請在【產品記憶體內OLTP】資料庫建立【廠商】資料表，擁有4個欄位，並且同時建立【廠商編號】和【廠商名稱】這2個欄位的索引，如下所示： CREATE TABLE 廠商 ( 廠商編號 int NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 10000), 廠商名稱 nvarchar(100) COLLATE Chinese_Taiwan_Stroke_BIN2 NOT NULL

19-3-1 建立記憶體中OLTP的資料庫 -建立記憶體最佳化資料表（2） INDEX 廠商名稱_索引 HASH WITH (BUCKET_COUNT = 10000) , 聯絡人 nvarchar(20) NULL, 開始交易日期 datetime NULL ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)

19-3-2 原生編譯的預存程序－說明 SQL Server記憶體最佳化資料庫一樣可以執行傳統T-SQL指令或預存程序，除此之外，記憶體中OLTP引擎還支援全新類型的預存程序，稱為原生編譯的預存程序（Natively Compiled Stored Procedure）。 原生編譯的預存程序是使用Visual C/C++編譯器編譯成原生程式碼，而且只能使用在記憶體最佳化資料表，請注意！在傳統資料表並不能使用原生編譯的預存程序。

19-3-2 原生編譯的預存程序 － 使用Management Studio建立 請啟動Management Studio，在「物件總管」視窗展開【產品記憶體內OLTP】資料庫下的【可程式性】項目，在【預存程序】上執行【右】鍵快顯功能表的「新增>原生編譯的預存程序」指令，可以看到新增查詢編輯視窗的預存程序範本。

19-3-2 原生編譯的預存程序 － 新增查詢來建立（1） 我們可以直接按【新增查詢】鈕新增查詢編輯視窗後，自行輸入建立原生編譯預存程序的T-SQL指令敘述。 SQL指令碼檔：Ch19_3_2_01.sql 請建立名為【新增廠商】的原生編譯預存程序，可以在【廠商】資料表新增一筆記錄，如下所示： CREATE PROCEDURE 新增廠商 @F_ID int, @F_Name nvarchar(100), @F_Contacts nvarchar(20), @F_Since datetime WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER AS BEGIN ATOMIC WITH (

19-3-2 原生編譯的預存程序 － 新增查詢來建立（2） TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'Traditional Chinese' ) INSERT INTO dbo.廠商 (廠商編號, 廠商名稱, 聯絡人,開始交易日期) VALUES (@F_ID, @F_Name, @F_Contacts, @F_Since) END GO

19-3-2 原生編譯的預存程序 － 新增查詢來建立（3） SQL指令碼檔：Ch19_3_2_02.sql 在建立名為【新增廠商】的原生編譯預存程序後，就可以執行此預存程序來新增一筆記錄，如下所示： EXEC 新增廠商 @F_ID = 2001, @F_Name = N'好巿多', @F_Contacts = N'李四', @F_Since = '2001-04-01'

19-4 使用JSON格式的資料 19-4-1 認識JSON 19-4-2 剖析JSON資料 19-4-3 將資料表輸出成JSON資料 19-4-4 在SQL Server儲存JSON資料

19-4-1 認識JSON – 說明 JSON是Douglas Crockford創造的一種輕量化資料交換格式，使用大括號定義成對的鍵和值（Key-value Pairs），相當於是物件的屬性和值，如下所示： { "key1": "value1", "key2": "value2", "key3": "value3", … }

19-4-1 認識JSON – JSON語法規則 JSON語法並沒有關鍵字，其基本語法規則，如下所示： 資料是成對的鍵和值（Key-value Pairs），使用「:」符號分隔。 資料之間是使用「,」符號分隔。 使用大括號定義物件。 使用方括號定義物件陣列。

19-4-1 認識JSON – JSON鍵和值 JSON資料是成對的鍵和值（Key-value Pairs），其格式是由欄位名稱開始，接著「:」符號後，再加上值，如下所示： "author": "陳會安" 上述"author"是欄位名稱，"陳會安"是值，JSON值可以是整數、浮點數、字串（使用「"」括起）、布林值（true或false）、陣列（使用方括號括起）和物件（使用大括號括起）。

19-4-1 認識JSON – JSON物件 JSON物件是使用大括號包圍的多個JSON鍵和值，如下所示： { "title": "ASP.NET網頁設計", "author": "陳會安", "category": "Web", "pubdate": "06/2015", "id": "W101" }

19-4-1 認識JSON – JSON物件陣列 JSON物件陣列可以擁有多個JSON物件，例如："Employees"欄位值是一個物件陣列，共有3個JSON物件，如下所示： { "Boss": "陳會安", "Employees": [ { "name" : "陳允傑", "tel" : "02-22222222" }, { "name" : "江小魚", "tel" : "03-33333333" }, { "name" : "陳允東", "tel" : "04-44444444" } ] }

19-4-2 剖析JSON資料 – 內建函數 對於JSON資料的字串或資料表的欄位值，SQL Server提供內建函數來處理JSON資料，可以剖析單一值、JSON物件和陣列，如下表所示： 內建函數 說明 ISJSON(jsonString) 判斷參數字串是否是合法JSON資料，參數也可以是NVARCHAR欄位值 JSON_VALUE(jsonString, path) 剖析第1個參數的JSON資料，取出第2個參數路徑的單一值 JSON_QUERY(jsonString, path) 剖析第1個參數的JSON資料，取出第2個參數路徑的JSON物件或陣列

19-4-2 剖析JSON資料 –路徑字串 參數path是JSON路徑字串，可以在JSON資料定位出欲剖析的資料，如下表所示： JSON路徑 說明 $ 參考整個JSON資料的JSON物件 $.property 參考JSON物件的property屬性 $[3] 參考JSON物件陣列的第4個元素，索引值是從0開始 $.property1.property2.arr1[2].property3 參考JSON物件中多層結構的property3屬性

19-4-2 剖析JSON資料 – 儲存JSON資料的字串 宣告T-SQL變數來儲存JSON資料的字串，如下所示： DECLARE @jsonString NVARCHAR(4000) SET @jsonString = N'{ "info" : { "type" : 1, "address" : { "road" : "中正路", "city" : "新北巿", "country" : "台灣" }, "hobbies" : ["運動", "音樂", "打電動"] "type" : "基本" }'

19-4-2 剖析JSON資料 – 判斷是否是合法JSON資料 SQL指令碼檔：Ch19_4_2_01.sql 在宣告和指定T-SQL變數@jsonString和@jsonString2值後，@jsonString是合法JSON資料；@jsonString2並不是JSON資料，然後使用ISJSON()函數判斷是否是合法JSON資料，如下所示： DECLARE @jsonString2 NVARCHAR(4000) SET @jsonString2 = N'This is a JSON data.' SELECT ISJSON(@jsonString) AS 是否是JSON, ISJSON(@jsonString2) AS 是否是JSON

19-4-2 剖析JSON資料 – 取出指定JSON路徑的單一值 SQL指令碼檔：Ch19_4_2_02.sql 在宣告和指定T-SQL變數@jsonString值後，使用JSON_VALUE()函數取出指定JSON路徑的單一值，如下所示： SELECT JSON_VALUE(@jsonString, '$.type') AS 類型, JSON_VALUE(@jsonString, '$.info.address.city') AS 城巿

19-4-2 剖析JSON資料 – 取出指定JSON路徑的單一值 SQL指令碼檔：Ch19_4_2_03.sql 在宣告和指定T-SQL變數@jsonString值後，使用JSON_QUERY()函數取出指定JSON路徑的JSON物件與陣列，如下所示： SELECT JSON_QUERY(@jsonString, '$.info.hobbies') AS 興趣, JSON_QUERY(@jsonString, '$.info.address') AS 地址

19-4-3 將資料表輸出成JSON資料 SQL Server可以將SELECT資料表的查詢結果輸出成JSON格式的資料，我們只需在SELECT指令最後加上FOR JSON PATH和FOR JSON AUTO子句，就可以輸出JSON資料，其說明如下所示： FOR子句 說明 FOR JSON PATH 使用欄位名稱來輸出JSON資料，如果有使用逗號分隔的欄位名稱，JSON屬性就會依序轉換 FOR JSON AUTO 依據關聯自動輸出成階層結構的JSON物件陣列

19-4-3 將資料表輸出成JSON資料- 範例1 SQL指令碼檔：Ch19_4_3_01.sql SELECT * FROM 學生 FOR JSON PATH

19-4-3 將資料表輸出成JSON資料- 範例2 SQL指令碼檔：Ch19_4_3_02.sql SELECT 學號, 姓名, 生日 FROM 學生 FOR JSON PATH

19-4-3 將資料表輸出成JSON資料- 範例3 SQL指令碼檔：Ch19_4_3_03.sql SELECT 學生.學號, 學生.姓名, 班級.課程編號, 班級.教授編號 FROM 學生 INNER JOIN 班級 ON 學生.學號 = 班級.學號 FOR JSON AUTO

19-4-4 在SQL Server儲存JSON資料- 建立資料表 因為JSON資料就是一個文字內容的字串，我們可以使用SQL Server的NVARCHAR(MAX)型態欄位來儲存JSON資料。 SQL指令碼檔：Ch19_4_4_01.sql 請在【教務系統】資料庫建立【個人】資料表來儲存JSON資料，如下所示： CREATE TABLE 個人 ( 編號 int IDENTITY PRIMARY KEY NONCLUSTERED, 姓名 nvarchar(100) NOT NULL, JSON資料 nvarchar(max) )

19-4-4 在SQL Server儲存JSON資料- 新增一筆記錄 SQL指令碼檔：Ch19_4_4_02.sql 請在【個人】資料表新增一筆記錄，變數@jsonString值是JSON格式的字串，如下所示： INSERT INTO 個人 ( 姓名, JSON資料 ) VALUES ('陳會安', @jsonString)

19-4-4 在SQL Server儲存JSON資料- 新增條件約束 SQL指令碼檔：Ch19_4_4_03.sql 請修改【個人】資料表新增條件約束，使用ISJSON()函數來檢查欄位值是否是合法的JSON資料，如下所示： ALTER TABLE 個人 ADD CONSTRAINT 合法JSON資料_條件 CHECK (ISJSON(JSON資料) > 0)

19-5 SQL Server與R語言 19-5-1 啟用SQL Server的R服務 19-5-2 R語言的基本語法

19-5 SQL Server與R語言 R語言（R Language）是一種統計的程式語言和開發環境，這是貝爾實驗室John Chambers領導開發的GNU專案，內建多種統計和繪圖技術，可以幫助我們進行資料的統計分析，也是目前巨量資料或大數據分析的主要程式語言之一。 SQL Server可以在T-SQL指令內嵌R語言的腳本程式，然後使用SQL Server的R服務來執行R腳本程式來進行統計分析。

19-5-1 啟用SQL Server的R服務 在第4章安裝SQL Server因為是勾選全部功能，所以我們已經安裝好【R Services (資料庫內)】功能，這就是R服務，現在，我們只需啟用SQL Server的R服務，就可以在T-SQL指令使用R腳本程式。

19-5-2 R語言的基本語法-說明 因為本書內容是SQL Server並不是R語言，所以這一節筆者只準備簡單說明R語言，其進一步說明請自行參閱R語言的相關書籍。 首先，請執行Ch19_5_2_01.sql建立名為【R資料】的測試資料表，擁有主鍵欄位【列1】和3筆記錄，如下圖所示：

19-5-2 R語言的基本語法- 使用R腳本產生查詢結果(1) 我們可以使用R腳本顯示資料表的查詢結果（SQL指令碼檔案：Ch19_5_2_02.sql），如下所示： EXEC sp_execute_external_script @language = N'R', @script = N'OutputDataSet<-InputDataSet;', @input_data_1 = N'SELECT * FROM [R資料];' WITH RESULT SETS (([計數] int NOT NULL));

19-5-2 R語言的基本語法- 使用R腳本產生查詢結果(2) 更進一步，我們可以使用R函數來產生輸出資料，而不用指定@input_data_1參數值（SQL指令碼檔案：Ch19_5_2_03.sql），如下所示： EXEC sp_execute_external_script @language = N'R', @script = N'OutputDataSet<-data.frame(c("Mary"), c("Joe"), c("Jason"));', @input_data_1 = N' ' WITH RESULT SETS (([玩家1] varchar(10),[玩家2] varchar(10), [玩家3] varchar(10)));

19-5-2 R語言的基本語法- 隱含型態轉換 因為R語言和SQL Server資料型態並不相同，在執行運算時，R語言會隱含型態轉換成相同型態後，再執行運算，例如：矩陣相乘（SQL指令碼檔案：Ch19_5_2_04.sql），如下所示： EXEC sp_execute_external_script @language = N'R', @script = N' a <- as.matrix(InputDataSet); b <- array(12:15); OutputDataSet<-as.data.frame(a %*% b);', @input_data_1 = N'SELECT 列1 FROM [R資料];' WITH RESULT SETS (([列1] int, [列2] int, [列3] int, [列4] int));

19-5-2 R語言的基本語法- 使用R語言的內建函數 (1) R語言提供功能強大的內建函數，例如：使用rnorm()函數產生10個亂數值（SQL指令碼檔案：Ch19_5_2_05.sql），如下所示： EXEC sp_execute_external_script @language = N'R', @script = N'OutputDataSet<-as.data.frame(rnorm(10, mean = 50));', @input_data_1 = N' ' WITH RESULT SETS (([亂數值] float NOT NULL));

19-5-2 R語言的基本語法- 使用R語言的內建函數 (2) 我們也可以使用R語言的工具函數，例如：使用memory.limit()函數取得目前環境的可用記憶體空間（SQL指令碼檔案：Ch19_5_2_06.sql），如下所示： EXEC sp_execute_external_script @language = N'R', @script = N' library(utils); OutputDataSet<-as.data.frame(memory.limit());', @input_data_1 = N' ' WITH RESULT SETS (([記憶體] int NOT NULL));