SQL *

Продолжая публикацию материалов о техниках физического моделирования в СУБД Teradata мы, как и обещали в прошлой статье, хотим рассказать о дополнительных техниках, не относящихся напрямую к обычным индексам (хоть в этой статье мы и коснемся особых видов индексов, о которых следует знать).

Помимо обычных индексов в СУБД Teradata присутствует ряд специфичных и иногда тонких техник, которые могут применяться в физическом моделировании. Приведем описание большинства из техник, а если возникнут вопросы или желание узнать подробней, будем рады ответить в комментариях.

Предыдущий пост: Что такое Teradata?

Как Teradata распределяет строки?

• Teradata использует алгоритм хэширования для рандомного распределения строк таблицы между AMP-ами (преимущества: распределение одинаково, независимо от объема данных, и зависит от содержания строки, а не демографии данных)
• Primary Index определяет, будут ли строки таблицы распределены равномерно или неравномерно между AMP-ами
• Равномерное распределение строк таблицы ведет к равномерному распределению нагрузки
• Каждый AMP отвечает только за свое подмножество строк каждой таблицы
• Строки размещаются неупорядоченно (преимущества: не требуется поддержка сохранения порядка, порядок не зависит от любого представленного запроса)

Primary Key (PK) vs. Primary Index (PI)

Primary Key (первичный ключ) – это условность реляционной модели, которая однозначно определяет каждую строку.
Primary Index – это условность Teradata, которая определяет распределение строк и доступ.
Хорошо спроектированная база данных содержит таблицы, в которых PI такой же как и PK, а также таблицы, в которых PI определен в столбцах, отличных от PK, и может влиять на пути доступа.

Что такое физический дизайн структур хранения

Основная цель, преследуемая в ходе разработки физической модели данных, — создание таких объектов для конкретной платформы/СУБД, которые позволят достигнуть максимальной производительности запросов/приложений, создающих основную нагрузку, сведя при этом дополнительные затраты, такие как необходимость поддерживать дополнительные индексы, выполнять материализацию производных данных и т. п., к минимуму.
Все реляционные СУБД построены на одних принципах, но каждой платформе присущи уникальные черты в виде наличия различных типов объектов и особенностей их реализации. По этой причине процесс физического моделирования является платформенно-зависимым, в отличие от логического моделирования, основная цель которого — достоверно описать данные и бизнес-процессы.

Здравствуйте! И сразу прошу прощение, за слишком мудрёное название, но оно наиболее полно отражает излагаемый ниже материал.

Я думаю многие из вас сталкивались с необходимостью вычисления пересекающихся интервалов. Но задача с которой я столкнулся на днях — оказалась не столь тривиальной. Но, обо всем по порядку.

Вычисление пересекающихся интервалов в линейном пространстве имен

Если у вас уже есть представление о пересечении интервалов, то пройдите сразу сюда.

Вычисление пересечений временных интервалов (отрезков времени) на прямой линии времени не составляет особого труда. Мы можем условно иметь пять видов временных пересечений.
Обозначим один отрезок времени как "\ \", а другой "/ /"

1. Смещение вперед по оси времени "/ \ / \"
2. Смещение назад по оси времени "\ / \ /"
3. Вхождение " / \ \ / "
4. Поглощение "\ / / \ "
5. Совпадение «X X»

Предисловие: По работе мне пришлось изучать устройство базы данных Teradata, и оказалось, что в интернете почти нет информации, особенно на русском языке. Поэтому я решила собрать в кучу всю доступную информацию.

Стремительное увеличение объемов носителей информации и удешевление стоимости хранения данных привело к появлению методов, способных обеспечить более быстрый доступ к необходимым данным – индексы, хранение данных в отсортированном виде и т.п. Эти методы вполне успешно справляются со своей задачей, однако возрастающая конкуренция в мире заставляет искать новые, более быстрые, способы доступа к информации. «Кто владеет информацией, тот владеет миром». Основной интерес вызывают базы данных с традиционной реляционной моделью данных, отвечающие требованиям ACID (Atomicity, Consistency, Isolation, Durability — атомарность, согласованность, изолированность, надежность) и предназначенные для аналитики Больших Данных (Big Data).

Teradata – это параллельная реляционная СУБД, которая работает на операционных системах:

• MP-RAS UNIX
• Microsoft Windows 2000/2003 Server
• SuSE Linux

Разнообразие поддерживаемых ОС — одна из причин, почему Teradata имеет открытую архитектуру.

Оригинал статьи носит название «10 SQL ошибок, которые делают Java разработчики», но, по большому счёту, приведённые в ней принципы можно отнести к любому языку.



Java программисты мешают объектно-ориентированное и императивное мышление в зависимости от их уровня:
— мастерства (каждый может программировать императивно)
— догмы (шаблон для применения шаблонов где-либо и их именование)
— настроения (применять истинный объектный подход немного сложнее чем императивный)

Но всё меняется, когда Java разработчики пишут SQL код.

Задача выбора случайных строчек из таблицы довольно часто возникает перед разработчиками.
В случае, если используется СУБД MySQL, обычно она решается примерно следующим способом:

SELECT *
FROM users
WHERE role_id=5
ORDER BY rand()
LIMIT 10

Такой код работает крайне медленно для больших таблиц.
Если в запросе не нужно использовать WHERE или таблица небольшая, есть эффективные решения, например habrahabr.ru/post/54176 или habrahabr.ru/post/55864.
Но готовых решений для большой таблицы и необходимости фильтровать по условию, получая при каждом запросе новые значения, я не нашел, поэтому описание моего способа под катом.

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

В синтаксисе T-SQL для выполнения подобного преобразования предусмотрена отдельная конструкция PIVOT. Стоит заметить, что в SQL Server 2000 поддержки конструкции PIVOT еще не было, поэтому аналогичные задачи решались через множественные CASE WHEN.

Собственно, почему я упомянул о CASE WHEN, если есть PIVOT? Ведь, по определению, PIVOT более элегантная конструкция и, соответственно, должна быть более эффективной.

Проверим это на практике…

Не секрет, что зашифрованные параметры (т.е. имеющие тип Encrypted), используемые в IBM DataStage в версиях до 8.7 очень легко расшифровать. Эти зашифрованные параметры часто используются для передачи паролей, необходимых для соединения с базами данных.
При постороении корпоративных ODS (а в некоторых случаях даже и в случае хранилищ данных) имеет смысл создавать универсальные джобы — так называемые генерики, которые полностью конфигурируются извне и не содержат специфичной для каждой таблицы информации, а поэтому их можно использовать для многих ETL процессов. Особенно это необходимо при извлечении данных из баз данных источников (Extraction). В таком случае необходимо хранить в файлах конфигураии пароли для каждого источника данных. И вам приходится, прогибаясь под политики безопасности различных предприятий, делать вид, что это надежный алгоритм шифрования и хранить пароли к корпоративным данным в зашифрованном DataStage виде.
Но проблемы возникают, если вы захотите передать такие параметры в джоб. Какие проблемы и как их решать я и напишу в этой статье.

Сначала результат, а потом разбор полетов. Допустим, вы создали запрос типа select [document].[id], [document].[name] from [SomeDocuments][document], но вместо ожидаемого

  id = 1
 name = d1

получаете

 document = 1
 document = d1

А теперь, если интересно будем разбираться.

Доброго дня, хабровчане! Пришлось мне в проекте столкнуться с точностью вычислений в MS SQL Server и я обнаружил не совсем интуитивное поведение при выполнении казалось бы интуитивных операций.

Для затравки вопрос (попробуйте ответить на него, не выполняя):
Каков будет результат операции?

declare @var1 decimal(38,10) = 0.0000007,
        @var2 decimal(38,10) = 1;
select @var1 * @var2;

Ответ и объяснение под катом

Здравствуй Хабр!

Совсем недавно в нашей компании проходил очередной Хакатон. И в его рамках мне захотелось убить время поинтересней сделать полезную вещь, как для себя так и для других разработчиков. Выбор пал на этакий валидатор sql кода, который бы проверял его на разные правила что не под силу компилятору и те что могут пропустить ребята которые делают Code Review. Таких правил можно придумать массу, начиная от простого “Добавлять GO в конце запроса” и заканчивая более сложными “Использовать View вместо Table”. И самое главное, этот валидатор никоим образом не должен добавлять время разработчику на его использование, т.е. попросту говоря, он должен валидировать себе где-то автоматически, вне зависимости от действий разработчика.

Так уж исторически сложилось, что весь sql-код перед тем как выйти в продакшн (т.е. исполнится на основной БД) сохраняется у нас в GIT репозитории, куда попадает напрямую от разработчиков (естественно после Code Review). Так вот, возникла идея добавить git-hook в этом репозитории который бы валидировал sql-код и если он не валидный то коммит бы возвращался разработчику на доработку. Немного тяжело представить, легче нарисовать:

Хочу задать этот вопрос Хабровчанам.

Современные информационные системы строятся на различных видах СУБД и все же реляционные СУБД остаются самыми распространенными и используемыми. Интересная статистика на эту тему ТУТ и ТУТ.


При разработке и модификации систем уровень формализации знаний аналитиков и разработчиков остается небольшим (автоматизации создания умных запросов или с учетом ряда четких правил) и чаще всего результирующие SQL запросы написаны «нормально», «как привык», «так пишут у нас на фирме», а вопросы оптимизации остаются на этап выполнения запросов в СУБД и последующие этапы оптимизации (в худшем случае ждут, когда все начинает тормозить).

Объем ручного кода остается большим даже несмотря на

В статье я описал несколько примеров неочевидных моментов при использовании LINQ to SQL. Если вы гуру .NET, вам, возможно, покажется это скучным, остальным — добро пожаловать!
Начнем с такого примера. Допустим, у нас есть сущность «тип действия». У типа действия есть human-readable имя и системное имя — некий уникальный идентификатор, по которому с объектами этой сущности мы сможем работать из кода. Вот такая структура в виде объектов в коде:

class ActionType
{
	public int id;
	public string systemname;
	public string name;
}

var ActionTypes = new ActionType[] {
	new ActionType {
		id = 1,
		systemname = "Registration",
		name = "Регистрация"
	},
	new ActionType {
		id = 2,
		systemname = "LogOn",
		name = "Вход на сайт"
	},
	new ActionType {
		id = 3,
		systemname = null,
		name = "Некоторый тип действия без системного имени"
	}
};

Для такой же структуры с аналогичными данными создана таблица в БД и вспомогательные объекты для использования LINQ to SQL. Допустим, нам необходимо выяснить, существует ли у нас тип действия с системным именем NotExistingActionType. Вопрос в том, что будет выведено на экран после выполнения этих инструкций:

var resultForObjects = ActionTypes.All(actionType => actionType.systemname != "NotExistingActionType");
var context = new LinqForHabr.DataClasses1DataContext();
var resultForLTS = context.ActionTypes.All(actionType => actionType.SystemName != "NotExistingActionType");

Console.WriteLine("Result for objects: " + resultForObjects + "\nResult for Linq to sql: " + resultForLTS);
Console.ReadLine();

SQL Server хранит информацию обо всех объектах и их свойствах в виде метаданных, доступ к которым возможен через системные представления. Кроме того, некоторые из системных представлений скрывают в себе интересные нюансы, позволяющие лучше понять как устроена DBMS.

Чтобы просмотреть тело системного преставления, как впрочем и любого другого скриптового объекта, применяют функцию – OBJECT_DEFINITION:

PRINT OBJECT_DEFINITION(OBJECT_ID('sys.objects'))

Однако, у OBJECT_DEFINITION, также как и у ее аналога sp_helptext, есть существенный недостаток – с их помощью нельзя вернуть скриптовое описание для табличного объекта.

IF OBJECT_ID('dbo.Table1', 'U') IS NOT NULL
  DROP TABLE dbo.Table1
GO

CREATE TABLE dbo.Table1 (ColumnID INT PRIMARY KEY)
GO

EXEC sys.sp_helptext 'dbo.Table1'
SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.Table1', 'U'))

При выполнении sp_helptext мы получим ошибку:

Msg 15197, Level 16, State 1, Procedure sp_helptext, Line 107
There is no text for object 'dbo.Table1'.

При тех же условиях, системная функция OBJECT_DEFINITION вернет NULL.

Также не решит проблемы выборка из sys.sql_modules, поскольку внутри этого системного представления используется все тот же вызов функции OBJECT_DEFINITION:

CREATE VIEW sys.sql_modules AS
    SELECT object_id = o.id,
        definition = object_definition(o.id),
        ...
    FROM sys.sysschobjs o

Такое поведение весьма печально, поскольку для некоторых сценариев, бывает полезно получить скриптовое описание таблицы. Что ж, заглянем в системные представления и создадим аналог функции OBJECT_DEFINITION для работы с табличными объектами.

В относительно недавнем прошлом, возникла задача автоматизировать процесс генерации и рассылки HTML отчетов руководству по продажам за текущий месяц. Так уж вышло, что для каждого руководящего лица создавались отдельные таблицы с необходимой только им информацией.

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

Было решено генерировать HTML со стороны сервера базы данных и через Database Mail формировать рассылку путем выполнения команды sp_send_dbmail.

Большинство примеров, приведенный в Сети, создавали разметку вручную — это было не слишком эффективным подходом. При этом я не нашел универсального решения, позволяющего работать с таблицой имеющей произвольную структуру.

Чтобы заполнить этот пробел предлагаю на рассмотрение мой вариант решения.

Вы, возможно, помните как несколько лет назад стремительно стали набирать популярность NoSQL-базы данных (MongoDB, DynamoDB и другие). Многие пророчили смерть классических реляционных баз данных, торжество новых парадигм и всеобщее счастье в мире. И вы, возможно, в курсе того, как в последний год (или около того) наблюдается откат этой эйфории — выходят статьи типа «Broken by Design: MongoDB Fault Tolerance» и Why You Should Never Use MongoDB. Народ на Хабре на Тостере интересуется — «А почему же Монгу критикуют?», на что получает ответы «перерекламировали», «серебрянной пули нет», «надо выбирать базу данных по задачам».

Все 3 очевидных варианта — «Использовать реляционную БД», «Использовать NoSQL-БД», «Выбирать БД по задачам проекта» мне не нравятся по причине, высказанной в заголовке статьи.

Постигшие дзэн Пайтона считают, что должен быть один (и, желательно, только один) очевидный способ достигнуть желаемого.

А постигшие список модулей Node.js могут убедиться в том, что создатели этих модулей духовно ближе не к дзэн-буддистам, а к байхуистам — к поклонникам движения «Байхуа юньдун» (百花运动), провозглашённого Мао Цзэдуном в 1957 году по мотивам классического китайского стихотворения «пусть расцветают сто цветов, пусть соперничают сто школ», начинающегося словами «бай хуа» («百花», «сто цветов»). Иными словами, модули для Node.js предоставляют, как правило, несколько способов сделать одно и то же, и из них потребитель выбирает тот способ, который более всех пригоден ему.

Но почему не существует такого одного способа, который был бы пригоден для всех?

Ответ на этот вопрос я предлагаю рассмотреть на примере употребления базы данных SQLite.

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

Msg 8117, Level 16, State 1, Line 1
Operand data type char is invalid for sum operator.

Хотя для решения подобного рода задач, для MySQL была добавлена функция GROUP_CONCAT, а в Oracle – LISTAGG. В свою же очередь, SQL Server такого встроенного функционала пока не имеет.

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

Сегодня все большую популярность завоевывают In-Memory BI решения. Кубы уже не в моде, их структура морально устарела, и хотя они довольно прилично масштабируются, требования к скорости работы современных BI систем значительно возросли. Тем не менее, многие компании до сих пор успешно используют аналитику, построенную на одном из OLAP-серверов (Microsoft, Oracle, Cognos, и др.). Мне, например, очень нравится Microsoft SQL Server Analysis Service, и я хотел бы рассказать, как в нем можно использовать немного необычную для аналитики функцию – обратную запись данных в источник (Write Back).