search

seanharr11 / etlalchemy

Extract, Transform, Load: Any SQL Database in 4 lines of Code.
MIT License
556 stars 83 forks source link

Trouble testing this with Adventureworks2014 MSSQL -> Postgresql #30

Open kaylon opened 6 years ago

kaylon commented 6 years ago

Hey Sean, I am currently trying this out. My ultimate goal is to copy only one schema, not a complete db, but later for that.

versions:

etlalchemy==1.1.1
six==1.11.0
py==1.4.31
SQLAlchemy==1.1.14
sqlalchemy-migrate==0.9.7
SQLAlchemy-Utils==0.30.9

To test it I am trying to migrate adventueworks2014 via this script:

from etlalchemy import ETLAlchemySource, ETLAlchemyTarget
import pymssql
refstring = "mssql+pyodbc://etl:foo@server/AdventureWorks2014?driver=ODBC+Driver+13+for+SQL+Server"
src = ETLAlchemySource(refstring)
tgt = ETLAlchemyTarget("postgresql://etl:foo@localhost/foo",
                          drop_database=True)
tgt.addSource(src)
tgt.migrate()

For once it seems, that eltalchemy only discovers the tables in dbo, not the others.

Output (sorry, couldn't attach it as a file):

~foo git:(etlalchemy) python alchemy_foo.py
/home/foo/.local/lib/python2.7/site-packages/flask/exthook.py:71: ExtDeprecationWarning: Importing flask.ext.babel is deprecated, use flask_babel instead.
  .format(x=modname), ExtDeprecationWarning
postgresql
postgresql://etl:foo@localhost/foo
Dropping database 'foo'
Creating database 'foo'
Sending source '<etlalchemy.ETLAlchemySource.ETLAlchemySource instance at 0x7f9d0f9c6170>' to destination 'postgresql://etl:foo@localhost/foo'
ETLAlchemySource (INFO) -
        *************************
        *** Total Tables: 6 ***
        *************************

ETLAlchemySource (INFO) - Reading Table Schema 'AWBuildVersion'...
ETLAlchemySource (INFO) - Loaded indexes and FKs for table 'AWBuildVersion'
ETLAlchemySource (WARNING) - Table 'AWBuildVersion' does not exist in the dst database (we will create this later...)
ETLAlchemySource (INFO) - Building query to fetch all rows from AWBuildVersion
ETLAlchemySource (INFO) - Done. (1 total rows)
ETLAlchemySource (INFO) - Loading all rows into memory...
ETLAlchemySource (INFO) - Done
ETLAlchemySource (INFO) - (SystemInformationID) TINYINT
ETLAlchemySource (INFO) - Bases: ['INTEGER']
ETLAlchemySource (INFO) - (SystemInformationID) Class: TINYINT
ETLAlchemySource (INFO) - (SystemInformationID) ---> Bases: (<class 'sqlalchemy.sql.sqltypes.Integer'>,)
ETLAlchemySource (INFO) - Checking column for elimination status...
ETLAlchemySource (INFO) - (Database Version) NVARCHAR
ETLAlchemySource (INFO) - Bases: ['UNICODE']
ETLAlchemySource (INFO) - Checking column for elimination status...
ETLAlchemySource (INFO) - (VersionDate) DATETIME
ETLAlchemySource (INFO) - Bases: ['DATETIME']
ETLAlchemySource (WARNING) - {'datetime': 1}
ETLAlchemySource (INFO) - Postgresql has no DATETIME - converting to TIMESTAMP
ETLAlchemySource (INFO) - Checking column for elimination status...
ETLAlchemySource (INFO) - (ModifiedDate) DATETIME
ETLAlchemySource (INFO) - Bases: ['DATETIME']
ETLAlchemySource (WARNING) - {'datetime': 1}
ETLAlchemySource (INFO) - Postgresql has no DATETIME - converting to TIMESTAMP
ETLAlchemySource (INFO) - Checking column for elimination status...
ETLAlchemySource (INFO) -  --> Creating table 'AWBuildVersion'
ETLAlchemySource (INFO) - Transforming & Dumping 1 total rows from table 'AWBuildVersion' into '/home/foo/airflow/dags/AWBuildVersion.sql'.
ETLAlchemySource (INFO) -  (AWBuildVersion) -- Transforming rows: 0 -> 1...(1 Total)
ETLAlchemySource (INFO) -  (AWBuildVersion) -- Dumping rows: 0 -> 1 to 'AWBuildVersion.sql'...(1 Total)[Table 0/6]
ETLAlchemySource (INFO) - Transferring data from local file 'AWBuildVersion.sql' to target DB
ETLAlchemySource (INFO) - Sending data to target Postgresql instance...(Fast [COPY ... FROM ... WITH CSV]):
 ----> COPY AWBuildVersion (SystemInformationID,Database Version,VersionDate,ModifiedDate) FROM '/home/foo/airflow/dags/AWBuildVersion.sql'
                    WITH CSV QUOTE ''''
                    ESCAPE '\'
ETLAlchemySource (INFO) - Done.
ETLAlchemySource (INFO) - Cleaning up 'AWBuildVersion'.sql
ETLAlchemySource (INFO) - Done
ETLAlchemySource (INFO) - Reading Table Schema 'DatabaseLog'...
/home/foo/.local/lib/python2.7/site-packages/sqlalchemy/dialects/mssql/base.py:1829: SAWarning: Did not recognize type 'xml' of column 'XmlEvent'
  (type, name))
ETLAlchemySource (INFO) - Loaded indexes and FKs for table 'DatabaseLog'
ETLAlchemySource (WARNING) - Table 'DatabaseLog' does not exist in the dst database (we will create this later...)
ETLAlchemySource (INFO) - Building query to fetch all rows from DatabaseLog
ETLAlchemySource (INFO) - Done. (1597 total rows)
ETLAlchemySource (INFO) - Loading all rows into memory...
ETLAlchemySource (INFO) - Done
ETLAlchemySource (INFO) - (DatabaseLogID) INTEGER
ETLAlchemySource (INFO) - Bases: ['INTEGER']
ETLAlchemySource (INFO) - (DatabaseLogID) Class: INTEGER
ETLAlchemySource (INFO) - (DatabaseLogID) ---> Bases: (<class 'sqlalchemy.sql.sqltypes.Integer'>,)
ETLAlchemySource (INFO) - Checking column for elimination status...
ETLAlchemySource (INFO) - (PostTime) DATETIME
ETLAlchemySource (INFO) - Bases: ['DATETIME']
ETLAlchemySource (WARNING) - {'datetime': 1597}
ETLAlchemySource (INFO) - Postgresql has no DATETIME - converting to TIMESTAMP
ETLAlchemySource (INFO) - Checking column for elimination status...
ETLAlchemySource (INFO) - (DatabaseUser) NVARCHAR
ETLAlchemySource (INFO) - Bases: ['UNICODE']
ETLAlchemySource (INFO) - Checking column for elimination status...
ETLAlchemySource (INFO) - (Event) NVARCHAR
ETLAlchemySource (INFO) - Bases: ['UNICODE']
ETLAlchemySource (INFO) - Checking column for elimination status...
ETLAlchemySource (INFO) - (Schema) NVARCHAR
ETLAlchemySource (INFO) - Bases: ['UNICODE']
ETLAlchemySource (INFO) - Checking column for elimination status...
ETLAlchemySource (INFO) - (Object) NVARCHAR
ETLAlchemySource (INFO) - Bases: ['UNICODE']
ETLAlchemySource (INFO) - Checking column for elimination status...
ETLAlchemySource (INFO) - (TSQL) NVARCHAR
ETLAlchemySource (INFO) - Bases: ['UNICODE']
ETLAlchemySource (INFO) - Checking column for elimination status...
ETLAlchemySource (INFO) - (XmlEvent) NullType
ETLAlchemySource (INFO) - Bases: ['TYPEENGINE']
ETLAlchemySource (WARNING) - Type 'NullType' has no base class!
ETLAlchemySource (INFO) - Checking column for elimination status...
ETLAlchemySource (INFO) -  --> Creating table 'DatabaseLog'
ETLAlchemySource (ERROR) - Failed to create table 'DatabaseLog'

%d format: a number is required, not str
Traceback (most recent call last):
  File "alchemy_foo.py", line 18, in <module>
    tgt.migrate()
  File "/home/foo/.local/lib/python2.7/site-packages/etlalchemy/ETLAlchemyTarget.py", line 86, in migrate
    migrate_data=migrate_data)
  File "/home/foo/.local/lib/python2.7/site-packages/etlalchemy/ETLAlchemySource.py", line 1073, in migrate
    tableCreationSuccess = self.create_table(T_dst_exists, T)
  File "/home/foo/.local/lib/python2.7/site-packages/etlalchemy/ETLAlchemySource.py", line 590, in create_table
    T.create(conn)
  File "/home/foo/.local/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 725, in create
    checkfirst=checkfirst)
  File "/home/foo/.local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1481, in _run_visitor
    **kwargs).traverse_single(element)
  File "/home/foo/.local/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py", line 121, in traverse_single
    return meth(obj, **kw)
  File "/home/foo/.local/lib/python2.7/site-packages/sqlalchemy/sql/ddl.py", line 764, in visit_table
    include_foreign_key_constraints=include_foreign_key_constraints
  File "/home/foo/.local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 914, in execute
    return meth(self, multiparams, params)
  File "/home/foo/.local/lib/python2.7/site-packages/sqlalchemy/sql/ddl.py", line 68, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "/home/foo/.local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 962, in _execute_ddl
    compiled = ddl.compile(dialect=dialect)
  File "<string>", line 1, in <lambda>
  File "/home/foo/.local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 494, in compile
    return self._compiler(dialect, bind=bind, **kw)
  File "/home/foo/.local/lib/python2.7/site-packages/sqlalchemy/sql/ddl.py", line 26, in _compiler
    return dialect.ddl_compiler(dialect, self, **kw)
  File "/home/foo/.local/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 190, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
  File "/home/foo/.local/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 213, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/home/foo/.local/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
    return meth(self, **kw)
  File "/home/foo/.local/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 2157, in visit_create_table
    and not first_pk)
  File "/home/foo/.local/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 213, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/home/foo/.local/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
    return meth(self, **kw)
  File "/home/foo/.local/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 2188, in visit_create_column
    first_pk=first_pk
  File "/home/foo/.local/lib/python2.7/site-packages/sqlalchemy/dialects/postgresql/base.py", line 1600, in get_column_specification
    type_expression=column)
  File "/home/foo/.local/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 261, in process
    return type_._compiler_dispatch(self, **kw)
  File "/home/foo/.local/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
    return meth(self, **kw)
  File "/home/foo/.local/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 2589, in visit_string
    return self.visit_VARCHAR(type_, **kw)
  File "/home/foo/.local/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 2535, in visit_VARCHAR
    return self._render_string_type(type_, "VARCHAR")
  File "/home/foo/.local/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 2523, in _render_string_type
    text += "(%d)" % type_.length
TypeError: %d format: a number is required, not str