load data from hive partition table

Hi!

 

How i can load data  into vertica from hive partition table?

i used with hdfs connector by following steps:

CREATE external TABLE UR_MD_TEST11 (GN_CDR_ID DOUBLE PRECISION,year integer,month integer,day integer,hour integer,minute integer)
dbadmin-> AS COPY source Hdfs(url='http://nd1:50070/webhdfs/v1/user/hive/warehouse/ur_reu/*/*/*/*/*/', username='hdfs') DELIMITER E'\001' ;
CREATE TABLE
dbadmin=> select count(*) from UR_MD_TEST11;
ERROR 3399: Failure in UDx RPC call InvokePlanUDL(): Error calling planUDL() in User Defined Object [Hdfs] at [src/Hdfs.cpp:965], error code: 0, message: [http://hdfs@nd1:50070/webhdfs/v1/user/hive/warehouse/ur_reu/year=2014/month=201408/day=20140808/hour=2014080806/minute=201408080615/] is a directory.

Comments

  • SruthiASruthiA Vertica Employee Administrator

    Hi,

     

         What does wildcard * indicate ?/ur_reu/*/*/*/*/*/ .  It looks like a subdirectory exists in the  HDFSFilePath ([http://hdfs@nd1:50070/webhdfs/v1/user/hive/warehouse/ur_reu/year=2014/month=201408/day=20140808/hour=2014080806/minute=201408080615) you have specified.

     

    Can you cross check??

     

    Thank you,

    Sruthi

  • yes it subdirectory because as i have write it hive partition tables with 4 keys:

    year,month,day,hour ,minute

    year=2014/month=201408/day=20140808/hour=2014080806/minute=201408080615

     

    i moved all partition keys from create external tables and it working as i expected.

    THank you :

     CREATE external  TABLE UR_MD_TEST11 (GN_CDR_ID DOUBLE PRECISION)
    dbadmin-> AS COPY  source Hdfs(url='http://nd1:50070/webhdfs/v1/user/hive/warehouse/ur_reu/*/*/*/*/*/*', username='hdfs')  DELIMITER E'\001';

     

     

     

     

  • SruthiASruthiA Vertica Employee Administrator

    Hi,

     

         Good to know it is working.

     

     

    Thank you,

    Sruthi

  • Is it possible to reference the directory-based partition implicit columns, such as:

    ... WHERE Year = '2016'

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file