Converting datime formats in Hive - please help
Hi,
The date time format coming from the source is* 25-FEB-01' .* I want to
convert it to the following format. *'MM/DD/' *. How can we do this in
Hive?
I see that as per the documentation
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateFunctions
I could possibly convert the string date to a Unix time stamp in seconds
using the below UDF. However, what is the string pattern if the date at
source is coming this way* 25-FEB-01'* ? The link provided to look up for
the patterns does not work.
Please help.
*Name:* unix_timestamp (string date, string pattern) function.
*Description: *Convert time string with given pattern (see [
http://java.sun.com/j2se/1.4.2/docs/api/java/text/SimpleDateFormat.html])
to Unix time stamp (in seconds), return 0 if fail:
unix_timestamp('2009-03-20', '-MM-dd') = 1237532400.
Vidya
Re: Converting datime formats in Hive - please help
But what should the format be if the source has data coming *25-FEB-01' ?
appreciate your help.*
On Sun, Oct 26, 2014 at 12:01 PM, Nagarjuna Vissarapu
nagarjuna.v...@gmail.com wrote:
Timestamp class is there to do this.
On Oct 27, 2014 12:26 AM, Vidya Sujeet sjayatheer...@gmail.com wrote:
Hi,
The date time format coming from the source is* 25-FEB-01' .* I want to
convert it to the following format. *'MM/DD/' *. How can we do this
in Hive?
I see that as per the documentation
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateFunctions
I could possibly convert the string date to a Unix time stamp in seconds
using the below UDF. However, what is the string pattern if the date at
source is coming this way* 25-FEB-01'* ? The link provided to look up
for the patterns does not work.
Please help.
*Name:* unix_timestamp (string date, string pattern) function.
*Description: *Convert time string with given pattern (see [
http://java.sun.com/j2se/1.4.2/docs/api/java/text/SimpleDateFormat.html])
to Unix time stamp (in seconds), return 0 if fail:
unix_timestamp('2009-03-20', '-MM-dd') = 1237532400.
Vidya
Re: Converting datime formats in Hive - please help
(dd-mmm-yy) returns NULL
On Sun, Oct 26, 2014 at 12:11 PM, Vidya Sujeet sjayatheer...@gmail.com
wrote:
But what should the format be if the source has data coming *25-FEB-01' ?
appreciate your help.*
On Sun, Oct 26, 2014 at 12:01 PM, Nagarjuna Vissarapu
nagarjuna.v...@gmail.com wrote:
Timestamp class is there to do this.
On Oct 27, 2014 12:26 AM, Vidya Sujeet sjayatheer...@gmail.com wrote:
Hi,
The date time format coming from the source is* 25-FEB-01' .* I want
to convert it to the following format. *'MM/DD/' *. How can we do
this in Hive?
I see that as per the documentation
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateFunctions
I could possibly convert the string date to a Unix time stamp in seconds
using the below UDF. However, what is the string pattern if the date at
source is coming this way* 25-FEB-01'* ? The link provided to look up
for the patterns does not work.
Please help.
*Name:* unix_timestamp (string date, string pattern) function.
*Description: *Convert time string with given pattern (see [
http://java.sun.com/j2se/1.4.2/docs/api/java/text/SimpleDateFormat.html])
to Unix time stamp (in seconds), return 0 if fail:
unix_timestamp('2009-03-20', '-MM-dd') = 1237532400.
Vidya
Re: Converting datime formats in Hive - please help
Ok, the pattern should be (DD-MMM-YY) all in upper case! thanks
On Sun, Oct 26, 2014 at 12:47 PM, Vidya Sujeet sjayatheer...@gmail.com
wrote:
(dd-mmm-yy) returns NULL
On Sun, Oct 26, 2014 at 12:11 PM, Vidya Sujeet sjayatheer...@gmail.com
wrote:
But what should the format be if the source has data coming *25-FEB-01' ?
appreciate your help.*
On Sun, Oct 26, 2014 at 12:01 PM, Nagarjuna Vissarapu
nagarjuna.v...@gmail.com wrote:
Timestamp class is there to do this.
On Oct 27, 2014 12:26 AM, Vidya Sujeet sjayatheer...@gmail.com
wrote:
Hi,
The date time format coming from the source is* 25-FEB-01' .* I want
to convert it to the following format. *'MM/DD/' *. How can we do
this in Hive?
I see that as per the documentation
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateFunctions
I could possibly convert the string date to a Unix time stamp in
seconds using the below UDF. However, what is the string pattern if the
date at source is coming this way* 25-FEB-01'* ? The link provided to
look up for the patterns does not work.
Please help.
*Name:* unix_timestamp (string date, string pattern) function.
*Description: *Convert time string with given pattern (see [
http://java.sun.com/j2se/1.4.2/docs/api/java/text/SimpleDateFormat.html])
to Unix time stamp (in seconds), return 0 if fail:
unix_timestamp('2009-03-20', '-MM-dd') = 1237532400.
Vidya
Re: Converting datime formats in Hive - please help
Hi Harpreet,
I used the below statement but it doesn't give me the correct time. It
gives me 2000-12-31 instead of 2001/02/05..what am I doing wrong?
select from_unixtime(unix_timestamp('05-FEB-01', 'DD-MMM-YY'))
thanks,
Vidya
On Sun, Oct 26, 2014 at 1:06 PM, Harpreet Singh Bedi
harpreet.be...@gmail.com wrote:
You’ll have to use lowercase “y” and lowercase “d” for year and date of
the month.
On Oct 26, 2014, at 15:03, Harpreet Singh Bedi harpreet.be...@gmail.com
wrote:
Yup, that should work!
On Oct 26, 2014, at 15:00, Vidya Sujeet sjayatheer...@gmail.com wrote:
Ok, the pattern should be (DD-MMM-YY) all in upper case! thanks
On Sun, Oct 26, 2014 at 12:47 PM, Vidya Sujeet sjayatheer...@gmail.com
wrote:
(dd-mmm-yy) returns NULL
On Sun, Oct 26, 2014 at 12:11 PM, Vidya Sujeet sjayatheer...@gmail.com
wrote:
But what should the format be if the source has data coming *25-FEB-01' ?
appreciate your help.*
On Sun, Oct 26, 2014 at 12:01 PM, Nagarjuna Vissarapu
nagarjuna.v...@gmail.com wrote:
Timestamp class is there to do this.
On Oct 27, 2014 12:26 AM, Vidya Sujeet sjayatheer...@gmail.com
wrote:
Hi,
The date time format coming from the source is* 25-FEB-01' .* I want
to convert it to the following format. *'MM/DD/' *. How can we do
this in Hive?
I see that as per the documentation
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateFunctions
I could possibly convert the string date to a Unix time stamp in
seconds using the below UDF. However, what is the string pattern if the
date at source is coming this way* 25-FEB-01'* ? The link provided
to look up for the patterns does not work.
Please help.
*Name:* unix_timestamp (string date, string pattern) function.
*Description: *Convert time string with given pattern (see [
http://java.sun.com/j2se/1.4.2/docs/api/java/text/SimpleDateFormat.html])
to Unix time stamp (in seconds), return 0 if fail:
unix_timestamp('2009-03-20', '-MM-dd') = 1237532400.
Vidya
Re: Converting datime formats in Hive - please help
yes, this retunrs NULLs.. from_unixtime(unix_timestamp('05-FEB-01',
'dd-MMM-yy '))
On Sun, Oct 26, 2014 at 2:09 PM, Harpreet Bedi harpreet.be...@gmail.com
wrote:
Hi Vidya,
Have you tried using this instead?
select from_unixtime(unix_timestamp('05-FEB-01', 'dd-MMM-yy '))
On Sunday, October 26, 2014, Vidya Sujeet sjayatheer...@gmail.com wrote:
Hi Harpreet,
I used the below statement but it doesn't give me the correct time. It
gives me 2000-12-31 instead of 2001/02/05..what am I doing wrong?
select from_unixtime(unix_timestamp('05-FEB-01', 'DD-MMM-YY'))
thanks,
Vidya
On Sun, Oct 26, 2014 at 1:06 PM, Harpreet Singh Bedi
harpreet.be...@gmail.com wrote:
You’ll have to use lowercase “y” and lowercase “d” for year and date of
the month.
On Oct 26, 2014, at 15:03, Harpreet Singh Bedi harpreet.be...@gmail.com
wrote:
Yup, that should work!
On Oct 26, 2014, at 15:00, Vidya Sujeet sjayatheer...@gmail.com wrote:
Ok, the pattern should be (DD-MMM-YY) all in upper case! thanks
On Sun, Oct 26, 2014 at 12:47 PM, Vidya Sujeet sjayatheer...@gmail.com
wrote:
(dd-mmm-yy) returns NULL
On Sun, Oct 26, 2014 at 12:11 PM, Vidya Sujeet sjayatheer...@gmail.com
wrote:
But what should the format be if the source has data coming *25-FEB-01' ?
appreciate your help.*
On Sun, Oct 26, 2014 at 12:01 PM, Nagarjuna Vissarapu
nagarjuna.v...@gmail.com wrote:
Timestamp class is there to do this.
On Oct 27, 2014 12:26 AM, Vidya Sujeet sjayatheer...@gmail.com
wrote:
Hi,
The date time format coming from the source is* 25-FEB-01' .* I
want to convert it to the following format. *'MM/DD/' *. How
can we do this in Hive?
I see that as per the documentation
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateFunctions
I could possibly convert the string date to a Unix time stamp in
seconds using the below UDF. However, what is the string pattern if the
date at source is coming this way* 25-FEB-01'* ? The link provided
to look up for the patterns does not work.
Please help.
*Name:* unix_timestamp (string date, string pattern) function.
*Description: *Convert time string with given pattern (see [
http://java.sun.com/j2se/1.4.2/docs/api/java/text/SimpleDateFormat.html])
to Unix time stamp (in seconds), return 0 if fail:
unix_timestamp('2009-03-20', '-MM-dd') = 1237532400.
Vidya
--
Harpreet Bedi
error while running a hive view
Hello, I am getting the following error while running a view. However, the hive script that is used in the creation of hive view runs without error. It's only when I select * from hive_view I am getting the error saying *Caused by: java.lang.RuntimeException: Cannot serialize object* Could anyone please help with this issue? The underlying base tables are bucketed..but not partitioned. Thanks,
create table / data type syntax for csv files with comma in the column
Hello, I have a csv file that has columns which contains commas within a string enclosed with a . ex: column name:*'Issue' *value:*Other (phone, health club, etc)* *Question:* What should the data type of 'Issue' be? Or how should I format the table (row format delimited terminated by) so that the comma in the column (issue) is accounted for correctly I had set it as below but this puts the words in the string (ex: *Other (phone, health club, etc)) * into separate columns create table consumercomplaints (ComplaintID int, Product string, Subproduct string, Issue string, Subissue string, State string, ZIPcode int, Submittedvia string, Datereceived string, Datesenttocompany string, Company string, Companyresponse string, Timelyresponse string, Consumerdisputed string ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' location '/user/hive/warehouse/mydb/consumer_complaints.csv'; Sample data -- Complaint ID,Product,Sub-product,Issue,Sub-issue,State,ZIP code,Submitted via,Date received,Date sent to company,Company,Company response,Timely response?,Consumer disputed? 943291,Debt collection,,Cont'd attempts collect debt not owed,Debt is not mine,MO,63123,Web,07/18/2014,07/18/2014,Enhanced Recovery Company, LLC,Closed with non-monetary relief,Yes, 943698,Bank account or service,Checking account,Deposits and withdrawals,,CA,93030,Web,07/18/2014,07/18/2014,U.S. Bancorp,In progress,Yes, 943521,Debt collection,,Cont'd attempts collect debt not owed,Debt is not mine,OH,44116,Web,07/18/2014,07/18/2014,Vital Solutions, Inc.,Closed with explanation,Yes, 943400,Debt collection,Other (phone, health club, etc.),Communication tactics,Frequent or repeated calls,MD,21133,Web,07/18/2014,07/18/2014,The CBE Group, Inc.,Closed with explanation,Yes, Thanks, Vidya
Re: Errors while creating a new table using existing table schema
thanks all. I created a new database and it works fine there.. On Sat, Jul 19, 2014 at 1:37 PM, Lefty Leverenz leftylever...@gmail.com wrote: And now it's documented in the DDL wiki: - Use Database https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-UseDatabase - Create Table https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-CreateTable (notes after syntax) -- Lefty On Sat, Jul 19, 2014 at 3:59 PM, Lefty Leverenz leftylever...@gmail.com wrote: André, thanks for the reminder about USE database, which hadn't been documented in the wiki. I've added a note to the Select Syntax https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select#LanguageManualSelect-SelectSyntax section (for starters). -- Lefty On Sat, Jul 19, 2014 at 4:52 AM, sai chaitanya tirumerla tirume...@gmail.com wrote: Hi Vidya, I believe you need to remove Location '/user/hive/warehouse/default.db/jobs_ex2' line since it stores the table automatically in warehouse once you create the table. On Sat, Jul 19, 2014 at 1:46 AM, Andre Araujo ara...@pythian.com wrote: Vidya, I'm not sure I've understood your problem correctly. But if you want to create a table in the default database you can do either: use default; create table jobs_ex2 as select ... OR create table default.jobs_ex2 as select ... With that you don't need to specify the LOCATION clause. However, the ROW FORMAT clause cannot be specified after the query. It must come at the beginning of your statement: Try this: *create table default.jobs_ex2 * *row format delimited fields terminated by ',' * *as select year, capitalregion, universe from jobs;* On 19 July 2014 17:23, Vidya Sujeet sjayatheer...@gmail.com wrote: Hello, I am trying to create a new table using an existing table's schema (existing table name in hive: jobs). However, when I do that it doesn't put the new table (new table name in hive: jobs_ex2) in the same location as the existing table. When I specify the location explicitly, it errors out. Query which has the problem is pasted below: *create table jobs_ex2as select year, capitalregion, universe from jobsrow format delimited fields terminated by ',' Location '/user/hive/warehouse/default.db/jobs_ex2'* The file that is being used to create a table is in the following location: */user/hive/warehouse/default.db/jobs/universe=1/Jobs.csv* where universe=1 is the partition. The new table jobs_ex_2 needs to be created inside default.db folder. thanks, Vidya -- André Araújo Big Data Consultant/Solutions Architect The Pythian Group - Australia - www.pythian.com Office (calls from within Australia): 1300 366 021 x1270 Office (international): +61 2 8016 7000 x270 *OR* +1 613 565 8696 x1270 Mobile: +61 410 323 559 Fax: +61 2 9805 0544 IM: pythianaraujo @ AIM/MSN/Y! or ara...@pythian.com @ GTalk “Success is not about standing at the top, it's the steps you leave behind.” — Iker Pou (rock climber) --
Re: Errors while creating a new table using existing table schema
Hello, I am trying to create a new table using an existing table's schema (existing table name in hive: jobs). However, when I do that it doesn't put the new table (new table name in hive: jobs_ex2) in the same location as the existing table. When I specify the location explicitly, it errors out. Query which has the problem is pasted below: *create table jobs_ex2as select year, capitalregion, universe from jobsrow format delimited fields terminated by ',' Location '/user/hive/warehouse/default.db/jobs_ex2'* The file that is being used to create a table is in the following location: */user/hive/warehouse/default.db/jobs/universe=1/Jobs.csv* where universe=1 is the partition. The new table jobs_ex_2 needs to be created inside default.db folder. thanks, Vidya
Errors while creating a new table using existing table schema
Hello, I am trying to create a new table using an existing table's schema (existing table name in hive: jobs). However, when I do that it doesn't put the new table (new table name in hive: jobs_ex2) in the same location as the existing table. When I specify the location explicitly, it errors out. Query which has the problem is pasted below: *create table jobs_ex2as select year, capitalregion, universe from jobsrow format delimited fields terminated by ',' Location '/user/hive/warehouse/default.db/jobs_ex2'* The file that is being used to create a table is in the following location: */user/hive/warehouse/default.db/jobs/universe=1/Jobs.csv* where universe=1 is the partition. The new table jobs_ex_2 needs to be created inside default.db folder. thanks, Vidya
