GSP281

Огляд

SQL (Structured Query Language – мова структурованих запитів) – це стандартна мова для роботи з даними, за допомогою якої можна ставити запитання й отримувати статистику зі структурованих наборів даних. Вона широко використовується в керуванні базами даних і дає змогу виконувати такі завдання, як запис транзакцій у реляційні бази даних і аналіз величезних обсягів даних, які вимірюються петабайтами.

Ця практична робота складається з двох частин. У першій частині ви вивчите основні ключові слова запитів SQL, які ви виконуватимете в BigQuery із загальнодоступним набором даних про прокат велосипедів у Лондоні.

У другій частині ви дізнаєтесь, як експортувати підмножини набору даних про прокат велосипедів у Лондоні у файли CSV, які ви потім завантажите в Cloud SQL. Ви також навчитеся використовувати Cloud SQL, щоб створювати бази даних і таблиці й керувати ними. Наприкінці ви отримаєте практичні навички роботи з додатковими ключовими словами SQL, за допомогою яких можна редагувати дані й виконувати з ними певні операції.

Чого ви навчитесь

Під час цієї практичної роботи ви навчитеся виконувати наведені нижче дії.

• Завантажувати бази даних і таблиці в BigQuery.
• Виконувати прості запити в таблицях, щоб отримати значущі дані з наборів даних.
• Експортувати підмножину даних у файл CSV й зберігати цей файл у новому сегменті Cloud Storage.
• Створювати новий екземпляр Cloud SQL і завантажувати експортований файл CSV як нову таблицю.

Рівень попередньої підготовки

Важливо. Перед початком цієї практичної роботи вийдіть зі свого особистого або корпоративного облікового запису Gmail.

Це практична робота початкового рівня. Вона передбачає, що попередній досвід роботи з SQL мінімальний або взагалі відсутній. Знання Cloud Storage і Cloud Shell бажане, але не є обов’язковим. У цій практичній роботі ви навчитесь основам читання й написання запитів мовою SQL, які ви застосовуватимете в роботі з BigQuery й Cloud SQL.

Перш ніж виконувати цю практичну роботу, оцініть, наскільки добре ви знаєте мову SQL. Нижче наведено практичні роботи підвищеного рівня складності, де ви зможете застосувати свої знання в розширених прикладах використання.

• Дані про погоду в BigQuery
• Аналіз даних про народжуваність за допомогою Vertex AI і BigQuery

Щойно ви будете готові, прокрутіть сторінку вниз і виконайте описані кроки, щоб налаштувати середовище для практичної роботи.

Налаштування й вимоги

Перш ніж натиснути кнопку Start Lab (Почати практичну роботу)

Ознайомтеся з наведеними нижче вказівками. На виконання практичної роботи відводиться обмежений час, і її не можна призупинити. Щойно ви натиснете Start Lab (Почати практичну роботу), з’явиться таймер, який показуватиме, скільки часу для роботи з ресурсами Google Cloud у вас залишилося.

Ви зможете виконати практичну роботу в дійсному робочому хмарному середовищі (не в симуляції або демонстраційному середовищі). Для цього на час виконання практичної роботи вам надаються тимчасові облікові дані для реєстрації і входу в Google Cloud.

Щоб виконати цю практичну роботу, потрібно мати:

• стандартний веб-переглядач, наприклад Chrome (рекомендовано)

Примітка. Виконуйте практичну роботу в режимі анонімного перегляду. Так ви уникнете додаткової плати, що може стягуватися з вашого особистого облікового запису внаслідок його конфліктів з обліковим записом для навчання.

• достатню кількість часу, оскільки почавши практичну роботу, ви не зможете призупинити її

Примітка. Якщо ви маєте особистий обліковий запис або проект Google Cloud, не використовуйте їх для доступу до цієї практичної роботи. Так ви уникнете додаткових стягнень з вашого облікового запису.

Як почати виконувати практичну роботу й увійти в Google Cloud Console

1. Натисніть кнопку Start Lab (Почати практичну роботу). Якщо за практичну роботу необхідно заплатити, відкриється спливаюче вікно, де ви зможете обрати спосіб оплати. Ліворуч розміщено панель Lab Details (Відомості про практичну роботу) з такими даними:

   • кнопка Open Google Console (Відкрити Google Console);
   • час до закінчення;
   • тимчасові облікові дані, які потрібно використовувати для доступу до цієї практичної роботи;
   • інша необхідна для виконання цієї практичної роботи інформація.

2. Натисніть Open Google Console (Відкрити Google Console). Завантажаться необхідні ресурси. Потім відкриється нова вкладка зі сторінкою Sign in (Вхід).

   Порада. Упорядковуйте вкладки в окремих вікнах, розміщуючи їх поруч.

   Примітка. Якщо з’явиться вікно Choose an account (Виберіть обліковий запис), натисніть Use Another Account (Увійти в інший обліковий запис).

3. За потреби скопіюйте Username (Ім’я користувача) з панелі Lab Details (Відомості про практичну роботу) і вставте його у вікні Sign in (Вхід). Натисніть Next (Далі).

4. Скопіюйте Password (Пароль) з панелі Lab Details (Відомості про практичну роботу) і вставте його у вікні Welcome (Привітання). Натисніть Next (Далі).

   Важливо. Обов’язково використовуйте облікові дані з панелі ліворуч. Не використовуйте облікові дані Google Cloud Skills Boost. Примітка. Якщо ввійти у власний обліковий запис Google Cloud, може стягуватися додаткова плата.

5. Виконайте наведені нижче дії.

   • Прийміть Умови використання.
   • Не додавайте способи відновлення та двохетапну перевірку (оскільки це тимчасовий обліковий запис).
   • Не реєструйте безкоштовні пробні версії.

Через кілька секунд Cloud Console відкриється в новій вкладці.

Примітка. Ви можете переглянути меню зі списком продуктів і сервісів Google Cloud, натиснувши меню навігації вгорі ліворуч.

Завдання 1. Основи SQL

Бази даних і таблиці

Як зазначалося вище, SQL дає змогу отримувати інформацію зі "структурованих наборів даних". Структуровані набори даних мають чіткі правила й форматування та часто організовані як таблиці, тобто впорядковані в рядки й стовпці.

Прикладом неструктурованих даних може бути файл зображення. Неструктуровані дані непридатні для роботи з SQL, а також не можуть зберігатися в наборах даних або таблицях BigQuery (принаймні безпосередньо). Для роботи з даними зображень можна скористатися сервісом на кшталт Cloud Vision (наприклад, напряму через інтерфейс API).

Нижче наведено приклад структурованого набору даних у вигляді простої таблиці.

User (Користувач)	Price (Ціна)	Shipped (Відправлено)
Sean (Шон)	$35 (35 дол. США)	Yes (Так)
Rocky (Роккі)	$50 (50 дол. США)	No (Ні)

Наведений вище формат даних має бути вам знайомим, якщо ви вже працювали з Google Таблицями. Таблиця має стовпці User (Користувач), Price (Ціна) і Shipped (Відправлено) та два рядки, які складаються із заповнених значень стовпців.

База даних – це, по суті, набір з однієї або декількох таблиць. SQL – це інструмент керування структурованими базами даних, але досить часто (зокрема в цій практичній роботі) ви виконуватимете запити в одній або декількох об’єднаних таблицях, а не в цілих базах даних.

SELECT і FROM

Мова SQL має фонетичну природу, і перед виконанням запиту завжди потрібно визначити, на яке запитання має відповісти ваша вибірка даних (якщо ви серйозно займаєтеся цією справою).

SQL має попередньо визначені ключові слова, які використовуються для перетворення вашого запитання в псевдоанглійський синтаксис SQL, завдяки чому процесор бази даних повертає потрібну відповідь.

Найважливішими ключовими словами є SELECT і FROM.

• SELECT використовується для того, щоб указати, які поля потрібно отримати з набору даних.
• FROM використовується для того, щоб указати, з якої таблиці або таблиць ви хочете отримати дані.

Для наочності можна навести приклад. Припустімо, що у вас є наведена нижче таблиця example_table, яка містить стовпці USER (КОРИСТУВАЧ), PRICE (ЦІНА) і SHIPPED (ВІДПРАВЛЕНО).

Скажімо, ви хочете отримати дані зі стовпця USER (КОРИСТУВАЧ). Для цього ви можете виконати наведений нижче запит із використанням ключових слів SELECT і FROM.

SELECT USER FROM example_table

Якщо виконати наведену вище команду, то буде вибрано всі імена зі стовпця USER (КОРИСТУВАЧ), які містяться в таблиці example_table.

Також за допомогою ключового слова SQL SELECT можна вибрати кілька стовпців. Скажімо, ви хочете отримати дані зі стовпців USER (КОРИСТУВАЧ) і SHIPPED (ВІДПРАВЛЕНО). Для цього змініть попередній запит SELECT, додавши значення ще одного стовпця (обов’язково відокремивши його комою):

SELECT USER, SHIPPED FROM example_table

У результаті виконання цього запиту буде отримано дані стовпців USER (КОРИСТУВАЧ) і SHIPPED (ВІДПРАВЛЕНО).

Таким чином, ви вивчили два основні ключові слова SQL. Тепер розгляньмо інший приклад.

WHERE

Ключове слово WHERE – це ще одна команда SQL, яка фільтрує таблиці за певними значеннями стовпців. Скажімо, ви хочете отримати з таблиці example_table імена користувачів, чиї посилки було відправлено. Ви можете доповнити свій запит ключовим словом WHERE, як показано нижче.

SELECT USER FROM example_table WHERE SHIPPED='YES'

У результаті виконання цього запиту буде отримано дані всіх стовпців USER (КОРИСТУВАЧ), для чиїх посилок указано статус SHIPPED (ВІДПРАВЛЕНО).

Ви ознайомилися з основними ключовими словами SQL. Тепер час застосувати отримані знання, виконуючи ці типи запитів у консолі BigQuery.

Перевірте свої знання

Дайте відповіді на запитання з кількома варіантами відповіді нижче, щоб закріпити розуміння понять, які зустрічаються в практичній роботі.

Завдання 2. Огляд консолі BigQuery

Структура сховища даних BigQuery

BigQuery – це повністю кероване сховище даних, яке працює в Google Cloud і може містити петабайти інформації. Аналітики й спеціалісти з обробки даних можуть швидко надсилати запити, фільтрувати великі набори даних, узагальнювати результати й виконувати складні операції, не витрачаючи час на налаштування серверів і керування ними. Доступ до сховища BigQuery можна отримати через інструмент командного рядка (попередньо встановлений у Cloud Shell) або веб-консоль. В обох випадках ви можете надсилати запити й керувати даними, розміщеними в проектах Google Cloud.

Під час цієї практичної роботи ви використовуватимете для виконання запитів SQL веб-консоль.

Як відкрити консоль BigQuery

1. У Google Cloud Console виберіть меню навігації > BigQuery.

Відкриється повідомлення Welcome to BigQuery in the Cloud Console (Вітаємо в BigQuery в Cloud Console). Це повідомлення містить посилання на короткий посібник і примітки до випуску.

2. Натисніть Done (Готово).

Відкриється консоль BigQuery.

Зверніть увагу на деякі важливі елементи інтерфейсу. Праворуч на консолі можна знайти редактор запитів. Це місце, де ви пишете й виконуєте команди SQL, як було показано в прикладах вище. Нижче розташовується Query history (Історія запитів) – це список запитів, які ви виконували раніше.

На панелі ліворуч можна знайти меню навігації. Воно містить історію запитів, збережені запити, історію завдань, а також вкладку Explorer (Провідник).

На найвищому рівні ресурсів на вкладці Explorer (Провідник) відображаються проекти Google Cloud, а саме тимчасові проекти Google Cloud, у які ви входите і які використовуєте в кожній практичній роботі Google Cloud Skills Boost. Як ви бачите у своїй консолі й на останньому знімку екрана, на вкладці Explorer (Провідник) відображається лише ваш проект. Якщо ви натиснете стрілку поруч із назвою проекту, нічого не відкриється.

Це тому, що ваш проект не містить наборів даних або таблиць, тобто нічого, на що можна було б надіслати запит. Раніше ви дізналися, що набори даних містять таблиці. Коли ви додаєте дані в проект, пам’ятайте, що в BigQuery проекти містять набори даних, а набори даних – таблиці. Отже, ви познайомилися зі структурою "проект > набір даних > таблиця" й інтерфейсом консолі. Тепер можна завантажити деякі дані для подальшого надсилання запитів.

Завантаження даних для запитів

У цьому розділі ви додасте деякі загальнодоступні дані у свій проект, щоб потренуватися виконувати команди SQL у BigQuery.

1. Натисніть + ADD (ДОДАТИ).

2. Виберіть Star a project by name (Позначити проект зірочкою за назвою).

3. Введіть таку назву проекту: bigquery-public-data.

4. Натисніть STAR (ПОЗНАЧИТИ ЗІРОЧКОЮ).

Важливо зауважити, що на цій новій вкладці ви продовжуєте працювати над проектом своєї практичної роботи. Ви отримали загальнодоступний проект із наборами даних і таблицями в BigQuery для аналізу, але ви не переключилися на цей проект. Усі ваші завдання й сервіси, як і раніше, прив’язано до вашого облікового запису Google Cloud Skills Boost. Ви можете переконатися в цьому, переглянувши поле Project (Проект) у верхній частині консолі.

5. Тепер у вас є доступ до наведених нижче даних.

• Проект Google Cloud → bigquery-public-data
• Набір даних → london_bicycles

6. Натисніть набір даних london bicycles (велосипеди в Лондоні), щоб відкрити пов’язані з ним таблиці.

• Таблиця → cycle_hire
• Таблиця → cycle_stations

У цій практичній роботі ви використовуватимете дані таблиці cycle_hire. Відкрийте таблицю cycle_hire, а потім – вкладку Preview (Попередній перегляд). Сторінка матиме приблизно такий вигляд:

Перегляньте стовпці й значення в рядках. Тепер можна виконувати запити SQL у таблиці cycle_hire.

Запити з ключовими словами SELECT, FROM і WHERE в BigQuery

Тепер у вас є базове розуміння ключових слів для запитів SQL і структури сховища даних BigQuery, а також деякі дані для роботи. Запустіть певні команди SQL за допомогою цього сервісу.

Якщо ви подивитеся в правий нижній кут консолі, то побачите, що таблиця містить 24 369 201 рядок даних – це кількість окремих поїздок на велосипеді, здійснених у Лондоні в період із 2015 по 2017 рік (і це доволі велике число!).

Тепер зверніть увагу на ключ у сьомому стовпці: end_station_name. Це кінцева станція поїздок на велосипеді. Перш ніж продовжувати, виконайте простий запит, щоб виокремити стовпець end_station_name.

1. Скопіюйте наведену нижче команду й вставте її в редактор запитів.

SELECT end_station_name FROM `bigquery-public-data.london_bicycles.cycle_hire`;

2. Тепер натисніть Run (Виконати).

Через приблизно 20 секунд ви маєте отримати 24 369 201 рядок тільки із запитаним стовпцем end_station_name.

Тепер можна дізнатися, скільки поїздок на велосипеді тривали 20 хвилин або довше.

3. Видаліть запит із редактора, а потім виконайте наведений нижче запит із ключовим словом WHERE.

SELECT * FROM `bigquery-public-data.london_bicycles.cycle_hire` WHERE duration>=1200;

Виконання цього запиту може зайняти приблизно хвилину.

Ключове слово із зірочкою SELECT * повертає значення всіх стовпців із таблиці. Тривалість вимірюється в секундах, тому використовується значення 1200 (60 * 20).

Якщо ви подивитесь у правий нижній кут, то побачите, що запит повернув 7 334 890 рядків. Вирахувавши частку від загальної кількості (7 334 890/24 369 201), ми дізнаємося, що приблизно 30% поїздок на велосипеді в Лондоні тривали 20 хвилин та більше (і це досить довго!).

Перевірте свої знання

Дайте відповіді на запитання з кількома варіантами відповіді нижче, щоб закріпити розуміння понять, які зустрічаються в практичній роботі.

Завдання 3. Інші ключові слова SQL: GROUP BY, COUNT, AS і ORDER BY

GROUP BY

Ключове слово GROUP BY об’єднує рядки списку результатів, які мають спільні критерії (наприклад, значення стовпця), і повертає всі унікальні записи, знайдені за цими критеріями.

Це ключове слово допомагає знайти в таблицях інформацію категоріального характеру.

1. Щоб краще зрозуміти, що робить це ключове слово, видаліть запит із редактора, а потім скопіюйте й вставте наведену нижче команду.

SELECT start_station_name FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name;

2. Натисніть Run (Виконати).

У результаті ви отримаєте список унікальних (неповторюваних) значень стовпців.

Запит без ключового слова GROUP BY повернув би весь список із 24 369 201 рядка. GROUP BY дає змогу отримати унікальні значення стовпців, знайдені в таблиці. Якщо ви подивитесь у правий нижній кут консолі, то побачите, що запит повернув 880 рядків. Це означає, що в Лондоні є 880 різних початкових станцій прокату велосипедів.

COUNT

Функція COUNT() повертає кількість рядків, які відповідають однаковим критеріям (наприклад, значенням стовпця). Вона може бути корисною в поєднанні з ключовим словом GROUP BY.

Додайте функцію COUNT у попередній запит, щоб дізнатися, скільки поїздок починається з кожної станції.

• Видаліть запит із редактора, потім скопіюйте й вставте наведену нижче команду та натисніть Run (Виконати).

SELECT start_station_name, COUNT(*) FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name;

У результаті ви отримаєте кількість поїздок, які починаються з кожної станції.

AS

У SQL також є ключове слово AS, яке створює псевдонім таблиці або стовпця. Псевдонім – це нова назва поверненого стовпця або таблиці, яку вказує ключове слово AS.

1. Додайте ключове слово AS в останній запит, який ви виконували, щоб побачити це в дії. Видаліть запит із редактора, а потім скопіюйте й вставте наведену нижче команду.

SELECT start_station_name, COUNT(*) AS num_starts FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name;

2. Натисніть Run (Виконати).

У результаті назву правого стовпця було змінено з COUNT(*) на num_starts.

Отже, стовпець COUNT(*) у повернутій таблиці тепер має псевдонім num_starts. Це корисне ключове слово, особливо якщо ви маєте справу з великими наборами даних, адже неоднозначні назви таблиць або стовпців зустрічаються частіше, ніж може здатися на перший погляд.

ORDER BY

Ключове слово ORDER BY сортує дані, отримані під час виконання запиту, за зростанням або спаданням на основі заданого критерію або значення стовпця. Додайте це ключове слово в попередній запит, щоб:

• повернути таблицю, яка містить кількість поїздок із кожної початкової станції, упорядковану за алфавітом;
• повернути таблицю, яка містить кількість поїздок із кожної початкової станції, упорядковану за числом від найменшого до найбільшого;
• повернути таблицю, яка містить кількість поїздок із кожної початкової станції, упорядковану за числом від найбільшого до найменшого.

Кожна з таких команд представляє окремий запит. Для їх запуску виконайте наведені нижче дії.

1. Очистьте редактор запитів.
2. Скопіюйте й вставте команду в редактор запитів.
3. Натисніть Run (Виконати). Перегляньте результати.

SELECT start_station_name, COUNT(*) AS num FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name ORDER BY start_station_name; SELECT start_station_name, COUNT(*) AS num FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name ORDER BY num; SELECT start_station_name, COUNT(*) AS num FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name ORDER BY num DESC;

У результаті останнього запиту ви отримаєте список початкових станцій за кількістю стартів із цього місця.

Ви бачите, що пункт "Belgrove Street, King’s Cross" має найбільшу кількість стартів. Однак якщо вирахувати частку від загальної кількості (234 458/24 369 201), то виявиться, що із цієї станції починається <1% поїздок.

Перевірте свої знання

Дайте відповіді на запитання з кількома варіантами відповіді нижче, щоб закріпити розуміння понять, які зустрічаються в практичній роботі.

Завдання 4. Робота із сервісом Cloud SQL

Експорт запитів у файли CSV

Cloud SQL – це повністю керований сервіс баз даних, який дає змогу легко налаштовувати, підтримувати й адмініструвати реляційні бази даних PostgreSQL і MySQL та керувати ними в хмарі. У Cloud SQL підтримуються два формати даних: файли дампу (.sql) або CSV (.csv). Ви дізнаєтесь, як експортувати підмножини таблиці cycle_hire у файли CSV й завантажити їх у Cloud Storage як проміжне місце.

У консолі BigQuery Console ви мали виконати останньою таку команду:

SELECT start_station_name, COUNT(*) AS num FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name ORDER BY num DESC;

1. У розділі Query Results (Результати запиту) натисніть SAVE RESULTS (ЗБЕРЕГТИ РЕЗУЛЬТАТИ) > CSV(local file). Таким чином буде ініційовано завантаження запиту у вигляді файлу CSV. Запам’ятайте розташування й назву завантаженого файлу, оскільки він вам скоро знадобиться.

2. Очистьте редактор запитів, а потім скопіюйте й запустіть наведену нижче команду.

SELECT end_station_name, COUNT(*) AS num FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY end_station_name ORDER BY num DESC;

У результаті ви отримаєте таблицю, яка містить кількість поїздок на велосипеді, що завершилися на кожній кінцевій станції, упорядковану за числом від найбільшого до найменшого.

3. У розділі Query Results (Результати запиту) натисніть SAVE RESULTS (ЗБЕРЕГТИ РЕЗУЛЬТАТИ) > CSV(local file). Таким чином буде ініційовано завантаження запиту у вигляді файлу CSV. Запам’ятайте розташування й назву завантаженого файлу, оскільки він знадобиться вам у наступному розділі.

Завантаження файлів CSV в Cloud Storage

1. Перейдіть у Cloud Console, де буде створено сховище, куди ви зможете завантажити щойно збережені файли.

2. Виберіть меню навігації > Cloud Storage > Buckets (Сегменти), а потім натисніть CREATE BUCKET (СТВОРИТИ СЕГМЕНТ).

Примітка. Якщо з’явиться запит щодо незбережених даних, натисніть LEAVE (ВИЙТИ).

3. Введіть унікальну назву для сегмента, залиште всі інші налаштування за умовчанням і натисніть Create (Створити).

4. Якщо з’явиться вікно Public access will be prevented (Загальний доступ буде заборонено), натисніть Confirm (Підтвердити).

Перевірка виконаного завдання

Щоб підтвердити виконання завдання, натисніть Підтвердити виконання нижче. Якщо сегмент створено правильно, з’явиться оцінка.

Створення сегмента Cloud Storage.

Тепер ви маєте бути в Cloud Console, де відображається щойно створений сегмент Cloud Storage.

1. Натисніть UPLOAD FILES (ЗАВАНТАЖИТИ ФАЙЛИ) і виберіть файл CSV, який містить дані start_station_name.

2. Натисніть Open (Відкрити). Повторіть ці дії з даними end_station_name.

3. Змініть назву файлу start_station_name, натиснувши три крапки збоку файлу, а потім – Rename (Перейменувати). Нова назва файлу має бути така: start_station_data.csv.

4. Змініть назву файлу end_station_name, натиснувши три крапки збоку файлу, а потім – Rename (Перейменувати). Нова назва файлу має бути така: end_station_data.csv.

Тепер файли start_station_data.csv й end_station_data.csv мають відображатися в списку Objects (Об’єкти) на сторінці Bucket details (Відомості про сегмент).

Перевірка виконаного завдання

Щоб підтвердити виконання завдання, натисніть Підтвердити виконання. Якщо об’єкти файлів CSV завантажено правильно, з’явиться оцінка.

Завантаження файлів CSV в Cloud Storage.

Завдання 5. Створення екземпляра Cloud SQL

У консолі виберіть меню навігації > SQL.

1. Натисніть CREATE INSTANCE (СТВОРИТИ ЕКЗЕМПЛЯР) > Choose MySQL (Виберіть MySQL).

2. Укажіть такий ідентифікатор екземпляра: my-demo.

3. У полі Password (Пароль) введіть надійний пароль (запам’ятайте його!).

4. Виберіть таку версію бази даних: MySQL 8.

5. У Choose a Cloud SQL edition (Виберіть версію Cloud SQL) натисніть Enterprise.

6. Для параметра Preset (Набір налаштувань) виберіть Development (Розробка) (4 віртуальні центральні процесори, 16 ГБ оперативної пам’яті, сховище обсягом 100 ГБ, одна зона).

Попередження. Якщо ви виберете набір налаштувань, більший ніж Development (Розробка), ваш проект отримає відповідну позначку, а практичну роботу буде завершено.

7. У полі Multi zones (Highly available) (Кілька зон (Високий рівень доступності)) укажіть:

8. Натисніть CREATE INSTANCE (СТВОРИТИ ЕКЗЕМПЛЯР).

Примітка. Створення екземпляра може тривати кілька хвилин. Після цього ви побачите зелений прапорець поруч із назвою екземпляра на сторінці екземплярів SQL.

9. Натисніть екземпляр Cloud SQL. Відкриється сторінка SQL Overview (Огляд SQL).

Перевірка виконаного завдання

Щоб підтвердити виконання завдання, натисніть Підтвердити виконання нижче. Якщо екземпляр Cloud SQL налаштовано правильно, з’явиться оцінка.

Створення екземпляра Cloud SQL.

Завдання 6. Нові запити в Cloud SQL

Ключове слово CREATE (бази даних і таблиці)

Тепер у вас є активний екземпляр Cloud SQL, і ви можете створити базу даних усередині нього за допомогою командного рядка Cloud Shell.

1. Відкрийте Cloud Shell, натиснувши значок у верхньому правому куті консолі.

2. Виконайте наведену нижче команду, щоб установити ідентифікатор вашого проекту як змінну середовища.

export PROJECT_ID=$(gcloud config get-value project) gcloud config set project $PROJECT_ID

Створення бази даних у Cloud Shell

1. Виконайте наведену нижче команду в Cloud Shell, щоб налаштувати авторизацію без відкриття веб-переглядача.

gcloud auth login --no-launch-browser

Ви отримаєте посилання, яке потрібно відкрити у веб-переглядачі (у тому самому, де ви входили в обліковий запис Qwiklabs). Після входу ви отримаєте код підтвердження, який потрібно скопіювати й вставити в Cloud Shell.

2. Виконайте наведену нижче команду, щоб підключитися до екземпляра SQL. За потреби замініть my-demo іншою назвою, яку ви використовували для свого екземпляра.

gcloud sql connect my-demo --user=root --quiet Примітка. Підключення до екземпляра може тривати кілька хвилин. Якщо з’являється повідомлення Operation failed because another operation was already in progress (Не вдалося виконати операцію, оскільки вже виконується інша операція), то потрібно дочекатися створення екземпляра SQL, а потім спробувати підключитися ще раз.

3. Якщо з’явиться відповідний запит, введіть кореневий пароль, який ви встановили для екземпляра.

Вивід матиме приблизно такий вигляд:

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 494 Server version: 8.0.31-google (Google) Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>

Екземпляр Cloud SQL надається з попередньо налаштованими базами даних, але ви створите власну базу для зберігання даних про прокат велосипедів у Лондоні.

4. Виконайте наведену нижче команду на сервері MySQL, щоб створити базу даних під назвою bike.

CREATE DATABASE bike;

Вивід має бути таким:

Query OK, 1 row affected (0.05 sec) mysql>

Перевірка виконаного завдання

Щоб підтвердити виконання завдання, натисніть Підтвердити виконання. Якщо базу даних в екземплярі Cloud SQL створено правильно, з’явиться оцінка.

Створення бази даних.

Створення таблиці в Cloud Shell

1. Виконайте наведену нижче команду, щоб створити таблицю всередині бази даних "bike".

USE bike; CREATE TABLE london1 (start_station_name VARCHAR(255), num INT);

Ця інструкція містить ключове слово CREATE, але цього разу зі словом TABLE, щоб указати, що потрібно створити таблицю, а не базу даних. Ключове слово USE указує базу даних, до якої ви хочете підключитися. Тепер у вас є таблиця з назвою "london1", яка містить два стовпці: "start_station_name" і "num". VARCHAR(255) указує, що стовпець змінної довжини може містити до 255 символів, а INT – тип стовпця, у якому значення має бути цілим числом.

2. Виконайте наведену нижче команду, щоб створити іншу таблицю з назвою "london2".

USE bike; CREATE TABLE london2 (end_station_name VARCHAR(255), num INT);

3. Переконайтеся, що було створено порожні таблиці. Виконайте наведені нижче команди у відповідь на запит сервера MySQL.

SELECT * FROM london1; SELECT * FROM london2;

Для обох команд ви маєте отримати такий вивід:

Empty set (0.04 sec)

У консолі відображається "empty set" ("порожній набір"), тому що ви ще не завантажили дані.

Завантаження файлів CSV в таблиці

Поверніться в консоль Cloud SQL. Тепер потрібно завантажити файли CSV start_station_name і end_station_name у нові таблиці "london1" та "london2".

1. На сторінці екземпляра Cloud SQL натисніть IMPORT (ІМПОРТУВАТИ).
2. У полі файлу Cloud Storage виберіть Browse (Переглянути), а потім натисніть стрілку поруч із назвою сегмента й start_station_data.csv. Натисніть Select (Вибрати).
3. Виберіть формат файлу CSV.
4. Виберіть базу даних bike і введіть london1 для таблиці.
5. Натисніть Import (Імпортувати).

Виконайте ті самі дії для іншого файлу CSV.

1. На сторінці екземпляра Cloud SQL натисніть IMPORT (ІМПОРТУВАТИ).
2. У полі файлу Cloud Storage виберіть Browse (Переглянути), а потім натисніть стрілку поруч із назвою сегмента й end_station_data.csv. Натисніть Select (Вибрати).
3. Виберіть формат файлу CSV.
4. Виберіть базу даних "bike" і в полі таблиці введіть london2.
5. Натисніть Import (Імпортувати).

Обидва файли CSV мають завантажитися в таблиці в базі даних bike.

1. Поверніться в сеанс Cloud Shell і виконайте наведену нижче команду у відповідь на запит сервера MySQL, щоб перевірити вміст таблиці "london1".

SELECT * FROM london1;

Ви повинні отримати 955 рядків, по одному для кожної унікальної назви станції.

2. Виконайте наведену нижче команду, щоб переконатися, що таблицю "london2" було заповнено.

SELECT * FROM london2;

Ви повинні отримати 959 рядків, по одному для кожної унікальної назви станції.

Ключове слово DELETE

Пропонуємо ознайомитися з іще кількома ключовими словами SQL, які допомагають у керуванні даними. Перше таке ключове слово – DELETE.

• Виконайте наведені нижче команди в сеансі MySQL, щоб видалити перший рядок таблиць "london1" і "london2".

DELETE FROM london1 WHERE num=0; DELETE FROM london2 WHERE num=0;

Після виконання обох команд ви маєте отримати такий вивід:

Query OK, 1 row affected (0.04 sec)

Видалені рядки були заголовками стовпців у файлах CSV. Ключове слово DELETE видаляє не перший рядок файлу сам по собі, а всі рядки таблиці, де назва стовпця (у цьому випадку "num") містить указане значення (у цьому випадку "0"). Якщо ви виконаєте запити SELECT * FROM london1; і SELECT * FROM london2; та прокрутите таблицю вгору, то побачите, що цих рядків більше немає.

Ключове слово INSERT INTO

Ви також можете вставляти значення в таблиці за допомогою ключового слова INSERT INTO.

• Виконайте наведену нижче команду, щоб вставити новий рядок у таблицю "london1", при цьому встановивши для start_station_name значення "test destination", а для num значення "1".

INSERT INTO london1 (start_station_name, num) VALUES ("test destination", 1);

Для ключового слова INSERT INTO потрібна таблиця ("london1"), тоді буде створено новий рядок зі стовпцями, визначеними умовами в перших дужках (у цьому випадку "start_station_name" і "num"). Усе, що стоїть після оператора "VALUES" ("ЗНАЧЕННЯ"), буде вставлено в новому рядку.

Вивід має бути таким:

Query OK, 1 row affected (0.05 sec)

Якщо ви виконаєте запит SELECT * FROM london1;, то побачите новий рядок, доданий у нижній частині таблиці "london1".

Ключове слово UNION

Останнє ключове слово SQL, яке ви вивчите, – це UNION. Воно об’єднує принаймні два запити SELECT і виводить для них список результатів. UNION використовується для об’єднання підмножин даних таблиць "london1" і "london2".

Такий ланцюговий запит витягує певні дані з обох таблиць і об’єднує їх за допомогою оператора UNION.

• Виконайте наведену нижче команду у відповідь на запит сервера MySQL.

SELECT start_station_name AS top_stations, num FROM london1 WHERE num>100000 UNION SELECT end_station_name, num FROM london2 WHERE num>100000 ORDER BY top_stations DESC;

Перший запит SELECT вибирає два стовпці з таблиці "london1" і створює псевдонім для стовпця "start_station_name", якому присвоюється значення "top_stations". У ньому використовується ключове слово WHERE, щоб отримати лише назви станцій, з яких стартують понад 100 000 велосипедів.

Другий запит SELECT вибирає два стовпці з таблиці "london2" і використовує ключове слово WHERE, щоб отримати лише назви станцій, де закінчують поїздку понад 100 000 велосипедів.

Ключове слово UNION між ними поєднує результати цих запитів, комбінуючи дані таблиць "london2" й "london1". Оскільки "london1" об’єднується з "london2", значення стовпців, яким надається пріоритет, є "top_stations" і "num".

Ключове слово ORDER BY упорядкує кінцеву об’єднану таблицю за значенням стовпця "top_stations" в алфавітному порядку за спаданням.

Вивід має бути таким:

Отже, як ви можете бачити, 13 із 14 станцій посідають перші місця за кількістю як стартів, так і завершень поїздок. За допомогою базових ключових слів SQL ви зробили запит у великий набір даних, у результаті чого отримали точки даних і відповіді на конкретні запитання.

Вітаємо!

Під час цієї практичної роботи ви вивчили основи мови SQL, а також навчилися застосовувати ключові слова та виконувати запити в BigQuery й CloudSQL. Ви розглянули ключові поняття, як-от проекти, бази даних і таблиці. Ви потренувалися використовувати ключові слова, які дають змогу редагувати дані й керувати ними. Ви дізнались, як завантажувати набори даних у BigQuery й спробували виконувати запити в таблицях. Ви навчилися створювати екземпляри в Cloud SQL і переносити підмножини даних у таблиці, які містяться в базах даних. Ви створили ланцюжок і виконали запити в Cloud SQL, щоб зробити висновки щодо початкових і кінцевих станцій прокату велосипедів у Лондоні.

Пройдіть квест

Цю практичну роботу можна виконувати у власному темпі, і вона входить до квестів Data Science on Google Cloud, Cloud SQL, BigQuery Basics for Data Analysts, NCAA® March Madness®: Bracketology with Google Cloud, Cloud Engineering, Data Catalog Fundamentals, Applying BQML's Classification, Regression, and Demand Forecasting for Retail Applications. Квест – це низка пов’язаних практичних робіт, що разом складають план навчання. Виконавши всю серію, ви отримаєте значок як відзнаку за досягнення. Значок можна зробити видимим для інших і додати у своє онлайн-резюме або в обліковий запис у соціальній мережі. Зареєструйтеся на відповідний квест, і цю практичну роботу в ньому буде відразу зараховано. Усі доступні квести можна переглянути в каталозі Google Cloud Skills Boost.

Наступні кроки/Докладніше

Продовжуйте вивчати й застосовувати на практиці Cloud SQL і BigQuery за допомогою наведених нижче практичних робіт Google Cloud Skill Boost.

• Дані про погоду в BigQuery
• Вивчення даних NCAA за допомогою BigQuery
• Завантаження даних у Google Cloud SQL
• Cloud SQL із Terraform

Якщо вас цікавить наука про дані, більше інформації ви знайдете в книзі Data Science on the Google Cloud Platform (Наука про дані з Google Cloud Platform, 2-й випуск, видавництво O’Reilly Media, Inc).

Навчання й сертифікація Google Cloud

…допомагають ефективно використовувати технології Google Cloud. Наші курси передбачають опанування технічних навичок, а також ознайомлення з рекомендаціями, що допоможуть вам швидко зорієнтуватися й вивчити матеріал. Ми пропонуємо курси різних рівнів – від базового до високого. Ви можете вибрати формат навчання (за запитом, онлайн або офлайн) відповідно до власного розкладу. Пройшовши сертифікацію, ви перевірите й підтвердите свої навички та досвід роботи з технологіями Google Cloud.

Посібник востаннє оновлено 16 січня 2024 року

Практичну роботу востаннє протестовано 6 жовтня 2023 року

© Google LLC 2024. Усі права захищено. Назва та логотип Google є торговельними марками Google LLC. Усі інші назви компаній і продуктів можуть бути торговельними марками відповідних компаній, з якими вони пов’язані.