Поделиться через

Запрашивание JSON-файлов

Применимо к:✅ Конечная точка аналитики SQL и хранилище в Microsoft Fabric

В этой статье вы узнаете, как запрашивать ФАЙЛЫ JSON с помощью Fabric SQL, включая хранилище данных Fabric и конечную точку аналитики SQL.

JSON (нотация объектов JavaScript) — это упрощенный формат для полуструктурированных данных, широко используемый в больших данных для потоков датчиков, конфигураций Интернета вещей, журналов и геопространственных данных (например, GeoJSON).

Использование OPENROWSET для запроса JSON-файлов напрямую

В Хранилище данных Fabric и конечной точке аналитики SQL для Lakehouse можно запрашивать JSON-файлы непосредственно в озере OPENROWSET с помощью функции.

OPENROWSET( BULK '{{filepath}}', [ , <options> ... ])
[ WITH ( <column schema and mappings> ) ];

При запросе JSON-файлов с помощью OPENROWSET сначала укажите путь к файлу, который может быть прямым URL-адресом или шаблоном подстановочных знаков, предназначенным для одного или нескольких файлов. По умолчанию Fabric отображает каждое свойство верхнего уровня в документе JSON как отдельный столбец в результирующем наборе. Для файлов строк JSON каждая строка рассматривается как отдельная строка, что делает ее идеальной для сценариев потоковой передачи.

Если вам нужен дополнительный контроль:

  • Используйте необязательное WITH предложение, чтобы определить схему явным образом и сопоставить столбцы с определенными свойствами JSON, включая вложенные пути.
  • Используется DATA_SOURCE для ссылки на корневое расположение относительных путей.
  • Настройте параметры обработки ошибок, такие как MAXERRORS, чтобы эффективно управлять проблемами синтаксического анализа.

Распространенные варианты использования JSON-файла

Распространенные типы файлов JSON и варианты использования, которые можно обрабатывать в Microsoft Fabric:

  • Файлы JSON с разделителями строк ("строки JSON"), в которых каждая строка является автономным, допустимым документом JSON (например, событием, чтением или записью журнала).
    • Весь файл не обязательно является единым валидным документом JSON, это последовательность объектов JSON, разделенных символами новой строки.
    • Файлы с этим форматом обычно имеют расширения .jsonl, .ldjson или .ndjson. Идеально подходит для потоковой передачи и сценариев добавления данных — авторы могут добавлять новые события как новые строки без перезаписи файла или нарушения структуры.
  • Файлы JSON с одним документом ("классический JSON"), имеющие расширение .json, в которых весь файл является одним допустимым документом JSON либо единым объектом, либо массивом объектов (возможно, вложенных).
    • Обычно он используется для конфигурации, создания моментальных снимков и экспорта наборов данных единым целым.
    • Например, файлы GeoJSON обычно хранят один объект JSON, описывающий функции и их геометрии.

Запрос файлов JSONL с помощью OPENROWSET

Хранилище данных Fabric и конечная точка аналитики SQL для Lakehouse позволяют разработчикам SQL запрашивать файлы JSON Lines (.jsonl, ldjson, ndjson) непосредственно из озера данных с помощью OPENROWSET функции.

Эти файлы содержат один допустимый объект JSON на строку, что делает их идеальными для потоковой передачи и сценариев только для добавления. Чтобы прочитать файл строк JSON, укажите его URL-адрес в аргументе BULK :

SELECT TOP 10 *
FROM OPENROWSET(
    BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.jsonl'
);

По умолчанию OPENROWSET использует вывод схемы, автоматически обнаруживая все свойства верхнего уровня в каждом объекте JSON и возвращая их в виде столбцов.

Однако можно явно определить схему для управления возвращаемыми свойствами и переопределения выводимых типов данных:

SELECT TOP 10 *
FROM OPENROWSET(
    BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.jsonl'
) WITH (
    country_region VARCHAR(100),
    confirmed INT,
    date_reported DATE '$.updated'
);

Явное определение схемы полезно, если:

  • Вы хотите переопределить типы выводимых по умолчанию (например, принудительно использовать тип данных даты вместо varchar).
  • Вам нужны стабильные названия столбцов и выборочные проекции.
  • Вы хотите сопоставить столбцы с определенными свойствами JSON, включая вложенные пути.

Чтение сложных (вложенных) структур JSON с помощью OPENROWSET

Хранилище данных Fabric и конечная точка аналитики SQL для Lakehouse позволяют разработчикам SQL читать JSON с вложенными объектами или подмассивами непосредственно из озера с помощью OPENROWSET.

{
  "type": "Feature",
  "properties": {
    "shapeName": "Serbia",
    "shapeISO": "SRB",
    "shapeID": "94879208B25563984444888",
    "shapeGroup": "SRB",
    "shapeType": "ADM0"
  }
}

В следующем примере выполните запрос к файлу, содержащему примеры данных, и используйте конструкцию WITH для явного отображения свойств нижнего уровня.

SELECT
    *
FROM
  OPENROWSET(
    BULK '/Files/parquet/nested/geojson.jsonl'
  )
  WITH (
    -- Top-level field
    [type]     VARCHAR(50),
    -- Leaf properties from the nested "properties" object
    shapeName  VARCHAR(200) '$.properties.shapeName',
    shapeISO   VARCHAR(50)  '$.properties.shapeISO',
    shapeID    VARCHAR(200) '$.properties.shapeID',
    shapeGroup VARCHAR(50)  '$.properties.shapeGroup',
    shapeType  VARCHAR(50)  '$.properties.shapeType'
  );

Замечание

В этом примере используется относительный путь без источника данных, который работает при запросе файлов в Lakehouse через конечную точку аналитики SQL. В хранилище данных Fabric необходимо выполнить следующие действия.

  • Используйте абсолютный путь к файлу или
  • Укажите корневой URL-адрес во внешнем источнике данных и ссылайтесь на него в OPENROWSET выражении, используя опцию DATA_SOURCE.

Преобразуйте вложенные массивы (из JSON в строки) с помощью OPENROWSET

Хранилище данных Fabric и конечная точка аналитики SQL для Lakehouse позволяют читать JSON-файлы с вложенными массивами с помощью OPENROWSET. Затем вы можете развернуть (разнормализовать) эти массивы с помощью CROSS APPLY OPENJSON. Этот метод полезен, если документ верхнего уровня содержит вложенный массив, который требуется в качестве одной строки для каждого элемента.

В следующем упрощенном примере ввода в документе GeoJSON имеется массив функций:

{
  "type": "FeatureCollection",
  "crs": { "type": "name", "properties": { "name": "urn:ogc:def:crs:OGC:1.3:CRS84" } },
  "features": [
    {
      "type": "Feature",
      "properties": {
        "shapeName": "Serbia",
        "shapeISO": "SRB",
        "shapeID": "94879208B25563984444888",
        "shapeGroup": "SRB",
        "shapeType": "ADM0"
      },
      "geometry": {
        "type": "Line",
        "coordinates": [[[19.6679328, 46.1848744], [19.6649294, 46.1870428], [19.6638492, 46.1890231]]]
      }
    }
  ]
}

Следующий запрос:

  1. Считывает документ JSON из озера с помощью OPENROWSET, проецируя свойство верхнего уровня типа вместе с массивом необработанных признаков.
  2. Применяется CROSS APPLY OPENJSON для расширения массива функций, чтобы каждый элемент стал собственной строкой в результирующем наборе. В рамках этого расширения запрос извлекает вложенные значения с помощью выражений пути JSON. Такие значения, как shapeName, shapeISO, и geometry, а также подробности, такие как geometry.type и coordinates, теперь являются плоскими столбцами для упрощения анализа.
SELECT
  r.crs_name,
  f.[type] AS feature_type,
  f.shapeName,
  f.shapeISO,
  f.shapeID,
  f.shapeGroup,
  f.shapeType,
  f.geometry_type,
  f.coordinates
FROM
  OPENROWSET(
      BULK '/Files/parquet/nested/geojson.jsonl'
  )
  WITH (
      crs_name    VARCHAR(100)  '$.crs.properties.name', -- top-level nested property
      features    VARCHAR(MAX)  '$.features'             -- raw JSON array
  ) AS r
CROSS APPLY OPENJSON(r.features)
WITH (
  [type]           VARCHAR(50),
  shapeName        VARCHAR(200)  '$.properties.shapeName',
  shapeISO         VARCHAR(50)   '$.properties.shapeISO',
  shapeID          VARCHAR(200)  '$.properties.shapeID',
  shapeGroup       VARCHAR(50)   '$.properties.shapeGroup',
  shapeType        VARCHAR(50)   '$.properties.shapeType',
  geometry_type    VARCHAR(50)   '$.geometry.type',
  coordinates      VARCHAR(MAX)  '$.geometry.coordinates'
) AS f;

Связанный контент

  • Last updated on