0
$\begingroup$

I need to create a table in hive (or Impala) by reading from a csv file (named file.csv), the problem is that this csv file could have a different number of columns each time I read it. The only thing I am sure of is that it will always have three columns called A, B, and C.

For example, the first csv I get could be (the first row is the header):

 ------------------------
|  X | Y | A | Z | B | C |
 ------------------------
|  1 | 2 | 3 | 4 | 5 | 6 | 

and the second:

 ------------
|  C | A | B | 
 -------------
|  1 | 2 | 3 | 

And I need to store this in a table, maybe an external table. Something like this:

CREATE EXTERNAL TABLE file (A STRING, B STRING, C STRING)
AS
SELECT A, B, C
USING HEADER
LOCATION 'input/loading/';

That obviously does not work. Any ideas?

$\endgroup$
2
  • $\begingroup$ Will you do any transformations before writing into an external table? I am asking that because you have a pyspark tag in the question. You can do some transformations in pyspark to your dataframes and write them to external tables. $\endgroup$
    – Danny
    Sep 25 '19 at 17:21
  • $\begingroup$ I don't need to do any transformation, just to exclude the other columns. I would prefer a solution without pyspark. $\endgroup$ Sep 26 '19 at 7:42
0
$\begingroup$

Hive's RegexSerDe which is often used to process logs could be used in your use case. You could use the regex to extract the first 3 columns from a line

CREATE EXTERNAL TABLE file (A STRING, B STRING, C STRING)
AS
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe' 
    WITH SERDEPROPERTIES 
      ('input.regex'='^(\\w+)\\t(\\w+)\\t(\\w+)(.*)')
LOCATION 'input/loading/'

The regex included is using pattern groups (\w+) to extract the first 3 columns and the last group (.*) is anything else which may be in the line

See more details here Community Article and Official Documentation

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.