Striim 3.9.6 documentation

Spanner Writer

Writes to one or more tables in Google Cloud Spanner.

property

type

default value

notes

Batch Policy

java.lang.String

eventCount: 1000, Interval: 60s

The batch policy includes eventcount and interval (see Setting output names and rollover / upload policies for syntax). Events are buffered locally on the Striim server and sent as a batch to the target every time either of the specified values is exceeded. When the app is stopped, all remaining events are sent to the target. To disable batching, set to EventCount:1,Interval:0.

With the default setting, data will be written every 60 seconds or sooner if the buffer accumulates 1000 events.

Checkpoint Table

java.lang.String

CHKPOINT

To support recovery (see Recovering applications, a checkpoint table must be created in each target database using the following DDL:Recovering applications

CREATE TABLE CHKPOINT (
  ID STRING(MAX) NOT NULL,
  SOURCEPOSITION BYTES(MAX)
) PRIMARY KEY (ID);

If necessary you may use a different table name, in which case change the value of this property. All databases must use the same checkpoint table name.

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.

Ignorable Exception Code

java.lang.String

By default, if the target DBMS returns an error, Striim crashes the application. Use this property to specify one or more numeric gRPC response codes to ignore, separated by semicolons.

Instance ID

java.lang.String

Specify the instance ID for the databases containing the tables to be written to. (Note: the instance ID may not be the same as the instance name.)

Service Account Key

java.lang.String

The path (from root or the Striim program directory) and file name to the .json credentials file downloaded from Google (see Service Accounts). This file must be copied to the same location on each Striim server that will run this adapter, or to a network location accessible by all servers. The associated service account must have the Cloud Spanner Database User or higher role for the instance (see Cloud Spanner Roles).

Tables

java.lang.String

The name(s) of the table(s) to write to, in the format <database>.<table>. The table(s) must exist when the application is started.

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

When the input stream of the target is the output of a CDC or DatabaseReader 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:

SOURCEDB.EMP,target.emp;SOURCEDB.DEPT,target.dep
SOURCEDB.%,targetdb.%

Known issue DEV-18018: Per Spanner's documentation, table names are case-insensitive, but if the table name specified here does not match the case shown in the Spanner UI, writing may fail.

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.

The following example application will copy all tables from two Oracle source schemas to tables with the same names in two Spanner databases. All source and target tables must exist before the application is started.

CREATE SOURCE OracleSource1 USING OracleReader (
  Username:'myname',
  Password:'******',
  ConnectionURL: 'localhost:1521:XE’,
  Tables:'MYDB1.%;MYDB2.%’
) 
OUTPUT TO sourceStream;

CREATE TARGET SpannerWriterTest USING SpannerWriter(
  Tables:'ORADB1.%,spandb1.%;ORADB2.%,spandb2.%',
  ServiceAccountKey: '<path>/<filename>.json',
  instanceId: 'myinstance'
)
INPUT FROM sourceStream;
SpannerWriter data type support and conversion

TQL type

Spanner type

notes

Boolean

BOOL

byte[]

BYTES

DateTime

DATE, TIMESTAMP

Double, Float

FLOAT64, NUMERIC

Integer, Long

INT64

String

STRING

maximum permitted length in Spanner is 2,621,440

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

Oracle type

Spanner type

notes

BINARY_DOUBLE

FLOAT64, NUMERIC

BINARY

FLOAT64, NUMERIC

BLOB

BYTES

CHAR

STRING

maximum permitted length in Spanner is 2,621,440

CLOB

DATE

DATE

FLOAT

FLOAT64, NUMERIC

LONG

STRING

maximum permitted length in Spanner is 2,621,440

NCHAR

STRING

maximum permitted length in Spanner is 2,621,440

NCLOB

STRING

maximum permitted length in Spanner is 2,621,440

NVARCHAR2

STRING

maximum permitted length in Spanner is 2,621,440

NUMBER

INT64

NUMBER(precision,scale)

FLOAT64, NUMERIC

RAW

BYTES

ROWID

STRING

maximum permitted length in Spanner is 2,621,440

TIMESTAMP

TIMESTAMP

TIMESTAMP

TIMESTAMP WITH LOCAL TIMEZONE

TIMESTAMP

TIMESTAMP

TIMESTAMP WITH TIMEZONE

TIMESTAMP

TIMESTAMP

UROWID

STRING

maximum permitted length in Spanner is 2,621,440

VARCHAR2

STRING

maximum permitted length in Spanner is 2,621,440

XMLTYPE

When the input of a SpannerWriter target is the output of a SQL Server source (DatabaseReader, IncremenatlBatchReader, or MySQLReader):

SQL Server type

Spanner type

bigint

INT64

binary

bit

bit

char

STRING

maximum permitted length in Spanner is 2,621,440

date

datetime

datetime2

datetimeoffset

decimal

FLOAT64, NUMERIC

float

FLOAT64, NUMERIC

image

int

INT64

money

nchar

ntext

numeric

nvarchar

nvarchar(max)

real

smalldatetime

smallint

INT64

smallmoney

text

STRING

maximum permitted length in Spanner is 2,621,440

time

tinyint

INT64

uniqueidentifier

varbinary

varchar

STRING

maximum permitted length in Spanner is 2,621,440

xml