Pgloader: pgLoader seems to be unable to handle detached memos
[ ] pgloader --version
pgloader version "3.6.2~devel" compiled with SBCL 1.3.14.debian[ ] did you test a fresh compile from the source tree?
Used a docker image. Supposedly latest.
[ ] did you search for other similar issues?
Nothing similar came in search.
[ ] how can I reproduce the bug?
LOAD DBF
FROM DNORDOC.DBF
WITH ENCODING CP866
INTO pgsql://user@postgres/db?fias.dnordoc
WITH create table
SET search_path to 'fias'
BEFORE LOAD DO
$$ CREATE SCHEMA IF NOT EXISTS 'fias'; $$;
- [ ] pgloader output you obtain
2019-03-26T17:27:36.076000Z LOG Migrating from #<DBF-CONNECTION dbf:///var/www/src/../data/DNORDOC.DBF {10071E64C3}>
2019-03-26T17:27:36.078000Z LOG Migrating into #<PGSQL-CONNECTION pgsql://user@postgres:5432/db {10071EAE33}>
2019-03-26T17:27:36.081000Z ERROR dbf: Failed to open dbf file #P"/var/www/src/../data/DNORDOC.DBF": Can't handle this file
2019-03-26T17:27:36.082000Z LOG report summary reset
table name errors read imported bytes total time read write
----------------- --------- --------- --------- --------- -------------- --------- ---------
fetch 0 0 0 0.000s
----------------- --------- --------- --------- --------- -------------- --------- ---------
----------------- --------- --------- --------- --------- -------------- --------- ---------
- [ ] data that is being loaded, if relevant
This DBASE file: DNORDOC.zip (Source is federal informational address system, archive is over 6 GB, 60GB+ unpacked, don't try to download it.)
- [ ] How the data is different from what you expected, if relevant
It's missing.
AnrDaemon
All 18 comments
pgloader uses the https://github.com/dimitri/cl-db3 project to read a DBF file, which doesn't seem to support memo files (03H file type is the only supported here). Can you find information on DBF memos and maybe provide a smaller DBF/DBT file using the memo feature so that I can complete the cl-dbf implementation and then allow pgloader to work on your input?
dimitri
on 26 Mar 2019
The example database file I provided is fairly small (14k DBF, approx 120 records).
I only warned against downloading origin of it unless absolutely necessary.
Regarding information, I sadly have to pass, the format is foreign to me, and I only stumbled upon it due to work requirements.
AnrDaemon
on 26 Mar 2019
Speaking of format description, http://www.dbase.com/KnowledgeBase/int/db7_file_fmt.htm
AnrDaemon
on 5 Apr 2019
Have a look at https://github.com/dimitri/cl-db3/commit/3f5fcff3717e2c5e78a0dcbdd807936f1f3c98cc where I begin to hack support for Dbase III+ memo files.
Current output doesn't seem to promising. If you have some time to dive into the file format details and compare data output here with other tooling output, that'd be helpful:
CL-USER> (let ((input "/Users/dim/dev/temp/pgloader-issues/926/DNORDOC.DBF"))
(with-open-file (s input
:direction :input :element-type '(unsigned-byte 8))
(let ((db3 (make-instance 'db3:db3 :filename input)))
(db3:load-header db3 s)
(loop :repeat (db3:record-count db3)
:collect (db3:load-record db3 s)))))
load-memo-header: block-size 512, next block 106
(#("8755876c-3ba8-4454-a00b-f780fb646773" "¥à¥¢¥¤¥ ¢ à §àï¤ ®à¨¥â¨à®¢"
" " " " " 0.00000"
" ")
#("454de0f6-dd9b-425f-997f-5b7f5f480335" "¢¥¤¥ ®à¨¥â¨à" " "
" " " 0.00000"
" ")
#("10332651-bc57-4c40-8c5f-75ecd53fa4be" " ᯮà殮¨¥ ü 3-à ®â 10.01.2006"
" " " " " 0.00000"
" ")
Thanks for your efforts!
I have very basic understanding of LISP, sorry. Can't be of much help.
AnrDaemon
on 14 Apr 2019
Maybe you could compare the following values extracted from your files and what your current DBF application gives you for the data? The numbers are a byte at a time printed in decimal, the strings are an attempt to decode those numbers as ascii codes...
#(143 165 224 165 162 165 164 165 173 32 162 32 224 160 167 224 239
164 32 174 224 168 165 173 226 168 224 174 162)
¥à¥¢¥¤¥ ¢ à §àï¤ ®à¨¥â¨à®¢
#(130 162 165 164 165 173 32 174 224 168 165 173 226 168 224)
¢¥¤¥ ®à¨¥â¨à
#(144 160 225 175 174 224 239 166 165 173 168 165 32 252 32 51 45 224
32 174 226 32 49 48 46 48 49 46 50 48 48 54)
ᯮà殮¨¥ ü 3-à ®â 10.01.2006
#(141 160 32 174 225 173 174 162 160 173 168 169 32 225 162 165 164
165 173 168 169 32 175 224 165 164 174 225 226 160 162 171 165 173
173 235 229 32 140 227 173 168 230 168 175 160 171 236 173 235 172
32 174 161 224 160 167 174 162 160 173 168 165 172)
®á®¢ ¨© ᢥ¤¥¨© ¯à¥¤®áâ ¢«¥ëå ã¨æ¨¯ «ìë¬ ®¡à §®¢ ¨¥¬
Do iconv -f CP866 -t UTF-8 equivalent on them, they are not in UTF-8 or ISO-8859-1.
The results look sensible, though.
AnrDaemon
on 14 Apr 2019
Oh, so you're using CP866 in those files. Good to know. Here's what I have with that knowledge in my lower-layer test case now. And with a very small attached fix, it seems to make it to PostgreSQL now too.
CL-USER> (let ((input "/Users/dim/dev/temp/pgloader-issues/926/DNORDOC.DBF")
(db3:*external-format* (ccl:make-external-format :character-encoding :IBM866)))
(with-open-file (s input
:direction :input :element-type '(unsigned-byte 8))
(let ((db3 (make-instance 'db3:db3 :filename input)))
(db3:load-header db3 s)
(loop :repeat 4
:do (db3:load-record db3 s)
:do (terpri)))))
load-memo-header: block-size 512, next block 106
#(143 165 224 165 162 165 164 165 173 32 162 32 224 160 167 224 239
164 32 174 224 168 165 173 226 168 224 174 162)
Переведен в разряд ориентиров
#(130 162 165 164 165 173 32 174 224 168 165 173 226 168 224)
Введен ориентир
#(144 160 225 175 174 224 239 166 165 173 168 165 32 252 32 51 45 224
32 174 226 32 49 48 46 48 49 46 50 48 48 54)
Распоряжение № 3-р от 10.01.2006
#(141 160 32 174 225 173 174 162 160 173 168 169 32 225 162 165 164
165 173 168 169 32 175 224 165 164 174 225 226 160 162 171 165 173
173 235 229 32 140 227 173 168 230 168 175 160 171 236 173 235 172
32 174 161 224 160 167 174 162 160 173 168 165 172)
На оснований сведений предоставленных Муниципальным образованием
CL-USER> (pgloader:run-commands "/Users/dim/dev/temp/pgloader-issues/926/DNORDOC.load"
:client-min-messages :warning)
2019-04-14T22:12:06.000853+02:00 LOG pgloader version "3.6.efe70ba"
2019-04-14T22:12:06.026069+02:00 LOG Migrating from #<DBF-CONNECTION dbf:///Users/dim/dev/temp/pgloader-issues/926/DNORDOC.DBF #x302006232A7D>
2019-04-14T22:12:06.026369+02:00 LOG Migrating into #<PGSQL-CONNECTION pgsql://dim@UNIX:5432/pgloader #x302006214FED>
load-memo-header: block-size 512, next block 106
2019-04-14T22:12:06.149805+02:00 LOG report summary reset
table name errors rows bytes total time
----------------------- --------- --------- --------- --------------
fetch 0 0 0.000s
Create Schemas 0 0 0.002s
Create SQL Types 0 0 0.001s
Create tables 0 2 0.029s
----------------------- --------- --------- --------- --------------
public.dnordoc 0 108 19.0 kB 0.038s
----------------------- --------- --------- --------- --------------
COPY Threads Completion 0 4 0.036s
Create Triggers 0 0 0.000s
Install Comments 0 0 0.000s
----------------------- --------- --------- --------- --------------
Total import time ✓ 108 19.0 kB 0.037s
And then in PostgreSQL:
pgloader# \d public.dnordoc
Table "public.dnordoc"
Column │ Type │ Collation │ Nullable │ Default
═══════════╪═════════╪═══════════╪══════════╪═════════
normdocid │ text │ │ │
docname │ text │ │ │
docdate │ date │ │ │
docnum │ text │ │ │
doctype │ numeric │ │ │
docimgid │ text │ │ │
pgloader# table public.dnordoc limit 4;
─[ RECORD 1 ]───────────────────────────────────────────────────────────────
normdocid │ 8755876c-3ba8-4454-a00b-f780fb646773
docname │ Переведен в разряд ориентиров
docdate │ ¤
docnum │
doctype │ 0.00000
docimgid │
═[ RECORD 2 ]═══════════════════════════════════════════════════════════════
normdocid │ 454de0f6-dd9b-425f-997f-5b7f5f480335
docname │ Введен ориентир
docdate │ ¤
docnum │
doctype │ 0.00000
docimgid │
═[ RECORD 3 ]═══════════════════════════════════════════════════════════════
normdocid │ 10332651-bc57-4c40-8c5f-75ecd53fa4be
docname │ Распоряжение № 3-р от 10.01.2006
docdate │ ¤
docnum │
doctype │ 0.00000
docimgid │
═[ RECORD 4 ]═══════════════════════════════════════════════════════════════
normdocid │ 82f3d158-b256-4a60-a339-cfea607ba9ca
docname │ На оснований сведений предоставленных Муниципальным образованием
docdate │ ¤
docnum │
doctype │ 0.00000
docimgid │
If you remember the very beginning of our conversation… ;)
LOAD DBF
FROM DNORDOC.DBF
WITH ENCODING CP866
…
Can we expect a release Docker image sometime soon with these changes?
Thank you in advance.
AnrDaemon
on 15 Apr 2019
I missed the encoding bits, sorry about that... about docker, we have a docker hub over at https://cloud.docker.com/repository/docker/dimitri/pgloader ; and I just noticed that automated builds weren't enabled anymore. I just fixed that, so that we should have docker images back for each commit onward.
dimitri
on 15 Apr 2019
Still not building.
https://travis-ci.org/dimitri/pgloader
AnrDaemon
on 16 Apr 2019
In a local docker build it worked fine, so that should be good in a moment, let's see how dockerhub likes it.
dimitri
on 17 Apr 2019
They did not :(
AnrDaemon
on 17 Apr 2019
The docker hub builds seem fine now: https://hub.docker.com/r/dimitri/pgloader/builds.
dimitri
on 17 Apr 2019
That seems all right. Thank you. And sorry for the noise.
AnrDaemon
on 18 Apr 2019
2019-04-29T14:41:10.127000Z ERROR dbf: Failed to open dbf file #P"/var/www/data/529/DNORDOC.DBF": error opening #P"/var/www/data/529/DNORDOC.dbt": No such file or directory
2019-04-29T14:41:10.128000Z LOG report summary reset
Quite unsurprisingly, the file name was DNORDOC.DBT (all uppercase). Perhaps, it needs an additional nudging option in WITH block? Something like
LOAD DBF
FROM '{{SOURCE_DIR}}/DNORDOC.DBF'
WITH ENCODING CP866, MEMO 'DNORDOC.DBT'
…
Otherwise, worked like a charm.
AnrDaemon
on 29 Apr 2019
It should be good now, with an update from the cl-db3 library before rebuilding again.
dimitri
on 29 Apr 2019
Confirmed fixed.
AnrDaemon
on 6 May 2019
Related issues
phoe
·
4Comments
ReinierNL
·
3Comments
VonSNAKE77
·
4Comments
toobulkeh
·
3Comments
chrisspen
·
8Comments