Database to Database
Examples of using Sling to load data from one database to another
We first need to make sure our connections are available in our environment. See Environment and Database Connections for more details.
export MY_SOURCE_DB='...'
export MY_TARGET_DB='...'
$ sling conns list
+---------------+------------------+-----------------+
| CONN NAME | CONN TYPE | SOURCE |
+---------------+------------------+-----------------+
| MY_SOURCE_DB | DB - PostgreSQL | env variable |
| MY_TARGET_DB | DB - Snowflake | env variable |
+---------------+------------------+-----------------+
# using windows Powershell
$env:MY_SOURCE_DB = '...'
$env:MY_TARGET_DB = '...'
$ sling conns list
+---------------+------------------+-----------------+
| CONN NAME | CONN TYPE | SOURCE |
+---------------+------------------+-----------------+
| MY_SOURCE_DB | DB - PostgreSQL | env variable |
| MY_TARGET_DB | DB - Snowflake | env variable |
+---------------+------------------+-----------------+
Database ⇨ Database (Full Refresh)
Using CLI Flags
$ sling run --src-conn MY_SOURCE_DB \
--src-stream 'source_schema.source_table' \
--tgt-conn MY_TARGET_DB \
--tgt-object 'target_schema.target_table' \
--mode full-refresh
Using Replication
Running with Sling: sling run -r /path/to/replication.yaml
source: MY_SOURCE_DB
target: MY_TARGET_DB
defaults:
object: '{target_schema}.{stream_schema}_{stream_table}'
mode: full-refresh
streams:
source_schema.source_table:
object: target_schema.target_table # override default object pattern
source_schema.another_table:
# all tables in schema, except "forbidden_table"
my_schema.*:
my_schema.forbidden_table:
disabled: true
env:
SLING_THREADS: 3 # run streams concurrently
Database ⇨ Database (Custom SQL)
See also Custom SQL Examples.
Using CLI Flags
$ sling run --src-conn MY_SOURCE_DB \
--src-stream 'select * from my_schema.my_table where col1 is not null' \
--tgt-conn MY_TARGET_DB \
--tgt-object 'target_schema.target_table' \
--mode full-refresh
# we can also read from a SQL file (/path/to/query.sql)
$ sling run --src-conn MY_SOURCE_DB \
--src-stream file:///path/to/query.sql \
--tgt-conn MY_TARGET_DB \
--tgt-object 'target_schema.target_table' \
--mode full-refresh
Using Replication
Running with Sling: sling run -r /path/to/replication.yaml
source: MY_SOURCE_DB
target: MY_TARGET_DB
defaults:
mode: full-refresh
streams:
source_schema.source_table.1:
sql: |
select *
from my_schema.my_table
where col1 is not null
object: target_schema.target_table
source_schema.source_table.2:
sql: file:///path/to/query.sql
object: target_schema.target_table
Database ⇨ Database (Incremental / Backfill)
See also Incremental Examples.
Using CLI Flags
# limit to 10M records at a time. Will be sorted by update_key ASC.
# just loop command until all data is transferred / caught up.
$ sling run --src-conn MY_SOURCE_DB \
--src-stream 'source_schema.source_table' \
--tgt-conn MY_TARGET_DB \
--tgt-object 'target_schema.target_table' \
--primary-key 'id' \
--update-key 'last_modified_dt' \
--mode incremental \
--limit 1000000 -d
# Backfill specific date range
$ sling run --src-conn MY_SOURCE_DB \
--src-stream 'source_schema.source_table' \
--tgt-conn MY_TARGET_DB \
--tgt-object 'target_schema.target_table' \
--primary-key 'id' \
--update-key 'last_modified_dt' \
--mode backfill \
--range '2021-01-01,2021-02-01'
Using Replication
Running with Sling: sling run -r /path/to/replication.yaml
source: MY_SOURCE_DB
target: MY_TARGET_DB
defaults:
mode: incremental
object: '{target_schema}.{stream_schema}_{stream_table}'
primary_key: [id]
update_key: last_modified_dt
source_options:
limit: 10000000 # limit to 10M records at a time
streams:
source_schema.source_table:
object: target_schema.target_table # override default object pattern
update_key: updated_at # override default update key
# backfill
source_schema.backfill_table:
mode: backfill
object: target_schema.backfill_table
primary_key: [some_id]
update_key: updated_at # override default update key
source_options:
range: 2021-01-01,2021-02-01 # specific date range
chunk_size: 7d # 7-day stream chunking/splitting
source_schema.another_table:
target_options:
delete_missing: soft # track deletes from source table
Database ⇨ Database (Incremental - New Data Upsert)
See also Incremental Examples.
Using CLI Flags
$ sling run --src-conn MY_SOURCE_DB \
--src-stream 'source_schema.source_table' \
--tgt-conn MY_TARGET_DB \
--tgt-object 'target_schema.target_table' \
--mode incremental \
--primary-key 'id' \
--update-key 'last_modified_dt'
Using Replication
Running with Sling: sling run -r /path/to/replication.yaml
source: MY_SOURCE_DB
target: MY_TARGET_DB
defaults:
mode: incremental
object: '{target_schema}.{stream_schema}_{stream_table}'
primary_key: [id]
update_key: last_modified_dt
streams:
source_schema.source_table:
object: target_schema.target_table # override default object pattern
update_key: updated_at # override default update key
source_schema.another_table:
source_schema.some_table:
target_options:
delete_missing: soft # track deletes from source table
# all tables in schema, except "forbidden_table"
my_schema.*:
my_schema.forbidden_table:
disabled: true
env:
SLING_THREADS: 3 # run streams concurrently
Database ⇨ Database (Incremental - Full Data Upsert)
See also Incremental Examples.
Using CLI Flags
$ sling run --src-conn MY_SOURCE_DB \
--src-stream 'source_schema.source_table' \
--tgt-conn MY_TARGET_DB \
--tgt-object 'target_schema.target_table' \
--mode incremental \
--primary-key 'id'
Using Replication
Running with Sling: sling run -r /path/to/replication.yaml
source: MY_SOURCE_DB
target: MY_TARGET_DB
defaults:
object: '{target_schema}.{stream_schema}_{stream_table}'
mode: incremental
primary_key: [id]
streams:
source_schema.source_table:
object: target_schema.target_table # override default object pattern
primary_key: [ col1, col2 ] # override default primary_key
source_schema.another_table:
source_schema.some_table:
target_options:
delete_missing: soft # track deletes from source table
# all tables in schema, except "forbidden_table"
my_schema.*:
my_schema.forbidden_table:
disabled: true
env:
SLING_THREADS: 3 # run streams concurrently
Database ⇨ Database (Incremental - Append Only)
See also Incremental Examples.
Using CLI Flags
$ sling run --src-conn MY_SOURCE_DB \
--src-stream 'source_schema.source_table' \
--tgt-conn MY_TARGET_DB \
--tgt-object 'target_schema.target_table' \
--mode incremental \
--update-key 'created_dt'
Using Replication
Running with Sling: sling run -r /path/to/replication.yaml
source: MY_SOURCE_DB
target: MY_TARGET_DB
defaults:
mode: incremental
object: '{target_schema}.{stream_schema}_{stream_table}'
update_key: created_dt
streams:
source_schema.source_table:
object: target_schema.target_table # override default object pattern
update_key: created_at # override default update_key
source_schema.another_table:
# all tables in schema, except "forbidden_table"
my_schema.*:
my_schema.forbidden_table:
disabled: true
env:
SLING_THREADS: 3 # run streams concurrently
Database ⇨ Database (Truncate)
Using CLI Flags
$ sling run --src-conn MY_SOURCE_DB \
--src-stream 'source_schema.source_table' \
--tgt-conn MY_TARGET_DB \
--tgt-object 'target_schema.target_table' \
--mode truncate
Using Replication
Running with Sling: sling run -r /path/to/replication.yaml
source: MY_SOURCE_DB
target: MY_TARGET_DB
defaults:
mode: truncate
object: '{target_schema}.{stream_schema}_{stream_table}'
streams:
source_schema.source_table:
object: target_schema.target_table # override default object pattern
source_schema.another_table:
# all tables in schema, except "forbidden_table"
my_schema.*:
my_schema.forbidden_table:
disabled: true
env:
SLING_THREADS: 3 # run streams concurrently
Database ⇨ Database (Snapshot)
Using CLI Flags
$ sling run --src-conn MY_SOURCE_DB \
--src-stream 'source_schema.source_table' \
--tgt-conn MY_TARGET_DB \
--tgt-object 'target_schema.target_table' \
--mode snapshot
Using Replication
Running with Sling: sling run -r /path/to/replication.yaml
source: MY_SOURCE_DB
target: MY_TARGET_DB
defaults:
mode: snapshot
object: '{target_schema}.{stream_schema}_{stream_table}'
streams:
source_schema.source_table:
object: target_schema.target_table # override default object pattern
source_schema.another_table:
# all tables in schema, except "forbidden_table"
my_schema.*:
my_schema.forbidden_table:
disabled: true
env:
SLING_THREADS: 3 # run streams concurrently
Last updated