Using components: Google Spanner Destination

Use the Google Spanner destination component to store the output of a data flow in a Google Spanner table.

Connection

Select an existing Google Spanner connection or create a new one (for more information, see Allowing Integrate.io ETL access to my Google Cloud Spanner instance.) 

Destination Properties

  • Target table - the name of the target table. By default, if the table doesn't exist, it will be created automatically.
  • Automatically create table if it doesn't exist - if unchecked and the table doesn't exist, the job fails.
  • Automatically add missing columns - when checked, the job will check if each of the specified columns exist in the table and if one does not exist, it will add it. Key columns can't be automatically added to a table.

Operation type

Append (Insert only) - default behavior. Data will only be appended to the target table

Merge with existing data using Insert or Update - If the key values are found in the table, update the non-key columns provided in the flow. If it doesn't exist, insert new row to the table.

Merge with existing data using Replace - Rows that exist in the table (found by their key values) are deleted, and the column data provided in the flow is inserted.

Pre and post action SQL

Pre-action SQL - SQL code to execute before inserting the data into the target table.

Post-action SQL - SQL code to execute after inserting the data into the target table. 

Schema Mapping 

Map the dataflow fields to the target table's columns. Columns defined as key will be used as the sort key when Integrate.io ETL creates the table. If merge operation is used, you must select at least a field or multiple fields as keys, which will be used to uniquely identify rows in the table for the merge operation.

The following table displays matching the matching data types:

Integrate.io ETL Cloud Spanner
String STRING(MAX)
Integer INT64
Long INT64
Float FLOAT64
Double FLOAT64
DateTime TIMESTAMP
Binary BYTES(MAX)
Boolean BOOL

    Automatically creating and altering destination table

    Integrate.io ETL can automatically create the destination table for you if it doesn't exist and it can also append columns to an existing table. If you define columns as key (regardless of the operation type), Integrate.io ETL defines them as the primary key in a new table. The data types in Integrate.io ETL are automatically mapped as defined in the table above. Note that since Integrate.io ETL doesn't have a notion of maximum string length, the string columns are created with the maximum length allowed.