Yes dean is right. We can not create external table with dynamic partition. Here i share with u how to create external table and dynamic partition.:
*Sample Commands:::::* * * * * external table =============================================== create external table externaltable(a1 int,a2 string,a3 string,a4 string) row format delimited fields terminated by "," location '/path/to/ur/file'; Dynamic partition: =============================================== create table dynamictable(a1 int,a2 string,a3 string,a4 string) row format delimited fields terminated by ","; load data local inpath '/path/to/ur/file' into table dynamictable; create table part1(a1 int,a2 string) partitioned by(a3 string,a4 string); insert overwrite table part1 partition(a3,a4) select a.a1,a.a2 from dynamictable a; I hope u it is helpful to u.. Regards Manish On Fri, Jul 26, 2013 at 4:31 PM, Omkar Joshi <omkar.jo...@lntinfotech.com>wrote: > Hi Dean,**** > > ** ** > > Thanks for the inputs !**** > > ** ** > > I guess have to go by the tedious/hard way :P**** > > ** ** > > ** ** > > Regards,**** > > Omkar Joshi**** > > ** ** > > ** ** > > *From:* Dean Wampler [mailto:deanwamp...@gmail.com] > *Sent:* Friday, July 26, 2013 4:26 PM > *To:* user@hive.apache.org > *Subject:* Re: Hive - external (dynamically) partitioned table**** > > ** ** > > Hive doesn't support dynamically-partitioned, external tables, in part > because the rational for external is that the data already exists or will > exist outside the control of Hive. Sqoop can create internal tables > directly as it imports and I believe it handles internal, > dynamically-partitioned tables (but I'm not certain...). **** > > ** ** > > In general, for external, partitioned tables, you have to use the ALTER > TABLE command to add the partitions:**** > > ** ** > > ALTER TABLE table_name ADD PARTITION (partCol = 'value1') location 'loc1'; > **** > > ** ** > > That's obviously tedious if you have a lot of them. There's a variant for > adding many at once:**** > > ** ** > > ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION partition_spec > [LOCATION 'location1'] partition_spec [LOCATION 'location2'] ...**** > > **** > > partition_spec:**** > > : (partition_col = partition_col_value, partition_col = > partiton_col_value, ...)**** > > ** ** > > But you still have to specify every one of them.**** > > ** ** > > I think the easiest solution is to use bash or another scripting language > to generate the commands for you. Run "hadoop fs -lsr /root/path/of/table", > then hack the output into a script for the ALTER TABLE commands.**** > > ** ** > > HTH,**** > > Dean**** > > ** ** > > On Fri, Jul 26, 2013 at 6:00 AM, Omkar Joshi <omkar.jo...@lntinfotech.com> > wrote:**** > > I'm having a table in MySQL viz. nas_comps.**** > > select comp_code, count(leg_id) from nas_comps_01012011_31012011 n group > by comp_code;**** > > comp_code count(leg_id)**** > > 'J' 20640**** > > 'Y' 39680**** > > First, I imported data onto HDFSHadoop version 1.0.2) using Sqoop :**** > > sqoop import --connect jdbc:mysql://172.25.37.135/pros_olap2 --username > hadoopranch --password hadoopranch --query "select * from nas_comps where > dep_date between '2011-01-01' and '2011-01-10' AND \$CONDITIONS" -m 1 > --target-dir /pros/olap2/dataimports/nas_comps**** > > Then, I created an external, partitioned Hive table :**** > > /*shows the partitions on 'describe' but not 'show partitions'*/**** > > create external table nas_comps(DS_NAME string,DEP_DATE string, CRR_CODE > string,FLIGHT_NO string,ORGN string,DSTN string,PHYSICAL_CAP > int,ADJUSTED_CAP int,CLOSED_CAP int) PARTITIONED BY (LEG_ID int, month INT, > COMP_CODE string) location '/pros/olap2/dataimports/nas_comps'**** > > The partition columns are shown when described :**** > > hive> describe extended nas_comps;**** > > OK**** > > ds_name string**** > > dep_date string**** > > crr_code string**** > > flight_no string**** > > orgn string**** > > dstn string**** > > physical_cap int**** > > adjusted_cap int**** > > closed_cap int**** > > leg_id int**** > > month int**** > > comp_code string**** > > **** > > Detailed Table Information Table(tableName:nas_comps, > dbName:pros_olap2_optim, owner:hadoopranch, createTime:1374849456, > lastAccessTime:0, retention:0, > sd:StorageDescriptor(cols:[FieldSchema(name:ds_name, type:string, > comment:null), FieldSchema(name:dep_date, type:string, comment:null), > FieldSchema(name:crr_code, type:string, comment:null), > FieldSchema(name:flight_no, type:string, comment:null), > FieldSchema(name:orgn, type:string, comment:null), FieldSchema(name:dstn, > type:string, comment:null), FieldSchema(name:physical_cap, type:int, > comment:null), FieldSchema(name:adjusted_cap, type:int, comment:null), > FieldSchema(name:closed_cap, type:int, comment:null), > FieldSchema(name:leg_id, type:int, comment:null), FieldSchema(name:month, > type:int, comment:null), FieldSchema(name:comp_code, type:string, > comment:null)], location:hdfs:// > 172.25.37.21:54300/pros/olap2/dataimports/nas_comps, > inputFormat:org.apache.hadoop.mapred.TextInputFormat, > outputFormat:org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat, > compressed:false, numBuckets:-1, serdeInfo:SerDeInfo(name:null, > serializationLib:org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, > parameters:{serialization.format=1}), bucketCols:[], sortCols:[], > parameters:{}), partitionKeys:[FieldSchema(name:leg_id, type:int, > comment:null), FieldSchema(name:month, type:int, comment:null), > FieldSchema(name:comp_code, type:string, comment:null)], > parameters:{EXTERNAL=TRUE, transient_lastDdlTime=1374849456}, > viewOriginalText:null, viewExpandedText:null, tableType:EXTERNAL_TABLE)*** > * > > *But I'm not sure if the partitions are created because :***** > > hive> show partitions nas_comps;**** > > OK**** > > Time taken: 0.599 seconds**** > > **** > > **** > > select count(1) from nas_comps;**** > > returns 0 records**** > > How do I create an external Hive table with dynamic partitions?**** > > **** > > **** > > **** > > **** > > Regards,**** > > Omkar Joshi**** > > **** > > ** ** > ------------------------------ > > The contents of this e-mail and any attachment(s) may contain confidential > or privileged information for the intended recipient(s). Unintended > recipients are prohibited from taking action on the basis of information in > this e-mail and using or disseminating the information, and must notify the > sender and delete it from their system. L&T Infotech will not accept > responsibility or liability for the accuracy or completeness of, or the > presence of any virus or disabling code in this e-mail"**** > > > > **** > > ** ** > > -- > Dean Wampler, Ph.D. > @deanwampler > http://polyglotprogramming.com **** > -- MANISH DUNANI -THANX +91 9426881954,+91 8460656443 manishd...@gmail.com
