How to retrieve ambiguous columns from a hive table using subquery?
Main table:
CREATE EXTERNAL TABLE user(language STRING,snapshot_time STRING,products STRUCT<id:STRING,name:STRING>,item STRUCT<quantity:ARRAY<STRUCT<name:STRING>>>)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
STORED AS TEXTFILE
LOCATION '/user/input/sample';
I'm trying to insert ambiguous column names "product.name","A.name" into user_prod_info table. Since, the column names are same, I'm facing Ambiguous column reference text in q error.
Insert command:
INSERT OVERWRITE TABLE user_prod_info
SELECT q.* FROM (
SELECT row_number() OVER (PARTITION BY products.id ORDER BY snapshot_time DESC) AS temp_row_num,
language,
snapshot_time,
products.id,
products.name,
A.name
FROM user as raw
LATERAL VIEW EXPLODE(item.quantity) quantity as A
) q WHERE temp_row_num == 1;
This command is unable to retrieve the field from the specific table because we have two "name" fields. one is in "products" and the other is in "A".
I tried creating alias for "A.name as name1". I'm able to insert the data without errors. But, one record is storing in 3 rows with some nulls in it.
I got stuck over here. Can anyone please help me out regarding this...
hive
asked Nov 16 '18 at 6:58
ferventfervent
287
add a comment |
Main table:
CREATE EXTERNAL TABLE user(language STRING,snapshot_time STRING,products STRUCT<id:STRING,name:STRING>,item STRUCT<quantity:ARRAY<STRUCT<name:STRING>>>)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
STORED AS TEXTFILE
LOCATION '/user/input/sample';
I'm trying to insert ambiguous column names "product.name","A.name" into user_prod_info table. Since, the column names are same, I'm facing Ambiguous column reference text in q error.
Insert command:
INSERT OVERWRITE TABLE user_prod_info
SELECT q.* FROM (
SELECT row_number() OVER (PARTITION BY products.id ORDER BY snapshot_time DESC) AS temp_row_num,
language,
snapshot_time,
products.id,
products.name,
A.name
FROM user as raw
LATERAL VIEW EXPLODE(item.quantity) quantity as A
) q WHERE temp_row_num == 1;
This command is unable to retrieve the field from the specific table because we have two "name" fields. one is in "products" and the other is in "A".
I tried creating alias for "A.name as name1". I'm able to insert the data without errors. But, one record is storing in 3 rows with some nulls in it.
I got stuck over here. Can anyone please help me out regarding this...
hive
asked Nov 16 '18 at 6:58
ferventfervent
287
You haven't used product table at all in after FROM .From where product table comes from?
– saravanatn
Nov 16 '18 at 7:45
I have edited my post by adding the main table from which I'm trying to retrieve the fields and load into my new table
– fervent
Nov 16 '18 at 8:34
add a comment |
Main table:
CREATE EXTERNAL TABLE user(language STRING,snapshot_time STRING,products STRUCT<id:STRING,name:STRING>,item STRUCT<quantity:ARRAY<STRUCT<name:STRING>>>)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
STORED AS TEXTFILE
LOCATION '/user/input/sample';
I'm trying to insert ambiguous column names "product.name","A.name" into user_prod_info table. Since, the column names are same, I'm facing Ambiguous column reference text in q error.
Insert command:
INSERT OVERWRITE TABLE user_prod_info
SELECT q.* FROM (
SELECT row_number() OVER (PARTITION BY products.id ORDER BY snapshot_time DESC) AS temp_row_num,
language,
snapshot_time,
products.id,
products.name,
A.name
FROM user as raw
LATERAL VIEW EXPLODE(item.quantity) quantity as A
) q WHERE temp_row_num == 1;
This command is unable to retrieve the field from the specific table because we have two "name" fields. one is in "products" and the other is in "A".
I tried creating alias for "A.name as name1". I'm able to insert the data without errors. But, one record is storing in 3 rows with some nulls in it.
I got stuck over here. Can anyone please help me out regarding this...
hive
asked Nov 16 '18 at 6:58
ferventfervent
287
Main table:
CREATE EXTERNAL TABLE user(language STRING,snapshot_time STRING,products STRUCT<id:STRING,name:STRING>,item STRUCT<quantity:ARRAY<STRUCT<name:STRING>>>)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
STORED AS TEXTFILE
LOCATION '/user/input/sample';
I'm trying to insert ambiguous column names "product.name","A.name" into user_prod_info table. Since, the column names are same, I'm facing Ambiguous column reference text in q error.
Insert command:
INSERT OVERWRITE TABLE user_prod_info
SELECT q.* FROM (
SELECT row_number() OVER (PARTITION BY products.id ORDER BY snapshot_time DESC) AS temp_row_num,
language,
snapshot_time,
products.id,
products.name,
A.name
FROM user as raw
LATERAL VIEW EXPLODE(item.quantity) quantity as A
) q WHERE temp_row_num == 1;
This command is unable to retrieve the field from the specific table because we have two "name" fields. one is in "products" and the other is in "A".
I tried creating alias for "A.name as name1". I'm able to insert the data without errors. But, one record is storing in 3 rows with some nulls in it.
I got stuck over here. Can anyone please help me out regarding this...
hive
hive
asked Nov 16 '18 at 6:58
ferventfervent
287
asked Nov 16 '18 at 6:58
ferventfervent
287
edited Nov 16 '18 at 8:33
fervent
asked Nov 16 '18 at 6:58
ferventfervent
287
asked Nov 16 '18 at 6:58
ferventfervent
287
asked Nov 16 '18 at 6:58
ferventfervent
287
287
You haven't used product table at all in after FROM .From where product table comes from?
– saravanatn
Nov 16 '18 at 7:45
I have edited my post by adding the main table from which I'm trying to retrieve the fields and load into my new table
– fervent
Nov 16 '18 at 8:34
add a comment |
You haven't used product table at all in after FROM .From where product table comes from?
– saravanatn
Nov 16 '18 at 7:45
I have edited my post by adding the main table from which I'm trying to retrieve the fields and load into my new table
– fervent
Nov 16 '18 at 8:34
You haven't used product table at all in after FROM .From where product table comes from?
– saravanatn
Nov 16 '18 at 7:45
You haven't used product table at all in after FROM .From where product table comes from?
– saravanatn
Nov 16 '18 at 7:45
I have edited my post by adding the main table from which I'm trying to retrieve the fields and load into my new table
– fervent
Nov 16 '18 at 8:34
I have edited my post by adding the main table from which I'm trying to retrieve the fields and load into my new table
– fervent
Nov 16 '18 at 8:34
add a comment |
0
active
oldest
votes
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
});
}
});
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%2fstackoverflow.com%2fquestions%2f53332892%2fhow-to-retrieve-ambiguous-columns-from-a-hive-table-using-subquery%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
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%2fstackoverflow.com%2fquestions%2f53332892%2fhow-to-retrieve-ambiguous-columns-from-a-hive-table-using-subquery%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
You haven't used product table at all in after FROM .From where product table comes from?
– saravanatn
Nov 16 '18 at 7:45
I have edited my post by adding the main table from which I'm trying to retrieve the fields and load into my new table
– fervent
Nov 16 '18 at 8:34