Hive create query with timestamp having format AM/PM
I have a file with only one date column which contains date in format '10/2/2017 10:56:00 AM'
I want to create a external hive table on this file but due to the AM/PM format hive timestamp is not able to recognize it, any pointers to this?
hive hiveql
asked Nov 22 at 12:35
Mahesh J
317
add a comment |
I have a file with only one date column which contains date in format '10/2/2017 10:56:00 AM'
I want to create a external hive table on this file but due to the AM/PM format hive timestamp is not able to recognize it, any pointers to this?
hive hiveql
asked Nov 22 at 12:35
Mahesh J
317
add a comment |
I have a file with only one date column which contains date in format '10/2/2017 10:56:00 AM'
I want to create a external hive table on this file but due to the AM/PM format hive timestamp is not able to recognize it, any pointers to this?
hive hiveql
asked Nov 22 at 12:35
Mahesh J
317
I have a file with only one date column which contains date in format '10/2/2017 10:56:00 AM'
I want to create a external hive table on this file but due to the AM/PM format hive timestamp is not able to recognize it, any pointers to this?
hive hiveql
hive hiveql
asked Nov 22 at 12:35
Mahesh J
317
asked Nov 22 at 12:35
Mahesh J
317
asked Nov 22 at 12:35
Mahesh J
317
asked Nov 22 at 12:35
Mahesh J
317
asked Nov 22 at 12:35
Mahesh J
317
317
add a comment |
add a comment |
3 Answers
3
active
oldest
votes
You can create hive table with timestamp format then in table properties map your data timestamp format.
Example:
i have a txt file with below data in it:
bash$ cat t1.txt
9/1/2016 11:31:21 AM
10/2/2017 10:56:00 AM
Create a hive table with:
hive> create external table i(ts timestamp)
row format delimited
fields terminated by ','
stored as textfile
TBLPROPERTIES("timestamp.formats"="MM/dd/yyyy hh:mm:ss a");
select from the table:
hive> select * from i;
+------------------------+--+
| i.ts |
+------------------------+--+
| 2016-09-01 11:31:21.0 |
| 2017-10-02 10:56:00.0 |
+------------------------+--+
answered Nov 22 at 14:10
Shu
4,4762418
Thanks for reply, it works but the data type of column is string I want it to be timestamp
– Mahesh J
Nov 23 at 5:45
@MaheshJ, does yoursource data field value is enclosed in ""(double quotes)?, then you cannot define astimestampformat unless if you useRegex serdetoexclude ""(double quotes)and capture only thetimestampvalue.
– Shu
Nov 23 at 12:18
add a comment |
Instead of using timestamp as datatype you can use string as a dataype and try it
answered Nov 23 at 4:54
sivaraj
143
add a comment |
Ok fine.In my view whether your datatype is in timestamp or in string format it is not going to make much difference.Because whatever you are trying to achieve by using timestamp format we can achieve using string format as well
answered Nov 24 at 5:35
sivaraj
143
add a comment |
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
});
}
});
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%2f53431180%2fhive-create-query-with-timestamp-having-format-am-pm%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can create hive table with timestamp format then in table properties map your data timestamp format.
Example:
i have a txt file with below data in it:
bash$ cat t1.txt
9/1/2016 11:31:21 AM
10/2/2017 10:56:00 AM
Create a hive table with:
hive> create external table i(ts timestamp)
row format delimited
fields terminated by ','
stored as textfile
TBLPROPERTIES("timestamp.formats"="MM/dd/yyyy hh:mm:ss a");
select from the table:
hive> select * from i;
+------------------------+--+
| i.ts |
+------------------------+--+
| 2016-09-01 11:31:21.0 |
| 2017-10-02 10:56:00.0 |
+------------------------+--+
answered Nov 22 at 14:10
Shu
4,4762418
Thanks for reply, it works but the data type of column is string I want it to be timestamp
– Mahesh J
Nov 23 at 5:45
@MaheshJ, does yoursource data field value is enclosed in ""(double quotes)?, then you cannot define astimestampformat unless if you useRegex serdetoexclude ""(double quotes)and capture only thetimestampvalue.
– Shu
Nov 23 at 12:18
add a comment |
You can create hive table with timestamp format then in table properties map your data timestamp format.
Example:
i have a txt file with below data in it:
bash$ cat t1.txt
9/1/2016 11:31:21 AM
10/2/2017 10:56:00 AM
Create a hive table with:
hive> create external table i(ts timestamp)
row format delimited
fields terminated by ','
stored as textfile
TBLPROPERTIES("timestamp.formats"="MM/dd/yyyy hh:mm:ss a");
select from the table:
hive> select * from i;
+------------------------+--+
| i.ts |
+------------------------+--+
| 2016-09-01 11:31:21.0 |
| 2017-10-02 10:56:00.0 |
+------------------------+--+
answered Nov 22 at 14:10
Shu
4,4762418
Thanks for reply, it works but the data type of column is string I want it to be timestamp
– Mahesh J
Nov 23 at 5:45
@MaheshJ, does yoursource data field value is enclosed in ""(double quotes)?, then you cannot define astimestampformat unless if you useRegex serdetoexclude ""(double quotes)and capture only thetimestampvalue.
– Shu
Nov 23 at 12:18
add a comment |
You can create hive table with timestamp format then in table properties map your data timestamp format.
Example:
i have a txt file with below data in it:
bash$ cat t1.txt
9/1/2016 11:31:21 AM
10/2/2017 10:56:00 AM
Create a hive table with:
hive> create external table i(ts timestamp)
row format delimited
fields terminated by ','
stored as textfile
TBLPROPERTIES("timestamp.formats"="MM/dd/yyyy hh:mm:ss a");
select from the table:
hive> select * from i;
+------------------------+--+
| i.ts |
+------------------------+--+
| 2016-09-01 11:31:21.0 |
| 2017-10-02 10:56:00.0 |
+------------------------+--+
answered Nov 22 at 14:10
Shu
4,4762418
You can create hive table with timestamp format then in table properties map your data timestamp format.
Example:
i have a txt file with below data in it:
bash$ cat t1.txt
9/1/2016 11:31:21 AM
10/2/2017 10:56:00 AM
Create a hive table with:
hive> create external table i(ts timestamp)
row format delimited
fields terminated by ','
stored as textfile
TBLPROPERTIES("timestamp.formats"="MM/dd/yyyy hh:mm:ss a");
select from the table:
hive> select * from i;
+------------------------+--+
| i.ts |
+------------------------+--+
| 2016-09-01 11:31:21.0 |
| 2017-10-02 10:56:00.0 |
+------------------------+--+
answered Nov 22 at 14:10
Shu
4,4762418
answered Nov 22 at 14:10
Shu
4,4762418
answered Nov 22 at 14:10
Shu
4,4762418
answered Nov 22 at 14:10
Shu
4,4762418
4,4762418
Thanks for reply, it works but the data type of column is string I want it to be timestamp
– Mahesh J
Nov 23 at 5:45
@MaheshJ, does yoursource data field value is enclosed in ""(double quotes)?, then you cannot define astimestampformat unless if you useRegex serdetoexclude ""(double quotes)and capture only thetimestampvalue.
– Shu
Nov 23 at 12:18
add a comment |
Thanks for reply, it works but the data type of column is string I want it to be timestamp
– Mahesh J
Nov 23 at 5:45
@MaheshJ, does yoursource data field value is enclosed in ""(double quotes)?, then you cannot define astimestampformat unless if you useRegex serdetoexclude ""(double quotes)and capture only thetimestampvalue.
– Shu
Nov 23 at 12:18
Thanks for reply, it works but the data type of column is string I want it to be timestamp
– Mahesh J
Nov 23 at 5:45
Thanks for reply, it works but the data type of column is string I want it to be timestamp
– Mahesh J
Nov 23 at 5:45
@MaheshJ, does your
source data field value is enclosed in ""(double quotes)? , then you cannot define as timestamp format unless if you use Regex serde to exclude ""(double quotes) and capture only the timestamp value.– Shu
Nov 23 at 12:18
@MaheshJ, does your
source data field value is enclosed in ""(double quotes)? , then you cannot define as timestamp format unless if you use Regex serde to exclude ""(double quotes) and capture only the timestamp value.– Shu
Nov 23 at 12:18
add a comment |
Instead of using timestamp as datatype you can use string as a dataype and try it
answered Nov 23 at 4:54
sivaraj
143
add a comment |
Instead of using timestamp as datatype you can use string as a dataype and try it
answered Nov 23 at 4:54
sivaraj
143
add a comment |
Instead of using timestamp as datatype you can use string as a dataype and try it
answered Nov 23 at 4:54
sivaraj
143
Instead of using timestamp as datatype you can use string as a dataype and try it
answered Nov 23 at 4:54
sivaraj
143
answered Nov 23 at 4:54
sivaraj
143
answered Nov 23 at 4:54
sivaraj
143
answered Nov 23 at 4:54
sivaraj
143
143
add a comment |
add a comment |
Ok fine.In my view whether your datatype is in timestamp or in string format it is not going to make much difference.Because whatever you are trying to achieve by using timestamp format we can achieve using string format as well
answered Nov 24 at 5:35
sivaraj
143
add a comment |
Ok fine.In my view whether your datatype is in timestamp or in string format it is not going to make much difference.Because whatever you are trying to achieve by using timestamp format we can achieve using string format as well
answered Nov 24 at 5:35
sivaraj
143
add a comment |
Ok fine.In my view whether your datatype is in timestamp or in string format it is not going to make much difference.Because whatever you are trying to achieve by using timestamp format we can achieve using string format as well
answered Nov 24 at 5:35
sivaraj
143
Ok fine.In my view whether your datatype is in timestamp or in string format it is not going to make much difference.Because whatever you are trying to achieve by using timestamp format we can achieve using string format as well
answered Nov 24 at 5:35
sivaraj
143
answered Nov 24 at 5:35
sivaraj
143
answered Nov 24 at 5:35
sivaraj
143
answered Nov 24 at 5:35
sivaraj
143
143
add a comment |
add a comment |
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.
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.
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%2f53431180%2fhive-create-query-with-timestamp-having-format-am-pm%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
