Use SQL Server 2019 PolyBase to Virtualize a Sybase IQ Database

This post has been republished via RSS; it originally appeared at: Premier Field Engineering articles.

 

 

Introduced with SQL Server 2016, the PolyBase feature enabled access to non-relational data stores, namely Hadoop and Azure Blob Storage all while using the familiar and popular Transact-SQL language. 

SQL Server 2019 builds upon that foundation by unlocking the ability to access many new relational and non-relational data stores such as Oracle, Teradata, MongoDB, SQL Server and any ODBC-compatible data source.  These expanded capabilities enable SQL Server 2019 to act as a hub or conduit to many disparate data sources. 

This article will focus specifically on leveraging PolyBase in SQL Server 2019 to virtualize a Sybase IQ data source.  Let’s get started.

 

Demo environment topology

• Sybase IQ environment
  • Windows Server 2019
  • Sybase IQ 16.1.030.1031 sp03
  • Database: iqdemo
  • Port: 2638

 

• SQL Server environment
  • Windows Server 2019
  • SQL Server 2019 Developer 15.0.2000.5
  • Database: FourthCoffee
  • Port: 1433

 

Step One – Enable the PolyBase feature

This step assumes that the PolyBase feature (PolyBase Query Service for External Data) is installed but not yet enabled.  If you are unsure whether the PolyBase feature is installed on the server, use the query below to validate.

 

SELECT SERVERPROPERTY ('IsPolyBaseInstalled') AS IsPolyBaseInstalled;

 

To enable PolyBase, use the sp_configure stored procedure as shown below.

 

EXEC sp_configure @configname = 'polybase enabled' ,@configvalue = 1; GO RECONFIGURE WITH OVERRIDE;

 

 

 

 

Use SQL Server Configuration Manager to ensure the “SQL Server PolyBase Engine (<Instance Name>)” service is in a Running state.

 

 

Step Two – Create a Database Master Key

If one does not already exist, we’ll need to create a Database Master Key (DBMK).  The DBMK is used to protect any secrets associated with the credential we’ll be creating in the next step.  Follow the example below to create a DBMK (but use a stronger password!).

 

 

USE FourthCoffee GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pass@word!!!' -- -- Validate key creation -- SELECT * FROM sys.symmetric_keys

 

 

 

Step Three – Create a Database Scoped Credential

Now that we have a DBMK, we can create a database credential.  A database credential is used by the database to access an external location.  In the case of this article the external location is a Sybase database.  Note that a database credential is not mapped to a server login or database user and should not be considered an orphaned login.

The example below is creating a database credential called “SybaseIQreadOnly” with an identity (think of this like a username) of “SybaseReadOnly”.

 

 

USE FourthCoffee GO CREATE DATABASE SCOPED CREDENTIAL SybaseIQreadOnly WITH IDENTITY = 'SybaseReadOnly', SECRET = 'Pass@word!!!'; GO SELECT * FROM sys.database_credentials

 

 

The database master key created in the previous step will be used to encrypt and protect the secret associated with the database credential.

 

Step Four – Create the External Data Source

The last step is to create the external data source which represents the actual connection string and associated properties.  In the example below, we’re connecting to Sybase using an ODBC connection and are providing the various connection options required for successful connection.  Note that the required connection options will vary based on the external data source and the full list and description of valid options can be found here.

 

Let’s decode our Sybase external data source a bit.

 

 

 

USE FourthCoffee GO CREATE EXTERNAL DATA SOURCE [SybaseIQ] WITH ( LOCATION = N'odbc://WIN-4USCI1DM3AF:2638' ,CONNECTION_OPTIONS = 'DRIVER={Sybase IQ}; Host=WIN-4USCI1DM3AF:2638; Provider = ''DBODBC17''; ServerName= win-4usci1dm3af_iqdemo; DatabaseName=iqdemo;' ,PUSHDOWN = ON ,CREDENTIAL = [SybaseIQreadOnly] ) -- SELECT [name],[location],[type],[connection_options],[pushdown] FROM sys.external_data_sources

 

 

 

Not bad at all.  Four easy steps to create and connect to an external data source.  Now let’s investigate what that provides.

The first thing that we need to do is create an external table for the external data source.  An external table is essentially a virtual representation within SQL Server of a table in the external data source.

 

Let’s connect to the “emp1” table in the “iqdemo” database on our Sybase IQ instance.

 

This is what the “emp1” table looks like in Sybase.

 

 

 

 

USE FourthCoffee GO CREATE EXTERNAL TABLE empl ( dept_id INT NULL, start_date date NULL, name nvarchar(20) NULL, salary INT NULL ) WITH ( LOCATION='emp1', DATA_SOURCE=[SybaseIQ] );

 

 

 

Here we can see the external data source and external table in our FourthCoffee database.

 

 

Example One

A simple query in SQL Server Management Studio returns the same data that we see when using the Interactive SQL graphical query utility for Sybase.

 

 

 

Example Two

Another quick example is creating an external table based on the results of a view on the source system.  In this case I’ve created a view within the Sybase source system and an external table in SQL Server.

 

View in Sybase “iqdemo” database

 

 

 

Create an external table in SQL Server to reference the Sybase view.

 

 

 

Example Three

The final example is where the power and possibilities of PolyBase in SQL Server 2019 begin to really come into focus.  In this scenario, we’ll join an external table in the Sybase IQ database to a table in the FourthCoffee database hosted on the SQL Server 2019 instance.

Just like the previous examples, first we create an external table in the FourthCoffee database that references the “Customers” table in the Sybase IQ database.  Note that for this example I’ve created a separate schema, “SybaseIQ”, for the “Customers” external table.

 

 

 

Now that we have our external table, let’s display customer results between the FourthCoffee.dbo.Customers table and the SybaseIQ.Customers external table where customers are in the same PostalCode.  Note the query below is written for illustrative purposes only.

 

 

What we’ve accomplished in this final example is to join to disparate datasets in two disparate database systems without performing any extraction, transformation or loading (ETL) of data between systems.  And we accomplished this using only a familiar and simple Transact-SQL query. 

Taking this a step further, we could perform the same join of disparate datasets between Hadoop, for instance, and a Sybase IQ database using a Transact-SQL query with PolyBase on SQL Server 2019 serving as the central coordinator.  Again, with no ETL involved.

 

Key points

• When using simple SELECT statements as those shown above, query results are not persisted.  Data is stored in temp tables which are immediately dropped upon query completion.

To persist data into a SQL Server table from an external data source, use the SELECT INTO FROM EXTERNAL TABLE or INSERT INTO SELECT FROM Transact-SQL query syntax.

 

• For servers hosting multiple instances of SQL Server, PolyBase can only be installed on one of those instances.

 

PolyBase Resources

• What is PolyBase?
• Install PolyBase on Windows
• Install PolyBase on Linux
• Database Scoped Credentials
• External Data Source
• External Table