Лабораторна робота №1

Хмарні технології обробки даних

Найближчі кілька лабораторних робіт ми будемо працювати з даними, які представляють сервіс з доставки продуктів. Тому дуже важливо розібратися у них. Всього буде 6 таблиць:

Важливо

Посилання на дані

1. Обліковий запис Google Cloud Platform

  • Створіть проєкт в Google Cloud Platform (GCP)1, наприклад cloud-course-IAV-21.

2. Дані

  • Створіть набір даних (dataset) в BigQuery, наприклад food_delivery.
  • Завантажте дані в таблиці відповідно до назви файлів. Використайте наступні схеми для створення таблиць:
    • courier_actions:
      • courier_id - INT64, id кур’єра
      • order_id - INT64, id замовлення
      • action - STRING, дія кур’єра із замовленням
      • time - DATETIME, час виконання дії
    • couriers:
      • courier_id - INT64, id кур’єра
      • bitrh_day - DATE, дата народження
      • sex - STRING, стать
    • orders2:
      • order_id - INT64, id замовлення
      • creation_time - DATETIME, час створення замовлення
      • product_ids - INT64, REPEATED, id продуктів у замовленні
    • products:
      • product_id - INT64, id продукту
      • name - STRING, назва продукту
      • price - FLOAT64, ціна продукту
    • user_actions:
      • user_id - INT64, id користувача
      • order_id - INT64, id замовлення
      • action - STRING, дія користувача із замовленням
      • time - DATETIME, час виконання дії
    • users:
      • user_id - INT64, id користувача
      • bitrh_day - DATE, дата народження
      • sex - STRING, стать

Для кращого розуміння наводжу зрізи даних з кожної таблиці.

  • courier_actions:
courier_id order_id action time
2766 59342 deliver_order 2022-09-08 23:18:00
1716 52265 deliver_order 2022-09-07 19:22:00
154 23521 accept_order 2022-09-02 06:34:00
1713 19462 deliver_order 2022-09-01 07:03:00
1038 15831 deliver_order 2022-08-31 06:56:00
  • couriers:
courier_id birth_date sex
2163 1992-09-21 male
37 1991-05-02 female
18 1995-01-05 female
2314 1990-12-07 male
166 1989-12-08 female
  • orders:
order_id creation_time product_ids
6063 2022-08-28 10:44:00 ['31', '79', '14', '32', '10']
44689 2022-09-05 21:29:00 ['80', '77', '76', '83']
18888 2022-09-01 00:36:00 ['82', '3', '56', '63']
26217 2022-09-02 19:06:00 ['57', '6', '40']
9526 2022-08-29 12:04:00 ['1', '30']
  • products:
product_id name price
41 carbonated water 80
57 pork 450
68 dried fish 116
56 sausages 150
35 lemonade 90
  • user_actions:
user_id order_id action time
10792 24277 create_order 2022-09-02 10:40:00
1382 6061 create_order 2022-08-28 10:42:00
5092 55299 create_order 2022-09-08 08:22:00
10405 23282 create_order 2022-09-02 04:15:00
15622 39134 create_order 2022-09-04 20:18:00
  • users:
user_id birth_date sex
3366 1993-08-23 female
20802 1993-09-27 male
20784 1993-03-28 female
12504 1993-10-20 female
6255 1993-11-27 female

3. Запити

Створіть запити для вирішення наступних завдань:

  1. Який товар має найдовшу назву?
  2. Які користувачі зробили найбільше замовлень?
  3. Підвищить ціну на 5% тільки на товари, вартість яких перевищує 100 одиниць. Ціну решти товарів залиште без змін. Також не підвищуйте ціну на ікру (caviar), яка й так коштує 800 одиниць. Виведіть id та найменування всіх товарів, їх стару та нову ціну.
  4. Розрахуйте вік наймолодшого3 кур’єра з таблиці couriers.
  5. Використовуючи функцію DATE_TRUNC, порахуйте, скільки замовлень було зроблено та скільки було скасовано кожного місяця. Розрахунки проводьте за таблицею user_actions.
  6. За таблицями orders та courier_actions визначте id десяти замовлень, які доставляли найдовше.
Примітка

Документація до операторів та функцій BigQuery: https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators

Важливо
  • Дедлайн здачі роботи: 26.03.2024

Примітки

  1. Назва проєкту повинна бути унікальною в межах GCP.↩︎

  2. Зверніть увагу, що для завантаження даних в таблицю products використовується JSON-файл. Це пояснюється тим, що в таблиці є поле product_ids, яке містить масив значень (CSV не підтримує масиви).↩︎

  3. Вік розраховується на момент виконання запиту.↩︎