Spanner & BigQuery: Perisai Pertahanan Penipuan Real-Time

1. Pengantar

Selamat datang di backend Petverse, game online multiplayer tempat pemain membuat avatar hewan, menjelajahi dunia, dan memperdagangkan mata uang dalam game.

Baru-baru ini, ekonomi game ini terancam. Uang dalam jumlah besar terkuras dari rekening pemain Dog yang kaya, dan dikonversi sepenuhnya menjadi stok tuna premium. Kami mencurigai sekelompok "Kucing Robin Hood" yang mencuri dari kaya untuk memberi makan kucing-kucing miskin.

Dalam codelab ini, Anda akan membangun perisai pertahanan terhadap penipuan real-time untuk menangkap dalang dan jaringan bot otomatisnya. Anda akan merasakan bagaimana data operasional di Spanner digabungkan dengan analisis di BigQuery untuk mendeteksi dan menyelidiki pola penipuan yang kompleks dengan kueri graf (GQL) dan relasional.

Yang akan Anda lakukan

  • Mendeteksi anomali dalam streaming real-time menggunakan kueri berkelanjutan BigQuery dan mengirimkannya ke Spanner.
  • Selidiki jaringan penipuan menggunakan Spanner Graph untuk melacak hubungan keuangan.
  • Identifikasi akun bot menggunakan penelusuran vektor Spanner untuk menemukan deskripsi profil yang serupa.
  • Ungkap plot koordinasi menggunakan BigQuery Graph untuk menganalisis log komunikasi yang sangat besar.

Yang Anda butuhkan

  • Project Google Cloud dengan Penagihan Diaktifkan.
  • Pengetahuan dasar tentang SQL, perintah terminal, dan Python.
  • Anda mungkin memerlukan akun GitHub (kode dihosting di GitHub).

Audiens: Developer, Data Engineer, dan Arsitek Menengah.

Perkiraan Total Durasi: 45 hingga 60 menit.

Perkiraan Biaya: Resource yang dibuat dalam codelab ini seharusnya berbiaya kurang dari $5.

2. Sebelum Memulai/Penyiapan

Membuat atau Memilih Project Google Cloud

Anda memerlukan project Google Cloud dengan penagihan yang diaktifkan untuk menggunakan layanan yang diperlukan untuk lab ini.

  1. Di Konsol Google Cloud, di halaman pemilih project, pilih atau buat project Google Cloud.
  2. Pastikan penagihan diaktifkan untuk project Cloud Anda. Pelajari cara memverifikasi apakah penagihan diaktifkan.
  3. Temukan project ID Anda dari halaman beranda Konsol Cloud

Halaman beranda Konsol Cloud

Mulai Cloud Shell

Anda akan menggunakan Google Cloud Shell sebagai lingkungan eksekusi. Cloud Shell telah diinstal dengan gcloud, git, dan alat lain yang Anda perlukan.

  1. Buka Google Cloud Shell.
  2. Jika diminta, klik Authorize.
  3. Pastikan Anda mengerjakan project Anda dengan menetapkan variabel lingkungan di terminal Cloud Shell:
export PROJECT_ID=<YOUR_PROJECT_ID>
gcloud config set project $PROJECT_ID

Cloud Shell: menggunakan gcloud config untuk menetapkan project

Mengaktifkan API yang Diperlukan

Jalankan perintah berikut untuk mengaktifkan API untuk Spanner, BigQuery, dan Vertex AI:

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

Membuat Clone Repositori

Buat clone repositori yang berisi kode aplikasi dan contoh skema:

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

3. Menyediakan Infrastruktur

Sekarang, Anda akan menyiapkan data warehouse di BigQuery dan database operasional di Spanner.

Menyiapkan Set Data dan Koneksi BigQuery

BigQuery akan menganalisis aliran telemetri dari game.

Masih di dalam konsol di Google Cloud Shell, jalankan perintah berikut untuk memastikan project ID Anda masih ditetapkan:

export PROJECT_ID=<YOUR_PROJECT_ID>
gcloud config set project $PROJECT_ID
  1. Buat set data BigQuery game_analytics:
bq mk -d --location=US game_analytics
  1. Buat koneksi untuk terhubung ke bucket Storage dan (opsional) ke Spanner:
bq mk --connection --location=US --project_id=$PROJECT_ID \
    --connection_type=CLOUD_RESOURCE unicorn-connection
  1. Buat skema untuk tabel GameplayTelemetry, AccountSignals, Players, dan ChatLogs menggunakan file skema:
bq query --use_legacy_sql=false < bq_schema.sql

Menyiapkan Spanner

Google Cloud Spanner akan menangani transaksi real-time operasional. Untuk lab ini, kita akan menggunakan instance 100 Unit Pemrosesan (PU) yang hemat biaya.

  1. Buat instance Spanner:
gcloud spanner instances create game-instance \
    --config=regional-us-central1 \
    --description="Game Instance" \
    --processing-units=100 \
    --edition=ENTERPRISE
  1. Buat database Spanner game-db:
gcloud spanner databases create game-db --instance=game-instance
  1. Perbarui skema Spanner dengan tabel aplikasi (Players, Transactions, AccountSignals) dan grafik properti (PlayerNetwork):
gcloud spanner databases ddl update game-db --instance=game-instance --ddl-file=spanner_schema.sql
  1. Buat AvatarSearchIndex untuk penelusuran vektor multimodal kita:
gcloud spanner databases ddl update game-db --instance=game-instance \
    --ddl="CREATE SEARCH INDEX AvatarSearchIndex ON Players(AvatarDescriptionTokens)"

Mengimpor Data ke BigQuery

  1. Impor data demo ke 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. Buka Konsol BigQuery dan jelajahi data dalam set data game_analytics.

Konsol BigQuery

Mengimpor Data ke Spanner

Buka Konsol Spanner dan pelajari data dalam database game-db.

Klik Spanner Studio dan buka New Query (+).

Konsol Spanner

Tempel pernyataan INSERT berikut ke dalam editor kueri, lalu klik Run:

-- 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');

Proses ini mungkin memerlukan waktu beberapa menit. Anda akan dapat melihat pratinjau data setelah selesai.

Pratinjau data dari Spanner Studio

4. Watchdog (Kueri Berkelanjutan BigQuery & Sinkronisasi Spanner)

Garis pertahanan pertama kami adalah data telemetri yang di-streaming ke BigQuery. Kita ingin memantau pergerakan yang mencurigakan (misalnya, jarak yang mustahil) dan mengirimkan pemberitahuan kembali ke Spanner secara real-time.

Dalam skenario dunia nyata, Anda akan menggunakan kueri berkelanjutan BigQuery dan ETL terbalik untuk melakukan streaming data ini. Namun, hal ini memerlukan pemesanan dengan edisi ENTERPRISE atau yang lebih tinggi.

Berikut adalah tampilan perintah jika pemesanan tersedia. Anda tidak perlu menyalinnya ke konsol jika belum menyiapkan pemesanan:

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);

Untuk membuat kueri berkelanjutan, klik More > Create continuous query di BigQuery Console SQL Workspace.

Kueri ini bertindak sebagai mesin reverse-ETL, yang memastikan sistem transaksional kita (Spanner) langsung mengetahui anomali yang terdeteksi dalam sistem analisis kita (BigQuery).

Untuk lab ini, kami sengaja memasukkan beberapa transaksi ke Spanner.

5. Detektif Multimodal (Spanner Graph & Penelusuran Vektor)

Setelah Spanner memiliki sinyal "Risiko Tinggi", Anda dapat menyelidiki jaringan penipuan. Kita akan menggunakan Spanner Graph untuk memvisualisasikan jaringan keuangan dan menemukan dalangnya.

Jalankan kueri ini di Spanner Studio

Grafik: Menemukan Dalang

Kueri ini melacak jaringan transaksi keuangan tempat korban mentransfer ke pencuri, yang kemudian mentransfer ke node bos. Tabel ini mengelompokkan berdasarkan bos dan menjumlahkan jarahan.

Salin ke Spanner Studio dan klik Run.

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;

Anda akan melihat "Pixel" sebagai penerima penghargaan teratas.

Investigasi Multi-sinyal

Mari kita gabungkan hasil Graph dengan sinyal perilaku real-time yang kita kirim dari BigQuery sebelumnya. Kita ingin menemukan pemain yang mengirim uang ke "Pixel" DAN telah ditandai karena pergerakan yang mencurigakan.

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;

Penelusuran vektor: Mengidentifikasi Akun Bot

Apakah ini pemain sungguhan atau jaringan bot terkoordinasi? Gunakan penelusuran vektor untuk mengidentifikasi akun dengan deskripsi profil yang mencurigakan dan mirip dengan "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;

Skor kesamaan yang lebih rendah berarti skor tersebut lebih dekat dengan vektor "Pixel". Jika deskripsinya mirip, kemungkinan besar mereka adalah bot.

Kita juga dapat menerapkan fungsi skalar dalam klausa 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. Mengungkap Plot (Integrasi BigQuery Property Graph & GCS)

Kita telah menangkap dalangnya, tetapi kita perlu memahami cara mereka mengoordinasikan "pencurian tuna" besar-besaran ini. Kita akan melacak pola komunikasi di BigQuery menggunakan BigQuery Property Graph untuk mengkueri log chat game.

Jalankan kueri berikut di BigQuery Studio:

Grafik Properti BigQuery

Melacak komunikasi antara "Pixel" dan pemain lain:

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;

Amati pesan seperti "Operation Fishbowl is a go" dan "Diverting funds to the central tuna reserve". Anda dapat melihat cara BigQuery Property Graphs memungkinkan Anda menganalisis komunikasi yang diperkaya dengan data tidak terstruktur (referensi Gambar GCS menggunakan ProfilePictureUrl.uri).

Jika Anda mengikuti link GCS di hasil, Anda akan melihat gambar pemain:

Foto Profil Pixel

Kueri analisis ini selanjutnya membandingkan pola chat dan gambar di antara anggota jaringan penipuan.

Sebelum Anda dapat menjalankannya, deklarasikan model multimodal untuk membuat embedding foto profil yang disimpan di bucket Cloud Storage. Model ini terhubung melalui koneksi yang dibuat dalam penyiapan awal, sehingga Anda juga akan memberikan izin pengguna Vertex AI kepada pengguna teknis yang terlampir untuk koneksi tersebut.

Ganti «PROJECT_ID» dengan project ID Anda.

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

Sekarang Anda dapat membuat koneksi.

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

Jika gagal karena error izin (yaitu,"The bqcx-12345745345345@gcp-sa-bigquery-condel.iam.gserviceaccount.com does not have the permission to access or use the endpoint..."), tunggu beberapa menit hingga izin diterapkan, lalu coba lagi.

Kueri di bawah menggunakan fungsi AI.GENERATE_EMBEDDING untuk memeriksa gambar di bucket penyimpanan dan membuat embedding. Embedding ini kemudian dibandingkan menggunakan COSINE_DISTANCE, sehingga kita mendapatkan pemahaman yang baik tentang seberapa mirip log chat dan foto profil.

-- 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;

Jika Anda membuka foto profil, Anda akan melihat kesamaan dalam cara anggota klan menampilkan diri mereka.

Memvisualisasikan jaringan penipuan

Anda dapat menggunakan notebook dan Python Cell Magic untuk memvisualisasikan jaringan penipuan. Hal ini memungkinkan kita memvisualisasikan hasil grafik dengan mudah. Untuk informasi selengkapnya tentang hal ini, lihat dokumentasi untuk visualisasi.

Di BigQuery Studio, klik More > Notebook > Empty Notebook.

Buat Notebook

Tempelkan kode berikut ke dalam sel kode:

!pip install bigquery-magics==0.12.1

Gunakan tombol + Code untuk membuat sel baru dan tempelkan kode berikut:

%%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

Klik Run all. Setelah sekitar satu menit, Anda akan melihat visualisasi grafik jaringan komunikasi.

Visualisasi Grafik

7. Pembersihan

Agar tidak menimbulkan biaya pada akun Google Cloud Anda untuk resource yang digunakan dalam codelab ini, hapus resource yang Anda buat.

Menghapus Instance Spanner

gcloud spanner instances delete game-instance

Menghapus Set Data BigQuery

bq rm -r -f -d game_analytics

Atau, Hapus Project

Jika Anda membuat project baru untuk lab ini, Anda dapat menghapus seluruh project:

gcloud projects delete <YOUR_PROJECT_ID>

8. Selamat!

Selamat! Anda telah berhasil membangun sistem pertahanan terhadap penipuan secara real-time menggunakan Spanner dan BigQuery.

Anda telah mempelajari cara:

  • Gunakan kueri berkelanjutan BigQuery untuk mengirim insight real-time ke Spanner.
  • Gunakan Spanner Graph untuk melacak hubungan keuangan.
  • Gunakan penelusuran vektor Spanner untuk kueri kemiripan pada data tidak terstruktur.
  • Gunakan BigQuery Graph untuk melacak jaringan komunikasi.

Apa langkah selanjutnya?