| D toc |
|---|
| Excerpt | ||||||
|---|---|---|---|---|---|---|
This section describes how to create connections to
|
...
. |
| Tip |
|---|
Tip: This connection is now known as Azure Synapse Analytics. |
...
| Operation |
| Amazon | Microsoft Azure | |
|---|---|---|---|---|
| Read | Not supported | Not supported | Supported | |
| Write | Not supported | Not supported | Supported |
Overview
Table types
| D s conntype | ||
|---|---|---|
|
| Table type | Description | |||
|---|---|---|---|---|
| Managed table | Managed tables are database tables that are specifically defined within the database server. Read and write are supported. | |||
| External table | ||||
| Info | 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.
|
SQL pool types
| D s conntype | ||
|---|---|---|
|
| Tip | ||||
|---|---|---|---|---|
Tip: The type of SQL pooling in use is determined by the URL that you use to connect to
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
|
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 |
Supported table types: External tables only
Limitations
connections are available only if you have deployed theD s conntype type sqldatawarehouse
onto Azure.D s platform r true SSL connections to
are required.D s conntype type sqldatawarehouse 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
connections.D s conntype type sqldatawarehouse - 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
to be shared by all relational connections. For more information, see Create Encryption Key File.D s node
...
The
| D s platform |
|---|
| D s conntype | ||
|---|---|---|
|
| Connection Type | Description | Notes | ||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| SQL DW Read-Only | Read-only access to the
To create, see Import Data Page. | This connection requires fewer permissions on the data warehouse and its databases but is less performant. | ||||||||||||||||
| SQL DW Read-Write | Read-write access to the
To create, see Connections Page. | This connection requires more permissions. You must also specify an External Datasource Name. See below.
|
...
| D s conntype | ||
|---|---|---|
|
When specifying a SQL DW Read
| D s conntype | ||
|---|---|---|
|
| Info | ||||
|---|---|---|---|---|
NOTE: This setting is not used for
|
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
is used.D s platform
Requirements:
- The external data source must be created by the database admin on the default database defined in the SQL DW connection. For more information, see https://docs.microsoft.com/en-us/sql/t-sql/statements/create-external-data-source-transact-sql?view=azure-sqldw-latest&tabs=dedicated.
- The External Data Source must point to the same storage location as the base storage layer for the
. For example, if the base storage layer is WASB, the External Datasource must point to the same storage account defined inD s platform
. If this configuration is incorrect, then publishing and ingestion of data fail.D s item item configuration - For more information on privileges required for the authenticating DB user, see https://docs.microsoft.com/en-us/sql/t-sql/statements/create-external-table-transact-sql.
...
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:
| Property | Description | ||||
|---|---|---|---|---|---|
| Host | Enter your hostname. Example:
| ||||
| Port | Set this value to 1433. | ||||
| Database | Set this value to the default database name. | ||||
| External Data Source Name | For 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 Name | Username to use to connect to the database. | ||||
| Password | Password associated with the above username. | ||||
| Credential Type |
| ||||
| Default Column Data Type Inference | Set to
enabled. |
...
For more information, see Using SQL DW.
For more information on creating 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.
