Migration de données de MS SQL à PostgreSQL en utilisant SQLAlchemy

TL; DR

Je souhaite migrer datatables d'un MS SQL Server + ArcSDE vers un PostGIS PostgreSQL +, idéalement en utilisant SQLAlchemy.


J'utilise SQLAlchemy 1.0.11 pour migrer une database existante de MS SQL 2012 vers PostgreSQL 9.2 (mise à niveau vers 9.5 prévue).

J'ai lu à ce sujet et trouvé quelques sources différentes ( Tyler Lesmann , Inada Naoki , Stefan Urbanek et Mathias Fussenegger ) avec une approche similaire pour cette tâche:

  1. Connectez-vous aux deux bases de données
  2. Reflète les tables de la database source
  3. Itérer sur les tables et pour chaque table
    1. Créer une table égale dans la database cible
    2. Récupère les lignes dans la source et les insère dans la database cible

Code

Voici un petit exemple utilisant le code de la dernière reference.

from sqlalchemy import create_engine, MetaData src = create_engine('mssql://user:pass@host/database?driver=ODBC+Driver+13+for+SQL+Server') dst = create_engine('postgresql://user:pass@host/database') meta = MetaData() meta.reflect(bind=src) tables = meta.tables for tbl in tables: data = src.execute(tables[tbl].select()).fetchall() if data: dst.execute(tables[tbl].insert(), data) 

Je suis conscient que récupérer toutes les lignes en même time est une mauvaise idée, cela peut être fait avec un iterator ou avec fetchmany , mais ce n'est pas mon problème maintenant.

Problème 1

Tous les quatre exemples échouent avec mes bases de données. Une des erreurs que je reçois est liée à une colonne de type NVARCHAR :

 sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) type "nvarchar" does not exist LINE 5: "desigOperador" NVARCHAR(100) COLLATE "SQL_Latin1_General_C... ^ [SQL: '\nCREATE TABLE "Operators" (\n\t"idOperador" INTEGER NOT NULL, \n\t"idGrupo" INTEGER, \n\t"desigOperador" NVARCHAR(100) COLLATE "SQL_Latin1_General_CP1_CI_AS", \n\t"Rua" NVARCHAR(200) COLLATE "SQL_Latin1_General_CP1_CI_AS", \n\t"Localidade" NVARCHAR(200) COLLATE "SQL_Latin1_General_CP1_CI_AS", \n\t"codPostal" NVARCHAR(10) COLLATE "SQL_Latin1_General_CP1_CI_AS", \n\tdataini DATETIME, \n\tdataact DATETIME, \n\temail NVARCHAR(50) COLLATE "SQL_Latin1_General_CP1_CI_AS", \n\turl NVARCHAR(50) COLLATE "SQL_Latin1_General_CP1_CI_AS", \n\tPRIMARY KEY ("idOperador")\n)\n\n'] 

Ma compréhension de cette erreur est que PostgreSQL n'a pas NVARCHAR mais VARCHAR , ce qui devrait être équivalent. Je pensais que SQLAlchemy mappait automatiquement les deux à Ssortingng dans sa couche d'abstraction, mais peut-être que cela ne fonctionne pas de cette façon dans ce cas.

Question: Dois-je définir toutes les classs / tables à l'avance, par exemple, dans models.py , afin d'éviter des erreurs comme celle-ci? Si oui, comment cela s'intégrerait-il au stream de travail donné (ou autre)?

En fait, cette erreur a été obtenue en exécutant le code d'Urbanek, où je peux spécifier quelles tables je veux copyr. Exécuter l'échantillon ci-dessus, me conduit à …

Problème 2

L'installation MS SQL est une géodatabase qui utilise ArcSDE (Spatial Database Engine). Pour cette raison, certaines colonnes ont un type de geometry non défini par défaut . Du côté de PostgreSQL , j'utilise PostGIS 2 .

Lorsque j'essaie de copyr des arrays avec ces types, j'obtiens des avertissements comme ceux-ci:

 /usr/local/lib/python2.7/dist-packages/sqlalchemy/dialects/mssql/base.py:1791: SAWarning: Did not recognize type 'geometry' of column 'geom' (type, name)) /usr/local/lib/python2.7/dist-packages/sqlalchemy/dialects/mssql/base.py:1791: SAWarning: Did not recognize type 'geometry' of column 'shape' 

Ceux-ci sont ensuite suivis d'une autre erreur (celle-ci a effectivement été levée lors de l'exécution du code fourni ci-dessus):

 sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) relation "SDE_spatial_references" does not exist LINE 1: INSERT INTO "SDE_spatial_references" (srid, description, aut... ^ 

Je pense qu'il n'a pas réussi à créer les colonnes référencées dans les avertissements, mais l'erreur a été lancée à une étape ultérieure lorsque ces colonnes étaient nécessaires.

Question: La question est une extension de la précédente: comment faire la migration avec des types personnalisés (ou définis ailleurs)?

Je connais GeoAlchemy2 qui peut être utilisé avec PostGIS. GeoAlchemy prend en charge MS SQL Server 2008 , mais dans ce cas, je suppose que je suis coincé avec SQLAlchemy 0.8.4 (peut-être avec des fonctionnalités less sympa). Aussi, j'ai trouvé ici qu'il est possible de faire la reflection en utilisant des types définis par GeoAlchemy. Cependant, mes questions restnt.

Peut-être lié

  • https://stackoverflow.com/questions/34475241/how-to-migrate-from-mysql-to-postgressql-using-pymysql
  • SqlAlchemy: exporter la table vers la nouvelle database
  • https://stackoverflow.com/questions/34956523/sqlalchemy-custom-column-type-use-bindparam-as-multiple-function-parameters
  • Réflexion SQLAlchemy utilisant métaclass avec rlocation de colonne

modifier

Lorsque j'ai vu l'erreur référençant SDE_spatial_references j'ai pensé que cela pourrait être lié à ArcSDE, car ArcGIS for Server est également installé sur la même machine. Puis j'ai appris que MS SQL Server a aussi quelques types de données spatiales , et puis j'ai confirmé que c'est le cas. Je me suis trompé avec cette édition: la database utilise en effet ArcSDE.

Modifier 2

Voici quelques détails que j'ai oublié d'inclure.

La migration ne doit pas être effectuée avec SQLAlchemy. Je pensais que ce serait une bonne idée parce que:

  • Je préfère travailler avec Python
  • La solution doit être avec FOSS
  • Idéalement, il serait d'une manière facilement reproductible, et possible de lancer et d'attendre
  • Après la migration, j'aimerais utiliser Alembic pour effectuer d'autres migrations de schémas

D'autres choses que j'ai essayées et échouées (je ne peux pas me callbacker maintenant les raisons exactes, mais je les passerais à nouveau en revue si une réponse s'y référait):

  • Bouilloire
  • Geokettle
  • ogr2ogr (essayant toujours cette approche)

Détails de la database

  • Petite database, ± 3 Go
  • ± 40 tables
  • Il y a des arrays avec des données spatiales et non spatiales
  • Les deux bases de données (SQL Server et PostgreSQL) dans le même server, qui exécute Windows Server 2008
  • Pas de gros problème avec les time d'arrêt (jusqu'à 8 heures serait bien)

Je recommand ce stream avec deux grandes étapes à migrer:

Migrer le schéma

  • Décharger le schéma de database source, de preference à un format unifié à travers les outils de données comme UML (cette étape et les prochaines étapes seront plus faciles avec Toad Data Modeler ou IBM Rational Rose ).
  • Changez les définitions de tables des types de sources en types de cibles si nécessaire avec TDM ou RR. Par exemple. se débarrasser de varchar(n) et coller au text dans postgres, sauf si vous avez spécifiquement besoin d'une application pour planter sur la couche de données avec des strings plus longues que n . Omettez (pour l'instant) les types complexes comme la geometry, s'il n'y a aucun moyen de les convertir dans les outils de modélisation de données.
  • Générez un file DDL pour la database cible (les outils mentionnés sont utiles ici, encore une fois).
  • Créer (et append aux tables) des types complexes tels qu'ils devraient être gérés par le SGBDR cible. Essayez d'insert quelques inputs pour vous assurer que les types de données sont cohérents. Ajoutez ces types à votre file DDL .
  • Vous pouvez également désactiver les controls comme les contraintes de key étrangère ici.

Migrer datatables

  1. Dump arrays simples (c'est-à-dire avec des champs scalaires) à un CSV.
  2. Importer des données de tables simples .
  3. Ecrire un simple morceau de code pour sélectionner des données complexes à partir de la source et l'insert dans la cible (c'est plus facile que ça en a l'air, il suffit de sélectionner -> attributes de carte -> insert). N'écrivez pas la migration pour tous les types complexes dans une seule routine de code, gardez-la simple, divisez et conquérez.
  4. Si vous n'avez pas désactivé les vérifications pendant la migration du schéma, il est possible que vous deviez répéter les étapes 2 et 3 pour différentes tables (c'est pourquoi, bien, désactivez les vérifications :)).
  5. Activer les vérifications

De cette façon, vous splitez votre process de migration en étapes atomiques simples, et l'échec d'une étape 3 de migration de données ne vous obligera pas à revenir à la migration de schéma, etc. Vous pouvez simplement tronquer quelques tables et réexécuter l'import de données si quelque chose échoue.

Voici ma solution en utilisant SQLAlchemy. C'est un article de type blog, j'espère que c'est quelque chose d'acceptable et utile à quelqu'un.

Il est possible que cela fonctionne également avec d'autres combinaisons de bases de données source et cible (en plus de MS SQL Server et de PostgreSQL, respectivement), bien qu'elles n'aient pas été testées.

Workflow (sorte de TL, DR)

  1. Inspectez la source automatiquement et déduisez les templates de table existants (c'est ce qu'on appelle la reflection).
  2. Importe les templates de table précédemment définis qui seront utilisés pour créer les nouvelles tables dans la cible.
  3. Itérer sur les templates de table (ceux existant à la fois dans la source et dans la cible).
  4. Pour chaque table, récupérez des blocs de lignes à partir de la source et insérez-les dans la cible.

Exigences

  • SQLAlchemy
  • GeoAlchemy2
  • sqlacodegen

Étapes détaillées

1. Connectez-vous aux bases de données

SQLAlchemy appelle le moteur à l'object qui gère la connection entre l'application et la database réelle. Ainsi, pour se connecter aux bases de données, un moteur doit être créé avec la string de connection correspondante. La forme typique d'une URL de database est:

 dialect+driver://username:password@host:port/database 

Vous pouvez voir quelques exemples d'URL de connection dans la documentation SQLAlchemy .

Une fois créé, le moteur n'établit pas de connection tant qu'il n'est pas explicitement demandé de le faire, soit par la méthode .connect() , soit lorsqu'une opération dépendant de cette méthode est invoquée (par exemple, .execute() ).

 con = ms_sql.connect() 

2. Définir et créer des tables

2.1 Base de données source

Les tables du côté source sont déjà définies, nous pouvons donc utiliser la reflection de table:

 from sqlalchemy import MetaData metadata = MetaData(source_engine) metadata.reflect(bind=source_engine) 

Vous pouvez voir des avertissements si vous essayez ceci. Par exemple,

 SAWarning: Did not recognize type 'geometry' of column 'Shape' 

En effet, SQLAlchemy ne reconnaît pas automatiquement les types personnalisés. Dans mon cas, c'était à cause d'un type ArcSDE. Cependant, ceci n'est pas problématique lorsque vous avez seulement besoin de lire des données. Ignorez simplement ces avertissements.

Après la reflection de la table, vous pouvez accéder aux tables existantes via cet object de métadonnées.

 # see all the tables names print list(metadata.tables) # handle the table named 'Troco' src_table = metadata.tables['Troco'] # see that table columns print src_table.c 

2.2 Base de données cible

Pour la cible, parce que nous démarrons une nouvelle database, il n'est pas possible d'utiliser la reflection des tables. Cependant, il n'est pas compliqué de créer les templates de table via SQLAlchemy ; en fait, cela pourrait même être plus simple que d'écrire du SQL pur.

 from sqlalchemy import Column, Integer, Ssortingng from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class SomeClass(Base): __tablename__ = 'some_table' id = Column(Integer, primary_key=True) name = Column(Ssortingng(50)) Shape = Column(Geometry('MULTIPOLYGON', srid=102165)) 

Dans cet exemple, il y a une colonne avec des données spatiales (définies ici grâce à GeoAlchemy2).

Maintenant, si vous avez des dixièmes de tables, définir autant de tables peut être déconcertant, fastidieux ou sujet aux erreurs. Heureusement, il y a sqlacodegen , un outil qui lit la structure d'une database existante et génère le code du model SQLAlchemy correspondant. Exemple:

 pip install sqlacodegen sqlacodegen mssql:///some_local_db --outfile models.py 

Étant donné que le but ici est simplement de migrer datatables, et non le schéma, vous pouvez créer les templates à partir de la database source et simplement adapter / corriger le code généré à la database cible.

Remarque: Il générera des templates de class mixtes et des templates de Table . Lisez ici à propos de ce comportement.

Encore une fois, vous verrez des avertissements similaires concernant les types de données personnalisés non reconnus. C'est l'une des raisons pour lesquelles nous devons maintenant éditer le file models.py et ajuster les templates. Voici quelques conseils sur les choses à régler:

  • Les colonnes avec des types de données personnalisés sont définies avec NullType . Remplacez-les par le type approprié, par exemple, Géomésortinge de GeoAlchemy2. Lors de la définition de Geometry , transmettez le type de geometry correct (linessortingng, multilinessortingng, polygon, etc.) et le SRID.
  • Les types de caractères PostgreSQL sont de longueur variable, et SQLAlchemy leur assignera par défaut des colonnes Ssortingng , donc nous pouvons replace tous les Unicode et Ssortingng(...) par Ssortingng . Notez qu'il n'est pas nécessaire, ni conseillé (ne me citez pas dessus), de spécifier le nombre de caractères dans Ssortingng , il suffit de les omettre.
  • Vous devrez vérifier, mais, probablement, toutes les colonnes BIT sont en fait Boolean .
  • La plupart des types numériques (par exemple, Float(...) , Numeric(...) ), de même pour les types de caractères, peuvent être simplifiés en Numeric . Soyez prudent avec des exceptions et / ou un cas spécifique.
  • J'ai remarqué quelques problèmes avec des colonnes définies comme des index ( index=True ). Dans mon cas, parce que le schéma sera migré, ceux-ci ne devraient pas être requirejs maintenant et pourraient être supprimés en toute security.
  • Assurez-vous que les noms de table et de colonne sont les mêmes dans les deux bases de données (tables reflétées et templates définis), ceci est obligatoire pour une étape ultérieure.

Maintenant, nous pouvons connecter les templates et la database set, et créer toutes les tables du côté cible.

 Base.metadata.bind = postgres Base.metadata.create_all() 

Notez que, par défaut, .create_all() ne touchera pas les tables existantes. Dans le cas où vous souhaitez recréer ou insert des données dans une table existante, il est nécessaire de le faire au préalable.

 Base.metadata.drop_all() 

3. Obtenez des données

Vous êtes maintenant prêt à copyr des données d'un côté et, plus tard, à les coller dans l'autre. Fondamentalement, vous avez juste besoin d'émettre une requête SELECT pour chaque table. C'est quelque chose de possible et facile à faire sur la couche d'abstraction fournie par SQLAlchemy ORM.

 data = ms_sql.execute(metadata.tables['TableName'].select()).fetchall() 

Cependant, cela ne suffit pas, vous aurez besoin d'un peu plus de contrôle. La raison en est liée à ArcSDE. Parce qu'il utilise un format propriétaire, vous pouvez récupérer datatables mais vous ne pouvez pas les parsingr correctement. Vous obtiendrez quelque chose comme ceci:

 (1, Decimal('0'), u' ', bytearray(b'\x01\x02\x00\x00\x00\x02\x00\x00\x00@\xb1\xbf\xec/\xf8\xf4\xc0\x80\nF%\x99(\xf9\xc0@\xe3\xa5\x9b\x94\xf6\xf4\xc0\x806\xab>\xc5%\xf9\xc0')) 

La solution de contournement consistait à convertir la colonne géomésortingque au format WKT (Well Known Text). Cette conversion doit avoir lieu du côté de la database. ArcSDE est là, donc il sait comment le convertir. Par exemple, dans TableName, il existe une colonne avec des données spatiales appelées shape . L'instruction SQL requirejse doit ressembler à ceci:

 SELECT [TableName].[shape].STAsText() FROM [TableName] 

Cela utilise .STAsText() , une méthode de type de données de geometry de SQL Server.

Si vous ne travaillez pas avec ArcSDE, les étapes suivantes ne sont pas requirejses:

  • parcourir les tables (seulement celles qui sont définies à la fois dans la source et dans la cible),
  • pour chaque table, searchz une colonne de geometry (listz-les au préalable)
  • build une instruction SQL comme celle ci-dessus

Une fois une instruction créée, SQLAlchemy peut l'exécuter.

 result = ms_sql.execute(statement) 

En fait, cela n'obtient pas vraiment datatables (comparez avec l'exemple ORM – notez l'appel .fetchall() manquant). Pour expliquer, voici une citation des docs de SQLAlchemy:

Le résultat renvoyé est une instance de ResultProxy , qui fait reference à un slider DBAPI et fournit une interface largement compatible avec celle du slider DBAPI. Le slider DBAPI sera fermé par ResultProxy lorsque toutes ses lignes de résultat (le cas échéant) sont épuisées.

Les données ne seront récupérées que juste avant leur insertion.

4. Insérer des données

Les connections sont établies, les tables sont créées, datatables ont été préparées, maintenant insérons-le. De même que pour get datatables, SQLAlchemy permet également d' INSERT données dans une table donnée via son ORM:

 postgres_engine.execute(Base.metadata.tables['TableName'].insert(), data) 

Encore une fois, c'est facile, mais à cause de formats non standard et de données erronées, d'autres manipulations seront probablement nécessaires.

4.1 Colonnes correspondantes

Tout d'abord, il y avait quelques problèmes avec la correspondance des colonnes sources avec les colonnes cibles (de la même table) – peut-être était-ce lié à la colonne Geometry . Une solution possible consiste à créer un dictionary Python, qui mappe les valeurs de la colonne source à la key (nom) de la colonne cible.

Ceci est effectué ligne par ligne – bien que ce ne soit pas aussi lent que l'on pourrait le penser, car l'insertion réelle se fera sur plusieurs lignes en même time. Donc, il y aura un dictionary par ligne, et, au lieu d'insert l'object de données (qui est une list de tuples, un tuple correspond à une ligne), vous allez insert une list de dictionarys.

Voici un exemple pour une seule ligne. Les données extraites sont une list avec un seul tuple, et les valeurs sont le dictionary construit.

 # data [(1, 6, None, None, 204, 1, True, False, 204, 1.0, 1.0, 1.0, False, None] # values [{'DateDeleted': None, 'sentidocirculacao': False, 'TempoPercursoMed': 1.0, 'ExtensaoTroco': 204, 'OBJECTID': 229119, 'NumViasSentido': 1, 'Deleted': False, 'TempoPercursoMin': 1.0, 'IdCentroOp': 6, 'IDParagemInicio': None, 'IDParagemFim': None, 'TipoPavimento': True, 'TempoPercursoMax': 1.0, 'IDTroco': 1, 'CorredorBusext': 204}] 

Notez que les dictionarys Python ne sont pas classés, c'est pourquoi les nombres dans les deux lists ne sont pas dans la même position. La colonne géomésortingque a été supprimée de cet exemple pour simplifier.

4.2 Fixer des geometrys

Probablement, la solution de contournement précédente ne serait pas requirejse si ce problème n'avait pas eu lieu: parfois les geometrys sont stockées / récupérées avec le type incorrect.

Dans MSSQL / ArcSDE, le type de données de geometry ne spécifie pas le type de geometry dans lequel il est stocké (par exemple, ligne, polygone, etc.). Il se soucie seulement que c'est une geometry. Cette information est stockée dans une autre table (système), appelée SDE_geometry_columns (voir en bas de cette page). Cependant, Postgres (PostGIS, en fait) requirejs le type de geometry lors de la définition d'une colonne géomésortingque.

Cela conduit à stocker des données spatiales avec le mauvais type de geometry. Par mauvais je veux dire que c'est différent de ce qu'il devrait être. Par exemple, en regardant la table SDE_geometry_columns (extrait):

 f_table_name geometry_type TableName 9 

geometry_type = 9 correspond à ST_MULTILINESTRING . Cependant, il existe des lignes dans la table TableName qui sont stockées (ou reçues) en tant que ST_LINESTRING . Cette discordance provoque une erreur du côté de Postgres.

Pour contourner le problème, vous pouvez modifier le WKT en créant les dictionarys susmentionnés. Par exemple, 'LINESTRING (10 12, 20 22)' est transformé en MULTILINESTRING ((10 12, 20 22))' .

4.3 SRID manquant

Enfin, si vous souhaitez conserver les SRID, vous devez également les définir lors de la création de colonnes géomésortingques.

Si un SRID est défini dans le model de table, il doit être satisfait lors de l'insertion de données dans Postgres. Le problème est que lorsque vous récupérez des données géomésortingques en tant que WKT avec la méthode .STAsText() , vous perdez les informations SRID.

Heureusement, PostGIS prend en charge un format Extended-WKT (E-WKT) incluant le SRID. La solution ici consiste à inclure le SRID lors de la fixation des geometrys. Avec le même exemple, 'LINESTRING (10 12, 20 22)' est transformé en 'SRID=102165;MULTILINESTRING ((10 12, 20 22))' .

4.4 Chercher et insert

Une fois que tout est réparé, vous êtes prêt à insert. Comme mentionné précédemment, seulement maintenant datatables seront réellement récupérées de la source. Vous pouvez le faire en morceaux (une quantité définie par l'user) de données, par exemple, 1000 lignes à la fois.

alors que True: rows = data.fetchmany (1000) sinon les lignes: break values ​​= [{key: (val si key.lower ()! = "shape" sinon fix (val, 102165)) pour key, val in zip ( keys, ligne)} pour la ligne dans les lignes] postgres_engine.execute (target_table.insert (), valeurs)

Ici fix() est la fonction qui corrigera les geometrys et appenda le SRID donné aux colonnes géomésortingques (qui sont identifiées, dans cet exemple, par le nom de colonne "shape") – comme décrit ci-dessus -, et les valeurs sont les list de dictionarys précitée.

Résultat

Le résultat est une copy du schéma et des données, existant sur une database MS SQL Server + ArcSDE, dans une database PostgreSQL + PostGIS.

Voici quelques statistics, issues de mon cas d'utilisation, pour l'parsing des performances. Les deux bases de données sont dans la même machine; le code a été exécuté à partir d'une machine différente, mais dans le même réseau local.

 Tables | Geometry Column | Rows | Fixed Geomesortinges | Insert Time --------------------------------------------------------------------------------- Table 1 MULTILINESTRING 1114797 702 17min12s Table 2 None 460874 --- 4min55s Table 3 MULTILINESTRING 389485 389485 4min20s Table 4 MULTIPOLYGON 4050 3993 34s Total 3777964 871243 48min27s 

J'ai fait face aux mêmes problèmes en essayant de migrer d' Oracle 9i vers MySQL .

J'ai construit etlalchemy pour résoudre ce problème, et il a été testé pour migrer depuis et vers MySQL, PostgreSQL, SQL Server, Oracle et SQLite . Il exploite SQLAlchemy, et BULK CSV Import caractéristiques des SGBDR susmentionnés (et peut être assez rapide!).

Installer (non El-capitan): pip install etlalchemy

Installer (El-capitan): pip install --ignore-installed etlalchemy

Courir:

 from etlalchemy import ETLAlchemySource, ETLAlchemyTarget # Migrate from SQL Server onto PostgreSQL src = ETLAlchemySource("mssql+pyodbc://user:passwd@DSN_NAME") tgt = ETLAlchemyTarget("postgresql://user:passwd@hostname/dbname", drop_database=True) tgt.addSource(src) tgt.migrate()