Events
Mar 31, 11 p.m. - Apr 2, 11 p.m.
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Applies to:
SQL Server
This article explains how to use PolyBase on a SQL Server instance to query external data in Teradata.
If you haven't installed PolyBase, see PolyBase installation. The installation article explains the prerequisites.
Before creating a database scoped credential a Master Key must be created.
To use PolyBase on Teradata, VC++ redistributable is needed.
To query the data from a Teradata data source, you must create external tables to reference the external data. This section provides sample code to create these external tables.
The following Transact-SQL commands are used in this section:
Create a database scoped credential for accessing the Teradata source.
/* specify credentials to external data source
* IDENTITY: user name for external source.
* SECRET: password for external source.
*/
CREATE DATABASE SCOPED CREDENTIAL credential_name WITH IDENTITY = 'username', Secret = 'password';
Important
The Teradata ODBC Connector for PolyBase supports only basic authentication, not Kerberos authentication.
Create an external data source with CREATE EXTERNAL DATA SOURCE.
/* LOCATION: Location string should be of format '<vendor>://<server>[:<port>]'.
* PUSHDOWN: specify whether computation should be pushed down to the source. ON by default.
* CONNECTION_OPTIONS: Specify driver location
* CREDENTIAL: the database scoped credential, created above.
*/
CREATE EXTERNAL DATA SOURCE external_data_source_name
WITH (LOCATION = teradata://<server address>[:<port>],
-- PUSHDOWN = ON | OFF,
CREDENTIAL = credential_name);
Create an external table with CREATE EXTERNAL TABLE.
/*
* LOCATION: Two-part identifier indicating the database and the table name.
* DATA_SOURCE: Data source created above.
*/
CREATE EXTERNAL TABLE [TableC] (
[MyKey] INT NOT NULL,
[RandomInt] INT NOT NULL,
[RandomFloat] DECIMAL(13, 2) NOT NULL)
WITH (
LOCATION = 'TD_SERVER_DB.TableC',
DATA_SOURCE = external_data_source_name)
Optional: Create statistics on an external table.
We recommend creating statistics on external table columns, especially the ones used for joins, filters and aggregates, for optimal query performance.
CREATE STATISTICS statistics_name ON customer (C_CUSTKEY) WITH FULLSCAN;
Important
Once you have created an external data source, you can use the CREATE EXTERNAL TABLE command to create a queryable table over that source.
For more tutorials on creating external data sources and external tables to a variety of data sources, see PolyBase Transact-SQL reference.
To learn more about PolyBase, see Overview of SQL Server PolyBase.
Events
Mar 31, 11 p.m. - Apr 2, 11 p.m.
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayTraining
Module
Introduction to SQL Server 2022 data virtualization - Training
Learn about data virtualization, how to use Polybase to access and query external data, and enhanced Polybase features in SQL Server 2022.
Certification
Microsoft Certified: Azure Data Engineer Associate - Certifications
Demonstrate understanding of common data engineering tasks to implement and manage data engineering workloads on Microsoft Azure, using a number of Azure services.
Documentation
Access external data: ODBC generic types - PolyBase - SQL Server
PolyBase in SQL Server allows you to connect to compatible data sources through the ODBC connector. Install the ODBC driver and create external tables.
Introducing data virtualization with PolyBase - SQL Server
PolyBase enables your SQL Server instance to process Transact-SQL queries that read data from external data sources, such as Azure Blob Storage.
Access external data: Hadoop - PolyBase - SQL Server
The article uses PolyBase on a SQL Server instance with Hadoop. PolyBase is suited for ad hoc queries of external tables and data import/export.