Гараж
Інтро в Google BigQuery: як робити перші запити
Можна ставити таск програмістам і чекати, чекати, чекати. А можна взяти ситуацію базу даних у власні руки, написати запит та самостійно все візуалізувати. Вчимося з Projector створювати перші запити у Google BigQuery мовою SQL: повільно, покроково, з роз'ясненням подробиць.
Знайомство з інтерфейсом Google BigQuery
Навіть якщо ви ще не користуєтеся BigQuery, мусите знати її переваги: безкоштовна, багатофункціональна, дозволяє експортувати дані з нового Google Analytics 4. Освоївши BigQuery, за потреби можна швидко розібратися і з багатьма іншими базами даних (MySQL, PostgreSQL, Amazon Redshift тощо). А ще ви отримаєте користь і без технічного заглиблення — залишимо його програмістам.
Для реєстрації вам потрібен акаунт у Google. Далі перейдіть до консолі та створіть новий проєкт. Скоріш за все, на панелі зліва ви нічого не побачите — саме тут далі будуть висвітлюватися бази даних, з якими ви працюватимете.
Попрактикуватися на старті можна з публічними датасетами — базами даних у відкритому доступі. Для цього натисніть на консолі зліва Add Data та з випадаючого списку оберіть Explore public datasets. Перед вами з'явиться Marketplace публічних датасетів з різних індустрій та з різною завантаженістю. Початківцям можна обрати щось просте. Наприклад, covid19_open_data_eu — актуальна та близька для всіх тема.
Для реєстрації вам потрібен акаунт у Google. Далі перейдіть до консолі та створіть новий проєкт. Скоріш за все, на панелі зліва ви нічого не побачите — саме тут далі будуть висвітлюватися бази даних, з якими ви працюватимете.
Попрактикуватися на старті можна з публічними датасетами — базами даних у відкритому доступі. Для цього натисніть на консолі зліва Add Data та з випадаючого списку оберіть Explore public datasets. Перед вами з'явиться Marketplace публічних датасетів з різних індустрій та з різною завантаженістю. Початківцям можна обрати щось просте. Наприклад, covid19_open_data_eu — актуальна та близька для всіх тема.
Отже, ми обрали базу даних. У ній можуть зберігатись багато датасетів, а кожен із них має певну кількість таблиць. Деякі датасети також містять структуру View, що дозволяє зробити запит поверх таблиці.
У нашому кейсі в переліку датасетів обираємо саме таблицю, вона позначена іконкою калькулятора. Зверніть увагу на вкладки:
У нашому кейсі в переліку датасетів обираємо саме таблицю, вона позначена іконкою калькулятора. Зверніть увагу на вкладки:
1
Schema — тут викладені всі поля таблиці; кожне має режим Mode (часто тут вказано NULLABLE, тобто не порожнє, а таке, що не містить значення); також у кожного поля є тип даних: від нього залежить, як ми з ними будемо працюватимемо (наприклад, INTEGER — натуральні числа, FLOAT — числа з комами, GEOGRAPHY — геодані тощо);
2
Details — тут усе зрозуміло; можемо побачити дату створення і останнього оновлення таблиці, локацію, розмір тощо; також отримати лінк на список доступних джерел на GitHub;
3
Preview — тут і зібрано дані, які трохи нагадують Microsoft Excel з рядками і стовпчиками; деякі з останніх незаповнені, адже в різних ситуаціях ми вносимо різні дані.
Перед стартом роботи з таблицею добре ознайомтеся з полями у Schema та з даними у Preview.
Як робити перші запити
Найпростіша інформація, яку можна отримати з нашого датасету, це кількість хворих на коронавірус у Євросоюзі. Щоб почати роботу з даними, можемо натиснути Query Table — її простого і зрозумілого інтерфейсу всередині BigQuery цілком достатньо, а сама система вже прописала базову конструкцію запиту. Пізніше можна перейти в окрему програму IDE (тобто integrated development environment).
Отже, створюємо запит.
Отже, створюємо запит.
1
У нашому кейсі запити починатимуться з SELECT, адже ми використовуємо дані тільки для читання. Далі в рядку ми бачимо джерело. Ієрархія виглядає ось так: база даних — датасет — таблиця в датасеті. Наприкінці ви побачите LIMIT 1000 — це обмеження кількості рядків, яке убезпечить від задовгих запитів, що банально уповільнюють браузер; його можна не прибирати.
2
Окремі знаки позначають звертання до будь-чого. Наприклад, зірочка — вибір цілої таблиці; її використання має сенс хіба, якщо у вас з-десяток рядків. Замість ней пропишемо, що саме хочемо вибрати з бази даних.
3
Ми визначили, що будемо дізнаватися кількість хворих на коронавірус в ЄС. Для цього після SELECT вказуємо Field name; у нашому випадку — date та new confirmed. Натискаємо RUN і вже за цим запитом отримуємо вибірку за вказаними полями.
4
Як відсортувати дані? Наприклад, у зворотньому порядку. Для цього перед LIMIT додаємо функцію ORDER BY і вказуємо поле, за яким хочемо сортувати — date i desc. У результаті можемо отримати багато повторюваних значень, але їх усіх теж можна згрупувати — за аналогією до зведеної таблиці в Excel. Для цього перед ORDER (і саме тут — порядок у SQL дуже важливий) додаємо функцію GROUP BY і вказуємо date.
5
Тут система може видати вам помилку, адже у рядку з date ми залишили new confirmed. Тому є сенс сумувати повторювані значення даних. Для цього можна застосувати агрецію. Якщо до поля new confirmed візьмемо SUM, отримаємо 1 унікальний рядок для кожної дати, а всі повторювані значення будуть просто додані.
Зверніть увагу: у більшості запитів усі поля будемо, скоріш за все, групувати, а з числовими значеннями проводити якусь агрегацію (сума, середнє арифметичне, медіана тощо).
6
Останнє, що корисно затестити на старті — функція WHERE, по суті фільтр. Наприклад, нас цікавлять записи за конкретний період, скажімо до 1 березня цього року; для звуження періоду обмежимо період і з іншого боку. У результаті наш рядок виглядатиме так: WHERE date <'2021-03-01' AND date >'2021-03-14'
Ми отримали базову структуру нашого запиту. Уже зараз ви можете вивантажити дані через Explore у Data Studio та вибрати для результатів базову візуалізацію до вподоби.
Кілька фішок для початківців
— При агрегації над полем new confirmed ви побачили, що воно втратило таку назву: BigQuery залишив date, але змінив для нової агрегації. Задати адекватнішу назву можна через AS.
— У GROUP і ORDER ви можете посилатися на поля не за назвою, а за їхнім порядком у SELECT. Якщо у нас спочатку вказана дата, можемо написати ORDER 1.
— Зверніть увагу: у такому тестовому завданні ви працюєте в Sandbox, тобто не платите за користування. BigQuery з вашими реальними базами даних знімає плату за кількість відсканованих даних. Намагайтеся не робити запити на велику кількість даних.
— Корисна штука — Shortcut. За їхньою допомогою ви можете навіть у веб-інтерфейсі запускати запити та опрацьовувати багато популярних дій.
Почитати, як влаштований Google BigQuery можна у блогах Раджеша Талама BigQuery Explained: Querying your Data та BigQuery Explained: Working with Joins, Nested & Repeated Data.
Дізнатися більше про різницю SQL і NoSQL — у тексті Майка Чена SQL vs. NoSQL – what's the best option for your database needs? та на блозі Scylla.
— У GROUP і ORDER ви можете посилатися на поля не за назвою, а за їхнім порядком у SELECT. Якщо у нас спочатку вказана дата, можемо написати ORDER 1.
— Зверніть увагу: у такому тестовому завданні ви працюєте в Sandbox, тобто не платите за користування. BigQuery з вашими реальними базами даних знімає плату за кількість відсканованих даних. Намагайтеся не робити запити на велику кількість даних.
— Корисна штука — Shortcut. За їхньою допомогою ви можете навіть у веб-інтерфейсі запускати запити та опрацьовувати багато популярних дій.
Почитати, як влаштований Google BigQuery можна у блогах Раджеша Талама BigQuery Explained: Querying your Data та BigQuery Explained: Working with Joins, Nested & Repeated Data.
Дізнатися більше про різницю SQL і NoSQL — у тексті Майка Чена SQL vs. NoSQL – what's the best option for your database needs? та на блозі Scylla.
Щоб навчитися працювати з даними для покращення свого продукту, ви можете обрати потрібний курс на факультеті маркетингу чи менеджменту в Projector.
Текст: Тоня Чундак
Головне фото: Наталія Азаркіна
Текст: Тоня Чундак
Головне фото: Наталія Азаркіна
Гараж
Сподобалась стаття?
