Від Google Sheets до BigQuery: Вирішення проблем фінансової команди з великими обсягами даних

Від Google Sheets до BigQuery: Вирішення проблем фінансової команди з великими обсягами даних

Команди фінансів у сучасних компаніях часто стикаються з непомірно великими обсягами даних, які необхідно обробляти й аналізувати. Використання традиційних інструментів, таких як Google Sheets, може стати недостатнім при масштабуванні операцій. У цій статті ми розглянемо, як команда розробників знайшла рішення цієї проблеми, використовуючи Google AppSheet і BigQuery, і як це вплинуло на роботу фінансового відділу.

Проблема завантаженості Google Sheets

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

💡 Google Sheets має низку обмежень, включно з максимумом у 5 мільйонів клітинок на документ, максимумом у 18,278 стовпчиків на аркуш і лімітом розміру файлу в 100 МБ. Хоча обмежень на кількість рядків прямо немає, загальна кількість комірок не повинна перевищувати 5 мільйонів. Також існують обмеження на використання формул, включно з максимум 1,000 формул GoogleFinance і максимум 50 формул для зовнішніх даних, таких як ImportHtml і ImportXml.

Більш детальніше про обмеження👇

https://spreadsheetpoint.com/google-sheets-limitations/

Ми зіткнулися з проблемою обробки великої кількості транзакцій платежів за день, які раніше реєструвалися в Google Sheets. До цих таблиць були підключені різні інтеграції, але через великий обсяг операцій таблиці не справлялися з навантаженням: вони не могли адекватно обробляти інтеграції та реєстрацію транзакцій.

Ситуація погіршилася, коли в одній із таблиць накопичилося близько 16 тисяч записів. Будь-яка спроба використати формулу для пошуку інформації або розрахунку середньої валюти, навіть для визначення дати, вимагала обробки всього обсягу даних. Це призводило до значних затримок, "зависань" або навіть повного виходу з ладу таблиці.

Альтернативне рішення замість таблиць

Щоб прискорити й оптимізувати нашу роботу, ми вирішили використовувати спеціалізовану базу даних, таку як BigQuery від Google Cloud. Це дозволило нам значно поліпшити процес обробки даних.

💡 Google BigQuery – це хмарний сервіс Google для роботи з великими обсягами даних. Це PaaS-сервіс, що пропонує функції СУБД та інтегрується з безліччю платформ. Він дає змогу завантажувати, зберігати й обробляти масштабні обсяги даних, використовуючи SQL-запити. BigQuery забезпечує високу швидкість обробки даних і підтримує інтеграцію з сервісами на кшталт Google Таблиці та Microsoft Power BI, пропонуючи широкий спектр аналітичних можливостей

Что такое Google BigQuery и почему им стоит пользоваться
Как работать с оконными функциями в Google BigQuery — подробное руководство
Как начать работу с BigData — загружаем данные в Google BigQuery

Рішення з використанням Google Cloud та BigQuery

Для вирішення проблеми з перевантаженими таблицями Google Sheets і поліпшення обробки великих обсягів даних, ми розробили і реалізували нове рішення, використовуючи можливості Google Cloud і BigQuery. Ми створили спеціалізовану базу даних у платному проєкті Google Cloud, де встановили суворі налаштування приватності, що дозволяють доступ до даних тільки авторизованим користувачам.

Для автоматизації процесу передачі даних ми написали скрипт на Google Apps Script, який було опубліковано у вигляді веб-додатку. Це дало йому змогу функціонувати як webhook, приймаючи дані з наших внутрішніх систем, де вже встановлені відповідні тригери для надсилання даних. Потім скрипт обробляє ці дані і перенаправляє їх у базу даних BigQuery.

Таким чином, webhook у Google Apps Script отримує дані з наших систем обліку, де постійно діють тригери надсилання webhook, і передає їх до бази даних на BigQuery, що значно прискорює та оптимізує процес роботи з великими обсягами фінансових даних.

Чи використовувати платну версію?

Ось порівняльна таблиця переваг та особливостей між платною та безкоштовною версіями Google Cloud

Функція

Безкоштовна версія Google Cloud

Платна версія Google Cloud

Доступ до сервісів

Обмежений доступ до обраних сервісів

Повний доступ до всіх сервісів Google Cloud

Підтримка

Підтримка спільноти, онлайн документація

Кілька рівнів платних опцій підтримки

Ресурси обчислень

Обмежені лімітами Always Free

Масштабуються відповідно до потреб, з налаштованими квотами

Ємність зберігання

Стандартний постійний диск: 30 ГБ-місяців знімків, 5 ГБ-місяців зберігання знімків

Масштабується до петабайт, ціноутворення на основі використання

Бази даних

Firestore: 1 ГБ зберігання, Cloud Bigtable: немає прямого безкоштовного рівня, але можна застосувати кредит на 300$

Повністю масштабовані, з витратами на основі зберігання та обробки

Мережа

Застосовуються обмеження на вихідний трафік для безкоштовних сервісів

Більш високі або відсутні обмеження на вихідний трафік, залежно від сервісу і плану ціноутворення

Виклики API

Обмежені

Вищі ліміти або необмежені, залежно від сервісу

Ціноутворення

Доступний безкоштовний рівень для обраних сервісів, кредит на 300$ на 90 днів

Оплата за фактом використання, підписка або індивідуальне ціноутворення

Про Вебхуки детальніше можна почитати тут:

WEBHOOKS
Добавление триггера "Отправить webhook»

Вибір технології для оптимізації роботи з даними

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

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

Крім того, ми інтегрували BigQuery з Google AppSheet, який служить інтерфейсом для взаємодії з базою даних. Це дало нам змогу легко під'єднувати базу даних і ефективно використовувати її в рамках нашої інфраструктури. AppSheet забезпечує високу швидкість роботи завдяки інтеграції з Google Акаунтами і полегшує налаштування доступу для різних користувачів, що дає можливість детально контролювати, хто може переглядати або видаляти дані.

Детальніше, про те, як використовувати AppSheet і бази даних:

Use data from MySQL
Using Cloud SQL as a Datasource for Your AppSheet App
Use data from SQL Server
AppSheet with MySQL Database
Connect to an on-premises database

Простий шлях повторити процес оптимізації даних

Якби нам знову знадобилося збирати дані про фактичні платежі, найпростішим і найкоротшим шляхом було б використання веб-хука через платформу Make (раніше відому як Integromat), який забезпечував би інтеграцію з API BigQuery для автоматичного додавання даних. Цей підхід ідеально підходить для рішень без написання коду, що особливо зручно для тих, хто не володіє навичками програмування.

Однак якщо в когось є досвід програмування, альтернативним варіантом було б написання скрипта на Google Apps Script. Це безкоштовний інструмент зі зрозумілим синтаксисом, який дає змогу легко створювати та редагувати код для роботи з різними сервісами Google, включно з BigQuery. Такий підхід дасть змогу більш глибоко і гнучко налаштовувати процес роботи з даними.

Конектори в make webhook-kommo-bigquery
Конектори в make webhook-kommo-bigquery

Складнощі під час роботи з BigQuery

Під час реалізації нашого проєкту з BigQuery ми зіткнулися з кількома проблемами, особливо в початковий період. Основними труднощами було розуміння структури API BigQuery та особливостей бази даних, таких як спеціальні поля даних. Зокрема, нам потрібно було точно визначити типи даних у полях, наприклад, коли справа стосувалася колонок з ідентифікаторами (ID). Важливо було правильно вказати, що такі поля мають бути позначені як 'big number', а не просто 'number', щоб уникнути помилок під час обробки великих числових значень.

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

Ось документи, які допомогли розібратися з проблемою:

BigQuery documentation
BigQuery Service

Оптимізація коду і запитів до API

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

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


Принципи та практики в реалізації ефективного кодування

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

Помітивши це, ми почали виокремлювати повторювані дії в окремі функції й активно використовувати умовні оператори, як-от if і switch, що значно прискорило та поліпшило якість наших запитів. Це також зробило наш код чистішим, красивішим і ефективнішим.


Навички, які потрібні для роботи з BigQuery

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

Важливо було також мати глибоке розуміння роботи баз даних, особливо SQL-запитів типу SELECT, INSERT, UPDATE і DELETE. У цьому допоможуть такі статті:

Query syntax
Data manipulation language (DML) statements in GoogleSQL

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

Почитайте, як швидко отримати відповідь на свій запит саппорту в google cloud:

Customer Care procedures: Escalate a case

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


ВИСНОВОК

Впровадження технологій Google AppSheet і BigQuery справило значний вплив на підвищення ефективності роботи фінансового відділу нашої компанії. Подолання обмежень Google Sheets шляхом міграції на потужнішу та гнучкішу систему дало нам змогу не тільки прискорити обробку великих обсягів даних, а й підвищити точність фінансової звітності.

Використання BigQuery, у поєднанні з автоматизацією процесів через Google Apps Script і веб-хуки, демонструє, як технологічні інновації можуть трансформувати складні задачі оброблення даних у більш прості та керовані процеси.

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

En