Striim 3.9.4 / 3.9.5 documentation

AzureSQLDWHWriter

Writes to Azure SQL Data Warehouse.

Prerequisites:

  • deploy an Azure SQL Data Warehouse instance

  • deploy an Azure storage account to be used for staging the data

  • create an Azure SQL Data Warehouse login for use by Striim

  • create an Azure SQL Data Warehouse database scoped credential with the storage account name as the IDENTITY and the storage account access key as the SECRET

property

type

default value

notes

Account Access Key

java.lang.String

the account access key for the storage account from Storage accounts > <account name> > Access keys

Account Name

java.lang.String

the storage account name

Column Delimiter

java.lang.String

|

If the data to be written may contain the default column delimiter (ASCII / UTF-8 124), specify a different delimiter that will never appear in the data.

Connection URL

java.lang. String

the JDBC connection URL for Azure SQL Data Warehouse, in the format jdbc:sqlserver://<fully qualified server name>:<port>;database=<database name>, for example, jdbc:sqlserver://mysqldw.database.windows.net:1433;database=mydb

Excluded Tables

java.lang. String

When a wildcard is specified for Tables, you may specify here any tables you wish to exclude. Specify the value as for Tables. For example, to include data from HR_EMPLOYEES and HR_DEPTS but not from HRMASTER when writing to SQL Server (since you cannot specify a literal underscore in the Tables string):

Tables='HR%',
ExcludedTables='HRMASTER'

Password

com. webaction. security. Password

The password for the specified user. See Encrypted passwords.

Tables

java.lang. String

The name(s) of the table(s) to write to. The table(s) must exist in the DBMS and the user specified in Username must have insert permission.

When the target's input stream is a user-defined event, specify a single table.

If the source table has no primary key, you may use the KeyColumns option to define a unique identifier for each row in the target table: for example, Tables:'SOURCEDB.EMP,MYDB.MYSCHEMA.EMP KeyColumns(EMP_ID)'. If necessary to ensure uniqueness, specify multiple columns with the syntax KeyColumns(<column 1>,<column 2>,...). You may use wildcards for the source table, provided all the tables have the key columns: for example, Tables:'SOURCEDB.%,MYSCHEMA.% KeyColumns(...)'.

When the input stream of the target is the output of a DatabaseReader, IncrementalBatchReader, or SQL CDC source (that is, when replicating data from one database to another), it can write to multiple tables. In this case, specify the names of both the source and target tables. You may use wildcards for the table names, but not for the schema or database. For example:

source.emp,target.emp
source.db1,target.db1;source.db2,target.db2
source.%,target.%
source.mydatabase.emp%,target.mydb.%
source1.%,target1.%;source2.%,target2.%

MySQL and Oracle names are case-sensitive, SQL Server names are not. Specify names as <schema name>.<table name> for MySQL and Oracle and as <database name>.<schema name>.<table name> for SQL Server.

See Mapping columns for additional options.

Upload Policy

java.lang. String

eventcount:10000, interval:5m

The upload policy may include eventcount, interval, and/or filesize (see Setting output names and rollover / upload policies for syntax). Cached data is written to the storage account every time any of the specified values is exceeded. With the default value, data will be written every five minutes or sooner if the cache contains 10,000 events. When the app is undeployed, all remaining data is written to the storage account.

Username

java.lang. String

the user name Striim will use to log in to the Azure SQL Data Warehouse specified in ConnectionURL

The following sample application would read from Oracle using IncrementalBatchReader and write to Azure SQL Data Warehouse.

CREATE  SOURCE ibr2azdw_Source USING IncrementalBatchReader  ( 
  Username: 'striim',
  Password: '********',
  ConnectionURL: '192.0.2.1:1521:orcl',
  Tables: 'MYSCHEMA.TABLE1',
  CheckColumn: 'MYSCHEMA.TABLE1=UUID',
  StartPosition: 'MYSCHEMA.TABLE1=1234'
) 
OUTPUT TO ibr2azdw_Source_Stream ;

CREATE  TARGET ibr2azdw_AzureSQLDWTarget1 USING AzureSQLDWHWriter  ( 
  Username: 'striim',
  Password: '********',
  ConnectionURL: 'jdbc:sqlserver://testserver.database.windows.net:1433;database=rlsdwdb',
  Tables: 'MYSCHEMA.TABLE1,dbo.TABLE1',
  AccountName: 'mystorageaccount'
  AccountAccessKey: '********'
) 
INPUT FROM ibr2azdw_Source_Stream;
AzureSQLDWHWriter data type support and conversion

TQL type

Azure SQL Data Warehouse type

java.lang.Byte

tinyint

java.lang.Double

float

java.lang.Float

float

java.lang.Integer

int

java.lang.Long

bigint

java.lang.Short

smallint

java.lang.String

char, nchar, nvarchar, varchar

org.joda.time.DateTime

datetime, datetime2, datetimeoffset

When the input of an AzureSQLDWHWriter target is the output of a MySQL source (DatabaseReader, IncremenatlBatchReader, or MySQLReader):

MySQL type

Azure SQL Data Warehouse type

bigint

bigint

bigint

numeric

bigint unsigned

bigint

binary

binary

char

nchar

char

uniqueidentifier

date

date

datetime

datetime

datetime

datetime2

datetime

datetimeoffset

decimal

decimal

decimal unsinged

decimal

double

money

double

smallmoney

float

float

float

real

int

int

int unsigned

int

longblob

varbinary

longtext

varchar

mediumblob

binary

mediumint

int

mediumint unsigned

int

mediumtext

varchar

numeric unsigned

int

smallint

smallint

smallint unsigned

smallint

text

varchar

time

time

tinyblob

binary

tinyint

tinyint

tinyint(1)

bit

tinyint unsigned

tinyint

tinytext

varchar

varbinary

varbinary

varchar

nvarchar

varchar

varchar

year

varchar

When the input of an AzureSQLDWHWriter target is the output of an Oracle source (DatabaseReader, IncremenatlBatchReader, or OracleReader):

Oracle type

Azure SQL Data Warehouse type

binary_double

float

binary_float

real

blob

binary

blob

varbinary

char

char

clob

nvarchar

clob

uniqueidentifier

date

date

float

float

nchar

nchar

nclob

varchar

number(1)

bit

number(10,4)

smallmoney

number(10)

int

number(19,4)

money

number(19)

bigint

Number(3)

tinyint

number(5)

smallint

Number(5)

char

timestamp(3)

datetime

timestamp(3)

datetime2

timestamp(3)

datetimeoffset

timestamp with local timezone

datetimeoffset

timestamp with timezone

datetimeoffset

varchar2

varchar

varchar2(30)

time

xmltype

varchar

When the input of an AzureSQLDWHWriter target is the output of a SQL Server source (DatabaseReader, IncremenatlBatchReader, or MSSQLReader):

SQL Server type

Azure SQL Data Warehouse type

bigint

bigint

binary

binary

bit

bit

bit

char

date

date

datetime

datetime

datetime2

datetime2

datetimeoffset

datetimeoffset

decimal

decimal

float

float

image

varbinary

int

int

money

money

nchar

nchar

ntext

varchar

numeric

numeric

nvarchar

nvarchar

nvarchar(max)

nvarchar

real

real

smalldatetime

smalldatetime

smallint

smallint

smallmoney

smallmoney

text

varchar

time

time

tinyint

tinyint

uniqueidentifier

uniqueidentifier

varbinary

varbinary

varbinary(max)

varbinary

varchar

varchar

varchar(max)

varchar

xml

varchar