Zapytania zagnieżdżone typy w plikach Parquet i JSON przy użyciu bezserwerowej puli SQL w usłudze Azure Synapse Analytics

Artykuł
06/01/2023

W tym artykule dowiesz się, jak napisać zapytanie przy użyciu bezserwerowej puli SQL w usłudze Azure Synapse Analytics. Zapytanie odczytuje zagnieżdżone typy Parquet. Zagnieżdżone typy to złożone struktury reprezentujące obiekty lub tablice. Zagnieżdżone typy można przechowywać w następujących ździe:

Parquet, gdzie można mieć wiele złożonych kolumn zawierających tablice i obiekty.
Hierarchiczne pliki JSON, w których można odczytać złożony dokument JSON jako pojedynczą kolumnę.
Kolekcje usługi Azure Cosmos DB (obecnie dostępne w publicznej wersji zapoznawczej), w których każdy dokument może zawierać złożone zagnieżdżone właściwości.

Bezserwerowa pula SQL formatuje wszystkie zagnieżdżone typy jako obiekty i tablice JSON. W związku z tym można wyodrębniać lub modyfikować złożone obiekty przy użyciu funkcji JSON lub analizować dane JSON przy użyciu funkcji OPENJSON.

Oto przykład zapytania, które wyodrębnia wartości skalarne i obiekty z pliku JSON zestawu danych Open Research Dataset COVID-19 , który zawiera zagnieżdżone obiekty:

SELECT
    title = JSON_VALUE(doc, '$.metadata.title'),
    first_author = JSON_QUERY(doc, '$.metadata.authors[0]'),
    first_author_name = JSON_VALUE(doc, '$.metadata.authors[0].first'),
    complex_object = doc
FROM
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/covid19temp/comm_use_subset/pdf_json/000b7d1517ceebb34e1e3e817695b6de03e2fa78.json',
        FORMAT='CSV', FIELDTERMINATOR ='0x0b', FIELDQUOTE = '0x0b', ROWTERMINATOR = '0x0b'
    )
    WITH ( doc varchar(MAX) ) AS docs;

Funkcja JSON_VALUE zwraca wartość skalarną z pola w określonej ścieżce. Funkcja JSON_QUERY zwraca obiekt sformatowany jako JSON z pola w określonej ścieżce.

Ważne

W tym przykładzie użyto pliku z zestawu danych Open Research Dataset covid-19. Zobacz licencję i strukturę danych tutaj.

Wymagania wstępne

Pierwszym krokiem jest utworzenie bazy danych, w której zostanie utworzone źródło danych. Następnie zainicjujesz obiekty, uruchamiając skrypt instalacyjny w bazie danych. Skrypt instalacji utworzy źródła danych, poświadczenia o zakresie bazy danych i zewnętrzne formaty plików, które są używane w przykładach.

Zagnieżdżone lub powtarzające się dane projektu

Plik Parquet może zawierać wiele kolumn ze złożonymi typami. Wartości z tych kolumn są formatowane jako tekst JSON i zwracane jako kolumny VARCHAR. Następujące zapytanie odczytuje plik structExample.parquet i pokazuje, jak odczytywać wartości zagnieżdżonych kolumn:

SELECT
    DateStruct, TimeStruct, TimestampStruct, DecimalStruct, FloatStruct
FROM
    OPENROWSET(
        BULK 'parquet/nested/structExample.parquet',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT='PARQUET'
    )
    WITH (
        DateStruct VARCHAR(8000),
        TimeStruct VARCHAR(8000),
        TimestampStruct VARCHAR(8000),
        DecimalStruct VARCHAR(8000),
        FloatStruct VARCHAR(8000)
    ) AS [r];

To zapytanie zwraca następujący wynik. Zawartość każdego zagnieżdżonego obiektu jest zwracana jako tekst JSON.

DateStruct	TimeStruct	TimestampStruct	DecimalStruct	FloatStruct
{"Date":"2009-04-25"}	{"Time":"20:51:54.3598000"}	{"Sygnatura czasowa":"5501-04-08 12:13:57.4821000"}	{"Decimal":11143412.25350}	{"Float":0.5}
{"Date":"1916-04-29"}	{"Time":"00:16:04.6778000"}	{"Sygnatura czasowa":"1990-06-30 20:50:52.6828000"}	{"Decimal":1963545.62800}	{"Float":-2.125}

Poniższe zapytanie odczytuje plik justSimpleArray.parquet. Projektuje wszystkie kolumny z pliku Parquet, w tym zagnieżdżone i powtarzające się dane.

SELECT
    SimpleArray
FROM
    OPENROWSET(
        BULK 'parquet/nested/justSimpleArray.parquet',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT='PARQUET'
    ) AS [r];

To zapytanie zwróci następujący wynik:

SimpleArray
[11,12,13]
[21,22,23]

Odczytywanie właściwości z zagnieżdżonych kolumn obiektów

Funkcja JSON_VALUE umożliwia zwracanie wartości z kolumn sformatowanych jako tekst JSON:

SELECT
    title = JSON_VALUE(complex_column, '$.metadata.title'),
    first_author_name = JSON_VALUE(complex_column, '$.metadata.authors[0].first'),
    body_text = JSON_VALUE(complex_column, '$.body_text.text'),
    complex_column
FROM
    OPENROWSET( BULK 'https://azureopendatastorage.blob.core.windows.net/covid19temp/comm_use_subset/pdf_json/000b7d1517ceebb34e1e3e817695b6de03e2fa78.json',
                FORMAT='CSV', FIELDTERMINATOR ='0x0b', FIELDQUOTE = '0x0b', ROWTERMINATOR = '0x0b' ) WITH ( complex_column varchar(MAX) ) AS docs;

Wynik jest wyświetlany w poniższej tabeli:

tytuł	first_author_name	body_text	complex_column
Dodatkowe informacje eko-epidemiolo...	Julien	- Rysunek S1: Phylogeny...	`{ "paper_id": "000b7d1517ceebb34e1e3e817695b6de03e2fa78", "metadata": { "title": "Supplementary Information An eco-epidemiological study of Morbilli-related paramyxovirus infection in Madagascar bats reveals host-switching as the dominant macro-evolutionary mechanism", "authors": [ { "first": "Julien"`

W przeciwieństwie do plików JSON, które w większości przypadków zwracają jedną kolumnę zawierającą złożony obiekt JSON, pliki Parquet mogą mieć wiele złożonych kolumn. Właściwości kolumn zagnieżdżonych można odczytać przy użyciu JSON_VALUE funkcji w każdej kolumnie. OPENROWSET umożliwia bezpośrednie określenie ścieżek zagnieżdżonych właściwości w klauzuli WITH . Ścieżki można ustawić jako nazwę kolumny lub dodać wyrażenie ścieżki JSON po typie kolumny.

Poniższe zapytanie odczytuje plik structExample.parquet i pokazuje, jak uwidocznieć elementy zagnieżdżonej kolumny. Istnieją dwa sposoby odwołowania się do wartości zagnieżdżonej:

Określając wyrażenie ścieżki wartości zagnieżdżonej po specyfikacji typu.
Formatowanie nazwy kolumny jako ścieżki zagnieżdżonej przy użyciu polecenia "" w celu odwołania się do pól.

SELECT
    *
FROM
    OPENROWSET(
        BULK 'parquet/nested/structExample.parquet',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT='PARQUET'
    )
    WITH (
        [DateValue] DATE '$.DateStruct.Date',
        [TimeStruct.Time] TIME,
        [TimestampStruct.Timestamp] DATETIME2,
        DecimalValue DECIMAL(18, 5) '$.DecimalStruct.Decimal',
        [FloatStruct.Float] FLOAT
    ) AS [r];

Uzyskiwanie dostępu do elementów z powtarzających się kolumn

Następujące zapytanie odczytuje plik justSimpleArray.parquet i używa JSON_VALUE do pobrania elementu skalarnego z powtarzającej się kolumny, takiej jak tablica lub mapa:

SELECT
    *,
    JSON_VALUE(SimpleArray, '$[0]') AS FirstElement,
    JSON_VALUE(SimpleArray, '$[1]') AS SecondElement,
    JSON_VALUE(SimpleArray, '$[2]') AS ThirdElement
FROM
    OPENROWSET(
        BULK 'parquet/nested/justSimpleArray.parquet',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT='PARQUET'
    ) AS [r];

Oto wynik:

SimpleArray	FirstElement	SecondElement	Trzeci element
[11,12,13]	11	12	13
[21,22,23]	21	22	23

Uzyskiwanie dostępu do obiektów podrzędnych z kolumn złożonych

Następujące zapytanie odczytuje plik mapExample.parquet i używa JSON_QUERY do pobrania elementu nieskalarnego z powtarzającej się kolumny, takiej jak tablica lub mapa:

SELECT
    MapOfPersons,
    JSON_QUERY(MapOfPersons, '$."John Doe"') AS [John]
FROM
    OPENROWSET(
        BULK 'parquet/nested/mapExample.parquet',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT='PARQUET'
    ) AS [r];

Możesz również jawnie odwołać się do kolumn, które mają zostać zwrócone w klauzuli WITH :

SELECT DocId,
    MapOfPersons,
    JSON_QUERY(MapOfPersons, '$."John Doe"') AS [John]
FROM
    OPENROWSET(
        BULK 'parquet/nested/mapExample.parquet',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT='PARQUET'
    ) 
    WITH (DocId bigint, MapOfPersons VARCHAR(max)) AS [r];

Struktura MapOfPersons jest zwracana jako kolumna VARCHAR i sformatowana jako ciąg JSON.

Wartości projektu z powtarzających się kolumn

Jeśli masz tablicę wartości skalarnych (na przykład [1,2,3]) w niektórych kolumnach, możesz je łatwo rozwinąć i połączyć je z głównym wierszem przy użyciu tego skryptu:

SELECT
    SimpleArray, Element
FROM
    OPENROWSET(
        BULK 'parquet/nested/justSimpleArray.parquet',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT='PARQUET'
    ) AS arrays
    CROSS APPLY OPENJSON (SimpleArray) WITH (Element int '$') as array_values

Następne kroki

W następnym artykule przedstawiono sposób wykonywania zapytań dotyczących plików JSON.

Udostępnij za pośrednictwem