一、写好需要执行的抽数脚本文件
[root@hadoop02 importDataToHbase]# vim HDR_EMR_CONTENT.sh
#! /bin/bash
sqoop import --connect 'jdbc:sqlserver://192.168.1.40; username=sa; password=mandala; databasename=mandala' --query "
SELECT
(case when LEN(a.INPATIENTID)>=7
THEN reverse(SUBSTRING(a.INPATIENTID, 4, 4))
ELSE 'zzzz' END)
+'|'+ '43136152-9'
+'|'+ a.INPATIENTID
+'|'+ 住院序数
+'|'+ A.INPATIENTID+REPLACE(REPLACE(REPLACE(ESTTIME,'-',''),':',''),' ','')
+'|'+ '43136152-9.2'as ROWKEY,
a.INPATIENTID IN_PATIENT_ID,
住院序数 VISIT_ID,
c.PARTYPE TOPIC,
'02' VISIT_TYPE_CODE,
'住院' VISIT_TYPE_NAME,
t.MEDIC_NAME CREATOR_NAME,
t.username CREATOR_CODE,
CASE WHEN LEN(convert(xml,XMLSTRUCT).value('(//创建时间)[1]', 'nvarchar(max)'))>1
then convert(xml,XMLSTRUCT).value('(//创建时间)[1]', 'nvarchar(max)')
else c.ESTTIME END CREATE_DATE_TIME,
c.ESTTIME LAST_MODIFY_DATE_TIME,
CASE WHEN PARTYPE LIKE '%查房%' THEN '查房记录'
WHEN PARTYPE LIKE '%手术%' THEN '手术记录'
WHEN PARTYPE LIKE '%病程%' THEN '病程记录'
WHEN PARTYPE LIKE '%转入记录%' THEN '转入记录'
WHEN PARTYPE LIKE '%转出记录%' THEN '转出记录'
WHEN PARTYPE LIKE '%出院记录%' THEN '出院记录'
WHEN PARTYPE LIKE '%会诊记录%' THEN '会诊记录'
WHEN PARTYPE LIKE '%交班记录%' THEN '交班记录'
WHEN PARTYPE LIKE '%接班记录%' THEN '接班记录'
WHEN PARTYPE LIKE '%抢救记录%' THEN '抢救记录'
WHEN PARTYPE LIKE '%入院记录%' THEN '入院记录'
WHEN PARTYPE LIKE '%首次%' THEN '首次病程记录'
WHEN PARTYPE LIKE '%术前小结%' THEN '术前小结'
WHEN PARTYPE LIKE '%阶段%' THEN '阶段小结'
WHEN PARTYPE LIKE '%入出院记录%' THEN '24小时内入出院记录'
WHEN PARTYPE LIKE '%入院死亡记录%' THEN '24小时内入院死亡记录'
ELSE '其他' END MR_CLASS_NAME,
(SELECT TOP 1 content_code from CONTENT_DICT where content_name=CASE WHEN PARTYPE LIKE '%查房%' THEN '查房记录'
WHEN PARTYPE LIKE '%手术%' THEN '手术记录'
WHEN PARTYPE LIKE '%病程%' THEN '病程记录'
WHEN PARTYPE LIKE '%转入记录%' THEN '转入记录'
WHEN PARTYPE LIKE '%转出记录%' THEN '转出记录'
WHEN PARTYPE LIKE '%出院记录%' THEN '出院记录'
WHEN PARTYPE LIKE '%会诊记录%' THEN '会诊记录'
WHEN PARTYPE LIKE '%交班记录%' THEN '交班记录'
WHEN PARTYPE LIKE '%接班记录%' THEN '接班记录'
WHEN PARTYPE LIKE '%抢救记录%' THEN '抢救记录'
WHEN PARTYPE LIKE '%入院记录%' THEN '入院记录'
WHEN PARTYPE LIKE '%首次%' THEN '首次病程记录'
WHEN PARTYPE LIKE '%术前小结%' THEN '术前小结'
WHEN PARTYPE LIKE '%阶段%' THEN '阶段小结'
WHEN PARTYPE LIKE '%入出院记录%'THEN '24小时内入出院记录'
WHEN PARTYPE LIKE '%入院死亡记录%' THEN '24小时内入院死亡记录'
ELSE '其他' END) MR_CLASS_CODE,
A.INPATIENTID+REPLACE(REPLACE(REPLACE(ESTTIME,'-',''),':',''),' ','') MR_CODE,
convert(xml,XMLSTRUCT).value('(//默认类型)[1]', 'nvarchar(max)') MR_CATALOG,
b.[@@实习医师] HOUSEMAN_NAME,
T1.USERNAME HOUSEMAN_CODE,
c.ESTTIME CAPTION_DATE_TIME,
t.MEDIC_DIVISION CREATOR_DEPT_NAME,
t2.ksdm CREAROR_DEPT_CODE,
c.UHID+REPLACE(REPLACE(REPLACE(ESTTIME,'-',''),':',''),' ','') FILE_UNIQUE_ID,
CASE WHEN PATINDEX('_',a.INPATIENTID)>0 THEN 1 ELSE NULL END BABY_NO,
RIGHT(a.TYPE,LEN(a.TYPE)-1) STAY_DEPT_NAME,
t3.ksdm STAY_DEPT_CODE,
t.MEDIC_NAME LAST_MODIFY_USER_NAME,
t.username LAST_MODIFY_USER_CODE,
a.DATE_REGISTER FIRST_MR_SIGN_DATE_TIME,
'YYYY-MM-DD' DEFAULT_DATE_TYPE,
'TRUE' DEFAULT_TOPIC_VISIBLE,
c.HTMLSTRUCT MR_CONTENT,
C.HTMLSTRUCT MR_CONTENT_HTML,
c.HTMLSTRUCT MR_CONTENT_TEXT,
CASE (a.DONE)
WHEN 1 THEN '已归档'
ELSE '未归档'
END CONTENT_STATUS_NAME,
a.DONE CONTENT_STATUS_CODE
FROM dbo.HOSPITAL_all a
left join dbo.HOSPITAL_GATHERING b on a.UHID=b.病人标识
left join dbo.StructuredCourseRecord c on a.UHID=c.UHID
left join MEDIC_BASE t on t.UMID=c.CREATEDOCTOR
LEFT JOIN MEDIC_BASE T1 ON b.[@@实习医师] = T1.MEDIC_NAME
LEFT JOIN DEPT_DICT T2 ON T2.ksmc = t.MEDIC_DIVISION or t2.BQMC = t.MEDIC_DIVISION
LEFT JOIN DEPT_DICT T3 ON RIGHT(a.TYPE,LEN(a.TYPE)-1) = T3.ksmc
WHERE
HIS_OUTSIDE_ID IS NOT NULL AND PARTYPE<>'入院记录'
AND c.ESTTIME >(select DateAdd(DD, -3, GETDATE()))
and \$CONDITIONS " --hbase-table HDR_EMR_CONTENT --column-family cf --hbase-row-key ROWKEY --verbose -m 1
:wq 保存退出
二、写好执行抽数脚本的文件
[root@hadoop02 importDataToHbase]# vim run_sql.sh
#! /bin/bash
echo 'start ==================' >> /root/hdr/importDataToHbase/run-logs.log
echo `date` >> /root/hdr/importDataToHbase/run-logs.log
sh /root/hdr/importDataToHbase/HDR_EMR_CONTENT.sh
echo 'end ===================' >> /root/hdr/importDataToHbase/run-logs.log
三、设置定时任务
[root@hadoop02 importDataToHbase]# crontab -e
#设定晚上两点中执行抽数脚本
0 2 * * * sh /root/hdr/importDataToHbase/run_sql.sh
查看crontab任务
[root@hadoop02 importDataToHbase]# crontab -l
0 2 * * * sh /root/hdr/importDataToHbase/run_sql.sh