Record a Historical data Migration from oracle to hive (1)

Introduce the specific scene.

It is necessary to migrate the historical data from the existing oracle and import it into hive. There are several problems: 1. The existing oracle database cannot export the entire database, and the database server does not have permission. 2. Due to the upgrades of each data provider, the oracle database is updated by adding fields without deleting fields, while the table data structure established in hive needs to be updated according to the new one, that is to say, the data exported by oracle to the hive table needs to correspond with fields.

Decide to import oracle into another oracle first, compare the table field in oracle with the table field in hive to get the corresponding relationship, and then write the corresponding sqoop statement corresponding to the one-day import using ETL process installation account.

Import scheme from one oracle to another oracle, using the sqluldr2 tool written by God to export data, import and utilize

The sqlldr command is imported into another library table.

Specific commands for export

Sqluldr2.bin user=root/pwd@tnsname query= "select / * + parallel (8) * / * from table" head=no File=/data/oracle/tmp/file exports to a local directory

Specific commands for import

Sqlldr root/pwd@@ip:1521/tnsname control=/data/oracle/shell/xxx.ctl

Data=/data/oracle/tmp/file

Log=/data/oracle/log/file.log errors=0 rows=100000 bindsize=1024000000 direct=y

Xxx.ctl is used to specify the fields and separators of the import table as follows

Load data

Truncate into table tablename

Column delimiter for Fields terminated by','# file

TRAILING NULLCOLS

(

IP char (255)

APPKEY char (255)

PLATFORM char (255)

PORTAL char (255)

COMPANYID char (255))