Оволодінням просунутим SQL для BigQuery: Поради, хитрощі та найкращі практики

Фото від Firmbee.com на Unsplash

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

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

1. Передові техніки запитів

Функції вікон для аналітичних запитів

Функції вікон, також звані аналітичними функціями, дозволяють виконувати обчислення на наборі рядків, пов'язаних з поточним рядком. Вони ідеально підходять для ранжування, підсумкових сум та інших кумулятивних метрик.

Приклад: Обчислення поточної суми продажів по місяцях:

SELECT   
 sales_date,  
 SUM(sales_amount) OVER (PARTITION BY YEAR(sales_date) ORDER BY sales_date) AS running_total  
FROM `project.dataset.sales_data`  
ORDER BY sales_date;

Загальні вирази таблиць (CTE) для модульних запитів

CTE спрощують складні запити, розбиваючи їх на зрозумілі, багаторазово використовувані частини.

WITH monthly_sales AS (  
 SELECT   
 EXTRACT(MONTH FROM sales_date) AS month,  
 SUM(sales_amount) AS total_sales  
 FROM `project.dataset.sales_data`  
 GROUP BY month  
)  
SELECT   
 month,   
 total_sales   
FROM monthly_sales  
WHERE total_sales > 10000;

Маніпулювання масивами (ARRAY) та структурами (STRUCT)

Підтримка BigQuery напівструктурованих типів даних, таких як ARRAY та STRUCT, дає можливість потужної і гнучкої обробки даних.

• Розпакуйте вкладені дані за допомогою UNNEST().
• Створюйте ієрархічні дані за допомогою ARRAY_AGG() або STRUCT().

Приклад:

SELECT   
 customer_id,   
 ARRAY_AGG(STRUCT(order_id, order_total)) AS order_details  
FROM `project.dataset.orders`  
GROUP BY customer_id;

Агрегування значень стовпців

Припустимо, ви об’єднуєте 2 таблиці за допомогою join key. Тепер друга таблиця має кілька записів для одного й того ж join key. Ваша мета — створити виведення, яке агрегує всі значення з вашої другої таблиці (для кожного даного join key) і відображає їх в одному стовпці.

SELECT f.column1, STRING_AGG(s.column1, ",") AS CONCATENATED_VALUES  
FROM FIRST_TABLE AS f  
LEFT OUTER JOIN SECOND_TABLE AS s  
ON f.join_key = s.join_key  
GROUP BY f.column1

2. Використання інформаційної схеми

Отримати всі визначення збережених процедур

SELECT routine_definition FROM region-us.INFORMATION_SCHEMA.ROUTINES  
WHERE routine_name LIKE '%TEST%';

Отримати інформацію про всі запити, виконані за останні n днів

SELECT * FROM ``.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT  
WHERE creation_time > 'YYYY-MM-DD';

Невелика зміна вищенаведеного запиту також може допомогти вам знайти таку інформацію:

• Ідентифікуйте запити, виконані певним користувачем за останній місяць.
• Ідентифікуйте всі запити, які не вдалися за останній тиждень (разом з причинами помилок) для даного проєкту.
• Ідентифікуйте заплановані запити та завдання передачі даних.
• Знайдіть усі запити, які вставили записи в певну таблицю.
• Проаналізуйте продуктивність певного запиту (з часом).
• Ідентифікуйте вартість певного запиту (в доларах).
• Чи використовував запит кеш?

Знайти DDL для будь-якої таблиці

SELECT table_name, ddl FROM ``.``.INFORMATION_SCHEMA.TABLES;

3. Стратегії оптимізації

Зменшення вартості запитів за допомогою партиціонування та кластеризації

Партиціонування розбиває ваші таблиці на сегменти на основі стовпця (наприклад, DATE). Кластеризація організовує дані в межах партицій для покращення продуктивності запитів.

Приклад партиціонування:

CREATE TABLE `project.dataset.table_name`  
PARTITION BY DATE(timestamp_column) AS  
SELECT * FROM `project.dataset.source_table`;

Використання EXPLAIN для аналізу запитів

BigQuery надає оператор EXPLAIN, щоб допомогти проаналізувати плани виконання запитів.
Використовуйте це для виявлення вузьких місць (bottlenecks) та оптимізації запитів.

Фільтруйте на ранніх етапах

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

SELECT   
 *   
FROM `project.dataset.large_table`  
WHERE DATE(timestamp_column) = '2024-01-01';

4. Кращі практики для масштабованих запитів

Використовуйте SELECT з конкретними стовпцями

Уникайте використання SELECT * у запитах на продуктивність, щоб обмежити кількість сканованих даних і покращити продуктивність.

Використовуйте SQL функції

BigQuery надає набір функцій для спрощення операцій:

• STRING_AGG для об’єднання рядків.
• APPROX_QUANTILES для наближеного аналізу квартилів.
• Функції GEOGRAPHY для геопросторових запитів.

5. Налагодження та моніторинг запитів

Логування продуктивності запитів за допомогою INFORMATION_SCHEMA

BigQuery надає системні таблиці для моніторингу продуктивності запитів.

SELECT   
 query,   
 total_bytes_processed,   
 execution_time  
FROM `region-us`.INFORMATION_SCHEMA.JOBS  
WHERE DATE(creation_time) = CURRENT_DATE();

Встановіть ліміти для запитів, щоб запобігти перевантаженням

Використовуйте обмеження ресурсів, такі як LIMIT або MAX_STEPS, для довготривалих запитів.

6. Корисні запити

Отримати значення з поля дати у форматі YYYY-MM-DD

SELECT   
 FORMAT_DATE('%Y-%m-%d', your_date_column) AS formatted_date  
FROM `project.dataset.table_name`;

Заміна кожного символу в source_characters на відповідний символ у target_characters

У наведеному прикладі, s буде замінено на z, c буде замінено на k, а o буде замінено на u:

SELECT TRANSLATE('This is a cookie', 'sco', 'zku') AS translate  

/*------------------*  
 | translate |  
 +------------------+  
 | Thiz iz a kuukie |  
 *------------------*/

Знайти різницю між двома датами

Простий спосіб знайти різницю між двома датами — це перетворити кожну в формат UNIX_DATE (який перетворює значення DATE в кількість днів з 1970-01-01), а потім відняти ці значення.

SELECT UNIX_DATE(CURRENT_DATE()) AS unix_days_today;  
-- 19885

Інший варіант — використати функцію TIMESTAMP_DIFF.

SELECT TIMESTAMP_DIFF(TIMESTAMP "2018-08-14", TIMESTAMP "2018-10-14", DAY) AS negative_diff;  

/*---------------*  
 | negative_diff |  
 +---------------+  
 | -61 |  
 *---------------*/

Або функцію TIME_DIFF:

TIME_DIFF(TIMESTAMP, TIMESTAMP, PART)

Додати час (дні, години, хвилини, секунди або мілісекунди) до заданої дати/мітки часу

SELECT  
 TIMESTAMP("2008-12-25 15:30:00+00") AS original,  
 TIMESTAMP_ADD(TIMESTAMP "2008-12-25 15:30:00+00", INTERVAL 10 MINUTE) AS later;  

-- Результати можуть відрізнятись в залежності від середовища та часового поясу, де виконується цей запит.  
/*-------------------------+-------------------------*  
 | original | later |  
 +-------------------------+-------------------------+  
 | 2008-12-25 15:30:00 UTC | 2008-12-25 15:40:00 UTC |  
 *-------------------------*/

TIMESTAMP_ADD підтримує наступні значення для date_part:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR. Еквівалентно 60 частинам MINUTE.
• DAY.
  Еквівалентно 24 частинам HOUR.

Перетворення рядка таблиці в формат JSON

Ви можете використовувати функцію TO_JSON_STRING для серіалізації цілого рядка в формат JSON.

WITH SampleData AS (  
 SELECT   
 'Product A' AS product_name,   
 100 AS price,   
 STRUCT('Electronics' AS category, 'Available' AS status) AS details  
)  
SELECT   
 TO_JSON_STRING(SampleData) AS json_output  
FROM SampleData;

Виведення: -

{"product_name":"Product A","price":100,"details":{"category":"Electronics","status":"Available"}}

Увага! Цю функцію зазвичай використовують для підготовки даних у форматі JSON для зовнішніх систем (наприклад, API або NoSQL баз даних)

Ось приклад, де ця функція використовується для перетворення вкладеного STRUCT з масивом в JSON —

SELECT TO_JSON_STRING(  
 STRUCT(  
 'Alice' AS name,   
 ARRAY[10, 20, 30] AS scores  
 )  
) AS json_output;

Виведення: -

{"name":"Alice","scores":[10,20,30]}

Увага! Якщо ви хочете отримати дані у чистому форматі JSON (без перетворення в STRING), можна використати TO_JSON.

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

Отримання частини (підрядка) значення STRING або BYTES

SUBSTR(value, position[, length])

Приклади -

SELECT SUBSTR('apple', 2, 2) AS example  

/*---------*  
 | example |  
 +---------+  
 | pp |  
 *---------*/

SELECT SUBSTR('apple', 2) AS example  

/*---------*  
 | example |  
 +---------+  
 | pple |  
 *---------*/

SELECT SUBSTR('apple', -2) AS example  

/*---------*  
 | example |  
 +---------+  
 | le |  
 *---------*/

Зазначте, що функція SUBSTRING є лише псевдонімом для SUBSTR.

Знайти позицію конкретного символу в рядку

SELECT STRPOS('[email protected]', '@') AS example  

/*---------*  
 | example |  
 +---------+  
 | 4 |  
 *---------*/

Агрегувати всі значення з колонки для заданого ключа і показати їх в агрегованій формі

Функція STRING_AGG може використовуватися для об'єднання значень з кількох рядків в один рядок, з можливістю додавання роздільника.

У наведеному прикладі ми використовуємо цю функцію для групування даних за категоріями:

SELECT department, STRING_AGG(employee_name, ', ') AS employees  
FROM `project.dataset.employee_data`  
GROUP BY department;  

/*-------------------------------------*  
 |department | employees  
/*-------------------------------------*  
 |Sales | John, Alice  
 |Engineering | Bob, Maria, Charlie  
/*-------------------------------------*

Цю функцію також можна використовувати для підготовки структур, схожих на JSON, для API або експортів даних.

SELECT STRING_AGG(FORMAT('%s: %d', name, score), ', ') AS json_format  
FROM `project.dataset.scores`;

Виведення: -

John: 95, Alice: 88, Bob: 92

Інший випадок використання — обробка великих текстових агрегатів з LIMIT

SELECT STRING_AGG(name, ', ' LIMIT 3) AS top_names  
FROM `project.dataset.employees`;

Виведення: -

John, Alice, Bob

Порада: Ви можете використовувати клаузулу WITHIN_GROUP, щоб уникнути випадкового сортування значень під час їх об'єднання.

SELECT   
 department,   
 STRING_AGG(name, ', ' WITHIN GROUP (ORDER BY score DESC)) AS ordered_names  
FROM `project.dataset.scores`  
GROUP BY department;  

/*-------------------------------------*  
 |department | ordered_names  
/*-------------------------------------*  
 |Sales | John, Alice  
 |Engineering | Charlie, Bob, Maria   
/*-------------------------------------*

У наведеному результаті імена будуть відсортовані на основі їх оцінок (тобто
John мав вищий бал, ніж Alice).

Використовуйте інше значення, якщо основне значення є NULL

IFNULL(expression, replacement)

Використовуйте CASE для обробки сценаріїв

(CASE WHEN Field01 > 3 THEN 'Greater_Than_Three'  
 WHEN Field01 = 3 THEN 'Equal_To_Three'  
 ELSE 'Not Applicable'  
 END  
) AS ALIAS_NAME

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

WHERE  
QUALIFY COUNT(DISTINCT Rate) OVER (PARTITION BY Start_Date, ID) = 1

Використовуйте REGEX як порятунок

Хочете витягти ім'я файлу з шляху до файлу? Використовуйте наступний регулярний вираз:

REGEXP_EXTRACT(file_path, r'/([^/]+)$')

Хочете витягти значення для заданого ключа (“X/Y”) з поля JSON? Використовуйте наступний регулярний вираз.

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

REGEXP_EXTRACT(TO_JSON_STRING(Json_Column), r'"X/Y"\s*:\s*"([^"]*)"')

Хочете перевірити, чи міститься певний рядок у полі (пошук без врахування регістру)? Використовуйте наступний регулярний вираз.

REGEXP_CONTAINS(lower(Column_Name), 'search_string')

7. Запобігання помилок під час обчислень

Функції SAFE_ у BigQuery призначені для обробки помилок під час виконання, повертаючи NULL замість того, щоб генерувати помилку.

Ось всі доступні функції SAFE_ у BigQuery:

• SAFE_ADD: Еквівалент оператора додавання (X + Y), але повертає NULL, якщо виникає переповнення.
• SAFE_CAST: Схожий на функцію CAST, але повертає NULL, коли виникає помилка під час виконання.
• SAFE_CONVERT_BYTES_TO_STRING: Перетворює значення типу BYTES у тип STRING і замінює будь-які недійсні UTF-8 символи на символ заміни Unicode, U+FFFD.
• SAFE_DIVIDE: Еквівалент оператора ділення (X / Y), але повертає NULL, якщо виникає помилка.
• SAFE_MULTIPLY: Еквівалент оператора множення (X * Y), але повертає NULL, якщо виникає переповнення.
• SAFE_NEGATE: Еквівалент унарного оператора мінуса (-X), але повертає NULL, якщо виникає переповнення.
• SAFE_SUBTRACT: Еквівалент оператора віднімання (X - Y), але повертає NULL, якщо виникає переповнення.

Висновок

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

Сподіваюсь, ви дізналися щось нове сьогодні!

Перекладено з: Mastering Advanced BigQuery SQLs: Tips, Tricks, and Best Practices