Обсуждение: data import - duplicates

I have ~350K rows of sample data that has come to me in 64 text files
(CSV) that I'd like to import into a new database. Each file can be
considered its own category and is so named. That is, each file name
will be inserted into a separate categories table. I'd like to relate
each row to its category. However, while the rows are unique in each
file, some rows are found in more than one file.

I also must parse some of the fields into separate columns, but that's
no big deal. But it means that I must do some pre-processing on these
files, in any case.

After some thought, I thought I might brute-force the problem with Perl
by reading each line of each file into an assoc. array unless it's
already in it, in which case I'd append the "key" based on the list of
categories that line is found in (I'd still need to parse outthe keys
later but I'd get rid of the dupes). Each array row would be like so:

'key' => '1,4,etc.', 'text' => 'a line'

Aside from the fact that the array search would become ridiculously
large after just a couple of files, I realised that this is a
non-starter for the simple fact that this data comprises less than 25%
of the eventual total. So refactoring it in this way would be a waste of
time (there will probably be dupes in the files to come).

So, I'd like to instead parse out my columns properly and write each
line (along with its category key) to a new, single file to be copied
into a working table. ONce I've done so, is there an efficient method I
can use to select all duplicates (save for the category key) into a set
from which I could then select into the final table (and insert the keys
into the category join table)?

For example (pk is the PK from the working table and ck is the category
key), my dupes query on the working table would give the following set:

pk  ck
1   1  a  a  a  a
2   3  a  a  a  a
3   3  b  b  b  b
4   7  b  b  b  b
5   4  a  a  a  a

I would then want to insert just the unique rows into the final table
yet add all of the the PKs and CKs to the category join table. After
that was done, I'd delete all of these from the working table and then
move the unique rows that are left to the final table (and insert the
keys into the join table).

I hope that makes sense. I'm not looking for anyone to do my homework
for me; I'm sure I could fix up a tasty function for this (the data is
destined for MySQL, alas, but I'll be damned if I won't use PG for the
heavy lifting). What I'm really looking for is a handy way to grab all
of those dupes.

I haven't tested but this is what I would do (uses arrays, which are
handy when you need them), with the names changed to protect the
innocent:

begin;
-- create a table with some duplicates in one of the columns (y is ck);
wsprague=# select x, x%4 as y into temp fbar from generate_series(1,10) as x(a);
SELECT

wsprague=# select * from fbar;
 x  | y
----+---
  1 | 1
  2 | 2
  3 | 3
  4 | 0
  5 | 1
  6 | 2
  7 | 3
  8 | 0
  9 | 1
 10 | 2
(10 rows)

-- create a table with the pk, an array of the duplicate keys, and the
length of that array
select y, x_list, array_upper(x_list, 1) as x_len into baz from
(select y, array_accum(x) as x_list from fbar group by y ) a ;

wsprague=# select * from baz;
 y |  x_list  | x_len
---+----------+-------
 3 | {3,7}    |     2
 2 | {2,6,10} |     3
 1 | {1,5,9}  |     3
 0 | {4,8}    |     2
(4 rows)

-- delete all rows that don't have ck in the first element of the pk list
wsprague=# delete from fbar where not exists (select 1 from baz where
fbar.x=baz.x_list[1]);DELETE 6
wsprague=# select * from fbar;
 x | y
---+---
 1 | 1
 2 | 2
 3 | 3
 4 | 0
(4 rows)

commit;

I hope that makes sense.  It should be easy to make into a function
(like clean_table(table_name text, pk_name text, dup_key_name text).
I don't know how well it will work with indexes.  You could probably
even write a function to do the entire import inside postgres,
starting with a copy and moving to merging tables and ending with some
consistency checks, and thus benefit from  transaction isolation of
the whole process....

HTH

On Sat, Mar 8, 2008 at 9:42 PM, brian <brian@zijn-digital.com> wrote:
> I have ~350K rows of sample data that has come to me in 64 text files
>  (CSV) that I'd like to import into a new database. Each file can be
>  considered its own category and is so named. That is, each file name
>  will be inserted into a separate categories table. I'd like to relate
>  each row to its category. However, while the rows are unique in each
>  file, some rows are found in more than one file.
>
>  I also must parse some of the fields into separate columns, but that's
>  no big deal. But it means that I must do some pre-processing on these
>  files, in any case.
>
>  After some thought, I thought I might brute-force the problem with Perl
>  by reading each line of each file into an assoc. array unless it's
>  already in it, in which case I'd append the "key" based on the list of
>  categories that line is found in (I'd still need to parse outthe keys
>  later but I'd get rid of the dupes). Each array row would be like so:
>
>  'key' => '1,4,etc.', 'text' => 'a line'
>
>  Aside from the fact that the array search would become ridiculously
>  large after just a couple of files, I realised that this is a
>  non-starter for the simple fact that this data comprises less than 25%
>  of the eventual total. So refactoring it in this way would be a waste of
>  time (there will probably be dupes in the files to come).
>
>  So, I'd like to instead parse out my columns properly and write each
>  line (along with its category key) to a new, single file to be copied
>  into a working table. ONce I've done so, is there an efficient method I
>  can use to select all duplicates (save for the category key) into a set
>  from which I could then select into the final table (and insert the keys
>  into the category join table)?
>
>  For example (pk is the PK from the working table and ck is the category
>  key), my dupes query on the working table would give the following set:
>
>  pk  ck
>  1   1  a  a  a  a
>  2   3  a  a  a  a
>  3   3  b  b  b  b
>  4   7  b  b  b  b
>  5   4  a  a  a  a
>
>  I would then want to insert just the unique rows into the final table
>  yet add all of the the PKs and CKs to the category join table. After
>  that was done, I'd delete all of these from the working table and then
>  move the unique rows that are left to the final table (and insert the
>  keys into the join table).
>
>  I hope that makes sense. I'm not looking for anyone to do my homework
>  for me; I'm sure I could fix up a tasty function for this (the data is
>  destined for MySQL, alas, but I'll be damned if I won't use PG for the
>  heavy lifting). What I'm really looking for is a handy way to grab all
>  of those dupes.
>
>  --
>  Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>  To make changes to your subscription:
>  http://www.postgresql.org/mailpref/pgsql-general
>

Webb Sprague wrote:
> I haven't tested but this is what I would do (uses arrays, which are
> handy when you need them), with the names changed to protect the
> innocent:
>
> begin;
> -- create a table with some duplicates in one of the columns (y is ck);
> wsprague=# select x, x%4 as y into temp fbar from generate_series(1,10) as x(a);
> SELECT
>
> wsprague=# select * from fbar;
>  x  | y
> ----+---
>   1 | 1
>   2 | 2
>   3 | 3
>   4 | 0
>   5 | 1
>   6 | 2
>   7 | 3
>   8 | 0
>   9 | 1
>  10 | 2
> (10 rows)
>
> -- create a table with the pk, an array of the duplicate keys, and the
> length of that array
> select y, x_list, array_upper(x_list, 1) as x_len into baz from
> (select y, array_accum(x) as x_list from fbar group by y ) a ;
>
> wsprague=# select * from baz;
>  y |  x_list  | x_len
> ---+----------+-------
>  3 | {3,7}    |     2
>  2 | {2,6,10} |     3
>  1 | {1,5,9}  |     3
>  0 | {4,8}    |     2
> (4 rows)
>
> -- delete all rows that don't have ck in the first element of the pk list
> wsprague=# delete from fbar where not exists (select 1 from baz where
> fbar.x=baz.x_list[1]);DELETE 6
> wsprague=# select * from fbar;
>  x | y
> ---+---
>  1 | 1
>  2 | 2
>  3 | 3
>  4 | 0
> (4 rows)
>
> commit;
>
> I hope that makes sense.  It should be easy to make into a function
> (like clean_table(table_name text, pk_name text, dup_key_name text).
> I don't know how well it will work with indexes.  You could probably
> even write a function to do the entire import inside postgres,
> starting with a copy and moving to merging tables and ending with some
> consistency checks, and thus benefit from  transaction isolation of
> the whole process....
>


Hey, neat idea. I changed things a little:

test=# SELECT * FROM table1;
  id | ck | content
----+----+---------
   1 |  1 | string1
   2 |  2 | string2
   3 |  2 | string3
   4 |  4 | string1
   5 |  5 | string1
   6 |  1 | string3
   7 |  2 | string1
(7 rows)

test=# SELECT key_list, array_upper(key_list, 1) AS key_num, content
test-# INTO table2 FROM (
test-# SELECT array_accum(ck) AS key_list, content FROM table1 GROUP BY
content
test=# ) a;
SELECT

test=# SELECT * FROM table2;
  key_list  | key_num | content
-----------+---------+---------
  {2,1}     |       2 | string3
  {1,4,5,2} |       4 | string1
  {2}       |       1 | string2
(3 rows)


I had to modify your DELETE suggestion a bit because the table1.id (your
x) is non-unique (although it must be within a given key_list). It'll be
a performance hog though because it's comparing each string.


test-# DELETE FROM table1 WHERE NOT EXISTS (
test-# SELECT 1 FROM table2
test-# WHERE table1.content = table2.content
test-# AND table1.ck = table2.key_list[1]
test-# );
DELETE 4

test=# SELECT * FROM table1;
  id | ck | content
----+----+---------
   1 |  1 | string1
   2 |  2 | string2
   3 |  2 | string3
(3 rows)

 From here I can drop the table1.ck column. But, in order to place the
table2.key_list ints into a join table, I need the PK from table1. I
have the content column with which to fetch that from table1 but that
hardly seems an efficient method. So, I modified your idea for gathering
the dupe CKs a little:

test-# SELECT key_list, array_upper(key_list, 1) AS key_num, content
test-# INTO table2 FROM (
test-# SELECT array_cat(ARRAY[array_accum(id)], ARRAY[array_accum(ck)])
AS key_list, content
test-# FROM table1 GROUP BY content) a;
SELECT

test=# select * from table2;
        key_list        | key_num | content
-----------------------+---------+---------
  {{3,6},{2,1}}         |       2 | string3
  {{1,4,5,7},{1,4,5,2}} |       2 | string1
  {{2},{2}}             |       2 | string2
(3 rows)

Huzzah! IOW, I accumulate both the PKs and ck from table1 for each
string. Then, I can ditch all but the first PK, which should make
transferring these values in to the join table a snap.

# lightbulb comes on ...

Actually, what I'll do is remove the extraneous PKs and use them to
DELETE FROM table1. Much more efficient, that.

Thanks for your ideas. And if anyone else has any suggestion please feel
free to chime in. When I get the rest of the data I'll post back here on
how it went in case google points someone here in the future.

b