Udostępnij za pośrednictwem

Importowanie dokumentów JSON do programu SQL Server

  • Artykuł

Dotyczy: SQL Server 2016 (13.x) i nowszych Azure SQL DatabaseAzure SQL Managed Instance

W tym artykule opisano sposób importowania plików JSON do programu SQL Server. Dokumenty JSON przechowują wiele typów danych, na przykład dzienniki aplikacji, dane czujnika itd. Ważne jest, aby móc odczytywać dane JSON przechowywane w plikach, ładować dane do programu SQL Server i analizować je.

W przykładach w tym artykule użyto pliku JSON z przykładowego usługi GitHub zawierającego listę książek.

Uprawnienia

Na poziomie wystąpienia funkcja ta wymaga członkostwa w stałej roli serwera bulkadmin lub uprawnień ADMINISTER BULK OPERATIONS.

Na poziomie bazy danych ta funkcja wymaga ADMINISTER DATABASE BULK OPERATIONS uprawnień.

Uzyskiwanie dostępu do usługi Azure Blob Storage wymaga dostępu do odczytu i zapisu.

Importowanie dokumentu JSON do jednej kolumny

OPENROWSET(BULK) to funkcja wartości tabeli, która może odczytywać dane z dowolnego pliku na dysku lokalnym lub w sieci, jeśli program SQL Server ma dostęp do odczytu do tej lokalizacji. Zwraca tabelę z jedną kolumną zawierającą zawartość pliku. Istnieją różne opcje, których można używać z funkcją OPENROWSET(BULK), taką jak separatory. Ale w najprostszym przypadku można po prostu załadować całą zawartość pliku jako wartość tekstową. (Ta pojedyncza duża wartość jest znana jako pojedynczy obiekt znakowy dużej wartości lub SINGLE_CLOB).

Oto przykład funkcji OPENROWSET(BULK), która odczytuje zawartość pliku JSON i zwraca ją użytkownikowi jako pojedynczą wartość:

SELECT BulkColumn
FROM OPENROWSET(BULK 'C:\JSON\Books\book.json', SINGLE_CLOB) as j;

OPENJSON(BULK) odczytuje zawartość pliku i zwraca ją w BulkColumn.

Zawartość pliku można również załadować do zmiennej lokalnej lub do tabeli, jak pokazano w poniższym przykładzie:

-- Load file contents into a variable
DECLARE @json NVARCHAR(MAX);
SELECT @json = BulkColumn
 FROM OPENROWSET(BULK 'C:\JSON\Books\book.json', SINGLE_CLOB) as j

-- Load file contents into a table
SELECT BulkColumn
INTO #temp
FROM OPENROWSET(BULK 'C:\JSON\Books\book.json', SINGLE_CLOB) as j

Po załadowaniu zawartości pliku JSON można zapisać tekst JSON w tabeli.

Importowanie dokumentów JSON z usługi Azure File Storage

Możesz również użyć OPENROWSET(BULK) zgodnie z wcześniejszym opisem, aby odczytywać pliki JSON z innych lokalizacji plików, do których program SQL Server może uzyskać dostęp. Na przykład usługa Azure File Storage obsługuje protokół SMB. Dzięki temu można zamapować lokalny dysk wirtualny na udział usługi Azure File Storage przy użyciu następującej procedury:

  1. Utwórz konto magazynu plików (na przykład mystorage), udział plików (na przykład sharejson) i folder w usłudze Azure File Storage przy użyciu witryny Azure Portal lub programu Azure PowerShell.

  2. Przekaż niektóre pliki JSON do udziału magazynu plików.

  3. Utwórz regułę dla ruchu wychodzącego w zaporze systemu Windows na komputerze, która zezwala na połączenia przez port 445. Dostawca usług internetowych może zablokować ten port. Jeśli w poniższym kroku wystąpi błąd DNS (błąd 53), port 445 nie jest otwarty lub usługodawca nie blokuje go.

  4. Zainstaluj udział usługi Azure File Storage jako dysk lokalny (na przykład T:).

    Oto składnia polecenia:

    net use [drive letter] \\[storage name].file.core.windows.net\[share name] /u:[storage account name] [storage account access key]

    Oto przykład przypisania lokalnej litery dysku T: do udziału Azure File Storage:

    net use t: \\mystorage.file.core.windows.net\sharejson /u:myaccount hb5qy6eXLqIdBj0LvGMHdrTiygkjhHDvWjUZg3Gu7bubKLg==

    Klucz konta magazynowego oraz klucz dostępu do podstawowego lub pomocniczego konta magazynowego można znaleźć w sekcji Klucze w Ustawieniach na portalu Azure.

  5. Teraz możesz uzyskać dostęp do plików JSON z usługi udostępniania plików Azure File Storage przy użyciu dysku mapowanego, jak pokazano w poniższym przykładzie.

    SELECT book.*
FROM OPENROWSET(BULK N't:\books\books.json', SINGLE_CLOB) AS json
CROSS APPLY OPENJSON(BulkColumn) WITH (
    id NVARCHAR(100),
    name NVARCHAR(100),
    price FLOAT,
    pages_i INT,
    author NVARCHAR(100)
) AS book

Aby uzyskać więcej informacji na temat usługi Azure File Storage, zobacz File Storage.

Importowanie dokumentów JSON z usługi Azure Blob Storage

Dotyczy: SQL Server 2017 (14.x) i nowsze wersje oraz Azure SQL

Pliki można ładować bezpośrednio do usługi Azure SQL Database z usługi Azure Blob Storage za pomocą polecenia T-SQL BULK INSERT lub funkcji OPENROWSET.

Najpierw utwórz zewnętrzne źródło danych, jak pokazano w poniższym przykładzie.

CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH (
    TYPE = BLOB_STORAGE,
    LOCATION = 'https://myazureblobstorage.blob.core.windows.net',
    CREDENTIAL = MyAzureBlobStorageCredential
);

Następnie uruchom polecenie BULK INSERT z opcją DATA_SOURCE.

BULK INSERT Product
FROM 'data/product.dat'
WITH ( DATA_SOURCE = 'MyAzureBlobStorage');

Analizowanie dokumentów JSON w wierszach i kolumnach

Zamiast odczytywać cały plik JSON jako pojedynczą wartość, możesz chcieć go przeanalizować i zwrócić książki w pliku i ich właściwości w wierszach i kolumnach.

Przykład 1

W najprostszym przykładzie wystarczy załadować całą listę z pliku.

SELECT value
FROM OPENROWSET(BULK 'C:\JSON\Books\books.json', SINGLE_CLOB) AS j
CROSS APPLY OPENJSON(BulkColumn);

Poprzedni OPENROWSET odczytuje pojedynczą wartość tekstową z pliku. OPENROWSET zwraca wartość jako kolumnę BulkColumn i przekazuje funkcję BulkColumn do funkcji OPENJSON. OPENJSON iteruje tablicę obiektów JSON w tablicy BulkColumn i zwraca jedną książkę w każdym wierszu. Każdy wiersz jest sformatowany jako JSON, jak pokazano poniżej.

{"id":"978-0641723445", "cat":["book","hardcover"], "name":"The Lightning Thief", ... }
{"id":"978-1423103349", "cat":["book","paperback"], "name":"The Sea of Monsters", ... }
{"id":"978-1857995879", "cat":["book","paperback"], "name":"Sophie's World : The Greek", ... }
{"id":"978-1933988177", "cat":["book","paperback"], "name":"Lucene in Action, Second", ... }

Przykład 2

Funkcja OPENJSON może analizować zawartość JSON i przekształcać ją w tabelę lub zestaw wyników. Poniższy przykład ładuje zawartość, analizuje załadowany kod JSON i zwraca pięć pól jako kolumny:

SELECT book.*
FROM OPENROWSET(BULK 'C:\JSON\Books\books.json', SINGLE_CLOB) AS j
CROSS APPLY OPENJSON(BulkColumn) WITH (
    id NVARCHAR(100),
    name NVARCHAR(100),
    price FLOAT,
    pages_i INT,
    author NVARCHAR(100)
) AS book;

W tym przykładzie OPENROWSET(BULK) odczytuje zawartość pliku i przekazuje tę zawartość do funkcji OPENJSON ze zdefiniowanym schematem danych wyjściowych. OPENJSON dopasowuje właściwości w obiektach JSON, korzystając z nazw kolumn. Na przykład właściwość price jest zwracana jako kolumna price i konwertowana na typ danych zmiennoprzecinkowych. Oto wyniki:

Id Nazwa cena pages_i Autor
978-0641723445 Złodziej błyskawic 12.5 384 Rick Riordan
978-1423103349 Morze potworów 6.49 304 Rick Riordan
978-1857995879 Świat Sophie: Greccy filozofowie 3.07 64 Jostein Gaarder
978-1933988177 Lucene in Action, Wydanie drugie 30.5 475 Michael McCandless

Teraz możesz zwrócić tę tabelę do użytkownika lub załadować dane do innej tabeli.

Powiązana zawartość