DB2 Export data and import into hive staging table

Operation:

1. Export data from DB2 to TXT

2. Change the delimiter in the file to ":"

3. Create a new table in hive (you need to set a delimiter when building a table)

4. Import data

--------

1. Export data from DB2 to TXT

Db2-x "Select Col1,col2,col3 from tbl_name where xxx with ur" >filename.txt

2. Change the delimiter in the file to ":"

Cat Filename.txt | awk ' {print $ ': ' $ $ ': ' $ >filename1.txt} '

3. Create a new table in hive (you need to set a delimiter when building a table)

ROW FORMAT Delimited delimiter set start statement

Field TERMINATED by: Sets the delimiter between fields

COLLECTION ITEMS TERMINATED by: Set a delimiter between the individual items of a complex type (array,struct) field

MAP KEYS TERMINATED by: Sets the delimiter between key value of a complex type (MAP) field

< Span style= "color: #4b4b4b; Font-family:verdana, Arial, Helvetica, Sans-seriff; font-size:13px; Line-height:19px ">hive> CREATE TABLE t (ID struct<id1:int,id2:int,id3:int>,name array<string>,xx Map <int,string>)
    > row format delimited
    > Fields terminated by ' t '
  & nbsp > Collection items terminated by ', '
    > Maps keys terminated by ': '
    > Lines Termi nated by ' \ n ';
OK
Time taken:0.287 seconds

The ROW FORMAT delimited must precede the other delimited settings, which is the top of the delimiter Set statement

LINES TERMINATED by must be set after other separators, that is, the end of the delimiter Set statement, otherwise it will error

Hive> CREATE TABLE t (ID struct<id1:int,id2:int,id3:int>,name array<string>,xx map<int,string>)
> Row Format delimited
> Fields terminated by ' \ t '
> lines terminated by ' \ n '
> Collection items terminated by ', '
> Map keys terminated by ': ';
Failed:parseexception line 5:0 missing EOF @ ' collection ' near ' \ n '

4. Import data into Hive

Load Data local inpath ' filename1.txt ' into table tbl_cdhd_usr_id_xxt_tmp

DB2 Export data and import into hive staging table