The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Accessing a Partitioned ORC

Hi,
this is my first time here, so sorry for any mistake.

 

I am running Vertica 7.2 and need to map some external tables based on ORC files which reside on a Hadoop (Hive) database. I'm using the ORC reader instead of the HCatalog because is much more fast.

 

I defined the external tables as described in the documentation (create external table ... as copy ... from '/<path>/*' ORC) and this works for all the "tables" that have multiple ORC files in the same folder.

 

I also have a "table" that is partitioned by a field and the result is that I see a bunch of (I guess, I'm not keen on Hive) unpredictable sub-folders (each containing a set of ORC files) based on that partitioning criteria. The issue here is that I cannot point Vertica to the "main" folder because it will give me an error saying that it finds a folder instead of a file, but at the same time I don't know how many "sub-folders" we have (supposing I want to create a different external table for each partition).

 

I cannot find so much documentation on these ORC files, did anybody faced this problem before?

 

Any help is appreciated
Stefano

Comments

  • You can read nested folders by specifying a "/*" for each sub-directory.

     

    create external table ... as copy ... from '/<path>/*/*' ORC

    Assuming you have a single partition column in Hive, the above syntax will read into all the sub-directories in the partition folder.

    Does this solve your problem ?

     

  • And if you have more than one layer of partitioning, add wildcard directories to the path.  For example, if you have two partition columns, use '/path/*/*/*'.  The number of wildcards should always be one more than the number of levels of partitioning in the directory tree.

     

  • Hi Deepak and Monica,
    thank you very much for your solutions! You both explained really well how it works.

    I'm going to try it immediatly.

    Best regards
    Stefano

     

     

     

Leave a Comment

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