Need help with etlalchemy?
Click the “chat” button below for chat support from the developer who created it, or find similar developers for support.

About the developer

seanharr11
441 Stars 52 Forks MIT License 124 Commits 33 Opened issues

Description

Extract, Transform, Load: Any SQL Database in 4 lines of Code.

Services available

!
?

Need anything else?

Contributors list

No Data

etlalchemy

Extract, Transform and Load...Migrate any SQL Database in 4 Lines of Code. Read more here...

Donate Donate

Installation

pip install etlalchemy
# On El Capitan:
### pip install --ignore-installed etlalchemy

Also install the necessary DBAPI modules and SQLAlchemy dialects

For example, for MySQL, you might use:

pip install pymsql

Basic Usage

from etlalchemy import ETLAlchemySource, ETLAlchemyTarget

source = ETLAlchemySource("mssql+pyodbc://username:[email protected]_NAME") target = ETLAlchemyTarget("mysql://username:[email protected]/db_name", drop_database=True) target.addSource(source) target.migrate()

Examples

Provide a list of tables to include/exclude in migration ```python from etlalchemy import ETLAlchemySource, ETLAlchemyTarget

Load ONLY the 'salaries' table

source = ETLAlchemySource("mysql://etlalchemy:[email protected]/employees", included_tables=["salaries"])

Conversely, you could load ALL tables EXCEPT 'salaries'

source = ETLAlchemySource("mysql://etlalchemy:[email protected]/employees",\

excluded_tables=["salaries"])

target = ETLAlchemyTarget("postgresql://etlalchemy:[email protected]/test", drop_database=True) target.addSource(source) target.migrate()

**Only migrate schema, or only Data, or only FKs, or only Indexes (or any combination of the 4!)**
python from etlalchemy import ETLAlchemySource, ETLAlchemyTarget

source = ETLAlchemySource("mysql://etlalchemy:[email protected]/employees")

target = ETLAlchemyTarget("postgresql://etlalchemy:[email protected]/test", drop_database=True) target.addSource(source)

Note that each phase (schema, data, index, fk) is independent of all others,

and can be run standalone, or in any combination. (Obviously you need a schema to send data, etc...)

target.migrate(migratefks=False, migrateindexes=False, migratedata=False, migrateschema=True)

**Skip columns and tables if they are empty**
python from etlalchemy import ETLAlchemySource, ETLAlchemyTarget

This will skip tables with no rows (or all empty rows), and ignore them during schema migration

This will skip columns if they have all NULL values, and ignore them during schema migration

source = ETLAlchemySource("mysql://etlalchemy:[email protected]/employees",\ skipcolumnifempty=True,\ skiptableifempty=True) target = ETLAlchemyTarget("postgresql://etlalchemy:[email protected]/test", drop_database=True) target.addSource(source) target.migrate()

**Enable 'upserting' of data**
python from etlalchemy import ETLAlchemySource, ETLAlchemyTarget

source = ETLAlchemySource("mysql://etlalchemy:[email protected]/employees")

This will leave the target DB as is, and if the tables being migrated from Source -> Target

already exist on the Target, then rows will be updated based on PKs if they exist, or

inserted if they DNE on the Target table.

target = ETLAlchemyTarget("postgresql://etlalchemy:[email protected]/test", drop_database=False) target.addSource(source) target.migrate()

**Alter schema (change column names, column types, table names, and Drop tables/columns)**
python from etlalchemy import ETLAlchemySource, ETLAlchemyTarget

See below for the simple structure of the .csv's for schema changes

source = ETLAlchemySource("mysql://etlalchemy:[email protected]/employees",\ columnschematransformationfile=os.getcwd() + "/transformations/columnmappings.csv",\ tableschematransformationfile=os.getcwd() + "/transformations/tablemappings.csv") target = ETLAlchemyTarget("postgresql://SeanH:[email protected]/test", dropdatabase=True) target.addSource(source) target.migrate() ``` | *columnmappings.csv* | table_mappings.csv | | :--- | :--- | |Column Name,Table Name,New Column Name,New Column Type,Delete|Table Name,New Table Name,Delete| |lastname,employees,,,True|tabletorename,newtablename,False| |fired,employees,,Boolean,False|tabletodelete,,True| |birthdate,employees,dob,,False|departments,dept,False|

Rename any column which ends in a given 'suffix' (or skip the column during migration) ```python from etlalchemy import ETLAlchemySource, ETLAlchemyTarget

globalrenamedcol_suffixes is useful to standardize column names across tables (like the date example below)

source = ETLAlchemySource("mysql://etlalchemy:[email protected]/employees",\ globalignoredcolsuffixes=['dropallcolumnsthatendinthis'],\ globalrenamedcolsuffixes={'date': 'dt'},\ #i.e. "createddate -> createddt" ) target = ETLAlchemyTarget("postgresql://SeanH:[email protected]/test", drop_database=True) target.addSource(source) target.migrate() ```

Known Limitations

  1. 'sqlalchemymigrate' does not support MSSQL FK migrations. *(So, FK migrations will be skipped when Target is MSSQL)_
  2. Currently not compatible with Windows
  3. If Target DB is in the Azure Cloud (MSSQL), FreeTDS has some compatibility issues which are performance related. This may be noticed when migrating tables with 1,000,000+ rows into a Azure MSSQL Server.
  4. Though the MSSQL 'BULK INSERT' feature is supported in this tool, it is NOT supported on either Azure environments, or AWS MSSQL Server environments (no 'bulkadmin' role allowed). Feel free to test this out on a different MSSQL environment!
  5. Regression tests have not (yet) been created due to the unique (and expensive) way one must test all of the different database types.
  6. Migrations to MSSQL and Oracle are extremely slow due to the lack of 'fast' import capabilities.
    • 'SQL Loader' can be used on Oracle, and the 'BULK INSERT' operation can be used on MSSQL, however the former is a PITA to install, and the latter is not supported in several MSSQL environments (see 'Known Limitations' below).
    • 'BULK INSERT' is supported in etlalchemy (with limited testing), but "SQL LOADER" is not (yet).
  7. When sending data to PostgreSQL, if the data contains VARCHAR() or TEXT() columns with carriage returns ('^M' or '\r'), these will be stripped.
    • This is due to the lack of the "ENCLOSED BY" option of psycopg.copy_from() - these chars are interpreted as literals, and in turn tell the COPY FROM operation that "the row ends here"

Assumptions Made

  1. Default date formats for all Target DB's are assumed to be the 'out-of-the-box' defaults.
  2. Text fields to not contain the character "|", or the string "|,".
    • On some Target DBs, if you have text fields containing "|," (mssql) or "|" (sqlite), then the 'fast' import may fail, or insert bizarre values into your DB. This is due to the 'delimiter' which separates column values in the file that is sent to the Target DB.

On Testing

  1. The 'Performance' matrix has been put together using a simple script which tests every combination of Source (5) and Target (5) DB migration (25 total combinations).
    • The script is not included (publicly), as it contains the connection strings of AWS RDS instances.
  2. A regression test suite is needed, as is funding to setup an environment for Oracle and MSSQL instances..
  3. There are definitely some untested column types here amongst all 5 RDBMS's. Please create pull requests or open issues that describe the problem in detail as these arise!

Contributors

We are always looking for contributors!

This project has its origins in solving the problem of migrating off of bulky, expensive enterprise-level databases. If the project has helped you to migrate off of these databases, and onto open-source RDBMS's, the best way to show your support is by opening Pull Requests and Issues.

Donations

Donations through Gratipay are welcome, but Pull Requests are better!

You can also support us via PayPal here.

Other

For help installing cxOracle on a Mac (El Capitan + cxOracle = Misery), check out this blog post for help.

Run this tool from the same server that hosts your Target database to get maximum performance out of it.

We use cookies. If you continue to browse the site, you agree to the use of cookies. For more information on our use of cookies please see our Privacy Policy.