Création d'une table externe

[pbatty1]

Bonjour,
En faisant des recherches sur le web sur le moyen d'arriver à faire des insertions de plusieurs lignes (BULK INSERT), j'ai trouvé que la table externe est un moyen pour y arriver.
J'ai donc créé un fichier pour stocker mes données. mon problème est que je n'arrive pas à créer ma table externe

Code :

CREATE TABLE temp1 EXTERNAL FILE 'C:\Users\avelompa\Documents\datamining\DataAnalysis\Monitoring\bin\Debug\dt.ext'(field1 TIMESTAMP,field2 VARCHAR(10), field3 float, field4 VARCHAR(250));

Je travaille avec C# et flameRobbin comme outil d'administration de firebird.
Quand je lance ma requête avec flameRobin celui ci plante. Et avec C#, il ne ressort plus du commad.ExecuteNonQuery().
Qu'est ce qui ne fonctionne pas dans requête? Y t-il un moyen de faire une mutli-insertion pour enregistrer des données de 500000 lignes?

Merci pour votre aide

[Ph. B.]

Bonjour,

1. Dans le fichier firebird.conf, la propriété ExternalFileAccess a pour valeur par défaut None : Aucune table externe autorisée.
   Pour autoriser toute table externe (cependant, attention à la sécurité !)
   
   Code :

   ExternalFileAccess = Full

   ou pour autoriser les tables d'un (ou plusieurs) répertoire(s) donné(s)
   
   Code :

   ExternalFileAccess = Restrict C:\MonRepertoire;C:\Temp

2. Créer un fichier avec des enregistrements de longueur fixe
3. Créer la réquête de création de la table
   
   Code :

   CREATE TABLE temp1 EXTERNAL FILE 'C:\MonRepertoire\dt.ext'(field1 CHAR(19), field2 CHAR(10), field3 CHAR(8), field4 CHAR(250), fieldCRLF CHAR(2));

   La dernière colonne stockera les caractères de retour à la ligne CR/LF
4. Créer la requête d'insertion
   
   Code :

   1
   2
   3
   4
   5
   6
   7
   INSERT INTO TABLE_REELLE 
   SELECT
     CAST(field1 AS TIMESTAMP),
     CAST(field2 AS VARCHAR(10)),
     CAST(field3 AS FLOAT),
     CAST(field4 AS VARCHAR(250))
   FROM temp1

--
Philippe.

[pbatty1]

Merci pour la reponse, mais est ce qu'il est possible d'avoir une adresse relative pour la configuration du externalaccessfile? En effet il se peut que je distribue le logiciel.

[makowski]

oui et non
si tu définis par exemple :

Code :

ExternalFileAccess = Restrict C:\MonRepertoire

alors

Code :

CREATE TABLE temp1 EXTERNAL FILE 'dt.ext' ...(

ira chercher dans C:\MonRepertoire

[Ph. B.]

Envoyé par pbatty1

Merci pour la reponse, mais est ce qu'il est possible d'avoir une adresse relative pour la configuration du externalaccessfile? En effet il se peut que je distribue le logiciel.

Une adresse relative par rapport à quoi ? au répertoire d'installation de Firebird ? au répertoire d'installation de votre application ?
Le moteur Firebird ne peut rien spéculer sur un chemin relatif. Il attend que ce paramètre contienne un (ou des) chemin(s) absolu(s).
cf. la FAQ de Firebird

Quant à l'emplacement du fichier firebird.conf, il est à la racine du répertoire d'installation de Firebird soit pour Firebird 2.5x sous windows XP le chemin par défaut suivant : C:\Program Files\Firebird\Firebird_2_5.
Pour les détails, je te renvoie à la doc anglophone de firebird en suivant ce lien.
--
Philippe.

[pbatty1]

Ok, il n'est pas évident de mettre l'adresse relative à partir de l'application. Je pense faire un script qui modifie le firebird.config. Mais j'ai un autre souci au niveau de mon fichier externe. J'avais ecrit dans mon fichier les données comme suit

2007/03/20 12:30:20 var1 8912.55 hist
2007/03/20 13:30:20 var1 89.23 hist

et avec la definition de la table temp1

Code :

string.Format(@"CREATE TABLE temp1 EXTERNAL FILE '{0}\dt.ext'(field1 CHAR(19),field2 CHAR(10), field3 CHAR(8), field4 CHAR(250),fieldCRLF char(2));", filepath)

j'obtiens des résultats qui ne correspondent psa au format requis car la table découpe selon le nombre de caractères affecté or j'ai des informations avec une taille variable pour field2, le field3 et le field 4
N'y a t-il pas un moyen de définir la ligne d'enregistrement et les données de chaque champ?

[Ph. B.]

Le fichier doit avoir des lignes de longueur fixe pour pouvoir le traiter comme une table externe.

Chaque colonne doit être typée CHAR(n). Il suffit de la "caster" ensuite au bon format.

Pour ton exemple en longueur fixe :

2007/03/20 12:30:20 var1 8912.55 hist
2007/03/20 13:30:20 var1 89.23 hist

On pourrait écrire :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE TABLE temp1 EXTERNAL FILE 'C:\Temp\dt.ext'
(
field1 CHAR(19),
fieldsep1 CHAR(1),
field2 CHAR(4),
fieldsep2 CHAR(1),
field3 CHAR(7),
fieldsep3 CHAR(1),
field4 CHAR(4),
fieldCRLF char(2)
);
--
SELECT
  CAST(field1 AS TIMESTAMP) AS F1,
  CAST(field2 AS VARCHAR(4)) AS F2,
  CAST(field3 AS FLOAT) AS F3,
  CAST(field4 AS VARCHAR(4)) AS F4
FROM temp1

--
Philippe.

[pbatty1]

Si on prend la requête

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE TABLE temp1 EXTERNAL FILE 'C:\Temp\dt.ext'
(
field1 CHAR(19),
fieldsep1 CHAR(1),
field2 CHAR(4),
fieldsep2 CHAR(1),
field3 CHAR(7),
fieldsep3 CHAR(1),
field4 CHAR(4),
fieldCRLF char(2)
);
--
SELECT
  CAST(field1 AS TIMESTAMP) AS F1,
  CAST(field2 AS VARCHAR(4)) AS F2,
  CAST(field3 AS FLOAT) AS F3,
  CAST(field4 AS VARCHAR(4)) AS F4
FROM temp1

elle marchera pour la 1ere ligne mais pas pour 2eme ligne. La 2eme ligne donnera

field1=2007/03/20 13:30:20
fieldsep1=
field2=var1
fieldsep2=
field3=89.23 h
fieldsep3=i
field4=st

Dans mon fichier il se trouve que les données qui iront dans field3 et field4 seront de taille variable.
Est ce qu-il est donc impossible pour moi d'utiliser le fichier externe pour mes données?

[makowski]

Envoyé par pbatty1

Dans mon fichier il se trouve que les données qui iront dans field3 et field4 seront de taille variable.
Est ce qu-il est donc impossible pour moi d'utiliser le fichier externe pour mes données?

oui
les tables externes utilisent des fichiers de taille fixe

soit tu transformes tes fichiers, sois tu utilises une autre solution

[pbatty1]

Pour l'instant je transforme les données. mais y at il un autre moyen aussi rapide pour faire des grosses insertions comme

Code :

INSERT INTO DATA (field1,field2,field3,field4) VALUES ((value1,value2,value3,value4),(value5,value5,value6,value8),...)

Je sais que ce type de requête ne marche pas sur Firebird mais existe t-il le même principe avec un autre syntaxe. J'ai déjà essayé le type d'insertion suivant

Code :

INSERT INTO DATA (field1, field2,field3,field4) SELECT(value1,value2,value3,value4) FROM rdb$database UNION SELECT(value5,value5,value6,value8) FROM rdb$database ...)

Mais l'union est limitée à 125

[Ph. B.]

Pour le 1° cas, une liste d'insert englobé dans une transaction

Pour le 2° cas, peut-être en utilisent une vue ou un CTE...
--
Philippe.

[makowski]

Envoyé par pbatty1

Pour l'instant je transforme les données. mais y at il un autre moyen

dbfile ?
http://www.ibphoenix.com/products/software/dbfile

[pbatty1]

Pour le 1° cas, une liste d'insert englobé dans une transaction

J'ai utilisé la méthode suivante pour insérer mes données

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
sql ="INSERT INTO DATA (field1, field2,field3,field4)
VALUES (?,?,?,?)"
command.CommandText=sql;
transac = con.BeginTransaction(IsolationLevel.RepeatableRead);
command.Transaction = transac;
FOR(int i=0;i<LIMIT;i++)
{
command.Parameters.AddWithValues("field1",value1);
command.Parameters.AddWithValues("field2",value2);
command.Parameters.AddWithValues("field3",value3);
command.Parameters.AddWithValues("field4",value4);
commad.ExecuteNonQuery();
command.Paramters.Clear();
}
transac.Commit();

Mais le résultat de l'insertion est lent

Pour le 2° cas, peut-être en utilisent une vue ou un CTE...

Je vais me renseigner sur cette piste
Pour dbfile je ferai un test sur son efficacité, car si il peut parser le fichier externe ca pourrait m'être utile pour d'autres insertion