CODEWITHSUNDEEP
sqlhiveimpala
Stanislav Jirák
Stanislav Jirák

Reputation: 485

Created external table but it's empty

I want to create an external table from a .csv file I uploaded to the server earlier. In Bline (shell for Hive), I tried running this script:

CREATE EXTERNAL TABLE c_fink_category_mapping (
    trench_code string,
    fink_code string
)
row format delimited fields terminated by '\073' stored as textfile 
location '/appl/trench/dev/data/in/main/daily_wf/fink_category_mapping'
TABLEPROPERTIES ('serialization.null.format' = '')
;

which creates the table w/o any error byt the table itself is empty. Help would be appreciated. My textfile is populated with data.

Upvotes: 0

Views: 869

Answers (2)

Mihg
Mihg

Reputation: 1

response provided above seems to be correct:

CREATE EXTERNAL TABLE c_fink_category_mapping (
    trench_code string,
    fink_code string
)
ROW FORMAT SERDE 
      'org.apache.hadoop.hive.serde2.OpenCSVSerde' 
WITH SERDEPROPERTIES ( 
  'quoteChar'='"', 
  'separatorChar'=',') 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  '/appl/trench/dev/data/in/main/daily_wf/fink_category_mapping';

This will create the table using a comma as the delimiter, which should correctly parse the data in your CSV file and populate the table with the data from the file. You can also specify a different delimiter character, such as '\t', if that is more appropriate for your data.

Upvotes: 0

Liutprand
Liutprand

Reputation: 557

First, check if the location path is correct.

Then try with this configuration:

CREATE EXTERNAL TABLE c_fink_category_mapping (
    trench_code string,
    fink_code string
)
ROW FORMAT SERDE 
      'org.apache.hadoop.hive.serde2.OpenCSVSerde' 
WITH SERDEPROPERTIES ( 
  'quoteChar'='"', 
  'separatorChar'=',') 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  '/appl/trench/dev/data/in/main/daily_wf/fink_category_mapping';

Upvotes: 1

Related Questions