Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
 Re: Подход к обновлению большой таблицы SSIS  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4807
alexeyvg
Santa89
интересует прежде всего именно как удалять-вставлять только свежие данные за текущение 4мес. всем скопом
То есть за последние 4 месяца данные во время загрузки полностью заменяются на новые?
Тогда да, используйте секционирование, штатное или своё.

"Своё" - делайте 2 таблицы для активных и архивных данных, объединяя их через вьюху.

"Штатное" - обычное сиквельное секционирование; с активной секцией будете работать, переключая её на время загрузки в самостоятельную таблицу.
Проблема штатного секционирование только в том, что придётся наверняка постоянно менять параметры секционирования, что требует немалых ресурсов. Но это зависит от того, что такое у вас "за последние 4 месяца".


Я практикую нагенерить партиций на несколько лет вперед и нормально.
22 ноя 16, 13:58    [19920660]     Ответить | Цитировать Сообщить модератору
 Re: Подход к обновлению большой таблицы SSIS  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8300
a_voronin,

Да, вполне реально лет на 200. Я тоже так и сделал.
22 ноя 16, 15:53    [19921363]     Ответить | Цитировать Сообщить модератору
 Re: Подход к обновлению большой таблицы SSIS  [new]
Santa89
Member

Откуда:
Сообщений: 1494
a_voronin,

я правильно понимаю чтобы вставить данные в секцию (т.е. сделать SWITCH) мне нужно сначала её отчистить?
сделать это можно через DELETE?
22 ноя 16, 16:52    [19921731]     Ответить | Цитировать Сообщить модератору
 Re: Подход к обновлению большой таблицы SSIS  [new]
Ferdipux
Member

Откуда: Москва
Сообщений: 584
Santa89
я правильно понимаю чтобы вставить данные в секцию (т.е. сделать SWITCH) мне нужно сначала её отчистить?
сделать это можно через DELETE?

Можно через DELETE или TRUNCATE.
22 ноя 16, 16:53    [19921742]     Ответить | Цитировать Сообщить модератору
 Re: Подход к обновлению большой таблицы SSIS  [new]
Santa89
Member

Откуда:
Сообщений: 1494
Ferdipux,

в 2008м сервере походу нельзя((
это плохо, у меня ведь обьемы большие...
22 ноя 16, 17:00    [19921782]     Ответить | Цитировать Сообщить модератору
 Re: Подход к обновлению большой таблицы SSIS  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37138
Santa89
Ferdipux,

в 2008м сервере походу нельзя((
это плохо, у меня ведь обьемы большие...
Чо эта?

https://msdn.microsoft.com/library/dd578580(sql.100).aspx , там есть ссылка на документ, который рассматривает разные техники партиционирования.
22 ноя 16, 17:03    [19921799]     Ответить | Цитировать Сообщить модератору
 Re: Подход к обновлению большой таблицы SSIS  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4807
Santa89
Ferdipux,

в 2008м сервере походу нельзя((
это плохо, у меня ведь обьемы большие...


Поменять партиции вы можете. Для этого вам их не надо очищать.

1) Во второй таблице делате TRUNCATE полностью (в 2012 можно транкейтить отдельные партиции)
2) Заливаете туда данные -- они падают в нужную партицию.
3) Делаете SWITCH PARTITIONS -- данные оказываются в первой таблице
22 ноя 16, 17:42    [19922008]     Ответить | Цитировать Сообщить модератору
 Re: Подход к обновлению большой таблицы SSIS  [new]
Santa89
Member

Откуда:
Сообщений: 1494
a_voronin,
а почему вы не рекомендуете сделать лишь две партиции в таблице - со старыми и новыми данными, и каждый месяц менять эту дату в функции партицирования которая будет определять где старые а где новые данные?

звучит по крайней мере это проще чем собирать все партиции (разбитые по месяцам) и переключать их
ведь мне придется не просто последнюю партицию переключить а последние четыре ( в случае если применять подход когда таблица разбита на партиции по месяцам)
22 ноя 16, 17:51    [19922050]     Ответить | Цитировать Сообщить модератору
 Re: Подход к обновлению большой таблицы SSIS  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37138
Santa89
a_voronin,
а почему вы не рекомендуете сделать лишь две партиции в таблице - со старыми и новыми данными, и каждый месяц менять эту дату в функции партицирования которая будет определять где старые а где новые данные?
А с помощью какой магии, по-вашему, данные из одной партиции перельются в другую?
22 ноя 16, 17:56    [19922078]     Ответить | Цитировать Сообщить модератору
 Re: Подход к обновлению большой таблицы SSIS  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4807
Santa89
a_voronin,
а почему вы не рекомендуете сделать лишь две партиции в таблице - со старыми и новыми данными, и каждый месяц менять эту дату в функции партицирования которая будет определять где старые а где новые данные?


где рекомендую? Я говорил о двух партициях, которые вы меняете местами. Выше я предлагал партиционировать таблицу по месяцам на несколько лет вперед. Это предложение полностью в силе.
22 ноя 16, 18:19    [19922176]     Ответить | Цитировать Сообщить модератору
 Re: Подход к обновлению большой таблицы SSIS  [new]
Santa89
Member

Откуда:
Сообщений: 1494
a_voronin,

а почему тогда двух? ведь таблица будет партицирована по месяцам, а мне нужно будет подменить последние четыре месяца в этой таблице на свежие данные..
как в этом случае быть?
22 ноя 16, 19:39    [19922427]     Ответить | Цитировать Сообщить модератору
 Re: Подход к обновлению большой таблицы SSIS  [new]
Santa89
Member

Откуда:
Сообщений: 1494
Хочется все-таки докопаться до сути..опыта с партиционированием нет, вот постараюсь обьяснить на простом примере, как я делаю?
Есть табличка (на скрине слева), я применяю к ней вот такую функцию партиционирования:
create partition function pf_dt ( date )
as range left for values
(
'2016-01-01', '2016-02-01', '2016-03-01',
'2016-04-01', '2016-05-01', '2016-06-01',
'2016-07-01', '2016-08-01', '2016-09-01',
'2016-10-01', '2016-11-01', '2016-12-01'
);
create partition scheme ps_dt 
as partition pf_dt all to ([primary]);


Получается вот такая разбивка как на скрине посередине.
Теперь вопрос - как мне данные из той таблицы что на скрине справа слить в основную таблицу, удалив при этом из основной таблицы последние четыре месяца?

К сообщению приложен файл. Размер - 36Kb
22 ноя 16, 19:52    [19922480]     Ответить | Цитировать Сообщить модератору
 Re: Подход к обновлению большой таблицы SSIS  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37138
Данные "справа" тоже должны быть секционированны той же схемой. Или лежать в 4х разных таблицах.
22 ноя 16, 19:54    [19922491]     Ответить | Цитировать Сообщить модератору
 Re: Подход к обновлению большой таблицы SSIS  [new]
Ferdipux
Member

Откуда: Москва
Сообщений: 584
Santa89
a_voronin,
а почему вы не рекомендуете сделать лишь две партиции в таблице - со старыми и новыми данными, и каждый месяц менять эту дату в функции партицирования которая будет определять где старые а где новые данные?

звучит по крайней мере это проще чем собирать все партиции (разбитые по месяцам) и переключать их

Не проще. Вам при 2-х партициях придется сначала обединить 2 партици в 1, а затем установить новую границу. Но по моему опыту - такие операции с границами партиций не самые быстрые, особенно если данных много.
Вы можете делать merge партициям, объединяя неактуальные партиции при их устаревании.

При наличии SSIS и четко определенных границ (по месяцам) проблем перетасовать 4 партиции нет - делаете базовый цикл, где даты входят в переменные. Примерно как делал в одном из проектов:
1. Определяются граничные даты.
2. Определяется номер партиции для этих границ.
3. Truncate промежуточной таблицы
4. Установка check constraints для промежуточной таблицы
5. Переключение партиции на пром таблицу
6. Dataflow в промежуточную таблицу
7. Переключение из пром таблицы в партицию основной таблицы
Тексты SQL делаются как вычислимые выражения.
22 ноя 16, 19:55    [19922493]     Ответить | Цитировать Сообщить модератору
 Re: Подход к обновлению большой таблицы SSIS  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4807
Santa89
Хочется все-таки докопаться до сути..опыта с партиционированием нет, вот постараюсь обьяснить на простом примере, как я делаю?
Есть табличка (на скрине слева), я применяю к ней вот такую функцию партиционирования:
create partition function pf_dt ( date )
as range left for values
(
'2016-01-01', '2016-02-01', '2016-03-01',
'2016-04-01', '2016-05-01', '2016-06-01',
'2016-07-01', '2016-08-01', '2016-09-01',
'2016-10-01', '2016-11-01', '2016-12-01'
);
create partition scheme ps_dt 
as partition pf_dt all to ([primary]);


Получается вот такая разбивка как на скрине посередине.
Теперь вопрос - как мне данные из той таблицы что на скрине справа слить в основную таблицу, удалив при этом из основной таблицы последние четыре месяца?


1) Делаете две идентичные таблицы
2) партиционируете обе по месяцам с запасом в будущее и в прошлое.
3) Основная таблица с данными, вторая буфер
4) Обнуляете буфер (TRUNCATE всей таблице)
5) Заполняете буфер данными за 4 месяца, которые ложатся в 4 партиции
6) Переключаете партиции (меняете соответствующие месяца друг с другом) 4 раза SWITCH PARTITION
7) Повторяете с шага 4
22 ноя 16, 20:03    [19922515]     Ответить | Цитировать Сообщить модератору
 Re: Подход к обновлению большой таблицы SSIS  [new]
Santa89
Member

Откуда:
Сообщений: 1494
a_voronin,

спасибо, самой главный вопрос по 6-му пункту - как мне узнать номера партиций которые нужно SWITCH-ить? Как понять что именно партиции 10,9,8 и 7 (из моего скриншота) я должен подменить на партиции с теми же номерами из таблицы-буфера?

и немного непонял зачем 7й пункт если я уже всё сделаю в 6м?
22 ноя 16, 20:19    [19922548]     Ответить | Цитировать Сообщить модератору
 Re: Подход к обновлению большой таблицы SSIS  [new]
o-o
Guest
Вы же даты знаете, по дате однозначно считается номер партиции.
Кстати, лучше right function, чтобы весь месяц в одной партиции был
22 ноя 16, 20:25    [19922563]     Ответить | Цитировать Сообщить модератору
 Re: Подход к обновлению большой таблицы SSIS  [new]
Santa89
Member

Откуда:
Сообщений: 1494
o-o,
да, спасибо, нашел
SELECT $PARTITION.pf_dt(@date); 
22 ноя 16, 20:28    [19922568]     Ответить | Цитировать Сообщить модератору
 Re: Подход к обновлению большой таблицы SSIS  [new]
Santa89
Member

Откуда:
Сообщений: 1494
Спасибо всем за ответы, вроде бы получилось как надо.
Еще вопрос, надеюсь крайний:

Перед SWITCH'ем секций сначала нужно соответствующую секцию в основной таблице очистить.
Например я хочу подменить в основной таблице данные за Июль, пишу вот так:

ALTER TABLE [test].[PatitioningTable_FreshData]
SWITCH PARTITION 7 TO [test].[PatitioningTable] PARTITION 7


где PatitioningTable - моя основная таблица.
Если перед выполнением этого скрипта не очистить данные за Июль в основной таблице возникнет ошибка.
Правильно ли я понимаю что ОБЯЗАТЕЛЬНО нужно эти данные очищать? Как делаете это вы?
Я вот нашел такую процедуру: http://www.sqlservercentral.com/scripts/Truncate Table/69506/

(т.е.перед переключением Июльских секций я сначала сделаю Exec dbo.truncate_partition 'test','PatitioningTable', 7)
Буду пользоваться ей, как думаете это нормальный подход?
22 ноя 16, 20:45    [19922594]     Ответить | Цитировать Сообщить модератору
 Re: Подход к обновлению большой таблицы SSIS  [new]
Верблюд
Member

Откуда: Яженичеловек!!!
Сообщений: 65007
Можно ее в другую таблицу переключить а потом уже там почистить

CREATE TABLE t  -- основная таблица
(
	id INT IDENTITY(1,1),
	dt          DATE NOT NULL,
	[name]      NCHAR(10) NOT NULL,
	[value]     INT NOT NULL
	CONSTRAINT [PK_t] PRIMARY KEY CLUSTERED 
	(
		[id],
		[dt] ASC
	) ON [ps_dt]([dt])
)

CREATE TABLE t_new  -- таблица для новых данных
(
	id INT IDENTITY(1,1),
	dt          DATE NOT NULL,
	[name]      NCHAR(10) NOT NULL,
	[value]     INT NOT NULL
	CONSTRAINT [PK_t_new] PRIMARY KEY CLUSTERED 
	(
		id,
		[dt] ASC
	) ON [ps_dt]([dt])
)

CREATE TABLE t_old -- таблица для старых данных
(
	id INT IDENTITY(1,1),
	dt          DATE NOT NULL,
	[name]      NCHAR(10) NOT NULL,
	[value]     INT NOT NULL
	CONSTRAINT [PK_t_old] PRIMARY KEY CLUSTERED 
	(
		id,
		[dt] ASC
	) ON [ps_dt]([dt])
)

INSERT t
(dt, [name], [value])
VALUES 
('20160101', 'vasya', 200),
('20160201', 'vasya', 200),
('20160301', 'vasya', 200),
('20160401', 'vasya', 200)

INSERT t_new
(dt, [name], [value])
VALUES 
('20160101', 'vasya', 300),
('20160201', 'vasya', 300),
('20160301', 'vasya', 300),
('20160401', 'vasya', 300)

DECLARE @ps INT

SET @ps = (SELECT $PARTITION.pf_dt('20160101')); 

ALTER TABLE t SWITCH PARTITION @ps TO t_old PARTITION @ps
ALTER TABLE t SWITCH PARTITION @ps+1 TO t_old PARTITION @ps+1
ALTER TABLE t SWITCH PARTITION @ps+2 TO t_old PARTITION @ps+2
ALTER TABLE t SWITCH PARTITION @ps+3 TO t_old PARTITION @ps+3

TRUNCATE TABLE t3

ALTER TABLE t_new SWITCH PARTITION @ps TO t PARTITION @ps
ALTER TABLE t_new SWITCH PARTITION @ps+1 TO t PARTITION @ps+1
ALTER TABLE t_new SWITCH PARTITION @ps+2 TO t PARTITION @ps+2
ALTER TABLE t_new SWITCH PARTITION @ps+3 TO t PARTITION @ps+3

SELECT	* FROM	t
SELECT	* FROM	t_new
SELECT	* FROM	t_old
22 ноя 16, 20:52    [19922616]     Ответить | Цитировать Сообщить модератору
 Re: Подход к обновлению большой таблицы SSIS  [new]
Верблюд
Member

Откуда: Яженичеловек!!!
Сообщений: 65007
автор
TRUNCATE TABLE t3



это лишнее :)
22 ноя 16, 20:54    [19922620]     Ответить | Цитировать Сообщить модератору
 Re: Подход к обновлению большой таблицы SSIS  [new]
Santa89
Member

Откуда:
Сообщений: 1494
Верблюд,

да, сижу гадаю откуда оно взялось!)
Спасибо, теперь всё понятно!
Постараюсь отписать в ближайшее время как это всё легло на мою милионную таблицу
22 ноя 16, 21:04    [19922649]     Ответить | Цитировать Сообщить модератору
 Re: Подход к обновлению большой таблицы SSIS  [new]
Верблюд
Member

Откуда: Яженичеловек!!!
Сообщений: 65007
Santa89
Верблюд,

да, сижу гадаю откуда оно взялось!)
Спасибо, теперь всё понятно!
Постараюсь отписать в ближайшее время как это всё легло на мою милионную таблицу


там должно быть truncate table t_old, но для посмотреть это делать не нужно :)
22 ноя 16, 21:04    [19922654]     Ответить | Цитировать Сообщить модератору
 Re: Подход к обновлению большой таблицы SSIS  [new]
Santa89
Member

Откуда:
Сообщений: 1494
Верблюд, инетерсно а этот перенос партиций (SWITCH) делает тоже самое что и INSERT в другую таблицу с последующим DELETOM из старой или эта операция SWITCH гораздо быстрее будет выполнятся?
22 ноя 16, 21:09    [19922670]     Ответить | Цитировать Сообщить модератору
 Re: Подход к обновлению большой таблицы SSIS  [new]
Верблюд
Member

Откуда: Яженичеловек!!!
Сообщений: 65007
Santa89
Верблюд, инетерсно а этот перенос партиций (SWITCH) делает тоже самое что и INSERT в другую таблицу с последующим DELETOM из старой или эта операция SWITCH гораздо быстрее будет выполнятся?


Патриции переключают место хранения.
22 ноя 16, 21:16    [19922687]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить