PolyBase in SQL Server 2019 – The End of ETL?
PolyBase has been around since 2016, but Microsoft is enhancing it in 2019 with the ability to connect to more data sources. You can now use PolyBase to connect to other SQL Server instances, and it’s easy to set up with a wizard. In this article Benjamin Weissman explains what’s new in PolyBase and walks you through setting it up and connecting to an external table.
One of the new features in SQL Server 2019 is SQL Server Big Data Clusters, and one part of this feature is PolyBase. Now you may wonder: Hasn’t PolyBase been around for quite a while? And you’re right! PolyBase was introduced in SQL Server 2016 and is also a key feature in Azure SQL Data Warehouse to bring in data from flat files sitting on an HDFS Cluster. It treats these sources as external tables which you can query through T-SQL just like any local table stored in your SQL database.
So, what’s new then? In SQL Server 2019, Microsoft greatly enhanced the capabilities of PolyBase. Instead of only flat files, you can now bring in data residing on other relational and non-relational data sources such as other SQL Servers, Oracle, Teradata or MongoDB.
Microsoft is using PolyBase – a technology which has been around since SQL Server 2016 – to deliver a solution for data virtualisation.
Data integration has been accomplished in the past by using tools like SSIS, PowerShell, bcp and others to copy data from one data source to another. Data virtualisation allows the data to be queried at its original source. This means that any query against such data becomes a real-time query while at the same time, it avoids the redundancy of storing data in two places. From a user perspective, a query against a PolyBase table is no different than against a regular table so you may also offload and switch local tables into external tables at some point without breaking any queries. Such a change can, of course, imply substantial positive or negative performance impacts.
One potential use case would be offloading older data to cheaper storage and servers or even to Azure while still having it accessible within your central data hub through PolyBase.
What will I need to do to use the new PolyBase capabilities?
First, you will need an installation of SQL Server 2019, which is currently in the Customer Technology Preview (CTP) phase. You can download the latest preview here.
During the installation, you must also add the PolyBase feature:
If you plan to use PolyBase against a Teradata source, you will need the C++ Redistributable 2012.
If you are using Chocolatey, you can install it using this line in a command prompt:
In the PolyBase Configuration screen, you can decide between a standalone as well as a scale-out option. Both will work, however the standalone option is recommended unless you have a specific interest or need in the additional installation overhead that comes with scale-out.
Once the installer has finished, connect to your SQL Server instance to enable Polybase. This is done through a small T-SQL script:
TCP-IP must also be enabled. Be sure to review the PolyBase installation instructions for more information. After enabling the feature, restart your SQL Server instance. PolyBase is now ready to use.
OK, I got it installed. But how do I use it? How can I query another SQL Server?
Once you connect a table from another server using PolyBase, you’ll see the table listed just as if the table was local. The table is read-only, however, and has the word “External” next to the name when viewing from ADS. At this time, you won’t see the tables in the Object explorer in SSMS, but you can still query them from that tool.
To add a table from another SQL Server as a PolyBase source to your database, you will need a master key (to encrypt credentials), credentials to connect to the other database (PolyBase only supports simple authentication), the external data source itself (server- and database name) as well as the definition of the table(s) you want to add.
While you can achieve all this through T-SQL, the easiest way is to make use of the external table wizard in Azure Data Studio (ADS). To get the wizard, you will need to add the SQL Server 2019 extension for Azure Data Studio. Follow the instructions on the download page to perform the installation.
Note that at the time of this writing, you must change the downloaded file’s extension from .zip to .vsix.
Before getting started, make sure that the source instance is using mixed-mode and set up a SQL Server login with read permission to the table you will link.
To open the wizard, connect to the instance running PolyBase and the database where you want to see the external table. Right-click the user database and select “Create External Table.”
The wizard will first prompt you to confirm the destination database and pick a data source. While PolyBase in SQL Server 2019 supports a wide range of external sources like Teradata or DB2, the Wizard currently only supports SQL Server and Oracle. There is another wizard for flat files. All others will need manual scripting. In this case, select SQL Server.
As SQL Server will store the usernames and passwords required to connect to the external data sources, these need to be encrypted. This requires a master key to be set on the source database which will be taken care of by the wizard if no key exists yet:
In the next step, you need to provide a server as well as a database name. Supply a name for the new Credential and add the login and password that you created. This will link the login to the new Credential which will be used to connect to the source instance.
If you have created such a connection and credentials before, you can re-use them by picking their respective entry from the select box:
Now that you have a connection to the external server, the wizard will get a list of all tables and views included in the respective source database. You can either select specific tables/views or all of them:
For each table, you can change the schema as well as the table name itself which will be used for the external table as shown in the next figure. To get to this screen, just click in the respective table name in the list. You cannot change data types or column names, nor can you only select specific columns. While this may seem confusing to begin with, it doesn’t matter as no actual data is being transferred at this point. That will only happen later when querying the table, and at this point, your SELECT statement will determine which columns are about to be used.
In the end, the wizard will give you a summary as well as the option to either generate everything that you’ve just defined or to create a script which you can manually execute at any later point in time:
You can also complete all the steps using T-SQL. In this example, I have just brought in the Customer table for an AdventureWorksLT database in Azure SQLDB:
О новых функциях SQL Server 2016
Базы данных — это сердце любой компании, которое обеспечивает работоспособность буквального каждого направления деятельности. Но какое место базы данных занимают в современных реалиях? Ведь они появились не сегодня. Они были созданы в то время, когда мы и представить не могли, что мир технологий будет таким, какой он есть сейчас. Традиционные базы данных предназначались исключительно для записи и извлечения транзакций, таких как заказы и платежи. Их задачей было обеспечивать надежную и безопасную работу приложений в небольшом или среднего масштаба локальном ЦОДе.
Ниже подробности о некоторых новых функциях SQL Server 2016 – R Services, PolyBase, Stretch Database.
Еще больше и подробнее вы можете узнать на нашем виртуальном форуме.
Анализ внутри базы данных с использованием языка R
Язык программирования R широко используется как программное обеспечение для анализа данных и составления прогнозов. Он бесплатный, основан на открытом исходном коде и поддерживается большим сообществом. Вы можете использовать несколько тысяч пакетов для решения различных задач. К вашим услугам — стандартные функции статистики и управления данными, графические интерфейсы и передовые алгоритмы машинного обучения. Язык R выбирают ученые и компании, которые хотят внедрять инновации с использованием современных аналитических методов.
- Службы R Services позволяют выполнять анализ внутри базы данных. Вы можете хранить данные в SQL Server 2016, а приложения вызывают скрипты R через хранимую процедуру T-SQL. Это упрощает интеграцию приложений с языком R. Скрипты R выполняются на той же машине, где хранятся данные, поэтому вам не нужно беспокоиться о безопасности: код R не сможет получить доступ к Интернету или к другим процессам, запущенным на SQL Server.
- Пользователи R Services могут использовать библиотеку алгоритмов ScaleR — набор функций, который предлагает эквиваленты для повседневных задач, выполняемых на языке R. Однако в отличие от аналогов CRAN-R, функции ScaleR масштабируемы для обработки сотен миллионов и миллиардов строк посредством параллельных вычислений. Это обеспечивает производительность, которой невозможно достичь при использовании распространенных пакетов с открытым исходным кодом. API ScaleR были разработаны компанией Revolution Analytics. После того как Microsoft приобрела эту компанию, API были интегрированы в SQL Server. Они являются кроссплатформенными, то есть поддерживают не только SQL Server, но и другие платформы.
- SQL Server также предлагает существующие функции и механизмы для интеграции и повышения производительности. Например, возможно использование ColumnStore индексов вместе с языком R, чтобы быстрее выполнять аналитические запросы. Встроенный механизм управления позволяет контролировать ресурсы, выделенные для среды выполнения R. А службы SQL Server Integration Services (SSIS) обеспечивают бесшовную интеграцию с ETL и планирование регламентных задач через интерфейс хранимых процедур.
- Другая важная особенность R Services заключается в том, что пользователи могут работать со знакомыми инструментами:
- Специалисты, занимающиеся изучением данных, имеют возможность использовать привычную среду R IDE. Они могут работать с R Studio, с недавно анонсированными средствами R Tools for Visual Studio или с любой средой IDE по своему усмотрению. Вместо того чтобы переносить данные на локальную машину, можно воспользоваться средством ScaleR для удаленного выполнения непосредственно из IDE. Применение средств SQL Server 2016 для анализа внутри БД позволяет ускорить анализ больших наборов данных.
- К услугам администраторов серверов — хорошо им знакомое ПО SQL Server Management Studio для управления службами R Services, контроля ресурсов и назначения прав доступа.
- Разработчикам приложений доступны API T-SQL. Они могут создавать отчеты SQL Server Reporting Services или панели мониторинга Power BI с оценками, прогнозами и графикой из R без необходимости изучать сам язык R.
- Инженеры данных могут комбинировать R с существующими потоками ETL и планировать регламентные задачи с помощью служб SQL Server Integration Services.
от Data Driven, в котором рассказывается о том, как службы SQL Server R Services помогают создавать интеллектуальные приложения, выполнять предиктивный анализ и извлекать ценную информацию из данных. Другие видео по R Services см. в плейлисте Data Driven и на канале Channel9.на сайте Cortana Analytics Gallery, образцы данных и кода на GitHub.
- Если вы ничего не знаете о языке R, пройдите наши онлайн-курсы, например на сайте Datacamp, где вы также сможете найти углубленные курсы по API ScaleR.
Представляем PolyBase
За последние 10 лет мир информационных технологий изменился до неузнаваемости. Интернет вещей, технологии обработки неструктурированных данных и снижение цен на ИТ-оборудование, — все это обусловило настоящий бум Больших данных. Появились решения (такие как Hadoop and HDFS), позволяющие обрабатывать огромные массивы полуструктурированных данных и при этом не требующие покупки дорогостоящего специализированного «железа». Все это открыло перед бизнесом новые возможности получения прибыли, однако «оборотной стороной медали» стала растущая сложность корпоративных платформ для работы с данными. Кроме того, источники данных стали гораздо более разнообразными. Зачастую компаниям приходится иметь дело сразу с несколькими разрозненными наборами данных: реляционными в SQL Server и нереляционными в HDFS. Если аналитику необходимо совместить анализ полуструктурированных и структурированных данных, то ему придется сначала скопировать их из одной среды в другую, что отнимает много времени и сил.
Но все в мире меняется к лучшему! Решение PolyBase в SQL Server 2016 снимает проблему разрозненности реляционных и полуструктурированных данных. Использование PolyBase и T-SQL позволяет пользователям отправлять запросы к данным HDFS так, как будто они хранятся на локальном SQL Server, что открывает массу новых возможностей для анализа.
- разовые запросы к Hadoop и SQL Server 2016 с помощью T-SQL;
- импорт данных из Hadoop или blob-хранилища Azure в SQL Server 2016;
- экспорт «холодных» реляционных данных в Hadoop или blob-хранилище Azure при сохранении возможности одновременной отправки запросов.
Неограниченно долгое хранение данных с технологией Stretch Database
Технология Stretch Database, входящая в состав СУБД SQL Server 2016, дает возможность хранить любой объем данных столько, сколько потребуется, без нарушения соглашения об уровне обслуживания бизнеса и высоких затрат на приобретение систем хранения данных уровня предприятия. В отличие от обычных решений, применяемых для хранения «холодных» данных, Stretch Database обеспечивает постоянный доступ к вашим данным за счет использования неисчерпаемых облачных ресурсов Azure, а также не требует модификации большинства приложений. Администраторам баз данных лишь нужно активировать хранение «холодных» данных в облаке.
- Перенос в облако всей таблицы: уже имеющуюся выделенную таблицу для «холодных» данных можно перенести целиком. Допустим, у вас есть таблицы Order_details и Order_details_history, последняя из которых содержит только «холодные» данные, переносимые из первой.
- Перенос «холодных» строк: если в одной и той же таблице есть и «горячие», и «холодные» данные, можно перенести в Azure только «холодные». Для этого достаточно указать, какие именно строки являются таковыми (обычно это определяется по дате или состоянию), а SQL Server позаботится о переносе.
- Получите пространство хранения корпоративного класса — сколько и когда нужно. Автоматизированное резервное копирование и георепликация включены по умолчанию.
- При необходимости масштабируйте вычислительные ресурсы и пространство хранения с учетом требований рабочей задачи и платите только за используемый объем.
- При помощи встроенного механизма безопасности централизованно управляйте доступом для заказчиков, объединивших локальный Active Directory с Azure Active Directory.
- Пользуйтесь имеющимися знаниями и инструментами, такими как SQL Server Management Studio, SQL Server Data Tools, T-SQL и PowerShell, и расширяйте доступные возможности посредством портала Azure.
Виртуальный Форум
Мы рады пригласить Вас на ключевое событие года в мире данных – Виртуальный Форум Microsoft «Данные. Технологии. SQL Server 2016», который состоится 8 июня 2016 года.
- SQL Server 2016: новые стандарты в мире транзакции;
- Бизнес-aналитика: SQL, Power BI, R, Mobile;
- Azure: новое поколение решений для аналитики и Big Data.
- Каждый участник сможет посетить выставку партнерских решений.
- Пообщаться с технологическими экспертамиMVP, задать интересующие вопросы.
- Задать интересующие вопросы докладчикам.
- Получить все необходимые материалы и презентации.
- И даже выйграть призы – самые активные получат один из 30 сертификатов на сдачу экзаменов по SQL Server 2016.
Участие в форуме бесплатное. Необходима предварительная регистрация.
Дополнительно
Предлагаем вам обратить внимание на блог Сергея Олонцева, где вы можете найти множество материалов, посвященных SQL Server 2016.
What is PolyBase and How To Install PolyBase in SQL Server
This article contains information about PolyBase in sql server. Use cases, installation steps,configuring polybase cluster.
- We can bridge between SQL Server and Hadoop, PDW, Azure Blob Storage, or Azure Data Lake Store.
- We can access SQL Server from the above mentioned environments outside of SQL Server using TSQL.
- With SQL Server 2019, we can now use Polybase to access Oracle, Teradata and MongoDB.
With the use of different technologies according to the need, we need to query both relational and non-relational data at the same time. Polybase is a bridge between these two types of systems.
PolyBase Use Cases
- We can query the data on Hadoop using TSQL over SQL Server or PDW.
- We can query the data on Azure Blob Storage using TSQL over SQL Server.
- We can import data to SQL Server through Hadoop, Azure Blob Storage, or Azure Data Lake Store.
- We can export data from SQL Server to Hadoop, Azure Blob Storage, or Azure Data Lake Store.
- We can use PolyBase with third party tools supported by Microsft BI or SQL Server.
- We can use PolyBase to access Oracle,Teradata or MongoDB
PolyBase Installation Requirements
To install PolyBase, your server must have the following components.
- 64-bit SQL Server Instance
- Microsoft .NET Framework 4.5
- Oracle Java SE RunTime Environment (JRE) (at least version 7.51)
- Minimum 4 GB memory
- Minimum 2 GB disk
- The TCP / IP protocol must be enabled. You can find the required information in the article “SQL Server Configuration Manager Settings“.
- Systems such as Azure, HADOOP. Because we will use PolyBase with SQL Server and other external systems.
Install PolyBase
During SQL Server Installation, we need to select PolyBase Query Service for External Data in the Feature Selection screen as follows. For detailed information about the installation, please read the article “How To Install SQL Server“.
After the above screen, we see a screen like the one below.
Use this SQL Server as standalone PolyBase-enabled instance:
This means that we have selected SQL Server Intance as the Standalone head node. This means that we will process the data read from Hadoop on a single instance.
Use this SQL Server as a part of PolyBase scale-out group:
We may experience performance problems when processing large data on a single instance. By selecting this option, we select the instance we are installing as a Compute node in a cluster of SQL Server Instances to process large data with PolyBase Group feature.
If your organization has a firewall, you will need to open the ports specified in the following screenshot among all nodes in the PolyBase scale-out group. Here you can also specify the port range yourself. The port range you specify must be minimum 6. Even if it is more than 6, SQL Server will use the first 6 ports during the installation.
The following screenshot from Microsoft’s website summarizes the situation very well.
You can see the options mentioned above in the screenshot below.
On the next screen, the Server Configuration screen, we need to specify accounts for the SQL Server PolyBase Engine and SQL Server PolyBase Data Movement services.
If we use the PolyBase scale-out group in the second option above, the PolyBase engine and PolyBase data movement services on all nodes must use the same domain account.
Click Next, Next and Finish to complete the installation.
If PolyBase is not installed on Instance, clicking on the + sign on PolyBase via SSMS will throw an error as follows.
The PolyBase feature must be installed on the server instance before you can create a PolyBase group on this instance.
After the installation is complete, you must configure SQL Server to connect to external sources such as Azure, HADOOP.
With the following command, you can configure as follows to connect to Azure Blob Storage. By default, this setting is 7 after installation. You can set one of the following options for another connection type.
What is PolyBase .
PolyBase enables your SQL Server instance to process Transact-SQL queries that read data from external data sources.
Starting from SQL Server 2019 CTP 2.0, you can use polybase to connect to ORACLE, TERADATA, MONGODB additionally to access external data in Hadoop and Azure Blob Storage which already exists from SQL 2016 and higher.
Why use PolyBase?
In the past it was more difficult to join your SQL Server data with external data. You had the two following unpleasant options:
- Transfer half your data so that all your data was in one format or the other.
- Query both sources of data, then write custom query logic to join and integrate the data at the client level.
PolyBase avoids those unpleasant options by using T-SQL to join the data. You query external data by using the same T-SQL syntax used to query a database table. The support actions implemented by PolyBase all happen transparently.
How-To-Guides
Install
When install SQL server instance, you must select PolyBase Query Service for External Data feature from installation Wizard.
Enable PolyBase
I use new tool from Microsoft called “Azure Data Studio” to run required command to enable and configure PolyBase feature. Also you can use SQL Server Management Studio.