just use hive split function for strings and get the value extracted.
by the way I am not sure why the to_date function is failing
stringto_date(string timestamp)Returns the date part of a timestamp string:
to_date("1970-01-01 00:00:00") = "1970-01-01"
also, I am sorry i might have misread your question
what do you mean by "partition column does not accepts to_date(event_date)
form "On Fri, Jun 14, 2013 at 1:04 PM, Hamza Asad <hamza.asa...@gmail.com> wrote: > sample row of my data is > *591269735,1,1022,2012-06-24 > 11:08:10.9,null,2,null,null,null,null,null,null,null,null,12,null,null,2,null,null,null,null,4,1,null,null,null,null,null,null > * > > and i want to partition it according to date i.e *2012-06-24 *skiping the > hh:mm:ss.ff part > > > On Fri, Jun 14, 2013 at 12:27 PM, Nitin Pawar <nitinpawar...@gmail.com>wrote: > >> can you provide whats your data and what you want it to look like ? >> >> >> On Fri, Jun 14, 2013 at 12:31 PM, Hamza Asad <hamza.asa...@gmail.com>wrote: >> >>> which UDF? it does not take to_date(event_date) column >>> >>> >>> On Fri, Jun 14, 2013 at 11:54 AM, Nitin Pawar >>> <nitinpawar...@gmail.com>wrote: >>> >>>> use already existing UDFs to split or transform your values the way you >>>> want >>>> >>>> >>>> On Fri, Jun 14, 2013 at 12:09 PM, Hamza Asad <hamza.asa...@gmail.com>wrote: >>>> >>>>> OIC. I got it. Thanx alot nitin :). One more thing i want to ask >>>>> related this issue, if old table contains event_date in format "2012-06-24 >>>>> 06:04:11.9" then how can i partition it according to date part only? As >>>>> partition column does not accepts to_date(event_date) form. >>>>> >>>>> >>>>> On Thu, Jun 13, 2013 at 5:07 PM, Nitin Pawar >>>>> <nitinpawar...@gmail.com>wrote: >>>>> >>>>>> If the input column value is NULL or empty string, the row will be put >>>>>> into a special partition, whose name is controlled by the hive parameter >>>>>> hive.exec.default.dynamic.partition.name. The default value is >>>>>> `__HIVE_DEFAULT_PARTITION__`. Basically this partition will contain all >>>>>> "bad" rows whose value are not valid partition names. >>>>>> >>>>>> so basically you do following things >>>>>> >>>>>> when you create a partitioned table, your partitioned column is normally >>>>>> at the end of the table, so when you are inserting data into this >>>>>> partitioned table, I would recommend using the column names in place >>>>>> select * from >>>>>> >>>>>> so your insert query should look like >>>>>> >>>>>> set hive.exec.dynamic.partition=true; >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> set hive.exec.dynamic.partition.mode=nonstrict; >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> insert overwrite table new_table partition(event_date) select col1, col2 >>>>>> .... coln, event_date from old_table; >>>>>> >>>>>> >>>>>> >>>>>> On Thu, Jun 13, 2013 at 5:24 PM, Hamza Asad >>>>>> <hamza.asa...@gmail.com>wrote: >>>>>> >>>>>>> when i browse it in browser, all the data is in * >>>>>>> event_date=__HIVE_DEFAULT_PARTITION__<http://10.0.0.14:50075/browseDirectory.jsp?dir=%2Fvar%2Flog%2Fpring%2Fhive%2Fwarehouse%2Fnydus.db%2Fnew_rc_partition_cluster_table%2Fevent_date%3D__HIVE_DEFAULT_PARTITION__&namenodeInfoPort=50070> >>>>>>> *, rest of the files does not contains data >>>>>>> >>>>>>> >>>>>>> On Thu, Jun 13, 2013 at 4:52 PM, Nitin Pawar < >>>>>>> nitinpawar...@gmail.com> wrote: >>>>>>> >>>>>>>> what do you mean when you say "it wont split correctly" ? >>>>>>>> >>>>>>>> >>>>>>>> On Thu, Jun 13, 2013 at 5:19 PM, Hamza Asad <hamza.asa...@gmail.com >>>>>>>> > wrote: >>>>>>>> >>>>>>>>> what if i have data of more then 500 days then how can i create >>>>>>>>> partition on date column by specifying each and every date? (i knw >>>>>>>>> that >>>>>>>>> does not happens in dynamic partition but on dynamic partition, it >>>>>>>>> wont >>>>>>>>> splits correctly). >>>>>>>>> >>>>>>>>> >>>>>>>>> On Thu, Jun 13, 2013 at 4:12 PM, Nitin Pawar < >>>>>>>>> nitinpawar...@gmail.com> wrote: >>>>>>>>> >>>>>>>>>> you can partition existing table unless the hdfs data is laid out >>>>>>>>>> in partitioned fashion. >>>>>>>>>> your best bet is create a new partitioned table >>>>>>>>>> enable dynamic paritionining >>>>>>>>>> read from old table and write into new table >>>>>>>>>> >>>>>>>>>> you can verify the new partitions by using command "show >>>>>>>>>> partitions" >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> On Thu, Jun 13, 2013 at 4:40 PM, Hamza Asad < >>>>>>>>>> hamza.asa...@gmail.com> wrote: >>>>>>>>>> >>>>>>>>>>> now i created partition table like >>>>>>>>>>> *CREATE TABLE new_rc_partition_cluster_table( >>>>>>>>>>> >>>>>>>>>>> id int, >>>>>>>>>>> event_id int, >>>>>>>>>>> user_id BIGINT, >>>>>>>>>>> >>>>>>>>>>> intval_1 int , >>>>>>>>>>> intval_2 int, >>>>>>>>>>> intval_3 int, >>>>>>>>>>> intval_4 int, >>>>>>>>>>> intval_5 int, >>>>>>>>>>> intval_6 int, >>>>>>>>>>> intval_7 int, >>>>>>>>>>> intval_8 int, >>>>>>>>>>> intval_9 int, >>>>>>>>>>> intval_10 int, >>>>>>>>>>> intval_11 int, >>>>>>>>>>> intval_12 int, >>>>>>>>>>> intval_13 int, >>>>>>>>>>> intval_14 int, >>>>>>>>>>> intval_15 int, >>>>>>>>>>> intval_16 int, >>>>>>>>>>> intval_17 int, >>>>>>>>>>> intval_18 int, >>>>>>>>>>> intval_19 int, >>>>>>>>>>> intval_20 int, >>>>>>>>>>> intval_21 int, >>>>>>>>>>> intval_22 int, >>>>>>>>>>> intval_23 int, >>>>>>>>>>> intval_24 int, >>>>>>>>>>> intval_25 int, >>>>>>>>>>> intval_26 int) >>>>>>>>>>> PARTITIONED BY (event_date string) >>>>>>>>>>> >>>>>>>>>>> CLUSTERED BY(id) INTO 256 BUCKETS >>>>>>>>>>> ROW FORMAT DELIMITED >>>>>>>>>>> FIELDS TERMINATED BY ',' >>>>>>>>>>> STORED AS RCFile; >>>>>>>>>>> * >>>>>>>>>>> >>>>>>>>>>> rest of the commands are same. But this time INSERT OVERWRITE >>>>>>>>>>> query executed and took time but when i queries from that table, it >>>>>>>>>>> results >>>>>>>>>>> none as it does not contains data. why is this so? and also please >>>>>>>>>>> tell me >>>>>>>>>>> how can i partition my existing table dynamicaly on date so that >>>>>>>>>>> data >>>>>>>>>>> splits equally without mentioning date explicitly? >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> On Wed, Jun 12, 2013 at 6:50 PM, Nitin Pawar < >>>>>>>>>>> nitinpawar...@gmail.com> wrote: >>>>>>>>>>> >>>>>>>>>>>> you did not create partitioned table. You just created a >>>>>>>>>>>> bucketed table. >>>>>>>>>>>> >>>>>>>>>>>> refer to partitioned table created >>>>>>>>>>>> something like >>>>>>>>>>>> partitioned by (event_date string) >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> On Wed, Jun 12, 2013 at 7:17 PM, Hamza Asad < >>>>>>>>>>>> hamza.asa...@gmail.com> wrote: >>>>>>>>>>>> >>>>>>>>>>>>> i have created table after enabling dynamic partition. i >>>>>>>>>>>>> partitioned it on date but it is not splitting data datewise. >>>>>>>>>>>>> Below is the >>>>>>>>>>>>> query of table created and data insert >>>>>>>>>>>>> CREATE TABLE rc_partition_cluster_table( >>>>>>>>>>>>> id int, >>>>>>>>>>>>> event_id int, >>>>>>>>>>>>> user_id BIGINT, >>>>>>>>>>>>> event_date string, >>>>>>>>>>>>> intval_1 int ) >>>>>>>>>>>>> CLUSTERED BY(id) INTO 256 BUCKETS >>>>>>>>>>>>> ROW FORMAT DELIMITED >>>>>>>>>>>>> FIELDS TERMINATED BY ',' >>>>>>>>>>>>> STORED AS RCFile; >>>>>>>>>>>>> >>>>>>>>>>>>> set hive.exec.dynamic.partition=true; >>>>>>>>>>>>> set hive.exec.dynamic.partition.mode=nonstrict; >>>>>>>>>>>>> set hive.exec.max.dynamic.partitions=1000; >>>>>>>>>>>>> set hive.exec.max.dynamic.partitions.pernode=1000; >>>>>>>>>>>>> >>>>>>>>>>>>> INSERT OVERWRITE TABLE rc_partition_cluster_table Partition >>>>>>>>>>>>> (event_date) >>>>>>>>>>>>> SELECT * FROM events_details; >>>>>>>>>>>>> >>>>>>>>>>>>> why it is not working fine? >>>>>>>>>>>>> >>>>>>>>>>>>> -- >>>>>>>>>>>>> *Muhammad Hamza Asad* >>>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> -- >>>>>>>>>>>> Nitin Pawar >>>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> -- >>>>>>>>>>> *Muhammad Hamza Asad* >>>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> -- >>>>>>>>>> Nitin Pawar >>>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> -- >>>>>>>>> *Muhammad Hamza Asad* >>>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> -- >>>>>>>> Nitin Pawar >>>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>> -- >>>>>>> *Muhammad Hamza Asad* >>>>>>> >>>>>> >>>>>> >>>>>> >>>>>> -- >>>>>> Nitin Pawar >>>>>> >>>>> >>>>> >>>>> >>>>> -- >>>>> *Muhammad Hamza Asad* >>>>> >>>> >>>> >>>> >>>> -- >>>> Nitin Pawar >>>> >>> >>> >>> >>> -- >>> *Muhammad Hamza Asad* >>> >> >> >> >> -- >> Nitin Pawar >> > > > > -- > *Muhammad Hamza Asad* > -- Nitin Pawar
