GCP: Завантаження даних у BigQuery

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

Автор

Приналежність

Ігор Мірошниченко

КНУ імені Тараса Шевченка | ФІТ

Вимоги

Створіть обліковий запис на Google Cloud Platform (безкоштовно)

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

1. Підпишіться на [3-місячну ($300+$100 кредит) безкоштовну пробну версію] (https://console.cloud.google.com/freetrial) Google Cloud Platform (GCP). Для цього потрібен наявний обліковий запис Google/Gmail¹. Під час реєстрації вам буде запропоновано ввести дані кредитної картки для виставлення рахунку. Не хвилюйтеся, з вас не стягуватимуться кошти доти, доки ви не зробите активний запит на продовження доступу до GCP після завершення безкоштовної пробної версії. Але для отримання доступу до платформи необхідно мати ідентифікатор проекту, який підлягає оплаті.
2. (Опціонально) Завантажте та дотримуйтесь інструкцій з встановлення утиліти командного рядка Google Cloud SDK gcloud.

Вступ

До хмари!

Найпростіший і найдешевший спосіб отримати доступ до більших обчислювальних потужностей сьогодні - це хмара². Хоча є багато чудових постачальників хмарних послуг, я зосереджуся на Google Cloud Platform (GCP)³. GCP пропонує низку неймовірно корисних сервісів, про деякі з яких ми розповімо в наступних лекціях, а 3-місячна безкоштовна пробна версія є ідеальною відправною точкою для вивчення хмарних обчислень.

Початок роботи

1. Увійдіть в Google Cloud Console.
2. Виберіть або створіть проект.
3. Відкрийте Cloud Shell⁴.
4. Введіть команду для копіювання файлів з цього репозиторію:

git clone https://github.com/aranaur/data-science-on-gcp
cd data-science-on-gcp

Дослідження даних у GCP

Дані

В якості даних будемо використовувати Airline On-Time Performance Data.

Усі великі авіаперевізники США зобов’язані подавати статистичні дані про кожен свій внутрішній рейс до Служби транспортної безпеки (BTS).

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

Фактичний час вильоту і прибуття визначається досить точно, виходячи з того, коли відпускається стоянкове гальмо літака і коли воно знову вмикається в пункті призначення. Правила навіть визначають, що станеться, якщо пілот забуде увімкнути стоянкове гальмо - в такому випадку замість нього використовується час закриття або відкриття пасажирських дверей.

Змінні

Датасет містить понад 100 змінних, але ми використаємо лише деякі з них:

Змінна	Опис
FlightDate	Дата польоту (yyyymmdd)
Reporting_Airline	Унікальний код оператора. Якщо один і той самий код використовувався кількома перевізниками, для більш ранніх користувачів використовується цифровий суфікс, наприклад, PA, PA(1), PA(2). Використовуйте це поле для аналізу за різні роки.
Origin	Аеропорт вильоту
Dest	Аеропорт призначення
CRSDepTime	Час відправлення комп’ютерної системи бронювання (CRS) (місцевий час: hhmm)
DepTime	Фактичний час відправлення (місцевий час: hhmm)
DepDelay	Різниця в хвилинах між запланованим і фактичним часом відправлення. Ранні відправлення показують від’ємні числа.
TaxiOut	Тривалість таксі-ауту, в хвилинах
WheelsOff	Час висадки (місцевий час: hhmm)
WheelsOn	Час відправлення (місцевий час: hhmm)
TaxiIn	Тривалість таксі (хвилини)
CRSArrTime	Час прибуття CRS (місцевий час: hhmm)
ArrTime	Фактичний час прибуття (місцевий час: hhmm)
ArrDelay	Різниця в хвилинах між запланованим і фактичним часом прибуття. Ранні прибуття показують від’ємні значення.
Cancelled	Індикатор скасованого польоту (1 = Так)
CancellationCode	Причину скасування
Diverted	Індикатор відхиленого польоту (1 = Так)
Distance	Відстань між аеропортами (милі)

Примітка

Весь перелік змінних доступний за посиланням https://www.transtats.bts.gov/Fields.asp?gnoyr_VQ=FGJ.

Завантаження

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

Перейдіть за посиланням https://www.transtats.bts.gov/DL_SelectFields.aspx?gnoyr_VQ=FGJ&QO_fu146_anzr=b0-gvzr та оберіть опцію “Prezipped File”.

Відкрийте вкладку “Network” в інструментах розробника браузера та введіть запит на завантаження даних.

Ми побачимо, що відбувається запит до https://transtats.bts.gov/PREZIP/On_Time_Reporting_Carrier_On_Time_Performance_1987_present_2023_1.zip.

Видно, з чого складається шаблон запиту:

${Базовий_рік}_${Рік}_${Місяць}.zip

Спробуємо з командного рядка:

BTS=https://transtats.bts.gov/PREZIP
BASEURL="${BTS}/On_Time_Reporting_Carrier_On_Time_Performance_1987_present"
YEAR=2015
MONTH=3
curl -k -o temp.zip ${BASEURL}_${YEAR}_${MONTH}.zip

Примітка

• curl - це утиліта командного рядка для взаємодії з серверами за допомогою різних протоколів. В даному випадку ми використовуємо протокол HTTP.
• -k вказує на те, що ми дозволяємо curl використовувати незахищене з’єднання.
• -o вказує на те, що ми хочемо зберегти результат у файл temp.zip.

Розпакуємо архів:

unzip temp.zip

Тепер видно, що у середині архіву знаходиться CSV-файл. Подивимось на перші кілька рядків:

head -n 5 *.csv

Замість того, щоб назвати завантажений файл temp.zip, давайте назвемо його 201503.zip і помістимо у тимчасовий каталог. Щоб замінити місяць 3 на 03, ми можемо скористатися командою printf у bash:

MONTH2=$(printf "%02d" $MONTH)

Для створення каталогу використаємо команду mkdir:

TMPDIR=$(mktemp -d)

Тепер для завантаження даних використаємо команду curl:

ZIPFILE=${TMPDIR}/${YEAR}_${MONTH2}.zip
curl -o $ZIPFILE ${BASEURL}_${YEAR}_${MONTH}.zip

Тепер ми можемо розархівувати файл, витягти CSV-файл до поточного каталогу (./) і видалити решту вмісту ZIP-файлу:

unzip -d $TMPDIR $ZIPFILE
mv $TMPDIR/*.csv ./${YEAR}${MONTH2}.csv
rm -rf $TMPDIR

Всі попередні команди містяться у файлі під назвою download.sh, а потім у скрипті ingest.sh викликаємо їх з циклу for:

for MONTH in `seq 1 12`; do
  bask download.sh $YEAR $MONTH
done

Виконаємо скрипт:

for MONTH in `seq 1 12`; do bash ../02_ingest/download.sh 2015 $MONTH; done

Подивимось на кількість рядків у файлах:

wc -l *.csv

Завантаження даних у Google Cloud Storage

Тепер, коли ми завантажили дані, давайте завантажимо їх у Google Cloud Storage (GCS). Це дозволить нам використовувати їх у інших сервісах GCP, таких як BigQuery, Dataflow, Dataproc, AI Platform тощо.

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

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

PROJECT=$(gcloud config get-value project)
BUCKET=${PROJECT}-dsongcp
REGION=us-central1 # Дивіться https://cloud.google.com/storage/docs/locations
gsutil mb -l $REGION gs://$BUCKET

Тепер, коли бакет створено, ми можемо завантажити файли у нього:

gsutil -m cp *.csv gs://fit-cloud-course-dsongcp/flights/raw/

BigQuery

Першим кроком для отримання даних у BigQuery є створення набору даних - набору даних, який є контейнером для таблиць. У проекті можна створити кілька наборів даних. Перейдіть до веб-консолі і виберіть опцію Create Dataset. Потім створіть набір даних з назвою dsongcp.

Або використайте командний рядок:

bq mk dsongcp

Завантаження даних

Ми можемо завантажити дані безпосередньо у власне сховище BigQuery за допомогою утиліти командного рядка bq:

PROJECT=$(gcloud config get-value project)
BUCKET=${PROJECT}-dsongcp
bq load --autodetect --source_format=CSV \
dsongcp.flights_auto \
gs://${BUCKET}/flights/raw/201501.csv

Ми можемо спробувати зробити запит на дані, щоб знайти середню затримку вильоту та прильоту в найбільш завантажених аеропортах:

SELECT
  ORIGIN,
  AVG(DepDelay) AS dep_delay,
  AVG(ArrDelay) AS arr_delay,
  COUNT(ArrDelay) AS num_flights
FROM
  `dsongcp.flights_auto`
GROUP BY
  ORIGIN
ORDER BY num_flights DESC
LIMIT 10

Автоматичне визначення схеми дозволяє нам завантажити дані без необхідності визначення схеми. Але це може бути не найкращим варіантом для великих наборів даних. В такому випадку краще визначити схему вручну.

Наразі ми мало що знаємо про поля, тому можемо попросити BigQuery обробляти всі стовпці, окрім FlightDate, як рядки:

SCHEMA=Year:STRING,...,FlightDate:DATE,Reporting_Airline:STRING,...

Крім того, ми можемо розбити дані на партіції за допомогою поля FlightDate:

--time_partitioning_field=FlightDate --time_partitioning_type=MONTH

Тоді код для завантаження даних у BigQuery буде виглядати наступним чином:

PROJECT=$(gcloud config get-value project)
BUCKET=${PROJECT}-dsongcp
YEAR=2015
SCHEMA=Year:STRING,Quarter:STRING,Month:STRING,DayofMonth:STRING,DayOfWeek:STRING,FlightDate:DATE,Reporting_Airline:STRING,DOT_ID_Reporting_Airline:STRING,IATA_CODE_Reporting_Airline:STRING,Tail_Number:STRING,Flight_Number_Reporting_Airline:STRING,OriginAirportID:STRING,OriginAirportSeqID:STRING,OriginCityMarketID:STRING,Origin:STRING,OriginCityName:STRING,OriginState:STRING,OriginStateFips:STRING,OriginStateName:STRING,OriginWac:STRING,DestAirportID:STRING,DestAirportSeqID:STRING,DestCityMarketID:STRING,Dest:STRING,DestCityName:STRING,DestState:STRING,DestStateFips:STRING,DestStateName:STRING,DestWac:STRING,CRSDepTime:STRING,DepTime:STRING,DepDelay:STRING,DepDelayMinutes:STRING,DepDel15:STRING,DepartureDelayGroups:STRING,DepTimeBlk:STRING,TaxiOut:STRING,WheelsOff:STRING,WheelsOn:STRING,TaxiIn:STRING,CRSArrTime:STRING,ArrTime:STRING,ArrDelay:STRING,ArrDelayMinutes:STRING,ArrDel15:STRING,ArrivalDelayGroups:STRING,ArrTimeBlk:STRING,Cancelled:STRING,CancellationCode:STRING,Diverted:STRING,CRSElapsedTime:STRING,ActualElapsedTime:STRING,AirTime:STRING,Flights:STRING,Distance:STRING,DistanceGroup:STRING,CarrierDelay:STRING,WeatherDelay:STRING,NASDelay:STRING,SecurityDelay:STRING,LateAircraftDelay:STRING,FirstDepTime:STRING,TotalAddGTime:STRING,LongestAddGTime:STRING,DivAirportLandings:STRING,DivReachedDest:STRING,DivActualElapsedTime:STRING,DivArrDelay:STRING,DivDistance:STRING,Div1Airport:STRING,Div1AirportID:STRING,Div1AirportSeqID:STRING,Div1WheelsOn:STRING,Div1TotalGTime:STRING,Div1LongestGTime:STRING,Div1WheelsOff:STRING,Div1TailNum:STRING,Div2Airport:STRING,Div2AirportID:STRING,Div2AirportSeqID:STRING,Div2WheelsOn:STRING,Div2TotalGTime:STRING,Div2LongestGTime:STRING,Div2WheelsOff:STRING,Div2TailNum:STRING,Div3Airport:STRING,Div3AirportID:STRING,Div3AirportSeqID:STRING,Div3WheelsOn:STRING,Div3TotalGTime:STRING,Div3LongestGTime:STRING,Div3WheelsOff:STRING,Div3TailNum:STRING,Div4Airport:STRING,Div4AirportID:STRING,Div4AirportSeqID:STRING,Div4WheelsOn:STRING,Div4TotalGTime:STRING,Div4LongestGTime:STRING,Div4WheelsOff:STRING,Div4TailNum:STRING,Div5Airport:STRING,Div5AirportID:STRING,Div5AirportSeqID:STRING,Div5WheelsOn:STRING,Div5TotalGTime:STRING,Div5LongestGTime:STRING,Div5WheelsOff:STRING,Div5TailNum:STRING


for MONTH in `seq -w 1 12`; do
CSVFILE=gs://${BUCKET}/flights/raw/${YEAR}${MONTH}.csv
bq --project_id $PROJECT \
load --time_partitioning_field=FlightDate \
--time_partitioning_type=MONTH \
--source_format=CSV --ignore_unknown_values \
--skip_leading_rows=1 --schema=$SCHEMA \
${PROJECT}:dsongcp.flights_raw\$${YEAR}${MONTH} $CSVFILE
done

Повний код для завантаження даних у BigQuery міститься у файлі bqload.sh.

Дашборди

Створення представлень

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

Створимо представлення для затримок вильоту та прильоту:

CREATE OR REPLACE VIEW dsongcp.flights AS
SELECT
  FlightDate AS FL_DATE,
  Reporting_Airline AS UNIQUE_CARRIER,
  OriginAirportSeqID AS ORIGIN_AIRPORT_SEQ_ID,
  Origin AS ORIGIN,
  DestAirportSeqID AS DEST_AIRPORT_SEQ_ID,
  Dest AS DEST,
  CRSDepTime AS CRS_DEP_TIME,
  DepTime AS DEP_TIME,
  CAST(DepDelay AS FLOAT64) AS DEP_DELAY,
  CAST(TaxiOut AS FLOAT64) AS TAXI_OUT,
  WheelsOff AS WHEELS_OFF,
  WheelsOn AS WHEELS_ON,
  CAST(TaxiIn AS FLOAT64) AS TAXI_IN,
  CRSArrTime AS CRS_ARR_TIME,
  ArrTime AS ARR_TIME,
  CAST(ArrDelay AS FLOAT64) AS ARR_DELAY,
  IF(Cancelled = '1.00', True, False) AS CANCELLED,
  IF(Diverted = '1.00', True, False) AS DIVERTED,
  DISTANCE
FROM dsongcp.flights_raw;

Перша візуалізація

Перейдіть до Looker Studio за посиланням https://lookerstudio.google.com/ та виберіть опцію “Створити” -> Джерело даних.

У меню виберіть BigQuery та вкажіть проект та набір даних/представлення, яка Вас цікавить (у нашому випадку це представлення flights з набору даних dsongcp). За необхідності змініть тип даних та інші необхідні маніпуляції з даними і натисніть Створити звіт.

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

Додайте новий графік за допомогою кнопки Додати діаграму -> Точкова діаграма.

Поки що проігноруйте Параметр діапазону дат і використайте три характеристики: Параметр, Показник X, Показник X.

Змініть (за необхідності) Параметр на UNIQUE_CARRIER, Показник X на DEP_DELAY, Показник X на ARR_DELAY і змініть метрику агрегування для X і Y на Середнє. Ми маємо отримати усереднену затримку відправлення та прибуття для різних перевізників.

Перейдіть на вкладку Стиль, додайте лінію тренду і додайте мітки даних:

Керування даними

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

Оберіть меню Додати елемент керування -> Діапазон дат та оберіть місце його розташування. В налаштуваннях меню оберіть Фікосваний діапазон дат і вкажіть початкову дату 1 січня 2015, а кінцеву - 31 грудня 2015.

Перейдіть у меню Переглянути у верхній частині екрану і спробуйте змінити кінцеву дату на 31 травня 2015:

Друга візуалізація

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

Додайте новий графік за допомогою кнопки Додати діаграму -> Кільцева діаграма.

Нам треба показати співвідношення рейсів із запізненням до рейсів без запізнення. Як нам отримати ці дані?

В базі даних немає стовпця, який би вказував на загальну кількість вильотів. Однак у Looker Studio є спеціальне значення Record Count, яке ми можемо використовувати як матрику, попередньо змінивши тип агрегації на Кількість.

Додамо нову змінну Параметр з назвою is_late. Клікніть у поле Параметр -> Додати поле. Дайте назву новій змінній та вкажіть формулу:

CASE WHEN
(ARR_DELAY < 15)
THEN
'ON TIME'
ELSE
'LATE'
END

Третя візуалізація

Додамо ще один графік, який показуватиме затримки для кожного перевізника.

Додайте новий графік за допомогою кнопки Додати діаграму -> Стовпчаста діаграма

Налаштування:

• Параметр: UNIQUE_CARRIER
• Показник X: DEP_DELAY
• Показник Y: ARR_DELAY
• Агрегація: Середнє
• Сортування: UNIQUE_CARRIER (за зростанням)

Стиль:

• Стовпчики: 20
• Осі: Одинарна

Додаткові візуалізації

Додамо ще декілька схожих візуалізацій, але цього разу з різними порогами затримки вильоту: 10, 15, 20+ хвилин.

Для цього нам знадобиться створити нові представлення даних:

CREATE OR REPLACE VIEW dsongcp.delayed_10 AS
SELECT * FROM dsongcp.flights WHERE dep_delay >= 10;

CREATE OR REPLACE VIEW dsongcp.delayed_15 AS
SELECT * FROM dsongcp.flights WHERE dep_delay >= 15;

CREATE OR REPLACE VIEW dsongcp.delayed_20 AS
SELECT * FROM dsongcp.flights WHERE dep_delay >= 20;

Які висновки можемо зробити з цих графіків?

Примітки

1. Якщо у вас є кілька облікових записів Gmail, виберіть один і постійно використовуйте його, коли з’являється запит на автентифікацію нового API сервісу GCP

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

3. Альтернативи GCP включають AWS та Digital Ocean. Posit нещодавно також запустила власний хмарний сервіс: Posit Cloud, який має вужчу спрямованість, але чудово підходить для навчання і є (наразі) безкоштовним для використання. Хороша новина полягає в тому, що це все чудові варіанти, а загальні принципи хмарних обчислень переносяться дуже легко. Тож використовуйте те, що вам зручніше

4. Cloud Shell - це мікро-ВМ, яка існує протягом усього часу, поки відкрито вікно браузера, і надає вам термінальний доступ до мікро-ВМ. Закрийте вікно браузера, і мікро-ВМ зникне. ВМ Cloud Shell безкоштовна і постачається з багатьма інструментами, які знадобляться розробникам на Google Cloud Platform. Наприклад, на ній встановлені Python, Git, Google Cloud SDK та Orion (веб-редактор коду). Хоча віртуальна машина Cloud Shell є ефемерною, вона прикріплена до постійного диска, який прив’язаний до вашого облікового запису користувача. Файли, які ви зберігаєте в домашньому каталозі, зберігаються під час різних сеансів Cloud Shell.