Wykonywanie zapytań dotyczących plików JSON przy użyciu bezserwerowej puli SQL w usłudze Azure Synapse Analytics

Artykuł
10/18/2023

W tym artykule dowiesz się, jak napisać zapytanie przy użyciu bezserwerowej puli SQL w usłudze Azure Synapse Analytics. Celem zapytania jest odczytywanie plików JSON przy użyciu zestawu OPENROWSET.

Standardowe pliki JSON, w których wiele dokumentów JSON jest przechowywanych jako tablica JSON.
Rozdzielane wierszami pliki JSON, w których dokumenty JSON są oddzielone znakiem nowego wiersza. Typowe rozszerzenia dla tych typów plików to jsonl, ldjsoni ndjson.

Odczytywanie dokumentów JSON

Najprostszym sposobem wyświetlenia zawartości pliku JSON jest podanie adresu URL pliku do OPENROWSET funkcji, określenie pliku csv FORMATi ustawienie wartości 0x0b dla fieldterminator i fieldquote. Jeśli musisz odczytać rozdzielane wierszami pliki JSON, wystarczy. Jeśli masz klasyczny plik JSON, musisz ustawić wartości 0x0b dla elementu rowterminator. OPENROWSET funkcja przeanalizuje kod JSON i zwróci każdy dokument w następującym formacie:

Dok
{"date_rep":"2020-07-24","day":24,"month":7,"year":2020,"cases":3,"deaths":0,"geo_id":"AF"}
{"date_rep":"2020-07-25","day":25,"month":7,"year":2020,"cases":7,"deaths":0,"geo_id":"AF"}
{"date_rep":"2020-07-26","day":26,"month":7,"year":2020,"cases":4,"deaths":0,"geo_id":"AF"}
{"date_rep":"2020-07-27","day":27,"month":7,"year":2020,"cases":8,"deaths":0,"geo_id":"AF"}

Jeśli plik jest publicznie dostępny lub jeśli tożsamość firmy Microsoft Entra może uzyskać dostęp do tego pliku, powinna zostać wyświetlona zawartość pliku przy użyciu zapytania, takiego jak pokazana w poniższych przykładach.

Odczytywanie plików JSON

Poniższe przykładowe zapytanie odczytuje pliki JSON i JSON rozdzielane wierszami i zwraca każdy dokument jako oddzielny wiersz.

select top 10 *
from openrowset(
        bulk 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/latest/ecdc_cases.jsonl',
        format = 'csv',
        fieldterminator ='0x0b',
        fieldquote = '0x0b'
    ) with (doc nvarchar(max)) as rows
go
select top 10 *
from openrowset(
        bulk 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/latest/ecdc_cases.json',
        format = 'csv',
        fieldterminator ='0x0b',
        fieldquote = '0x0b',
        rowterminator = '0x0b' --> You need to override rowterminator to read classic JSON
    ) with (doc nvarchar(max)) as rows

Dokument JSON w poprzednim przykładowym zapytaniu zawiera tablicę obiektów. Zapytanie zwraca każdy obiekt jako oddzielny wiersz w zestawie wyników. Upewnij się, że masz dostęp do tego pliku. Jeśli plik jest chroniony przy użyciu klucza sygnatury dostępu współdzielonego lub tożsamości niestandardowej, należy skonfigurować poświadczenia na poziomie serwera na potrzeby logowania sql.

Użycie źródła danych

W poprzednim przykładzie do pliku jest używana pełna ścieżka. Alternatywnie możesz utworzyć zewnętrzne źródło danych z lokalizacją wskazującą folder główny magazynu i użyć tego źródła danych oraz ścieżki względnej do pliku w OPENROWSET funkcji:

create external data source covid
with ( location = 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases' );
go
select top 10 *
from openrowset(
        bulk 'latest/ecdc_cases.jsonl',
        data_source = 'covid',
        format = 'csv',
        fieldterminator ='0x0b',
        fieldquote = '0x0b'
    ) with (doc nvarchar(max)) as rows
go
select top 10 *
from openrowset(
        bulk 'latest/ecdc_cases.json',
        data_source = 'covid',
        format = 'csv',
        fieldterminator ='0x0b',
        fieldquote = '0x0b',
        rowterminator = '0x0b' --> You need to override rowterminator to read classic JSON
    ) with (doc nvarchar(max)) as rows

Jeśli źródło danych jest chronione przy użyciu klucza sygnatury dostępu współdzielonego lub tożsamości niestandardowej, możesz skonfigurować źródło danych przy użyciu poświadczeń o zakresie bazy danych.

W poniższych sekcjach przedstawiono sposób wykonywania zapytań dotyczących różnych typów plików JSON.

Analizowanie dokumentów JSON

Zapytania w poprzednich przykładach zwracają każdy dokument JSON jako pojedynczy ciąg w osobnym wierszu zestawu wyników. Funkcje i OPENJSON umożliwiają JSON_VALUE analizowanie wartości w dokumentach JSON i zwracanie ich jako wartości relacyjnych, jak pokazano w poniższym przykładzie:

date_rep	przypadki	geo_id
2020-07-24	3	AF
2020-07-25	7	AF
2020-07-26	4	AF
2020-07-27	8	AF

Przykładowy dokument JSON

Przykłady zapytań odczytują pliki json zawierające dokumenty o następującej strukturze:

{
    "date_rep":"2020-07-24",
    "day":24,"month":7,"year":2020,
    "cases":13,"deaths":0,
    "countries_and_territories":"Afghanistan",
    "geo_id":"AF",
    "country_territory_code":"AFG",
    "continent_exp":"Asia",
    "load_date":"2020-07-25 00:05:14",
    "iso_country":"AF"
}

Uwaga

Jeśli te dokumenty są przechowywane jako rozdzielane wierszami JSON, należy ustawić FIELDTERMINATOR i FIELDQUOTE 0x0b. Jeśli masz standardowy format JSON, musisz ustawić wartość ROWTERMINATOR na 0x0b.

Wykonywanie zapytań dotyczących plików JSON przy użyciu JSON_VALUE

Poniższe zapytanie pokazuje, jak używać JSON_VALUE do pobierania wartości skalarnych (date_rep, countries_and_territories, cases) z dokumentów JSON:

select
    JSON_VALUE(doc, '$.date_rep') AS date_reported,
    JSON_VALUE(doc, '$.countries_and_territories') AS country,
    CAST(JSON_VALUE(doc, '$.deaths') AS INT) as fatal,
    JSON_VALUE(doc, '$.cases') as cases,
    doc
from openrowset(
        bulk 'latest/ecdc_cases.jsonl',
        data_source = 'covid',
        format = 'csv',
        fieldterminator ='0x0b',
        fieldquote = '0x0b'
    ) with (doc nvarchar(max)) as rows
order by JSON_VALUE(doc, '$.geo_id') desc

Po wyodrębnieniu właściwości JSON z dokumentu JSON można zdefiniować aliasy kolumn i opcjonalnie rzutować wartość tekstową na jakiś typ.

Wykonywanie zapytań dotyczących plików JSON przy użyciu formatu OPENJSON

Poniższe zapytanie używa pliku OPENJSON. Pobierze statystyki COVID zgłaszane w Serbii:

select
    *
from openrowset(
        bulk 'latest/ecdc_cases.jsonl',
        data_source = 'covid',
        format = 'csv',
        fieldterminator ='0x0b',
        fieldquote = '0x0b'
    ) with (doc nvarchar(max)) as rows
    cross apply openjson (doc)
        with (  date_rep datetime2,
                cases int,
                fatal int '$.deaths',
                country varchar(100) '$.countries_and_territories')
where country = 'Serbia'
order by country, date_rep desc;

Wyniki są funkcjonalnie takie same jak wyniki zwracane przy użyciu JSON_VALUE funkcji . W niektórych przypadkach OPENJSON może mieć przewagę nad JSON_VALUE:

W klauzuli WITH można jawnie ustawić aliasy kolumn i typy dla każdej właściwości. Nie trzeba umieszczać funkcji na CAST każdej kolumnie na SELECT liście.
OPENJSON może być szybsze, jeśli zwracasz dużą liczbę właściwości. Jeśli zwracasz tylko 1–2 właściwości, OPENJSON funkcja może być obciążeniem.
Należy użyć OPENJSON funkcji , jeśli musisz przeanalizować tablicę z każdego dokumentu i dołączyć ją do wiersza nadrzędnego.

Następne kroki

Następne artykuły z tej serii pokażą, jak wykonać następujące czynności: