add second field of two files on different servers

saurabhmehan · 09-01-2010, 07:12 AM

Hi

I have one file placed at one server whose sample as follows:
57272 28
56767 0
57575 4
58888 6
53030 26
54242 0
56060 0

And another file at different server whose sample data as follows:
57272 22
56767 0
57575 1
58888 2
53030 13
54242 0
56060 1

Now i want to run a script from one of the server which adds second field from both files which are not on same server and gives the output as follows:
57272 50
56767 0
57575 5
58888 8
53030 39
54242 0
56060 1

druuna · 09-01-2010, 07:27 AM

Hi,

There's not enough info to help.....

Do these 2 files always have the same first columns (same number in the same location) or is the following also possible???

File one:
57272 28
57575 4
58888 6
53030 26

File two:
57272 22
57575 1
58888 2
53030 13
56060 1

Please elaborate a bit.

Chirel · 09-01-2010, 08:37 AM

Hi,

Let's say you can connect on both server using ssh and using public/private keys.
Let's say also that :

File 1 is /app1/f-one on server1.foo.org
File 2 is /app2/f-two on server2.foo.org

Then first you could create a script doit.sh :

Code:

#!/bin/bash
declare -a akey
declare -a aval

idx=0
while read k v; do
    [ -z ${aval[$k]} ] && akey[$((idx++))]=$k
    aval[$k]=$((${aval[$k]:-0}+$v))
done

i=0
while [ $i -lt $idx ]; do
    echo "${akey[$i]} ${aval[${akey[$((i++))]}]}"
done

It's obvious that you will not forget to chmod +x doit.sh, then all you have to do is :

Code:

# { ssh server1.foo.org "cat /app1/f-one"; ssh server2.foo.org "cat /app2/f-two"; } | ./doit.sh

druuna · 09-01-2010, 08:41 AM

Hi,

@Chirel if the two files are always the same (saurabhmehan still hasn't answered that one), it can be done a bit simpler:

Code:

#!/bin/bash

# machines to fetch file from
serverOne="1.0.0.1"
serverTwo="1.0.0.2"

# user to use on remote machines
userOne="user1"
userTwo="user2"

# fetch the files (passwordless scp is assumed)
scp $userOne@$serverOne:path/to/fileone /tmp/tmp.$serverOne
scp $userTwo@$serverTwo:path/to/filetwo /tmp/tmp.$serverTwo

# create new file
diff -y /tmp/tmp.$serverOne /tmp/tmp.$serverTwo | \
awk '{ print $1, $2+$5 }' > /path/to/outfile

exit 0

schneidz · 09-01-2010, 08:49 AM

assuming nfs or sshfs:

Code:

[schneidz@hyper temp]$ paste /mnt/sshfs-server1/path/to/input1 /mnt/sshfs-server2/path/to/input2 | awk '{a = $2 + $4}  {print $1 " " a}'
57272 50
56767 0
57575 5
58888 8
53030 39
54242 0
56060 1

Chirel · 09-01-2010, 08:52 AM

Hi,

@druuna, you are right

But as i did not know i consider the "hard" way

I must admit i love the awk + diff way

druuna · 09-01-2010, 09:00 AM

Hi,

@Chirel: One tends to use the tools one knows. The longer you work with unix/linux the more tools you come across (and sometimes forget again...).

Your solution has one advantage over mine: It also works when the infiles are not the same.....
A simple sort might take care of that problem in my script, but that might not be what the OP wants.

saurabhmehan · 09-03-2010, 12:28 AM

Hi,

@drunna thanks for your post, i want to tell you the file structure is same but count will be different.

But i want to do it without copying the file from both servers.

Quote:

Originally Posted by druuna

Hi,

@Chirel: One tends to use the tools one knows. The longer you work with unix/linux the more tools you come across (and sometimes forget again...).

Your solution has one advantage over mine: It also works when the infiles are not the same.....
A simple sort might take care of that problem in my script, but that might not be what the OP wants.

saurabhmehan · 09-03-2010, 12:37 AM

Hi,

@schneidz: Please provide me the sample command with the dummy inputs as i am trying following command and its not working:

Code:

paste /mnt/sshfs-10.0.0.1/home/smehan/applog/cp_report.txt /mnt/sshfs-10.0.0.2/home/smehan/applog/cp_report.txt | awk '{a = $2 + $4}  {print $1 " " a}'

Quote:

Originally Posted by schneidz

assuming nfs or sshfs:

Code:

[schneidz@hyper temp]$ paste /mnt/sshfs-server1/path/to/input1 /mnt/sshfs-server2/path/to/input2 | awk '{a = $2 + $4}  {print $1 " " a}'
57272 50
56767 0
57575 5
58888 8
53030 39
54242 0
56060 1

saurabhmehan · 09-03-2010, 12:50 AM

Hi,

@drunna: Yes the first column will be same in both files of different servers that is same number in same location and below strategy which u mention according to my case not possible.

Quote:

Originally Posted by druuna

Hi,

There's not enough info to help.....

Do these 2 files always have the same first columns (same number in the same location) or is the following also possible???

File one:
57272 28
57575 4
58888 6
53030 26

File two:
57272 22
57575 1
58888 2
53030 13
56060 1

Please elaborate a bit.

ghostdog74 · 09-03-2010, 01:14 AM

assuming you already set up passwordless ssh.

Code:

ssh root@localhost "cat /path/file1" | awk 'BEGIN{
  while( ( getline line<"/path/file2") > 0 ){
   m=split(line,a," ")
   array[a[1]]=a[2]
  }
}
{
   array[$1]+=$2
}
END{
  for(i in array) print i ,array[i]
}'

druuna · 09-03-2010, 01:49 AM

Hi,

Quote:

Originally Posted by saurabhmehan

But i want to do it without copying the file from both servers.

Have a look at Chirel's or ghostdog74's solutions.

saurabhmehan · 09-03-2010, 01:58 AM

Hi Guys,

@druuna,@Chirel,@ghostdog74 thanks for providing me hints to solve my problem.
I have done with it with one liner.Check the below code:

Code:

ssh hostname "cat serverfile" | awk 'NR==FNR{_[$1]=$2; next} {print $1, _[$1]+$2}' localfile -

Quote:

Originally Posted by druuna

Hi,

Have a look at Chirel's or ghostdog74's solutions.

schneidz · 09-03-2010, 08:53 AM

Quote:

Originally Posted by saurabhmehan

Hi,

@schneidz: Please provide me the sample command with the dummy inputs as i am trying following command and its not working:

Code:

paste /mnt/sshfs-10.0.0.1/home/smehan/applog/cp_report.txt /mnt/sshfs-10.0.0.2/home/smehan/applog/cp_report.txt | awk '{a = $2 + $4}  {print $1 " " a}'

my first post does have the sample command with the dummy inputs. if yours is not working i suspect your server isnt mounted in that directory (notice that my original post said: assuming nfs or sshfs). you can test that by posting the result of:
ls /mnt/sshfs-10.0.0.1/home/smehan/applog/cp_report.txt /mnt/sshfs-10.0.0.2/home/smehan/applog/cp_report.txt