Carga de datos hacia Azure Synapse Analytics (SQL Data Warehouse) con BCP

PorNelson López Centeno

Carga de datos hacia Azure Synapse Analytics (SQL Data Warehouse) con BCP

bcp (Bulk Copy Program) es una utilidad que permite insertar datos desde un archivo hacia una tabla SQL, y exportar desde una tabla hacía un archivo. En esta entrada cargaremos un archivo CSV con unas 2.5 millones de filas hacia una tabla en un almacén de datos en Azure Synapse Analytics (SQL Data Warehouse).

El archivo contiene datos de los vuelos comerciales dentro de los Estados Unidos entre julio y octubre de 2019 (fuente: https://www.transtats.bts.gov/DL_SelectFields.asp?Table_ID=236&DB_Short_Name=On-Time).
Para crearlo seleccioné algunas de las columnas disponibles, descargué los archivos CSV para cada mes y después los uní en uno solo.

Como se puede ver en la siguiente muestra, la primera línea del archivo contiene los encabezados, las columnas están separadas por coma y las cadenas de caracteres están rodeadas por comillas dobles.

"FL_DATE","OP_UNIQUE_CARRIER","OP_CARRIER_FL_NUM","ORIGIN_AIRPORT_ID","ORIGIN","ORIGIN_CITY_NAME","ORIGIN_STATE_ABR","DEST_AIRPORT_ID","DEST","DEST_CITY_NAME","DEST_STATE_ABR","CRS_DEP_TIME","DEP_TIME","DEP_DELAY","CRS_ARR_TIME","ARR_TIME","ARR_DELAY","CANCELLED","CANCELLATION_CODE","DIVERTED",
2019-07-01,"9E","5130",10685,"BMI","Bloomington/Normal, IL","IL",10397,"ATL","Atlanta, GA","GA","0600","0558",-2.00,"0851","0830",-21.00,0.00,"",0.00,
2019-07-01,"9E","5132",10990,"CHO","Charlottesville, VA","VA",12953,"LGA","New York, NY","NY","1305","1416",71.00,"1431","1530",59.00,0.00,"",0.00,
2019-07-01,"9E","5133",11042,"CLE","Cleveland, OH","OH",12478,"JFK","New York, NY","NY","0700","0703",3.00,"0845","0838",-7.00,0.00,"",0.00,
2019-07-01,"9E","5134",15919,"XNA","Fayetteville, AR","AR",10397,"ATL","Atlanta, GA","GA","1520","1516",-4.00,"1812","1803",-9.00,0.00,"",0.00,
2019-07-01,"9E","5135",10581,"BGR","Bangor, ME","ME",12953,"LGA","New York, NY","NY","1646","1635",-11.00,"1829","1753",-36.00,0.00,"",0.00,
2019-07-01,"9E","5135",12953,"LGA","New York, NY","NY",10581,"BGR","Bangor, ME","ME","1400","1355",-5.00,"1549","1523",-26.00,0.00,"",0.00,
2019-07-01,"9E","5138",11953,"GNV","Gainesville, FL","FL",10397,"ATL","Atlanta, GA","GA","0715","0710",-5.00,"0842","0821",-21.00,0.00,"",0.00,
2019-07-01,"9E","5140",15249,"TLH","Tallahassee, FL","FL",10397,"ATL","Atlanta, GA","GA","1238","1233",-5.00,"1401","1334",-27.00,0.00,"",0.00,
2019-07-01,"9E","5141",10581,"BGR","Bangor, ME","ME",12953,"LGA","New York, NY","NY","1119","1114",-5.00,"1259","1230",-29.00,0.00,"",0.00,
2019-07-01,"9E","5141",12953,"LGA","New York, NY","NY",10581,"BGR","Bangor, ME","ME","0859","0856",-3.00,"1043","1040",-3.00,0.00,"",0.00,
2019-07-01,"9E","5142",14524,"RIC","Richmond, VA","VA",12478,"JFK","New York, NY","NY","0725","0717",-8.00,"0900","0837",-23.00,0.00,"",0.00,
2019-07-01,"9E","5145",12478,"JFK","New York, NY","NY",10821,"BWI","Baltimore, MD","MD","2040","2054",14.00,"2223","2158",-25.00,0.00,"",0.00,


En Azure he creado un recurso Synapse Analytics (SQL Data Warehouse) con el nombre AcmeDW y ahora crearé la tabla StgFlights, para importar los datos.

CREATE TABLE [dbo].[StgFlights] 
(
	FL_DATE date,
	OP_UNIQUE_CARRIER char(2),
	OP_CARRIER_FL_NUM int,
	ORIGIN_AIRPORT_ID int,
	ORIGIN char(3),
	ORIGIN_CITY_NAME varchar(40),
	ORIGIN_STATE_ABR char(2),
	DEST_AIRPORT_ID int,
	DEST char(3),
	DEST_CITY_NAME varchar(40),
	DEST_STATE_ABR char(2),
	CRS_DEP_TIME char(4),
	DEP_TIME char(4),
	DEP_DELAY decimal(8,2),
	CRS_ARR_TIME char(4),
	ARR_TIME char(4),
	ARR_DELAY decimal(8,2),
	CANCELLED char(4),
	CANCELLATION_CODE char(1),
	DIVERTED char(4)
)
WITH  
(   
	DISTRIBUTION = ROUND_ROBIN,  
	CLUSTERED COLUMNSTORE INDEX
)

La tabla tiene las mismas columnas que el archivo CSV. Los nombres de las columnas de la tabla y el orden coinciden con los del archivo, aunque no tiene por qué ser así.

Para cargar los datos con bcp desde un archivo CSV, es recomendable preparar un archivo de formato, que permite definir los separadores de columna y de líneas y relacionar las columnas del archivo de datos y la tabla.

Con bcp podemos crear un archivo de formato a partir de la tabla, con una línea de comandos como esta:

bcp AcmeDW.dbo.StgFlights format nul -f flights.fmt -c -S xxxxx.database.windows.net -U sqluser -P sqlpassword
donde:
  • AcmeDW.dbo.StgFlights es el nombre completo de la tabla SQL
  • format null -f indica que queremos crear un archivo de formato con el nombre flights.fmt
  • -c indica que el archivo de datos tiene caracteres de un solo byte (char), otras opciones disponibles son -w para caracteres unicode (nchar) y -n para formato nativo
  • -S -U y -P contienen el nombre del servidor SQL, el usuario y la contraseña

Al ejecutar el comando, se creó el archivo flights.fmt con el siguiente contenido:

14.0
20
1       SQLCHAR             0       11      "\t"     1     FL_DATE                                ""
2       SQLCHAR             0       2       "\t"     2     OP_UNIQUE_CARRIER                      Latin1_General_CI_AS
3       SQLCHAR             0       12      "\t"     3     OP_CARRIER_FL_NUM                      ""
4       SQLCHAR             0       12      "\t"     4     ORIGIN_AIRPORT_ID                      ""
5       SQLCHAR             0       3       "\t"     5     ORIGIN                                 Latin1_General_CI_AS
6       SQLCHAR             0       40      "\t"     6     ORIGIN_CITY_NAME                       Latin1_General_CI_AS
7       SQLCHAR             0       2       "\t"     7     ORIGIN_STATE_ABR                       Latin1_General_CI_AS
8       SQLCHAR             0       12      "\t"     8     DEST_AIRPORT_ID                        ""
9       SQLCHAR             0       3       "\t"     9     DEST                                   Latin1_General_CI_AS
10      SQLCHAR             0       40      "\t"     10    DEST_CITY_NAME                         Latin1_General_CI_AS
11      SQLCHAR             0       2       "\t"     11    DEST_STATE_ABR                         Latin1_General_CI_AS
12      SQLCHAR             0       19      "\t"     12    CRS_DEP_TIME                           ""
13      SQLCHAR             0       19      "\t"     13    DEP_TIME                               ""
14      SQLCHAR             0       41      "\t"     14    DEP_DELAY                              ""
15      SQLCHAR             0       19      "\t"     15    CRS_ARR_TIME                           ""
16      SQLCHAR             0       19      "\t"     16    ARR_TIME                               ""
17      SQLCHAR             0       41      "\t"     17    ARR_DELAY                              ""
18      SQLCHAR             0       3       "\t"     18    CANCELLED                              Latin1_General_CI_AS
19      SQLCHAR             0       3       "\t"     19    CANCELLATION_CODE                      Latin1_General_CI_AS
20      SQLCHAR             0       41      "\r\n"   20    DIVERTED                               ""

Ya casi estamos listos para cargar los datos del archivo CSV utilizando bcp, con una línea de comandos como esta:

bcp AcmeDW.dbo.StgFlights in 182553109_T_ONTIME_REPORTING_2019.csv -f flights.fmt  -F 2 -e error.txt -q -S xxxxx.database.windows.net -U sqluser -P sqlpassword
donde:
  • AcmeDW.dbo.StgFlights es el nombre completo de la tabla SQL
  • in 182553109_T_ONTIME_REPORTING_2019.csv indica que queremos insertar los datos del un archivo
  • -f flights.fmt, es al archivo de formato
  • -F 2, indica que comience desde la segunda fila, para que ignore la fila de encabezados
  • -e error.txt, indica que si hay errores se registren en un archivo
  • -q es para forzar la ejecución del comando QUOTED_IDENTIFIER to ‘ON’, porque por defecto bcp ejecuta QUOTED_IDENTIFIER to ‘OFF’, pero Azure Synapse no lo soporta en OFF
  • -S -U y -P contienen el nombre del servidor SQL, el usuario y la contraseña

Sin embargo, si ejecutamos el comando, recibiremos un error como el siguiente

Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 17 for SQL Server]Unexpected EOF encountered in BCP data-file

BCP copy in failed

La causa del error está en la columna 5 del archivo de formato, donde se indica que el separador para cada columna es un tab («\t»), y los separadores de filas son el retorno y cambio de línea («\r\n»). Pero nuestro archivo de datos CSV utliza la coma (,) como separador de columnas y el cambio de línea como separador de fila, por lo que tenemos que cambiar los tabs a comas («,») y el último separador a «,\n».

Si aplicamos estos cambios al archivo de formato y volvemos a ejecutar el comando, recibiremos otro error, como el siguiente

Starting copy...
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 17 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 17 for SQL Server]String data, right truncation
Y si miramos en el archivo error.tx encontraremos errores como estos
#@ Row 1, Column 2: String data, right truncation @#
2019-07-01	"9E"	"5130"	10685	"BMI"	"Bloomington/Normal	 IL"	"IL"	10397	"ATL"	"Atlanta	 GA"	"GA"	"0600"	"0558"	-2.00	"0851"	"0830"	-21.00	0.00,"",0.00	
#@ Row 2, Column 2: String data, right truncation @#
2019-07-01	"9E"	"5132"	10990	"CHO"	"Charlottesville	 VA"	"VA"	12953	"LGA"	"New York	 NY"	"NY"	"1305"	"1416"	71.00	"1431"	"1530"	59.00	0.00,"",0.00	
Lo que ha pasado es que la columna 2 del archivo CSV se ha leído como "9E", incluyendo las dobles comillas al inicio y al final, lo que hace un total de 4 caracteres, pero el campo en la tabla es de tipo char(2), por lo que tendría que truncar el valor. La solución a este problema es modificar el archivo de formato para incluir las dobles comillas («\»») como parte del separador de columna, cuando corresponda.

Después de hacer las modificaciones, el archivo de formato quedaría asi

14.0
20
1       SQLCHAR             0       11      ",\""       1     FL_DATE                                ""
2       SQLCHAR             0       2       "\",\""     2     OP_UNIQUE_CARRIER                      Latin1_General_CI_AS
3       SQLCHAR             0       12      "\","       3     OP_CARRIER_FL_NUM                      ""
4       SQLCHAR             0       12      ",\""       4     ORIGIN_AIRPORT_ID                      ""
5       SQLCHAR             0       3       "\",\""     5     ORIGIN                                 Latin1_General_CI_AS
6       SQLCHAR             0       40      "\",\""     6     ORIGIN_CITY_NAME                       Latin1_General_CI_AS
7       SQLCHAR             0       2       "\","       7     ORIGIN_STATE_ABR                       Latin1_General_CI_AS
8       SQLCHAR             0       12      ",\""       8     DEST_AIRPORT_ID                        ""
9       SQLCHAR             0       3       "\",\""     9     DEST                                   Latin1_General_CI_AS
10      SQLCHAR             0       40      "\",\""     10    DEST_CITY_NAME                         Latin1_General_CI_AS
11      SQLCHAR             0       2       "\",\""     11    DEST_STATE_ABR                         Latin1_General_CI_AS
12      SQLCHAR             0       19      "\",\""     12    CRS_DEP_TIME                           ""
13      SQLCHAR             0       19      "\","       13    DEP_TIME                               ""
14      SQLCHAR             0       41      ",\""       14    DEP_DELAY                              ""
15      SQLCHAR             0       19      "\",\""     15    CRS_ARR_TIME                           ""
16      SQLCHAR             0       19      "\","       16    ARR_TIME                               ""
17      SQLCHAR             0       41      ","         17    ARR_DELAY                              ""
18      SQLCHAR             0       3       ",\""       18    CANCELLED                              Latin1_General_CI_AS
19      SQLCHAR             0       3       "\","       19    CANCELLATION_CODE                      Latin1_General_CI_AS
20      SQLCHAR             0       41      ",\n"       20    DIVERTED                               ""

Volvemos a ejecutar el comando bcp y ahora si vemos como los datos se van cargando en bloques de 1000 filas hasta completar los 2.5 millones.

Starting copy...
1000 rows sent to SQL Server. Total sent: 1000
1000 rows sent to SQL Server. Total sent: 2000
1000 rows sent to SQL Server. Total sent: 3000
1000 rows sent to SQL Server. Total sent: 4000
1000 rows sent to SQL Server. Total sent: 5000
...
...
1000 rows sent to SQL Server. Total sent: 2556000
1000 rows sent to SQL Server. Total sent: 2557000
1000 rows sent to SQL Server. Total sent: 2558000
1000 rows sent to SQL Server. Total sent: 2559000

2559483 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 32282  Average : (79285.14 rows per sec.)

Deja un comentario