Using components: Google Cloud Spanner Destination

Use the Google Cloud Spanner Destination component to store the output of your package in a Cloud Spanner table. 

To define the Google Cloud Spanner destination:

  1. Add a Google Cloud Spanner Destination component at the end of your dataflow.
  2. Open the component and name it.

Destination settings

  1. Define the parameters for connecting to your Cloud Spanner instance as follows (for more information, see Allow Xplenty access to my Google Cloud Spanner instance):
    • google cloud spanner connection - either click the drop-down arrow and select an existing connection, or click (create new) to create a new connection.
    • target table - the name of the target table in your database.
    • create table - automatically create the table defined in the component if it doesn't exist. See below for more information.
    • add columns - automatically add columns that are mapped in the destination and do not exist in the table. See below for more information. 
    • operation type - the method of data insertion
      • insert - default behavior. Data will only be appended to the target table.
      • 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.
      • 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 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.

    Then click the Test Connection button to help check that the connection is good and that the target table exists.

  2. Map fields from your dataflow to columns in the target table. You can click the green Auto-fill fields icon to automatically populate the list of fields and column names. When mapping fields to table columns, take into consideration the mapping of data types. Make sure to check which of the fields will be used as keys in the table. If you map mismatched data types, the job will fail. The following table displays matching the matching data types:
Xplenty 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

Xplenty 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), Xplenty defines them as the primary key in a new table. The data types in Xplenty are automatically mapped as defined in the table above. Note that since Xplenty doesn't have a notion of maximum string length, the string columns are created with the maximum length allowed.

Feedback and Knowledge Base