SQL Server 2012 új funkció – File Table

calendar 2012-06-06 · 4 perc olvasás · SQL SQL Server SQL Server 2012  ·
Megosztás: linkedin copy

Alapvetően nem egy új funkció, csak a Filestream továbbfejlesztése. Nekem pl. ez a funkció segít rendbetenni a dokumentumaimat- nesze nekem BigData. Lényegében a FileTable egy adott séma alapján – windows API-k segítségével – visszaadja egy “mappa” tartalmát, a fastruktúra megtartásával.

A FileTable oszlopai:

  • stream_id: ez egy egyedi azonosító a mappára vagy file-ra. Típusa mi más lenne mint uniqueidentifier :-)
  • file_stream: ez maga az adat, mappa, file. Itt érdemes megjegyezni egy nagyon fontos dolgot: a FileStream és a FileTable esetében a 2 GB limit nem - érvényes, mint a varbinary(max) típusra. Lásd BOL: http://technet.microsoft.com/en-us/library/bb933993(SQL.105).aspx
  • name: a file vagy a mappa neve.
  • path_locator: ez egy hierarchyid típusú mező, ami a mappák fastruktúrájában azonosítja egy elem helyét.
  • parent_path_locator: ez mutatja meg, hogy melyik elem áll az adott elem felett a hierarchiában.
  • file_type: fájl típusa, mappa esetén NULL értéket vesz fel. a Full-Text Search esetében használható a TYPE COLUMN megadásakor.
  • cached_file_size: a fájl méretét adja meg. Ez egy számított oszlop.
  • creation_time: a file létrehozásának dátuma, melyet a Windows API ad vissza.
  • last_access_time: a fájl utolsó hozzáférésének/megnyitásának időpontja.
  • is_directory: ha az értéke 1, akkor mappa az adott elem.
  • is_offline: Ha az értéke 1, akkor offline fájl.
  • is_hidden: rejtett fájl, ha az értéke 1.
  • is_readonly: csak olvasható, ha az értéke 1.
  • is_archive: archív, ha az értéke 1.
  • is_system: rendszer file, ha az értéke 1.
  • is_temporary: ideiglenes file, ha az értéke 1.

Hogyan hozható létre FileTable?

A FileTable létrehozása előtt enegdélyezni kell pár dolgot az SQL Server-ben insatnce, majd adatbázis szinten. Az alábbi lépések segítségével felépítek egy egyszerű adatbázist, ahol mindenféle dokumentumokat fogok tárolni, majd keresni is fogok bennük.

Instance szintű beállítások

FILESTREAM engedélyezése

  • Indítsuk el az SQL Server Configuration Manager-t. (itt figyeljünk arra, hogy az SQL Server 2012-höz kiadott verziót indítsuk el!!!)
  • Navigáljunk az SQL Server Services menüpontra.
  • Nyomjunk jobb egérgombot az SQL Server instance-n, majd kattinsunk a Properties menüpontra,
  • SQL Server Configuration Manager-ben engedélyezzük a FILESTREAM-et (csak helyi elérésre van szükségem, így csak azt engedélyeztem)
  • Futtassuk le az alábbi query-t:
1EXEC sp_configure filestream_access_level, 2;
2RECONFIGURE;
3GO

Adatbázis beállítások

A példa egyszerűsítése érdekében létrehozok egy teljesen új adatbázist. A példa adatbázis beállításait nem feltétlenül ajánlom éles környezetben :-)

Adatbázis létrehozása

Az alábbi script segítségével hozzuk létre a minta adatbázist:

 1USE [master];
 2GO
 3IF DATABASEPROPERTYEX('FileTableDB', 'Version') > 0
 4    ALTER DATABASE [FileTableDB] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE;
 5    DROP DATABASE [FileTableDB];
 6GO
 7 
 8CREATE DATABASE [FileTableDB]
 9ON  PRIMARY (NAME = N'FileTableDB', FILENAME = N'C:\Temp\FileTableDB.mdf'),
10FILEGROUP [FILESTREAM_FG] CONTAINS FILESTREAM  DEFAULT (NAME = N'FILESTREAM_FG',
11FILENAME = N'C:\temp\FILESTREAM_STORAGE' , MAXSIZE = UNLIMITED)
12LOG ON (NAME = N'FileTableDB_log', FILENAME = N'C:\temp\FileTableDB_log.ldf')
13GO

Figyeljük meg, hogy van egy FILESTREAM FileGroup is, illetve győződjünk meg arról, hogy létezik a c:\temp mappa és az SQL Server service account-nak van joga a mappához.

Adatbázis szintű beállítások

Az alábbi beállítások szükségesek ahhoz, hogy az adatbázisunk tudja kezelni a FileTable táblákat.

1ALTER DATABASE [FileTableDB] SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME=N'FileTableDB_FileTablesDirectory') WITH NO_WAIT;
2GO

A kódban két fontos opció is felfedezhető a FILESTREAM-el kapcsolatban:

  • NON_TRANSACTED_ACCESS: három állapota van ennek a beállításnak: OFF, READ_ONLY és FULL. Én most a FULL-t állítom be.
  • DIRECTORY_NAME: az a mappa neve, amiben az adatbázishoz tartozó FileTable adatok lesznek letárolva. Nagyon fontos, hogy ennek a mappának a eve - egyedi kell legyen egy instance-en belül. Én azt a névkonvenciót szoktam alkalmazni, hogy <adatbázisnév>_<mappanév>. Így van esély arra, ogy - egyedi lesz. Ezekkel a beállításokkal már neki lehet állni létrehozni a FileTable-t.

A FileTable

Az alábbi kód segítségével létre tudjuk hozni a FileTable-t.

1CREATE TABLE [dbo].[FileTableSample] AS FILETABLE
2ON [PRIMARY] FILESTREAM_ON [FileTableDBFileStreamFG]
3WITH
4(
5FILETABLE_DIRECTORY = N'FileTableSample',
6FILETABLE_COLLATE_FILENAME = database_default
7)

Adatbetöltés

Két módon lehet adatot tenni a FileTable-be:

  • Transact-SQL segítségével és
  • File másolással.

Transact-SQL adatbetöltés

Itt azért ne várjunk csodákat :-) file másolás a legjobb megoldás, az alábbi kód csak szemléltetés:

1INSERT INTO [dbo].[FileTableSample] ([file_stream], [name])
2VALUES (CAST('test text in the file' AS varbinary(max)), 'test.txt')

Hagyományos adatbetöltés

Itt a hagyományos másolás, xcopy, robocopy mehet. Alapvetően egy-egy FileTable-nek van saját elérési útvonala, ami lekérdezhető az alábbiak szerint:

1SELECT FILETABLEROOTPATH('[dbo].[FileTableSample]')

Ez visszaadja az elérési utat, ami az adott FileTable-nek be lett állítva:

\<FileStreamShareName><FileStreamDefaultFolder><YourFileTableFolder>\

Esetemben ez így nézett ki:

\ROADRUNNER\SQL2012\FileTableDB_FileTablesDirectory\FileTableSample

Miután ebbe a mappába bemásoltam pár dokumentumot, a FileTable lekérdezhető:

1SELECT * FROM [FileTableSample]

Vigyázat, a file_stream oszlop visszaadja a file varbinary formátumát! Ez nem igazán jó ötlet, ha lekérdezéseket csinálunk az SSMS-ben, nem igazán szerette nekem sem a 8GB-os file-t 😁