MS SQL Server. Загрузка файлов в БД

Постановка задачи

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

Загрузка файла в БД

Для того, чтобы загрузить файл в БД необходимо воспользоваться следующей командой
OPENROWSET(BULK 'data_file' , { SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB } } )
В завиcимости от параметра, данная конструкция возвращает следующие типы данных:
  • varbinary(max) - SINGLE_BLOB (рекомендуется для чтения XML-файлов)
  • varchar(max) - SINGLE_CLOB (используется для считывания ASCII файлов. Использует кодировку текущей базы данных)
  • nvarchar(max) - SINGLE_NCLOB (используется для считывания данных в кодировке юникод)
Обратиться к считываемой информации можно используя конструкцию bulkcolumn. Все содержимое файла будет содержаться в одной ячейке.
-- Загрузка изображения
select bulkcolumn from openrowset(bulk '\\share\icon.png', single_blob) as img

Загрузим теперь текстовый файл. Я скачал первый том произведения "Война и мир".
select bulkcolumn from openrowset(bulk '\\share\Vojna i mir. Tom 1.txt', single_clob) as img
select bulkcolumn from openrowset(bulk '\\share\Vojna i mir. Tom 1.txt', single_nclob) as img
Результат первого запроса некорректно разобрал русские символы, а второй вообще свалился с ошибкой ("SINGLE_NCLOB requires a UNICODE (widechar) input file. The file specified is not Unicode.").


Откроем блокнот и поменяем кодировку файла. Один файл сохраним в кодировки ANSI, второй сохраним в кодировке "Юникод Big Endian" -- UTF16-BE, именно такую кодировку поддерживает MS SQL Server. После загрузки файлов, получим корректные результаты.

Получение информации о файлах  в директории

Получить всё содержимое файловой директории можно используя недокументированную команду master.sys.xp_dirtree, которая принимает следующие параметры
  • directory - имя исследуемой директории;
  • depth - определяет сколько уровней вложенных директорий необходимо отобразить. Параметр по умолчанию 0 отображает все директории;
  • file - определяет отображать ли файлы в каждой директории. По умолчанию 0 не отображает файлы.
Следующая команда возвращает содержимое директории \\Share как файлы, так и вложенные директории. Значение 1 в колонке depth значит, что файл находится в указанной директории
exec master.sys.xp_dirtree '\\Share\', 0, 1;


Ниже приводится код загрузки содержимого директории, отфильтрованного по расширению png во временную таблицу userLogo

declare @folder nvarchar(255) = '\\Share\Files\'
declare @sql nvarchar(max);
declare @fileName nvarchar(255);
declare @filePath nvarchar(500);
declare @files table (
 fileName nvarchar(255),
 depth tinyint,
 isFile bit
);
if object_id('tempdb..#usersLogo') is not null
 drop table #usersLogo;

create table #usersLogo (
 id int identity,
 fileName nvarchar(255),
 fileContent varbinary(max)
)

set @sql = 'exec master.sys.xp_dirtree ''' + @folder + ''' , 0, 1;';
insert into @files
exec sp_executesql @sql;
select * from @files

declare fileCursor cursor 
for select fileName from @files 
 where isFile = 1 and depth = 1 and fileName like '%.png' 

open fileCursor
fetch next from fileCursor into @fileName

while (@@fetch_status = 0)
begin 
 set @sql = '
 insert into #usersLogo(fileName, fileContent) 
 select 
  $fileName, 
  (select bulkcolumn from openrowset(bulk $filepath, single_blob) t )';
 set @filePath = @folder + @fileName
 set @sql = replace(@sql, '$fileName', '''' + @fileName  + '''' );
 set @sql = replace(@sql, '$filePath', '''' + @filePath + '''' );
    print @sql
 exec sp_executesql @sql;

 fetch next from fileCursor into @fileName
end

select * from #usersLogo
Результаты выполнения приведенного выше скрипта

Права доступа

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

Ресурсы

  1. MSDN. OPENROWSET
  2. Why is SQL Server Big Endian?



MS Power BI. Справочник

Ресурсы по Power BI

C чего начать

Примеры отличных визуализаций

Custom visuals

DAX

Power BI Embeded

Блоги и стороние ресурсы


Интересные статьи

R, Shiny

Всё течёт, всё меняется.
Для реализации небольших BI проектов не нужно стрелять из пушки по воробьям и можно обойтись бесплатными решениями.

Одно из таких решений фрэймворк Shiny, использующий всю мощь языка R

“Shiny is an open source R package that provides an elegant and powerful web framework for building web applications using R.
Shiny helps you turn your analyses into interactive web applications without requiring HTML, CSS, or JavaScript knowledge.”

Отличительная особенность данного решения – декларативный подход к описанию дашборда и возможность использования огромное число библиотек R, что даёт очень большую гибкость в создании BI решений. Для R есть просто чумовые библиотеки по визуализации данных.

Он не заменит BI платформы, но у данного решения определенно есть своя ниша. Особенно в продвинутой аналитике данных

Примеры:

Очень крутой пример, исходники в открытом доступе https://mbienz.shinyapps.io/tourism_dashboard_prod/
Для того, чтобы понять как работает, можно посмотреть простенький пример с кодом реализации http://shiny.rstudio.com/gallery/telephones-by-region.html
Галерея с исходниками http://shiny.rstudio.com/gallery/
Хорошая библиотека с графиками http://jkunst.com/highcharter/index.html

Анализ данных выборов (исходники, хабр)


Туториалы

Исходники

Редакции

Можно публиковать созданные приложения в облаке http://www.shinyapps.io/
Есть on premise редакция https://www.rstudio.com/products/shiny/shiny-server/

SSIS 2016 new features

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

1. Поддержка AlwaysOn msdn

2. При обработке ошибок в потоке данных, к колонкам ErrorCode (определяет код ошибки) и ErrorColumn (определяет идентификатор колонки в пакете - lineage Id), добавились колонки ErrorDescription и ErrorColumnName. Из окна Data Viewer в режиме отладки эти колонки отображаются, правда разработчики почему-то не включили их в стандартный выход потока ошибок. Получить эти колонки можно через Script Component

В предыдущих версиях, чтобы получить эту информацию приходилось изрядно извращаться, чтобы получить по lineageId колонки её наименование. Приходилось парсить xml-файл с пакетом и получать маппинг колонок!!! Пример такого изврата.

3. Инкрементальное обновление пакетов, которое позволяет деплоить пакеты(один или несколько) отдельно от проекта. При этом пакет можно опубликовать как в текущий проект, так и отдельно (в новый проект).

4. В дефолтной поставке появились следующие компоненты:
- Balance Data Distributor (давно пора! равномерно делит входной поток на N потоков)
- Data Feed Publishsing (позволяет обращаться к результатам работы пакета через представление, предварительно настроив подключение линкед сервера к SSIS, msdn)
- Коннекторы для платформы Hadoop (для работы с HDFS, для запуска тасков в Pig и Hive, msdn )
- Коннекторы к сервисам Azure (необходимо установить Azure Feature Pack, msdn )
- Поддержка Excel 2016 и OData v4

5. Возможность создать свой собственный уровень логирования событий, очень тонко настроив необходимые статистики и события для логирования. При запуске пакета есть возможность выбрать тип логирования. Добавлен уровень логирования RuntimeLineage.

6. В дизайнере пакетов появился новый функционал, немного упрощающий разработку сложных проектов - Package parts - набор, компонентов потока управления, созданный для повторного использования в других пакетах, или по-другому, шаблон. При изменении шаблона он автоматически изменяется и в родительских пакетах, в которых он используется. При этом в родительском пакете нет возможности изменить package part. Сами шаблоны не публикуются на сервер, так как являются частью пакетов, в которых они используются.
7. Очень полезная фича - опция AutoAdjustBufferSize автоматически вычисляющая размер буфера для потока данных
8. Поддержка SSAS Tabular в компонентах процессинга
9. Добавили роли ssis_logreader - просмотр отчетов по запуску пакетов, ssis_monitor - внутренняя для AlwaysOn

Полезные ссылки:
1. TechNet Virtual Lab: Exploring What's New in SQL Server 2016 Integration Services
2. MSDN. What's New in Integration Services
3. Reuse Control Flow across Packages by Using Control Flow Package Parts
4. Data Flow Performance Features
5. Operationalize your machine learning project using SQL Server 2016 SSIS and R Services
6. What's New in SQL Server Integration Services 2016 - Part 1What's New in Integration Services 2016 - Part 2
7. Improving data flow performance with SSIS AutoAdjustBufferSize property
8. Презентация 

Выполнение пакетов в SSIS при помощи T-SQL

Начиная с SQL Server 2012 структура проектов SSIS претерпела существенные изменения, путем перехода от модели управления единичными пакетами (package deployment model) к модели управления проектами (project deployment model). Последний подход упрощает и унифицирует разработку, позволяя группировать логически связанные пакеты в проекты и управлять ими как общей единицей. При развертывании проекта на сервер, все его компоненты размещаются в общей базе данных SSISDB.

Сегодняшняя статья будет посвящена процессу выполнения пакетов, развернутых в SSIS при помощи project deployment model.

В SSIS, начиная с 2012 версии обновился компонент Execute package task. Для запуска пакета, находящихся в одном проекте можно воспользоваться данным компонентом с опцией Project Reference. В выпадающем списке PackageNameFromProjectReference отобразятся все пакеты текущего проекта. Перейдя на вкладку Parameter Bindings можно задать значения переменных запускаемого пакета. Особых комментариев по работе данного компонента не требуется, за исключением одного. Данные компонент не позволяет выполнить пакет из другого проекта.

Добавление пользователей в группы Sharepoint

$siteUrl = "http://siteUrl"
$siteCollUrl = "http://siteUrl/Reports"
$web = Get-SpWeb -site $siteUrl | Where-Object {$_.Url -eq $siteCollUrl}

$users = Invoke-SQL -sqlCommand  "exec dbo.GetUserRightsForSP"
foreach ($user in $users.Rows)
{
    
    $userSp = Get-SpUser -Identity $user["SpUser"] -Web $web
    Set-SPUser -Identity $userSp -Web $web -Group $user["Group"]
}

Добавление пользователей в локальные группы при помощи PowerShell

В данной статье будет рассмотрен способ добавления пользователей в определенные для них группы, информация по которым будет браться из базы данных.
Следующий командлет PowerShell добавляет пользователя AD в заданную локальную группу пользователей:

function Add-LocalUser{
     Param(
        $computer   = $env:computername,
        $group      = "GroupName",
        $userdomain = $env:userdomain,
        $username   = $env:username
    )
        ([ADSI]"WinNT://$computer/$group,group").psbase.Invoke("Add",([ADSI]"WinNT://$userdomain/$username").path)
}
Следующий командлет выполняет SQL-запрос на заданной базе данных MS SQL Server и возвращает табличный результат.

function Invoke-SQL {
    param(
        [string] $dataSource = "ServerName",
        [string] $database   = "DbName",
        [string] $sqlCommand = "select [User], [Domain], [Group] from dbo.vUserRights"
      )

    $connectionString = "Data Source=$dataSource; " +
            "Integrated Security=SSPI; " +
            "Initial Catalog=$database"

    $connection = new-object system.data.SqlClient.SQLConnection($connectionString)
    $command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
    $connection.Open()

    $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
    $dataset = New-Object System.Data.DataSet
    $adapter.Fill($dataSet) | Out-Null

    $connection.Close()
    $dataSet.Tables
}
Получаем из базы данных таблицу принадлежности пользователей к группам и добавляем каждого пользователя в определенную для него локальную группу на данной машине.

$users = Invoke-SQL -sqlCommand  "select [User], [Domain], [Group] from dbo.vUserRights";
foreach ($user in $users.Rows)
{
    Add-LocalUser -group $user["Group"] -username $user["User"] -userdomain $user["Domain"] 
}