webv2

README

Spanner migration tool: Schema Assistant

Spanner migration tool is a stand-alone open source tool for Cloud Spanner evaluation and migration. This README provides details of the Spanner migration tool schema assistant, which supports schema customization. For general Spanner migration tool information see this README.

Starting web server for Spanner migration tool

The following examples assume spanner-migration-tool alias has been setup as following.

git clone https://github.com/GoogleCloudPlatform/spanner-migration-tool
cd spanner-migration-tool
alias spanner-migration-tool="go run github.com/GoogleCloudPlatform/spanner-migration-tool"

Spanner migration tool's Web API feature can be used with all the driver modes available, using mysql or postgres dump or direct connection.

To generate the Spanner migration tool binary, run:

make build

To start Spanner migration tool web server, run:

./spanner-migration-tool web

The UI will launched at http://localhost:8080/.

For more details on how to use the UI, click on the help button on the top right corner of the page.

Note:

The pg_dump and mysqldump drivers cannot be used for data migration if the Spanner schema has interleaved tables. Note that the postgres or mysql drivers do not have this restriction -- consider using these as an alternative.

APIs

These are the REST APIs and their details:

Connect

/connect is a POST API used to configure direct connection to a database. The request body contains connection details and the driver name.

Method

POST

Request body

Connection information along with driver name.

Example

{
  "Driver": "postgres",
  "Host": "localhost",
  "Port": "5432",
  "Database": "dbname",
  "User": "user",
  "Password": "password"
}

Response body

No response body is returned.

Convert

(1) /convert/infoschema is a GET API followed by /connect API to convert using infoschema mode. It returns the schema conversion state in json format.

Method

GET

Request body

No request body is needed.

Response body

Conv struct in JSON format.

(2) /convert/dump is a POST API used to perform schema conversion on a dump file. The request body contains the path to the dump file.

Method

POST

Request body

Provide driver and path to dump file.

Example

{
  "Driver": "postgres",
  "Path": "/path/to/dumpFile"
}

Response body

Conv struct in JSON format.

DDL

/ddl is a GET API which must be used after using conversion APIs (i.e, /connect or /convert). This API returns the DDL statements of the converted schema.

Method

GET

Request body

No request body is needed.

Response body

Table wise DDL statements.

Example

{
  "Albums": "--\n-- Spanner schema for source table Albums\n--\nCREATE TABLE
Albums (\n    SingerId INT64 NOT NULL, -- From: SingerId smallint(6)\n
AlbumId INT64 NOT NULL, -- From: AlbumId smallint(6)\n    AlbumTitle STRING(50)
-- From: AlbumTitle varchar(50)\n) PRIMARY KEY (SingerId, AlbumId)"
}

Session

(1) /session is a GET API which returns the schema conversion state in json format. It also create a file with suffix .session.json in the frontend/ folder.

Method

GET

Request body

No request body is needed.

Response body

Session file info with driver name.

Example

{
  "Driver": "postgres",
  "FilePath": "/path/to/sessionFile",
  "FileName": "sessionFile.session.json",
  "CreatedAt": "Thu, 04 Feb 2021 18:10:32 IST"
}

(2) /session/resume is a POST API which can be used to resume a previous session. The request body contains the path to the previous session. Note that sessions are created by the /session API.

Method

POST

Request body

Provide driver and session file path.

Example

{
  "driver": "postgres",
  "path": "/path/to/sessionFile",
  "fileName": "sessionFile.session.json"
}

Response body

No response body is returned.

Summary

/summary is a GET API which returns a table-by-table report of the conversion.

Method

GET

Request body

No request body is needed.

Response body

Table wise summary of conversion.

Example

{
  "Albums": "Note\n1) Some columns will consume more storage in Spanner
e.g. for column 'AlbumId', source DB type smallint(6) is mapped to Spanner
type int64.\n\n"
}

Overview

/overview is a GET API which returns the overview of the conversion.

Method

GET

Request body

No request body is needed.

Response body

Overall summary of conversion in string format.

Conversion

/conversion is a GET API which returns table wise rate of conversion which is encoded in color values.

Method

GET

Request body

No request body is needed.

Response body

Table wise rate of conversion encoded in color values.

Example

{
  "Albums": "GREEN",
  "Singers": "BLUE",
  "Userinfo": "ORANGE"
}

Typemap

(1) /typemap is a GET API which returns a map that, for each source type, provides the potential Spanner types that can be used for that source type. This map can be used for customization of type mapping in the UI.

Method

GET

Request body

No request body is needed.

Response body

Map from each source type to the list of possible Spanner types and issues.

Example

{
  "smallint": [
    {
      "T": "INT64",
      "Brief": "Some columns will consume more storage in Spanner"
    },
    {
      "T": "STRING",
      "Brief": "Some columns will consume more storage in Spanner"
    }
  ],
  "varchar": [
    {
      "T": "BYTES",
      "Brief": ""
    },
    {
      "T": "STRING",
      "Brief": ""
    }
  ]
}

(2) /typemap/global is a POST API to customize schema conversion on global level. The request body contains a map from source type to Spanner type in json format.

Method

POST

Request body

Map from source type to Spanner type.

Example

{
  "smallint": "STRING",
  "varchar": "BYTES"
}

Response body

Updated Conv struct in JSON format.

(3) /typemap/table?table=<table_name> is a POST API which performs following operations on a single table.

• Remove column
• Rename column
• Remove or Add primary key
• Update type of column
• Remove or Add NOT NULL constraint

Method

POST

Request body

Column wise actions to be performed with following possible values for each field.

• Removed : true/false
• Rename : New name or empty string
• PK : "" | "ADDED" | "REMOVED"
• NotNull : "" | "ADDED" | "REMOVED"
• ToType : New Spanner type or empty string

Example

{
  "UpdateCols": {
    "AlbumTitle": {
      "Removed": false,
      "Rename": "AlbumName",
      "PK": "",
      "NotNull": "ADDED",
      "ToType": "BYTES"
    }
  }
}

Response body

Updated Conv struct in JSON format.

Report file

/report is a GET API which generates report file and returns file path.

Method

GET

Request body

No request body is needed.

Response body

Path to report file.

Example

/path/to/reportfile.report.txt

Schema file

/schema is a GET API which generates schema file and returns file path.

Method

GET

Request body

No request body is needed.

Response body

Path to schema file.

Example

/path/to/schemafile.schema.txt

Interleave tables

/setparent?table=<table_name> is a GET API which checks whether it is possible to convert a table into a Spanner interleaved table. If this conversion is possible, then the schema is changed and the parent table name is returned. If the conversion is not possible, a failure message is returned.

Method

GET

Request body

No request body is needed.

Response body

If it is possible to make given table interleaved, it will return with table name of parent.

Example

{
  "Possible": true,
  "Parent": "Singers",
  "Comment": ""
}

If it is not possible to interleave given table, it will return with the reason of why it failed.

Example

{
  "Possible": false,
  "Parent": "",
  "Comment": "No valid prefix"
}

Drop foreign key

/drop/fk?table=<table_name>&pos=<position> is a GET API which takes table name and array position of foreign key in query params. It drops foreign key at given position for given table name.

Method

GET

Request body

No request body is needed.

Response body

Updated Conv struct in JSON format.

Drop secondary index

/drop/secondaryindex?table=<table_name>&pos=<position> is a GET API which takes table name and array position of secondary index in query params. It drops secondary index at given position for given table name.

Method

GET

Request body

No request body is needed.

Response body

Updated Conv struct in JSON format.

Documentation

Overview

Package web defines web APIs to be used with Spanner migration tool frontend. Apart from schema conversion, this package involves API to update converted schema.

Index

• Variables
• func App(logLevel string, open bool, port int) error
• type ColMaxLength
• type ConnectionConfig
• type DataflowCfg
• type DataflowLocation
• type DatastreamCfg
• type DropDetail
• type GeneratedResources
• type ResourceDetails
• type SessionState
• type ShardIdPrimaryKey
• type StreamingCfg
• type TableIdAndName
• type TableInterleaveStatus
• type WebCmd
  • func (cmd *WebCmd) Execute(ctx context.Context, f *flag.FlagSet, _ ...interface{}) subcommands.ExitStatus
  • func (cmd *WebCmd) Name() string
  • func (cmd *WebCmd) SetFlags(f *flag.FlagSet)
  • func (cmd *WebCmd) Synopsis() string
  • func (cmd *WebCmd) Usage() string

Variables

var FrontendDir embed.FS

Functions

func App

func App(logLevel string, open bool, port int) error

App connects to the web app v2.

Types

type ColMaxLength

type ColMaxLength struct {
	SpDataType     string `json:"spDataType"`
	SpColMaxLength string `json:"spColMaxLength"`
}

type ConnectionConfig

type ConnectionConfig struct {
	Name     string `json:"name"`
	Location string `json:"location"`
}

type DataflowCfg

type DataflowCfg struct {
	JobName             string `json:"JobName"`
	Location            string `json:"Location"`
	Network             string `json:"Network"`
	Subnetwork          string `json:"Subnetwork"`
	MaxWorkers          string `json:"MaxWorkers"`
	NumWorkers          string `json:"NumWorkers"`
	ServiceAccountEmail string `json:"ServiceAccountEmail"`
	HostProjectId       string `json:"HostProjectId"`
}

type DataflowLocation

type DataflowLocation struct {
	DataflowConfig profiles.DataflowConfig
}

type DatastreamCfg

type DatastreamCfg struct {
	StreamId               string           `json:"streamId"`
	StreamLocation         string           `json:"streamLocation"`
	StreamDisplayName      string           `json:"streamDisplayName"`
	SourceConnectionConfig ConnectionConfig `json:"sourceConnectionConfig"`
	TargetConnectionConfig ConnectionConfig `json:"destinationConnectionConfig"`
	Properties             string           `json:properties`
}

type DropDetail

type DropDetail struct {
	Name string `json:"Name"`
}

type GeneratedResources

type GeneratedResources struct {
	DatabaseName string `json:"DatabaseName"`
	DatabaseUrl  string `json:"DatabaseUrl"`
	BucketName   string `json:"BucketName"`
	BucketUrl    string `json:"BucketUrl"`
	//Used for single instance migration flow
	DataStreamJobName      string `json:"DataStreamJobName"`
	DataStreamJobUrl       string `json:"DataStreamJobUrl"`
	DataflowJobName        string `json:"DataflowJobName"`
	DataflowJobUrl         string `json:"DataflowJobUrl"`
	DataflowGcloudCmd      string `json:"DataflowGcloudCmd"`
	PubsubTopicName        string `json:"PubsubTopicName"`
	PubsubTopicUrl         string `json:"PubsubTopicUrl"`
	PubsubSubscriptionName string `json:"PubsubSubscriptionName"`
	PubsubSubscriptionUrl  string `json:"PubsubSubscriptionUrl"`
	//Used for sharded migration flow
	ShardToDatastreamMap         map[string]ResourceDetails `json:"ShardToDatastreamMap"`
	ShardToDataflowMap           map[string]ResourceDetails `json:"ShardToDataflowMap"`
	ShardToPubsubTopicMap        map[string]ResourceDetails `json:"ShardToPubsubTopicMap"`
	ShardToPubsubSubscriptionMap map[string]ResourceDetails `json:"ShardToPubsubSubscriptionMap"`
}

type ResourceDetails

type ResourceDetails struct {
	JobName   string `json:"JobName"`
	JobUrl    string `json:"JobUrl"`
	GcloudCmd string `json:"GcloudCmd"`
}

type SessionState

type SessionState struct {
	// contains filtered or unexported fields
}

SessionState stores information for the current migration session.

type ShardIdPrimaryKey

type ShardIdPrimaryKey struct {
	AddedAtTheStart bool `json:"AddedAtTheStart"`
}

type StreamingCfg

type StreamingCfg struct {
	DatastreamCfg DatastreamCfg `json:"datastreamCfg"`
	DataflowCfg   DataflowCfg   `json:"dataflowCfg"`
	TmpDir        string        `json:"tmpDir"`
}

type TableIdAndName

type TableIdAndName struct {
	Id   string `json:"Id"`
	Name string `json:"Name"`
}

type TableInterleaveStatus

type TableInterleaveStatus struct {
	Possible bool
	Parent   string
	Comment  string
}

TableInterleaveStatus stores data regarding interleave status.

type WebCmd

type WebCmd struct {
	DistDir embed.FS
	// contains filtered or unexported fields
}

func (*WebCmd) Execute

func (cmd *WebCmd) Execute(ctx context.Context, f *flag.FlagSet, _ ...interface{}) subcommands.ExitStatus

func (*WebCmd) Name

func (cmd *WebCmd) Name() string

Name returns the name of operation.

func (*WebCmd) SetFlags

func (cmd *WebCmd) SetFlags(f *flag.FlagSet)

func (*WebCmd) Synopsis

func (cmd *WebCmd) Synopsis() string

Synopsis returns summary of operation.

func (*WebCmd) Usage

func (cmd *WebCmd) Usage() string