Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Trouble testing this with Adventureworks2014 MSSQL -> Postgresql #30

Open
kaylon opened this issue Oct 19, 2017 · 0 comments
Open

Trouble testing this with Adventureworks2014 MSSQL -> Postgresql #30

kaylon opened this issue Oct 19, 2017 · 0 comments

Comments

@kaylon
Copy link

kaylon commented Oct 19, 2017 •
edited
Loading

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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@kaylon