Skip to content

Commit bd05ee1

Browse files
calmitchell617calmitchell617
committed
added option to create schema
1 parent 7501bbc commit bd05ee1

2 files changed

Lines changed: 75 additions & 70 deletions

File tree

‎README.MD‎

Lines changed: 5 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -107,17 +107,17 @@ Before we get into the details, here are a few example curl commands to create S
107107
108108
-- mssql to snowflake full table transfer. drop and recreate target table in snowflake.
109109
-- note on mssql sources: to supply additional arguments to a target connection string, you must put the in the target-hostname field
110-
curl -d '{"source-name": "<any name you want>", "source-type": "mssql", "source-connection-string": "Server=<hostname>,<port>;Database=<db name>;User Id=<username>;Password=<password>;", "source-schema": "<schema name... such as dbo>", "source-table": "<table name>", "target-name": "<any name you want>", "target-type": "snowflake", "target-connection-string": "<snowflake username>:<snowflake password>@<account identifier>.snowflakecomputing.com/<db name>", "target-schema": "<schema name... such as public>", "target-table": "<table name>", "drop-target-table-if-exists": true, "create-target-table-if-not-exists": true, "target-database": "<db name>"}' localhost:9000/transfers/create
110+
curl -d '{"source-name": "<any name you want>", "source-type": "mssql", "source-connection-string": "Server=<hostname>,<port>;Database=<db name>;User Id=<username>;Password=<password>;", "source-schema": "<schema name... such as dbo>", "source-table": "<table name>", "target-name": "<any name you want>", "target-type": "snowflake", "target-connection-string": "<snowflake username>:<snowflake password>@<account identifier>.snowflakecomputing.com/<db name>", "target-schema": "<schema name... such as public>", "target-table": "<table name>", "drop-target-table-if-exists": true, "create-target-table-if-not-exists": true, "create-target-schema-if-not-exists": true, "target-database": "<db name>"}' localhost:9000/transfers/create
111111
112112
-- oracle to postgresql full table transfer. drop and recreate.
113-
curl -d '{"source-type": "oracle", "source-connection-string": "oracle://<username>:<password>@<hostname>:<port>/<db_name>", "target-type": "postgresql", "target-connection-string": "postgresql://<username>:<password>@<hostname>:<port>/<db_name>", "source-schema": "<schema name>, "source-table": "<table to move>, "drop-target-table-if-exists": true, "create-target-table-if-not-exists": true, "target-schema": "<schema_name>", "target-table": "<new_table_name>"}' localhost:9000/transfers/create
113+
curl -d '{"source-type": "oracle", "source-connection-string": "oracle://<username>:<password>@<hostname>:<port>/<db_name>", "target-type": "postgresql", "target-connection-string": "postgresql://<username>:<password>@<hostname>:<port>/<db_name>", "source-schema": "<schema name>, "source-table": "<table to move>, "drop-target-table-if-exists": true, "create-target-schema-if-not-exists": true, "create-target-table-if-not-exists": true, "target-schema": "<schema_name>", "target-table": "<new_table_name>"}' localhost:9000/transfers/create
114114
115115
*********************
116116
** QUERY TRANSFERS **
117117
*********************
118118
119119
-- postgresql to mssql query transfer
120-
curl -d '{"source-name": "<any name you want>", "source-type": "postgresql", "source-connection-string": "postgresql://<username>:<password>@<hostname>:<port>/<db name>", "query": "<query to move>", "target-name": "<any name you want>", "target-type": "mssql", "target-connection-string": "Server=<hostname>,<port>;Database=<db name>;User Id=<username>;Password=<password>;", "target-schema": "<schema name... such as dbo>", "target-table": "<table name>", "drop-target-table-if-exists": true, "create-target-table-if-not-exists": true, "target-hostname": "<hostname>;<additional flags, such as TrustServerCertificate=yes>", "target-database": "<db name>", "target-username": "<username>", "target-password": "<password>"}' localhost:9000/transfers/create
120+
curl -d '{"source-name": "<any name you want>", "source-type": "postgresql", "source-connection-string": "postgresql://<username>:<password>@<hostname>:<port>/<db name>", "query": "<query to move>", "target-name": "<any name you want>", "target-type": "mssql", "target-connection-string": "Server=<hostname>,<port>;Database=<db name>;User Id=<username>;Password=<password>;", "target-schema": "<schema name... such as dbo>", "target-table": "<table name>", "drop-target-table-if-exists": true, "create-target-schema-if-not-exists": true, "create-target-table-if-not-exists": true, "target-hostname": "<hostname>;<additional flags, such as TrustServerCertificate=yes>", "target-database": "<db name>", "target-username": "<username>", "target-password": "<password>"}' localhost:9000/transfers/create
121121
122122
-- mysql to oracle query transfer. do not drop target table, and do not attempt to create it.
123123
-- note on mysql sources: you must supply a "parseTime" and url-encoded "loc" query parameter in the source connection string
@@ -178,6 +178,7 @@ source-name
178178
target-name
179179
drop-target-table-if-exists
180180
create-target-table-if-not-exists
181+
create-target-schema-if-not-exists
181182
delimiter
182183
newline
183184
null
@@ -208,6 +209,7 @@ keep-files
208209
- `source-table`: The table that SQLpipe will move the data to.
209210
- `drop-target-table-if-exists`: Drops the target table (if it exists) before moving the data.
210211
- `create-target-table-if-not-exists`: Creates the target table before moving the data. SQLpipe intelligently maps from one DB's types to another.
212+
- `create-target-schema-if-not-exists`: Creates the target schema before moving the data. Only works in databases where schemas are supported. You will need sysdba privs to do this in Oracle.
211213
- `delimiter`: Some DB clients do not support [RFC 4180 CSVs](https://datatracker.ietf.org/doc/html/rfc4180) (shame on them!). This optional flag lets you set a custom multi-character delimiter - you should pick one that will not appear on your data. The default is `{dlm}`.
212214
- `newline`: Some DB clients do not support [RFC 4180 CSVs](https://datatracker.ietf.org/doc/html/rfc4180). This optional flag lets you set a custom multi-character newline. The default is `{nwln}`.
213215
- `null`: Some DB clients do not support [RFC 4180 CSVs](https://datatracker.ietf.org/doc/html/rfc4180). This optional flag lets you set a custom multi-character null value. The default is `{nll}`

‎cmd/sqlpipe/transfers.go‎

Lines changed: 70 additions & 67 deletions
Original file line numberDiff line numberDiff line change
@@ -26,29 +26,30 @@ type ConnectionInfo struct {
2626
}
2727

2828
type Transfer struct {
29-
Id string `json:"id"`
30-
CreatedAt time.Time `json:"created-at"`
31-
StoppedAt string `json:"stopped-at,omitempty"`
32-
Status string `json:"status"`
33-
Error string `json:"error,omitempty"`
34-
KeepFiles bool `json:"keep-files"`
35-
TmpDir string `json:"tmp-dir"`
36-
PipeFileDir string `json:"pipe-file-dir"`
37-
FinalCsvDir string `json:"final-csv-dir"`
38-
Context context.Context `json:"-"`
39-
Cancel context.CancelFunc `json:"-"`
40-
SourceConnectionInfo ConnectionInfo `json:"source-connection-info"`
41-
TargetConnectionInfo ConnectionInfo `json:"target-connection-info"`
42-
DropTargetTableIfExists bool `json:"drop-target-table-if-exists"`
43-
CreateTargetTableIfNotExists bool `json:"create-target-table-if-not-exists"`
44-
SourceSchema string `json:"source-schema,omitempty"`
45-
SourceTable string `json:"source-table,omitempty"`
46-
TargetSchema string `json:"target-schema,omitempty"`
47-
TargetTable string `json:"target-name"`
48-
Query string `json:"query,omitempty"`
49-
Delimiter string `json:"delimiter"`
50-
Newline string `json:"newline"`
51-
Null string `json:"null"`
29+
Id string `json:"id"`
30+
CreatedAt time.Time `json:"created-at"`
31+
StoppedAt string `json:"stopped-at,omitempty"`
32+
Status string `json:"status"`
33+
Error string `json:"error,omitempty"`
34+
KeepFiles bool `json:"keep-files"`
35+
TmpDir string `json:"tmp-dir"`
36+
PipeFileDir string `json:"pipe-file-dir"`
37+
FinalCsvDir string `json:"final-csv-dir"`
38+
Context context.Context `json:"-"`
39+
Cancel context.CancelFunc `json:"-"`
40+
SourceConnectionInfo ConnectionInfo `json:"source-connection-info"`
41+
TargetConnectionInfo ConnectionInfo `json:"target-connection-info"`
42+
DropTargetTableIfExists bool `json:"drop-target-table-if-exists"`
43+
CreateTargetSchemaIfNotExists bool `json:"create-target-schema-if-not-exists"`
44+
CreateTargetTableIfNotExists bool `json:"create-target-table-if-not-exists"`
45+
SourceSchema string `json:"source-schema,omitempty"`
46+
SourceTable string `json:"source-table,omitempty"`
47+
TargetSchema string `json:"target-schema,omitempty"`
48+
TargetTable string `json:"target-name"`
49+
Query string `json:"query,omitempty"`
50+
Delimiter string `json:"delimiter"`
51+
Newline string `json:"newline"`
52+
Null string `json:"null"`
5253
}
5354

5455
var transferMap = NewSafeTransferMap()
@@ -107,28 +108,29 @@ func (sm *SafeTransferMap) Delete(key string) {
107108

108109
func createTransferHandler(w http.ResponseWriter, r *http.Request) {
109110
var input struct {
110-
KeepFiles bool `json:"keep-files"`
111-
SourceName string `json:"source-name"`
112-
SourceType string `json:"source-type"`
113-
SourceConnectionString string `json:"source-connection-string"`
114-
TargetName string `json:"target-name"`
115-
TargetType string `json:"target-type"`
116-
TargetConnectionString string `json:"target-connection-string"`
117-
TargetHostname string `json:"target-hostname"`
118-
TargetPort int `json:"target-port"`
119-
TargetDatabase string `json:"target-database"`
120-
TargetUsername string `json:"target-username"`
121-
TargetPassword string `json:"target-password"`
122-
DropTargetTableIfExists bool `json:"drop-target-table-if-exists"`
123-
CreateTargetTableIfNotExists bool `json:"create-target-table-if-not-exists"`
124-
SourceSchema string `json:"source-schema"`
125-
SourceTable string `json:"source-table"`
126-
TargetSchema string `json:"target-schema"`
127-
TargetTable string `json:"target-table"`
128-
Query string `json:"query"`
129-
Delimiter string `json:"delimiter"`
130-
Newline string `json:"newline"`
131-
Null string `json:"null"`
111+
KeepFiles bool `json:"keep-files"`
112+
SourceName string `json:"source-name"`
113+
SourceType string `json:"source-type"`
114+
SourceConnectionString string `json:"source-connection-string"`
115+
TargetName string `json:"target-name"`
116+
TargetType string `json:"target-type"`
117+
TargetConnectionString string `json:"target-connection-string"`
118+
TargetHostname string `json:"target-hostname"`
119+
TargetPort int `json:"target-port"`
120+
TargetDatabase string `json:"target-database"`
121+
TargetUsername string `json:"target-username"`
122+
TargetPassword string `json:"target-password"`
123+
DropTargetTableIfExists bool `json:"drop-target-table-if-exists"`
124+
CreateTargetSchemaIfNotExists bool `json:"create-target-schema-if-not-exists"`
125+
CreateTargetTableIfNotExists bool `json:"create-target-table-if-not-exists"`
126+
SourceSchema string `json:"source-schema"`
127+
SourceTable string `json:"source-table"`
128+
TargetSchema string `json:"target-schema"`
129+
TargetTable string `json:"target-table"`
130+
Query string `json:"query"`
131+
Delimiter string `json:"delimiter"`
132+
Newline string `json:"newline"`
133+
Null string `json:"null"`
132134
}
133135

134136
err := readJSON(w, r, &input)
@@ -178,27 +180,28 @@ func createTransferHandler(w http.ResponseWriter, r *http.Request) {
178180
ctx, cancel := context.WithCancel(context.Background())
179181

180182
transfer := Transfer{
181-
Id: id,
182-
CreatedAt: time.Now(),
183-
Status: StatusQueued,
184-
KeepFiles: input.KeepFiles,
185-
TmpDir: tmpDir,
186-
PipeFileDir: pipeFileDir,
187-
FinalCsvDir: finalCsvDir,
188-
Delimiter: input.Delimiter,
189-
Newline: input.Newline,
190-
Null: input.Null,
191-
Context: ctx,
192-
Cancel: cancel,
193-
SourceConnectionInfo: sourceConnectionInfo,
194-
TargetConnectionInfo: targetConnectionInfo,
195-
DropTargetTableIfExists: input.DropTargetTableIfExists,
196-
CreateTargetTableIfNotExists: input.CreateTargetTableIfNotExists,
197-
SourceSchema: input.SourceSchema,
198-
SourceTable: input.SourceTable,
199-
TargetSchema: input.TargetSchema,
200-
TargetTable: input.TargetTable,
201-
Query: input.Query,
183+
Id: id,
184+
CreatedAt: time.Now(),
185+
Status: StatusQueued,
186+
KeepFiles: input.KeepFiles,
187+
TmpDir: tmpDir,
188+
PipeFileDir: pipeFileDir,
189+
FinalCsvDir: finalCsvDir,
190+
Delimiter: input.Delimiter,
191+
Newline: input.Newline,
192+
Null: input.Null,
193+
Context: ctx,
194+
Cancel: cancel,
195+
SourceConnectionInfo: sourceConnectionInfo,
196+
TargetConnectionInfo: targetConnectionInfo,
197+
DropTargetTableIfExists: input.DropTargetTableIfExists,
198+
CreateTargetSchemaIfNotExists: input.CreateTargetSchemaIfNotExists,
199+
CreateTargetTableIfNotExists: input.CreateTargetTableIfNotExists,
200+
SourceSchema: input.SourceSchema,
201+
SourceTable: input.SourceTable,
202+
TargetSchema: input.TargetSchema,
203+
TargetTable: input.TargetTable,
204+
Query: input.Query,
202205
}
203206

204207
v := newValidator()
@@ -405,7 +408,7 @@ func runTransfer(transfer Transfer) (err error) {
405408
}
406409
defer target.closeConnectionPool(true)
407410

408-
if target.schemaRequired() {
411+
if target.schemaRequired() && transfer.CreateTargetSchemaIfNotExists {
409412
err = createSchemaIfNotExists(transfer.TargetSchema, target)
410413
if err != nil {
411414
return fmt.Errorf("error creating target schema :: %v", err)

0 commit comments

Comments
 (0)