Comparteix via

Ingerir datos en el almacenamiento mediante Transact-SQL

Se aplica a:✅Almacenamiento de Microsoft Fabric

El lenguaje Transact-SQL ofrece opciones que puede usar para cargar datos a gran escala desde tablas existentes en el lago y el almacenamiento a nuevas tablas en el almacenamiento. Estas opciones son convenientes si necesita crear nuevas versiones de una tabla con datos agregados, versiones de tablas con un subconjunto de filas o para crear una tabla como resultado de una consulta compleja. Pongamos algunos ejemplos.

Creación de una tabla con el resultado de una consulta

El almacenamiento en Microsoft Fabric le permite crear fácilmente una nueva tabla basada en un resultado de la consulta T-SQL mediante las siguientes instrucciones T-SQL:

  • Instrucción CREATE TABLE AS SELECT (CTAS) que permite crear una nueva tabla en tu almacén a partir del resultado de una instrucción SELECT.
  • SELECT INTO cláusula query que permite seleccionar los resultados de cualquier origen de tabla y redirigir los resultados a una nueva tabla. Se trata de una característica estándar en el lenguaje T-SQL.

Estas dos instrucciones son similares, por lo que los ejemplos siguientes se centran en la instrucción CTAS.

La instrucción CTAS ejecuta la operación de ingesta en la nueva tabla en paralelo, lo que la hace muy eficaz para la transformación de datos y la creación de nuevas tablas en su entorno de trabajo.

Puede usar las siguientes opciones para la parte SELECT de la instrucción CTAS:

  • Leer una tabla de almacenamiento, como una tabla de almacenamiento provisional.
  • Lectura de una carpeta Lakehouse Delta Lake utilizando una tabla generada automáticamente en el punto de enlace de SQL Analytics para Lakehouse.
  • Lectura de archivos CSV, Parquet o JSONL directamente desde Azure Data Lake o Azure Blob Storage mediante la OPENROWSET función .

Note

En los ejemplos de este artículo se usa el conjunto de datos de ejemplo de COVID-19 de Bing. Para cargar el conjunto de datos de ejemplo, siga los pasos descritos en Ingesta de datos en el almacenamiento mediante la instrucción COPY para crear los datos de ejemplo en el almacenamiento.

Creación de una tabla a partir de la tabla Warehouse

En el primer ejemplo se muestra cómo crear una nueva tabla que es una copia de la tabla existente dbo.bing_covid19_data_2023, pero filtrada solo a los datos del año 2023:

CREATE TABLE dbo.bing_covid19_data_2023
AS
SELECT * 
FROM dbo.bing_covid19_data 
WHERE DATEPART(YEAR, updated) = '2023';

También puede crear una nueva tabla con nuevas columnas year, month y dayofmonth con valores obtenidos de la columna updated de la tabla de origen. Esto puede ser útil si está intentando visualizar los datos de infección por año, o para ver los meses en los que se observan más casos de COVID-19:

CREATE TABLE dbo.bing_covid19_data_with_year_month_day
AS
SELECT DATEPART(YEAR, updated) AS [year],
       DATEPART(MONTH, updated) AS [month],
       DATEPART(DAY, updated) AS [dayofmonth],
       * 
FROM dbo.bing_covid19_data;

Otro ejemplo: puede crear una nueva tabla que resuma el número de casos observados en cada mes, independientemente del año, para evaluar cómo afecta la estacionalidad a la propagación en un país o región determinados. Usa la tabla creada en el ejemplo anterior con la nueva columna month como origen:

CREATE TABLE dbo.infections_by_month
AS
SELECT country_region, [month],
       SUM(CAST(confirmed as bigint)) AS confirmed_sum
FROM dbo.bing_covid19_data_with_year_month_day
GROUP BY country_region, [month];

Según esta nueva tabla, podemos ver que Estados Unidos observó más casos confirmados en todos los años en el mes de January, seguido de December y October. April es el mes con el número más bajo de casos en total:

SELECT * FROM dbo.infections_by_month
WHERE country_region = 'United States'
ORDER BY confirmed_sum DESC;

Creación de una tabla desde la carpeta delta Lake

Las carpetas de Delta Lake que se conservan en OneLake se representan automáticamente como tablas si se almacenan en la carpeta /Tables de un lago. El código siguiente crea una nueva tabla bing_covid19_data_2023 desde la carpeta de Delta Lake /Tables/bing_covid19_delta_lake en myLakehouse lakehouse :

CREATE TABLE dbo.bing_covid19_data_2023
AS
SELECT * 
FROM MyLakehouse.dbo.bing_covid19_delta_lake 
WHERE DATEPART(YEAR, updated) = '2023';

Puede hacer referencia a la carpeta Delta Lake mediante la notación de tres partes que hace referencia al lago donde se almacenan los archivos. Todos los ejemplos que se muestran en la sección anterior son aplicables a las carpetas de Delta Lake.

Creación de una tabla a partir del archivo CSV/Parquet/JSONL

En lugar de leer datos de la tabla Warehouse bing_covid19_data , también puede crear una tabla directamente desde un archivo externo mediante la OPENROWSET función :

CREATE TABLE dbo.bing_covid19_data_2022
AS
SELECT *
FROM OPENROWSET(BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet') AS data
WHERE DATEPART(YEAR, updated) = '2022';

También puede crear una nueva tabla mediante la transformación de datos desde un archivo CSV externo:

CREATE TABLE dbo.bing_covid19_data_with_year_month_day
AS
SELECT DATEPART(YEAR, updated) AS [year], 
       DATEPART(MONTH, updated) AS [month],
       DATEPART(DAY, updated) AS [dayofmonth],
       *
FROM OPENROWSET(BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.csv') AS data;

Otro ejemplo: puede crear una nueva tabla que resuma el número de casos observados en cada mes, independientemente del año, para evaluar cómo afecta la estacionalidad a la propagación en un país o región determinados. Usa la tabla creada en el ejemplo anterior con la nueva columna month como origen:

CREATE TABLE dbo.infections_by_month_2022
AS
SELECT country_region,
       DATEPART(MONTH, updated) AS [month],
       SUM(CAST(confirmed as bigint)) AS [confirmed_sum]
FROM OPENROWSET(BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.jsonl') AS data
WHERE DATEPART(YEAR, updated) = '2022'
GROUP BY country_region, DATEPART(MONTH, updated);

Según esta nueva tabla, podemos ver que Estados Unidos observó más casos confirmados en todos los años en el mes de January, seguido de December y October. April es el mes con el número más bajo de casos en total:

SELECT * FROM dbo.infections_by_month_2022
WHERE country_region = 'United States'
ORDER BY confirmed_sum DESC;

Captura de pantalla de los resultados de la consulta que muestra el número de infecciones por mes en Estados Unidos, ordenadas por mes, en orden descendente. El mes número 1 se muestra en la parte superior.

Para obtener más ejemplos y una referencia sintáctica, consulte CREATE TABLE AS SELECT (Transact-SQL).

Ingesta de datos en tablas existentes con consultas T-SQL

En los ejemplos anteriores se crean nuevas tablas basadas en el resultado de una consulta. Para replicar los ejemplos, pero en las tablas existentes, el patrón INSERT ... SELECT se puede usar.

Ingresar datos de la tabla Almacén

El código siguiente ingiere nuevos datos de una tabla de almacenamiento en una tabla existente:

INSERT INTO dbo.bing_covid19_data_2023
SELECT *
FROM dbo.bing_covid19_data
WHERE DATEPART(YEAR, updated) = '2023';

Los criterios de consulta de la instrucción SELECT pueden ser cualquier consulta válida, siempre y cuando los tipos de columna de consulta resultantes se alineen con las columnas de la tabla de destino. Si se especifican nombres de columna e incluyen solo un subconjunto de las columnas de la tabla de destino, todas las demás columnas se cargan como NULL. Para obtener más información, consulte Usar INSERT INTO…SELECT para realizar una importación masiva de datos con registros y paralelismos mínimos.

Ingesta de datos desde la carpeta delta Lake

Las carpetas de Delta Lake que se conservan en OneLake se representan automáticamente como tablas si se almacenan en la carpeta /Tables en un lakehouse.

El código siguiente ingiere nuevos datos de la sección de carpetas /Tables/bing_covid19_delta_lake de Delta Lake en lakehouse MyLakehouse* .

INSERT INTO dbo.bing_covid19_data_2023
SELECT *
FROM MyLakehouse.dbo.bing_covid19_delta_lake 
WHERE DATEPART(YEAR, updated) = '2023';

Ingesta de datos del archivo CSV/Parquet/JSONL

Puede usar la OPENROWSET función como origen para ingerir archivos Parquet, CSV o JSON desde el almacenamiento:

INSERT INTO dbo.bing_covid19_data_2023
SELECT *
FROM OPENROWSET(BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet') AS data
WHERE DATEPART(YEAR, updated) = '2023';

Puede leer varios archivos mediante caracteres comodín como *.parquet, o dirigiéndose a directorios particionados como /year=*/month=*. Para optimizar el rendimiento, aplique filtros en la cláusula WHERE para eliminar filas y particiones innecesarias durante la ejecución de la consulta.

Este ejemplo es similar a los usados en la ingesta con COPY INTO. El comando COPY INTO es más fácil de usar, especialmente para cargas sencillas de datos de origen a destino. Sin embargo, si necesita transformar los datos de origen (por ejemplo, convertir valores o combinar con otras tablas), el uso de INSERT ... SELECT proporciona la flexibilidad de realizar transformaciones durante el proceso de ingestión.

Ingesta de datos de OneLake

Puede usar la OPENROWSET función como origen para ingerir datos de Fabric OneLake Storage. Reemplace {workspaceId} y {lakehouseId} por los GUID de espacio de trabajo y lakehouse correspondientes en el ejemplo siguiente.

INSERT INTO dbo.bing_covid19_data_2023
SELECT *
FROM OPENROWSET(BULK 'https://onelake.dfs.fabric.microsoft.com/{workspaceId}/{lakehouseId}/Files/year=*/month=*/*.parquet') AS data
WHERE data.filepath(1) = '2023'

Este ejemplo se basa en el anterior que lee datos de Azure Data Lake Storage. Use este enfoque cuando necesite transformar los datos de origen, por ejemplo, convertir valores, combinar con otras tablas o leer particiones específicas. En tales casos, el uso INSERT ... SELECT de proporciona la flexibilidad de aplicar transformaciones durante la ingesta de datos.

Ingesta de datos de tablas en diferentes almacenamientos y almacenes de lago

Para ambos CREATE TABLE AS SELECT y INSERT ... SELECT, la instrucción SELECT también puede hacer referencia a tablas en almacenes distintos del almacén donde se almacena la tabla de destino, mediante consultas entre almacenes. Esto se puede lograr mediante la convención de nomenclatura de tres partes [warehouse_or_lakehouse_name.][schema_name.]table_name. Por ejemplo, supongamos que tiene los siguientes recursos de área de trabajo:

  • Una instancia de lago de datos denominada cases_lakehouse con los datos de casos más recientes.
  • Un almacén denominado reference_warehouse con tablas usadas para los datos de referencia.
  • Almacén denominado research_warehouse donde se crea la tabla de destino.

Se puede crear una nueva tabla que use nombres de tres partes para combinar datos de tablas en estos recursos del área de trabajo:

CREATE TABLE research_warehouse.dbo.cases_by_continent
AS
SELECT *
FROM cases_lakehouse.dbo.bing_covid19_data AS cases
INNER JOIN reference_warehouse.dbo.bing_covid19_data AS reference
ON cases.iso3 = reference.countrycode;

Para obtener más información sobre las consultas entre almacenamientos, consulte Escribir una consulta SQL entre bases de datos.

Auditoría y supervisión de la ingesta de T-SQL

Las operaciones y CTASINSERT ... SELECT ejecutadas a través de T-SQL aparecen en el historial o actividad de consultas de almacenamiento, y se pueden supervisar junto con otras operaciones de almacenamiento.

Opciones de ingesta de datos

Entre otras formas de ingerir datos en el almacén de datos se incluyen:

Contenido relacionado

  • Last updated on