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 menukarkan mata uang dalam game.

Baru-baru ini, ekonomi game tersebut terancam. Sejumlah besar uang terkuras dari akun pemain Dog yang kaya, yang sepenuhnya dikonversi menjadi stok tuna premium. Kami mencurigai sekelompok "Kucing Robin Hood" mencuri dari- kaya untuk memberi makan kucing-kucing miskin.

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

Yang akan Anda lakukan

• Mendeteksi anomali dalam streaming real-time menggunakan kueri berkelanjutan BigQuery dan mengirimkannya ke Spanner.
• Selidiki sindikat penipuan menggunakan Spanner Graph untuk melacak hubungan keuangan.
• Mengidentifikasi akun bot menggunakan penelusuran vektor Spanner untuk menemukan deskripsi profil yang serupa.
• Temukan 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 tingkat Menengah.

Perkiraan Total Durasi: 45 hingga 60 menit.

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

2. Sebelum Anda Mulai / Menyiapkan

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 Cloud Console

Mulai Cloud Shell

Anda akan menggunakan Google Cloud Shell sebagai lingkungan eksekusi. Cloud Shell telah diinstal dengan gcloud, git, dan alat lain yang akan 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:

Ganti <YOUR_PROJECT_ID> dengan ID project Anda yang sebenarnya. Gunakan Ctrl atau Command + v untuk menempelkan ke terminal.

export PROJECT_ID=<YOUR_PROJECT_ID>
gcloud config set project $PROJECT_ID

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 skema sampel:

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:

Ganti <YOUR_PROJECT_ID> dengan ID project Anda yang sebenarnya. Gunakan Ctrl atau Command + v untuk menempelkan ke terminal.

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

2. 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

3. 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 operasional real-time. 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

2. Buat database Spanner game-db:

gcloud spanner databases create game-db --instance=game-instance

3. 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

4. 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

3. Buka Konsol BigQuery dan jelajahi data di set data game_analytics.

Mengimpor Data ke Spanner

Buka Spanner Console dan jelajahi data di database game-db.

Klik Spanner Studio dan buka New Query (+).

Tempelkan pernyataan INSERT berikut ke 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 dapat melihat pratinjau data setelah selesai.

4. Watchdog (Kueri Berkelanjutan BigQuery & Sinkronisasi Spanner)

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

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

Perintahnya akan terlihat seperti ini jika reservasi tersedia. Anda tidak perlu menyalinnya ke konsol jika belum menyiapkan reservasi:

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, Anda dapat mengklik Lainnya > Buat kueri berkelanjutan di Ruang Kerja SQL Konsol BigQuery.

Kueri ini bertindak sebagai mesin reverse-ETL, yang memastikan sistem transaksi kami (Spanner) langsung mengetahui anomali yang terdeteksi di sistem analisis kami (BigQuery).

Untuk lab ini, kami secara buatan memasukkan beberapa transaksi ke Spanner.

5. Detektif Multimodal (Spanner Graph & Vector Search)

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

Jalankan kueri ini di Spanner Studio

Grafik: Temukan Pemimpinnya

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

Salin ke Spanner Studio, lalu 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 penghormatan teratas.

Investigasi Multi-sinyal

Mari gabungkan hasil Grafik 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 sangat 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 kemiripan yang lebih rendah berarti vektornya lebih dekat dengan vektor "Pixel". Jika memiliki deskripsi yang serupa, kemungkinan besar itu 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)

Kami telah menangkap dalangnya, tetapi kami perlu memahami bagaimana mereka mengoordinasikan "pencurian tuna" besar-besaran ini. Kami akan melacak pola komunikasi di BigQuery menggunakan Grafik Properti BigQuery 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;

Perhatikan pesan seperti "Operasi Fishbowl dimulai" dan "Mengalihkan dana ke cadangan tuna pusat". Anda dapat melihat bagaimana Grafik Properti BigQuery 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:

Kueri analitis ini selanjutnya membandingkan pola chat dan gambar di antara anggota sindikat penipuan.

Sebelum dapat menjalankan ini, deklarasikan model multimodal untuk membuat embedding foto profil yang disimpan di bucket Cloud Storage. Model ini terhubung melalui koneksi yang dibuat pada penyiapan awal, jadi Anda juga akan memberikan izin pengguna Vertex AI kepada pengguna teknis yang dilampirkan 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 hal ini gagal karena error izin (yaitu,"The [e-mail] does not have the permission to access or use the endpoint..."), tunggu beberapa menit hingga izin diterapkan dan coba lagi.

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

-- 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. Dengan begitu, kita dapat dengan mudah memvisualisasikan hasil grafik. Untuk mengetahui informasi selengkapnya tentang hal ini, lihat dokumentasi untuk visualisasi.

Di BigQuery Studio, klik Lainnya > Notebook > Notebook Kosong.

🗒️ Jika menggunakan notebook untuk pertama kalinya, Anda akan diminta memilih region. Kita telah menggunakan US di lab ini, jadi Anda harus memilih region dalam multi-region US. Anda mungkin juga perlu mengaktifkan Notebooks API.

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 Jalankan semua. Setelah sekitar satu menit, Anda akan melihat visualisasi grafik jaringan komunikasi.

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 perisai pertahanan penipuan real-time menggunakan Spanner dan BigQuery.

Anda telah mempelajari cara:

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

Apa langkah selanjutnya?

• Pelajari dokumentasi Spanner Graph.
• Temukan contoh kode lainnya di repositori GitHub Spanner Graph.
• Pelajari lebih lanjut Grafik BigQuery.
• Lihat kueri berkelanjutan BigQuery.