1. Introducción
En este lab, crearás un modelo de regresión lineal para predecir si las transacciones recientes son fraudulentas con SQL en BigQuery. Crearás un conjunto de datos de entrenamiento que contenga cada transacción con tarjeta y algunos de los atributos que decidimos indicar de mejor manera un fraude, como la distancia desde la casa del cliente, la hora del día y el importe de la transacción.
Luego, usarás BQML para crear un modelo de regresión logística a fin de predecir si una transacción es fraudulenta según nuestros datos de entrenamiento. Una de las ventajas de AA de BQ es que se encarga del sobreajuste para que nuestros datos de entrenamiento no afecten el rendimiento del modelo con datos nuevos. Por último, crearás tres transacciones de muestra con diferentes características y predecirás si son fraudulentas o no usan el modelo.
Qué aprenderás
En este lab, aprenderás a realizar las siguientes tareas:
- Carga un conjunto de datos desde un bucket de Google Cloud Storage
- Crea datos de entrenamiento
- Crear y entrenar un modelo de regresión logística
- Usar el modelo para predecir si las transacciones de muestra son fraudulentas o no
- Identifica transacciones fraudulentas por código postal mediante el análisis geoespacial
2. Carga un conjunto de datos desde un bucket de GCS
En esta tarea, crearás un conjunto de datos llamado bq_demo y lo cargarás con datos de la banca minorista desde un bucket de GCS. Se borrarán los datos existentes que ya se encuentren en tus tablas.
Abra Cloud Shell
- En la consola de Cloud, en la barra de herramientas superior derecha, haz clic en el botón Activar Cloud Shell.
- Cuando se cargue Cloud Shell, escribe lo siguiente:
bq rm -r -f -d bq_demo
bq rm -r -f -d bq_demo_shared
bq mk --dataset bq_demo
bq load --replace --autodetect --source_format=CSV bq_demo.account gs://retail-banking-looker/account
bq load --replace --autodetect --source_format=CSV bq_demo.base_card gs://retail-banking-looker/base_card
bq load --replace --autodetect --source_format=CSV bq_demo.card gs://retail-banking-looker/card
bq load --replace --autodetect --source_format=CSV bq_demo.card_payment_amounts gs://retail-banking-looker/card_payment_amounts
bq load --replace --autodetect --source_format=CSV bq_demo.card_transactions gs://retail-banking-looker/card_transactions
bq load --replace --autodetect --source_format=CSV bq_demo.card_type_facts gs://retail-banking-looker/card_type_facts
bq load --replace --autodetect --source_format=CSV bq_demo.client gs://retail-banking-looker/client
bq load --replace --autodetect --source_format=CSV bq_demo.disp gs://retail-banking-looker/disp
bq load --replace --autodetect --source_format=CSV bq_demo.loan gs://retail-banking-looker/loan
bq load --replace --autodetect --source_format=CSV bq_demo.order gs://retail-banking-looker/order
bq load --replace --autodetect --source_format=CSV bq_demo.trans gs://retail-banking-looker/trans
- Cuando termines, haz clic en la X para cerrar la terminal de Cloud Shell. Cargaste correctamente un conjunto de datos desde un bucket de Google Cloud Storage.
3. Crea datos de entrenamiento
Consulta de transacciones fraudulentas por tipo de tarjeta
Antes de crear datos de entrenamiento, analicemos cómo se distribuyen las transacciones fraudulentas entre los tipos de tarjetas. Nuestra base de datos de banca minorista contiene una marca que indica cuando un cliente denunció una transacción fraudulenta en su cuenta. Esta consulta muestra la cantidad de transacciones fraudulentas por tipo de tarjeta.
[Tema de conversación sobre la competencia: A diferencia de algunos competidores, BigQuery no requiere que exportes los datos de tu almacén de datos a un bucket de almacenamiento, que ejecutes algoritmos de aprendizaje automático y que, luego, copies los resultados a la base de datos. Todo esto se puede hacer de forma local, lo que preserva la seguridad de los datos y no genera una “dispersión de datos”.]
- Abre la consola de BigQuery:
En la consola de Google Cloud, selecciona Menú de navegación > en BigQuery.
- Se abrirá el cuadro de mensaje de bienvenida a BigQuery en la consola de Cloud. Este cuadro de mensaje contiene un vínculo a la guía de inicio rápido y las notas de la versión.
Haga clic en Listo.
Se abrirá la consola de BigQuery.
- Ejecuta la consulta en el Editor de consultas:
SELECT c.type, count(trans_id) as fraud_transactions
FROM bq_demo.card_transactions AS t
JOIN bq_demo.card c ON t.cc_number = c.card_number
WHERE t.is_fraud=1
GROUP BY type
Pero ¿qué pasaría si pudiéramos usar estos datos para predecir transacciones fraudulentas incluso antes de que el cliente lo note? El AA no es solo para expertos. Con BigQuery, los analistas pueden ejecutar modelos de AA de primer nivel directamente en los datos del almacén de datos a través de SQL.
Crea datos de entrenamiento
Crea un conjunto de datos de entrenamiento que contenga cada transacción con una tarjeta y algunos de los atributos que decidimos indicar de mejor manera un fraude, como la distancia desde la casa del cliente, la hora del día y el importe de la transacción.
Ejecuta la consulta en el Editor de consultas:
CREATE OR REPLACE TABLE bq_demo.training_data as (
SELECT
card_transactions.trans_id AS trans_id,
card_transactions.is_fraud AS is_fraud,
--amount for transaction: higher amounts are more likely to be fraud
cast(card_transactions.amount as FLOAT64) AS card_transactions_amount,
--distance from the customers home: further distances are more likely to be fraud
ST_DISTANCE((ST_GEOGPOINT((cast(card_transactions.merchant_lon as FLOAT64)),
(cast(card_transactions.merchant_lat as FLOAT64)))), (ST_GeogPoint((cast(SPLIT(client.address,'|')[OFFSET(4)] as float64)),
(cast(SPLIT(client.address,'|')[OFFSET(3)] as float64))))) AS card_transactions_transaction_distance,
--hour that transaction occured: fraud occurs in middle of night (usually between midnight and 4 am)
EXTRACT(HOUR FROM TIMESTAMP(CONCAT(card_transactions.trans_date,' ',card_transactions.trans_time)) ) AS card_transactions_transaction_hour_of_day
FROM bq_demo.card_transactions AS card_transactions
LEFT JOIN bq_demo.card AS card ON card.card_number = card_transactions.cc_number
LEFT JOIN bq_demo.disp AS disp ON card.disp_id = disp.disp_id
LEFT JOIN bq_demo.client AS client ON disp.client_id = client.client_id );
En "Resultados", haz clic en "Ir a la tabla" Deberías ver el siguiente resultado:
4. Creación y entrenamiento del modelo
Utilizamos BQML para crear un modelo de regresión logística y predecir si una transacción es fraudulenta según nuestros datos de entrenamiento creados en el paso anterior. Una de las ventajas de BQML es que se encarga del sobreajuste, por lo que nuestros datos de entrenamiento no afectarán el rendimiento del modelo con datos nuevos.
Ejecuta la consulta en el Editor de consultas:
CREATE OR REPLACE MODEL bq_demo.fraud_prediction
OPTIONS(model_type='logistic_reg', labels=['is_fraud']) AS
SELECT * EXCEPT(trans_id)
FROM bq_demo.training_data
WHERE (is_fraud = 1) OR
(is_fraud = 0 AND rand() <=
(SELECT SUM(is_fraud)/COUNT(*) FROM bq_demo.training_data));
Ver detalles del modelo
En “Resultados”, haz clic en “Ir al modelo”.
Deberías ver las pestañas Esquema, Entrenamiento y Evaluación.
En la pestaña “Entrenamiento”, deberías ver lo siguiente:
En la pestaña “Evaluación”, deberías ver lo siguiente:
En la pestaña “Esquema”, deberías ver lo siguiente:
5. Usa el modelo para predecir fraudes
Ahora, usemos nuestro modelo para predecir si una transacción es potencialmente fraudulenta. Crearemos 3 transacciones de muestra con diferentes características.
Ejecuta la siguiente consulta en el Editor de consultas:
SELECT * FROM ML.PREDICT(MODEL bq_demo.fraud_prediction, (
SELECT '001' as trans_id, 500.00 as card_transactions_amount, 600 as card_transactions_transaction_distance, 2 as card_transactions_transaction_hour_of_day
UNION ALL
SELECT '002' as trans_id, 5.25 as card_transactions_amount, 2 as card_transactions_transaction_distance, 13 as card_transactions_transaction_hour_of_day
UNION ALL
SELECT '003' as trans_id, 175.50 as card_transactions_amount, 45 as card_transactions_transaction_distance, 10 as card_transactions_transaction_hour_of_day
), STRUCT(0.55 AS threshold)
);
Debes ver resultados como los siguientes:
Como puedes ver, es casi seguro que la primera transacción sea fraudulenta según nuestro modelo, mientras que la segunda y la tercera no lo son. Establecimos el umbral en 55%, desde el valor predeterminado de 50%. Podríamos entrenar nuestro modelo con datos adicionales o agregar atributos adicionales para mejorar la exactitud.
6. Identifica transacciones fraudulentas por código postal mediante el análisis geoespacial
BigQuery ofrece una gran compatibilidad con datos geoespaciales. Este es un ejemplo que utiliza la función GIS ST_WITHIN para determinar el código postal en función de la latitud y longitud de las transacciones de un comercio mediante un conjunto de datos públicos de los límites del código postal.
Ejecuta el siguiente código en el Editor de consultas:
WITH trans_by_zip as (
SELECT
card_transactions.trans_id,
zip_code AS merchant_zip,
city as merchant_city,
county as merchant_county,
state_name as merchant_state
FROM
bq_demo.card_transactions AS card_transactions,
bigquery-public-data.geo_us_boundaries.zip_codes AS zip_codes
WHERE ST_Within(ST_GEOGPOINT((cast(card_transactions.merchant_lon as FLOAT64)),(cast(card_transactions.merchant_lat as FLOAT64))),zip_codes.zip_code_geom)
)
SELECT merchant_zip, 1.0 * (SUM(is_fraud)) / nullif((COUNT(*)),0) AS percent_transactions_fraud
FROM bq_demo.card_transactions t, trans_by_zip
WHERE t.trans_id = trans_by_zip.trans_id
GROUP BY merchant_zip
ORDER BY percent_transactions_fraud DESC;
Deberías ver los siguientes resultados:
Como puedes ver, el porcentaje de fraude en la mayoría de los códigos postales es relativamente pequeño (menos del 2%), pero en 69345 es un 11% sorprendente. Probablemente, esto sea algo que queremos investigar.