Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article, we will be talking about What is Polybase? and how we can extract data from SQL to SQL with Polybase.
Polybase is one of the auxiliary tools that SQL Server offers us.
Thanks to this, we can join our tables from different sources into a single database and make queries.
Some of these sources include MSSQL, Postgresql, MYSQL, Hadoop, Azure blog storage, etc.
In order to use Polybase, we need to select the fields related to Polybase during the installation.
After selecting this, you can perform the installations.
SQL201901 – Instance where data will be collected
SQL201902 – Instance where data will be retrieved
First, we enable it via sp_configure. (SQL201901)
1 2 | EXEC SP_CONFIGURE @CONFIGNAME = 'polybase enabled', @CONFIGVALUE = 1; RECONFIGURE WITH OVERRIDE; |
After enabling, we create a database in the instance where our data will be collected.
1 2 3 4 5 6 7 8 9 10 | CREATE LOGIN [PolyBaseUser] WITH PASSWORD=N'farukerdem.com', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO USE [d1] GO CREATE USER [PolyBaseUser] FOR LOGIN [PolyBaseUser] GO USE [d1] GO ALTER ROLE [db_datareader] ADD MEMBER [PolyBaseUser] GO |
Continue with the polybase configurations by coming back to the SQL201901 server.
We create our credential for the connection.
1 2 3 4 | USE PolyBaseD1 go CREATE DATABASE SCOPED CREDENTIAL d1 WITH IDENTITY = 'polybaseuser', Secret = 'farukerdem.com'; |
We create a data source and fill in the access information.
1 2 3 4 5 6 | CREATE EXTERNAL DATA SOURCE PolyBaseD1_DS WITH ( LOCATION = 'sqlserver://DESKTOP-V0UFSNU' , CONNECTION_OPTIONS = 'Server=%s\SQL201902' , CREDENTIAL = d1 ); |
We have created the Credential and Data Source and our connections are complete. We are creating an external table appropriate for the table we want to retrieve data from.
1 2 3 4 5 6 7 8 | CREATE EXTERNAL TABLE [dbo].[EX_Polybase_table] ( [id] [int] NULL, [adi] [varchar](50) NULL, [soyadi] [varchar](50) NULL ) WITH (DATA_SOURCE = [PolyBaseD1_DS],LOCATION = N'[d1].dbo.Polybase_table') GO |
As seen below, our table was created under the external table heading in the database.
We can use the familiar select statement to query data.
