how to define hive table structure using sqoop import-mainframe --create-hive-table command

2
votes

we are trying to import a flat mainframe file to load into hive table. I was able to import and load it to hive table using sqoop import-mainframe but my entire file is placed in one column and that too the column does not have a name in it.

Is there a possibility to define the table structure in sqoop import command itself?

we are using the below command to import from mainframe and load it to Hive table

sqoop import-mainframe --connect mainframe.com --dataset mainframedataset --username xxxxx -P --hive-import --create-hive-table --hive-table table1 --warehouse-dir /warehouse/

Sample mainframe data:

ASWIN|1234|1000.00
XXXX|1235|200.00
YYYY|1236|150.00

Hive table create script generated by sqoop:

CREATE TABLE Employee ( DEFAULT_COLUMN STRING) COMMENT 'Imported by sqoop on 2016/08/26 02:12:04' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001' LINES TERMINATED BY '\012' STORED AS TEXTFILE

1
sqoop
share sample data of mainframedataset dataset . what does it mean column does not have a name in it? - Dev
Also put --verbose in the end of your command (to see extended logs). It will show CREATE TABLE statement. share that statement too. - Dev
Thanks Dev for responding !!. My mainframe dataset has the following sample data in it <br/> <br/> ASWIN|1234|1000.00 <br/> XXXX|1235|200.00 <br/> YYYY|1236|150.00 <br/> i need to move this date to Hadoop hive table using sqoop command and want the table to be defined with table name as Employees and the columns as (Name String, Empid int,Amount float) <br/> but now in sqoop -hive-import --create-hive-table does not provide an option to define the column names is there a way to do so? - Aswin
i tried the --verbose statement and below is what happens while creating the table 16/08/26 02:12:04 DEBUG hive.TableDefWriter: Create statement: CREATE TABLE Employee ( DEFAULT_COLUMN STRING) COMMENT 'Imported by sqoop on 2016/08/26 02:12:04' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001' LINES TERMINATED BY '\012' STORED AS TEXTFILE All the three columns are stored in as a single column with the column name as "DEFAULT_COLUMN" - Aswin
I updated your question. This is what you intended. Right? - Dev

1 Answers

0
votes

As per Sqoop docs,

By default, each record in a dataset is stored as a text record with a newline at the end. Each record is assumed to contain a single text field with the name DEFAULT_COLUMN. When Sqoop imports data to HDFS, it generates a Java class which can reinterpret the text files that it creates.

Your psv file will be loaded to HDFS.

Now create table1 (hive table) yourself using -

CREATE TABLE table1 (Name string, Empid int,Amount float) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' LINES TERMINATED BY '\012' STORED AS TEXTFILE

Now run your sqoop import command without --create-hive-table tag. It should work.