Suite

Insertion en masse avec Python et cx_Oracle dans la table activée par st_geometry ?

Insertion en masse avec Python et cx_Oracle dans la table activée par st_geometry ?


J'essaie de faire une insertion en bloc d'un fichier CSV avec Python et cx_Oracle dans une table compatible st_geometry d'une base de données Oracle avec ArcSDE.

Je pourrais le faire avec la méthode execute() de cx_Oracle mais pour les gros fichiers, ce n'est pas l'approche la plus rapide. C'est pourquoi j'essaie de faire fonctionner la méthode executemany(). Avec executemany(), je peux insérer des registres avec des chaînes, des entiers,… mais pas des champs st_geometry. Avec les champs st_geometry, j'obtiens l'erreur : cx_Oracle.DatabaseError ORA-01036 : nom/numéro de variable illégal

Avec ce code "print cursor.bindnames()" renvoie ['1','2'], ce qui signifie que :3 y :4 ne sont pas reconnus. Juste pour le bien de l'argument, si je modifie st_geometry('point(:3 :4)',0) en st_geometry(point(:3 :4),0) "print cursor.bindnames()" renvoie ['1 ','2','3'], ce que cela signifie que :4 n'est pas reconnu.

Voici l'exemple de code que j'utilise actuellement :


Solution de contournement trouvée, voir la réponse suivante

# Insertion en bloc # # Sample test st_geometry table # create table test (col1 varchar2 (30 octets), col2 varchar2 (30 octets), forme sde.st_geometry); # # Exemple d'insertion SQL pour insérer des données dans la table # insertion dans les valeurs de test (col1, col2, shape) ('val01', 'val02', sde.st_geometry('point(12.67 2.32)', 0)); # # Exemple de fichier.csv # val11,val12,34.22,23.22 # val21,val22,12.33,14.22 # val31,val32,13.21,18.32 # import cx_Oracle dsn_tns = cx_Oracle.makedsn('server', 1521, 'sid') dbCon = cx_Oracle.connect('user', 'password', dsn_tns) cursor = dbCon.cursor() list = [] f = open('file.csv','r') # srid doit être un code valide dans sde. st_spatial_references cursor.prepare("""INSERT INTO test (col1,col2,shape) VALUES (:1,:2,sde.st_geometry('point(:3 :4)',0))""") pour la ligne dans f: pair = line.split(',') list.append((pair[0],pair[1],float(pair[2]),float(pair[3]))) print cursor.bindnames() curseur.executemany(Aucun, liste) dbCon.commit() f.close() curseur.close() dbCon.close()

J'ai trouvé un autre moyen d'insérer des points en bloc dans la géodatabase ArcSDE. Il est basé sur une autre façon de définir les points présentée par ESRI ici, sde.st_geometry (x,y,z,m,srid), qui est recommandée lors de l'exécution d'insertions par lots d'un grand nombre de données de points.

# Insertion en bloc # # Exemple de test st_geometry table # créer un test de table (col1 varchar2 (30 octets), col2 varchar2 (30 octets), shape sde.st_geometry); # # Exemple d'insertion SQL pour insérer des données dans la table # insertion dans les valeurs de test (col1, col2, shape) ('val01', 'val02', sde.st_geometry('point(12.67 2.32)', 0)); # # Exemple de fichier.csv # val11,val12,34.22,23.22 # val21,val22,12.33,14.22 # val31,val32,13.21,18.32 # import cx_Oracle dsn_tns = cx_Oracle.makedsn('server', 1521, 'sid') dbCon = cx_Oracle.connect('user', 'password', dsn_tns) cursor = dbCon.cursor() list = [] f = open('file.csv','r') # srid doit être un code valide dans sde. st_spatial_references cursor.prepare("""INSERT INTO test (col1,col2,shape) VALUES (:1,:2,sde.st_geometry(:3,:4,null,null,srid))""") pour la ligne dans f: pair = line.split(',') list.append((pair[0],pair[1],float(pair[2]),float(pair[3]))) print cursor.bindnames() curseur.executemany(Aucun, liste) dbCon.commit() f.close() curseur.close() dbCon.close()

Insertion en masse avec Python et cx_Oracle dans la table activée par st_geometry ? - Systèmes d'information géographique

etlhelper est une bibliothèque Python pour simplifier le transfert de données entre les bases de données.

etlhelper facilite l'exécution d'une requête SQL via Python et renvoie les résultats. Il repose sur la spécification DBAPI2 et prend en charge l'importation des pilotes, le formatage des chaînes de connexion et la gestion des curseurs. Cela réduit la quantité de code passe-partout nécessaire pour interroger une base de données relationnelle avec Python.

  • Le script setup_oracle_client installe Oracle Instant Client sur les systèmes Linux
  • Les objets DbParams offrent un moyen cohérent de se connecter à différents types de bases de données (actuellement Oracle, PostgreSQL, SQLite et MS SQL Server)
  • get_rows , iter_rows , fetchone et d'autres fonctions pour interroger la base de données
  • exécuter et exécuter de nombreuses fonctions pour insérer des données
  • copy_rows pour transférer des données d'une base de données à une autre
  • Prise en charge des requêtes paramétrées et de la transformation en cours des données
  • Résultats de sortie en tant que nametuple ou dictionnaire
  • Messages de journal horodatés pour le suivi des transferts de données de longue durée
  • Des messages d'erreur utiles affichent l'échec de la requête SQL

Ces outils peuvent créer des workflows d'extraction-transformation-chargement (ETL) faciles à comprendre, légers, versionnables et testables. etlhelper n'est pas un outil pour coordonner les tâches ETL (utiliser Apache Airflow), pour convertir les formats de données SIG (utiliser ogr2ogr ou fiona), pour traduire entre les dialectes SQL ou fournir un mappage de relation d'objet (utiliser SQLAlchemy). Cependant, il peut être utilisé en conjonction avec chacun d'eux.

La documentation ci-dessous explique comment les principales fonctionnalités sont utilisées. Voir les docstrings des fonctions individuelles pour plus de détails sur les paramètres et les options.

Pour une introduction de haut niveau à etlhelper, voir la présentation FOSS4GUK 2019 ETL spatial open source avec Python et Apache Airflow: vidéo (20 min), diapositives.

Les packages de pilotes de base de données ne sont pas inclus par défaut et doivent être spécifiés entre crochets. Les options sont oracle (installe cx_Oracle), mssql (installe pyodbc) et postgres (installe psycopg2). Plusieurs valeurs peuvent être séparées par des virgules.

Le pilote sqlite3 est inclus dans la bibliothèque standard de Python.

Dépendances du pilote de base de données

Certains pilotes de base de données ont des dépendances supplémentaires. Sous Linux, ceux-ci peuvent être installés via le gestionnaire de packages système.

Les bibliothèques Oracle Instant Client sont requises pour se connecter aux bases de données Oracle. Sous Linux, etlhelper fournit un script pour les télécharger et les décompresser depuis le site Web d'Oracle. Une fois les pilotes installés, leur emplacement doit être ajouté à la variable d'environnement LD_LIBRARY_PATH avant de pouvoir être utilisés. setup_oracle_client écrit un fichier qui peut ensuite être "sourcé" pour le faire pour le shell actuel. Ces deux étapes peuvent être exécutées en une seule commande comme :

Cette commande doit être exécutée dans chaque nouvelle session shell. Voir setup_oracle_client --help pour d'autres indicateurs de ligne de commande, y compris la spécification d'une URL alternative ou d'un chemin de système de fichiers pour l'emplacement du fichier zip.

Les détails de connexion à la base de données sont définis par les objets DbParams. Les connexions s'effectuent via leurs fonctions de connexion (voir ci-dessous). Les objets DbParams sont créés comme suit ou à partir de variables d'environnement à l'aide de la fonction from_environment(). La fonction d'initialisation de classe vérifie que les attributs corrects ont été fournis pour un type de base de données donné.

Les objets DbParams ont une fonction pour vérifier si une base de données donnée est accessible via le réseau. Cela ne nécessite pas de nom d'utilisateur ou de mot de passe.

Les autres méthodes/propriétés sont get_connection_string , get_sqlalchemy_connection_string , paramstyle et copy . Voir les docstrings de fonction pour plus de détails.

La fonction DbParams.connect() renvoie une connexion DBAPI2 telle que fournie par le pilote sous-jacent. L'utilisation de la syntaxe du gestionnaire de contexte comme ci-dessous garantit que la connexion est fermée après utilisation.

Une fonction de connexion autonome offre une rétrocompatibilité avec les versions précédentes d'etlhelper :

Les deux versions acceptent des arguments de mot-clé supplémentaires qui sont transmis à la fonction de connexion du pilote sous-jacent. Par exemple, ce qui suit définit l'encodage de caractères utilisé par cx_Oracle pour garantir que les valeurs sont renvoyées au format UTF-8 :

Ce qui précède est une solution lorsque des caractères spéciaux sont brouillés dans les données renvoyées.

Désactivation de fast_executemany pour SQL Server et d'autres connexions pyODBC

Par défaut, une connexion etlhelper pyODBC utilise un curseur avec son attribut fast_executemany défini sur True . Ce paramètre améliore les performances de executemany lors de l'exécution d'insertions en bloc dans une base de données SQL Server. Cependant, cela remplace le comportement par défaut de pyODBC et il y a quelques limitations à faire cela. Il est important de noter qu'il n'est recommandé que pour les applications qui utilisent le pilote ODBC de Microsoft pour SQL Server. Voir pyODBC fast_executemany.

L'utilisation de fast_executemany peut générer une MemoryError si la requête implique des colonnes de types TEXT et NTEXT , qui sont désormais obsolètes. Dans ces circonstances, etlhelper revient sur fast_executemany défini sur False et produit une sortie d'avertissement. Voir Insertion dans le serveur SQL avec fast_executemany de nombreux résultats dans MemoryError.

Si nécessaire, l'attribut fast_executemany peut être défini sur False via la fonction connect :

Cet argument de mot-clé est utilisé par etlhelper , tout autre argument de mot-clé est passé à la fonction de connexion du pilote sous-jacent.

Les mots de passe de la base de données doivent être spécifiés via une variable d'environnement. Cela réduit la tentation de les stocker dans des scripts. Cela peut être fait en ligne de commande via :

  • export ORACLE_PASSWORD=some-secret-password sous Linux
  • définir ORACLE_PASSWORD=some-secret-password sous Windows

Soit dans un terminal Python via :

Aucun mot de passe n'est requis pour les bases de données SQLite.

La fonction get_rows renvoie une liste de tuples nommés contenant des données en tant qu'objets Python natifs.

Les données sont accessibles via l'index ( row[4] ) ou le nom ( row.day ).

D'autres fonctions sont fournies pour sélectionner des données. fetchone , fetchmany et fetchall sont équivalents aux méthodes de curseur spécifiées dans DBAPI v2.0. dump_rows passe chaque ligne à une fonction (la valeur par défaut est print ).

Il est recommandé d'utiliser iter_rows pour boucler sur de grands ensembles de résultats. C'est une fonction génératrice qui ne fournit que les données demandées. Cela garantit que les données ne sont pas toutes chargées en mémoire en même temps.

Des variables peuvent être insérées dans des requêtes en les passant comme paramètres. Ces "variables de liaison" sont nettoyées par les pilotes sous-jacents pour empêcher les attaques par injection SQL. Le paramstyle requis peut être vérifié avec MY_DB.paramstyle . Un tuple est utilisé pour les espaces réservés de position, ou un dictionnaire pour les espaces réservés nommés.

Les fabriques de lignes contrôlent le format de sortie des lignes renvoyées. Pour renvoyer chaque ligne sous forme de dictionnaire, utilisez les éléments suivants :

Le dict_row_factory est utile lorsque les données doivent être sérialisées en JSON/YAML, ou lors de la modification de champs individuels avec une fonction de transformation (voir ci-dessous). Lors de l'utilisation de dict_row_factory avec copy_rows , il est nécessaire d'utiliser des espaces réservés nommés pour la requête INSERT (par exemple %(id)s au lieu de %s pour PostgreSQL, :id au lieu de :1 pour Oracle).

execute peut être utilisé pour insérer une seule ligne ou pour exécuter d'autres instructions simples, par ex. "CRÉER TABLEAU . ". La fonction executemany est utilisée pour insérer plusieurs lignes de données. Les grands ensembles de données sont divisés en morceaux et insérés par lots pour réduire le nombre de requêtes.

L'indicateur commit_chunks est défini par défaut sur True . Cela garantit qu'une erreur lors d'un transfert de données volumineux ne nécessite pas le renvoi de tous les enregistrements. Certains travaux peuvent être nécessaires pour déterminer quels dossiers restent à envoyer. La définition de commit_chunks sur False annulera l'intégralité du transfert en cas d'erreur.

Copier les lignes prend les résultats d'une requête SELECT et les applique en tant que paramètres à une requête INSERT. Les tables source et destination doivent déjà exister.

les paramètres peuvent être transmis à la requête SELECT comme auparavant et le drapeau commit_chunks peut être défini.

Les données peuvent être transformées en vol en appliquant une fonction de transformation. Il s'agit de n'importe quel appelable Python (par exemple une fonction) qui prend un itérateur (par exemple une liste) et renvoie un autre itérateur. Les fonctions de transformation sont appliquées aux données telles qu'elles sont lues à partir de la base de données et peuvent être utilisées avec les méthodes de type get_rows et avec copy_rows .

Le code suivant montre que le morceau renvoyé peut avoir un nombre de lignes différent et être de longueur différente par rapport à l'entrée. Lorsqu'elle est utilisée avec copy_rows , la requête INSERT doit contenir les espaces réservés corrects pour le résultat de la transformation. Des données supplémentaires peuvent résulter d'un calcul, d'un appel à un webservice ou à une autre base de données.

Il peut être plus facile de modifier des colonnes individuelles lors de l'utilisation de dict_row_factory (voir ci-dessus).

Les fonctions iter_chunks et iter_rows utilisées en interne renvoient des générateurs. Chaque bloc ou ligne de données n'est accessible que lorsque cela est nécessaire. La fonction de transformation peut également être écrite pour renvoyer un générateur au lieu d'une liste. La transformation des données peut ensuite être effectuée via des chaînes d'itérateurs économes en mémoire.

Les recettes suivantes montrent comment etlhelper peut être utilisé.

Déboguer SQL et surveiller la progression avec la journalisation

ETL Helper fournit un gestionnaire de journalisation personnalisé. Les messages horodatés indiquant le nombre de lignes traitées peuvent être activés en définissant le niveau de journalisation sur INFO. La définition du niveau sur DEBUG fournit des informations sur la requête exécutée, des exemples de données et la connexion à la base de données.

La sortie d'un appel à copy_rows ressemblera à :

Remarque : les erreurs sur les connexions à la base de données génèrent des messages qui incluent les informations de connexion en texte clair.

Modèle de script ETL de copie de base de données à base de données

Ce qui suit est un modèle pour un script ETL. Il copie copie toutes les lectures de capteur de la veille d'une source Oracle vers la destination PostgreSQL.

Il est utile de créer des scripts idempotents pour s'assurer qu'ils peuvent être réexécutés sans problème. Dans cet exemple, la commande "CREATE TABLE IF NOT EXISTS" peut être appelée à plusieurs reprises. La commande DELETE_SQL efface les données existantes avant l'insertion pour éviter les erreurs de clé en double. La syntaxe SQL telle que "INSERT OR UPDATE", "UPSERT" ou "INSERT . ON CONFLICT" peut être plus efficace, mais les commandes exactes dépendent du type de base de données cible.

Appel de scripts ETL Helper à partir d'Apache Airflow

Ce qui suit est un DAG Apache Airflow qui utilise la fonction copy_readings définie dans le script ci-dessus. Le planificateur Airflow créera des tâches pour chaque jour depuis le 1er août 2019 et appellera copy_readings avec les heures de début et de fin appropriées.

Aucun pilote spécifique n'est requis pour les données spatiales si elles sont transférées en tant que texte bien connu.

D'autres opérations spatiales, par ex. les transformations de coordonnées, les intersections et la mise en mémoire tampon peuvent être effectuées dans le SQL. Les fonctions de transformation peuvent manipuler des géométries à l'aide de la bibliothèque Shapely.

Modèle de script ETL de copie de base de données vers API / NoSQL

etlhelper peut être combiné avec la bibliothèque Requests de Python pour créer un ETL permettant de publier des données d'une base de données dans une API HTTP. L'API peut être un magasin de documents NoSQL (par exemple, ElasticSearch, Cassandra) ou un autre service Web.

Cet exemple transfère des données d'Oracle vers ElasticSearch. Il utilise iter_rows pour extraire les données de la base de données sans les charger toutes en mémoire à la fois. Une fonction de transformation personnalisée crée une structure de dictionnaire imbriquée à partir de chaque ligne de données. Ceci est « vidé » dans JSON et publié sur l'API via des requêtes.

Dans cet exemple, les lignes ayant échoué sont consignées et le processus se poursuit. Pour échouer tout le travail, relancez simplement HTTPError après l'avoir enregistré. Cet exemple publie un enregistrement à la fois. Si l'API prend en charge les téléchargements en masse, il peut être plus rapide d'utiliser iter_chunks et de publier des enregistrements par lots.

La bibliothèque Pandas peut se connecter aux bases de données via SQLAlchemy. Il dispose d'outils puissants pour manipuler les données tabulaires. ETL Helper facilite la préparation de la connexion SQL Alchemy.

ETL Helper a été créé et est maintenu par British Geological Survey Informatics.

  • John A Stevenson (volcan01010)
  • Jo Walsh (métazool)
  • Declan Valters (dvalters)
  • Colin Blackburn (ximénesuk)
  • Daniel Sutton (kerberpolis)

Le code est toujours en cours de développement et des changements de rupture sont possibles. Les utilisateurs doivent épingler la version dans leurs listes de dépendances et surveiller le référentiel pour les nouvelles versions. Voir CONTRIBUTING.md pour plus de détails sur la façon de contribuer.

ETL Helper est distribué sous la licence LGPL v3.0. Copyright : © BGS / UKRI 2019


etlhelper facilite l'exécution d'une requête SQL via Python et renvoie les résultats. Il repose sur la spécification DBAPI2 et prend en charge l'importation des pilotes, le formatage des chaînes de connexion et la gestion des curseurs. Cela réduit la quantité de code passe-partout nécessaire pour interroger une base de données relationnelle avec Python.

Caractéristiques

  • Le script setup_oracle_client installe Oracle Instant Client sur les systèmes Linux
  • Les objets DbParams offrent un moyen cohérent de se connecter à différents types de bases de données (actuellement Oracle, PostgreSQL, SQLite et MS SQL Server)
  • get_rows , iter_rows , fetchone et d'autres fonctions pour interroger la base de données
  • exécuter et exécuter de nombreuses fonctions pour insérer des données
  • copy_rows pour transférer des données d'une base de données à une autre
  • Prise en charge des requêtes paramétrées et de la transformation en cours des données
  • Résultats de sortie en tant que nametuple ou dictionnaire
  • Messages de journal horodatés pour le suivi des transferts de données de longue durée
  • Des messages d'erreur utiles affichent l'échec de la requête SQL

Ces outils peuvent créer des workflows d'extraction-transformation-chargement (ETL) faciles à comprendre, légers, versionnables et testables. etlhelper n'est pas un outil pour coordonner les tâches ETL (utiliser Apache Airflow), pour convertir les formats de données SIG (utiliser ogr2ogr ou fiona), pour traduire entre les dialectes SQL ou fournir un mappage de relation d'objet (utiliser SQLAlchemy). Cependant, il peut être utilisé en conjonction avec chacun d'eux.

La documentation ci-dessous explique comment les principales fonctionnalités sont utilisées. Voir les docstrings des fonctions individuelles pour plus de détails sur les paramètres et les options.

Pour une introduction de haut niveau à etlhelper , voir la présentation FOSS4GUK 2019 ETL spatial open source avec Python et Apache Airflow: vidéo (20 min), diapositives.

Documentation


Comment créer une clé primaire dans SQL Server

Nous pouvons créer une clé primaire dans 2 manières :

2. T-SQL : créer un primaire lors de la création d'une nouvelle table

Studio de gestion SQL Server

Étape 1) Cliquez avec le bouton droit sur le nom de la table. Cliquer sur Conception.

Étape 2) Cliquez avec le bouton droit sur le nom de la colonne. Cliquer sur 'Définir la clé primaire'

Résultat: Course_Id est maintenant un Clé primaire.

T-SQL : créez une clé primaire lors de la création d'une nouvelle table.

Vous trouverez ci-dessous la syntaxe pour créer une table avec la clé primaire de T-SQL

Créons une table avec une colonne en tant que clé primaire SQL.

Étape 1) Exécutez la requête en cliquant sur 'Exécuter.'

Résultat: Course_Id est maintenant un Clé primaire.

Exemple: Voyons si cela permet de saisir plusieurs enregistrements avec le même identifiant de cours.

Étape 1) Insérez 4 lignes avec différent Course_ID

Étape 2) Vérifiez toutes les données insérées avec succès en exécutant la requête Sélection.

Noter: Nous pouvons insérer des valeurs en double dans la clé non primaire.

Étape 3) Essayons maintenant d'insérer de nouveaux enregistrements avec un Course_ID existant qui est la clé primaire.

Résultat: Le système ne permet pas d'insérer une nouvelle valeur car 4 est la colonne Course_ID qui est une clé primaire.

T-SQL : ajouter une clé primaire à une table existante à l'aide d'Alter Table

Nous allons maintenant voir comment ajouter une clé primaire à une table existante en SQL :

Vous pouvez utiliser l'instruction ALTER pour créer une clé primaire. Cependant, la clé primaire ne peut être créée que sur des colonnes définies comme NON NULL. Vous ne pouvez pas créer de clé primaire sur une colonne qui autorise les valeurs NULL. Si vous avez besoin de le faire, vous devez supprimer et recréer la table.

Nous avons ajouté une contrainte de clé primaire à une table déjà existante. La contrainte a été ajoutée sur la colonne admission et affectée au nom student_pk.


Votre première application¶

Maintenant que vous avez vérifié que la connexion fonctionne, il est temps de créer un Application Django – un ensemble de code Django, comprenant des modèles et des vues, qui cohabitent dans un seul package Python et représentent une application Django complète.

Cela vaut la peine d'expliquer la terminologie ici, car cela a tendance à faire trébucher les débutants. Nous avons déjà créé un projet, au chapitre 2, quelle est la différence entre un projet Et un application? La différence est celle de la configuration par rapport au code :

  • Un projet est une instance d'un certain ensemble d'applications Django, ainsi que la configuration de ces applications.

    Techniquement, la seule exigence d'un projet est qu'il fournisse un fichier de paramètres, qui définit les informations de connexion à la base de données, la liste des applications installées, le TEMPLATE_DIRS , etc.

  • Une application est un ensemble portable de fonctionnalités Django, comprenant généralement des modèles et des vues, qui cohabitent dans un seul package Python.

    Par exemple, Django est livré avec un certain nombre d'applications, telles qu'un système de commentaires et une interface d'administration automatique. Un élément clé à noter à propos de ces applications est qu'elles sont portables et réutilisables dans plusieurs projets.

Il existe très peu de règles strictes sur la façon dont vous ajustez votre code Django dans ce schéma. Si vous créez un site Web simple, vous ne pouvez utiliser qu'une seule application. Si vous créez un site Web complexe avec plusieurs éléments indépendants, tels qu'un système de commerce électronique et un babillard, vous souhaiterez probablement les diviser en applications distinctes afin de pouvoir les réutiliser individuellement à l'avenir. .

En effet, vous n'avez pas nécessairement besoin de créer des applications, comme en témoignent les exemples de fonctions d'affichage que nous avons créés jusqu'à présent dans ce livre. Dans ces cas, nous avons simplement créé un fichier appelé views.py , l'avons rempli de fonctions de vue et avons pointé notre URLconf sur ces fonctions. Aucune “apps” n'était nécessaire.

Cependant, il y a une exigence concernant la convention d'application : si vous utilisez la couche de base de données (modèles) de Django, vous devez créer une application Django. Les modèles doivent vivre dans les applications. Ainsi, pour commencer à écrire nos modèles, nous devrons créer une nouvelle application.

Dans le répertoire du projet mysite, saisissez cette commande pour créer une application de livres :

Cette commande ne produit aucune sortie, mais elle crée un répertoire books dans le répertoire mysite. Regardons le contenu de ce répertoire :

Ces fichiers contiendront les modèles et les vues de cette application.

Jetez un œil à models.py et views.py dans votre éditeur de texte préféré. Les deux fichiers sont vides, à l'exception des commentaires et d'un import dans models.py . C'est l'ardoise vierge pour votre application Django.


Avis de non-responsabilité

  1. La solution de génération de données la plus rapide est la suivante :
    1. Insérez le nombre requis de lignes dans chaque table parent
    2. Obtenez les identifiants selon la logique de génération de données et utilisez-les pour ajouter des lignes à la table enfant

    Dans cette astuce, je n'utiliserai pas la technique ci-dessus, mais essayez de tout faire avec une seule instruction.

    Sur mon ordinateur portable, j'ai généré 100 000 lignes en utilisant la technique ci-dessous. Dans SQL Server, cela a pris 86 secondes par rapport à la logique des 3 instructions (comme ci-dessous) qui a pris environ 5 minutes.

    1. insérer dans la 1ère table parente + stocker la sortie dans la variable
    2. insérer dans la 2 ère table parente + stocker la sortie dans la variable
    3. insérer dans une table enfant

    2 réponses 2

    Vous créez une instruction d'insertion dynamique avec des valeurs paramétrées. Cela fonctionne et il n'y a rien de mal avec cette méthode. C'est peut-être même la meilleure méthode pour votre situation. Cela fonctionne bien lorsque votre table est "petite". J'ai une base de données assez volumineuse qui croît de façon monotone. Nous continuons à ajouter des lignes et n'en supprimons jamais. Lorsque votre table grandit au-delà d'un certain point spécifique à la conception de votre table, les insertions deviennent très lentes. À ce stade, vous voudrez envisager d'utiliser la classe SqlBulkCopy. Vous insérez les valeurs dans un DataTable, puis effectuez une insertion en bloc. C'est beaucoup plus rapide car il utilise une méthode spécifique à SQL Server pour charger les données plus rapidement. Le lien SqlBulkCopy contient un exemple de code.


    Vérifiez si votre matériel et votre système d'exploitation sont correctement configurés et réglés :

    • Source du problème (CPU/IO/Memory/Swap Usage). Avez-vous beaucoup d'IOP? Le processeur est-il chargé ? Si vous avez beaucoup d'IOP en lecture, vous n'avez probablement pas assez de pool de mémoire tampon InnoDB. Si le processeur est chargé, vos requêtes effectuent probablement des analyses complètes de la table au lieu d'utiliser les index appropriés.
    • Configuration disque/RAID/LVM. Dans certaines configurations spécifiques, l'entrelacement LVM peut vous apporter l'avantage d'égaliser la charge du disque (pas de RAID matériel, plusieurs LUN connectés)
    • Planificateur d'E/S : lorsque vous avez un bon contrôleur RAID matériel, le noop est probablement le meilleur. RedHat a fait quelques tests et ils ont dit que pour Oracle (et d'autres bases de données) CFQ est le meilleur choix. Vous devez exécuter des tests (comme tpc-c ou tpc-e) et choisir ce qui convient le mieux à votre matériel.
    • Bon système de fichiers - ext3 ne fonctionne pas bien dans les charges de travail spécifiques à la base de données. Mieux vaut XFS ou OCFS2. Vous avez encore besoin de points de repère.
    • Regardez, si votre système utilise swap. L'utilisation de swap dégrade les performances de mysql.

    Vérifiez si votre instance MySQL/InnoDB est correctement réglée :

    • taille du pool de mémoire tampon - cache les pages de données en mémoire
    • innodb_flush_method=O_DIRECT - évite la double mise en mémoire tampon des E/S
    • augmenter la taille du fichier journal InnoDB - pour une charge de travail intensive en écriture, cela pourrait améliorer les performances. Mais rappelez-vous : une plus grande taille de fichier journal signifie une récupération après incident plus longue. Parfois en heures.
    • innodb_flush_log_at_trx_commit=0 ou 2 - Si vous n'êtes pas préoccupé par ACID et que vous pouvez perdre des transactions pendant la dernière seconde ou deux.
    • key_buffer_size - très important pour MyISAM, mais il est utilisé pour les tables temporaires du disque.
    • Surveillez votre STATUT INNODB
    • Quelles requêtes prennent le plus de temps sur votre serveur ?
    • Des tables temporaires sont-elles créées, en particulier de grandes tables temporaires ?
    • Votre application utilise-t-elle des transactions ? Lorsque vous exécutez des requêtes avec autocommit=1 (par défaut pour MySQL), chaque requête d'insertion/mise à jour commence une nouvelle transaction, ce qui entraîne une surcharge. Si cela est possible, il vaut mieux désactiver l'autocommit (dans le pilote python MySQL, l'autocommit est désactivé par défaut) et exécuter manuellement la validation une fois toutes les modifications effectuées.
    • Votre application effectue-t-elle des séries d'insertions dans la même table en boucle ? La commande Load data infile est beaucoup plus rapide pour les séries d'insertions.
    • Rappelez-vous : select count(*) from table est beaucoup plus lent pour innodb que pour myisam.
    • Quels types de requêtes INSERT/UPDATE prennent le plus de temps sur le serveur ? Comment les optimiser ?
    • Vérifiez si votre base de données a les bons index et ajoutez-les, si nécessaire.

    Dans notre environnement, nous avons eu une situation, ce type de requêtes de mise à jour était lent. Le temps estimé pour terminer le travail par lots était de 2 jours. Après avoir analysé le journal de slowquery, nous constatons que ce type de requête de mise à jour a besoin de 4 secondes pour se terminer. La requête ressemblait à ceci : update table1 set field1=value1 where table1.field2=xx table2.field3=yy and table2.field4=zz . Après avoir converti la requête de mise à jour en requête de sélection et exécuté expliquer sur cette requête de sélection, nous constatons que ce type de requête n'utilise pas d'index. Après avoir créé l'index approprié, nous avons réduit le temps d'exécution des requêtes de mise à jour à quelques millisecondes et l'ensemble du travail s'est terminé en moins de deux heures.


    Script d'évaluation SQL Server

    Je peux enfin rayer un élément de ma liste de projets en veilleuse. En février dernier, j'étais chez un client évaluant l'intégralité de l'empreinte de sa base de données SQL Server pour la migration vers le cloud. N'ayant pas fait ce genre de travail auparavant, j'ai rapidement bricolé des scripts qui me donnaient un aperçu de base de chaque base de données. J'ai ensuite combiné les résultats dans un tableur.

    N'aurait-il pas été agréable d'obtenir ces informations dans un seul script ? Vous pouvez utiliser ce script non seulement pour mieux comprendre l'utilisation des ressources dans votre instance SQL Server, mais également pour déterminer les ressources nécessaires à la migration vers un autre matériel ou le cloud.

    J'ai déjà produit un long script de bilan de santé (ici) qui donne pas mal d'informations. Cependant, il n'a pas pris de mesures de l'utilisation du processeur et des valeurs IOPS sur une période de temps. Mon nouveau script d'évaluation SQL Server rassemble non seulement un ensemble d'informations plus petit mais plus critique que mon script de contrôle de santé, mais prend également des instantanés horaires de l'utilisation du processeur et des valeurs IOPS, y compris les valeurs individuelles de lecture et d'écriture au cours d'une journée. Exécutez ce script pendant une journée de travail normale ou pendant une période de traitement de fin de mois, s'il existe.

    Voici le script dans sa dernière forme. Pour ajuster les snapshots CPU et IOPS pris et l'intervalle entre les snapshots, modifiez ces deux lignes :

    while (@cntr) < 24 – Il s'agit du nombre d'instantanés à prendre avant que les résultats ne soient produits.

    waitfor delay 󈧅:00:00′ – Il s'agit du délai entre les instantanés. Elle est actuellement fixée à une heure.