Geek Answers Handbook

Create a table from CSV with comma-delimited values

I am trying to create a table in Impala from CSV, which I uploaded to the HDFS directory. CSV contains comma-delimited values ​​inside quotation marks.

Example:

1.66.96.0/19,"NTT Docomo,INC.","Ntt Docomo",9605,"NTT DOCOMO, INC."
1.66.128.0/17,"NTT Docomo,INC.","Ntt Docomo",9605,"NTT DOCOMO, INC."
1.67.0.0/17,"NTT Docomo,INC.","Ntt Docomo",9605,"NTT DOCOMO, INC."
1.67.128.0/18,"NTT Docomo,INC.","Ntt Docomo",9605,"NTT DOCOMO, INC."
1.67.192.0/19,"NTT Docomo,INC.","Ntt Docomo",9605,"NTT DOCOMO, INC."

Impala documentation says this can be resolved with a suggestion ESCAPED BY. Here is my current code:

DROP TABLE IF EXISTS GeoIP2_ISP_Blocks_IPv4;

CREATE TABLE GeoIP2_ISP_Blocks_IPv4 (
  network STRING
 ,isp STRING
 ,organization STRING
 ,autonomous_system_number STRING
 ,autonomous_system_organization STRING
  )
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ESCAPED BY '\\'

LOCATION 'hdfs://.../GeoIP2_ISP_Blocks_IPv4/';

INVALIDATE METADATA GeoIP2_ISP_Blocks_IPv4;

LOAD DATA INPATH 'hdfs://.../GeoIP2_ISP_Blocks_IPv4/' 
INTO TABLE GeoIP2_ISP_Blocks_IPv4;

I also tried using the sentence ESCAPED BY '"'. In both cases, Impala takes a comma inside the quotation marks and uses it as a separator, dividing the value into two columns.

Any ideas on how to fix the code to prevent this from happening?

EDIT (6/9/2015)

, , @K S Nidhin @JTUP. , , , SERDEPROPERTIES, :

1

DROP TABLE IF EXISTS GeoIP2_ISP_Blocks_IPv4;

CREATE TABLE GeoIP2_ISP_Blocks_IPv4 (
  network STRING
 ,isp STRING
 ,organization STRING
 ,autonomous_system_number STRING
 ,autonomous_system_organization STRING
  )
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
WITH SERDEPROPERTIES ( "quoteChar" = "'", "escapeChar" = "\\" ) 

LOCATION 'hdfs://.../GeoIP2_ISP_Blocks_IPv4/';

INVALIDATE METADATA GeoIP2_ISP_Blocks_IPv4;

LOAD DATA INPATH 'hdfs://.../GeoIP2_ISP_Blocks_IPv4/' 
INTO TABLE GeoIP2_ISP_Blocks_IPv4;

2

DROP TABLE IF EXISTS GeoIP2_ISP_Blocks_IPv4;

CREATE TABLE GeoIP2_ISP_Blocks_IPv4 (
  network STRING
 ,isp STRING
 ,organization STRING
 ,autonomous_system_number STRING
 ,autonomous_system_organization STRING
  )
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ESCAPED BY '\\'
WITH SERDEPROPERTIES ( 'quoteChar' = '"', 'escapeChar' = '\\' )

LOCATION 'hdfs://.../GeoIP2_ISP_Blocks_IPv4/';

INVALIDATE METADATA GeoIP2_ISP_Blocks_IPv4;

LOAD DATA INPATH 'hdfs://.../GeoIP2_ISP_Blocks_IPv4/' 
INTO TABLE GeoIP2_ISP_Blocks_IPv4;

3

DROP TABLE IF EXISTS GeoIP2_ISP_Blocks_IPv4;

CREATE TABLE GeoIP2_ISP_Blocks_IPv4 (
  network STRING
 ,isp STRING
 ,organization STRING
 ,autonomous_system_number STRING
 ,autonomous_system_organization STRING
  )
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ESCAPED BY '\\'
WITH SERDEPROPERTIES (
   "separatorChar" = "\,",
   "quoteChar"     = "\""
)

LOCATION 'hdfs://.../GeoIP2_ISP_Blocks_IPv4/';

INVALIDATE METADATA GeoIP2_ISP_Blocks_IPv4;

LOAD DATA INPATH 'hdfs://.../GeoIP2_ISP_Blocks_IPv4/' 
INTO TABLE GeoIP2_ISP_Blocks_IPv4;

SERDEPROPERTIES, ?

EDIT (6/10/2016)

, SERDE SERDEPROPERTIES Hive ( , ) :

DROP TABLE IF EXISTS GeoIP2_ISP_Blocks_IPv4;

CREATE TABLE GeoIP2_ISP_Blocks_IPv4(network STRING
 ,isp STRING
 ,organization STRING
 ,autonomous_system_number STRING
 ,autonomous_system_organization STRING)

ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'

WITH SERDEPROPERTIES (
   'separatorChar' = ',',
   'quoteChar'     = '"',
   'escapeChar'    = '\\'
)   
STORED AS TEXTFILE;

LOAD DATA INPATH 'hdfs://.../GeoIP2_ISP_Blocks_IPv4/' 
INTO TABLE GeoIP2_ISP_Blocks_IPv4;

SERDE Impala, . Hive, , Impala.

+4
1
DROP TABLE IF EXISTS GeoIP2_ISP_Blocks_IPv4;

CREATE TABLE GeoIP2_ISP_Blocks_IPv4 (
  network STRING
 ,isp STRING
 ,organization STRING
 ,autonomous_system_number STRING
 ,autonomous_system_organization STRING
  )
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ESCAPED BY '\\'

WITH SERDEPROPERTIES (
   "separatorChar" = "\,",
   "quoteChar"     = "\""
)

LOCATION 'hdfs://.../GeoIP2_ISP_Blocks_IPv4/';

INVALIDATE METADATA GeoIP2_ISP_Blocks_IPv4;

LOAD DATA INPATH 'hdfs://.../GeoIP2_ISP_Blocks_IPv4/' 
INTO TABLE GeoIP2_ISP_Blocks_IPv4;

SERDEPROPERTIES, , ,

0

More articles:


All Articles