1. Введение
В ходе этой лабораторной работы вы создадите модель линейной регрессии, чтобы предсказать, являются ли недавние транзакции мошенническими, с помощью SQL в BigQuery. Вы создаете набор обучающих данных, который содержит каждую транзакцию по карте и некоторые атрибуты, которые, по нашему мнению, лучше всего указывают на мошенничество, например расстояние от дома клиента, время суток и сумму транзакции.
Затем вы используете BQML для построения модели логистической регрессии, чтобы предсказать, является ли транзакция мошеннической, на основе наших обучающих данных. Одной из приятных особенностей BQ ML является то, что он заботится о переоснащении, поэтому наши обучающие данные не влияют на производительность модели на новых данных. Наконец, вы создаете три образца транзакций с разными характеристиками и прогнозируете, являются ли они мошенническими или не используют модель.
Что вы узнаете
В ходе этой лабораторной работы вы научитесь выполнять следующие задачи:
- Загрузить набор данных из корзины Google Cloud Storage
- Создание обучающих данных
- Создайте и обучите модель логистической регрессии
- Используйте модель, чтобы предсказать, являются ли образцы транзакций мошенническими или нет.
- Выявление мошеннических транзакций по почтовому индексу с помощью геопространственного анализа
2. Загрузите набор данных из сегмента GCS.
В этой задаче вы создаете набор данных с именем bq_demo и загружаете в него данные розничного банковского обслуживания из сегмента GCS. Он удалит все существующие данные, которые уже есть в ваших таблицах.
Открыть облачную оболочку
- В Cloud Console на верхней правой панели инструментов нажмите кнопку «Активировать Cloud Shell».
- После загрузки облачной оболочки введите:
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
- После завершения нажмите X, чтобы закрыть терминал облачной оболочки. Вы успешно загрузили набор данных из корзины Google Cloud Storage.
3. Создайте обучающие данные
Запрос мошеннических транзакций по типу карты
Прежде чем создавать обучающие данные, давайте проанализируем, как мошеннические транзакции распределяются по типам карт. В нашей базе данных розничных банковских услуг имеется флаг, указывающий на то, что клиент сообщил о мошеннической транзакции на своем счете. Этот запрос показывает количество мошеннических транзакций по типам карт.
[ Конкурентная тема для обсуждения: в отличие от некоторых конкурентов, BigQuery не требует от вас экспортировать данные из вашего хранилища данных в корзину хранения, запускать алгоритмы машинного обучения, а затем копировать результаты обратно в базу данных. Все это можно сделать на месте, что сохраняет безопасность данных и не приводит к «разрастанию данных». ]
- Откройте консоль BigQuery:
В Google Cloud Console выберите Меню навигации > BigQuery.
- Откроется окно сообщения «Добро пожаловать в BigQuery в Cloud Console». В этом окне сообщения содержится ссылка на краткое руководство и примечания к выпуску.
Нажмите Готово.
Откроется консоль BigQuery.
- Запустите запрос в редакторе запросов:
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
Но что, если бы мы могли использовать эти данные для прогнозирования мошеннических транзакций еще до того, как клиент это заметит? ML предназначен не только для экспертов. С помощью BigQuery аналитики могут запускать модели машинного обучения мирового класса непосредственно на данных хранилища данных через SQL.
Создание обучающих данных
Создайте набор обучающих данных, содержащий каждую транзакцию по карте и некоторые атрибуты, которые, по нашему мнению, лучше всего указывают на мошенничество, например расстояние от дома клиента, время суток и сумму транзакции.
Запустите запрос в редакторе запросов:
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 );
В разделе «Результаты» нажмите «Перейти к таблице», и вы должны увидеть следующий результат:
4. Создайте и обучите модель
Используйте BQML для построения модели логистической регрессии, чтобы предсказать, является ли транзакция мошеннической, на основе наших обучающих данных, созданных на предыдущем шаге. Одной из приятных особенностей BQML является то, что он заботится о переоснащении, поэтому наши обучающие данные не влияют на производительность модели на новых данных.
Запустите запрос в редакторе запросов:
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));
Посмотреть детали модели
В разделе «Результаты» нажмите «Перейти к модели».
Вы должны увидеть вкладки «Схема», «Обучение» и «Оценка».
Во вкладке «Обучение» вы должны увидеть следующее:
На вкладке «Оценка» вы должны увидеть следующее:
На вкладке «Схема» вы должны увидеть это:
5. Используйте модель для прогнозирования мошенничества
Теперь давайте воспользуемся нашей моделью, чтобы предсказать, является ли транзакция потенциально мошеннической. Мы создадим 3 образца транзакций с разными характеристиками.
Запустите следующий запрос в редакторе запросов:
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)
);
Вы должны увидеть такие результаты:
Как вы можете видеть, согласно нашей модели, первая транзакция почти наверняка является мошеннической, тогда как 2-я и 3-я транзакции, по прогнозам, не будут мошенническими. Мы установили пороговое значение 55 % по сравнению со значением по умолчанию, равным 50 %. Мы могли бы обучить нашу модель дополнительными данными или добавить дополнительные атрибуты для повышения точности.
6. Определите мошенническую транзакцию по почтовому индексу с помощью геопространственного анализа.
BigQuery имеет обширную поддержку геопространственных данных. Вот пример, в котором используется ГИС-функция ST_WITHIN для определения почтового индекса с учетом широты и долготы торговых транзакций с использованием общедоступного набора данных о границах почтового индекса.
Запустите следующий код в редакторе запросов:
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;
Вы должны увидеть следующие результаты:
Как видите, уровень мошенничества в большинстве почтовых индексов относительно невелик (менее 2%), но в 69345 уровень мошенничества составляет удивительные 11%. Вероятно, это то, что мы хотим исследовать.