Hive : Use OR in JOIN

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}

I am having one dataset

enter image description here

and another lookup table

enter image description here

with the help of below query

SELECT Col1, Col2, Col3, Col4, a.RefC4, b.RefC4, c.RefC4

from Tab1 LEFT OUTER JOIN  (

      SELECT * from Ref1 where RefC2 = 'A1') a on Col2 = RefC3

     LEFT OUTER JOIN  (

      SELECT * from Ref1 where RefC2 = 'A2') b on Col3 = RefC3

     LEFT OUTER JOIN  (

      SELECT * from Ref1 where RefC2 = 'A4') c on Col4 = RefC3;

I am getting below dataset

enter image description here

I have 45 values against which lookup needs to be done in order to enrich other columns, in this example (Col5, Col6, Col7). If I use left outer join, query become too long and difficult to maintain. I tried multiple ways but due to one or other hive restrictions I am not able to resolve in shorter and optimize way.

I can't use OR with joins and I don't want to use row_number() as this increase turnaround time.

Select ....

from Tab1 LEFT OUTER JOIN  (

      SELECT * from Ref1) a on  (RefC2 = 'A1' and Col2 = RefC3) 

      or (RefC2 = 'A2' and Col3 = RefC3)

      or (RefC2 = 'A4' and Col4 = RefC3);

Please suggest any other way to write this query in optimize and shorter way.

edited Nov 19 '18 at 6:37

asked Nov 16 '18 at 13:16

Ajay

3262828

The problem is your data structure. You are storing values in columns rather than in rows, and that makes it hard to process.

– Gordon Linoff
Nov 16 '18 at 13:19

I can understand, the data structure is bit complex but is there any way to resolve in efficient manner

– Ajay
Nov 16 '18 at 13:21

Will it help if I transpose the lookup table?

– Ajay
Nov 19 '18 at 6:36

. . It would help if you transpose both tables.

– Gordon Linoff
Nov 19 '18 at 13:17

add a comment |

I am having one dataset

enter image description here

and another lookup table

enter image description here

with the help of below query

SELECT Col1, Col2, Col3, Col4, a.RefC4, b.RefC4, c.RefC4

from Tab1 LEFT OUTER JOIN  (

      SELECT * from Ref1 where RefC2 = 'A1') a on Col2 = RefC3

     LEFT OUTER JOIN  (

      SELECT * from Ref1 where RefC2 = 'A2') b on Col3 = RefC3

     LEFT OUTER JOIN  (

      SELECT * from Ref1 where RefC2 = 'A4') c on Col4 = RefC3;

I am getting below dataset

enter image description here

I can't use OR with joins and I don't want to use row_number() as this increase turnaround time.

Select ....

from Tab1 LEFT OUTER JOIN  (

      SELECT * from Ref1) a on  (RefC2 = 'A1' and Col2 = RefC3) 

      or (RefC2 = 'A2' and Col3 = RefC3)

      or (RefC2 = 'A4' and Col4 = RefC3);

Please suggest any other way to write this query in optimize and shorter way.

edited Nov 19 '18 at 6:37

asked Nov 16 '18 at 13:16

Ajay

3262828

The problem is your data structure. You are storing values in columns rather than in rows, and that makes it hard to process.

– Gordon Linoff
Nov 16 '18 at 13:19

I can understand, the data structure is bit complex but is there any way to resolve in efficient manner

– Ajay
Nov 16 '18 at 13:21

Will it help if I transpose the lookup table?

– Ajay
Nov 19 '18 at 6:36

. . It would help if you transpose both tables.

– Gordon Linoff
Nov 19 '18 at 13:17

add a comment |

I am having one dataset

enter image description here

and another lookup table

enter image description here

with the help of below query

SELECT Col1, Col2, Col3, Col4, a.RefC4, b.RefC4, c.RefC4

from Tab1 LEFT OUTER JOIN  (

      SELECT * from Ref1 where RefC2 = 'A1') a on Col2 = RefC3

     LEFT OUTER JOIN  (

      SELECT * from Ref1 where RefC2 = 'A2') b on Col3 = RefC3

     LEFT OUTER JOIN  (

      SELECT * from Ref1 where RefC2 = 'A4') c on Col4 = RefC3;

I am getting below dataset

enter image description here

I can't use OR with joins and I don't want to use row_number() as this increase turnaround time.

Select ....

from Tab1 LEFT OUTER JOIN  (

      SELECT * from Ref1) a on  (RefC2 = 'A1' and Col2 = RefC3) 

      or (RefC2 = 'A2' and Col3 = RefC3)

      or (RefC2 = 'A4' and Col4 = RefC3);

Please suggest any other way to write this query in optimize and shorter way.

edited Nov 19 '18 at 6:37

asked Nov 16 '18 at 13:16

Ajay

3262828

I am having one dataset

enter image description here

and another lookup table

enter image description here

with the help of below query

SELECT Col1, Col2, Col3, Col4, a.RefC4, b.RefC4, c.RefC4

from Tab1 LEFT OUTER JOIN  (

      SELECT * from Ref1 where RefC2 = 'A1') a on Col2 = RefC3

     LEFT OUTER JOIN  (

      SELECT * from Ref1 where RefC2 = 'A2') b on Col3 = RefC3

     LEFT OUTER JOIN  (

      SELECT * from Ref1 where RefC2 = 'A4') c on Col4 = RefC3;

I am getting below dataset

enter image description here

I can't use OR with joins and I don't want to use row_number() as this increase turnaround time.

Select ....

from Tab1 LEFT OUTER JOIN  (

      SELECT * from Ref1) a on  (RefC2 = 'A1' and Col2 = RefC3) 

      or (RefC2 = 'A2' and Col3 = RefC3)

      or (RefC2 = 'A4' and Col4 = RefC3);

Please suggest any other way to write this query in optimize and shorter way.

sql hive hiveql

edited Nov 19 '18 at 6:37

asked Nov 16 '18 at 13:16

Ajay

3262828

edited Nov 19 '18 at 6:37

asked Nov 16 '18 at 13:16

Ajay

3262828

edited Nov 19 '18 at 6:37

asked Nov 16 '18 at 13:16

Ajay

3262828

asked Nov 16 '18 at 13:16

Ajay

3262828

asked Nov 16 '18 at 13:16

Ajay

3262828

The problem is your data structure. You are storing values in columns rather than in rows, and that makes it hard to process.

– Gordon Linoff
Nov 16 '18 at 13:19

I can understand, the data structure is bit complex but is there any way to resolve in efficient manner

– Ajay
Nov 16 '18 at 13:21

Will it help if I transpose the lookup table?

– Ajay
Nov 19 '18 at 6:36

. . It would help if you transpose both tables.

– Gordon Linoff
Nov 19 '18 at 13:17

add a comment |

The problem is your data structure. You are storing values in columns rather than in rows, and that makes it hard to process.

– Gordon Linoff
Nov 16 '18 at 13:19

I can understand, the data structure is bit complex but is there any way to resolve in efficient manner

– Ajay
Nov 16 '18 at 13:21

Will it help if I transpose the lookup table?

– Ajay
Nov 19 '18 at 6:36

. . It would help if you transpose both tables.

– Gordon Linoff
Nov 19 '18 at 13:17

The problem is your data structure. You are storing values in columns rather than in rows, and that makes it hard to process.

– Gordon Linoff
Nov 16 '18 at 13:19

I can understand, the data structure is bit complex but is there any way to resolve in efficient manner

– Ajay
Nov 16 '18 at 13:21

Will it help if I transpose the lookup table?

– Ajay
Nov 19 '18 at 6:36

. . It would help if you transpose both tables.

– Gordon Linoff
Nov 19 '18 at 13:17

add a comment |

0

active

oldest

votes

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53338679%2fhive-use-or-in-join%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

0

active

oldest

votes

0

active

oldest

votes

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

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');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Vfrdtyky