How to check if there are matching records in 2 huge tables











up vote
-1
down vote

favorite












I have 2 tables, with a common column in both. I wanted to check if there are matching records between 2 tables.



I tried writing a query with inner join in hive, but its taking long time. Any short cut for this?










share|improve this question
























  • We need more details in order to really help. Otherwise we can only guess. It would help, if you give us example files (with a few records in each file and some matches).
    – sudodus
    Nov 28 at 5:22










  • Each table has more then 30 columns. The common column is an id with string datatype.
    – Vandhana
    Nov 28 at 5:26










  • Is the common column always the same, for example column #1? And are the files unix (or linux) text files, or are they formatted some other way, for example in Windows?
    – sudodus
    Nov 28 at 5:28












  • yeah its always the same... the files are in unix only.
    – Vandhana
    Nov 28 at 5:45






  • 2




    In general, slow joins in SQL databases are nearly always caused by insufficient indexing. I'm not familiar with Hive, but it looks like they've replaced indexing with something else, so you must use whatever they replaced it with.
    – dirkt
    Nov 28 at 7:56















up vote
-1
down vote

favorite












I have 2 tables, with a common column in both. I wanted to check if there are matching records between 2 tables.



I tried writing a query with inner join in hive, but its taking long time. Any short cut for this?










share|improve this question
























  • We need more details in order to really help. Otherwise we can only guess. It would help, if you give us example files (with a few records in each file and some matches).
    – sudodus
    Nov 28 at 5:22










  • Each table has more then 30 columns. The common column is an id with string datatype.
    – Vandhana
    Nov 28 at 5:26










  • Is the common column always the same, for example column #1? And are the files unix (or linux) text files, or are they formatted some other way, for example in Windows?
    – sudodus
    Nov 28 at 5:28












  • yeah its always the same... the files are in unix only.
    – Vandhana
    Nov 28 at 5:45






  • 2




    In general, slow joins in SQL databases are nearly always caused by insufficient indexing. I'm not familiar with Hive, but it looks like they've replaced indexing with something else, so you must use whatever they replaced it with.
    – dirkt
    Nov 28 at 7:56













up vote
-1
down vote

favorite









up vote
-1
down vote

favorite











I have 2 tables, with a common column in both. I wanted to check if there are matching records between 2 tables.



I tried writing a query with inner join in hive, but its taking long time. Any short cut for this?










share|improve this question















I have 2 tables, with a common column in both. I wanted to check if there are matching records between 2 tables.



I tried writing a query with inner join in hive, but its taking long time. Any short cut for this?







join database table apache-hive






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 28 at 5:45

























asked Nov 28 at 5:12









Vandhana

366




366












  • We need more details in order to really help. Otherwise we can only guess. It would help, if you give us example files (with a few records in each file and some matches).
    – sudodus
    Nov 28 at 5:22










  • Each table has more then 30 columns. The common column is an id with string datatype.
    – Vandhana
    Nov 28 at 5:26










  • Is the common column always the same, for example column #1? And are the files unix (or linux) text files, or are they formatted some other way, for example in Windows?
    – sudodus
    Nov 28 at 5:28












  • yeah its always the same... the files are in unix only.
    – Vandhana
    Nov 28 at 5:45






  • 2




    In general, slow joins in SQL databases are nearly always caused by insufficient indexing. I'm not familiar with Hive, but it looks like they've replaced indexing with something else, so you must use whatever they replaced it with.
    – dirkt
    Nov 28 at 7:56


















  • We need more details in order to really help. Otherwise we can only guess. It would help, if you give us example files (with a few records in each file and some matches).
    – sudodus
    Nov 28 at 5:22










  • Each table has more then 30 columns. The common column is an id with string datatype.
    – Vandhana
    Nov 28 at 5:26










  • Is the common column always the same, for example column #1? And are the files unix (or linux) text files, or are they formatted some other way, for example in Windows?
    – sudodus
    Nov 28 at 5:28












  • yeah its always the same... the files are in unix only.
    – Vandhana
    Nov 28 at 5:45






  • 2




    In general, slow joins in SQL databases are nearly always caused by insufficient indexing. I'm not familiar with Hive, but it looks like they've replaced indexing with something else, so you must use whatever they replaced it with.
    – dirkt
    Nov 28 at 7:56
















We need more details in order to really help. Otherwise we can only guess. It would help, if you give us example files (with a few records in each file and some matches).
– sudodus
Nov 28 at 5:22




We need more details in order to really help. Otherwise we can only guess. It would help, if you give us example files (with a few records in each file and some matches).
– sudodus
Nov 28 at 5:22












Each table has more then 30 columns. The common column is an id with string datatype.
– Vandhana
Nov 28 at 5:26




Each table has more then 30 columns. The common column is an id with string datatype.
– Vandhana
Nov 28 at 5:26












Is the common column always the same, for example column #1? And are the files unix (or linux) text files, or are they formatted some other way, for example in Windows?
– sudodus
Nov 28 at 5:28






Is the common column always the same, for example column #1? And are the files unix (or linux) text files, or are they formatted some other way, for example in Windows?
– sudodus
Nov 28 at 5:28














yeah its always the same... the files are in unix only.
– Vandhana
Nov 28 at 5:45




yeah its always the same... the files are in unix only.
– Vandhana
Nov 28 at 5:45




2




2




In general, slow joins in SQL databases are nearly always caused by insufficient indexing. I'm not familiar with Hive, but it looks like they've replaced indexing with something else, so you must use whatever they replaced it with.
– dirkt
Nov 28 at 7:56




In general, slow joins in SQL databases are nearly always caused by insufficient indexing. I'm not familiar with Hive, but it looks like they've replaced indexing with something else, so you must use whatever they replaced it with.
– dirkt
Nov 28 at 7:56















active

oldest

votes











Your Answer








StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "106"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














draft saved

draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2funix.stackexchange.com%2fquestions%2f484583%2fhow-to-check-if-there-are-matching-records-in-2-huge-tables%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown






























active

oldest

votes













active

oldest

votes









active

oldest

votes






active

oldest

votes
















draft saved

draft discarded




















































Thanks for contributing an answer to Unix & Linux Stack Exchange!


  • Please be sure to answer the question. Provide details and share your research!

But avoid



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.





Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


Please pay close attention to the following guidance:


  • Please be sure to answer the question. Provide details and share your research!

But avoid



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2funix.stackexchange.com%2fquestions%2f484583%2fhow-to-check-if-there-are-matching-records-in-2-huge-tables%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







Popular posts from this blog

サソリ

広島県道265号伴広島線

Setup Asymptote in Texstudio