Использование Google Cloud для обработки и анализа данных (часть 3)

Облако — это новая норма, и я расскажу, как использовать Google Cloud для исследования, обработки и анализа данных.

Эта статья является продолжением Части 1 Здесь и Части 2 Здесь. Эта часть будет посвящена использованию Google BigQuery. Мы рассмотрим использование веб-консоли и интерфейса командной строки экземпляра для навигации по BigQuery.

Если у вас возникнут какие-либо проблемы с разрешениями, вам необходимо проверить свое разрешение IAM/API.

Использование веб-консоли

В веб-консоли мы увидим домашнюю страницу. Обратите внимание, что в проекте нет набора данных/базы данных. Нажмите на кнопку «Добавить».

Нажмите «Облачное хранилище Google».

Загрузите файл из облачного хранилища Google и создайте новый набор данных.

Я использовал приведенные ниже настройки для своего набора данных, затем нажмите «Создать набор данных».

Введите имя таблицы по вашему выбору и не забудьте отметить галочкой автоматическое определение схемы. Вы также можете сделать это вручную, если хотите. Ваша последняя страница должна быть похожа на фотографию ниже. Затем нажмите «Создать таблицу».

После создания таблицы обновите страницу, и вы сможете увидеть созданную таблицу полетов.

Использование экземпляра CLI (интерфейс командной строки)

Теперь мы попробуем разместить таблицу с помощью CLI, а не вручную через веб-консоль.

Чтобы составить таблицу, нам понадобятся данные и схема данных. Вместо того, чтобы вручную вводить схему данных в формате JSON, для этого конкретного примера мы можем загрузить схему из ранее загруженной таблицы, используя код ниже.

bq show --schema --format=prettyjson flights202301.flightstable > schemafordata.json

Файл JSON должен выглядеть, как показано ниже.

Затем мы создадим набор данных с помощью интерфейса командной строки экземпляра, используя приведенный ниже код.

bq mk flights202301cli

Для наглядности попробуем загрузить данные в таблицу без схемы, но это не сработает.

bq load flights202301cli.flightstable gs://lszbucket1/data_202301.csv

Мы используем приведенный ниже код, чтобы запустить его со схемой.

bq load --source_format=CSV flights202301cli.flightstable gs://lszbucket1/data_202301.csv ~/flights/schemafordata.json

Когда мы пытаемся загрузить схему, это должно сработать, но в данном случае это не удалось, потому что мы забыли удалить первую строку заголовков в файле csv. Таким образом, мы изменили код, чтобы пропустить первую строку CSV. Используйте приведенный ниже код:

bq load --source_format=CSV --skip_leading_rows=1 flights202301cli.flightstable gs://lszbucket1/data_202301.csv ~/flights/schemafordata.json

Мы можем проверить таблицу, используя приведенный ниже код.

bq show flights202301cli.flightstable

Мы также должны иметь возможность видеть то, что мы создали из CLI, на веб-консоли.

Теперь, когда у нас есть набор данных в BigQuery, давайте создадим материализованное представление того, что мы хотим, используя приведенный ниже код. Мы можем использовать этот код в BigQuery напрямую или через CLI.

Я буду использовать приведенный ниже код для создания материализованного представления:

CREATE OR REPLACE MATERIALIZED VIEW
  `flights202301.flightstable_view` AS
SELECT
  FlightDate AS FL_DATE,
  Operating_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
  `secure-brook-395300.flights202301.flightstable`;

При использовании CLI не забудьте заменить Flight202301 на Flight202301cli или имя набора данных.

Можно наблюдать, что было создано новое представление.

Мы также можем сделать то же самое с помощью CLI. Сначала создайте файл sql, используя nano. Файл должен содержать приведенный ниже сценарий (аналогичный приведенному выше).

Затем запустите приведенный ниже код:

bq query --use_legacy_sql=false --project_id=secure-brook-395300 < createview.sql

После обновления веб-консоли мы увидим новое представление в наборе данных cli.

Чтобы выполнить предварительную проверку данных, мы можем попытаться получить 5 основных пунктов отправления с наибольшим количеством рейсов, используя приведенный ниже код sql.

SELECT
  origin,
  COUNT(*) AS num_flights
FROM
  `flights202301.flightstable_view`
GROUP BY
  origin
ORDER BY
  num_flights DESC
LIMIT
  5;

Результаты веб-консоли показаны ниже.

Мы можем сохранить приведенный выше код в top5origin.sql.

Мы можем использовать CLI с приведенным ниже кодом, чтобы получить тот же результат, что и в веб-консоли.

bq query --use_legacy_sql=false --project_id=secure-brook-395300 < top5origin.sql

Поиск оптимального порога (веб-консоль)

Далее мы будем использовать SQL, чтобы найти оптимальный порог из нескольких возможных порогов около 15 минут.

Мы введем приведенный ниже код в файл, известный какOptimthreshold.sql.

WITH
  contingency_table AS (
  SELECT
    THRESH,
    COUNTIF(dep_delay < THRESH
      AND arr_delay < 15) AS true_positives,
    COUNTIF(dep_delay < THRESH
      AND arr_delay >= 15) AS false_positives,
    COUNTIF(dep_delay >= THRESH
      AND arr_delay < 15) AS false_negatives,
    COUNTIF(dep_delay >= THRESH
      AND arr_delay >= 15) AS true_negatives,
    COUNT(*) AS total
  FROM
    `secure-brook-395300.flights202301.flightstable_view`,
    UNNEST([5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20]) AS THRESH
  WHERE
    arr_delay IS NOT NULL
    AND dep_delay IS NOT NULL
  GROUP BY
    THRESH )
SELECT
  ROUND((true_positives + true_negatives)/total, 2) AS accuracy,
  ROUND(false_positives/(true_positives+false_positives), 2) AS fpr,
  ROUND(false_negatives/(false_negatives+true_negatives), 2) AS fnr,
  *
FROM
  contingency_table

Мы ищем уровень ложноотрицательных результатов (FNR) менее 0,20.

Судя по выходным данным, показанным ниже, 15 минут — это порог, при котором FNR меньше. Судя по выходным данным, показанным ниже, 15 минут — это порог, при котором FNR меньше 0,20, чем 0,20.

Поиск оптимального порога (CLI)

Мы можем сделать то же самое и из CLI. Мы можем сохранить приведенный выше код вOptimthreshold.sql.