Azure SQL | Polytomic Docs

Supports:

✅ Models
✅ Model sync destination
✅ Bulk sync source
✅ Bulk sync destination

Connection

Configuration

Name	Type	Description	Required
`blobStore`	`boolean`	Use Azure blob storage for faster bulk loading (destination only) ↓	`false`
`database`	`string`	Database	`true`
`hostname`	`string`	Server	`true`
`password`	`string`	Password	`true`
`port`	`integer`	Port	`true`
`ssh`	`boolean`	Connect over SSH tunnel ↓	`false`
`ssl`	`boolean`	Use SSL	`false`
`username`	`string`	Username	`true`

`blobStore`

When `blobStore` is `true`

Name	Type	Description	Required
`access_key`	`string`	Storage account access key (destination only)	`true`
`account_name`	`string`	Storage account name (destination only)	`true`
`container_name`	`string`	Storage container name (destination only)	`true`

`ssh`

When `ssh` is `true`

Name	Type	Description	Required
`ssh_host`	`string`	SSH host	`true`
`ssh_port`	`integer`	SSH port	`true`
`ssh_private_key`	`string`	Private key	`true`
`ssh_user`	`string`	SSH user	`true`

Example

1 {
2   "name": "Azure SQL connection",
3   "type": "azuresql",
4   "configuration": {
5     "blobStore": false,
6     "database": "sampledb",
7     "hostname": "example.database.windows.net",
8     "password": "secret",
9     "port": 1433,
10     "ssh": false,
11     "ssl": false,
12     "username": "user"
13   }
14 }

Model Sync

Source

Configuration

Name	Type	Description	Required
`query`	`string`		`false`
`table`	`string`	Table	`false`
`view`	`string`	View	`false`

Example

1 {
2   ...
3   "configuration": {
4     "query": "SELECT * from users",
5     "table": "users",
6     "view": "active_users"
7   }
8 }

Target

Azure SQL connections may be used as the destination in a model sync.

All targets

Configuration

Name	Type	Description	Required
`created_column`	`string`	’Created at’ timestamp column	`false`
`preserve_table_on_resync`	`boolean`	Preserve destination table when resyncing	`false`
`updated_column`	`string`	’Updated at’ timestamp column	`false`
`write_null_values`	`boolean`	Copy null values When enabled updates will set fields to NULL when the source value is null	`false`
`write_record_timestamps`	`boolean`	Write row timestamp metadata	`false`

Example

1 {
2   ...
3   "target": {
4       "configuration": {
5         "created_column": "",
6         "preserve_table_on_resync": false,
7         "updated_column": "",
8         "write_null_values": false,
9         "write_record_timestamps": false
10     }
11   }
12 }

Target creation

Azure SQL connections may be used to create a new target for a model sync. The following parameters are required to create a new target:

NAME	DESCRIPTION	ENUM
name	Table name	false

Bulk Sync

Source

Azure SQL connections may be used as a bulk sync source. No additional configuration options are required.

Destination

Configuration

Name	Type	Description	Required
`advanced`	`object`		`false`
`mirror_schemas`	`boolean`	Mirror schemas	`false`
`schema`	`string`	Output schema	`false`

Example

1 {
2   ...
3   "destination_configuration": {
4     "advanced": {
5         "empty_strings_null": false,
6         "hard_deletes": false,
7         "initial_execution": "",
8         "table_prefix": "",
9         "truncate_existing": false
10     },
11     "mirror_schemas": false,
12     "schema": "schema"
13   }
14 }

Type handling

Destination types

POLYTOMIC TYPE	AZURE SQL TYPE
`array<>`	`NVARCHAR(MAX)`
`bigint`	`BIGINT`
`boolean`	`BIT`
`date`	`DATE`
`datetime`	`DATETIMEOFFSET`
`decimal(precision, scale)`	`DECIMAL(precision,scale)`
`double`	`FLOAT`
`int`	`INT`
`json`	`NVARCHAR(MAX)`
`jsonarray`	`NVARCHAR(MAX)`
`number`	`NUMERIC(38,10)`
`object{}`	`NVARCHAR(MAX)`
`single`	`REAL`
`smallint`	`SMALLINT`
`string`	`NVARCHAR(4000)`
`time`	`TIME`

Source types

AZURE SQL TYPE	POLYTOMIC TYPE
`BIGINT`	`bigint`
`CHAR`	`string`
`DATE`	`date`
`DATETIME`	`datetime`
`DATETIME2`	`datetime`
`DATETIMEOFFSET`	`datetime`
`DECIMAL`	`number`
`DECIMAL(precision, scale)`	`decimal(precision, scale)`
`FLOAT`	`double`
`INT`	`int`
`MONEY`	`decimal(precision, scale)`
`NCHAR`	`string`
`NTEXT`	`string`
`NUMBER(precision, scale)`	`decimal(precision, scale)`
`NUMERIC`	`number`
`NUMERIC(precision, scale)`	`decimal(precision, scale)`
`NVARCHAR`	`string`
`REAL`	`single`
`SMALLDATETIME`	`datetime`
`SMALLINT`	`smallint`
`SMALLMONEY`	`decimal(precision, scale)`
`TEXT`	`string`
`TIME`	`time`
`TINYINT`	`smallint`
`UNIQUEIDENTIFIER`	`string`
`VARCHAR`	`string`
`XML`	`string`

Connection

Configuration

blobStore

When blobStore is true

ssh

When ssh is true

Example

Model Sync

Source

Configuration

Example

Target

All targets

Configuration

Example

Target creation

Bulk Sync

Source

Destination

Configuration

Example

Type handling

Destination types

Source types

`blobStore`

When `blobStore` is `true`

`ssh`

When `ssh` is `true`