1 of 42

Data Engineer. Лекция 2

  • Лектор: Анатолий Бардуков�
  • tg для связи: @sindb , либо в чате�
  • Ассистенты: Андрей @maninoffice и Максим @mlixakov�

Наливайте чай,

приветствуйте друг друга в чате и приготовьтесь хорошо провести время

2 of 42

Про что сегодня �расскажем

  • PostgreSQL
  • Схемы данных
  • SQL
  • Нормальная форма и нормализация данных
  • Slowly Changing Dimensions aka SCD

Наливайте чай,

приветствуйте друг друга в чате и приготовьтесь хорошо провести время

3 of 42

Материалы для самостоятельной работы.

  1. Для совсем-совсем начинающих – учимся алгоритмическому мышлению:
    • 7 Billion Humans
    • Human Resource Machine
    • Coding Game – здесь от самых простых задач до полной жести. Очень рекомендую всем.
  2. Для желающих прокачать логическое мышление:
    • Мартин Гарднер. Математические досуги.
    • Мартин Гарднер. Математические головоломки и развлечения.
    • Мартин Гарднер. Математические чудеса и тайны.
    • Мартин Гарднер. А ну-ка, догадайся!
    • Мартин Гарднер. Загадки Сфинкса и другие математические головоломки.
    • Яков Перельман. Занимательная … (все что угодно).
    • Яков Перельман. Первая и вторая сотня головоломок.
    • Яков Перельман. Научные задачи и развлечения.
  3. Учите и пользуйтесь Google Sheets. Решайте любые повседневные

задачи, научитесь хорошо пользоваться формулами, можно посмотреть

на JavaScript внутри Sheets.

Материалы для самостоятельной работы (общие)

4 of 42

  1. SQL Murder Mystery – немного развлечения перед работой.
  2. Практика SQL:
    • sql-ex.ru – исключительно хороший тренажер.
    • sql-tutorial.ru – документация к нему.
  3. Концепции построения баз данных от Oracle.
    • разделы 4-6 – таблицы, индексы, представления, партиции – для общего ознакомления с объектами, которые бывают в базах данных;
    • раздел 7 изучать внимательно;
    • раздел 8 – прочитать про справочники СУБД, тоже для общего ознакомления;
    • раздел 9 – собственно особенности SQL для Orcale;
    • раздел 10 – только для тех, кто хочет посвятить себя работе с Oracle;
    • раздел 11-12 – сейчас прочитать для общего развития, далее после 3-6 месяцев работы вернуться и изучить детально.
  4. Документация PostgreSQL.
  5. Справочник команд *nix. Учебник по bash: попроще и посложнее.
  6. Интерактивный тур по git.
  7. Регулярные выражения: введение, тренировка-развлечение, платформа проверки регулярных выражений.

Материалы для самостоятельной работы

5 of 42

  1. Установка СУБД PostgreSQL локально – страница с дистрибутивами.
  2. Виртуальная машина Oracle (ODI).
  3. Книги по SQL и базовой архитектуре баз данных:
    • Мартин Грубер. Понимание SQL.
    • К.Дж. Дейт. Введение в системы баз данных.
  4. Книги по архитектуре хранилищ данных (только для тех, кто пришел учиться строить хранилище в своей компании):
    • Vincent Rainardi Building a Data Warehouse With Examples in SQL Server
    • Ralph Kimball, Margy Ross (2013). The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling
    • Building the Data Warehouse, Fourth Edition W. H. Inmon
    • Haq, Q. M. R. U. (2016). Data Mapping for Data Warehouse Design.
    • Alex Gorelik (2019). The Enterprise Big Data Lake: Delivering the Promise of Big Data and Data Science
  5. Прочее
    • М. Клеппман. Высоконагруженные приложения. Программирование, масштабирование, поддержка
    • Т. Кормен. Алгоритмы: построение и анализ

Материалы для самостоятельной работы (глубокое погружение)

6 of 42

Разница в терминах:

​

  • База данных – собственно систематизированные данные.
  • СУБД (система управления базами данных) – программы, обеспечивающие возможность создания, управления и использования баз данных.
  • Обычно говорят «база данных» и из контекста понятно о чем идет речь.

​

Типы баз данных:

​

  • Простые структуры (например, простые файлы).
  • Иерархические (например, дерево каталогов).
  • Реляционные (с ними работаем в курсе, пояснения далее).
  • NoSQL – (key-value, документные, графовые).

Виды баз данных

7 of 42

  1. OLTP (Online Transaction Processing) – транзакционная система, предназначена для быстрого выполнения коротких операций. Типичные представители:

​

    • Oracle
    • SQL Server (MS SQL)
    • MySQL
    • PostgreSQL

​

  1. OLAP (Online Analytical Processing) – система для подготовки суммарной

(агрегированной) информации на основе больших массивов данных.

Типичные представители:

​

    • Oracle
    • Teradata
    • Vertica
    • Greenplum
    • Стек Hadoop

Виды нагрузки

8 of 42

Привычное представление таблицы:

Основные термины

Но нужно привыкать к следующему представлению:

9 of 42

Сущность (entity) – это нечто, о чем нужно хранить сведения в базе данных. Сущность в базе данных представлена в виде таблицы.

​

Атрибут – это описательные свойства сущности. В базе данных представлены в виде столбцов таблицы (также их называют поля, хотя вообще-то поле – это ячейка таблицы).

​

Запись – строка данных. Важный нюанс: в базах данных строки по умолчанию не имеют никакого порядка выдачи.

​

Связь – логическая связь между двумя и более сущностями.

​

Первичный ключ. Атрибут, точно идентифицирующий запись.

Классификация: простой и составной, естественный и суррогатный.

Ключ должен быть непустым, уникальным и не должен меняться.

​

​

Основные термины

Приведите пример сущности и ее первичного ключа в скобках. Например, для таблицы с прошлого слайда: сотрудники (табельный номер).

10 of 42

ER-модель

  1. Приведите пример связи один-ко-многим, например для модели выше: покупатель (customer_id) – страна (countrycode)
  2. Приведите пример связи многие-ко-многим, например для модели выше: продукт (product_id) – заказы (order_id)

​

​

11 of 42

  1. SQL – Structured Query Language – декларативный язык

программирования для манипуляции данными в базах данных.

​

  1. Есть диалекты SQL (практически у каждого вендора),

но также существует и стандарт SQL:2016.

Что такое SQL?

12 of 42

  1. DDL – Data Definition Language (сильно зависит от СУБД)
    • CREATE
    • DROP
    • ALTER
    • TRUNCATE

​

  • DML – Data Manipulation Language (во многом следует стандарту)
    • SELECT
    • INSERT
    • UPDATE
    • DELETE
    • MERGE

​

  • DCL – Data Control Language (ролевая модель специфична для каждой СУБД)
    • GRANT
    • REVOKE

​

  • TCL – Transaction Control Language (стандарт)
    • COMMIT
    • ROLLBACK

Компоненты SQL

13 of 42

CREATE TABLE SchemaName.TableName (

FieldName1 INTEGER,

FieldName2 VARCHAR(50) );

​

INSERT INTO SchemaName.TableName( FieldName1, FieldName2 )

VALUES ( 1, 'Text' );

​

SELECT * FROM SchemaName.TableName;

​

​

SQL. Первые шаги. Создаем и наполняем таблицу.

Важное дополнение про завершение транзакции.

Правило для тех, кто не хочет вникать – если у вас не включен режим AutoCommit, то после каждого действия INSERT, UPDATE, DELETE необходимо подать команду COMMIT.

14 of 42

CREATE TABLE SchemaName.TableName (

FieldName1 INTEGER,

FieldName2 VARCHAR(50) );

​

INSERT INTO SchemaName.TableName( FieldName1, FieldName2 )

VALUES ( 1, 'Text' );

​

SELECT * FROM SchemaName.TableName;

​

​

SQL. Первые шаги. Создаем и наполняем таблицу.

Давайте договоримся о правилах именования таблиц, поскольку схема у вас одна на всех. Прошу при создании своих таблиц (и вообще любых объектов) использовать префикс из четырех букв, полученных у преподавателя. Например, я буду использовать префикс SIND_

Создайте свою таблицу. Заполните ее данными. Проверьте что данные появились.

15 of 42

  1. INTEGER, DECIMAL, NUMERIC, NUMBER – целое число;
  2. VARCHAR( length ), в Oracle VARCHAR2( length ) – строка переменной длины;
  3. CHAR( length ) – строка фиксированной длины;
  4. DECIMAL( scale, precision ), NUMERIC( scale, precision ), NUMBER( scale, precision ) – дробное число
  5. DATE, TIME, TIMESTAMP, DATETIME – дата, время в разных конфигурациях.
  6. И многие, многие другие, которые используются очень нечасто (в DWH почти никогда). Читайте документацию PostgreSQL если хотите расширить кругозор. В каждой СУБД есть свой набор типов, но предыдущие 5 обычно есть в любой.

SQL. Типы данных.

16 of 42

SQL. Пример неаккуратной работы с типом.

17 of 42

  1. INTEGER, DECIMAL, NUMERIC, NUMBER – целое число;
  2. VARCHAR( length ), в Oracle VARCHAR2( length ) – строка переменной длины;
  3. CHAR( length ) – строка фиксированной длины;
  4. DECIMAL( scale, precision ), NUMERIC( scale, precision ), NUMBER( scale, precision ) – дробное число
  5. DATE, TIME, TIMESTAMP, DATETIME – дата, время в разных конфигурациях.
  6. И многие, многие другие, которые используются очень нечасто (в DWH почти никогда). Читайте документацию PostgreSQL если хотите расширить кругозор. В каждой СУБД есть свой набор типов, но предыдущие 5 обычно есть в любой.

SQL. Типы данных.

18 of 42

INSERT INTO SchemaName.TableName ( FieldName1, FieldName2 )

VALUES ( 1, 'Text' );

​

INSERT INTO SchemaName.TableName ( FieldName1, FieldName2 )

VALUES ( 1, 'Text' ), ( 2, 'Not text’ );

​

INSERT INTO SchemaName.TableName ( FieldName1, FieldName2 )

SELECT

OtherField1,

OtherField2

FROM SchemaName.OtherTable;

​

INSERT INTO SchemaName.TableName

SELECT * FROM SchemaName.OtherTable;

​

SQL. Способы вставки данных.

19 of 42

UPDATE SchemaName.TableName

SET FieldName2 = 'NewText'

WHERE FieldName1 = 1;

​

DELETE FROM SchemaName.TableName WHERE FieldName1 = 1;

DELETE FROM SchemaName.TableName;

​

TRUNCATE TABLE SchemaName.TableName;

​

SQL. Способы изменения и удаления данных

20 of 42

CREATE TABLE SchemaName.TableName (

FieldName1 INTEGER,

FieldName2 VARCHAR(50) );

​

CREATE TABLE SchemaName.NewTable AS

SELECT * FROM SchemaName.OldTable;

​

CREATE TABLE SchemaName.NewTable AS

SELECT * FROM SchemaName.OldTable WHERE 1=0;

​

SQL. Способы создания таблиц

Создайте копию таблицы hr.employees, включив в нее только сотрудников, чья зарплата более 10000.

21 of 42

Представление (на сленге «вьюха») – SQL код, сохраненный в справочнике базы данных под своим собственным именем. Данный код исполняется каждый раз при обращении к представлению. Обратите внимание, что представление само по себе не хранит данные, только SQL код.

​

Представления используются для двух целей:

  • замаскировать нежелательные данные (best practice)
  • спрятать в представление бизнес-логику (worth practice)

​

CREATE VIEW SchemaName.ViewName AS

SELECT * FROM SchemaName.TableName;

CREATE OR REPLACE VIEW SchemaName.ViewName AS

SELECT * FROM SchemaName.TableName WHERE 1=1;

DROP VIEW SchemaName.ViewName;

​

SQL. Представления

Создайте представление над таблицей hr.employees, скрывающее данные о зарплате сотрудников. В представлении должны быть поля first_name, last_name, email, phone_number.

22 of 42

Нормальные формы и нормализация

Наливайте чай,

приветствуйте друг друга в чате и приготовьтесь хорошо провести время

23 of 42

Нормальные формы и нормализация

  • Переменная отношения находится в первой нормальной форме (1НФ) тогда и только тогда, когда в любом допустимом значении отношения каждый его кортеж содержит только одно значение для каждого из атрибутов. �
  • Переменная отношения находится во второй нормальной форме тогда и только тогда, когда она находится в первой нормальной форме и каждый неключевой атрибут неприводимо (функционально полно) зависит от её потенциального ключа. �
  • Переменная отношения находится в третьей нормальной форме тогда и только тогда, когда она находится во второй нормальной форме, и отсутствуют транзитивные функциональные зависимости неключевых атрибутов от ключевых.

Наливайте чай,

приветствуйте друг друга в чате и приготовьтесь хорошо провести время

24 of 42

Нормальные формы и нормализация

​

Приведите к третьей нормальной форме предложенную таблицу (перед работой скопируйте ее себе):

​

https://docs.google.com/spreadsheets/d/1pSpLZfU6lqMj5nHpoB8SbUl0HnPrDynnxYU9m5mgtwA

Наливайте чай,

приветствуйте друг друга в чате и приготовьтесь хорошо провести время

25 of 42

Нормальные формы и нормализация

​

Приведите к третьей нормальной форме предложенную таблицу (перед работой скопируйте ее себе):

​

https://docs.google.com/spreadsheets/d/1pSpLZfU6lqMj5nHpoB8SbUl0HnPrDynnxYU9m5mgtwA

​

Чуть больше пояснений https://habr.com/ru/articles/254773/

​

https://triptonkosti.ru/17-foto/pri-dostizhenii-kakoj-normalnoj-formy-nf-obychno-ostanavlivayut-process-normalizacii-shemy-bd.html

​

Наливайте чай,

приветствуйте друг друга в чате и приготовьтесь хорошо провести время

26 of 42

  1. DDL – Data Definition Language (сильно зависит от СУБД)
    • CREATE
    • DROP
    • ALTE
    • TRUNCATE

​

  1. DML – Data Manipulation Language (во многом следует стандарту)
    • SELECT
    • INSERT
    • UPDATE
    • DELETE
    • MERGE

​

  1. DCL – Data Control Language (ролевая модель специфична для каждой СУБД)
    • GRANT
    • REVOKE

​

  • TCL – Transaction Control Language (стандарт)
    • COMMIT
    • ROLLBACK

Компоненты SQL

Наливайте чай,

приветствуйте друг друга в чате и приготовьтесь хорошо провести время

27 of 42

Best practice:

  • DBeaver – IDE (integrated development environment) – интегрированная среда разработки, где вы пишете код, и тут же его исполняете.
  • Любая другая IDE, которая может подключиться к СУБД PostgreSQL.
  • Любой редактор «плоских» файлов, то есть без разметки:
    • Notepad++
    • Sublime
    • Atom

​

Категорически нельзя редактировать код в MS Word, MS Power Point и

другим подобным редакторам WYSIWYG. MS Excel может использоваться для

кодогенерации, но очень аккуратно.

​

Прежде чем начать. Где редактируем код?

Минутка юмора: почему MS Word является лучшей средой разработки в мире.

28 of 42

CREATE TABLE SchemaName.TableName (

FieldName1 INTEGER,

FieldName2 VARCHAR(50) );

​

INSERT INTO SchemaName.TableName( FieldName1, FieldName2 )

VALUES ( 1, 'Text' );

​

SELECT * FROM SchemaName.TableName;

​

​

SQL. Первые шаги. Создаем и наполняем таблицу.

Важное дополнение про завершение транзакции.

Правило для тех, кто не хочет вникать – если у вас не включен режим AutoCommit, то после каждого действия INSERT, UPDATE, DELETE необходимо подать команду COMMIT.

29 of 42

SQL. Ограничения целостности данных

Виды ограничений целостности:

  • NOT NULL
  • Unique
  • Primary Key
  • Foreign Key
  • Check

​

​

30 of 42

CREATE TABLE articles (

articleid INTEGER CONSTRAINT pk_articles_articleid PRIMARY KEY,

title VARCHAR( 100 ) NOT NULL CONSTRAINT uq_articles_title UNIQUE,

text VARCHAR( 1000 ) NOT NULL,

published CHAR( 1 ) CHECK ( UPPER( published ) IN ( 'Y', 'N' ) ),

blogid INTEGER

);

​

SQL. Ограничения целостности данных

31 of 42

CREATE TABLE blogs (

blogid INTEGER CONSTRAINT pk_blogs_blogid PRIMARY KEY,

name VARCHAR( 100 ) NOT NULL CONSTRAINT uq_blogs_name UNIQUE

);

​

ALTER TABLE articles

ADD CONSTRAINT fk_articles_blogs FOREIGN KEY ( blogid )

REFERENCES blogs( blogid );

​

ALTER TABLE articles ALTER COLUMN text DROP NOT NULL;

SQL. Ограничения целостности данных

32 of 42

SQL. Задачи на ограничения целостности данных

Рубрика ээээксперименты!

​

  1. Создать таблицу с констрейнтом NOT NULL. Вставить в нее строку с NULL. Какая ошибка? Снять констрейнт. �
  2. Создать таблицу с констрейнтом UNIQUE и записать туда одинаковые значения. Какая ошибка?�
  3. Создать таблицу без констрейнтов и наложить констрейнт NOT NULL, причем в двух случаях – когда в таблице есть NULL и когда нет.�
  4. Создать две таблицы и связать их FOREIGN KEY. Попытаться сослаться на поле, не являющееся PRIMARY KEY. Вставить в таблицу с FOREIGN KEY значение, отсутствующее в справочнике.

33 of 42

SQL. Ограничения целостности данных

Виды ограничений целостности:

  • NOT NULL
  • Unique
  • Primary Key
  • Foreign Key
  • Check

​

​

34 of 42

SQL. Ограничения целостности данных

CREATE TABLE articles (

articleid INTEGER CONSTRAINT pk_articles_articleid PRIMARY KEY,

title VARCHAR( 100 ) NOT NULL CONSTRAINT uq_articles_title UNIQUE,

text VARCHAR( 1000 ) NOT NULL,

published CHAR( 1 ) CHECK ( UPPER( published ) IN ( 'Y', 'N' ) ),

blogid INTEGER

);

​

35 of 42

SQL. Ограничения целостности данных

CREATE TABLE blogs (

blogid INTEGER CONSTRAINT pk_blogs_blogid PRIMARY KEY,

name VARCHAR( 100 ) NOT NULL CONSTRAINT uq_blogs_name UNIQUE

);

​

ALTER TABLE articles

ADD CONSTRAINT fk_articles_blogs FOREIGN KEY ( blogid )

REFERENCES blogs( blogid );

​

ALTER TABLE articles ALTER COLUMN text DROP NOT NULL;

36 of 42

SQL. Задачи на ограничения целостности данных

Рубрика ээээксперименты!

​

  1. Создать таблицу с констрейнтом NOT NULL. Вставить в нее строку с NULL. Какая ошибка? Снять констрейнт.
  2. Создать таблицу с констрейнтом UNIQUE и записать туда одинаковые значения. Какая ошибка?
  3. Создать таблицу без констрейнтов и наложить констрейнт NOT NULL, причем в двух случаях – когда в таблице есть NULL и когда нет.
  4. Создать две таблицы и связать их FOREIGN KEY. Попытаться сослаться на поле, не являющееся PRIMARY KEY. Вставить в таблицу с FOREIGN KEY значение, отсутствующее в справочнике.

37 of 42

Нормальные формы и нормализация

Домашнее задание: создайте структуру базы данных по предложенной тематике

(дедлайн 2 недели)

К базе данных предъявляются следующие требования:

  • должно быть не менее 4 сущностей (включая технические объекты);
  • должна быть хотя бы одна связь один-ко-многим;
  • должна быть хотя бы одна связь многие-ко-многим;
  • все отношения приведены к 3НФ.

​

Предметная область (выберите одно):

1. Продажа автомобилей.

2. Приют для животных.

3. Железнодорожные перевозки.

4. Служба доставки.

5. Организация марафона.

Требования к оформлению:

  • ER-диаграмму необходимо составлять на app.dbdesigner.net, на проверку нужно прислать ссылку на диаграмму
  • Также необходимо сделать SQL скрипт с DDL для создания таблиц (обращаем внимание на ограничения) и заполнение примерами данных

​

Наливайте чай,

приветствуйте друг друга в чате и приготовьтесь хорошо провести время

38 of 42

SCD версионность

SCD0

​

​

Неизменяемые данные

​

39 of 42

SCD версионность

SCD1

​

​

​

​

40 of 42

SCD версионность

SCD2

​

​

​

​

41 of 42

SCD версионность

SCD2

​

​

​

​

42 of 42

Типовые действия с форматом SCD2

  • Выделение актуального среза
  • Выделение среза на дату
  • Создание таблицы SCD2 на основе фактовой таблицы
  • Соединение фактовой таблицы и SCD2 таблицы
  • Операция HistGroup
  • Операция HistMerge
  • Операция FDTD join

​