Практическая работа. YQL и работа с данными

В этом уроке вы освоите базовый набор операций для работы с данными с использованием YQL и консоли управления Yandex.Cloud. Подробная информация о YQL приведена в разделе Справочник YQL в документации.Чтобы начать, войдите в раздел Навигация консоли управления и откройте редактор SQL, нажав на кнопку SQL-запрос.

На прошлом уроке мы уже создали в нашей БД три таблицы, содержащие информацию о сериалах IT Crowd и Silicon Valley.

1. Добавим в БД еще одну таблицу с рейтингами эпизодов сериала IT Crowd на imbd.com.

YQL является диалектом SQL, поэтому многие инструкции в этих языках идентичны.Для создания таблицы вам понадобится сделать запрос к БД, содержащий инструкцию CREATE TABLE. Например, если бы мы хотели создать таблицу seasons (она уже есть в вашей БД), то SQL запрос выглядел бы следующим образом:

CREATE TABLE seasons
(
    series_id Uint64, 
    season_id Uint64, 
    first_aired Date, 
    last_aired Date, 
    title Utf8, 
        PRIMARY KEY (series_id, season_id)
); 

Обратите внимание, что в пределах директории YDB имена таблиц должны быть уникальны. Первичный ключ (PRIMARY KEY) — это столбец или комбинация столбцов, однозначно идентифицирующих каждую строку в таблице. Он может содержать только неповторяющиеся значения. Для таблицы YDB указание первичного ключа обязательно, при этом он может быть только один.Первичный ключ по сути является первичным индексом, который помогает СУБД быстрее обнаруживать отдельные записи в таблице и сокращает время выполнения запросов. Также в таблицу можно добавить один или несколько вторичных индексов. Они служат той же цели, но в отличие от первичного индекса могут содержать повторяющиеся значения. Добавить вторичные индексы можно в любой момент, когда возникнет необходимость, и это не вызовет деградацию производительности БД. Чтобы при создании таблицы добавить в нее вторичный индекс, используется такая конструкция:

INDEX <имя индекса> GLOBAL ON (<имя столбца1>, <имя столбца2>, ...) 

Вторичный индекс можно добавить и в уже существующую таблицу. Работа БД при этом не прерывается. В отличие от предыдущего случая в существующую таблицу можно добавлять только один вторичный индекс за раз. Делается это с помощью следующей команды:

ALTER TABLE <имя таблицы> ADD INDEX <имя индекса> GLOBAL ON (<имя столбца>); 

Задание 1: создайте таблицу ratings, в которой будут содержаться рейтинги всех эпизодов сериала IT Crowd, со столбцами season_id (Uint64), episodes_id (Uint64), title (Utf8), air_date (Date) и imbd_rating (Uint64) и вторичным индексом rating_index по полю imbd_rating.Ваш запрос должен быть написан так:

CREATE TABLE ratings (
    season_id Uint64, 
    episodes_id Uint64, 
    title Utf8, 
    air_date Date, 
    imbd_rating Uint64, 
        PRIMARY KEY (season_id, episodes_id), 
        INDEX rating_index GLOBAL ON (imbd_rating)
); 

2. Добавим в эту таблицу данные. Для вставки данных в YDB помимо обычной SQL инструкции INSERT также используются инструкции REPLACE и UPSERT.

При выполнении INSERT перед операцией записи выполняется операция чтения данных. Это позволяет убедиться, что уникальность первичного ключа будет соблюдена. При выполнении инструкций REPLACE и UPSERT осуществляется слепая запись.Инструкции REPLACE и UPSERT используются для добавления новой или изменения существующей строки по заданному значению первичного ключа. При операциях записи и изменения данных использование этих инструкций эффективнее.Если при выполнении этих инструкций строка с указанным значением первичного ключа не существует, то она будет создана. Если же такая строка существует, то значения ее столбцов будут заменены на новые. Отличие между REPLACE и UPSERT заключается в том, что первая из этих инструкций устанавливает значения столбцов, не участвующих в операции, в значения по умолчанию, а вторая такие значения не меняет.Одним запросом REPLACE, UPSERT или INSERT можно вставить в таблицу несколько строк.Например, если бы мы хотели добавить в таблицу series те данные, которые в ней сейчас содержатся, то SQL запрос выглядел бы так:

REPLACE INTO series (series_id, title, release_date, series_info) 
VALUES 
    ( 
        1, 
        "IT Crowd", 
        Date("2006-02-03"), 
        "The IT Crowd is a British sitcom produced by Channel 4, written by Graham Linehan, produced by Ash Atalla and starring Chris O'Dowd, Richard Ayoade, Katherine Parkinson, and Matt Berry."), 
    ( 
        2, 
        "Silicon Valley", 
        Date("2014-04-06"), 
        "Silicon Valley is an American comedy television series created by Mike Judge, John Altschuler and Dave Krinsky. The series focuses on five young men who founded a startup company in Silicon Valley." 
    ); 

Задание 2: добавьте в таблицу ratings данные из этого файла.Ваш запрос должен выглядеть таким образом:

REPLACE INTO ratings (season_id, episodes_id, title, air_date, imbd_rating) VALUES 
    (1, 1, "Yesterday's Jam", Date("2006-02-03"), 76),
    (1, 2, "Calamity Jen", Date("2006-02-03"), 82),
    (1, 3, "Fifty-Fifty", Date("2006-02-10"), 79),
    (1, 4, "The Red Door", Date("2006-02-17"), 80),
    (1, 5, "The Haunting of Bill Crouse", Date("2006-02-24"), 85),
    (1, 6, "Aunt Irma Visits", Date("2006-03-03"), 81),
    (2, 1, "The Work Outing", Date("2006-08-24"), 95),
    (2, 2, "Return of the Golden Child", Date("2007-08-31"), 82),
    (2, 3, "Moss and the German", Date("2007-09-07"), 82),
    (2, 4, "The Dinner Party", Date("2007-09-14"), 87),
    (2, 5, "Smoke and Mirrors", Date("2007-09-21"), 78),
    (2, 6, "Men Without Women", Date("2007-09-28"), 76),
    (3, 1, "From Hell", Date("2008-11-21"), 78),
    (3, 2, "Are We Not Men?", Date("2008-11-28"), 85),
    (3, 3, "Tramps Like Us", Date("2008-12-05"), 82),
    (3, 4, "The Speech", Date("2008-12-12"), 90),
    (3, 5, "Friendface", Date("2008-12-19"), 85),
    (3, 6, "Calendar Geeks", Date("2008-12-26"), 78),
    (4, 1, "Jen The Fredo", Date("2010-06-25"), 80),
    (4, 2, "The Final Countdown", Date("2010-07-02"), 84),
    (4, 3, "Something Happened", Date("2010-07-09"), 75),
    (4, 4, "Italian For Beginners", Date("2010-07-16"), 82),
    (4, 5, "Bad Boys", Date("2010-07-23"), 84),
    (4, 6, "Reynholm vs Reynholm", Date("2010-07-30"), 76); 

3. C помощью SQL запросов можно добавлять и удалять не только строки таблицы, но и столбцы. Для этого используется команда ALTER TABLE и фразы ADD COLUMN и DROP COLUMN.

Например, если вы хотите добавить в таблицу ratings столбец viewed с данными о том, какие эпизоды сериала вы уже посмотрели, то это можно сделать с помощью следующей команды.

ALTER TABLE ratings ADD COLUMN viewed Bool;  

Задание 3: Вы решили, что столбец с датой выхода эпизодов в таблице ratings не нужен, поскольку эта информация уже содержится в другой таблице. Удалите столбец air_date из таблицы ratings.Для этого понадобится выполнить такую команду:

ALTER TABLE ratings DROP COLUMN air_date;  

4. Теперь потренируемся извлекать данные из БД. Для этого используется команда SELECT. В простейшем случае ее синтаксис выглядит так:

SELECT <имя столбца1>, <имя столбца2>, ...
FROM <имя таблицы>; 

Например, чтобы выбрать всю информацию из таблицы seasons, нужно сделать следующий запрос к БД.

SELECT * FROM seasons; 

Если нужно выбрать из таблицы только те строки, которые удовлетворяют определенному условию, в запросе используют секцию WHERE. В этой секции должно находиться выражение, возвращающее логический результат. Обычно оно состоит из логических операций and, or, not и операций сравнения.Например, выбрать из таблицы episodes только первые эпизоды всех сезонов можно так:

SELECT * FROM episodes
WHERE episode_id = 1
;  

Запрос SELECT извлекает строки без определенного порядка. Чтобы отсортировать полученные данные нужным образом, в этот запрос включают секцию ORDER BY. В ней указывается список столбцов, которые будут определять порядок сортировки результатов запроса.Задание 4: получите список самых популярных (с рейтингом не менее 85) эпизодов сериала IT Crowd. При поиске используйте созданный ранее вторичный индекс rating_index. Чтобы упорядочить результаты по убыванию рейтинга используйте конструкцию ORDER BY … DESC.Используемый для этого запрос:

SELECT 
    season_id, 
    episodes_id, 
    title, 
    imbd_rating
FROM ratings VIEW rating_index 
WHERE 
    imbd_rating >= 85 
ORDER BY 
    imbd_rating DESC
;  

5. Для получения обобщенных сведений о содержащихся в таблице данных — например, о числе строк в таблице или среднем значении какого-либо выражения — в запрос SELECT включают агрегатные функции и секцию GROUP BY. Эта секция используется для агрегации внутри каждого ключа. Ключом является значение одной или более колонок, указанных в GROUP BY.

Примеры агрегатных функций:COUNT(*) — вычисляет число строк в таблице.MAX(expr) — находит максимум выражения expr по всем строкам.SUM(expr) — суммирует выражение expr по всем строкам. Тип выражения должен быть числовым.AVG(expr) — находит среднее значение выражения expr по всем строкам. Тип выражения должен быть числовым или интервалом.SOME(expr) — возвращает одно произвольное значение выражения по всем строкам.Результаты выполнения агрегатной функции выводятся в отдельном столбце. Чтобы задать этому столбцу имя, используют оператор AS. Конструкция может выглядеть, например, так:

SELECT 
    <имя столбца1>, 
    MAX(<имя столбца2>) AS max_value
...
; 

Задание 5: Напишите SQL запрос к таблице episodes, который выводит данные о числе эпизодов каждого сериала.Подсказка:Вам понадобится вычислить число строк для каждого значения столбца series_id и сгруппировать результаты по series_id.Ответ:

SELECT 
    series_id, 
    COUNT(*) AS total_episodes 
FROM episodes 
GROUP BY 
    series_id 
ORDER BY 
    series_id 
;  

Задание 6: Напишите SQL запрос, с помощью которого можно сравнить популярность сезонов сериала IT Crowd.Подсказка:Вам понадобится вычислить средний рейтинг эпизодов для каждого сезона и сгруппировать результаты по столбцу season_id.Ответ:

SELECT 
    season_id, 
    AVG (imbd_rating) AS avg_rating
FROM ratings 
GROUP BY season_id
ORDER BY avg_rating DESC; 

6. В реляционной БД таблицы логически связаны друг с другом. С помощью объединений (JOIN) можно получить данные из нескольких связанных друг с другом таблиц и представить их в виде одной результирующей таблицы.

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

1. После ключевого слова USING, например table1 AS a JOIN table2 AS b USING (foo). Это более короткий способ записи, удобный для простых случаев. Имена столбцов, по которым происходит объединение таблиц, должны быть одинаковы.
2. После ключевого слова ON (например, a JOIN b ON a.foo = b.bar). Этот способ позволяет использовать разные имена столбцов и указывать дополнительные условия по аналогии с WHERE.

Поскольку такие запросы затрагивают столбцы разных таблиц, имена столбцов должны содержать и имя таблицы (то есть, например, не просто series_id, а seasons.series_id).В YDB доступны следующие логические типы объединений:INNER (используется по умолчанию) — строки попадают в результат, только если значение ключевых колонок присутствует в обеих таблицах;FULL, LEFT и RIGHT — при отсутствии значения в обеих или в одной из таблиц включает строку в результат, но оставляет пустыми (NULL) колонки, соответствующие противоположной таблице.LEFT/RIGHT SEMI — одна сторона выступает как белый список (whitelist) ключей, её значения недоступны. В результат включаются столбцы только из одной таблицы, декартового произведения не возникает;LEFT/RIGHT ONLY — вычитание множеств по ключам (blacklist). Практически эквивалентно добавлению условия IS NULL на ключ противоположной стороны в обычном LEFT/RIGHT, но, как и в SEMI, нет доступа к значениям;CROSS — декартово произведение двух таблиц целиком без указания ключевых колонок, секция с ON/USING явно не пишется;EXCLUSION — обе стороны минус пересечение.Простой пример запроса с объединением таблиц приведен ниже.

SELECT
    sa.title AS season_title,
    sr.title AS series_title,
    sr.series_id, sa.season_id 
FROM seasons AS sa
INNER JOIN series AS sr ON sa.series_id = sr.series_id 
WHERE sa.season_id = 1
ORDER BY sr.series_id; 

Этот запрос извлекает из таблиц series и seasons сведения о первых сезонах всех сериалов и выводит объединенные данные в результирующей таблице.Задание 7: напишите запрос, который выводит таблицу, содержащую название сериала IT Crowd и названия всех его эпизодов (то есть, каждая строка итоговой таблице должна содержать название сериала и название отдельного эпизода).Это запрос может выглядеть следующим образом:

SELECT 
    sr.title AS series_title, 
    ep.title AS episode_title, 
    ep.season_id,     
    ep.episode_id 
FROM 
    series AS sr 
INNER JOIN 
    episodes AS ep 
ON sr.series_id = ep.series_id 
WHERE sr.series_id = 1 
ORDER BY 
    ep.season_id,     
    ep.episode_id 
;