What Is PolyBase In SQL Server

Faruk Erdem

1 month ago

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)

EXEC SP_CONFIGURE @CONFIGNAME = 'polybase enabled', @CONFIGVALUE = 1;
RECONFIGURE WITH OVERRIDE;

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.

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

CREATE LOGIN [PolyBaseUser] WITH PASSWORD=N'farukerdem.com', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

USE [d1]

CREATE USER [PolyBaseUser] FOR LOGIN [PolyBaseUser]

USE [d1]

ALTER ROLE [db_datareader] ADD MEMBER [PolyBaseUser]

Continue with the polybase configurations by coming back to the SQL201901 server.

We create our credential for the connection.

USE PolyBaseD1
go
CREATE DATABASE SCOPED CREDENTIAL d1
WITH IDENTITY = 'polybaseuser', Secret = 'farukerdem.com';

USE PolyBaseD1

CREATE DATABASE SCOPED CREDENTIAL d1

WITH IDENTITY = 'polybaseuser', Secret = 'farukerdem.com';

We create a data source and fill in the access information.

CREATE EXTERNAL DATA SOURCE PolyBaseD1_DS
WITH (
  LOCATION = 'sqlserver://DESKTOP-V0UFSNU' ,
  CONNECTION_OPTIONS = 'Server=%s\SQL201902' ,
  CREDENTIAL = d1
);

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.

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

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')

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.