Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Published by Scroll Versions from space DEV and version r092

D toc

Excerpt

This section describes how to create connections to 

D s conntype
rtrue
typesqldatawarehouse

...

.


Tip

Tip: This connection is now known as Azure Synapse Analytics.

...

Operation

D s product

AmazonMicrosoft Azure
ReadNot supportedNot supportedSupported
WriteNot supportedNot supported

Supported

Info

NOTE: Additional configuration is required.

...

Overview

Table types

D s conntype
typesqldatawarehouse

...

D s platform
rtrue

...

D s conntype
typesqldatawarehouse

...

 can interact with the following table types:

Table typeDescription
Managed table

Managed tables are database tables that are specifically defined within the database server.

Read and write are supported.

External table

External tables are references to files on the backend storage layer on top of which is a database schema. The table is defined by reading and writing through the database schema to the underlying file storage.

Info

NOTE:

...

Prerequisites

...

When publishing to an external table, the output file type is Parquet.

Info

NOTE: When publishing to an external table under ADLS user mode, the system credentials are used to write to the storage location and must have the appropriate permissions.

SQL pool types

d-s-

...

Connection Types

The 

conntype
typesqldatawarehouse
 supports two different SQL pooling methods through which you connect to your data managed by the database server. 

Tip

Tip: The type of SQL pooling in use is determined by the URL that you use to connect to

d-s-

...

conntype
typesqldatawarehouse
. URLs with ondemand in them are for serverless SQL pool connections.

Dedicated SQL pool

These connections utilize a fixed and dedicated set of SQL pool resources. The admin user can define the size and availability of these resources for performing work.

This connection requires more permissions. You must also specify an External Datasource Name. See below.

Tip

Tip: Spark-based jobs that read or write through your

D s conntype
typesqldatawarehouse

...

To create, see Import Data Page.

connection leverage PolyBase for faster performance.

Supported table types: Managed tables, external tables


Serverless SQL pool

These connections specify the SQL pool resources based on the size of the job. In theory, these connection types can scale infinitely for jobs of any size.

Tip

Tip: This connection requires fewer permissions on the data warehouse and its databases but is less performant

...

. The URL for these connections always contain ondemand.

Supported table types: External tables only

Limitations

  • D s conntype
    typesqldatawarehouse
     connections are available only if you have deployed the 
    D s platform
    rtrue
     onto Azure.
  • SSL connections to 

    D s conntype
    typesqldatawarehouse

...

  •  are required.

    Info

    NOTE: In this release, this connection cannot be created through the APIs. Please create connections of this type through the application.

    Info

    NOTE: Under Azure SSO, write operations are not supported through

...

To create, see Connections Page.

This connection requires more permissions. You must also specify an External Datasource Name. See below.

...

  • D s conntype
    typesqldatawarehouse
    connections.

  • JSON files cannot be read in through this connection type.
  • For custom SQL and file formats other than CSV and Parquet, data is read through CETAS (create external table and select).
    • In some cases, reading from CETAS tables may exceed 30 minutes, which is the read limit imposed by Azure. These jobs time out.
    • In timeout situations, you may be able to fall back to a direct JDBC read of these sources.    
  • When publishing to an external table, the output file type is always Parquet.

Prerequisites

  • If you haven't done so already, you must create and deploy an encryption key file for the 
    D s node
     to be shared by all relational connections. For more information, see Create Encryption Key File.

D s conntype
typesqldatawarehouse
 permissions

  • Read-Only connectionThe authenticating DB user must have read permissions to any 
    D s conntype
    typesqldatawarehouse
    , schemas and tables to which the user should have access.
  • Read-Write connectionIn addition to the above, the authenticating DB user must have the following permissions:

    Code Block
    CREATE TABLE**
    ALTER ANY SCHEMA
    ALTER ANY EXTERNAL DATA SOURCE
    ALTER ANY EXTERNAL FILE FORMAT

     

    • The authenticating DB user must also have read access to the external data source.

D s conntype
typesqldatawarehouse
 External Data Source Name

When specifying a SQL DW Read-Write connectionconnection to external tables, you can provide an External Data Source Name value as part of the connection definition. The External Data Source enables publishing and support for large-scale data ingestion. 

...

D s conntype
typesqldatawarehouse

...

When the External Data Source is provided:

  • CETAS (create external table and select) is used for reading in data.

If the External Data Source is not provided:

  • JDBC read is used for reading in data.
  • The connection is read-only.
  • The connection must be to a set of managed tables.
  • The native ingestion of the 
    D s platform
     is used.

...

...

To create this connection:

...

, see Connections Page.

...

For additional details on creating a relational connection, see Relational Access.

Please create a connection of this type in the appropriate page and modify the following properties with the listed values:

PropertyDescription
Host
Enter your hostname. Example:

 

Code Block
testsql.database.windows.net
Tip

Tip: If your Host value contains ondemand, then you are using serverless SQL pools.

PortSet this value to 1433.
DatabaseSet this value to the default database name.
External Data Source NameFor read-write external table connections, you must provide an External Data Source. Otherwise, the connection is read-only. See above for details.
Connect String options

Include any options required for your environment:

User NameUsername to use to connect to the database.
PasswordPassword associated with the above username.
Credential Type
  • basic - Specify username and password as part of the connection
  • Azure Token SSO - Use the SSO principal of the user creating the connection to authenticate to the SQL Server database. Additional configuration is required. See Enable SSO for Azure Relational Connections.
Default Column Data Type Inference

Set to disabled to prevent the

D s platform
from applying its own type inference to each column on import. The default value is enabled.

...

For more information, see Using SQL DW.

For more information on defining output objects, see Microsoft SQL Data Warehouse Table Settings.

Data Conversion

For more information on how values are converted during input and output with this database, see SQL DW Data Type Conversions.

D s also
labelsql_dw