sqoop抽取数据到hive上

sqoop在抽取数据到hive上的时候首先要建立hive内部表
hive 启动命令：
$HIVE_HOME/bin/hive
hive 退出命令：
quit;
hive新增字段：
alter table outpatient_drug add columns(drug_amount float);

hive 创建hbase内部表：
其中sehr是创建表时的表空间。
create table sehr.outpatient(
last_update_dtime string,
org_code string,
patient_id string,
outpat_form_no string,
visit_org_name string,
visit_org_code string,
visit_dept_name string,
visit_dtime string,
referral_mark string,
referral_mark_name string,
consult_question string,
health_service_demand string,
health_problem_eval string,
treatment_plan string,
other_medical_treatment string,
resp_doctor_name string,
sbp string,
dbp string,
dept_code string,
doctor_code string,
doctor_title_code string,
doctor_title_name string,
reg_type string,
reg_type_name string,
sec_type_code string,
sec_type_name string,
sec_no string,
is_local_mark string,
is_local_mark_name string,
std_dept_code string,
create_time string,
upload_status string,
serial_data_no string,
data_status string,
is_correct string
)row format delimited fields terminated by ‘\t’;

创建成功后会提示创建成功。然后可以用select * from sehr.outpatient 去查询创建的表。

创建表成功后就可以用sqoop语法抽取数据了
–target-dir /user/root/sehr.dug_org 这个参数指向的目录必须是没有创建的，他在执行的过程中会自动创建

sqoop import --connect jdbc:oracle:thin:@192.168.18.91:1521:xmhealth --username “cmmi_ihr” --password “cmmi_sehr” --query “select * from dug_org where $CONDITIONS” --target-dir /user/root/sehr.dug_org --hive-import --m 1 --fields-terminated-by “\t” --hive-table sehr.dug_org --hive-overwrite

上述的示例数据字段类型都是string类型的，但在实际的工作中肯定有时间类型，数字类型的数据，因此做了调整：
sqoop import --connect jdbc:oracle:thin:@192.168.9.9:1521:xmhealth
–username “ihr” --password “cmmi” --query "
select a.LAST_UPDATE_DTIME,
a.ORG_CODE,
a.OUTPAT_FORM_NO,
a.CN_MEDICINE_TYPE_CODE,
a.CN_MEDICINE_TYPE_NAME,
a.DRUG_TYPE,
a.DRUG_NAME,
a.DRUG_FORM_CODE,
a.DRUG_FORM_NAME,
a.DRUG_USING_DAYS,
a.DRUG_USING_FREQ,
a.DRUG_DOSE_UNIT,
a.DRUG_PER_DOSE,
a.DRUG_TOTAL_DOSE,
a.DRUG_ROUTE_CODE,
a.DRUG_ROUTE_NAME,
a.DRUG_STOP_DTIME,
a.DRUG_LOCAL_CODE,
a.DRUG_STD_NAME,
a.DRUG_STD_CODE,
a.ID,
a.DRUG_TOTAL_UNIT,
a.SPEC,
a.GROUP_NO,
a.DRUG_START_DTIME,
a.DISPENSING_DTIME,
a.NOTES,
a.DRUG_TYPE_CODE,
a.CREATE_TIME,
a.UPLOAD_STATUS,
a.SERIAL_DATA_NO,
a.DATA_STATUS,
a.IS_CORRECT,
a.DRUG_LOCAL_NAME,
a.DDD_VALUE,
a.ANTIBACTERIAL_FLAG,
to_char(Dispensing_DTime, ‘hh24:mi:ss’) as dispensing_dtime_time,
to_char(Dispensing_DTime,‘yyyy-mm-dd’) as dispensing_dtime_date
from outpatient_drug_2019 a
where $CONDITIONS"
–hive-import --target-dir /user/root/zmbd.outpatient_drug12 --hive-table zmbd.outpatient_drug
–null-string ‘\N’ --null-non-string ‘\N’
–hive-drop-import-delims
–hive-overwrite -m 1
–map-column-java LAST_UPDATE_DTIME=java.sql.Timestamp,DRUG_USING_DAYS=Integer,DRUG_PER_DOSE=Float,DRUG_TOTAL_DOSE=Float,DRUG_STOP_DTIME=java.sql.Timestamp,DRUG_START_DTIME=java.sql.Timestamp,CREATE_TIME=java.sql.Timestamp,UPLOAD_STATUS=Integer,DATA_STATUS=Integer,IS_CORRECT=Float,DDD_VALUE=Float,ANTIBACTERIAL_FLAG=Integer,DISPENSING_DTIME_DATE=java.sql.Date