home *** CD-ROM | disk | FTP | other *** search
/ NetNews Usenet Archive 1993 #3 / NN_1993_3.iso / spool / comp / database / ingres / 2347 < prev    next >
Encoding:
Text File  |  1993-01-23  |  6.7 KB  |  128 lines
  1. Newsgroups: comp.databases.ingres
  2. Path: sparky!uunet!elroy.jpl.nasa.gov!sdd.hp.com!saimiri.primate.wisc.edu!aplcen.apl.jhu.edu!news
  3. From: mae@aplpy.jhuapl.edu (Mary Anne Espenshade)
  4. Subject: Copying data into INGRES
  5. Message-ID: <1993Jan22.220936.27618@aplcen.apl.jhu.edu>
  6. Sender: news@aplcen.apl.jhu.edu (USENET News System)
  7. Organization: Johns Hopkins University Applied Physics Lab
  8. Date: Fri, 22 Jan 93 22:09:36 GMT
  9. Lines: 117
  10.  
  11. I have an interesting problem to deal with involving getting data into
  12. an INGRES database.  Explaining it may take a while though, and
  13. requires a good bit of UNIX background as well as INGRES copy knowledge,
  14. everyone else can bail out now...
  15.  
  16. I administer (and am writing a Windows 4GL query interface to) a
  17. database that is updated quarterly from an outside source.  Each update
  18. tape is a complete replacement for all the data.  Each file on the tape
  19. is a single table.  I'm running on an HP UNIX system, but the provider
  20. of the tape is apparently on VMS, because the only 9 track tape formats
  21. we can get are VMS formated or unlabeled.  I get the unlabeled tape and
  22. have a UNIX shell script to read it in using dd.  Since there are nearly
  23. 300 tables in this database, that means nearly 300 unnamed files on this
  24. tape, so the script has to keep track of where it is so I can identify
  25. which file will go into which table.
  26.  
  27. The data used to be in fixed length fields with no delimiters between
  28. them and a carriage return/linefeed pair at the end of each record.  My
  29. script that would read a file from the tape, pass it through a tr
  30. command that would replace the two line delimiters with a single newline
  31. and name the file appropriately so I could then read it with an SQL
  32. copy script.  I had nearly 10,000 lines of copy scripts, with the fixed
  33. length format for each field of each table and matching
  34. "with null(' ...many spaces... ')" clauses where needed.  I could load
  35. the entire database with a makefile in about 3 hours.
  36.  
  37. Not too surprisingly, the organization making these tapes decided to
  38. switch to a variable length format.  Lots of empty 240 character
  39. comment fields padded with blanks wastes a lot of tape space.  My
  40. problem is the particular format they came up with. Now, each record
  41. starts with a four digit record length, not followed by a delimiter,
  42. and then the fields of the record, separated by commas, with each
  43. character field within double quotes.  There is still a carriage
  44. return/linefeed pair at the end of each record.
  45.  
  46. I saw two ways to tackle this beast, rewrite my 10,000 lines of copy
  47. commands to decode the possible multiple delimiters around each field
  48. individually or rewrite my tape reading script to process the files into
  49. a format copy could deal with more easily.  I didn't want to face the
  50. magnitude of the changes to copy and thought I saw a way a few passes
  51. through sed and/or tr could fix the files.  Comma was a really
  52. unfortunate choice for the field delimiter, as there are many commas in
  53. the text fields as well, and how they got double quotes instead of
  54. single out of an SQL-based system (not Ingres, I think it's maintained
  55. on either Oracle or Sybase) at the other end I have no idea.  I verified
  56. that there were no tabs in the data and decided to make that the field
  57. delimiter.  A few global changes converted the copy scripts to all have
  58. the format:    (dummy = d4,    /* to dump the 4 digit record length */
  59.         .... = c(0)tab,
  60.         .... = c(0)tab with null(''),
  61.         .... = c(0)nl)
  62.  
  63. Sample record as input (each file has a different number and order of
  64. fields of course, except that the first and last fields are always
  65. character strings):
  66. 1234"key field","text",10,20,"more text","still, more, text"(CR)(LF)
  67. What I wanted:
  68. 1234key field(tab)text(tab)10(tab)20(tab)more text(tab)still, more, text(NL)
  69.  
  70. I ended up with a shell script that makes 10 passes over each file:
  71. 1) the original "tr -s" to replace the CR/LF pairs with NL
  72. 1234"key field","text",10,20,"more text","still, more, text"(NL)
  73. 2) remove the first double quote
  74. 1234key field","text",10,20,"more text","still, more, text"(NL)
  75. 3) remove the last double quote
  76. 1234key field","text",10,20,"more text","still, more, text(NL)
  77. 4) replace any ,", with ^A", (the ^A is a placeholder for a real text
  78.    comma, some of the continued character fields can have data ending
  79.    with a comma.  I missed this the first time around and had to redo
  80.    the script.  Apparently the source DBMS has a maximum character
  81.    field length of 240 characters, so there are tables with fields
  82.    description1, description2 and description3, each varchar(240).)
  83. 1234key field","text",10,20,"more text","still, more, text(NL)
  84. 5) replace any "," with a tab
  85. 1234key field(tab)text",10,20,"more text(tab)still, more, text(NL)
  86. 6) replace any ," with a tab
  87. 1234key field(tab)text",10,20(tab)more text(tab)still, more, text(NL)
  88. 7) replace any ", with a tab
  89. 1234key field(tab)text(tab)10,20(tab)more text(tab)still, more, text(NL)
  90. 8) replace any ,SP with a ^ASP (this is the problem assumption)
  91. 1234key field(tab)text(tab)10,20(tab)more text(tab)still^A more^A text(NL)
  92. 9) replace any remaining commas with tabs
  93. 1234key field(tab)text(tab)10(tab)20(tab)more text(tab)still^A more^A text(NL)
  94. 10) replace the ^A place-holders with commas.
  95. 1234key field(tab)text(tab)10(tab)20(tab)more text(tab)still, more, text(NL)
  96.  
  97. Needless to say, on the one huge table with 96,000+ records, this takes
  98. a while.
  99.  
  100. As noted on step #8, I had made a hopeful assumption that turned out to
  101. be wrong.  I thought all the commas in the text fields would be in
  102. sentence-structured text, with a space after each comma.  This was true
  103. for the comment and description type fields (with the exception that
  104. added step #4) but was not true for several shorter character fields,
  105. where lists of values had been crammed in with commas between them but
  106. no spaces.  The really irksome thing is that this data has major
  107. consistancy problems, so a field might have a value expressed as 'a/b/c'
  108. most of the time, but it sometimes shows up as 'a,b,c' or 'a-b-c'.
  109.  
  110. The first few files I loaded didn't have too many problems, and since
  111. copy tells me which lines it can't read and I have them coded
  112. "with on_error=continue, rollback=disabled", I thought I could go back
  113. and fix the offending lines individually and reload them.  Then I hit
  114. the file with 11765 records, 8588 of which failed.  I need another
  115. way to process this tape and I don't see what it is.  Is there another
  116. way to use copy that I'm not seeing, short of making extra dummy fields
  117. to get rid of all the extra delimiters?  Or do I really have to edit
  118. 9876 lines of SQL and change 80% of them to individual formats.
  119.  
  120. To anyone who actually made it through this description, sorry for
  121. being so long winded. :-)  Thanks for any help/ideas.
  122.  
  123. e-mail replies to:
  124. --
  125. Mary Anne Espenshade
  126. ARPA: mae@aplpy.jhuapl.edu     or    mae@aplexus.jhuapl.edu
  127. UUCP: ...!allegra!mimsy!aplcen!aplpy!mae
  128.