Create Hive Table from HDFS files

[#1]

I sqooped serveral tables from DB2 to hadoop HDFS.  The data landed fine.  I created hive tables using the following format(follows an example table create):

-- CCCE_APL
CREATE EXTERNAL TABLE IF NOT EXISTS ccce_apl_csv(
    APL_LNK INT,
    UPDT_DTTM CHAR(26),
    UPDT_USER CHAR(8),
    RLS_ORDR_MOD_CD CHAR(12),
    RLS_ORDR_MOD_TXT VARCHAR(255) )
ROW FORMAT DELIMITED
STORED AS TEXTFILE
location '/hdfs/data-lake/master/criminal/csv/ccce_apl';

The table is successfully created.  Now when I excute the  following query(select * from ccce_apl_csv)  in Hue, I only see NULLs in all the cols:

crct_cs_csv.cs_lnkcrct_cs_csv.yrcrct_cs_csv.tpcrct_cs_csv.seqcrct_cs_csv.cnty_geo_lnkcrct_cs_csv.updt_usercrct_cs_csv.updt_dttmcrct_cs_csv.num_gnrtn_cdcrct_cs_csv.inttng_doc_lnk

  crct_cs_csv.cs_lnk crct_cs_csv.yr crct_cs_csv.tp crct_cs_csv.seq crct_cs_csv.cnty_geo_lnk crct_cs_csv.updt_user crct_cs_csv.updt_dttm crct_cs_csv.num_gnrtn_cd crct_cs_csv.inttng_doc_lnk

1	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
2	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
3	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
4	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
5	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
6	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL

Any Ideas what went wrong.  Again the data on HDFS looks fine, below sample data:

1,2014-06-15 12:49:50.06,ICPSRK ,RMN SM ,\N 2,2014-06-15 15:15:42.424,ICPSRK ,RMN SM ,\N 3,2014-06-16 18:26:29.515,ICPSRK ,RMN SM ,\N 4,2014-06-17 08:20:52.825,C79AJL ,\N,\N 5,2014-06-17 08:56:04.507,C79TEW ,RMN SM ,\N 6,2014-06-17 09:00:02.569,C79TEW ,RMN SM ,\N 7,2014-06-17 10:42:15.601,C79EDN ,OTHR ,\N 8,2014-06-17 11:12:56.218,C79EDN ,OTHR ,SEE FILE #13CR56266 9,2014-06-17 12:43:40.972,C79WBS ,OTHR ,OTHER-$5000 SEC BOND;NUPILLCS/ALC; 22,2014-06-19 14:42:22.799,C79AJL ,RMN SM ,\N

[#2]

Hi imad87,

What is the purpose of specifying "ROW FORMAT DELIMITED" without a delimiter character?

On first glance it looks like your data file contains the substring "\N" (the \ character followed by the N character) to delimit lines, instead of the "\n" character (ASCII 0xA). Can you double check the file in a hex editor?

Cheers, Lars

[#3 · Accepted solution]

Your comment gave me the clue, when I generated the script, I missed the statment that follows:                   ROW FORMAT DELIMITED, namely, -FIELDS TERMINATED BY ','.  So the correct create statement would be:

CREATE EXTERNAL TABLE IF NOT EXISTS ccce_apl(
    APL_LNK INT,
    UPDT_DTTM CHAR(26),
    UPDT_USER CHAR(8),
    RLS_ORDR_MOD_CD CHAR(12),
    RLS_ORDR_MOD_TXT VARCHAR(255) )
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE
location '/hdfs/data-lake/master/criminal/csv/ccce_apl';

Thanks.