CS > DB & SQL > Flashcards
DB & SQL Flashcards
Архитектура СУБД
- Ядро - процессы, сеть, память, файловая система
- Диспетчер данных - транзакции, кэш
- Диспетчер запросов - парсер запросов, оптимизатор, исполнитель
- Инструменты для служебных операций - резервное копирование, восстановление, мониторинг
run mysql in terminal
mysql -u root -p
create a db
CREATE DATABASE database_name;
delete a db
DROP DATABASE database_name;
switching to a db
USE database_name;
create a table
CREATE TABLE tablename
(
column_name data_type,
column_name data_type
);show a table
desc table;
show tables;
delete a table
drop table;
inserting data
INSERT INTO table_name(column_name) VALUES (data);
a table with primary key constraint
CREATE TABLE unique_cats
(
cat_id INT NOT NULL,
name VARCHAR(100),
age INT,
PRIMARY KEY (cat_id)
);aliases
SELECT cat_id AS id, name FROM cats;
updating data
UPDATE table SET column WHERE condition;
run SQL files from mysql cli
source folder/script.sql
combine data from several columns
SELECT concat(column1, ‘ ‘, column2) AS [name] FROM table;
combine data from several columns using separator
select concat_ws(separator, col1, col2, …) as [name] from table;
how to use substring
select substring(text|column, start, end) as [name] from table; select substring(text|column, start) as [name] from table; select substring(text|column, -start) as [name] from table;
how to replace symbols in a string
select replace(text|column, old, new) as [name] from table;
reverse strings
select reverse(string|column)
count characters in a string
select char_length(string|column)
change case of a string
select upper|lower(string|column)
get unique values of a column
select distinct column from table;
sort results of selections
select column from table order by condition;
select column from table order by condition desc;
select col1, col2, col3 from table order by 2;
how to limit number of results
select column from table limit x;
select column from table limit start, end;
non strict search
select * from table where column like ‘condition’
count rows
select count(*) from table;
combine rows
SELECT author_lname, COUNT(*)
FROM books GROUP BY author_lname;
subqueries (min and max)
SELECT * FROM books
WHERE pages = (SELECT Min(pages)
FROM books);
decimal
decimal(max num of digits, digits after decimal point)
date math
SELECT birthdt, DATE_ADD(birthdt, INTERVAL 1 MONTH) FROM people;
SELECT birthdt, birthdt - INTERVAL 5 MONTH FROM people;
timestamps
CREATE TABLE comments (
content VARCHAR(100),
created_at TIMESTAMP DEFAULT NOW()
);
CREATE TABLE comments2 (
content VARCHAR(100),
changed_at TIMESTAMP DEFAULT NOW() ON UPDATE CURRENT_TIMESTAMP
);
formatting dates
SELECT DATE_FORMAT(birthdt, ‘%m/%d/%Y’) FROM people;
case statements
SELECT title, stock_quantity,
CASE
WHEN stock_quantity <= 50 THEN ‘’
WHEN stock_quantity <= 100 THEN ‘’
ELSE ‘’
END AS STOCK
FROM books;
IF(count > 0, ‘active’, ‘inactive’) as status
using foreign keys
CREATE TABLE customers(
id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(100),
last_name VARCHAR(100),
email VARCHAR(100)
);
CREATE TABLE orders(
id INT AUTO_INCREMENT PRIMARY KEY,
order_date DATE,
amount DECIMAL(8,2),
customer_id INT,
FOREIGN KEY(customer_id) REFERENCES customers(id)
);Inner Joins
– IMPLICIT INNER JOIN
SELECT * FROM customers, orders
WHERE customers.id = orders.customer_id;
– EXPLICIT INNER JOIN
SELECT * FROM customers
JOIN orders
ON customers.id = orders.customer_id;
replacing nulls
IFNULL(amount, 0)
Left join
SELECT * FROM customers
LEFT JOIN orders
ON customers.id = orders.customer_id;
Right join
SELECT * FROM customers
RIGHT JOIN orders
ON customers.id = orders.customer_id;
deleting data from tables with foreign key constraints
CREATE TABLE customers(
id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(100),
last_name VARCHAR(100),
email VARCHAR(100)
);
CREATE TABLE orders(
id INT AUTO_INCREMENT PRIMARY KEY,
order_date DATE,
amount DECIMAL(8,2),
customer_id INT,
FOREIGN KEY(customer_id)
REFERENCES customers(id)
ON DELETE CASCADE
);
exclude duplicate connections
CREATE TABLE likes (
user_id INTEGER NOT NULL,
photo_id INTEGER NOT NULL,
created_at TIMESTAMP DEFAULT NOW(),
FOREIGN KEY(user_id) REFERENCES users(id),
FOREIGN KEY(photo_id) REFERENCES photos(id),
PRIMARY KEY(user_id, photo_id)
);condition for aggregate values
SELECT username,
Count(*) AS num_likes
FROM users
INNER JOIN likes
ON users.id = likes.user_id
GROUP BY likes.user_id
HAVING num_likes = (SELECT Count(*)
FROM photos);connect to MySQL db using Node.js
var mysql = require(‘mysql’)
var connection = mysql.createConnection({
host : 'localhost',
user : 'root',
password: '/password/',
database: 'database_name',
insecureAuth: true
});
q = 'SELECT 1 + 1 AS solution'
connection.query(q, function (error, results, fields) {
if (error) throw error;
console.log(results[0].solution);
});
connection.end();
insert data using Node.js
var person = {
email: faker.internet.email(),
created_at: faker.date.past()
};
var end_result = connection.query(‘INSERT INTO users SET ?’, person, function(err, result) {
if (err) throw err;
console.log(result);
});
bulk inserting using Node.js
var data = [];
for (i = 0; i < 500; i++) {
data.push([
faker.internet.email(),
faker.date.past()
])
}
var q = 'INSERT INTO users (email, created_at) VALUES ?';
connection.query(q, [data], function (error, results, fields) {
if (error) throw error;
console.log(results);
});
connection.end();
triggers
DELIMITER $$
CREATE TRIGGER trigger_name
trigger_time trigger_event ON table_name FOR EACH ROW
BEGIN
END;
\$\$
DELIMITER ;
DELIMITER $$
CREATE TRIGGER create_unfollow
AFTER DELETE ON follows FOR EACH ROW
BEGIN
INSERT INTO unfollows
SET follower_id = OLD.follower_id,
followee_id = OLD.followee_id;
END\$\$
DELIMITER ;
Second Highest Salary
# task # rank
Write a SQL query to get the second highest salary from the Employee table.
\+----+--------+ | Id | Salary | \+----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | \+----+--------+ For example, given the above Employee table, the query should return 200 as the second highest salary. If there is no second highest salary, then the query should return null.
\+---------------------+ | SecondHighestSalary | \+---------------------+ | 200 | \+---------------------+
SELECT
(SELECT DISTINCT
Salary
FROM
Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1) AS SecondHighestSalary
;
// OFFSET - смещение строкиNth Highest Salary
# task # rank # functions
Write a SQL query to get the nth highest salary from the Employee table.
\+----+--------+ | Id | Salary | \+----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | \+----+--------+ For example, given the above Employee table, the nth highest salary where n = 2 is 200. If there is no nth highest salary, then the query should return null.
\+------------------------+ | getNthHighestSalary(2) | \+------------------------+ | 200 | \+------------------------+
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
select distinct e1.salary from Employee e1 where N-1 =
(select count(distinct e2.Salary) from Employee e2 where e1.Salary < e2.Salary)
);
END
Rank Scores # task # rank Write a SQL query to rank scores. If there is a tie between two scores, both should have the same ranking. Note that after a tie, the next ranking number should be the next consecutive integer value. In other words, there should be no "holes" between ranks.
\+----+-------+ | Id | Score | \+----+-------+ | 1 | 3.50 | | 2 | 3.65 | | 3 | 4.00 | | 4 | 3.85 | | 5 | 4.00 | | 6 | 3.65 | \+----+-------+ For example, given the above Scores table, your query should generate the following report (order by highest score):
\+-------+------+ | Score | Rank | \+-------+------+ | 4.00 | 1 | | 4.00 | 1 | | 3.85 | 2 | | 3.65 | 3 | | 3.65 | 3 | | 3.50 | 4 | \+-------+------+
SELECT
S1.Score,
(SELECT COUNT(DISTINCT Score) FROM Scores AS S2 WHERE S2.Score >= S1.Score) AS 'Rank'
FROM Scores AS S1
ORDER BY Score DESC;Consecutive numbers
# task #numbers
Write a SQL query to find all numbers that appear at least three times consecutively.
\+----+-----+ | Id | Num | \+----+-----+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 1 | | 6 | 2 | | 7 | 2 | \+----+-----+ For example, given the above Logs table, 1 is the only number that appears consecutively for at least three times.
\+-----------------+ | ConsecutiveNums | \+-----------------+ | 1 | \+-----------------+
SELECT DISTINCT
l1.Num AS ConsecutiveNums
FROM
Logs l1,
Logs l2,
Logs l3
WHERE
l1.Id = l2.Id - 1
AND l2.Id = l3.Id - 1
AND l1.Num = l2.Num
AND l2.Num = l3.Num;Employees Earning More Than Their Managers
# task
The Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id.
\+----+-------+--------+-----------+ | Id | Name | Salary | ManagerId | \+----+-------+--------+-----------+ | 1 | Joe | 70000 | 3 | | 2 | Henry | 80000 | 4 | | 3 | Sam | 60000 | NULL | | 4 | Max | 90000 | NULL | \+----+-------+--------+-----------+ Given the Employee table, write a SQL query that finds out employees who earn more than their managers. For the above table, Joe is the only employee who earns more than his manager.
\+----------+ | Employee | \+----------+ | Joe | \+----------+
SELECT name AS Employee FROM Employee e1 where Salary > (SELECT Salary FROM Employee e2 where e2.id = e1.ManagerId);
Duplicate Emails
# task #duplicate
Write a SQL query to find all duplicate emails in a table named Person.
\+----+---------+ | Id | Email | \+----+---------+ | 1 | a@b.com | | 2 | c@d.com | | 3 | a@b.com | \+----+---------+ For example, your query should return the following for the above table:
\+---------+ | Email | \+---------+ | a@b.com | \+---------+ Note: All emails are in lowercase.
SELECT Email from Person GROUP BY Email HAVING COUNT(*) > 1;
Department Highest Salary
# task # max
The Employee table holds all employees. Every employee has an Id, a salary, and there is also a column for the department Id.
\+----+-------+--------+--------------+ | Id | Name | Salary | DepartmentId | \+----+-------+--------+--------------+ | 1 | Joe | 70000 | 1 | | 2 | Jim | 90000 | 1 | | 3 | Henry | 80000 | 2 | | 4 | Sam | 60000 | 2 | | 5 | Max | 90000 | 1 | \+----+-------+--------+--------------+ The Department table holds all departments of the company.
\+----+----------+ | Id | Name | \+----+----------+ | 1 | IT | | 2 | Sales | \+----+----------+ Write a SQL query to find employees who have the highest salary in each of the departments. For the above tables, your SQL query should return the following rows (order of rows does not matter).
\+------------+----------+--------+ | Department | Employee | Salary | \+------------+----------+--------+ | IT | Max | 90000 | | IT | Jim | 90000 | | Sales | Henry | 80000 | \+------------+----------+--------+
SELECT
Department.name AS 'Department',
Employee.name AS 'Employee',
Salary
FROM
Employee
JOIN
Department ON Employee.DepartmentId = Department.Id
WHERE
(Employee.DepartmentId , Salary) IN
( SELECT
DepartmentId, MAX(Salary)
FROM
Employee
GROUP BY DepartmentId
)
;Rising Temperature
# task
Given a Weather table, write a SQL query to find all dates’ Ids with higher temperature compared to its previous (yesterday’s) dates.
+———+——————+——————+
| Id(INT) | RecordDate(DATE) | Temperature(INT) |
+———+——————+——————+
| 1 | 2015-01-01 | 10 |
| 2 | 2015-01-02 | 25 |
| 3 | 2015-01-03 | 20 |
| 4 | 2015-01-04 | 30 |
+———+——————+——————+
For example, return the following Ids for the above Weather table:
\+----+ | Id | \+----+ | 2 | | 4 | \+----+
select w1.Id from Weather w1, Weather w2 where w1.temperature > w2.temperature and TO_DAYS(w1.RecordDate)-TO_DAYS(w2.RecordDate)=1;
Swap Salary # task #swap Given a table salary, such as the one below, that has m=male and f=female values. Swap all f and m values (i.e., change all f values to m and vice versa) with a single update statement and no intermediate temp table.
Note that you must write a single update statement, DO NOT write any select statement for this problem.
Example:
| id | name | sex | salary | |----|------|-----|--------| | 1 | A | m | 2500 | | 2 | B | f | 1500 | | 3 | C | m | 5500 | | 4 | D | f | 500 | After running your update statement, the above salary table should have the following rows: | id | name | sex | salary | |----|------|-----|--------| | 1 | A | f | 2500 | | 2 | B | m | 1500 | | 3 | C | f | 5500 | | 4 | D | m | 500 |
update salary
set sex =
case sex
when 'm' then 'f'
when 'f' then 'm'
end;Exchange Seats # task # swap Mary is a teacher in a middle school and she has a table seat storing students' names and their corresponding seat ids. The column id is continuous increment. Mary wants to change seats for the adjacent students. Can you write a SQL query to output the result for Mary?
\+---------+---------+ | id | student | \+---------+---------+ | 1 | Abbot | | 2 | Doris | | 3 | Emerson | | 4 | Green | | 5 | Jeames | \+---------+---------+ For the sample input, the output is:
\+---------+---------+ | id | student | \+---------+---------+ | 1 | Doris | | 2 | Abbot | | 3 | Green | | 4 | Emerson | | 5 | Jeames | \+---------+---------+ Note: If the number of students is odd, there is no need to change the last one's seat.
// используем остаток от деления: если ид нечетный и не последний, прибавляем 1, если нечетный и последний, оставляем как есть, иначе отнимаем 1
SELECT
(CASE
WHEN MOD(id, 2) != 0 AND counts != id THEN id + 1
WHEN MOD(id, 2) != 0 AND counts = id THEN id
ELSE id - 1
END) AS id,
student
FROM
seat,
(SELECT
COUNT(*) AS counts
FROM
seat) AS seat_counts
ORDER BY id ASC;Department Top Three Salaries
# task # rank
The Employee table holds all employees. Every employee has an Id, and there is also a column for the department Id.
\+----+-------+--------+--------------+ | Id | Name | Salary | DepartmentId | \+----+-------+--------+--------------+ | 1 | Joe | 85000 | 1 | | 2 | Henry | 80000 | 2 | | 3 | Sam | 60000 | 2 | | 4 | Max | 90000 | 1 | | 5 | Janet | 69000 | 1 | | 6 | Randy | 85000 | 1 | | 7 | Will | 70000 | 1 | \+----+-------+--------+--------------+ The Department table holds all departments of the company.
\+----+----------+ | Id | Name | \+----+----------+ | 1 | IT | | 2 | Sales | \+----+----------+ Write a SQL query to find employees who earn the top three salaries in each of the department. For the above tables, your SQL query should return the following rows (order of rows does not matter).
\+------------+----------+--------+ | Department | Employee | Salary | \+------------+----------+--------+ | IT | Max | 90000 | | IT | Randy | 85000 | | IT | Joe | 85000 | | IT | Will | 70000 | | Sales | Henry | 80000 | | Sales | Sam | 60000 | \+------------+----------+--------+
// сначала объединяем таблицу работников с отделами по ид, потом объединяем по ид и зарплате, группируем по именам работников, названиям отделов и зарплате, ставим условие, что количество отдельных зарплат не больше 2
select c.name as Department, a.Name as Employee, a.Salary
from Employee as a
join Department as c on a.DepartmentId = c.Id
left join Employee as b on a.DepartmentId = b.DepartmentId and b.Salary > a.Salary
group by c.name, a.name, a.Salary
having count(distinct b.Salary) <= 2;
Trips and Users # task The Trips table holds all taxi trips. Each trip has a unique Id, while Client_Id and Driver_Id are both foreign keys to the Users_Id at the Users table. Status is an ENUM type of (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’).
+—-+———–+———–+———+——————–+———-+
| Id | Client_Id | Driver_Id | City_Id | Status |Request_at|
+—-+———–+———–+———+——————–+———-+
| 1 | 1 | 10 | 1 | completed |2013-10-01|
| 2 | 2 | 11 | 1 | cancelled_by_driver|2013-10-01|
| 3 | 3 | 12 | 6 | completed |2013-10-01|
| 4 | 4 | 13 | 6 | cancelled_by_client|2013-10-01|
| 5 | 1 | 10 | 1 | completed |2013-10-02|
| 6 | 2 | 11 | 6 | completed |2013-10-02|
| 7 | 3 | 12 | 6 | completed |2013-10-02|
| 8 | 2 | 12 | 12 | completed |2013-10-03|
| 9 | 3 | 10 | 12 | completed |2013-10-03|
| 10 | 4 | 13 | 12 | cancelled_by_driver|2013-10-03|
+—-+———–+———–+———+——————–+———-+
The Users table holds all users. Each user has an unique Users_Id, and Role is an ENUM type of (‘client’, ‘driver’, ‘partner’).
\+----------+--------+--------+ | Users_Id | Banned | Role | \+----------+--------+--------+ | 1 | No | client | | 2 | Yes | client | | 3 | No | client | | 4 | No | client | | 10 | No | driver | | 11 | No | driver | | 12 | No | driver | | 13 | No | driver | \+----------+--------+--------+ Write a SQL query to find the cancellation rate of requests made by unbanned users (both client and driver must be unbanned) between Oct 1, 2013 and Oct 3, 2013. The cancellation rate is computed by dividing the number of canceled (by client or driver) requests made by unbanned users by the total number of requests made by unbanned users.
For the above tables, your SQL query should return the following rows with the cancellation rate being rounded to two decimal places.
\+------------+-------------------+ | Day | Cancellation Rate | \+------------+-------------------+ | 2013-10-01 | 0.33 | | 2013-10-02 | 0.00 | | 2013-10-03 | 0.50 | \+------------+-------------------+
SELECT t.Request_at as Day,
// вычисляем соотношение отмененных вызовов: число выполненных вызовов делим на все заказы
1-ROUND(count(CASE WHEN t.Status=’completed’ THEN True ELSE NULL END) /count(*), 2 ) as ‘Cancellation Rate’
FROM Trips t
// клиенты и водители должны быть во множестве незабаненных
WHERE t.Client_Id IN (SELECT Users_Id FROM Users WHERE Banned=’No’)
AND t.Driver_Id IN (SELECT Users_Id from Users where Banned=’No’)
// время заказов между указанными датами
AND t.Request_at BETWEEN ‘2013-10-01’ and ‘2013-10-03’
// группируем по датам заказа
GROUP BY t.Request_at;
Human Traffic of Stadium
# task # top # consecutive
X city built a new stadium, each day many people visit it and the stats are saved as these columns: id, visit_date, people
Please write a query to display the records which have 3 or more consecutive rows and the amount of people more than 100(inclusive).
For example, the table stadium: \+------+------------+-----------+ | id | visit_date | people | \+------+------------+-----------+ | 1 | 2017-01-01 | 10 | | 2 | 2017-01-02 | 109 | | 3 | 2017-01-03 | 150 | | 4 | 2017-01-04 | 99 | | 5 | 2017-01-05 | 145 | | 6 | 2017-01-06 | 1455 | | 7 | 2017-01-07 | 199 | | 8 | 2017-01-08 | 188 | \+------+------------+-----------+ For the sample data above, the output is:
\+------+------------+-----------+ | id | visit_date | people | \+------+------------+-----------+ | 5 | 2017-01-05 | 145 | | 6 | 2017-01-06 | 1455 | | 7 | 2017-01-07 | 199 | | 8 | 2017-01-08 | 188 | \+------+------------+-----------+
SELECT t.* FROM stadium t // объединяем таблицу с версиями вчера-позавчера-завтра-послезавтра LEFT JOIN stadium p1 ON t.id - 1 = p1.id LEFT JOIN stadium p2 ON t.id - 2 = p2.id LEFT JOIN stadium n1 ON t.id + 1 = n1.id LEFT JOIN stadium n2 ON t.id + 2 = n2.id WHERE t.people >= 100 AND ( (p1.people >= 100 AND p2.people >= 100) OR (n1.people >= 100 AND n2.people >= 100) OR (n1.people >= 100 AND p1.people >= 100) ) ORDER BY id;
Принципы NoSQL
в NoSQL базах в отличие от реляционных структура данных не регламентирована (или слабо типизированна, если проводить аналогии с языками прогаммирования) — в отдельной строке или документе можно добавить произвольное поле без предварительного декларативного изменения структуры всей таблицы. Таким образом, если появляется необходимость поменять модель данных, то единственное достаточное действие — отразить изменение в коде приложения.
Приятное следствие отсутствия схемы — эффективность работы с разреженными (sparse) данными. Если в одном документе есть поле date_published, а во втором — нет, значит никакого пустого поля date_published для второго создано не будет. Это, в принципе, логично, но менее очевидный пример — column-family NoSQL базы данных, в которых используются знакомые понятия таблиц/колонок. Однако в силу отсутствия схемы, колонки не объявляются декларативно и могут меняться/добавляться во время пользовательской сессии работы с базой. Это позволяет в частности использовать динамические колонки для реализации списков.
У неструктурированной схемы есть свои недостатки — помимо упомянутых выше накладных расходов в коде приложения при смене модели данных — отсутствие всевозможных ограничений со стороны базы (not null, unique, check constraint и т.д.), плюс возникают дополнительные сложности в понимании и контроле структуры данных при параллельной работе с базой разных проектов (отсутствуют какие-либо словари на стороне базы).
В отличие от реляционной модели, которая сохраняет логическую бизнес-сущность приложения в различные физические таблицы в целях нормализации, NoSQL хранилища оперируют с этими сущностями как с целостными объектами
Структура должна подчиняться требованиям приложения и быть максимально оптимизированной под наиболее частые запросы.
Распределенные системы, без совместно используемых ресурсов (share nothing)
горизонтальное масштабирование, когда несколько независимых серверов соединяются быстрой сетью и каждый владеет/обрабатывает только часть данных и/или только часть запросов на чтение-обновление
основные свойства распределенных NoSQL баз:
Репликация — копирование данных на другие узлы при обновлении. Позволяет как добиться большей масштабируемости, так и повысить доступность и сохранность данных. Принято подразделять на два вида: master-slave и peer-to-peer
Первый тип предполагает хорошую масштабируемость на чтение (может происходить с любого узла), но немасштабируемую запись (только в мастер узел). Также есть тонкости с обеспечением постоянной доступности (в случае падения мастера либо вручную, либо автоматически на его место назначается один из оставшихся узлов). Для второго типа репликации предполагается, что все узлы равны и могут обслуживать как запросы на чтение, так и на запись.
Шардинг — разделение данных по узлам. Шардинг часто использовался как “костыль” к реляционным базам данных в целях увеличения скорости и пропускной способности: пользовательское приложение партицировало данные по нескольким независимым базам данных и при запросе соответствующих данных пользователем обращалось к конкретной базе. В NoSQL базах данных шардинг, как и репликация, производятся автоматически самой базой и пользовательское приложение обособленно от этих сложных механизмов.
mongodb structure
database - collections (tables) - documents (data pieces)
run mongodb
sudo mongod –dbpath “my/new/path”
mongo
mongodb switch to db
use db (also create)
mongodb inserting data
db.(collectionname).insertOne({“key”:value})
mongodb inserting data
db.(collectionname).insertOne({“key”:value, _id: ‘some_id’})
mongodb crud operations
Create - insertOne(data, options); insertMany(data, options)
Read - find(filter, options); findOne(filter, options)
Update - updateOne(filter, data, options); updateMany(filter, data, options); replaceOne(filter, data, options)
Delete - deleteOne(filter, options); deleteMany(filter, options)
mongoddb delete all data from collections
db.collection_name.deleteMany({})
mongodb updating data
db.collection_name.updateOne({filter}, {$set: {new data}})
mongodb show all data (no Cursor)
db.collection_name.find().toArray()
mongodb data filtering
db.collection_name.find({},{key:1, _id:0})
Принципы ETL
Extract Transform Load
один из основных процессов в управлении хранилищами данных, который включает в себя:
извлечение данных из внешних источников;
их трансформация и очистка, чтобы они соответствовали потребностям бизнес-модели;
и загрузка их в хранилище данных.
С точки зрения процесса ETL, архитектуру хранилища данных можно представить в виде трёх компонентов:
источник данных: содержит структурированные данные в виде таблиц, совокупности таблиц или просто файла (данные в котором разделены символами-разделителями);
промежуточная область: содержит вспомогательные таблицы, создаваемые временно и исключительно для организации процесса выгрузки.
получатель данных: хранилище данных или база данных, в которую должны быть помещены извлечённые данные.
При разработке процедуры извлечения данных в первую очередь необходимо определить частоту выгрузки данных из OLTP-систем (Online Transaction Processing, способ организации БД) или отдельных источников. Выгрузка данных занимает определённое время, которое называется окном выгрузки.
В процессе преобразования данных в рамках ETL чаще всего выполняются следующие операции:
преобразование структуры данных; агрегирование данных; перевод значений; создание новых данных; очистка данных.
Процесс загрузки заключается в переносе данных из промежуточных таблиц в структуру хранилища данных. При очередной загрузке в хранилище данных переносится не вся информация из источников, а только та, которая была изменена в течение промежуточного времени, прошедшего с предыдущей загрузки.
ETL-системы должны в идеале решать не одну, а две задачи:
Привести все данные к единой системе значений и детализации, попутно обеспечив их качество и надежность;
Обеспечить аудиторский след при преобразовании (Transform) данных, чтобы после преобразования можно было понять, из каких именно исходных данных и сумм собралась каждая строчка преобразованных данных.
Процесс загрузки – Его задача затянуть в ETL данные произвольного качества для дальнейшей обработки, на этом этапе важно сверить суммы пришедших строк, если в исходной системе больше строк, чем в RawData то значит — загрузка прошла с ошибкой;
Процесс валидации данных – на этом этапе данные последовательно проверяются на корректность и полноту, составляется отчет об ошибках для исправления;
Процесс мэппинга данных с целевой моделью – на этом этапе к валидированной таблице пристраивается еще n-столбцов по количеству справочников целевой модели данных, а потом по таблицам мэппингов в каждой пристроенной ячейке, в каждой строке проставляются значения целевых справочников. Значения могут проставляться как 1:1, так и :1, так и 1: и :, для настройки последних двух вариантов используют формулы и скрипты мэппинга, реализованные в ETL-инструменте;
Процесс агрегации данных – этот процесс нужен из-за разности детализации данных в OLTP и OLAP системах. OLAP-системы — это, по сути, полностью денормализованная таблица фактов и окружающие ее таблицы справочников (звездочка/снежинка), максимальная детализация сумм OLAP – это количество перестановок всех элементов всех справочников. А OLTP система может содержать несколько сумм для одного и того же набора элементов справочников. Можно было-бы убивать OLTP-детализацию еще на входе в ETL, но тогда мы потеряли бы «аудиторский след». Этот след нужен для построения Drill-down отчета, который показывает — из каких строк OLTP, сформировалась сумма в ячейке OLAP-системы. Поэтому сначала делается мэппинг на детализации OLTP, а потом в отдельной таблице данные «схлопывают» для загрузки в OLAP;
Выгрузка в целевую систему — это технический процесс использования коннектора и передачи данных в целевую систему.
