1. Introduzione
In questo lab creerai un modello di regressione lineare per prevedere se le transazioni recenti sono fraudolente o meno utilizzando SQL in BigQuery. Creerai un set di dati di addestramento che contiene ogni transazione con carta e alcuni degli attributi che abbiamo scelto meglio indicano attività fraudolente, come la distanza dalla casa del cliente, l'ora del giorno e l'importo della transazione.
Poi userai BQML per creare un modello di regressione logistica in modo da prevedere se una transazione è fraudolenta in base ai nostri dati di addestramento. Una delle caratteristiche interessanti di BQ ML è che si occupa dell'overfitting, in modo che i nostri dati di addestramento non influiscano sulle prestazioni del modello con nuovi dati. Infine, creerai tre transazioni di esempio con caratteristiche diverse e prevedi se sono fraudolente o se non utilizzano il modello.
Cosa imparerai a fare
In questo lab imparerai a:
- Carica il set di dati da un bucket Google Cloud Storage
- Crea dati di addestramento
- Creare e addestrare un modello di regressione logistica
- Utilizza il modello per prevedere se le transazioni di esempio sono fraudolente o meno
- Identifica le transazioni fraudolente per codice postale utilizzando l'analisi geospaziale
2. Carica il set di dati da un bucket GCS
In questa attività creerai un set di dati chiamato bq_demo e lo caricherai con i dati di retail banking da un bucket GCS. Verranno eliminati tutti i dati esistenti già presenti nelle tabelle.
Apri Cloud Shell
- Nella barra degli strumenti in alto a destra della console Cloud, fai clic sul pulsante Attiva Cloud Shell.
- Una volta caricato Cloud Shell, digita:
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
- Al termine, fai clic sulla X per chiudere il terminale Cloud Shell. Hai caricato un set di dati da un bucket Google Cloud Storage.
3. Crea dati di addestramento
Query sulle transazioni fraudolente per tipo di carta
Prima di creare i dati di addestramento, analizziamo in che modo le transazioni fraudolente sono distribuite tra i tipi di carta. Il nostro database di retail banking contiene un flag che segnala quando un cliente ha segnalato una transazione fraudolenta sul proprio conto. Questa query mostra il numero di transazioni fraudolente per tipo di carta.
[Spunti di discussione sulla concorrenza: a differenza di alcuni concorrenti, BigQuery non richiede di esportare i dati del data warehouse in un bucket di archiviazione, eseguire algoritmi di machine learning e quindi copiare i risultati nel database. Tutto questo può essere fatto in atto, preservando la sicurezza dei dati e non generando una "propagazione di dati".]
- Apri la console BigQuery:
Nella console Google Cloud, seleziona Menu di navigazione > in BigQuery.
- Si aprirà una finestra con il messaggio Ti diamo il benvenuto in BigQuery sulla console Cloud. Questa finestra fornisce un link alla guida rapida e alle note di rilascio.
Fai clic su Fine.
Si aprirà la console di BigQuery.
- Esegui la query nell'editor di query:
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
E se potessimo utilizzare questi dati per prevedere le transazioni fraudolente ancora prima che il cliente se ne accorga? Il ML non è destinato solo agli esperti. Con BigQuery, gli analisti possono eseguire modelli di ML di altissimo livello direttamente sui dati di data warehouse tramite SQL.
Creare dati di addestramento
Crea un set di dati di addestramento che contenga ogni transazione della carta e alcuni degli attributi che abbiamo scelto meglio indicano attività fraudolente, come la distanza dalla casa del cliente, l'ora del giorno e l'importo della transazione.
Esegui la query nell'editor di query:
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 );
Nella sezione "Risultati" fai clic su "vai alla tabella" Dovresti vedere il seguente risultato:
4. Crea e addestra il modello
Utilizza BQML per creare un modello di regressione logistica in modo da prevedere se una transazione è fraudolenta in base ai dati di addestramento creati nel passaggio precedente. Una delle caratteristiche interessanti di BQML è che si occupa dell'overfitting, in modo che i nostri dati di addestramento non influiscano sulle prestazioni del modello con nuovi dati.
Esegui la query nell'editor di query:
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));
Visualizza dettagli modello
In "Risultati", fai clic su "Vai al modello".
Dovresti vedere le schede Schema, Addestramento e Valutazione.
Nella scheda Addestramento dovresti vedere questo:
Nella scheda "Valutazione", dovresti vedere quanto segue:
Nella scheda "Schema" dovresti vedere questo:
5. Utilizza il modello per prevedere le frodi
Ora utilizziamo il nostro modello per prevedere se una transazione è potenzialmente fraudolenta. Creeremo tre transazioni di esempio con caratteristiche diverse.
Esegui la seguente query nell'editor di query:
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)
);
Dovresti vedere risultati come questi:
Come puoi vedere, in base al nostro modello la prima transazione è quasi certamente fraudolenta, mentre la seconda e la terza transazione non sono fraudolente. Abbiamo impostato la soglia al 55%, un valore superiore rispetto al valore predefinito del 50%. Potremmo addestrare il nostro modello con dati aggiuntivi o aggiungere altri attributi per migliorarne l'accuratezza.
6. Identifica le transazioni fraudolente per codice postale utilizzando l'analisi geospaziale
BigQuery offre un supporto avanzato per i dati geospaziali. Ecco un esempio che utilizza la funzione GIS ST_WITHIN per determinare il codice postale in base alla latitudine e alla longitudine delle transazioni dei commercianti utilizzando un set di dati pubblico di confini di codici postali.
Esegui questo codice nell'editor di query:
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;
Dovresti vedere i seguenti risultati:
Come si può vedere, il tasso di frode nella maggior parte dei codici postali è relativamente basso (inferiore al 2%), ma il tasso di frode nel 69345 è sorprendente dell'11%. Probabilmente, ci interessa approfondire l'argomento.