SQL y Power Query son dos herramientas que se utilizan para manipular y analizar datos y que popularmente se consideran herramientas no relacionadas entre sí.
Sin embargo, vamos a ver que, a pesar de sus amplias diferencias, comparten una serie de puntos en común que pueden ayudarnos a entender mejor cómo funcionan ambas herramientas y decidir en qué momento usar una u otra.
Es importante indicar que el contexto de este artículo está enfocado al uso de ambas herramientas en un entorno de transformación de datos para la creación de sistemas analíticos.
Introducción a SQL y Power Query
Cuando hablamos de tratar datos, los usuarios necesitamos herramientas y lenguajes que nos ayudan con la exploración de dichos datos, ya que es la tarea más importante, y por la que debemos de empezar cualquier proceso de descubrimiento de la información. Pero entendamos primero qué es cada cosa.
SQL es un lenguaje de programación que se utiliza para interactuar con bases de datos relacionales. Entre otras múltiples tareas, SQL se utiliza para efectuar consultas con el fin de recuperar información de forma sencilla y escalable (es decir, poder manejar muchos datos), pero también para realizar cambios en los datos: inserciones, actualizaciones y borrados. SQL es sin duda el lenguaje por excelencia que debemos de conocer para el manejo de bases de datos.
Por otro lado, Power Query es un motor de transformación y preparación de datos. Este motor incluye una interfaz gráfica que nos va a permitir conectarnos a múltiples orígenes de datos y un editor de Power Query donde aplicar las transformaciones sobre los datos. Las dos aplicaciones por excelencia que llevan incorporado Power Query son Excel y Power BI Desktop, dos herramientas muy cercanas al usuario final. Aunque no es el único sitio donde podemos utilizar Power Query, dentro de los distintos servicios de Azure, Microsoft ha incorporado esta herramienta para facilitar la creación de flujos de datos que “rellenen” tablas y que acercan Power Query a perfiles mucho más técnicos.
Por regla general, podemos decir que Power Query es más fácil de usar que SQL ya que no requiere conocimientos de programación, lo que la convierte en una buena aliada para los interesados en tratar la información pero que no tiene grandes conocimientos de SQL.
A pesar de la gran diferencia que hay entre los dos, hay que tener en cuenta que las transformaciones que se realizan con Power Query están basadas en la misma filosofía que debemos conocer a la hora de escribir sentencias SQL para tratar los datos.
Pero el conocer una de las dos, no debe eximirnos de conocer la otra. Si entendemos el funcionamiento de ambas, así como las principales similitudes, diferencias y limitaciones, nos permitirá poder cambiar de herramienta de una manera mucho más ágil y ayudarnos a gestionar mejor nuestros datos de cara a crear mejores modelos analíticos.
Similitudes entre SQL y Power Query
A grandes rasgos, ambas herramientas pueden:
- Manipular datos.
- Realizar operaciones de filtrado, clasificación y agregación de datos.
- Ambas pueden trabajar con datos de múltiples tipos.
- Mezclar información de distintas tablas.
- Generar nueva información a partir de los datos origen.
Diferencias entre SQL y Power Query
Pero antes de decidir si solo vamos a usar una de las dos herramientas para tratar nuestros datos, porque puedan parecernos que tiene una funcionalidad parecida, debemos evaluar también sus diferencias, ya que estos puntos son los que van a guiarnos a utilizar una herramienta u otra en función de nuestro escenario.
- Power Query es más fácil de usar que SQL y no requiere conocimientos de programación, lo que nos puede facilitar su uso como herramienta de entrada para nuestros primeros procesos de limpieza de datos.
- SQL es más adecuado para aplicaciones empresariales y de nivel empresarial, mientras que Power Query es más adecuado para aplicaciones de nivel de usuario y de pequeñas empresas. Aunque ambos podemos usarlos en los dos escenarios con resultados parecidos.
- SQL es más rápido y eficiente que Power Query para grandes conjuntos de datos y aplicaciones de alta demanda. Power Query es más adecuado para aplicaciones de menor escala y conjuntos de datos más pequeños.
- SQL se va a ejecutar utilizando la capacidad de cómputo y almacenaje del servidor de base de datos, mientras que Power Query lo va a hacer utilizar la capacidad de cómputo y almacenaje del sistema donde lo estemos ejecutando (que muchas veces es nuestro propio ordenador)
- Power Query se puede traducir a lenguaje M, dotándole de mayores funcionalidades, pero también de mayor dificultad, pero eso lo dejaremos para otro artículo.
Mismo resultado, dos procesos distintos, pero similares
Vamos a centrarnos en la parte parecida de ambas herramientas. Supongamos que estamos en un escenario donde queremos analizar las ventas de nuestra tienda, y queremos traernos la información de nuestros productos y clientes y recoger la información agregada de nuestras tiendas.
Supongamos que tenemos el siguiente esquema con todas las tablas que contienen la información de nuestros clientes:
Y queremos obtener una tabla con los datos básicos de los clientes de Francia, incluyendo su número de teléfono y mostrando su dirección de casa.
Para eso vamos a tener que juntar los datos de las siguientes tablas:
- Person:
o Columnas:
– BusinessEntityID
– Title
– FirstName
– LastName - BusinessEntityAddress
o Filtro
– AddressType = 2 — Direcciones de casa - Address
o Columnas:
– AddressLine1
– City
– PostalCode - StateProvince
o Columnas
– Name
o Filtro:
– CountryRegionCode = ‘FR’ — Direcciones de Francia - PersonPhone
o Columnas
– Phone - PhoneNumberType
o Columnas
– PhoneType — Para pivotar los diferentes números de teléfono
Inicia tu proyecto de migración a la nube
¿Hablamos?
Aproximación con SQL
Una única sentencia SQL que realice el JOIN entre las distintas tablas involucradas, utilizando las claves foráneas:
FROM Person.Person p
inner join person.BusinessEntityAddress ba
on p.BusinessEntityID = ba.BusinessEntityID
inner join Person.Address a
on ba.AddressID = a.AddressID
inner join Person.StateProvince sp
on a.StateProvinceID = sp.StateProvinceID
inner join person.PersonPhone phone
on p.BusinessEntityID = phone.BusinessEntityID
inner join person.PhoneNumberType phonety
on phone.PhoneNumberTypeID = phonety.PhoneNumberTypeID
Añadimos una clausula WHERE para filtra las direcciones que son de tipo ‘Home’ y de Francia.
where ba.AddressTypeID = 2 -- Home Address
and sp.CountryRegionCode = 'FR' --Francia
Además de seleccionar solo las columnas que queremos que devuelva la consulta.
SELECT P.BusinessEntityID AS BI,
Title,
FirstName,
MiddleName,
LastName,
AddressLine1,
City,
PostalCode,
sp.Name as [State Name],
phone.PhoneNumber as Phone,
phonety.Name as PhoneType
Ojo, porque una persona puede tener varios números de teléfono, y el modelo, nos generaría un registro por cada número de teléfono.
Pero como queremos que los teléfonos estén separados en columnas diferentes, tendremos que pivotar la información para que nos quede un único registro por persona
Por lo que vamos a utilizar la función PIVOT de SQL para que realice esa operación, quedando la sentencia SQL de la siguiente manera:
SELECT BI,
Title,
FirstName,
MiddleName,
LastName,
AddressLine1,
City,
PostalCode,
[State Name],
Work,
cell,
Home
from (
SELECT P.BusinessEntityID AS BI,
Title,
FirstName,
MiddleName,
LastName,
AddressLine1,
City,
PostalCode,
sp.Name as [State Name],
phone.PhoneNumber as Phone,
phonety.Name as PhoneType
FROM Person.Person p
inner join person.BusinessEntityAddress ba
on p.BusinessEntityID = ba.BusinessEntityID
inner join Person.Address a
on ba.AddressID = a.AddressID
inner join Person.StateProvince sp
on a.StateProvinceID = sp.StateProvinceID
inner join person.PersonPhone phone
on p.BusinessEntityID = phone.BusinessEntityID
inner join person.PhoneNumberType phonety
on phone.PhoneNumberTypeID = phonety.PhoneNumberTypeID
where ba.AddressTypeID = 2 -- Home Address
and sp.CountryRegionCode = 'FR' --Francia
) as SourceTable
PIVOT(
Max(Phone)
for PhoneType in (Work, Cell, Home)
) AS PIVOTABLE
Obteniendo el siguiente resultado:
Cursos de SQL
Solución con Power Query y base datos con relaciones
Si quisiéramos conseguir este mismo resultado utilizando Power Query, tendríamos que descomponer la sentencia SQL en varios pasos. A continuación vamos a ver un ejemplo de secuencia.
- Conectarnos a la base de datos para traer las tablas involucradas. Vamos a empezar por la tabla Person.person
- Eliminar las columnas que no nos hacen falta. Vamos a quedarnos solo con las siguientes:
a. BusinessEntityID
b. Title
c. FirstName
d. LastName - En este caso, como tenemos relaciones marcadas en la base de datos, vamos a ver que nos aparecen columnas que llamaremos “especiales”, que en nuestro caso son las siguientes:
a. PersonPhone
b. BusinessEntity
Estas columnas, en realidad no existen como tal en la tabla, si no que nos muestran la relación existente en la base de datos y que equivale al JOIN que hemos realizado en la sentencia SQL. Vamos a quedarnos también con esas columnas.
4. Una vez seleccionadas las columnas que queremos, con el menú contextual sobre cualquiera de ellas, hacemos click en la opción ‘Quitar otras columnas’.
5. De esta manera, nos quedamos con 4 columnas que tiene un valor concreto y que tiene un tipo de dato específico (entero, cadena de texto, etc.) y dos columnas que son “especiales” ya que nos van a permitir saltar a otras tablas de manera directa desde Power Query. Ese tipo de saltos los vamos a poder identificar en aquellas columnas que tiene este icono:
6. Vamos a expandir la columna person.BusinessEntity solo hacia la columna Person.BusinessEntityAddress. Vamos a ver como la nueva columna que aparece es de tipo tabla, es decir, hemos saltado por el diagrama hasta la tabla que nos va a permitir acceder a las direcciones de los clientes.
7. Vamos a volver a expandir esa columna llamada person.BusinessEntityAddress para quedarnos con las columnas person.address y person.addresstype
A su vez, la columna person.AddressType, vamos a volver a expanderla para traernos la columna AddressTypeID y posteriormente filtrar para las que tienen un valor ‘2’
A la hora de filtrar, solo nos aparecen los valores que se ha traído Power Query como muestra para trabajar, si quisiéramos filtrar por un valor diferente al de la muestra, tendríamos que forzar la carga utilizando ‘Cargar más’ hasta que apareciera el valor que buscamos, o realizar un filtro a mano en la barra de fórmulas, sustituyendo el 2 por otro valor.
8. Ahora vamos a traer los datos de las direcciones, para lo que vamos a expadir la columna person.address seleccionando las columnas AddressLine1, City y Person.StateProvince
9. Vemos que nos vuelve a aparecer una columna “especial” llamada Person.StateProvince que nos va a permitir expandirnos a la tabla donde se indica a qué país pertenece la dirección.
10. Expandimos esa columna y ahora ya podemos filtrar para quedarnos con aquellas direcciones que son de ‘Francia’ (FR)
Ahora nos falta añadir la información de los teléfonos, para lo cual vamos a expandir la columna person.personphone:
Vemos que la columna PhoneNumberType podemos seguir expandiéndola para traernos la descripción del tipo de nombre:
11. Como vemos, el tipo de teléfono está dentro de una columna, pero queremos que sea la cabecera de la columna donde se guarda el número de teléfono. Para eso, vamos a utilizar la opción ‘Columna Dinámica’. Seleccionamos la columna Name donde están guardados los Nombre de los tipos de teléfono y seleccionamos la trasformación ‘Columna Dinámica’
12. Vamos a indicar que la columna de valores asociada es el PhoneNumber y que no realice ninguna agregación. De esta manera nos habrá creado columnas adicionales para mostrar el teléfono:
En este caso solo tenemos dos columnas, no ha creado una columna Work porque en el muestreo de datos con el que trabaja Power Query no existía ninguna fila con teléfono de tipo Work, OJO con este tipo de situaciones, si cambiáramos de país a ‘US’ si se genera esta columna adicional en el modelo ya que hay clientes con los tres tipos de teléfono.
Aproximación con Power Query y base de datos sin relaciones
Sin embargo, no siempre nos encontramos con bases de datos que tiene las relaciones marcadas, eso nos obliga a llevar una aproximación diferente, en ese caso, no vamos a encontrarnos con esas columnas “especiales” y tendremos que ir trayendo de manera independiente cada una de las tablas con las que vamos a completar nuestra información.
Vamos a realizar solo los pasos para incorporar la información de los números de teléfono para ver de modo ilustrativo como realizaríamos la operación JOIN para unir ambas tablas.
- Vamos a conectarnos para traer la tabla person.person, pero esta vez nos vamos a quedar solo con las siguientes columnas para simular un entorno sin relaciones:
a. BusinessEntityID
b. Title
c. FirstName
d. LastName - A continuación nos traeremos las tablas person.PersonPhone y person.PhoneNumberType,
- Vamos a combinar las tablas person.PersonPhone y person.PhoneNumberType para que la tabla PersonPhone contenga el tipo de teléfono, para eso, vamos a situarnos en la tabla PersonPhone y seleccionar la opción combinar consultas.
- En la ventana vamos a definir como se relacionan las tablas, es decir el equivalente al JOIN en nuestro SQL así como el tipo de JOIN si va a ser un LEFT/RIGHT OUTER JOIN, u otro tipo de JOIN. Esa selección se indica en el campo “Tipo de Combinación”, además debemos seleccionar las columnas por las que vamos a realizar el JOIN entre las tablas.
Esta ventana muestra el equivalente a este JOIN:
p.BusinessEntityID = phone.BusinessEntityID
inner join person.PhoneNumberType phonety
on phone.PhoneNumberTypeID = phonety.PhoneNumberTypeID
Una vez realizada esta combinación, vemos que nos aparece una columna que podemos expandir, equivalente a las que teníamos cuando accedíamos a tablas con la relación marcada en la base de datos. En este caso, vamos a expandir, seleccionando el nombre del tipo de teléfono.
Y por último en la tabla person, vamos a realizar una nueva combinación entre la tabla person y la tabla personphone para recoger los tres tipos de datos y poder dinamizar la columna ‘PhoneNumberType’ como hicimos en el capítulo anterior.
Conclusiones
Como comentábamos en la parte de diferencias, Power Query va a utilizar nuestro ordenador/servidor para ejecutarse, por lo que es importante que tengamos en cuenta si vamos a manejar todos los datos directamente en SQL, si vamos a manejarlos directamente en Power Query, o si vamos a ir a un escenario híbrido donde hagamos la parte más pesada en SQL y posteriormente continuemos en Power Query.
TIP: Power Query tiene cierta inteligencia y es capaz de delegar en el origen de datos parte del proceso de transformación cuando detecta que las operaciones que estamos haciendo se pueden hacer directamente en SQL.
Si pulsamos con el botón derecho sobre cualquiera de los pasos podemos ver una opción llamada ‘Ver Consulta Nativa’, si dicha opción está activa, podremos ver la sentencia SQL que va a realizar contra nuestro origen de datos, es decir, todos los pasos anteriores se van a encapsular en una sentencia SQL para utilizar los recursos del origen de datos. Desafortunadamente no todos los pasos son compatibles con el origen de datos, aquellos pasos que no tiene esa opción habilitada, es porque no pueden traducirse en una sentencia SQL equivalente, y a partir de ese paso, todo pasará a ejecutarse en nuestro ordenador.
SQL y Power Query son dos herramientas que se utilizan para manipular y analizar datos. Ambas herramientas tienen similitudes y diferencias clave. SQL es un lenguaje de programación completo que se utiliza para interactuar con bases de datos relacionales, mientras que Power Query es una herramienta de preparación de datos que se utiliza para importar, transformar y combinar datos de diversas fuentes. SQL es más adecuado para aplicaciones empresariales y de nivel empresarial, mientras que Power Query es más adecuado para aplicaciones de nivel de usuario y de pequeñas empresas. Si necesita trabajar con grandes conjuntos de datos y aplicaciones de alta demanda, SQL es la mejor opción. Si necesita trabajar con aplicaciones de menor escala y conjuntos de datos más pequeños, Power Query es la mejor opción. Aunque siempre podemos optar por una aproximación híbrida que junte lo mejor de los dos mundos.
Fórmate con profesionales técnicos en activo certificados por Microsoft en nuestros cursos de SQL para todos los niveles y nuestro curso de Power Query, ¡bonificables por FUNDAE!
