Spanner e BigQuery: escudo de defesa contra fraudes em tempo real

1. Introdução

Este é o back-end do Petverse, um jogo on-line multiplayer em que os jogadores criam avatares de animais, exploram o mundo e negociam moedas do jogo.

Recentemente, a economia do jogo foi ameaçada. Grandes somas de dinheiro estão sendo drenadas das contas de jogadores caninos ricos, convertidas inteiramente em ações de atum premium. Suspeitamos de um grupo de "Gatos Robin Hood", que roubam dos cães ricos para alimentar as massas felinas.

Neste codelab, você vai criar um Fraud Defense em tempo real para capturar o líder e o grupo de bots automatizados. Você vai aprender como os dados operacionais no Spanner convergem com as análises no BigQuery para detectar e investigar padrões complexos de fraude com consultas de gráfico (GQL) e relacionais.

Atividades deste laboratório

O que é necessário

  • Um projeto na nuvem do Google Cloud com o faturamento ativado.
  • Conhecimento básico de SQL, comandos de terminal e Python.
  • Talvez seja necessário ter uma conta do GitHub (o código está hospedado no GitHub).

Público-alvo: desenvolvedores, engenheiros de dados e arquitetos de nível intermediário.

Duração total estimada: 45 a 60 minutos.

Estimativa de custo: os recursos criados neste codelab devem custar menos de US $5.

2. Antes de começar / Configuração

Criar ou selecionar um projeto do Google Cloud

Você precisa de um projeto do Google Cloud com o faturamento ativado para usar os serviços necessários neste laboratório.

  1. No console do Google Cloud, na página de seletor de projetos, selecione ou crie um projeto na nuvem.
  2. Verifique se o faturamento está ativado no seu projeto do Cloud. Saiba como verificar se o faturamento está ativado.
  3. Encontre o ID do projeto na página inicial do console do Cloud.

Página inicial do console do Cloud

Iniciar o Cloud Shell

Você vai usar o Google Cloud Shell como ambiente de execução. O Cloud Shell vem pré-instalado com gcloud, git e outras ferramentas necessárias.

  1. Acesse o Google Cloud Shell.
  2. Se for solicitado, clique em Autorizar.
  3. Para garantir que você está trabalhando no seu projeto, defina a variável de ambiente no terminal do Cloud Shell:
export PROJECT_ID=<YOUR_PROJECT_ID>
gcloud config set project $PROJECT_ID

Cloud Shell: use gcloud config para definir o projeto

Ativar as APIs necessárias

Execute o seguinte comando para ativar as APIs do Spanner, do BigQuery e da Vertex AI:

gcloud services enable spanner.googleapis.com \
    bigquery.googleapis.com \
    aiplatform.googleapis.com \
    run.googleapis.com

Clone o repositório

Clone o repositório que contém o código do aplicativo e os esquemas de amostra:

git clone https://github.com/GoogleCloudPlatform/cloud-spanner-samples.git
cd cloud-spanner-samples/spanner-bq-fraud-defense

3. Provisionar infraestrutura

Agora você vai configurar o data warehouse no BigQuery e o banco de dados operacional no Spanner.

Configurar o conjunto de dados e a conexão do BigQuery

O BigQuery vai analisar o stream de telemetria do jogo.

Ainda no console do Google Cloud Shell, execute os comandos a seguir para garantir que o ID do projeto ainda esteja definido:

export PROJECT_ID=<YOUR_PROJECT_ID>
gcloud config set project $PROJECT_ID
  1. Crie um conjunto de dados do BigQuery game_analytics:
bq mk -d --location=US game_analytics
  1. Crie uma conexão para se conectar aos buckets do Storage e, opcionalmente, ao Spanner:
bq mk --connection --location=US --project_id=$PROJECT_ID \
    --connection_type=CLOUD_RESOURCE unicorn-connection
  1. Crie os esquemas para as tabelas GameplayTelemetry, AccountSignals, Players e ChatLogs usando o arquivo de esquema:
bq query --use_legacy_sql=false < bq_schema.sql

Configurar o Spanner

O Google Cloud Spanner vai processar as transações operacionais em tempo real. Neste laboratório, vamos usar uma instância econômica de 100 unidades de processamento (PUs).

  1. Crie uma instância do Spanner:
gcloud spanner instances create game-instance \
    --config=regional-us-central1 \
    --description="Game Instance" \
    --processing-units=100 \
    --edition=ENTERPRISE
  1. Crie o banco de dados do Spanner game-db:
gcloud spanner databases create game-db --instance=game-instance
  1. Atualize o esquema do Spanner com as tabelas do aplicativo (Players, Transactions, AccountSignals) e o gráfico de propriedades (PlayerNetwork):
gcloud spanner databases ddl update game-db --instance=game-instance --ddl-file=spanner_schema.sql
  1. Crie o AvatarSearchIndex para nossa pesquisa vetorial multimodal:
gcloud spanner databases ddl update game-db --instance=game-instance \
    --ddl="CREATE SEARCH INDEX AvatarSearchIndex ON Players(AvatarDescriptionTokens)"

Importar dados para o BigQuery

  1. Importe os dados de demonstração para o BigQuery:
bq load --source_format=AVRO game_analytics.GameplayTelemetry gs://sample-data-and-media/spanner-bq-fraud-heist/GameplayTelemetry
bq load --source_format=AVRO game_analytics.AccountSignals gs://sample-data-and-media/spanner-bq-fraud-heist/AccountSignals
bq load --source_format=AVRO game_analytics.Players gs://sample-data-and-media/spanner-bq-fraud-heist/Players
bq load --source_format=AVRO game_analytics.ChatLogs gs://sample-data-and-media/spanner-bq-fraud-heist/ChatLogs
  1. Acesse o console do BigQuery e analise os dados no conjunto de dados game_analytics.

Console do BigQuery

Importar dados para o Spanner

Acesse o console do Spanner e confira os dados no banco de dados game-db.

Clique em Spanner Studio e abra uma Nova consulta (+).

Console do Spanner

Cole as seguintes instruções INSERT no editor de consultas e clique em Executar:

-- Table: Players
INSERT INTO Players (
  PlayerId,
  Name,
  Species,
  Clan,
  AvatarDescription,
  ProfilePictureUrl,
  CreatedAt
) VALUES
  ('dc8cf07a-ac0f-48da-9f64-4f379492b1e7', 'Pixel', 'Cat', 'CatClan', 'A heroic cat wearing a green tunic and a feathered cap', 'gs://sample-data-and-media/pixel_profile_booth.png', '2026-03-02T05:11:28.077335+00:00'),
  ('e82df4fb-0b6d-44dc-8609-70b41430af38', 'Rocky_1', 'Dog', 'DogClan', 'A robot dog with metal plating', 'gs://sample-data-and-media/wheaten_terrier_102.jpg', '2026-03-02T05:11:28.077374+00:00'),
  ('ea3afac7-54f0-4f68-8ed5-a5b6bd386c59', 'Whiskers_2', 'Cat', 'CatClan', 'A sneaky black cat hiding in the shadows', 'gs://sample-data-and-media/Bengal_100.jpg', '2026-03-02T05:11:28.077389+00:00'),
  ('f37d558b-fd0a-404c-a193-bc3a8a2edfba', 'Felix_3', 'Cat', 'CatClan', 'A cyber-punk cat with neon glasses', 'gs://sample-data-and-media/Abyssinian_1.jpg', '2026-03-02T05:11:28.077407+00:00'),
  ('f3687206-405e-43b6-afb0-8ca73eee5dd1', 'Luna_4', 'Cat', 'CatClan', 'A tabby cat with a red bandana', 'gs://sample-data-and-media/Bengal_100.jpg', '2026-03-02T05:11:28.077419+00:00'),
  ('82383e2d-d3a2-481d-b0cf-bfe165ed9bfd', 'Luna_5', 'Cat', 'CatClan', 'A fluffy persian cat with a golden collar', 'gs://sample-data-and-media/Abyssinian_114.jpg', '2026-03-02T05:11:28.077429+00:00'),
  ('755c7aff-e538-4681-9b90-4b870a42ac72', 'Buddy_6', 'Dog', 'DogClan', 'A tough bulldog with a spiked collar', 'gs://sample-data-and-media/yorkshire_terrier_101.jpg', '2026-03-02T05:11:28.077439+00:00'),
  ('8a034e84-26b3-4198-8ec9-3749b1f60537', 'Charlie_7', 'Dog', 'DogClan', 'A police german shepherd with a badge', 'gs://sample-data-and-media/wheaten_terrier_102.jpg', '2026-03-02T05:11:28.077462+00:00'),
  ('ca288a07-2bf8-46fa-a121-9bd0d0f44c64', 'Rocky_8', 'Dog', 'DogClan', 'A robot dog with metal plating', 'gs://sample-data-and-media/yorkshire_terrier_101.jpg', '2026-03-02T05:11:28.077474+00:00'),
  ('7b2881f0-289b-4ea4-9c0e-c1748249b70a', 'Bella_9', 'Dog', 'DogClan', 'A robot dog with metal plating', 'gs://sample-data-and-media/yorkshire_terrier_101.jpg', '2026-03-02T05:11:28.077484+00:00'),
  ('153f4022-a4ce-404a-8544-25d004fd34ad', 'Simba_10', 'Cat', 'CatClan', 'A tabby cat with a red bandana', 'gs://sample-data-and-media/Bengal_100.jpg', '2026-03-02T05:11:28.077494+00:00'),
  ('3fb82b8e-75a1-49fd-8691-a9dabb42bc4b', 'Charlie_11', 'Dog', 'DogClan', 'A police german shepherd with a badge', 'gs://sample-data-and-media/staffordshire_bull_terrier_116.jpg', '2026-03-02T05:11:28.077507+00:00'),
  ('e37b6dcf-7ccb-47d0-8b9d-0da2fa30ad09', 'Felix_12', 'Cat', 'CatClan', 'A cyber-punk cat with neon glasses', 'gs://sample-data-and-media/Bengal_105.jpg', '2026-03-02T05:11:28.077516+00:00'),
  ('0b395a7b-0673-4348-afd0-7cea9252629f', 'Bella_13', 'Dog', 'DogClan', 'A police german shepherd with a badge', 'gs://sample-data-and-media/yorkshire_terrier_101.jpg', '2026-03-02T05:11:28.077527+00:00'),
  ('64921a63-bea6-4a5c-9e49-7a817678c94f', 'Charlie_14', 'Dog', 'DogClan', 'A tough bulldog with a spiked collar', 'gs://sample-data-and-media/yorkshire_terrier_101.jpg', '2026-03-02T05:11:28.077537+00:00'),
  ('0d9040df-34a3-4e54-b660-a85e3c60a6fe', 'Felix_15', 'Cat', 'CatClan', 'A fluffy persian cat with a golden collar', 'gs://sample-data-and-media/Abyssinian_114.jpg', '2026-03-02T05:11:28.077546+00:00'),
  ('188c23a6-4c3b-4c25-9b40-9ec8ad7712a3', 'Max_16', 'Dog', 'DogClan', 'A fast greyhound wearing a racing vest', 'gs://sample-data-and-media/wheaten_terrier_102.jpg', '2026-03-02T05:11:28.077556+00:00'),
  ('a2eaefc9-dbff-4704-b908-74518d687e17', 'Rocky_17', 'Dog', 'DogClan', 'A tough bulldog with a spiked collar', 'gs://sample-data-and-media/shiba_inu_105.jpg', '2026-03-02T05:11:28.077565+00:00'),
  ('b59d9b9a-a169-43c1-832d-7e7d4acfa19c', 'Felix_18', 'Cat', 'CatClan', 'A sneaky black cat hiding in the shadows', 'gs://sample-data-and-media/Abyssinian_1.jpg', '2026-03-02T05:11:28.077581+00:00'),
  ('7019b91a-d908-4c93-8ed2-d1c60e518630', 'Nala_19', 'Cat', 'CatClan', 'A royal siamese cat wearing a crown', 'gs://sample-data-and-media/Abyssinian_1.jpg', '2026-03-02T05:11:28.077595+00:00'),
  ('1a972a71-a530-407a-b3f7-1dd9b2532cec', 'Luna_20', 'Cat', 'CatClan', 'A sneaky black cat hiding in the shadows', 'gs://sample-data-and-media/Bengal_105.jpg', '2026-03-02T05:11:28.077604+00:00'),
  ('3bd0489d-3f43-428d-be2d-0434f0e04ed8', 'Luna_21', 'Cat', 'CatClan', 'A cyber-punk cat with neon glasses', 'gs://sample-data-and-media/Bombay_104.jpg', '2026-03-02T05:11:28.077613+00:00'),
  ('28d7ab21-7ca7-435b-bdc4-5266a381ef89', 'Whiskers_22', 'Cat', 'CatClan', 'A fluffy persian cat with a golden collar', 'gs://sample-data-and-media/Bombay_104.jpg', '2026-03-02T05:11:28.077623+00:00'),
  ('87d880d4-c949-40e3-8d76-9307b45d57fe', 'Rocky_23', 'Dog', 'DogClan', 'A robot dog with metal plating', 'gs://sample-data-and-media/wheaten_terrier_102.jpg', '2026-03-02T05:11:28.077632+00:00'),
  ('b49a4523-83ff-4f59-8fca-54356928a18b', 'Nala_24', 'Cat', 'CatClan', 'A tabby cat with a red bandana', 'gs://sample-data-and-media/Bengal_105.jpg', '2026-03-02T05:11:28.077641+00:00'),
  ('1e049026-1231-4763-bc50-82cb51950fa2', 'Nala_25', 'Cat', 'CatClan', 'A fluffy persian cat with a golden collar', 'gs://sample-data-and-media/Abyssinian_1.jpg', '2026-03-02T05:11:28.077650+00:00'),
  ('e83caccd-e81e-454a-bf92-9d9a5a509e25', 'Simba_26', 'Cat', 'CatClan', 'A royal siamese cat wearing a crown', 'gs://sample-data-and-media/Bengal_105.jpg', '2026-03-02T05:11:28.077659+00:00'),
  ('45650b49-9701-4415-92a9-79073243d198', 'Simba_27', 'Cat', 'CatClan', 'A royal siamese cat wearing a crown', 'gs://sample-data-and-media/Bombay_104.jpg', '2026-03-02T05:11:28.077668+00:00'),
  ('2eb95b36-c9c3-4720-849b-fc240c9434da', 'Bella_28', 'Dog', 'DogClan', 'A robot dog with metal plating', 'gs://sample-data-and-media/samoyed_97.jpg', '2026-03-02T05:11:28.077677+00:00'),
  ('491943d2-8aa3-4484-9d6d-afcab7ec579a', 'Buddy_29', 'Dog', 'DogClan', 'A robot dog with metal plating', 'gs://sample-data-and-media/wheaten_terrier_102.jpg', '2026-03-02T05:11:28.077687+00:00'),
  ('004ac8b2-a37d-42c8-aba3-35b204f596f0', 'Max_30', 'Dog', 'DogClan', 'A police german shepherd with a badge', 'gs://sample-data-and-media/wheaten_terrier_102.jpg', '2026-03-02T05:11:28.077697+00:00'),
  ('5cf1a55e-7904-4937-a4bd-bf3d1fc1c839', 'Whiskers_31', 'Cat', 'CatClan', 'A royal siamese cat wearing a crown', 'gs://sample-data-and-media/Bengal_100.jpg', '2026-03-02T05:11:28.077706+00:00'),
  ('4736fc20-1c22-49c2-af87-c35802302507', 'Luna_32', 'Cat', 'CatClan', 'A fluffy persian cat with a golden collar', 'gs://sample-data-and-media/Bengal_100.jpg', '2026-03-02T05:11:28.077715+00:00'),
  ('d98fc7ff-dd92-4723-994c-6267ef951bcc', 'Buddy_33', 'Dog', 'DogClan', 'A police german shepherd with a badge', 'gs://sample-data-and-media/staffordshire_bull_terrier_116.jpg', '2026-03-02T05:11:28.077797+00:00'),
  ('3de80488-e1b6-4908-a6ec-9c51f46f43b9', 'Luna_34', 'Cat', 'CatClan', 'A sneaky black cat hiding in the shadows', 'gs://sample-data-and-media/Bombay_104.jpg', '2026-03-02T05:11:28.077821+00:00'),
  ('710128e0-4e1d-479c-acbc-497bbc5bc802', 'Bella_35', 'Dog', 'DogClan', 'A tough bulldog with a spiked collar', 'gs://sample-data-and-media/scottish_terrier_108.jpg', '2026-03-02T05:11:28.077836+00:00'),
  ('7e5d416f-f42c-410e-8e2a-19be0cdfc1e5', 'Simba_36', 'Cat', 'CatClan', 'A royal siamese cat wearing a crown', 'gs://sample-data-and-media/Abyssinian_114.jpg', '2026-03-02T05:11:28.077850+00:00'),
  ('b5af580c-4998-4f68-b52f-5c5b6a9d0aab', 'Buddy_37', 'Dog', 'DogClan', 'A robot dog with metal plating', 'gs://sample-data-and-media/scottish_terrier_108.jpg', '2026-03-02T05:11:28.077864+00:00'),
  ('145cc805-810a-4320-864d-ba6b5c6fbc33', 'Max_38', 'Dog', 'DogClan', 'A tough bulldog with a spiked collar', 'gs://sample-data-and-media/staffordshire_bull_terrier_116.jpg', '2026-03-02T05:11:28.077884+00:00'),
  ('34fa84c8-3356-4ed6-9ac5-967cd2baabac', 'Whiskers_39', 'Cat', 'CatClan', 'A royal siamese cat wearing a crown', 'gs://sample-data-and-media/Bengal_105.jpg', '2026-03-02T05:11:28.077894+00:00'),
  ('a50c967b-2e89-448d-be47-4df628c51572', 'Luna_40', 'Cat', 'CatClan', 'A royal siamese cat wearing a crown', 'gs://sample-data-and-media/Bengal_105.jpg', '2026-03-02T05:11:28.077906+00:00'),
  ('64aeeb22-42db-46bf-85bd-b21b135c9803', 'Simba_41', 'Cat', 'CatClan', 'A cyber-punk cat with neon glasses', 'gs://sample-data-and-media/Bombay_104.jpg', '2026-03-02T05:11:28.077915+00:00'),
  ('dec825d6-91f4-4f8f-b44b-74cf6b4b78f6', 'Felix_42', 'Cat', 'CatClan', 'A sneaky black cat hiding in the shadows', 'gs://sample-data-and-media/Bengal_100.jpg', '2026-03-02T05:11:28.077924+00:00'),
  ('d9380e8d-8949-4849-af57-7a91a1a2b953', 'Bella_43', 'Dog', 'DogClan', 'A police german shepherd with a badge', 'gs://sample-data-and-media/staffordshire_bull_terrier_116.jpg', '2026-03-02T05:11:28.077933+00:00'),
  ('88a425a0-b499-4825-8a16-3d33b85feec9', 'Whiskers_44', 'Cat', 'CatClan', 'A fluffy persian cat with a golden collar', 'gs://sample-data-and-media/Abyssinian_114.jpg', '2026-03-02T05:11:28.077942+00:00'),
  ('e17ae312-37c6-4a66-9172-2d20d8528033', 'Simba_45', 'Cat', 'CatClan', 'A cyber-punk cat with neon glasses', 'gs://sample-data-and-media/Abyssinian_1.jpg', '2026-03-02T05:11:28.077958+00:00'),
  ('ae82282e-d380-4500-99e0-2dc4e276c0cf', 'Nala_46', 'Cat', 'CatClan', 'A cyber-punk cat with neon glasses', 'gs://sample-data-and-media/Bombay_104.jpg', '2026-03-02T05:11:28.077967+00:00'),
  ('9ce13d41-dede-4658-a7e4-7bc00372d65c', 'Simba_47', 'Cat', 'CatClan', 'A cyber-punk cat with neon glasses', 'gs://sample-data-and-media/Bengal_105.jpg', '2026-03-02T05:11:28.077976+00:00'),
  ('8db26b5c-2c09-4578-89bf-f9afaf765e36', 'Whiskers_48', 'Cat', 'CatClan', 'A fluffy persian cat with a golden collar', 'gs://sample-data-and-media/Bengal_100.jpg', '2026-03-02T05:11:28.077989+00:00'),
  ('eb113965-6ee6-459e-ba0f-fb6cb1d0ae34', 'Luna_49', 'Cat', 'CatClan', 'A tabby cat with a red bandana', 'gs://sample-data-and-media/Bombay_104.jpg', '2026-03-02T05:11:28.078002+00:00'),
  ('3f064471-24a0-47cd-8927-925cb3e5df7f', 'Felix_50', 'Cat', 'CatClan', 'A tabby cat with a red bandana', 'gs://sample-data-and-media/Bengal_100.jpg', '2026-03-02T05:11:28.078012+00:00'),
  ('ed20f879-2d42-4ba7-8df7-d696c4b6aafd', 'Whiskers_51', 'Cat', 'CatClan', 'A sneaky black cat hiding in the shadows', 'gs://sample-data-and-media/Bengal_105.jpg', '2026-03-02T05:11:28.078021+00:00'),
  ('7e9bbec4-e2aa-4d67-b1c0-35f7db1d0dae', 'Buddy_52', 'Dog', 'DogClan', 'A fast greyhound wearing a racing vest', 'gs://sample-data-and-media/yorkshire_terrier_101.jpg', '2026-03-02T05:11:28.078030+00:00'),
  ('398de7e0-8f4f-44ba-adfe-503a157cfe7c', 'Rocky_53', 'Dog', 'DogClan', 'A fast greyhound wearing a racing vest', 'gs://sample-data-and-media/staffordshire_bull_terrier_116.jpg', '2026-03-02T05:11:28.078040+00:00'),
  ('0f2948c8-f5ba-4546-bdba-567a27b6b4f0', 'Bella_54', 'Dog', 'DogClan', 'A loyal golden retriever with a happy smile', 'gs://sample-data-and-media/wheaten_terrier_102.jpg', '2026-03-02T05:11:28.078049+00:00'),
  ('0b1e7a03-2922-4d53-8871-290520e6bb76', 'Max_55', 'Dog', 'DogClan', 'A fast greyhound wearing a racing vest', 'gs://sample-data-and-media/wheaten_terrier_102.jpg', '2026-03-02T05:11:28.078059+00:00'),
  ('d0ad6342-88fd-443f-82ba-919fc1d652d0', 'Luna_56', 'Cat', 'CatClan', 'A cyber-punk cat with neon glasses', 'gs://sample-data-and-media/Abyssinian_1.jpg', '2026-03-02T05:11:28.078068+00:00'),
  ('73e9a7c4-09e6-46d8-a093-b241d7d1d5df', 'Charlie_57', 'Dog', 'DogClan', 'A loyal golden retriever with a happy smile', 'gs://sample-data-and-media/wheaten_terrier_102.jpg', '2026-03-02T05:11:28.078081+00:00'),
  ('55a1207d-a029-48da-a79d-c1f3f6824a37', 'Buddy_58', 'Dog', 'DogClan', 'A tough bulldog with a spiked collar', 'gs://sample-data-and-media/wheaten_terrier_102.jpg', '2026-03-02T05:11:28.078090+00:00'),
  ('61dd4370-bff0-4d09-be31-19f19c4dad5d', 'Max_59', 'Dog', 'DogClan', 'A tough bulldog with a spiked collar', 'gs://sample-data-and-media/wheaten_terrier_102.jpg', '2026-03-02T05:11:28.078099+00:00'),
  ('4e9dfce6-555b-434a-81b6-237c61b9b530', 'Merry_Cat_0', 'Cat', 'RobinHoods', 'A sneaky rebel cat wearing a green tunic and a feathered cap', 'gs://sample-data-and-media/Bombay_104.jpg', '2026-03-02T05:11:28.078112+00:00'),
  ('686337b0-304e-4270-89c3-d015f9039294', 'Merry_Cat_1', 'Cat', 'RobinHoods', 'A sneaky rebel cat wearing a green tunic and a feathered cap', 'gs://sample-data-and-media/Abyssinian_114.jpg', '2026-03-02T05:11:28.078165+00:00'),
  ('1627bc18-c42e-4599-b5f1-6f3d52669edb', 'Merry_Cat_2', 'Cat', 'RobinHoods', 'A sneaky rebel cat wearing a green tunic and a feathered cap', 'gs://sample-data-and-media/Bengal_100.jpg', '2026-03-02T05:11:28.078212+00:00'),
  ('215b3d04-402a-4ac2-83ed-1edb9a421691', 'Merry_Cat_3', 'Cat', 'RobinHoods', 'A sneaky rebel cat wearing a green tunic and a feathered cap', 'gs://sample-data-and-media/Bengal_105.jpg', '2026-03-02T05:11:28.078258+00:00'),
  ('78d66ff4-0519-4157-9d95-76c2900ba7f9', 'Merry_Cat_4', 'Cat', 'RobinHoods', 'A sneaky rebel cat wearing a green tunic and a feathered cap', 'gs://sample-data-and-media/Abyssinian_114.jpg', '2026-03-02T05:11:28.078301+00:00'),
  ('822a8aae-57bd-460e-9643-69815990bec8', 'Merry_Cat_5', 'Cat', 'RobinHoods', 'A sneaky rebel cat wearing a green tunic and a feathered cap', 'gs://sample-data-and-media/Bombay_104.jpg', '2026-03-02T05:11:28.078349+00:00'),
  ('9f223b65-41a3-495e-940a-5a07d2ba4ba7', 'Merry_Cat_6', 'Cat', 'RobinHoods', 'A sneaky rebel cat wearing a green tunic and a feathered cap', 'gs://sample-data-and-media/Abyssinian_114.jpg', '2026-03-02T05:11:28.078390+00:00'),
  ('3910b199-caf7-4822-8346-1ba9bd750c8e', 'Merry_Cat_7', 'Cat', 'RobinHoods', 'A sneaky rebel cat wearing a green tunic and a feathered cap', 'gs://sample-data-and-media/Bengal_105.jpg', '2026-03-02T05:11:28.078427+00:00'),
  ('78c129b6-21e5-40c6-b9cf-d25ad5193e68', 'Merry_Cat_8', 'Cat', 'RobinHoods', 'A sneaky rebel cat wearing a green tunic and a feathered cap', 'gs://sample-data-and-media/Bengal_100.jpg', '2026-03-02T05:11:28.078463+00:00'),
  ('eba84e9d-bd10-466e-8fcd-899c0a868149', 'Merry_Cat_9', 'Cat', 'RobinHoods', 'A sneaky rebel cat wearing a green tunic and a feathered cap', 'gs://sample-data-and-media/Bombay_104.jpg', '2026-03-02T05:11:28.078508+00:00'),
  ('37a1e15c-a38c-4763-a2bd-042741bce012', 'Merry_Cat_10', 'Cat', 'RobinHoods', 'A sneaky rebel cat wearing a green tunic and a feathered cap', 'gs://sample-data-and-media/Bombay_104.jpg', '2026-03-02T05:11:28.078544+00:00'),
  ('c5337857-3b58-4b0c-8814-4585dcbf765f', 'Merry_Cat_11', 'Cat', 'RobinHoods', 'A sneaky rebel cat wearing a green tunic and a feathered cap', 'gs://sample-data-and-media/Bombay_104.jpg', '2026-03-02T05:11:28.078587+00:00');

-- Table: Transactions
INSERT INTO Transactions (
  TransactionId,
  SenderId,
  ReceiverId,
  Amount,
  Timestamp,
  IsSuspicious
) VALUES
  ('83086ce9-1813-40c7-8e60-624662e7dff8', '0f2948c8-f5ba-4546-bdba-567a27b6b4f0', '4e9dfce6-555b-434a-81b6-237c61b9b530', 13481, '2026-03-02T05:11:28.078136+00:00', TRUE),
  ('8c8f38a8-424f-4752-b662-78800e04b137', '4e9dfce6-555b-434a-81b6-237c61b9b530', 'dc8cf07a-ac0f-48da-9f64-4f379492b1e7', 7327, '2026-03-02T05:11:28.078145+00:00', TRUE),
  ('e4b88c5e-ad32-4fa7-87d7-7e7e70ab133a', '3fb82b8e-75a1-49fd-8691-a9dabb42bc4b', '686337b0-304e-4270-89c3-d015f9039294', 11283, '2026-03-02T05:11:28.078182+00:00', TRUE),
  ('8fe11946-c0ef-4b84-a259-11850955a3ea', '686337b0-304e-4270-89c3-d015f9039294', 'dc8cf07a-ac0f-48da-9f64-4f379492b1e7', 8181, '2026-03-02T05:11:28.078191+00:00', TRUE),
  ('c2d024eb-9131-47ce-84c0-3404e5a01db0', '145cc805-810a-4320-864d-ba6b5c6fbc33', '1627bc18-c42e-4599-b5f1-6f3d52669edb', 11086, '2026-03-02T05:11:28.078228+00:00', TRUE),
  ('7a51d50f-63ac-4406-b4df-2d6fd862977f', '1627bc18-c42e-4599-b5f1-6f3d52669edb', 'dc8cf07a-ac0f-48da-9f64-4f379492b1e7', 9443, '2026-03-02T05:11:28.078237+00:00', TRUE),
  ('edc980d0-539e-4951-9eaf-4ba98b88b40c', '2eb95b36-c9c3-4720-849b-fc240c9434da', '215b3d04-402a-4ac2-83ed-1edb9a421691', 10438, '2026-03-02T05:11:28.078274+00:00', TRUE),
  ('0e98eb5f-a942-4bb3-931a-1de1bdbb2e03', '215b3d04-402a-4ac2-83ed-1edb9a421691', 'dc8cf07a-ac0f-48da-9f64-4f379492b1e7', 7612, '2026-03-02T05:11:28.078286+00:00', TRUE),
  ('13cf3599-f18a-47a5-8b7d-71f9eda250ab', 'ca288a07-2bf8-46fa-a121-9bd0d0f44c64', '78d66ff4-0519-4157-9d95-76c2900ba7f9', 14301, '2026-03-02T05:11:28.078318+00:00', TRUE),
  ('db7051ef-aa4b-47fa-a54e-54ff03dbabf1', '78d66ff4-0519-4157-9d95-76c2900ba7f9', 'dc8cf07a-ac0f-48da-9f64-4f379492b1e7', 10294, '2026-03-02T05:11:28.078330+00:00', TRUE),
  ('9080d8a1-def2-4b95-bc9f-56c25ec9c3ba', 'e82df4fb-0b6d-44dc-8609-70b41430af38', '822a8aae-57bd-460e-9643-69815990bec8', 9613, '2026-03-02T05:11:28.078365+00:00', TRUE),
  ('06593b48-2546-4a52-a078-cf4ddfaed856', '822a8aae-57bd-460e-9643-69815990bec8', 'dc8cf07a-ac0f-48da-9f64-4f379492b1e7', 9787, '2026-03-02T05:11:28.078376+00:00', TRUE),
  ('d540b1d5-5e20-4252-985c-275b82fdad80', '491943d2-8aa3-4484-9d6d-afcab7ec579a', '9f223b65-41a3-495e-940a-5a07d2ba4ba7', 11403, '2026-03-02T05:11:28.078406+00:00', TRUE),
  ('6ba654b3-4c04-48ed-9975-1aae3f557a38', '9f223b65-41a3-495e-940a-5a07d2ba4ba7', 'dc8cf07a-ac0f-48da-9f64-4f379492b1e7', 13564, '2026-03-02T05:11:28.078413+00:00', TRUE),
  ('da73004f-a9e6-4078-af54-4dc7a697a0e4', '710128e0-4e1d-479c-acbc-497bbc5bc802', '3910b199-caf7-4822-8346-1ba9bd750c8e', 10088, '2026-03-02T05:11:28.078442+00:00', TRUE),
  ('ac25cfd6-1c64-483a-8b36-5fe4c6c36e36', '3910b199-caf7-4822-8346-1ba9bd750c8e', 'dc8cf07a-ac0f-48da-9f64-4f379492b1e7', 11354, '2026-03-02T05:11:28.078450+00:00', TRUE),
  ('3f09bdb0-5831-44f8-b293-98cbdb953c9d', 'd98fc7ff-dd92-4723-994c-6267ef951bcc', '78c129b6-21e5-40c6-b9cf-d25ad5193e68', 12426, '2026-03-02T05:11:28.078479+00:00', TRUE),
  ('65803100-7b84-4ff5-a273-b54fe48d5ad2', '78c129b6-21e5-40c6-b9cf-d25ad5193e68', 'dc8cf07a-ac0f-48da-9f64-4f379492b1e7', 13858, '2026-03-02T05:11:28.078490+00:00', TRUE),
  ('7caeab6c-eb5c-4284-9857-87ab6ba744d1', '398de7e0-8f4f-44ba-adfe-503a157cfe7c', 'eba84e9d-bd10-466e-8fcd-899c0a868149', 14851, '2026-03-02T05:11:28.078523+00:00', TRUE),
  ('441b31cd-1c7b-4385-a5da-490a6c98d58d', 'eba84e9d-bd10-466e-8fcd-899c0a868149', 'dc8cf07a-ac0f-48da-9f64-4f379492b1e7', 12481, '2026-03-02T05:11:28.078530+00:00', TRUE),
  ('ea509bae-75a0-4878-b73c-0688efd2808e', 'd9380e8d-8949-4849-af57-7a91a1a2b953', '37a1e15c-a38c-4763-a2bd-042741bce012', 8042, '2026-03-02T05:11:28.078564+00:00', TRUE),
  ('7ac7d043-2744-4689-835a-20b21dca962b', '37a1e15c-a38c-4763-a2bd-042741bce012', 'dc8cf07a-ac0f-48da-9f64-4f379492b1e7', 11248, '2026-03-02T05:11:28.078572+00:00', TRUE),
  ('1fdfc9a6-af31-4151-a96e-6df93e8fafff', '3fb82b8e-75a1-49fd-8691-a9dabb42bc4b', 'c5337857-3b58-4b0c-8814-4585dcbf765f', 11357, '2026-03-02T05:11:28.078603+00:00', TRUE),
  ('3408e82d-61c5-49e1-a037-b8e25422d9ca', 'c5337857-3b58-4b0c-8814-4585dcbf765f', 'dc8cf07a-ac0f-48da-9f64-4f379492b1e7', 13411, '2026-03-02T05:11:28.078612+00:00', TRUE),
  ('a63fa5df-9d00-44e4-bd00-3d993c737a99', '82383e2d-d3a2-481d-b0cf-bfe165ed9bfd', '28d7ab21-7ca7-435b-bdc4-5266a381ef89', 312, '2026-03-02T05:11:28.078658+00:00', FALSE),
  ('26b8b704-7b50-4ce5-894f-452741649286', 'e17ae312-37c6-4a66-9172-2d20d8528033', '7b2881f0-289b-4ea4-9c0e-c1748249b70a', 52, '2026-03-02T05:11:28.078679+00:00', FALSE),
  ('9d8197de-888c-42ce-8bcf-ef0fe6fccc39', '82383e2d-d3a2-481d-b0cf-bfe165ed9bfd', '188c23a6-4c3b-4c25-9b40-9ec8ad7712a3', 65, '2026-03-02T05:11:28.078700+00:00', FALSE),
  ('b0fd1afe-871a-4afc-be00-5c203b147a90', 'eb113965-6ee6-459e-ba0f-fb6cb1d0ae34', '61dd4370-bff0-4d09-be31-19f19c4dad5d', 292, '2026-03-02T05:11:28.078715+00:00', FALSE),
  ('b7be0c34-2d44-4d48-8cef-ddb74dd11260', 'ea3afac7-54f0-4f68-8ed5-a5b6bd386c59', '45650b49-9701-4415-92a9-79073243d198', 199, '2026-03-02T05:11:28.078730+00:00', FALSE),
  ('0baea611-d24a-43d6-be24-49286f4b80bc', 'c5337857-3b58-4b0c-8814-4585dcbf765f', 'e17ae312-37c6-4a66-9172-2d20d8528033', 313, '2026-03-02T05:11:28.078739+00:00', FALSE),
  ('936234c3-3d96-4649-8679-cdc7a35c7c14', '28d7ab21-7ca7-435b-bdc4-5266a381ef89', 'e83caccd-e81e-454a-bf92-9d9a5a509e25', 306, '2026-03-02T05:11:28.078748+00:00', FALSE),
  ('f29c2881-9ce1-47e3-8934-be21250d807b', '55a1207d-a029-48da-a79d-c1f3f6824a37', '1e049026-1231-4763-bc50-82cb51950fa2', 356, '2026-03-02T05:11:28.078757+00:00', FALSE),
  ('aea90ceb-8669-48b2-ad20-903fa2592cc4', '78c129b6-21e5-40c6-b9cf-d25ad5193e68', 'ca288a07-2bf8-46fa-a121-9bd0d0f44c64', 320, '2026-03-02T05:11:28.078766+00:00', FALSE),
  ('e5cb67d4-7fb5-4788-b705-e4b156846cf1', '37a1e15c-a38c-4763-a2bd-042741bce012', '45650b49-9701-4415-92a9-79073243d198', 96, '2026-03-02T05:11:28.078780+00:00', FALSE),
  ('1484d14a-4ece-4c60-8760-aca02d8912d3', 'b49a4523-83ff-4f59-8fca-54356928a18b', '4e9dfce6-555b-434a-81b6-237c61b9b530', 286, '2026-03-02T05:11:28.078797+00:00', FALSE),
  ('55bfca86-c16e-4d83-a3be-4a9339b5ca90', '7e9bbec4-e2aa-4d67-b1c0-35f7db1d0dae', 'e82df4fb-0b6d-44dc-8609-70b41430af38', 94, '2026-03-02T05:11:28.078812+00:00', FALSE),
  ('626b175d-7954-4b48-bfc3-47e1759eb62e', '4e9dfce6-555b-434a-81b6-237c61b9b530', '2eb95b36-c9c3-4720-849b-fc240c9434da', 364, '2026-03-02T05:11:28.078826+00:00', FALSE),
  ('657b64cd-eb82-47f7-af15-c485f09e4c6a', 'dec825d6-91f4-4f8f-b44b-74cf6b4b78f6', '9ce13d41-dede-4658-a7e4-7bc00372d65c', 180, '2026-03-02T05:11:28.078835+00:00', FALSE),
  ('45a3d118-9ada-400f-8007-f90f6573012f', 'f3687206-405e-43b6-afb0-8ca73eee5dd1', 'c5337857-3b58-4b0c-8814-4585dcbf765f', 21, '2026-03-02T05:11:28.078844+00:00', FALSE),
  ('bad0c577-9063-4315-a923-609b55354aec', '28d7ab21-7ca7-435b-bdc4-5266a381ef89', '0f2948c8-f5ba-4546-bdba-567a27b6b4f0', 96, '2026-03-02T05:11:28.078852+00:00', FALSE),
  ('f3f3c44c-29e5-4356-bcdb-34fceead7277', '4e9dfce6-555b-434a-81b6-237c61b9b530', '45650b49-9701-4415-92a9-79073243d198', 206, '2026-03-02T05:11:28.078863+00:00', FALSE),
  ('89e9f697-6cfe-4a11-ada1-2846a9a0e815', '7b2881f0-289b-4ea4-9c0e-c1748249b70a', '1627bc18-c42e-4599-b5f1-6f3d52669edb', 62, '2026-03-02T05:11:28.078878+00:00', FALSE),
  ('2d980b9e-ec93-4d4c-98b1-4f9440489e0e', '28d7ab21-7ca7-435b-bdc4-5266a381ef89', 'e17ae312-37c6-4a66-9172-2d20d8528033', 379, '2026-03-02T05:11:28.078894+00:00', FALSE),
  ('1eabdf25-5b41-4773-8870-bb3e71ad320a', '0b395a7b-0673-4348-afd0-7cea9252629f', '7b2881f0-289b-4ea4-9c0e-c1748249b70a', 10, '2026-03-02T05:11:28.078911+00:00', FALSE),
  ('eeb9524c-5aa7-4c5a-a8b6-b96fea0c0502', '1a972a71-a530-407a-b3f7-1dd9b2532cec', '78d66ff4-0519-4157-9d95-76c2900ba7f9', 200, '2026-03-02T05:11:28.079050+00:00', FALSE),
  ('2c38f9e6-97fe-477f-8ceb-a6e29880c926', '188c23a6-4c3b-4c25-9b40-9ec8ad7712a3', '0f2948c8-f5ba-4546-bdba-567a27b6b4f0', 197, '2026-03-02T05:11:28.079063+00:00', FALSE),
  ('a85ab466-5c6d-4863-b5fd-ec5418a52534', 'a50c967b-2e89-448d-be47-4df628c51572', '9f223b65-41a3-495e-940a-5a07d2ba4ba7', 215, '2026-03-02T05:11:28.079079+00:00', FALSE),
  ('b273c634-1b47-492e-9651-d32e54bdcbb8', 'ae82282e-d380-4500-99e0-2dc4e276c0cf', '491943d2-8aa3-4484-9d6d-afcab7ec579a', 298, '2026-03-02T05:11:28.079088+00:00', FALSE),
  ('93956d3c-c44b-4624-87d8-ac139b1310d0', 'dec825d6-91f4-4f8f-b44b-74cf6b4b78f6', '2eb95b36-c9c3-4720-849b-fc240c9434da', 495, '2026-03-02T05:11:28.079096+00:00', FALSE),
  ('1d827b0f-710e-4af6-8492-4e8753c11584', '153f4022-a4ce-404a-8544-25d004fd34ad', '9ce13d41-dede-4658-a7e4-7bc00372d65c', 242, '2026-03-02T05:11:28.079107+00:00', FALSE),
  ('226c5063-77e1-4888-90f1-a1f23cfb7461', '004ac8b2-a37d-42c8-aba3-35b204f596f0', 'c5337857-3b58-4b0c-8814-4585dcbf765f', 63, '2026-03-02T05:11:28.079115+00:00', FALSE),
  ('11584808-fc90-4f7f-bbb0-d9b444bf09b9', '3de80488-e1b6-4908-a6ec-9c51f46f43b9', 'a50c967b-2e89-448d-be47-4df628c51572', 385, '2026-03-02T05:11:28.079124+00:00', FALSE),
  ('69ac1396-8377-4e44-9257-d14eafdd8c3d', 'b59d9b9a-a169-43c1-832d-7e7d4acfa19c', '4e9dfce6-555b-434a-81b6-237c61b9b530', 331, '2026-03-02T05:11:28.079133+00:00', FALSE),
  ('c0400153-b737-4c54-8ae3-c6958c481aa5', '4e9dfce6-555b-434a-81b6-237c61b9b530', 'f3687206-405e-43b6-afb0-8ca73eee5dd1', 254, '2026-03-02T05:11:28.079142+00:00', FALSE),
  ('80ca0f0e-6a9d-446a-adeb-16a4388120f4', '755c7aff-e538-4681-9b90-4b870a42ac72', '0b1e7a03-2922-4d53-8871-290520e6bb76', 183, '2026-03-02T05:11:28.079150+00:00', FALSE),
  ('289f79a4-5743-4577-9880-85648f4e4c17', '8db26b5c-2c09-4578-89bf-f9afaf765e36', '1a972a71-a530-407a-b3f7-1dd9b2532cec', 344, '2026-03-02T05:11:28.079159+00:00', FALSE),
  ('fcea8d5f-c180-480d-83eb-45bd0801e1f3', 'a50c967b-2e89-448d-be47-4df628c51572', '78d66ff4-0519-4157-9d95-76c2900ba7f9', 416, '2026-03-02T05:11:28.079167+00:00', FALSE),
  ('a142f26e-c281-44d7-b7f2-e023a2362f56', '8a034e84-26b3-4198-8ec9-3749b1f60537', 'a2eaefc9-dbff-4704-b908-74518d687e17', 168, '2026-03-02T05:11:28.079176+00:00', FALSE),
  ('ad62fcc0-2bb3-430c-9dda-dd309e346f59', '0f2948c8-f5ba-4546-bdba-567a27b6b4f0', 'e83caccd-e81e-454a-bf92-9d9a5a509e25', 18, '2026-03-02T05:11:28.079185+00:00', FALSE),
  ('2ba99e26-8967-4cc0-b024-4545ea3220d9', '61dd4370-bff0-4d09-be31-19f19c4dad5d', 'dec825d6-91f4-4f8f-b44b-74cf6b4b78f6', 489, '2026-03-02T05:11:28.079194+00:00', FALSE),
  ('7c8c664b-5491-47f2-bd93-c5ad2a263e87', 'b5af580c-4998-4f68-b52f-5c5b6a9d0aab', '7e9bbec4-e2aa-4d67-b1c0-35f7db1d0dae', 175, '2026-03-02T05:11:28.079202+00:00', FALSE),
  ('3e5b5fae-26c5-44ff-97c0-e1b0624e208d', '7019b91a-d908-4c93-8ed2-d1c60e518630', '9ce13d41-dede-4658-a7e4-7bc00372d65c', 272, '2026-03-02T05:11:28.079211+00:00', FALSE),
  ('6995eaea-b5f3-4291-a1e4-68450d25b19b', 'd98fc7ff-dd92-4723-994c-6267ef951bcc', '88a425a0-b499-4825-8a16-3d33b85feec9', 186, '2026-03-02T05:11:28.079219+00:00', FALSE),
  ('9c50c700-e887-4336-b092-d5e37120d304', 'f37d558b-fd0a-404c-a193-bc3a8a2edfba', '3de80488-e1b6-4908-a6ec-9c51f46f43b9', 15, '2026-03-02T05:11:28.079232+00:00', FALSE),
  ('9e27bc69-bb96-4392-8e2c-a911db3e9dcb', 'e37b6dcf-7ccb-47d0-8b9d-0da2fa30ad09', 'd9380e8d-8949-4849-af57-7a91a1a2b953', 81, '2026-03-02T05:11:28.079241+00:00', FALSE),
  ('ba31c88c-49ec-4934-bc99-f036aa1e3cb5', '0d9040df-34a3-4e54-b660-a85e3c60a6fe', 'f37d558b-fd0a-404c-a193-bc3a8a2edfba', 208, '2026-03-02T05:11:28.079259+00:00', FALSE),
  ('93e0c429-0788-492a-935a-4674a4cf1522', '4736fc20-1c22-49c2-af87-c35802302507', '5cf1a55e-7904-4937-a4bd-bf3d1fc1c839', 244, '2026-03-02T05:11:28.079268+00:00', FALSE),
  ('9f001cbb-a6fb-4192-a0ae-892219b99444', '4736fc20-1c22-49c2-af87-c35802302507', 'eba84e9d-bd10-466e-8fcd-899c0a868149', 239, '2026-03-02T05:11:28.079277+00:00', FALSE),
  ('0ee4d029-3905-49ab-98cb-c7e5ffcd08bc', 'c5337857-3b58-4b0c-8814-4585dcbf765f', 'f37d558b-fd0a-404c-a193-bc3a8a2edfba', 204, '2026-03-02T05:11:28.079290+00:00', FALSE),
  ('e39956dd-af1f-4cc9-8831-cabbfbf5bec6', '61dd4370-bff0-4d09-be31-19f19c4dad5d', '398de7e0-8f4f-44ba-adfe-503a157cfe7c', 129, '2026-03-02T05:11:28.079310+00:00', FALSE),
  ('2404c2d3-4738-40f3-a169-9af84c909a79', '1e049026-1231-4763-bc50-82cb51950fa2', '87d880d4-c949-40e3-8d76-9307b45d57fe', 14, '2026-03-02T05:11:28.079319+00:00', FALSE),
  ('c9ba7281-5b78-4fe2-ad7c-105fafaad0b4', '8db26b5c-2c09-4578-89bf-f9afaf765e36', '82383e2d-d3a2-481d-b0cf-bfe165ed9bfd', 232, '2026-03-02T05:11:28.079327+00:00', FALSE),
  ('b848f820-a6d9-4f2b-b7e1-13ddfd887fe1', '61dd4370-bff0-4d09-be31-19f19c4dad5d', 'dec825d6-91f4-4f8f-b44b-74cf6b4b78f6', 426, '2026-03-02T05:11:28.079336+00:00', FALSE),
  ('db409655-050a-41e1-be6b-52c9d792deb2', '1a972a71-a530-407a-b3f7-1dd9b2532cec', 'eba84e9d-bd10-466e-8fcd-899c0a868149', 448, '2026-03-02T05:11:28.079344+00:00', FALSE),
  ('79811268-69cf-4542-8258-ad795155964c', '73e9a7c4-09e6-46d8-a093-b241d7d1d5df', '4e9dfce6-555b-434a-81b6-237c61b9b530', 244, '2026-03-02T05:11:28.079353+00:00', FALSE),
  ('71b40f64-aef1-4a83-a021-3118c6404475', 'd0ad6342-88fd-443f-82ba-919fc1d652d0', '9ce13d41-dede-4658-a7e4-7bc00372d65c', 223, '2026-03-02T05:11:28.079361+00:00', FALSE),
  ('06b9f40d-2988-446f-9eaf-7222198b7c52', '0b395a7b-0673-4348-afd0-7cea9252629f', '37a1e15c-a38c-4763-a2bd-042741bce012', 290, '2026-03-02T05:11:28.079374+00:00', FALSE),
  ('d3a2bd8f-5796-48bd-82bb-1c94f89b24a4', '0b1e7a03-2922-4d53-8871-290520e6bb76', 'b5af580c-4998-4f68-b52f-5c5b6a9d0aab', 411, '2026-03-02T05:11:28.079385+00:00', FALSE),
  ('1b8d50ce-4083-4b0b-b12d-b94529d200f3', '0b395a7b-0673-4348-afd0-7cea9252629f', '9f223b65-41a3-495e-940a-5a07d2ba4ba7', 237, '2026-03-02T05:11:28.079393+00:00', FALSE),
  ('d6ed7dff-b106-42cf-99c7-4f15bf114b79', 'e82df4fb-0b6d-44dc-8609-70b41430af38', '215b3d04-402a-4ac2-83ed-1edb9a421691', 205, '2026-03-02T05:11:28.079402+00:00', FALSE),
  ('de837bd6-5b12-47a3-ac05-c8fa4ac02ca3', '78c129b6-21e5-40c6-b9cf-d25ad5193e68', '73e9a7c4-09e6-46d8-a093-b241d7d1d5df', 251, '2026-03-02T05:11:28.079411+00:00', FALSE),
  ('56080f4e-a404-4340-b119-8f82a1f2d6d0', '3fb82b8e-75a1-49fd-8691-a9dabb42bc4b', '755c7aff-e538-4681-9b90-4b870a42ac72', 334, '2026-03-02T05:11:28.079419+00:00', FALSE),
  ('db94e731-6ed8-42a4-a62d-2227814c95f7', '78d66ff4-0519-4157-9d95-76c2900ba7f9', '87d880d4-c949-40e3-8d76-9307b45d57fe', 477, '2026-03-02T05:11:28.079428+00:00', FALSE),
  ('cf051085-4625-44aa-bf03-e4529f1324d8', '3de80488-e1b6-4908-a6ec-9c51f46f43b9', '34fa84c8-3356-4ed6-9ac5-967cd2baabac', 285, '2026-03-02T05:11:28.079441+00:00', FALSE),
  ('137f933b-40a1-441d-b547-d296bc977244', '82383e2d-d3a2-481d-b0cf-bfe165ed9bfd', '822a8aae-57bd-460e-9643-69815990bec8', 34, '2026-03-02T05:11:28.079449+00:00', FALSE),
  ('164295cd-bb80-4c24-bbfa-29b582aa8e0f', '3f064471-24a0-47cd-8927-925cb3e5df7f', 'b59d9b9a-a169-43c1-832d-7e7d4acfa19c', 311, '2026-03-02T05:11:28.079458+00:00', FALSE),
  ('2b3a7e6b-7474-439e-b387-baa708683ca8', 'e83caccd-e81e-454a-bf92-9d9a5a509e25', '755c7aff-e538-4681-9b90-4b870a42ac72', 432, '2026-03-02T05:11:28.079466+00:00', FALSE),
  ('fcecd45c-f950-4704-9405-8ff6cc34f3fc', 'ea3afac7-54f0-4f68-8ed5-a5b6bd386c59', 'b5af580c-4998-4f68-b52f-5c5b6a9d0aab', 368, '2026-03-02T05:11:28.079474+00:00', FALSE),
  ('c2d81a86-0c03-4495-a1e8-702e1754f424', '2eb95b36-c9c3-4720-849b-fc240c9434da', '34fa84c8-3356-4ed6-9ac5-967cd2baabac', 353, '2026-03-02T05:11:28.079483+00:00', FALSE),
  ('462f5078-6df6-4f57-843e-47d0b51bc7ea', '8a034e84-26b3-4198-8ec9-3749b1f60537', '1a972a71-a530-407a-b3f7-1dd9b2532cec', 173, '2026-03-02T05:11:28.079496+00:00', FALSE),
  ('059a2a27-e019-4f5f-8e24-8208898c723f', '4e9dfce6-555b-434a-81b6-237c61b9b530', '5cf1a55e-7904-4937-a4bd-bf3d1fc1c839', 411, '2026-03-02T05:11:28.079507+00:00', FALSE),
  ('8ab52f88-d38c-4c60-b9ca-c2d08a337a33', '64921a63-bea6-4a5c-9e49-7a817678c94f', '5cf1a55e-7904-4937-a4bd-bf3d1fc1c839', 457, '2026-03-02T05:11:28.079516+00:00', FALSE),
  ('117ff05e-21d2-48ab-a3c8-29e61658c822', 'c5337857-3b58-4b0c-8814-4585dcbf765f', '7b2881f0-289b-4ea4-9c0e-c1748249b70a', 142, '2026-03-02T05:11:28.079525+00:00', FALSE),
  ('34e5ea80-59b4-4cb0-a8c1-552e2ba6ecc5', 'eb113965-6ee6-459e-ba0f-fb6cb1d0ae34', '3910b199-caf7-4822-8346-1ba9bd750c8e', 142, '2026-03-02T05:11:28.079533+00:00', FALSE),
  ('db1cfbb1-8555-492e-b6c9-45f8040c469b', '0f2948c8-f5ba-4546-bdba-567a27b6b4f0', 'ae82282e-d380-4500-99e0-2dc4e276c0cf', 478, '2026-03-02T05:11:28.079542+00:00', FALSE),
  ('370f50b3-e558-447e-bfbe-fe4eca7e9628', '9ce13d41-dede-4658-a7e4-7bc00372d65c', '3910b199-caf7-4822-8346-1ba9bd750c8e', 72, '2026-03-02T05:11:28.079550+00:00', FALSE),
  ('d08c71a3-bfde-4f95-895a-dc6fa810250f', 'b59d9b9a-a169-43c1-832d-7e7d4acfa19c', '822a8aae-57bd-460e-9643-69815990bec8', 330, '2026-03-02T05:11:28.079558+00:00', FALSE),
  ('81d6bb50-9127-4e2d-acd5-ecaaa797fcb1', '1a972a71-a530-407a-b3f7-1dd9b2532cec', '82383e2d-d3a2-481d-b0cf-bfe165ed9bfd', 149, '2026-03-02T05:11:28.079567+00:00', FALSE),
  ('6ac1ba71-9852-4f0c-97b2-0bb166057ef0', '3de80488-e1b6-4908-a6ec-9c51f46f43b9', '2eb95b36-c9c3-4720-849b-fc240c9434da', 479, '2026-03-02T05:11:28.079575+00:00', FALSE),
  ('9125a495-66f2-435b-8eab-8a2557f55820', '7b2881f0-289b-4ea4-9c0e-c1748249b70a', '9f223b65-41a3-495e-940a-5a07d2ba4ba7', 220, '2026-03-02T05:11:28.079670+00:00', FALSE),
  ('e11b19c9-6bf7-45e2-9831-bb42050b7e8e', '78c129b6-21e5-40c6-b9cf-d25ad5193e68', '4e9dfce6-555b-434a-81b6-237c61b9b530', 380, '2026-03-02T05:11:28.079687+00:00', FALSE),
  ('09b6649c-99a3-48c3-864b-9cbd3d57980a', '7e9bbec4-e2aa-4d67-b1c0-35f7db1d0dae', 'ed20f879-2d42-4ba7-8df7-d696c4b6aafd', 317, '2026-03-02T05:11:28.079696+00:00', FALSE),
  ('68940de9-65d3-4afb-b080-0354b5c6b1a5', '153f4022-a4ce-404a-8544-25d004fd34ad', '82383e2d-d3a2-481d-b0cf-bfe165ed9bfd', 284, '2026-03-02T05:11:28.079710+00:00', FALSE),
  ('050b1905-c72a-446d-81f7-087cddb24434', '1a972a71-a530-407a-b3f7-1dd9b2532cec', 'e17ae312-37c6-4a66-9172-2d20d8528033', 156, '2026-03-02T05:11:28.079870+00:00', FALSE),
  ('7d1bbcaf-69eb-4acc-855d-3174d659a430', '188c23a6-4c3b-4c25-9b40-9ec8ad7712a3', '755c7aff-e538-4681-9b90-4b870a42ac72', 210, '2026-03-02T05:11:28.079923+00:00', FALSE),
  ('07fb83e9-5818-4991-aeba-54f65239954e', 'e83caccd-e81e-454a-bf92-9d9a5a509e25', '3de80488-e1b6-4908-a6ec-9c51f46f43b9', 291, '2026-03-02T05:11:28.079938+00:00', FALSE),
  ('a8a3e69b-392d-4d59-a016-183152eda94a', '64aeeb22-42db-46bf-85bd-b21b135c9803', '8a034e84-26b3-4198-8ec9-3749b1f60537', 261, '2026-03-02T05:11:28.079948+00:00', FALSE),
  ('dcdbd81b-9c64-42cb-9960-4f10b13675ac', '398de7e0-8f4f-44ba-adfe-503a157cfe7c', 'a50c967b-2e89-448d-be47-4df628c51572', 104, '2026-03-02T05:11:28.079958+00:00', FALSE),
  ('df5490e1-cb3f-4109-be18-2aa10a52d848', '491943d2-8aa3-4484-9d6d-afcab7ec579a', '7019b91a-d908-4c93-8ed2-d1c60e518630', 302, '2026-03-02T05:11:28.079967+00:00', FALSE),
  ('801607a9-a4f7-424d-b80f-915094a6f389', '3de80488-e1b6-4908-a6ec-9c51f46f43b9', '55a1207d-a029-48da-a79d-c1f3f6824a37', 129, '2026-03-02T05:11:28.079979+00:00', FALSE),
  ('80851b7f-a1b5-4eb5-a54e-2c3c55e9dd2a', 'a50c967b-2e89-448d-be47-4df628c51572', '710128e0-4e1d-479c-acbc-497bbc5bc802', 450, '2026-03-02T05:11:28.079999+00:00', FALSE),
  ('b1b728aa-177f-4b2f-a5ff-0267898ff6b1', '145cc805-810a-4320-864d-ba6b5c6fbc33', '78c129b6-21e5-40c6-b9cf-d25ad5193e68', 490, '2026-03-02T05:11:28.080011+00:00', FALSE),
  ('bc2e3265-7349-4ef4-a47d-9cd004d8baa4', 'a50c967b-2e89-448d-be47-4df628c51572', '5cf1a55e-7904-4937-a4bd-bf3d1fc1c839', 82, '2026-03-02T05:11:28.080021+00:00', FALSE),
  ('1b51395e-1dc0-4201-9596-34f3dbac4a14', 'eb113965-6ee6-459e-ba0f-fb6cb1d0ae34', '1e049026-1231-4763-bc50-82cb51950fa2', 175, '2026-03-02T05:11:28.080032+00:00', FALSE),
  ('cb319fb3-2707-4712-a042-b58a51d5407a', 'a2eaefc9-dbff-4704-b908-74518d687e17', '188c23a6-4c3b-4c25-9b40-9ec8ad7712a3', 20, '2026-03-02T05:11:28.080041+00:00', FALSE),
  ('5339f7f9-fd7e-4dc5-861b-98121e3fd696', '2eb95b36-c9c3-4720-849b-fc240c9434da', 'd9380e8d-8949-4849-af57-7a91a1a2b953', 339, '2026-03-02T05:11:28.080050+00:00', FALSE),
  ('ae4424b0-27d2-43d3-89d7-a1d8c49f5ee0', '37a1e15c-a38c-4763-a2bd-042741bce012', 'a50c967b-2e89-448d-be47-4df628c51572', 342, '2026-03-02T05:11:28.080059+00:00', FALSE),
  ('92c39e1d-45a3-41c9-9583-cfcaca15c4a3', '1a972a71-a530-407a-b3f7-1dd9b2532cec', 'a50c967b-2e89-448d-be47-4df628c51572', 484, '2026-03-02T05:11:28.080067+00:00', FALSE),
  ('a7cf23d8-b178-4a65-ba03-3ece7b259d51', 'dec825d6-91f4-4f8f-b44b-74cf6b4b78f6', '4736fc20-1c22-49c2-af87-c35802302507', 400, '2026-03-02T05:11:28.080076+00:00', FALSE),
  ('1a1981a8-b63a-4a73-9b8a-ff517286745d', 'ed20f879-2d42-4ba7-8df7-d696c4b6aafd', '8a034e84-26b3-4198-8ec9-3749b1f60537', 72, '2026-03-02T05:11:28.080087+00:00', FALSE),
  ('473dbb0e-652c-43b3-b571-f88c1af1e8c5', '7e9bbec4-e2aa-4d67-b1c0-35f7db1d0dae', '5cf1a55e-7904-4937-a4bd-bf3d1fc1c839', 146, '2026-03-02T05:11:28.080095+00:00', FALSE),
  ('26ebb626-b5a4-4a23-b411-9428219bc8ae', 'b5af580c-4998-4f68-b52f-5c5b6a9d0aab', '2eb95b36-c9c3-4720-849b-fc240c9434da', 261, '2026-03-02T05:11:28.080104+00:00', FALSE);

-- Table: AccountSignals
-- This is pushed by BigQuery through a continuous query if it is configured
INSERT INTO AccountSignals (SignalId, PlayerId, AlertType, EventTime) VALUES ('50b64a6a-2e8f-4a0b-9742-c7e180949e82', '4e9dfce6-555b-434a-81b6-237c61b9b530', 'SUSPICIOUS_MOVEMENT', '2026-03-02T05:11:28.078156+00:00');
INSERT INTO AccountSignals (SignalId, PlayerId, AlertType, EventTime) VALUES ('d2659e80-bce0-46b0-9570-6bb8b3c99d34', '686337b0-304e-4270-89c3-d015f9039294', 'SUSPICIOUS_MOVEMENT', '2026-03-02T05:11:28.078198+00:00');
INSERT INTO AccountSignals (SignalId, PlayerId, AlertType, EventTime) VALUES ('10504018-b7a4-47a4-89b2-950fa492bbd4', '1627bc18-c42e-4599-b5f1-6f3d52669edb', 'SUSPICIOUS_MOVEMENT', '2026-03-02T05:11:28.078244+00:00');
INSERT INTO AccountSignals (SignalId, PlayerId, AlertType, EventTime) VALUES ('ccbba78a-0f45-49b9-a58b-21777de250cf', '215b3d04-402a-4ac2-83ed-1edb9a421691', 'SUSPICIOUS_MOVEMENT', '2026-03-02T05:11:28.078293+00:00');
INSERT INTO AccountSignals (SignalId, PlayerId, AlertType, EventTime) VALUES ('affa841d-0f9a-4b0f-ad6d-eb3e180263fe', '78d66ff4-0519-4157-9d95-76c2900ba7f9', 'SUSPICIOUS_MOVEMENT', '2026-03-02T05:11:28.078337+00:00');
INSERT INTO AccountSignals (SignalId, PlayerId, AlertType, EventTime) VALUES ('1fbdd097-b431-4a21-9a5c-a1f53db6d754', '822a8aae-57bd-460e-9643-69815990bec8', 'SUSPICIOUS_MOVEMENT', '2026-03-02T05:11:28.078383+00:00');
INSERT INTO AccountSignals (SignalId, PlayerId, AlertType, EventTime) VALUES ('0fc87891-dd4b-494b-8c47-f6ccc2c592c7', '9f223b65-41a3-495e-940a-5a07d2ba4ba7', 'SUSPICIOUS_MOVEMENT', '2026-03-02T05:11:28.078420+00:00');
INSERT INTO AccountSignals (SignalId, PlayerId, AlertType, EventTime) VALUES ('cc117869-7a6b-4e66-89bf-e3148ac8c6ea', '3910b199-caf7-4822-8346-1ba9bd750c8e', 'SUSPICIOUS_MOVEMENT', '2026-03-02T05:11:28.078456+00:00');
INSERT INTO AccountSignals (SignalId, PlayerId, AlertType, EventTime) VALUES ('c85a78c6-3cc8-4262-8fd2-2fb9661a1b96', '78c129b6-21e5-40c6-b9cf-d25ad5193e68', 'SUSPICIOUS_MOVEMENT', '2026-03-02T05:11:28.078499+00:00');
INSERT INTO AccountSignals (SignalId, PlayerId, AlertType, EventTime) VALUES ('ccdb20f8-cd05-42ae-b942-cb41396ec27d', 'eba84e9d-bd10-466e-8fcd-899c0a868149', 'SUSPICIOUS_MOVEMENT', '2026-03-02T05:11:28.078537+00:00');
INSERT INTO AccountSignals (SignalId, PlayerId, AlertType, EventTime) VALUES ('721511f1-ebc4-45dc-adb5-9dafc5426bc3', '37a1e15c-a38c-4763-a2bd-042741bce012', 'SUSPICIOUS_MOVEMENT', '2026-03-02T05:11:28.078579+00:00');
INSERT INTO AccountSignals (SignalId, PlayerId, AlertType, EventTime) VALUES ('4d8b0f0d-211a-4537-8c79-c2fd815812f3', 'c5337857-3b58-4b0c-8814-4585dcbf765f', 'SUSPICIOUS_MOVEMENT', '2026-03-02T05:11:28.078627+00:00');

Isso pode levar alguns minutos. Você poderá visualizar os dados depois que o processo for concluído.

Prévia de dados do Spanner Studio

4. O Watchdog (consultas contínuas do BigQuery e sincronização do Spanner)

Nossa primeira linha de defesa são os dados de telemetria transmitidos para o BigQuery. Queremos monitorar movimentos suspeitos (por exemplo, distâncias impossíveis) e enviar um alerta de volta ao Spanner em tempo real.

Em um cenário real, você usaria as consultas contínuas do BigQuery e o ETL reverso para transmitir esses dados. No entanto, isso requer uma reserva com a edição ENTERPRISE ou mais recente.

Este é o comando que seria usado se a reserva estivesse disponível. Não será necessário copiar isso para o console se você não tiver configurado as reservas:

EXPORT DATA
  OPTIONS (
    uri = 'https://spanner.googleapis.com/projects/<YOUR_PROJECT_ID>/instances/game-instance/databases/game-db',
    format='CLOUD_SPANNER',
    spanner_options="""{ "table": "AccountSignals" }"""
  ) AS
SELECT
  GENERATE_UUID() as SignalId,
  PlayerId,
  'SUSPICIOUS_MOVEMENT' as AlertType,
  CURRENT_TIMESTAMP() as EventTime
FROM `game_analytics.GameplayTelemetry`
WHERE
  EventType = 'player_move'
  AND (LocationX > 1000 OR LocationY > 1000);

Para criar a consulta contínua, clique em Mais > Criar consulta contínua no espaço de trabalho SQL do console do BigQuery.

Essa consulta funciona como um mecanismo de ETL reverso, garantindo que nosso sistema transacional (Spanner) seja informado instantaneamente sobre a detecção de anomalias detectadas em nosso sistema analítico (BigQuery).

Para este laboratório, inserimos artificialmente algumas transações no Spanner.

5. O detetive multimodal (Spanner Graph e busca vetorial)

Agora que o Spanner tem o indicador "Alto risco", você pode investigar o grupo de fraude. Vamos usar o Spanner Graph para visualizar a rede financeira e encontrar o líder.

Execute essas consultas no Spanner Studio.

Gráfico: encontre o líder

Essa consulta rastreia a rede financeira de transações em que as vítimas transferem para um ladrão, que, por sua vez, transfere para um nó chefe. Ele agrupa por chefe e soma o saque.

Copie-a no Spanner Studio e clique em Executar.

GRAPH PlayerNetwork
MATCH (victim)-[:Transfers]->(thief)-[t:Transfers]->(boss)
RETURN
  boss.Name AS RingLeader, COUNT(t) AS TributesReceived,
  SUM(t.Amount) AS TotalLoot
GROUP BY RingLeader
ORDER BY TotalLoot DESC
LIMIT 5;

Você vai ver "Pixel" como o principal destinatário de tributos.

Investigação de vários indicadores

Vamos mesclar os resultados do Graph com os indicadores comportamentais em tempo real que enviamos do BigQuery anteriormente. Queremos encontrar jogadores que estão enviando dinheiro para "Pixel" E que foram sinalizados por movimentação suspeita.

SELECT DISTINCT
  p.Name,
  s.AlertType as BQ_Signal,
  s.EventTime as SignalTime
FROM GRAPH_TABLE (
  PlayerNetwork
  MATCH (associate:Players)-[:Transfers]->(boss:Players)
  WHERE boss.Name = 'Pixel'
  RETURN DISTINCT associate.Name
) as g
JOIN Players p
  ON p.Name = g.Name
JOIN AccountSignals s
  ON p.PlayerId = s.PlayerId
ORDER BY s.EventTime DESC;

Pesquisa vetorial: identificar contas de bot

Esses jogadores são reais ou uma rede coordenada de bots? Use a busca vetorial para identificar contas com descrições de perfil suspeitamente semelhantes à de "Pixel".

SELECT
  Name, AvatarDescription,
  COSINE_DISTANCE(AvatarEmbedding, (SELECT AvatarEmbedding FROM Players WHERE Name = 'Pixel')) as Similarity
FROM Players
WHERE Name != 'Pixel'
ORDER BY Similarity ASC
LIMIT 5;

Uma pontuação de similaridade mais baixa significa que eles estão mais próximos do vetor de "Pixel". Se as descrições forem semelhantes, é provável que sejam bots.

Também é possível aplicar funções escalares na cláusula MATCH:

GRAPH PlayerNetwork
MATCH (associate:Players)-[:Transfers]->(boss:Players)
WHERE boss.Name = 'Pixel'
ORDER BY (
  COSINE_DISTANCE(associate.AvatarEmbedding, (SELECT AvatarEmbedding FROM Players WHERE Name = 'Pixel'))
) ASC
RETURN DISTINCT associate.Name

6. Descobrindo o enredo (grafo de propriedades do BigQuery e integração com o GCS)

Pegamos o líder, mas precisamos entender como eles coordenaram esse enorme "roubo de atum". Vamos rastrear os padrões de comunicação no BigQuery usando o gráfico de propriedades do BigQuery para consultar os registros de chat do jogo.

Execute a seguinte consulta no BigQuery Studio:

Gráfico de propriedades do BigQuery

Rastreamento da comunicação entre o "Pixel" e outros jogadores:

GRAPH game_analytics.CatChatNetwork
MATCH (p1:Players)-[c:Communicates]->(p2:Players)
WHERE p1.Name = 'Pixel' OR p2.Name = 'Pixel'
RETURN
  p1.Name AS Sender,
  p2.Name AS Receiver,
  c.Message,
  -- Resolving structured metadata from ObjectRef
  p1.ProfilePictureUrl.uri AS SenderProfilePic
ORDER BY Message DESC;

Observe mensagens como "A Operação Aquário está em andamento" e "Desviando fundos para a reserva central de atum". Veja como os gráficos de propriedades do BigQuery permitem analisar comunicações enriquecidas com dados não estruturados (referências de imagem do GCS usando ProfilePictureUrl.uri).

Se você seguir o link do GCS nos resultados, vai encontrar a imagem do jogador:

Foto do perfil do Pixel

Essa consulta analítica compara os padrões de chat e as imagens entre os membros do grupo de fraude.

Antes de executar isso, declare um modelo multimodal para gerar embeddings das fotos do perfil armazenadas no bucket do Cloud Storage. Esse modelo se conecta pela conexão criada na configuração inicial. Portanto, você também vai conceder permissões de usuário da Vertex AI ao usuário técnico anexado a essa conexão.

Substitua «PROJECT_ID» pelo ID do projeto.

GRANT `roles/aiplatform.user`
ON PROJECT `<<PROJECT_ID>>`
TO "connection:<<PROJECT_ID>>.us.unicorn-connection";

Agora é possível criar a conexão.

CREATE OR REPLACE MODEL `game_analytics.multimodal_model`
  REMOTE WITH CONNECTION `us.unicorn-connection`
  OPTIONS (ENDPOINT = 'multimodalembedding@001');

Se isso falhar devido a um erro de permissão (por exemplo,"O bqcx-12345745345345@gcp-sa-bigquery-condel.iam.gserviceaccount.com não tem permissão para acessar ou usar o endpoint…"), aguarde alguns minutos até que as permissões sejam propagadas e tente novamente.

A consulta abaixo usa a função AI.GENERATE_EMBEDDING para analisar as imagens no bucket de armazenamento e criar embeddings. Esses embeddings são comparados usando uma COSINE_DISTANCE, para que possamos entender bem a semelhança entre os registros de chat e as fotos do perfil.

-- BigQuery Property Graph: Tracing communication patterns in chat logs
-- AND calculating distance between auto-embedded chat message and profile picture
-- BigQuery Property Graph: Tracing communication patterns
-- AND identifying similarity AMONG the fraudsters themselves
WITH GraphResults AS (
  SELECT *
  FROM GRAPH_TABLE(
  game_analytics.CatChatNetwork
    MATCH (p1:Players)-[c:Communicates]->(p2:Players)
    WHERE p1.Name = 'Pixel' OR p2.Name = 'Pixel'
    RETURN
      p1.Name AS Sender,
      c.Message,
      p1.ProfilePictureUrl.uri AS SenderProfilePic,
      c.MessageEmbedding.result AS MessageEmbedding
  )
),
UniquePics AS (
  SELECT DISTINCT SenderProfilePic AS uri FROM GraphResults
),
PicEmbeddings AS (
  SELECT embedding, uri
  FROM AI.GENERATE_EMBEDDING(
    MODEL game_analytics.multimodal_model,
    (
      SELECT OBJ.MAKE_REF(uri, 'us.unicorn-connection') as content, uri
      FROM UniquePics
    )
  )
),
CatData AS (
  -- Distinct list of players (excluding Pixel) with their embeddings and HTTPS Pic URLs
  SELECT DISTINCT
    g.Sender,
    g.MessageEmbedding,
    g.Message,
    p.embedding AS PicEmbedding,
    REPLACE( g.SenderProfilePic, 'gs://sample-data-and-media/spanner-bq-fraud-heist/profile_pics/', 'https://storage.mtls.cloud.google.com/sample-data-and-media/spanner-bq-fraud-heist/profile_pics/') AS SenderProfilePic
  FROM GraphResults g
  LEFT JOIN PicEmbeddings p ON g.SenderProfilePic = p.uri
  WHERE g.Sender != 'Pixel'
    AND g.MessageEmbedding IS NOT NULL
    AND p.embedding IS NOT NULL
)
SELECT
  c1.Sender AS Fraudster_A,
  c2.Sender AS Fraudster_B,
  c1.SenderProfilePic AS Pic_A,
  c2.SenderProfilePic AS Pic_B,
  c1.Message,
  -- Compare chat messages between Fraudster A and B
  COSINE_DISTANCE(c1.MessageEmbedding, c2.MessageEmbedding) AS MessageDistance,
  -- Compare profile pictures between Fraudster A and B
  COSINE_DISTANCE(c1.PicEmbedding, c2.PicEmbedding) AS PictureDistance
FROM CatData c1
CROSS JOIN CatData c2
WHERE c1.Sender < c2.Sender -- Avoid self-comparison and duplicate pairs (A-B and B-A)
  AND c1.SenderProfilePic <> c2.SenderProfilePic
ORDER BY PictureDistance ASC, MessageDistance ASC
LIMIT 10;

Se você abrir as fotos do perfil, vai notar a semelhança na forma como os membros do clã se apresentam.

Visualizar o grupo de fraude

É possível usar notebooks e o Python Cell Magic para visualizar o grupo de fraude. Isso permite visualizar facilmente os resultados do gráfico. Para mais informações, consulte a documentação sobre visualização.

No BigQuery Studio, clique em Mais > Notebook > Notebook vazio.

Criar Notebook

Cole o seguinte em uma célula de código:

!pip install bigquery-magics==0.12.1

Use o botão + Código para criar uma célula e cole o seguinte:

%%bigquery --graph
GRAPH game_analytics.CatChatNetwork
MATCH p=(p1:Players)-[c:Communicates]-(p2:Players)
WHERE p1.Name = 'Pixel' OR p2.Name = 'Pixel'
RETURN TO_JSON(p) AS full_path

Clique em Executar tudo. Depois de um minuto, vai aparecer uma visualização em gráfico da rede de comunicação.

Visualização de gráfico

7. Limpeza

Para evitar cobranças na sua conta do Google Cloud pelos recursos usados neste codelab, exclua os recursos criados.

Excluir a instância do Spanner

gcloud spanner instances delete game-instance

Excluir o conjunto de dados do BigQuery

bq rm -r -f -d game_analytics

Como alternativa, exclua o projeto

Se você criou um novo projeto para este laboratório, exclua o projeto inteiro:

gcloud projects delete <YOUR_PROJECT_ID>

8. Parabéns!

Parabéns! Você criou um escudo de defesa contra fraudes em tempo real usando o Spanner e o BigQuery.

Você aprendeu a:

  • Use as consultas contínuas do BigQuery para enviar insights em tempo real ao Spanner.
  • Use o Spanner Graph para rastrear relações financeiras.
  • Use a pesquisa vetorial do Spanner para consultas de similaridade em dados não estruturados.
  • Use o BigQuery Graph para rastrear redes de comunicação.

A seguir