Análisis simple de datos CSV en S3 con consultas SQL sin servidor
Como residente de Melbourne y viajero diario en nuestro sistema de tarifas de transporte público Myki (sin comentarios). Me intrigó cuando escuché que el conjunto de datos del Melbourne Datathon 2018 debía ser a gran escala, datos de uso de Myki en el mundo real.
¿Qué ideas interesantes podemos obtener sobre cómo nuestra bulliciosa ciudad utiliza su red de transporte público? ¡Vamos a averiguar!. Lo mejor de todo es que lo comprobamos sin transformarlo de CSV, o incluso sacándolo de S3.
Aquí hay un par de estadísticas rápidas que obtuve de este conjunto de datos de 1.800 millones de filas.
Con consultas SQL que se ejecutan en segundos, por mucho menos que el costo de una taza de café:
- Las tarifas consistían en un 65,65% de tarifa completa, un 33,65% en el pago de tarifas de concesión y un mísero 0,69% en concesiones de libre circulación.
- La ubicación favorita para un domingo para los poseedores de la tarjeta Victorian Seniors Concession es Box Hill Railway Station
AWS Athena
AWS Athena es un servicio de consultas sin administración y completamente administrado que le permite ejecutar consultas SQL contra datos almacenados en depósitos de S3, es un poco como magia.
Construido sobre Apache Hive y el Presto de Facebook , AWS Athena le permite definir (o incluso descubrir) el esquema de sus datos. Y luego comenzar a ejecutar consultas al instante. Y todo lo que paga es la información que AWS Athena tiene que buscar para ejecutar su consulta, y el costo de almacenarla en S3.
Así que primero obtengamos nuestros datos, introdúzcalos en S3 y preparemos nuestro esquema.
Preparando nuestros datos Myki en S3
Para echar un primer vistazo y explorar los archivos de datos, echa un vistazo a este enlace público de S3:
Edit: by request of Data Victoria, links to the data discussed in this article have been removed.
(bucket contents is 25.4gb)
---
Once again, so many thanks to Data Science Melbourne for organising the Melbourne Datathon and providing the data. Thanks also to PTV for releasing the dataset.
http://www.datasciencemelbourne.com/datathon/ https://www.ptv.vic.gov.au/
Aquí hay una breve descripción de los archivos que nos interesan para hoy:
events/ # our myki usage dataset is in here Samp_0/ # data is split into ten chunks ScanOffTransaction/ # myki 'touch on' events when people board ScanOnTransaction/ # myki 'touch off' events from disembarking 2015/ # year partitions ... 2017/ Week1/ # week partitions ... Week52/ QID3530164_20180713_12510_0.txt.gz # data! Samp_1/ ... Samp_9/ calendar/ # map dates to week numbers, days of week, etc. cardtypes/ # myki fare types: full fare, concession, etc. stops/ # links stop information to a stop id
¡Create un cubo y copia en los datos! (no dude en simplemente copiar uno de los Samp_Xdirectorios si desea tener una mejor visión de los datos)
aws s3 mb s3://mediumreader-myki-data aws s3 cp --recursive s3://bucket-no-longer-exists/ s3://mediumreader-myki-data/
¡Fácil! Ahora, le decimos a AWS Athena cómo se ven nuestros datos para que puedan consultarlos. Hacemos esto usando nuestro Catálogo de datos en AWS Glue, que se integra con AWS Athena.
Preparación de nuestro esquema de datos en AWS Glue Data Catalog
AWS Glue es el servicio ETL (extracción, transformación, carga) totalmente administrado de Amazon para facilitar la preparación y carga de datos de diversas fuentes de datos para análisis y procesamiento por lotes.
Hoy solo nos interesa usar Glue para el Catálogo de datos. Ya que nos permitirá definir un esquema en los datos de Myki que acabamos de verter en S3.
AWS Glue facilita esto al proporcionar “rastreadores” de datos que pueden ver nuestros datos y descubrir muchos de nuestros esquemas automáticamente.
- Navegue a AWS Glue
- Seleccione rastreadores en el menú
- Seleccione Agregar rastreador
- Nombra a nuestro rastreador myki_crawler
- Haga clic en Siguiente, no necesitamos ninguna configuración opcional aquí
- Almacén de datos S3
- Ruta especificada en mi cuenta
- Incluir ruta para mí es s3: // tomwwright-myki-data / , reemplazar con lo que haya llamado antes a su grupo cuando copió los datos
- Deje los patrones de Excluir en blanco, para este conjunto de datos solo limpiaremos cualquier basura que el rastreador encuentre que no queremos
- Haga clic en Siguiente, elija No para agregar otro almacén de datos, haga clic en Siguiente nuevamente.
- Seleccione Crear un rol de IAM (a menos que tenga roles en su lugar para Pegamento)
- Ingrese MykiCrawler para el nombre, o lo que quiera, soy una publicación de Medium, no su madre
-
Haga clic en siguiente
Haga clic en siguiente
- Seleccione Agregar base de datos , solo llamémoslo myki , no necesita ninguna configuración opcional, haga clic en Crear
- Deja toda la configuración opcional como está
- Haga clic en siguiente
- Haga clic en Finalizar!
Debería haber regresado a la pantalla de rastreadores de AWS Glue, así que seleccione myki_crawler y presione Ejecutar rastreador . Tomará aproximadamente 7 minutos correr, según mi experiencia, así que tal vez tómate un café o camina rápido.
Una vez que el rastreador se haya completado, podremos ver las tablas descubiertas en Bases de datos -> Tablas .
Lo primero que haremos es eliminar algunas tablas no deseadas que el rastreador ha creado al examinar algunos de los archivos que no son de datos en nuestro depósito de S3:
Elimine todas las tablas con la clasificación Desconocido , como se muestra en la siguiente captura de pantalla. Podríamos habernos ahorrado esta molestia al no copiarlos en el depósito en primer lugar, o agregarlos a la configuración de rutas excluidas del rastreador. Oh, bueno, lo suficientemente fácil como para simplemente eliminarlos.
Al abrir la tabla del calendario, podemos ver todas las grandes cosas que el rastreador ha descubierto para nosotros: formato de datos, delimitadores, recuentos de registros, tipos de columnas, etc. Una cosa que falta son los nombres de las columnas, porque esa información no está presente en los archivos de datos myki.
A continuación, encontrará algunas etiquetas de columna (no necesariamente todas) que debemos aplicar para poder escribir consultas legibles para nuestras tablas.
Seleccione una tabla y haga clic en Editar esquema en la esquina superior derecha para actualizar las columnas.
table: calendar
1 col0 bigint dateid 2 col1 string date 3 col2 bigint year 6 col5 string month 13 col12 string daytype # 'Weekday', 'Weekend', 'Public Holiday' 16 col15 string dayofweek
---
table: cardtypes
1 col0 bigint typeid 2 col1 string typedesc 3 col2 string paidorfree # 'Paid', 'Free' 4 col3 string concession # 'Full Fare', 'Concession'
---
table: events
1 col0 bigint mode 2 col1 string date 3 col2 string datetime 4 col3 bigint cardid 5 col4 bigint cardtypeid 6 col5 bigint vehicleid
8 col7 string routeid 9 col8 string stopid 10 partition_0 samplenum 11 partition_1 onoroff # 'ScanOffTransaction', 'ScanOnTra...on' 12 partition_2 year 13 partition_3 week
---
table: stops
1 col0 bigint stopid 2 col1 string name 3 col2 string longname 4 col3 string stoptype 5 col4 string suburb 6 col5 bigint postcode 10 col9 double lat 11 col10 double long
Con los nombres de nuestras columnas en su lugar, ¡finalmente estamos listos para hacer algunas consultas!
Consultando nuestro esquema con AWS Athena
Haciendo clic para el servicio AWS Athena nos encontramos primero en la pantalla del Editor de consultas. ¡Genial, seleccionemos nuestra mykibase de datos y conectemos una consulta de conteo simple!
Cuando tenemos una consulta con la que estamos realmente contentos podemos golpear Save asy darle un nombre. Luego podemos encontrarla fácilmente y ejecutarla de nuevo debajo Saved Queries.
AWS Athena también nos permite acceder a nuestro historial de consultas History. Donde podemos descargar los resultados de cualquier consulta.
Una métrica importante que se debe tener en cuenta aquí para sus consultas de AWS Athena son los datos escaneados. Ya que el precio de AWS Athena está directamente (y solo) relacionado con la cantidad de datos leídos por AWS Athena en el proceso de ejecución de su consulta:
“Se le cobra por la cantidad de bytes escaneados por Amazon Athena, redondeados al megabyte más cercano, con un mínimo de 10 MB por consulta.
$ 5 por TB de datos escaneados ”
Como opera en archivos planos sin formato, a AWS Athena le resulta difícil ser inteligente con respecto a los datos que escanea, y en general necesitará leer todos los archivos que su esquema especifica que conforman la tabla.
A menos que, al crear el esquema, se especifican particiones que definen “columnas” que se asignan a directorios que AWS Athena puede usar para ser inteligentes acerca de qué datos deben analizarse para una consulta determinada.
Para ilustrar, recordar la estructura de carpetas de nuestros datos:
events/ # our myki usage dataset is in here Samp_0/ # data is split into ten chunks ScanOffTransaction/ # myki 'touch on' events when people board ScanOnTransaction/ # myki 'touch off' events from disembarking 2015/ # year partitions ... 2017/ Week1/ # week partitions ... Week52/ QID3530164_20180713_12510_0.txt.gz # data! Samp_1/ ... Samp_9/ calendar/ # map dates to week numbers, days of week, etc. cardtypes/ # myki fare types: full fare, concession, etc. stops/ # links stop information to a stop id
Nuestra carpeta events está estructurada por una serie de subcarpetas que dividen de forma ordenada los datos de nuestros eventos en un par de columnas clave: una carpeta de muestra numerada. Ya sea un evento de contacto o de toma de contacto, el año y la semana.
Afortunadamente, nuestro rastreador AWS Glue fue lo suficientemente inteligente como para darse cuenta de todo eso, ¡y ya lo ha organizado en nuestro esquema! ¡Bonito!
Para ilustrar la importancia de estas particiones, he contado el número de tarjetas Myki únicas utilizadas en el año 2016 (unos 7,4 millones, por cierto) con dos consultas diferentes :
Una con un operador LIKE en la columna date de nuestros datos y otra utilizando nuestra columna year de particionamiento.
Nuestra primera consulta, usando date LIKE '2016%', tiene que escanear la eventstabla completa , todos los 25 gigabytes de ella, porque AWS Athena no puede determinar qué archivos podrían tener líneas coincidentes en ellos. El costo de esta consulta es de alrededor de $ 0.12.
¡Nuestra segunda consulta, utilizando year = '2016', es capaz de recuperar nuestros resultados y solo escanear 8 gigabytes de nuestros eventsdatos!.
Esto se debe a que yeares una de las columnas de partición que se asigna a patrones de clave particulares en nuestros datos S3. El costo de esta consulta es de aproximadamente $ 0.04, ¡y se ejecuta en menos de un tercio del tiempo!
La organización inteligente de sus datos con particiones es clave para poder ejecutar consultas rápidas y rentables en AWS Athena. Bien, sigamos adelante!
Paradas populares para el touch-off
Entonces, mi primer pensamiento, ¿a dónde va la gente usando su Myki? Parece una pregunta fácil de responder con SQL, comencemos por obtener una lista de las paradas más populares.
# top 20 stop ids for touch-off events
select stopid, count(*) as count from events where onoroff = 'ScanOffTransaction' group by stopid order by count desc limit 20;
Genial, podemos unir esto con nuestra tabla stops para una representación más legible para los humanos, hagámoslo.
# select our top stops like before, then join to our stops table
with topstops (stopid, count) as ( select stopid, count(*) as count from events where onoroff = 'ScanOffTransaction' group by stopid ) select topstops.stopid, stops.longname, stops.suburb, topstops.count from topstops left outer join stops on topstops.stopid = stops.stopid order by topstops.count desc limit 20;
Hmm Hay un montón de filas stopid en las que falta stopsinformación … Para mí, dado que son la mayoría de las paradas más populares y tiene el número correcto.
Creo que las 64xxxparadas son las paradas del tren City Loop, y tal vez 24001sea la estación de Richmond.
Aparte de eso, estos resultados tienen sentido para mí, lo que no sorprende en absoluto al ver que todas las paradas principales sean las paradas de ferrocarril.
Aparte de eso, estos resultados tienen sentido para mí, lo que no sorprende en absoluto al ver que todas las paradas principales sean las paradas de ferrocarril.
Desglose de los tipos de tarifas
Siempre parece haber un poco de charla sobre el gasto injustificado en la concesión de viajes en transporte público a varios grupos.
¿Cuál es el desglose de la tarifa completa frente a la concesión?. De nuevo, parece bastante simple. Comencemos agregando en el tipo de tarjeta como está presente en nuestra tabla events.
# aggregate our touch-on events by the card type used
select cardtypeid, count(*) as count from events where onoroff = 'ScanOnTransaction' group by cardtypeid order by count desc
Bien, igual que la última vez, unámonos a nuestra mesa cardtypes para ofrecer más información legible para los humanos:
# aggregate by card type as before, then join to card types # information to determine concession and paying status
with cardtypecounts (cardtypeid, count) as ( select cardtypeid, count(*) as count from events where onoroff = 'ScanOnTransaction' group by cardtypeid ) select cardtypecounts.cardtypeid, cardtypes.typedesc, cardtypes.concession, cardtypes.paidorfree, cardtypecounts.count from cardtypecounts left outer join cardtypes on cardtypecounts.cardtypeid = cardtypes.typeid order by cardtypecounts.count desc;
Los pasajeros de tarifa completa se ven como una mayoría abrumadora, con más de 4 veces el General Concessiontipo de tarifa de concesión más común (pero con una etiqueta vaga) . Pero luego, el resto de la lista se compone de los distintos tipos de concesión, por lo que podría no ser tan simple.
El único tipo de tarifa de conducción libre para descifrar este Top 14 es el Employee Travel Pass - tiene sentido.
Vayamos por un aspecto aún más alto agregando de nuevo. Tengo curiosidad por el desglose general de la tarifa completa frente a la concesión frente a la libre.
# further aggregating our query by concession and paying # statuses
with cardtypecounts (cardtypeid, count) as ( select cardtypeid, count(*) as count from events where onoroff = 'ScanOnTransaction' group by cardtypeid ) select cardtypes.concession, cardtypes.paidorfree, sum(cardtypecounts.count) as count from cardtypecounts left outer join cardtypes on cardtypecounts.cardtypeid = cardtypes.typeid group by cardtypes.concession, cardtypes.paidorfree order by count desc;
Muy bien, hay una imagen completa: 65,65% de tarifa completa, 33,65% que paga tarifas de concesión y una mísera concesión de 0,69% de equitación.
Los mejores destinos de domingo para personas mayores
Finalmente, intentemos algo un poco específico. En un domingo, ¿a dónde les gusta ir a nuestros usuarios principales de transporte público?
Unirse a nuestra mesa calendar nos permitirá determinar qué fechas son Sunday. Y nuestra consulta sobre los tipos de tarifas que cardtypeid = 9se muestran antes nos muestra cuál es el Victorian Seniors Concessiontipo de tarifa que debemos buscar. ¡Increíble!
# selecting touch-on events that were on a Sunday using a Victorian Seniors # Concession fare
select stopid, count(*) as count from events, calendar where onoroff = 'ScanOffTransaction' and events.date = calendar.date and events.cardtypeid = 9 and calendar.dayofweek = 'Sunday' group by events.stopid;
Ahora, al igual que en nuestra primera consulta, únase a stopspara obtener información legible para los usuarios.
Recordando de nuevo que a algunas paradas les falta información …
# join or sunday seniors stops to stops information
with seniorsundaystops (stopid, count) as ( select stopid, count(*) as count from events, calendar where onoroff = 'ScanOffTransaction' and events.date = calendar.date and events.cardtypeid = 9 and calendar.dayofweek = 'Sunday' group by events.stopid ) select seniorsundaystops.stopid, stops.longname, stops.suburb, seniorsundaystops.count from seniorsundaystops left outer join stops on seniorsundaystops.stopid = stops.stopid order by seniorsundaystops.count desc;
Nuestras paradas misteriosas sin nombre (casi seguro que son las paradas de City Loop) siguen siendo la cabeza de la manada. Pero al parecer a nuestros Seniors con Myki les gusta pasar tiempo en Box Hill, Caulfield y Footscray. Quien sabe.
Conclusión
Es bueno tomarse un momento y pensar qué tan genial es este tipo de tecnología, porque es fácil olvidarlo una vez que se sumerge. La capacidad de ejecutar consultas SQL, con uniones, contra nada más que archivos CSV que se encuentran en S3. Eso es impresionante.
Estos datos de Myki tampoco son una cantidad trivial para analizar. Más de 1.800 millones de filas, y podemos reducirlo en menos de 10 segundos. Y cuesta unos centavos absolutos. Las consultas enumeradas en este artículo todo escanea alrededor de 145 gigabytes de datos de S3, lo que me ha costado un total de …
$ 0.75
Feliz consulta!
Contando nuestros datos, es un poco decente analizarlos en menos de 10 segundos y pagar casi nada
