01 - BigQuery - Table Data Source.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "c28f7fa2",
   "metadata": {},
   "source": [
    "# 01 - BigQuery - Джерело даних для таблиці\n",
    "Використовуйте BigQuery для завантаження та підготовки даних для машинного навчання:\n",
    "\n",
    "**Передумови:**\n",
    "- [00 - Налаштування середовища](00%20-%20Environment%20Setup.ipynb)\n",
    "\n",
    "**Ресурси:**\n",
    "- [Клієнт Python для Google BigQuery](https://googleapis.dev/python/bigquery/latest/index.html)\n",
    "- [Завантажити дані BigQuery до Pandas](https://cloud.google.com/bigquery/docs/bigquery-storage-python-pandas)\n",
    "- [Блокноти з шаблонами BigQuery](https://github.com/GoogleCloudPlatform/bigquery-notebooks/tree/main/notebooks/official/template_notebooks)\n",
    "<!-- - Використання BigQuery з Python, блокноти у цьому репозиторії:\n",
    "    - [Tips/BigQuery - Python Client](../Tips/BigQuery%20-%20Python%20Client.ipynb)\n",
    "    - [03 - BigQuery ML (BQML)/Вступ до BigQuery ML (BQML)](../03%20-%20BigQuery%20ML%20(BQML)/Introduction%20to%20BigQuery%20ML%20(BQML).ipynb)\n",
    "    - [Прикладне прогнозування/1 - Огляд та підготовка даних для прогнозування часових рядів BigQuery](../Applied%20Forecasting/1%20-%20BigQuery%20Time%20Series%20Forecasting%20Data%20Review%20and%20Preparation.ipynb) -->"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5d0c695d-002d-48ca-9d52-839f95fdfee0",
   "metadata": {},
   "source": [
    "---\n",
    "## Вхідні дані\n",
    "\n",
    "**Огляд**\n",
    "\n",
    "Цей блокнот імпортує дані для поточного проекту в Google BigQuery. Всі інші блокноти використовують BigQuery як джерело даних.\n",
    "\n",
    "Цей блокнот, `01 - BigQuery - Table Data Source`, розпочинає життєвий цикл машинного навчання, імпортуючи вихідні дані та готуючи їх до машинного навчання.\n",
    "\n",
    "Подальші процеси використовують табличні дані, щоб вирішувати задачі навчання з вчителем: передбачити цільову змінну, використовуючи стовпчики ознак. Задачу яку будемо вирішувати - класифікація.\n",
    "\n",
    "**Дані\n",
    "\n",
    "Вихідні дані спочатку експортуються до хмарного сховища Google у форматі CSV. Вихідною таблицею BigQuery є `bigquery-public-data.ml_datasets.ulb_fraud_detection`.  Це таблиця транзакцій за кредитними картками, які класифікуються як шахрайські, `Class = 1`, або звичайні `Class = 0`.    \n",
    "- Дані можна додатково дослідити за цим [Kaggle-покликанням](https://www.kaggle.com/datasets/mlg-ulb/creditcardfraud).\n",
    "- Більше про публічні набори даних BigQuery [тут](https://cloud.google.com/bigquery/public-data)\n",
    "\n",
    "**Опис даних**\n",
    "\n",
    "Це таблиця з 284 807 транзакцій за кредитними картками, класифікованих як фрод або нормальні в колонці `Class`.  З метою захисту конфіденційності вихідні ознаки були перетворені за допомогою [аналізу головних компонент (PCA)] (https://en.wikipedia.org/wiki/Principal_component_analysis) у 28 ознак з іменами `V1, V2, ... V28` (з плаваючою комою).  Дві описові ознаки подано без перетворення за допомогою PCA:\n",
    "- `Time` (int) - секунди, що пройшли між транзакцією та найпершою транзакцією в таблиці\n",
    "- `Amount` (float) - сума транзакції\n",
    "\n",
    ">**Коротка довідка про PCA**<p>PCA - це метод навчання без вчителя: у ньому немає цільової змінної.  PCA зазвичай використовується як метод зменшення змінних/ознак.  Якщо у вас є 100 ознак, ви можете звести їх до числа p (скажімо, 10) прогнозованих ознак.  Вибір цього числа є балансом між тим, наскільки добре воно може пояснити дисперсію повного простору ознак і зменшенням кількості ознак.  Кожна спроектована ознака є ортогональною до іншої ознаки, що означає відсутність кореляції між цими новими спроектованими ознаками.</p>\n",
    "\n",
    "**Підготовка даних**\n",
    "\n",
    "Цей блокнот додає два стовпці до даних і зберігає їх у новій таблиці з суфіксом `_prepped`.  \n",
    "- `transaction_id` (рядок) унікальний ідентифікатор для рядка/транзакції\n",
    "- `splits` (рядок) розділяє транзакції на набори для `TRAIN` (80%), `VALIDATE` (10%) і `TEST` (10%)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a5046940",
   "metadata": {},
   "source": [
    "---\n",
    "## Налаштування"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a0ae60b2",
   "metadata": {},
   "source": [
    "вхідні дані:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d6f0503a-e864-4170-ade9-0ebabd14efcd",
   "metadata": {},
   "outputs": [],
   "source": [
    "project = !gcloud config get-value project\n",
    "PROJECT_ID = project[0]\n",
    "PROJECT_ID"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "56a5bc43",
   "metadata": {},
   "outputs": [],
   "source": [
    "REGION = 'us-central1'\n",
    "EXPERIMENT = '01'\n",
    "SERIES = '01'\n",
    "\n",
    "# source data\n",
    "BQ_PROJECT = PROJECT_ID\n",
    "BQ_DATASET = 'fraud'\n",
    "BQ_TABLE = 'fraud'\n",
    "\n",
    "# Data source for this series of notebooks: Described above\n",
    "BQ_SOURCE = 'bigquery-public-data.ml_datasets.ulb_fraud_detection'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e4a963be",
   "metadata": {},
   "source": [
    "пакети:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8a37b83a",
   "metadata": {},
   "outputs": [],
   "source": [
    "from google.cloud import bigquery\n",
    "from google.cloud import storage"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e24cebb6",
   "metadata": {},
   "source": [
    "клієнти:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9e515410",
   "metadata": {},
   "outputs": [],
   "source": [
    "bq = bigquery.Client(project = PROJECT_ID)\n",
    "gcs = storage.Client(project = PROJECT_ID)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a6601398",
   "metadata": {},
   "source": [
    "параметри:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1f1e2af3",
   "metadata": {},
   "outputs": [],
   "source": [
    "BUCKET = PROJECT_ID"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "504c3a89-d98f-471c-ae54-3b9157651f0b",
   "metadata": {},
   "source": [
    "---\n",
    "## Збережіть вихідні дані в GCS Storage Bucket\n",
    "Перевірте, чи існує таблиця, і створіть її, якщо не має:\n",
    "- експорт з таблиці bigquery в GCS bucket у форматі CSV\n",
    "    - посилання на таблицю міститься у змінній `BQ_SOURCE` у верхній частині цього блокнота\n",
    "- [Експорт табличних даних](https://cloud.google.com/bigquery/docs/exporting-data#python)\n",
    "- [BigQuery Python Client](https://cloud.google.com/python/docs/reference/bigquery/latest/google.cloud.bigquery.client.Client#google_cloud_bigquery_client_Client_extract_table)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6144857f-1ecc-4712-925b-4de69b6df0a1",
   "metadata": {},
   "outputs": [],
   "source": [
    "file = f\"{SERIES}/{EXPERIMENT}/data/{BQ_TABLE}.csv\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "65714181-b946-4c5c-b990-6f7ac4f2ca2a",
   "metadata": {},
   "outputs": [],
   "source": [
    "bucketDef = gcs.bucket(BUCKET)\n",
    "if storage.Blob(bucket = bucketDef, name = file).exists(gcs):\n",
    "    print(f'Файл вже створено за адресою: gs://{bucketDef.name}/{file}')\n",
    "else:\n",
    "    source = bigquery.TableReference.from_string(BQ_SOURCE)\n",
    "    extract = bq.extract_table(source = source, destination_uris = [f'gs://{bucketDef.name}/{file}'])\n",
    "    print('Експортуємо...')\n",
    "    extract.result()\n",
    "    print(f'Дані експортовані за адресою: gs://{bucketDef.name}/{file}')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "648c19f8-677e-4219-a0ff-bcfe1fd932a0",
   "metadata": {},
   "source": [
    "перелічити файли у бакеті:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ed784872-405b-4d64-a70b-4482529b5fc4",
   "metadata": {},
   "outputs": [],
   "source": [
    "list(bucketDef.list_blobs(prefix = f'{SERIES}/{EXPERIMENT}'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f40c2ea7-0649-4073-88a4-3e47af62f0c5",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(f'Ознайомитися з файлами в консолі можна тут:\\nhttps://console.cloud.google.com/storage/browser/{PROJECT_ID}/{SERIES};tab=objects&project={PROJECT_ID}')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7685eddc",
   "metadata": {},
   "source": [
    "---\n",
    "## Створення BigQuery Dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c77cab37",
   "metadata": {},
   "source": [
    "Перелічіть набори даних BigQuery в проекті:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2b49b948-5fa8-4d4e-a7d3-b2adb19362c4",
   "metadata": {},
   "outputs": [],
   "source": [
    "datasets = list(bq.list_datasets())\n",
    "for d in datasets:\n",
    "    print(d.dataset_id)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3b0b3aca",
   "metadata": {},
   "source": [
    "Створіть набір даних, якщо він відсутній:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "61001b78-418f-42a1-a4c4-d74a1f3aff9e",
   "metadata": {},
   "outputs": [],
   "source": [
    "ds = bigquery.Dataset(f\"{BQ_PROJECT}.{BQ_DATASET}\")\n",
    "ds.location = REGION\n",
    "ds.labels = {'experiment': f'{EXPERIMENT}'}\n",
    "ds = bq.create_dataset(dataset = ds, exists_ok = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "11a50638-515e-49d0-b479-25e92138ae96",
   "metadata": {},
   "source": [
    "Перелічіть набори даних BigQuery в проекті:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b39f4471-38f4-4b97-8d9c-7ee3122dbee9",
   "metadata": {},
   "outputs": [],
   "source": [
    "datasets = list(bq.list_datasets())\n",
    "for d in datasets:\n",
    "    print(d.dataset_id)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "34eeac68",
   "metadata": {},
   "source": [
    "---\n",
    "## Створення таблиці BigQuery\n",
    "- імпортуємо дані з бакету\n",
    "- [Завантаження CSV даних з хмарного сховища](https://cloud.google.com/bigquery/docs/loading-data-cloud-storage-csv)\n",
    "- [BigQuery Python Client](https://cloud.google.com/python/docs/reference/bigquery/latest/google.cloud.bigquery.client.Client#google_cloud_bigquery_client_Client_extract_table)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "86063a1e",
   "metadata": {},
   "outputs": [],
   "source": [
    "from google.cloud.exceptions import NotFound\n",
    "try:\n",
    "    table = bq.get_table(f'{BQ_PROJECT}.{BQ_DATASET}.{BQ_TABLE}')\n",
    "    if table:\n",
    "        print(f'Таблиця вже існує: {BQ_PROJECT}.{BQ_DATASET}.{BQ_TABLE}')\n",
    "except NotFound as error:\n",
    "    print(f'Створюємо таблицю ...')\n",
    "    destination = bigquery.TableReference.from_string(f\"{BQ_PROJECT}.{BQ_DATASET}.{BQ_TABLE}\")\n",
    "    job_config = bigquery.LoadJobConfig(\n",
    "        write_disposition = 'WRITE_TRUNCATE',\n",
    "        source_format = bigquery.SourceFormat.CSV,\n",
    "        autodetect = True,\n",
    "        labels = {'experiment': f'{EXPERIMENT}'}\n",
    "    )\n",
    "    job = bq.load_table_from_uri(f\"gs://{bucketDef.name}/{file}\", destination, job_config = job_config)\n",
    "    job.result()\n",
    "    print(f'Завершено створення таблиці: {BQ_PROJECT}.{BQ_DATASET}.{BQ_TABLE}')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "44285e37-6ac7-478d-9709-dd11a068a4dc",
   "metadata": {},
   "source": [
    "### Отримання та перегляд вибірки з таблиці:\n",
    "> **Примітка:** Оператор `LIMIT 5` обмежує кількість рядків, що повертаються BigQuery, до 5, але BigQuery все одно виконує повне сканування таблиці.  Якщо у вас таблиця більша за 1 ГБ і ви хочете обмежити кількість рядків, що скануються для швидкого перегляду, то ефективнішою буде заміна `LIMIT 5` на `TABLESAMPLE SYSTEM (1 PERCENT)`.  Для таблиць розміром менше 1 ГБ програма все одно повертатиме повну таблицю.  Докладніше про [тут](https://cloud.google.com/bigquery/docs/table-sampling)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "11924cf8-a040-4ee5-85ae-788cba106b56",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = f\"\"\"\n",
    "SELECT *\n",
    "FROM `{BQ_PROJECT}.{BQ_DATASET}.{BQ_TABLE}` TABLESAMPLE SYSTEM (1 PERCENT)\n",
    "#LIMIT 5\n",
    "\"\"\"\n",
    "bq.query(query = query).to_dataframe()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e85ccdce-e6a9-4735-9997-e5ae77a6f86c",
   "metadata": {},
   "source": [
    "### Перевірте цю таблицю в консолі BigQuery Console:\n",
    "- Натисніть: https://console.cloud.google.com/bigquery\n",
    "- Переконайтеся, що вибрано правильний проєкт\n",
    "- У розділі Explore розгорніть цей проект і перегляньте набір даних і таблицю"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d439a1dc-dfdd-4c82-8302-8dc1ffe44d28",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(f\"Пряме посилання на цей проект у BigQuery:\\nhttps://console.cloud.google.com/bigquery?project={PROJECT_ID}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2f133efb-8232-4b21-814f-23b815dc60f2",
   "metadata": {},
   "source": [
    "---\n",
    "## Перегляд даних у BigQuery\n",
    "Для перегляду даних можна використовувати додаткові SQL-запити.  У цьому розділі показано переміщення таблиці до фрейму даних Pandas для локального перегляду у Python:\n",
    "\n",
    "> **Примітка:** <p>Цей запит вибирає лише один стовпець.  Це означає, що BigQuery сканує менше даних, оскільки не обробляє інші стовпці.  </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f4ebf6f5-a178-4011-9ab6-fa8329c578c9",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = f\"\"\"\n",
    "SELECT Class\n",
    "FROM `{BQ_PROJECT}.{BQ_DATASET}.{BQ_TABLE}`\n",
    "\"\"\"\n",
    "df = bq.query(query = query).to_dataframe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "674f716e-8b9d-4c92-9b46-c166b869f6ae",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Class'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ba5d17e5-07c3-48fd-b682-aa471f3548f2",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Class'].value_counts(normalize=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "35b34894",
   "metadata": {},
   "source": [
    "---\n",
    "## Підготовка даних для аналізу"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4c982157",
   "metadata": {},
   "source": [
    "Створіть заздалегідь підготовлену версію даних з тестовим/тренувальним розбиттям за допомогою SQL DDL:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a50becbb-60ab-45f9-acd5-9beb5b6755f0",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = f\"\"\"\n",
    "CREATE TABLE IF NOT EXISTS `{BQ_PROJECT}.{BQ_DATASET}.{BQ_TABLE}_prepped` AS\n",
    "WITH add_id AS(SELECT *, GENERATE_UUID() transaction_id FROM `{BQ_PROJECT}.{BQ_DATASET}.{BQ_TABLE}`)\n",
    "SELECT *,\n",
    "    CASE \n",
    "        WHEN MOD(ABS(FARM_FINGERPRINT(transaction_id)),10) < 8 THEN \"TRAIN\" \n",
    "        WHEN MOD(ABS(FARM_FINGERPRINT(transaction_id)),10) < 9 THEN \"VALIDATE\"\n",
    "        ELSE \"TEST\"\n",
    "    END AS splits\n",
    "FROM add_id\n",
    "\"\"\"\n",
    "job = bq.query(query = query)\n",
    "job.result()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "77d6e2a9-ec35-477c-9d50-429326acc41d",
   "metadata": {},
   "outputs": [],
   "source": [
    "(job.ended-job.started).total_seconds()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a991ff73-a545-4eed-a709-b8292efd89ca",
   "metadata": {},
   "outputs": [],
   "source": [
    "if job.estimated_bytes_processed:\n",
    "    print(f'{job.estimated_bytes_processed/1000000} MB')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "372b2c41",
   "metadata": {},
   "source": [
    "Перегляньте розподіл між навчальною та тестовою вибірками:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4a94adb9-81d7-4710-ac50-e0d93dd5523a",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = f\"\"\"\n",
    "SELECT splits, count(*) as Count, 100*count(*) / (sum(count(*)) OVER()) as Percentage\n",
    "FROM `{BQ_PROJECT}.{BQ_DATASET}.{BQ_TABLE}_prepped`\n",
    "GROUP BY splits\n",
    "\"\"\"\n",
    "bq.query(query = query).to_dataframe()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3006ed11",
   "metadata": {},
   "source": [
    "Отримати підмножину даних до фрейму даних Pandas:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2ea70141-07db-46b0-a31e-0968befcd37b",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = f\"\"\"\n",
    "SELECT * \n",
    "FROM `{BQ_PROJECT}.{BQ_DATASET}.{BQ_TABLE}_prepped`\n",
    "LIMIT 5\n",
    "\"\"\"\n",
    "data = bq.query(query = query).to_dataframe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "326d2b3d",
   "metadata": {},
   "outputs": [],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "82d575d1",
   "metadata": {},
   "source": [
    "---\n",
    "## Видалити ресурси\n",
    "див. зошит [_Cleanup](_Cleanup.ipynb)"
   ]
  }
 ],
 "metadata": {
  "environment": {
   "kernel": "python3",
   "name": "tf2-gpu.2-11.m104",
   "type": "gcloud",
   "uri": "gcr.io/deeplearning-platform-release/tf2-gpu.2-11:m104"
  },
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}