question Re: Any way to create External Table with Specific Set of Files in Archives of Support Questions (Read Only)

Any way to create External Table with Specific Set of Files

mahender_bigdat — Sun, 03 Jun 2018 13:59:47 GMT

Hi,

We are copying files from our upstream system which are in JSON GZ format. They are following a pattern for very daily slice say YYYYMMDDHH (2018053100) they are maintianing two folders DATA and METADATA. Where DATA holds actual data and METADATA holds RowCount of that day's data.We need to create external table on top of copied data, where it only consider *.json.gz extension files only, excluding other file extensions. We dont want to copy files into another location since they are large in size. We also tried INPUT_ FILENAME .... virtual column, it didn't work. Any suggesstion for this scnearios ?

Re: Any way to create External Table with Specific Set of Files

vmurakami — Mon, 04 Jun 2018 23:03:53 GMT

Hey @Mahender S!
You can try a solution with hive + regex, using RegexSerde
Here's an example:

CREATE EXTERNAL TABLE Vini (name STRING) 
ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe' 
WITH SERDEPROPERTIES  ( "input.regex" = "*.json.gz")
LOCATION 'hdfs://myHDFSwithHA/user/hive/warehouse/test.db/vini'

Hope this helps ! 🙂

Re: Any way to create External Table with Specific Set of Files

mahender_bigdat — Wed, 20 Jun 2018 07:48:46 GMT

Thanks Vinicius. It worked.

Re: Any way to create External Table with Specific Set of Files

vmurakami — Wed, 20 Jun 2018 07:57:04 GMT

Awesome @Mahender S
As the issue is resolved, hence it will be also great if you can mark this HCC thread as Answered by clicking on the "Accept" Button. That way other HCC users can quickly find the solution when they encounter the same issue.!
Thanks 🙂