Shulou(Shulou.com)06/01 Report--

How to realize the join operation of two tables by shell

One of the problems studied today is: how to implement the join operation of two tables in the Shell script, the two here actually represent two files, but the file is in the form of a list, with a fixed division symbol, that is, it is equivalent to a file of a non-partitioned table in hive.

Of course, the operation of the table is best in the database: for example, there are: table1 table2, we need to associate according to the primary key of column1

Select * from table1 a table2 b where a.column1 = b.column1

Got it. If you need something a little more complex, you can use the join operation to do it.

But what if you happen to have two files? One way is to import the database, which is too slow, so let's do it with the shell command.

Data sample

Suppose the sample data for the table1.txt file is as follows:

DaacdaaedyWcMIV= http://mp.weixin.qq.com/s?__biz=daacdaaedyWcMIV=&mid=200664912&idx=1&sn=c3fbe92847655211faf109c6856f4c0adaacddeMdcedauY= http://mp.weixin.qq.com/s?__biz=daacddeMdcedauY=&mid=200435227&idx=1&sn=a8802c19dacf0b2db6462eaec09c0b19daacddeVdeaudaY= http://mp.weixin.qq.com/s?__biz=daacddeVdeaudaY=&mid=200668153&idx=1&sn=869b00b38caa70de6c32848f396b8aa6daacdduydVWyeMM= http://mp.weixin.qq.com/s?__biz=daacdduydVWyeMM=&mid=200659749&idx=1&sn=6fe9107dd69dd3b5de32bf6e5597a408daacdeMddMMyVWw= http://mp.weixin.qq.com/ For example: http://mp.weixin.qq.com/s?__biz=daacdMcadMMyduV=&mid=200654058&idx=3&sn=a6e57a52b1bd9c38f392825da34b499ddaacdudVdIecyMY= http://mp.weixin.qq.com/s?__biz=daacdudVdIecyMY=&mid=200382730&idx=4&sn=aeed567f2ad4ee595846623f7e2c8fbfdaacduyudeVeWdv= http://mp.weixin.qq.com/s?__biz=daacduyudeVeWdv=&mid=200468183&idx=1&sn=df3d20eef948571d3120b74416b9b010daacdycIdWdyaIV= McadMMyduV = http://mp.weixin.qq.com/s?__biz=daacdMcadMMyduV=&mid=200654058&idx=3&sn=a6e57a52b1bd9c38f392825da34b499ddaacdudVdIecyMY= McdaacdMcadMMyduV: / / mp.weixin.qq.com/s?__biz=daacdyIydayccyV=&mid=200303209&idx=1&sn= 63fbd789967812b16ff71b3667d36095```

The sample data for the table2.txt file is as follows:

MjM5MDQ3MDIyMg==MjM5MDU2OTI4OQ==MjM5MDc5MTk2NQ==MjM5MDg5MDE2Nw==MjM5MDk3NDQ4Mw==MjM5MTA4MTEyNQ==MjM5MTQwMzcwMA==MjM5MjEwMjkyMA==MjM5MjM2ODU5Nw==MjM5MjMxOTYxMw==

The following task is to find out all the records in table1.txt where the primary key exists in table2.txt, and the primary key of the file is the first column.

Shell command awk-F'\ t'{if (ARGIND==1) ccc [$1]} {if (ARGIND > 1 & & ($1 in ccc)) print $0} 'test2.txt test1.txt > test.txt

Command interpretation: awk-F represents a field division symbol

Next comes the command, which must be surrounded by single quotation marks, not double quotation marks.

ARGIND = = 1 means that when the first file is read, the first column is stored in the array ccc

When you read another file, here is the second file, determine whether its first column is in the array and output it.

Thank you for your reading, the above is the content of "how to achieve the join operation of two tables by shell". After the study of this article, I believe you have a deeper understanding of how to achieve the join operation of the two tables by shell. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.