Import data from Oracle into hive using Talend Open Studio

Use the TOS to build the model and import the data from Oracle to the Local:

After the build job, form a separate program that can run:

Upload the generated zip file to the Hadoop cluster on the machine with the hive environment:

[Email protected] work]$lsfile.ZipJobinfo.propertiesJoinLib[[email protected] work]$ CDJoin/[[Email protected]Join]$lsbigdatademo Items Join_0_1.jar join_run.bat join_run.SHsrc user_activity2[[email protected]Join]$pwd/home/work/Join[[Email protected]Join]$lsbigdatademo Items Join_0_1.jar join_run.bat join_run.SHsrc user_activity2[[email protected]Join]$pwd/home/work/Join[[Email protected]Join]$./join_run.SH> User_activity22>&1&

This gives the result of the SQL statement execution, which is stored in the User_activity2.

Hive Build Table statement:

Hive>ShowCreate TableUser_activity2;okCREATE TABLE' User_activity2 ' ('user_id' String, 'user_name' string, ' sex ' string, ' age ' string, ' Reg_hosp ' string, ' reg_community ' string, ' type ' string, ' Disease_c Ode ' string, ' disease ' string, ' Doctor ' string, ' Hosp_name ' string, ' service_id ' string, ' drug_id ' string, ' Dr    Ug_name ' string, ' antibiotic ' string, ' hormone ' string, ' source ' string, ' Base_drug ' string, ' Community ' string, ' Date ' string) ROW FORMAT delimited fields TERMINATED by '|'STORED asInputFormat'Org.apache.hadoop.mapred.TextInputFormat'OutputFormat'Org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' Location'Hdfs://h1:8020/apps/hive/warehouse/cyw.db/user_activity2'Tblproperties ('Transient_lastddltime'='1435547544') Time taken:0.288Seconds, fetched: toRow (s)

Import data into the Hive table:load Data local inpath './user_activity2' into table User_activity2;

Hive>Show tables; Oktime taken:0.794secondshive>Use Cyw;oktime taken:0.256secondshive>Show Tables;okuser_activityuser_activity2time taken:0.136Seconds, fetched:2row (s) Hive> Load Data Local Inpath'./user_activity2'into table user_activity2; Loading data to table cyw.user_activity2table cyw.user_activity2 stats: [Numfiles=1, totalsize=216927483]oktime taken:10.898secondshive>Select*From user_activity2;okf805418b-335f-4ca3-a209-7c9655148146 Yu Zeying2        -Cooperation of Chengdu High-tech Zone Cooperative Community Health Service Center1Acute bronchitis Tan Wanlong Chengdu High-tech Zone Cooperative Community Health Service Center 1e972231-c65a-4ce3-9233-8EA1B18058DE Sterile Injection Water d875aacf-4723-4777-91ec-12d63732b58f0       0Other cooperation the- Geneva- -f805418b-335f-4ca3-a209-7c9655148146 Yu Zeying2        -Cooperation of Chengdu High-tech Zone Cooperative Community Health Service Center

Query statement:

SelectA. Personal ID, B. Name, B. Gender,round((sysdate-B. Date of birth)/ 365) asFage, B. File-building units,Replace(Replace(Replace(b. File-building units,'High-tech zone'),'Community Health Service Center'),               'Chengdu') to build the archive community,1  asftype, A. Problem code, A. Problem name, a. Treatment of a doctor, c. Organization name, a. Service record ID, F. Name, f.id drug ID, f. Anti- Biotin, F. Hormone drugs, Case  whenF. Sources of medicines is NULL  Then 'other' ElseF. Sources of medicinesEndsources of Medicine, classification of base drugs,Replace(Replace(Replace(c. The name of the institution,'High-tech zone'),'Community Health Service Center'),'Chengdu') Clinic community, To_char (a. Date of discovery,'YYYY-MM-DD') Date of treatment fromZlchs. Personal Issues List A, zlchs. Personal information B, zlchs. Service activity record C, (SelectD. Event ID, E. Name, e.id, H. Source of medicine, classification of drugs, G. Antibiotics, g. hormone Drugs fromzlchs. Personal expense Records D, zlchs. Fee Items Directory e, Zlchs. Pharmaceutical specifications H, zlchs. Drug characteristics GwhereD. Item ID for charges=e.id andD. Receipt Feeinch('Western medicine Fee','Herbal Fee','Chinese patent medicine fee')            andH. Drug ID (+)=e.id andH. Drug name ID=g. Drug name ID) FwhereA. Personal ID=b.ID (+)    andA. Service Record ID=C.id (+)    andA. Service Record ID=F. Event ID (+)

Import data from Oracle into hive using Talend Open Studio