Compartir a través de

Consulta de archivo JSON

Se aplica a:✅ punto de conexión de análisis SQL y Almacenamiento de datos en Microsoft Fabric

En este artículo, aprenderá a consultar archivos JSON mediante Fabric SQL, incluido Fabric Data Warehouse y el punto de conexión de SQL Analytics.

JSON (notación de objetos JavaScript) es un formato ligero para datos semiestructurados, ampliamente usado en macrodatos para flujos de sensor, configuraciones de IoT, registros y datos geoespaciales (por ejemplo, GeoJSON).

Uso de OPENROWSET para consultar archivos JSON directamente

En Fabric Data Warehouse y el punto de conexión SQL Analytics para un Lakehouse, puede consultar archivos JSON directamente en el lago mediante la función OPENROWSET.

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

Al consultar archivos JSON con OPENROWSET, empiece especificando la ruta de acceso del archivo, que puede ser una dirección URL directa o un patrón de caracteres comodín que tenga como destino uno o varios archivos. De forma predeterminada, Fabric proyecta cada propiedad de nivel superior en el documento JSON como una columna independiente en el conjunto de resultados. En el caso de los archivos de líneas JSON, cada línea se trata como una fila individual, lo que lo convierte en ideal para escenarios de streaming.

Si necesita más control:

  • Use la cláusula opcional WITH para definir el esquema explícitamente y asignar columnas a propiedades JSON específicas, incluidas las rutas de acceso anidadas.
  • Use DATA_SOURCE para hacer referencia a una ubicación raíz para rutas de acceso relativas.
  • Configure parámetros de control de errores como MAXERRORS para administrar problemas de análisis correctamente.

Casos de uso comunes de archivos JSON

Tipos de archivo JSON comunes y casos de uso que puede controlar en Microsoft Fabric:

  • Archivos JSON delimitados por líneas ("líneas JSON") donde cada línea es un documento JSON independiente y válido (por ejemplo, un evento, una lectura o una entrada de registro).
    • Todo el archivo no es necesariamente un único documento JSON válido, sino una secuencia de objetos JSON separados por caracteres de nueva línea.
    • Los archivos con este formato suelen tener extensiones .jsonl, .ldjsono .ndjson. Ideal para streaming y escenarios de solo 'append', los usuarios pueden anexar un nuevo evento como una nueva línea sin volver a escribir el archivo o romper la estructura.
  • Archivos JSON de documento único ("JSON clásico") con la .json extensión donde todo el archivo es un documento JSON válido, ya sea un único objeto o una matriz de objetos (potencialmente anidado).
    • Normalmente se usa para la configuración, las instantáneas y los conjuntos de datos exportados en una sola pieza.
    • Por ejemplo, los archivos GeoJSON suelen almacenar un único objeto JSON que describe las características y sus geometrías.

Consulta de archivos JSONL con OPENROWSET

Fabric Data Warehouse y el punto de conexión analítico de SQL para Lakehouse permiten a los desarrolladores de SQL consultar directamente los archivos JSON Lines (.jsonl, .ldjson, .ndjson) desde el lago de datos mediante la función OPENROWSET.

Estos archivos contienen un objeto JSON válido por línea, por lo que son ideales para escenarios de streaming y de solo anexión. Para leer un archivo de líneas JSON, proporcione su dirección URL en el BULK argumento :

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'
);

De forma predeterminada, OPENROWSET usa la inferencia de esquemas, detecta automáticamente todas las propiedades de nivel superior de cada objeto JSON y las devuelve como columnas.

Sin embargo, puede definir explícitamente el esquema para controlar qué propiedades se devuelven e invalidan los tipos de datos inferidos:

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'
);

La definición de esquema explícita es útil cuando:

  • Quiere invalidar los tipos inferidos predeterminados (por ejemplo, para forzar el tipo de datos date en lugar de varchar).
  • Necesita nombres de columna estables y proyección selectiva.
  • Quiere asignar columnas a propiedades JSON específicas, incluidas rutas anidadas.

Leer estructuras JSON complejas (anidadas) con OPENROWSET

Fabric Data Warehouse y el punto de conexión de SQL Analytics para Lakehouse permiten a los desarrolladores de SQL leer JSON con objetos anidados o subbarrays directamente desde el lago mediante OPENROWSET.

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

En el ejemplo siguiente, consulte un archivo que contenga datos de ejemplo y use la WITH cláusula para proyectar explícitamente sus propiedades de nivel hoja:

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'
  );

Nota:

En este ejemplo se usa una ruta de acceso relativa sin origen de datos, que funciona al consultar archivos en Lakehouse a través de su punto de conexión SQL Analytics. En Fabric Data Warehouse, debe:

  • Usar una ruta de acceso absoluta al archivo o
  • Especifique una dirección URL raíz en un origen de datos externo y haga referencia a ella en la OPENROWSET instrucción mediante la DATA_SOURCE opción .

Expansión de matrices anidadas (JSON a filas) con OPENROWSET

Fabric Data Warehouse y el punto de conexión de SQL Analytics para Lakehouse permiten leer archivos JSON con matrices anidadas mediante OPENROWSET. A continuación, puede expandir (desanidar) esas matrices mediante CROSS APPLY OPENJSON. Este método es útil cuando un documento de nivel superior contiene una submatriz que desea convertir en una fila por elemento.

En la siguiente entrada de ejemplo simplificada, un documento similar a GeoJSON tiene una matriz de características:

{
  "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]]]
      }
    }
  ]
}

La consulta siguiente:

  1. Lee el documento JSON del lago mediante OPENROWSET, proyectando la propiedad de tipo en el nivel superior junto con la matriz de características en bruto.
  2. CROSS APPLY OPENJSON Se aplica para expandir la matriz de características para que cada elemento se convierta en su propia fila en el conjunto de resultados. Dentro de esta expansión, la consulta extrae valores anidados mediante expresiones de ruta JSON. Los valores como shapeName, shapeISOy detalles como geometry y geometry.typecoordinates, ahora son columnas planas para facilitar el análisis.
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;

Contenido relacionado

  • Last updated on