آموزش پردازش فایل های JSON در SQL Server

پردازش فایل‌های JSON در SQL Server: راهنمای جامع

پشتیبانی از فرمت JSON در SQL Server 2016 معرفی شد و از آن زمان به بعد قابلیت‌های آن گسترش یافته است. این پشتیبانی به کاربران SQL Server امکان می‌دهد تا داده‌های JSON را ذخیره، تجزیه، و جستجو کنند. قابلیت‌های کلیدی شامل تولید خروجی JSON از کوئری‌های SQL با استفاده از FOR JSON، اعتبارسنجی JSON با استفاده از ISJSON، و استخراج مقادیر از JSON با JSON_VALUE و JSON_QUERY است. همچنین، OPENJSON یک تابع مجموعه‌ساز قدرتمند است که امکان تبدیل داده‌های JSON به ردیف‌ها و ستون‌های رابطه‌ای را فراهم می‌کند و برای تجزیه فایل‌های JSON بزرگ ایده‌آل است.

برای شروع کار با داده‌های JSON در SQL Server، ابتدا یک جدول برای ذخیره این داده‌ها ایجاد می‌کنیم. این جدول می‌تواند شامل یک ستون از نوع NVARCHAR(MAX) برای نگهداری متن JSON باشد. به عنوان مثال، یک جدول به نام EmployeeData می‌تواند برای ذخیره اطلاعات کارکنان از یک فایل JSON استفاده شود.

CREATE TABLE EmployeeData (
    Id INT IDENTITY(1,1),
    JsonData NVARCHAR(MAX)
);

این جدول به ما امکان می‌دهد تا داده‌های JSON را به صورت خام ذخیره کنیم و سپس با استفاده از توابع JSON آن‌ها را پردازش نماییم. برای وارد کردن داده‌ها از یک فایل JSON محلی، ابتدا باید محتوای فایل را بخوانیم. در اینجا، یک مثال با استفاده از BULK INSERT (در سناریوی واقعی نیازمند خواندن فایل به یک متغیر یا CTE هستیم) یا استفاده از OPENROWSET(BULK…) برای خواندن محتوای فایل و سپس درج آن در جدول EmployeeData ارائه می‌شود. فرض می‌کنیم یک فایل JSON به نام C:\temp\Employee.json داریم که شامل آرایه‌ای از اشیاء JSON است.

INSERT INTO EmployeeData (JsonData)
SELECT BulkColumn
FROM OPENROWSET(BULK 'C:\temp\Employee.json', SINGLE_CLOB) AS JsonData;

پس از درج فایل JSON، می‌توانیم با استفاده از OPENJSON داده‌ها را تجزیه و به فرمت ستونی تبدیل کنیم. این کار به ما امکان می‌دهد تا به راحتی به فیلدهای مختلف درون JSON دسترسی پیدا کرده و آن‌ها را کوئری کنیم.

SELECT J.*
FROM EmployeeData
CROSS APPLY OPENJSON(JsonData)
WITH (
    id INT '$.id',
    firstName NVARCHAR(100) '$.firstName',
    lastName NVARCHAR(100) '$.lastName',
    email NVARCHAR(100) '$.email',
    gender NVARCHAR(50) '$.gender'
) AS J;

در این کوئری، OPENJSON هر شیء در آرایه JSON را به یک ردیف جداگانه تبدیل می‌کند و WITH clause طرح‌واره‌ای را برای استخراج مقادیر مشخص می‌کند.

هنگام کار با فایل‌های JSON، ممکن است با خطاهای تجزیه مواجه شویم، به خصوص اگر فرمت JSON معتبر نباشد. برای مدیریت این خطاها، می‌توانیم از بلوک‌های TRY…CATCH استفاده کنیم. این بلوک‌ها به ما امکان می‌دهند تا خطاهای مربوط به OPENJSON را شناسایی و پیام‌های خطای معنی‌داری را ارائه دهیم.

BEGIN TRY
    SELECT J.*
    FROM EmployeeData
    CROSS APPLY OPENJSON(JsonData)
    WITH (
        id INT '$.id',
        firstName NVARCHAR(100) '$.firstName',
        lastName NVARCHAR(100) '$.lastName',
        email NVARCHAR(100) '$.email',
        gender NVARCHAR(50) '$.gender'
    ) AS J;
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

این رویکرد تضمین می‌کند که حتی در صورت وجود داده‌های JSON نامعتبر، اسکریپت ما به طور کامل از کار نمی‌افتد و اطلاعات مفیدی در مورد خطا ارائه می‌دهد.

برای پردازش فایل‌های JSON از یک مکان از راه دور (مانند یک URL وب)، می‌توانیم از xp_cmdshell در ترکیب با یک اسکریپت PowerShell یا ابزارهای دیگر برای دانلود فایل استفاده کنیم. ابتدا باید xp_cmdshell را فعال کنیم:

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

پس از فعال‌سازی، می‌توانیم از یک دستور PowerShell برای دانلود فایل JSON از راه دور به یک مسیر محلی استفاده کنیم. این دستور می‌تواند از طریق xp_cmdshell اجرا شود.

DECLARE @URL NVARCHAR(MAX) = 'https://api.example.com/employees.json';
DECLARE @FilePath NVARCHAR(MAX) = 'C:\temp\RemoteEmployee.json';
DECLARE @PowerShellCommand NVARCHAR(MAX);

SET @PowerShellCommand = N'powershell -Command "Invoke-WebRequest -Uri ' + QUOTENAME(@URL, '''') + ' -OutFile ' + QUOTENAME(@FilePath, '''') + '"';
EXEC xp_cmdshell @PowerShellCommand;

این دستور فایل JSON را از URL مشخص شده دانلود کرده و آن را در C:\temp\RemoteEmployee.json ذخیره می‌کند. پس از دانلود، می‌توانیم همانند فایل‌های محلی، آن را با OPENROWSET(BULK…) به جدول EmployeeData وارد کنیم و سپس با OPENJSON تجزیه کنیم.

INSERT INTO EmployeeData (JsonData)
SELECT BulkColumn
FROM OPENROWSET(BULK 'C:\temp\RemoteEmployee.json', SINGLE_CLOB) AS JsonData;

SELECT J.*
FROM EmployeeData
CROSS APPLY OPENJSON(JsonData)
WITH (
    id INT '$.id',
    firstName NVARCHAR(100) '$.firstName',
    lastName NVARCHAR(100) '$.lastName',
    email NVARCHAR(100) '$.email',
    gender NVARCHAR(50) '$.gender'
) AS J;

مدیریت خطا برای فایل‌های JSON از راه دور نیز بسیار مهم است، زیرا مشکلات شبکه یا URL نامعتبر می‌توانند منجر به شکست عملیات دانلود یا تجزیه شوند. بلوک TRY…CATCH را می‌توان در اطراف کل فرآیند دانلود و تجزیه اعمال کرد تا هرگونه مشکلی را در حین اجرای فرمان xp_cmdshell یا تجزیه JSON به درستی مدیریت کند.

BEGIN TRY
    -- دانلود فایل
    DECLARE @RemoteURL NVARCHAR(MAX) = 'https://api.example.com/remote_employees.json';
    DECLARE @RemoteFilePath NVARCHAR(MAX) = 'C:\temp\DownloadedRemoteEmployee.json';
    DECLARE @DownloadCommand NVARCHAR(MAX);

    SET @DownloadCommand = N'powershell -Command "Invoke-WebRequest -Uri ' + QUOTENAME(@RemoteURL, '''') + ' -OutFile ' + QUOTENAME(@RemoteFilePath, '''') + '"';
    EXEC xp_cmdshell @DownloadCommand;

    -- درج داده از فایل دانلود شده
    INSERT INTO EmployeeData (JsonData)
    SELECT BulkColumn
    FROM OPENROWSET(BULK @RemoteFilePath, SINGLE_CLOB) AS JsonData;

    -- تجزیه و کوئری داده‌ها
    SELECT J.*
    FROM EmployeeData
    CROSS APPLY OPENJSON(JsonData)
    WITH (
        id INT '$.id',
        firstName NVARCHAR(100) '$.firstName',
        lastName NVARCHAR(100) '$.lastName',
        email NVARCHAR(100) '$.email',
        gender NVARCHAR(50) '$.gender'
    ) AS J;

END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

پس از اتمام کار با xp_cmdshell، توصیه می‌شود برای حفظ امنیت سیستم، آن را غیرفعال کنید.

sp_configure 'xp_cmdshell', 0;
RECONFIGURE;
sp_configure 'show advanced options', 0;
RECONFIGURE;

پردازش فایل‌های JSON در SQL Server با استفاده از OPENJSON و ابزارهای مرتبط، یک راهکار قدرتمند برای یکپارچه‌سازی و تجزیه و تحلیل داده‌های ساختاریافته و نیمه‌ساختاریافته ارائه می‌دهد. این قابلیت‌ها به کاربران امکان می‌دهد تا با حجم زیادی از داده‌های JSON به صورت کارآمد برخورد کنند و آن‌ها را به فرمت‌های قابل استفاده در محیط پایگاه داده تبدیل کنند.