1. 什么是结构化数据和非结构化数据?
在讨论SQL Server与非结构化数据的构建之前,我们需要先了解什么是结构化数据和非结构化数据。
1.1 结构化数据
结构化数据是指按照某种结构化的、规范化的格式进行组织和存储的数据。它具有如下特点:
数据的类型及其格式在存储过程中明确定义
数据具有固定的字段名和数据类型
数据之间存在明确的关系,并可以建立关系型数据库
结构化数据的一个典型例子是关系型数据库中的数据,如 SQL Server、Oracle、MySQL 等。
1.2 非结构化数据
非结构化数据则是指不按照某种固定格式进行组织和存储的数据,包括但不限于:
文本文件、Word 文档等
音频、视频文件
图片、PDF 文件等
社交媒体数据、日志文件等
这些数据的类型、格式、语义都没有固定的规范,导致无法使用传统的关系型数据库进行存储和管理。
2. 如何在 SQL Server 中存储非结构化数据?
虽然 SQL Server 是一种关系型数据库管理系统,但是它也支持在数据库中存储非结构化数据。下面介绍三种 SQL Server 中存储非结构化数据的方式。
2.1 使用文件表
在 SQL Server 中,可以使用文件表(file table)来存储文件。文件表定义了一个目录,用于存储文件系统中的文件。因此,文件表可以提供表格和文件系统访问的结合体。
现假设我们需要存储一些文档文件和图片等非结构化数据,可以创建一个文件表来进行存储。
--创建文件表
CREATE TABLE FileTableExample
AS FILETABLE
(
FileTableID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY
DEFAULT NEWID(),
FileTableDirectory NVARCHAR(200) NOT NULL,
CONSTRAINT UNQ_FileTableName UNIQUE (FileTableDirectoryCollate BIN2_COLLATION))
WITH
(
FileTable_Directory = 'FileTableShare',
FileTable_Collate_Filename = 'BIN2',
FileTable_StreamID = 'FileStreamGUID',
FileTable_FULLPATH_UNIQUE_CONSTRAINT_NAME = 'UQ_FileTableExample_FULLPATH')
GO
--插入文档文件
INSERT INTO FileTableExample (name, file_stream)
SELECT 'Document1.docx', * FROM OPENROWSET(
BULK N'C:\FileTableShare\Document1.docx', SINGLE_BLOB) AS document
GO
--插入图片文件
INSERT INTO FileTableExample (name, file_stream)
SELECT 'Picture1.jpg', * FROM OPENROWSET(
BULK N'C:\FileTableShare\Picture1.jpg', SINGLE_BLOB) AS picture
GO
这样,我们就可以使用文件表来存储非结构化数据了。
2.2 使用 FileStream
FileStream 是 SQL Server 提供的一种文件流存储技术,它可以把非结构化数据存储到文件系统中,同时数据库仍然可以通过 Transact-SQL 和 ADO.NET 的方式访问这些数据。
与文件表不同,FileStream 不需要将整个文件存储在数据库中,而是只存储文件的指针及其元数据。这样做的好处在于,可以大大减少数据库的存储量,同时也不会影响文件系统的性能。
--创建 FileStream
USE master;
--检查是否启用了 FileStream
EXEC sp_configure 'filestream_access_level'
--如果返回结果不为2,即需要修改配置,下面是修改配置的步骤
EXEC sp_configure 'filestream_access_level', 2
RECONFIGURE
GO
--创建文件组
ALTER DATABASE TestDB
ADD FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM
GO
--在文件组上创建文件
ALTER DATABASE TestDB
ADD FILE
(
NAME = FileStreamDBFile1,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\FileStreamDB1.ndf',
SIZE = 10MB
)
TO FILEGROUP FileStreamGroup1
GO
--创建表格,并启用 FileStream
USE TestDB;
CREATE TABLE TestFileStream
(
ID INT PRIMARY KEY,
Name VARCHAR(50),
FileStreamFile VARBINARY(MAX)
)
FILESTREAM_ON FileStreamGroup1
GO
使用 FileStream 时,数据库会在指定文件夹下创建一个文件夹,用于存储文件流。我们可以将文件流保存到这个文件夹内,然后通过 Transact-SQL 和 ADO.NET 来访问这些数据。
2.3 使用 Varbinary(max)
如果你不想使用上述两种方法来存储非结构化数据,可以选择在 SQL Server 中使用 varbinary(max) 数据类型。varbinary(max) 类型可以存储最大长度为 2GB 的数据。
--创建表格
CREATE TABLE NonStructuredData
(
ID INT PRIMARY KEY,
Content VARBINARY(max) NOT NULL
)
--插入数据
INSERT INTO NonStructuredData (ID, Content)
SELECT 1, * FROM OPENROWSET(
BULK N'C:\temp\largefile.bin', SINGLE_BLOB) AS document
这种方法较为简单,但是缺点也很明显,那就是无法对该数据进行有效的分析和管理。
3. 结语
在实际的应用中,我们常常需要处理各种格式的数据,包括结构化数据和非结构化数据。通过了解 SQL Server 的一些方法,我们可以将这些非结构化数据存储在数据库中,并使用 SQL Server 提供的分析、管理和其他功能进行处理。