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?
join database table apache-hive
asked Nov 28 at 5:12
Vandhana
366
|
show 4 more comments
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?
join database table apache-hive
asked Nov 28 at 5:12
Vandhana
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
|
show 4 more comments
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?
join database table apache-hive
asked Nov 28 at 5:12
Vandhana
366
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
join database table apache-hive
asked Nov 28 at 5:12
Vandhana
366
asked Nov 28 at 5:12
Vandhana
366
edited Nov 28 at 5:45
asked Nov 28 at 5:12
Vandhana
366
asked Nov 28 at 5:12
Vandhana
366
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
|
show 4 more comments
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
|
show 4 more comments
active
oldest
votes
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
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