Skip to main content

Spanner Database Reader

You can use Spanner Database Reader to read all the data that exists in a Spanner database at the time the Striim application containing Spanner Database Reader is started. The most common use case is to load this existing data to the target to establish an initial, point-in-time copy of the source data that can serve as a starting point for subsequent continuous updates or syncs. This process is called "initial load" in Striim, and "historical sync" or "initial snapshot" by others. After completing the initial load using Spanner Database Reader, you can use Spanner Batch Reader to continuously load updates to the source database or data warehouse to the target.

Spanner Database Reader supports:

  • reading from Spanner databases that both GoogleSQL and PostgreSQL dialects

  • Fast Snapshot Recovery (see Fast Snapshot Recovery during initial load)

  • Auto Quiesce on IL Completion for those targets that have that property (the application automatically quiesces when initial load is complete)

Unlke Database Reader, Spanner Database Reader does not support Auto Schema Creation (automatically creating schemas and tables in the target matching those in the source).

Spanner Database Reader properties

property

type

default value

notes

Connection URL

String

Specify as jdbc:cloudspanner:/projects/<project ID>/instances/<instance ID>/databases/<database>?credentials=<service account key>.

  • <project ID> is the name of the Google Cloud project containing the Spanner instance to be read.

  • <instance ID> is Spanner instance within the specified project.

  • <database> is the name of the database to be read within the specified instance. To read from more than one database, create multiple instances of Spanner Reader.

  • <service account key> is a Google Cloud service account with the SELECT privilege on the specified database and on the INFORMATION_SCHEMA for that database.

Excluded Tables

String

Data for any tables specified here will not be returned. For example, if Tables uses a wildcard, data from any tables specified here will be omitted. Multiple table names (separated by semicolons) and wildcards may be used exactly as for Tables.

Fetch Size

Integer

100

Sets the maximum number of records to be fetched from the database in a single JDBC method execution (see the discussion of fetchsize in the documentation for your JDBC driver).

Parallel Threads

Integer

1

For Fast Snapshot Loading to Azure Synapse, BiqQuery, Databricks, Fabric Data Warehouse, Microsoft Dataverse, or Snowflake, specify the number of threads Database Reader will use. This value should not be higher than the number of tables to be read. For other targets, leave set to the default of 1.

When Database Reader is started, the tables will be distributed among the threads in round-robin fashion, then each thread will read one table at a time. When the Tables property is a list of tables, the tables are read in that order. If the Tables property uses a wildcard, the sequence is determined by the order in which the JDBC driver gives Database Reader the list of tables.

Known issue DEV-49013: the maximum number of tables supported by Parallel Threads is 255.

Query

String

Optionally, specify a single SQL SELECT statement specifying the data to return. You may query tables, aliases, synonyms, and views.

Query is not supported when Parallel Threads is greater than 1..

When Query is specified and Tables is not, the WAEvent TableName metadata field value will be QUERY. When both Query and Tables are specified, the data specified by Query will be returned, and the Tables setting will be used only to populate the TableName field.

If the query includes a synonym containing a period, it must be enclosed in escaped quotes. For example: select * from \"synonym.name\"

If using a query when the output of a DatabaseReader source is the input of a DatabaseWriter target, specify the target table name as the value of DatabaseReader's Tables field.

Quiesce on IL Completion

Boolean

False

Restart Behavior on IL Interruption

Enum

Keep target table data

Do not change this setting. In this release, only the default value "Keep target table data" is supported.

See Fast Snapshot Recovery during initial load.

Return DateTime As

String

Java8DateTime

Do not change from the default. Only Java8DateTime is supported.

Statement Timeout

If you encounter deadline exceeded errors (see Troubleshoot Cloud Spanner deadline exceeded errors) with tables that have a large number of rows, set this property to increase the amount of time allowed to read tables. For example, to set the timeout to five seconds, specify 5s. You may specify the timeout in seconds (s), milliseconds (ms), microseconds (us), or nanoseconds (ns). If this property is blank, the timeout will be controlled by Spanner.

Tables

String

Specify the table(s) or view(s) to be read. For GoogleSQL tables in the default schema, specify only the table name. For GoogleSQL tables in named schemas and for PostgreSQL, specify the tables as <schema name>.<table name>.

You may specify multiple tables and views as a list separated by semicolons or with the % wildcard. For example, HR% would read all the tables whose names start with HR. You may use the % wildcard only for tables, not for schemas or databases. The wildcard is allowed only at the end of the string: for example, mydb.prefix% is valid, but mydb.%suffix is not.

If you are using the Query property, specify QUERY as the table name.

Modifying this property can interfere with recovery. If recovery is enabled for the application and it is necessary to modify the tables list, export the application (see Apps page), drop it, update the exported TQL with the new values, and import it (see Apps page).