Как импортировать JSON хранилища данных Azure в Azure SQL Server

Question

{
    "Events":    
        [
            {
            "dataOne":7.5555555555555555,    
            "dataTwo":7.5555555555555555,    
            "dataFive":1025,
            "dataSix":0,
            "dataSeven":1025,
            "dateTimeLocal":1234567890,     
            "dateTimeUTC":1234567890         
            }
        ],
    "infoType":"type1",
    "deviceID":"00000000000000000",         
    "dateTimeLocal":1234567890,
    "dateTimeUTC":1234567890,
    "EventProcessedUtcTime":"20xx-0x-xxT0x:0x:x.4781329Z",
    "PartitionId":1,
    "EventEnqueuedUtcTime":"20xx-0x-xxT0x:0x:x.3850000Z"
}

Вот JSON-запись моих данных, как мне определить таблицу sql в соответствии с ней и как импортировать данные JSON в Azure SQL Server: (

Answer 1

Вы можете использовать Azure Data Factory Copy Activity для прямой передачи ваших BLOB-данных на сервер SQL. Настройте вход как соединитель хранилища BLOB-объектов Azure, а вывод - как соединитель базы данных SQL Azure.

О сопоставлении столбцов см. По этой ссылке: https://docs.microsoft.com/en-us/azure/data-factory/copy-activity-schema-and-type-mapping. Свойство массива может храниться в виде строки в столбце базы данных sql.

Answer 2

Пожалуйста, посмотрите на этот документ: импорт документов JSON в SQL Server(база данных SQL Azure)

В нем показано, как импортировать большой двоичный объект хранилища Azure JSON в Azure SQL Server. И предоставляет демоверсии:

1. Импортировать документ JSON в один столбец
2. Импорт нескольких документов JSON
3. Импорт документов JSON из хранилища файлов Azure
4. Импорт документов JSON из хранилища BLOB-объектов Azure
5. Разобрать документы JSON в строки и столбцы

Демо SQL:

CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
 WITH ( TYPE = BLOB_STORAGE,
        LOCATION = 'https://myazureblobstorage.blob.core.windows.net',
        CREDENTIAL= MyAzureBlobStorageCredential);
BULK INSERT Product
FROM 'data/product.dat'
WITH ( DATA_SOURCE = 'MyAzureBlobStorage');

Вам просто нужно следовать учебнику.

Надеюсь это поможет.

Answer 3

В ADF вы можете создать «Действие копирования» для прямой передачи данных BLOB-объектов на сервер SQL. Настройте исходный набор данных как CSV 'DelimitedText' (представьте себе содержимое файла JSON как данные csv с 1 строкой и 1 столбцом), а набор данных Sink — как соединитель базы данных SQL Azure. Вы можете загружать сотни JSON из большого двоичного объекта Azure в таблицу Azure SQL с полем размера NVARCHAR(MAX).

Определите сопоставление для 1 столбца источника (например, JSON). Вы также можете добавить настраиваемые поля в Копировать источник активности и сделать это здесь. Создайте еще одно «Действие хранимой процедуры» для анализа JSON, хранящегося в таблице SQL.

Источник:

Исходный набор данных:

Раковина:

Отображение:

Таблица 'stg' для сохранения должна выглядеть так.

      CREATE TABLE [dbo].[stgSalesInvoice](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [JsonData] [nvarchar](max) NULL
) ON [PRIMARY] 
GO

Хранимая процедура для анализа JSON в таблице Target Azure Sql будет выглядеть следующим образом для примера вложенного json. Не прикрепляя файл json, но ниже показано, как легко вы можете анализировать вложенный json.

      CREATE  PROCEDURE [dbo].[pLoadSalesInvoice]
AS
BEGIN
    DECLARE @Jsons nVARCHAR(MAX);
    DECLARE @mPartitionKey VARCHAR(20) ;
    DECLARE @mFileName VARCHAR(1000) ;
    DECLARE @JsonTotalFilesCount INT = (SELECT COUNT(*) FROM [dbo].[stgSalesInvoice]);
    DECLARE @JsonLoopCount INT = 1;

    TRUNCATE TABLE  [dbo].[SalesInvoice]

    WHILE @JsonLoopCount <= @JsonTotalFilesCount
    BEGIN
        SELECT @Jsons = JsonData FROM [dbo].[stgSalesInvoice] WHERE id = @JsonLoopCount;

        --Target Azure Sql Table
        INSERT INTO [dbo].[SalesInvoice]
               ([SalesInvoiceId]
               ,[invoiceNo]
               ,[transactionId]
               ,[supplierId]
               ,[vendorSupplierId]
               ,[vendorNoExt]
               ,[vendorDate]
               ,[biddingId]
               ,[description]
               ,[biddingDimensionId]
               ,[biddingItemDimensionValueId]
                )
        SELECT  [SalesInvoiceId]
               ,[invoiceNo]
               ,[transactionId]
               ,[supplierId]
               ,[vendorSupplierId]
               ,[vendorNoExt]
               ,[vendorDate]
               ,[biddingId]
               ,[description]
               ,[biddingDimensionId]
               ,[biddingItemDimensionValueId]

        FROM OPENJSON(@Jsons) WITH (
                SalesInvoice NVARCHAR(MAX) '$.SalesInvoice' AS JSON
                ,supplier NVARCHAR(MAX) '$.supplier' AS JSON
                ,vendor NVARCHAR(MAX) '$.vendor' AS JSON
                ,bidding NVARCHAR(MAX) '$.bidding' AS JSON
                )
        OUTER APPLY OPENJSON(SalesInvoice) WITH (
                [SalesInvoiceId] NVARCHAR(100) '$.id'
                ,[invoiceNo] NVARCHAR(100) '$.invoiceNo'
                ,[transactionId] NVARCHAR(100) '$.transactionId'
                )
        OUTER APPLY OPENJSON(supplier) WITH (
                [supplierId] NVARCHAR(100) '$.id'
                ,[vendorSupplierId] NVARCHAR(100) '$.vendorSupplierId'
                )
        OUTER APPLY OPENJSON(vendor) WITH (
                [vendorNoExt] NVARCHAR(100) '$.vendorNoExt'
                ,[vendorDate] datetime2(7) '$.vendorDate'
                )
        OUTER APPLY OPENJSON(bidding) WITH (items NVARCHAR(MAX) '$.items' AS JSON)
        OUTER APPLY OPENJSON(items) WITH (
                [biddingId] NVARCHAR(100) '$.biddingId',
                [description] NVARCHAR(250) '$.description',
                biddingItemDimensionValues NVARCHAR(MAX) '$.biddingItemDimensionValues' AS JSON
                )
        OUTER APPLY OPENJSON(biddingItemDimensionValues) WITH (
                [biddingDimensionId] NVARCHAR(100) '$.biddingDimensionId'
                ,[biddingItemDimensionValueId] NVARCHAR(100) '$.biddingItemDimensionValueId'
                );
        SET @JsonLoopCount = @JsonLoopCount + 1
    END
    SELECT 1;
END