mem_memberbase_etl Normal 0 / ID_BATCH Y ID_BATCH CHANNEL_ID Y CHANNEL_ID TRANSNAME Y TRANSNAME STATUS Y STATUS LINES_READ Y LINES_READ LINES_WRITTEN Y LINES_WRITTEN LINES_UPDATED Y LINES_UPDATED LINES_INPUT Y LINES_INPUT LINES_OUTPUT Y LINES_OUTPUT LINES_REJECTED Y LINES_REJECTED ERRORS Y ERRORS STARTDATE Y STARTDATE ENDDATE Y ENDDATE LOGDATE Y LOGDATE DEPDATE Y DEPDATE REPLAYDATE Y REPLAYDATE LOG_FIELD Y LOG_FIELD EXECUTING_SERVER N EXECUTING_SERVER EXECUTING_USER N EXECUTING_USER CLIENT N CLIENT ID_BATCH Y ID_BATCH SEQ_NR Y SEQ_NR LOGDATE Y LOGDATE TRANSNAME Y TRANSNAME STEPNAME Y STEPNAME STEP_COPY Y STEP_COPY LINES_READ Y LINES_READ LINES_WRITTEN Y LINES_WRITTEN LINES_UPDATED Y LINES_UPDATED LINES_INPUT Y LINES_INPUT LINES_OUTPUT Y LINES_OUTPUT LINES_REJECTED Y LINES_REJECTED ERRORS Y ERRORS INPUT_BUFFER_ROWS Y INPUT_BUFFER_ROWS OUTPUT_BUFFER_ROWS Y OUTPUT_BUFFER_ROWS ID_BATCH Y ID_BATCH CHANNEL_ID Y CHANNEL_ID LOG_DATE Y LOG_DATE LOGGING_OBJECT_TYPE Y LOGGING_OBJECT_TYPE OBJECT_NAME Y OBJECT_NAME OBJECT_COPY Y OBJECT_COPY REPOSITORY_DIRECTORY Y REPOSITORY_DIRECTORY FILENAME Y FILENAME OBJECT_ID Y OBJECT_ID OBJECT_REVISION Y OBJECT_REVISION PARENT_CHANNEL_ID Y PARENT_CHANNEL_ID ROOT_CHANNEL_ID Y ROOT_CHANNEL_ID ID_BATCH Y ID_BATCH CHANNEL_ID Y CHANNEL_ID LOG_DATE Y LOG_DATE TRANSNAME Y TRANSNAME STEPNAME Y STEPNAME STEP_COPY Y STEP_COPY LINES_READ Y LINES_READ LINES_WRITTEN Y LINES_WRITTEN LINES_UPDATED Y LINES_UPDATED LINES_INPUT Y LINES_INPUT LINES_OUTPUT Y LINES_OUTPUT LINES_REJECTED Y LINES_REJECTED ERRORS Y ERRORS LOG_FIELD N LOG_FIELD ID_BATCH Y ID_BATCH CHANNEL_ID Y CHANNEL_ID LOG_DATE Y LOG_DATE METRICS_DATE Y METRICS_DATE METRICS_CODE Y METRICS_CODE METRICS_DESCRIPTION Y METRICS_DESCRIPTION METRICS_SUBJECT Y METRICS_SUBJECT METRICS_TYPE Y METRICS_TYPE METRICS_VALUE Y METRICS_VALUE 0.0 0.0 10000 50 50 N Y 50000 Y N 1000 100 - 2019/05/21 19:06:33.293 - 2019/05/21 19:06:33.293 H4sIAAAAAAAAAAMAAAAAAAAAAAA= N MallSqlserver 192.168.50.32 MSSQLNATIVE Native Mall 1433 sa Encrypted 2be98afc819c69e8ea300ff228dd38f99 EXTRA_OPTION_MSSQLNATIVE.defaultRowPrefetch 200 EXTRA_OPTION_MSSQLNATIVE.readTimeout 60 FORCE_IDENTIFIERS_TO_LOWERCASE N FORCE_IDENTIFIERS_TO_UPPERCASE N INITIAL_POOL_SIZE 100 IS_CLUSTERED N MAXIMUM_POOL_SIZE 300 MSSQLUseIntegratedSecurity false MSSQL_DOUBLE_DECIMAL_SEPARATOR N PORT_NUMBER 1433 PRESERVE_RESERVED_WORD_CASE Y QUOTE_ALL_FIELDS N SUPPORTS_BOOLEAN_DATA_TYPE Y SUPPORTS_TIMESTAMP_DATA_TYPE Y USE_POOLING Y 表输入 2 Elasticsearch bulk insert 2 Y Elasticsearch bulk insert 2 ElasticSearchBulk Y 1 none crm_memberbase _doc 5000 100 SECONDS N MemberKey Y N Y MemberKey MemberKey MemberGender MemberGender MemberNickName MemberNickName MemUsualPhone MemUsualPhone MemPhone1 MemPhone1 MemPhone2 MemPhone2 MemPhone3 MemPhone3 MemUsualAddress MemUsualAddress OriginType OriginType RegisterTime RegisterTime MemUsualAreaCode MemUsualAreaCode MemUsualCityCode MemUsualCityCode MemUsualProvinceCode MemUsualProvinceCode HasOrder HasOrder RegisterYear RegisterYear RegisterMonth RegisterMonth RegisterDay RegisterDay order_join_field order_join_field

192.168.50.32

9300 cluster.name es custom.aliase.source mem_memberbase_p custom.fields.HasOrder {"type":"boolean"} custom.fields.MemDesc {"type":"text","analyzer": "ik_smart", "search_analyzer":"ik_smart","index":true} custom.fields.MemberBirthday {"type":"date","format":"yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||strict_date_optional_time||epoch_millis"} custom.fields.RegisterTime {"type":"date","format":"yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||strict_date_optional_time||epoch_millis"} custom.fields.order_join_field { "type": "join","relations": {"Member": ["Order", "OriginType"] }} custom.index.number_of_replicas 1 custom.index.number_of_shards 11

256 272 Y 表输入 2 TableInput Y 1 none MallSqlserver select * from ( select ROW_NUMBER() over (order by AConsigneePhone2 asc ) as MemberKey, case when sex= ''then 1 else sex end as MemberGender, Consignee as MemberNickName, case when AConsigneePhone2= '' then (case when AconsigneePhone1!='' then dbo.DesDecryptFixKey('123456','123456e10adc3949ba59abbe56e057f20f883e',AConsigneePhone1) else ''end) ELSE dbo.DesDecryptFixKey('123456','123456e10adc3949ba59abbe56e057f20f883e',AConsigneePhone2) end as MemUsualPhone,--电话号码 case when AConsigneePhone3= '' then '' ELSE dbo.DesDecryptFixKey('123456','123456e10adc3949ba59abbe56e057f20f883e',AConsigneePhone1) end as MemPhone1,-- case when AConsigneePhone3= '' then '' ELSE dbo.DesDecryptFixKey('123456','123456e10adc3949ba59abbe56e057f20f883e',AConsigneePhone2) end as MemPhone2,-- case when AConsigneePhone1= '' then '' ELSE dbo.DesDecryptFixKey('123456','123456e10adc3949ba59abbe56e057f20f883e',AConsigneePhone3) end as MemPhone3,-- case when ADeliveryAddress= '' then '' ELSE dbo.DesDecryptFixKey('123456','123456e10adc3949ba59abbe56e057f20f883e',ADeliveryAddress) end as MemUsualAddress, OriginType, ( select CONVERT(varchar(100), min(b.OrderTime), 20) from orders b where b.AConsigneePhone2=a.AConsigneePhone2 and b.OrderTime!='1800/01/01 00:00:00' group by b.AConsigneePhone2 ) as RegisterTime, case when b0.Level= 3 then a.RegionCode else null end as MemUsualAreaCode, case when b1.Level= 2 then b.ParentCode else b.AreaCode end as MemUsualCityCode, case when c1.Level= 1 then c.ParentCode else c.AreaCode end as MemUsualProvinceCode ,'true' as HasOrder ,convert(varchar,datepart(year,getdate())) as RegisterYear ,convert(varchar,datepart(month,getdate())) as RegisterMonth ,convert(varchar,datepart(day,getdate())) as RegisterDay ,'Member' as order_join_field from orders a left join Areamap b0 on b0.MappingCode=a.RegionCode left join AreaRegion b on b.AreaCode=a.RegionCode left join Areamap b1 on b1.MappingCode=b.ParentCode left join AreaRegion c on c.AreaCode=b.ParentCode left join Areamap c1 on c1.MappingCode=c.ParentCode where 1=1 and (a.AConsigneePhone2!= '' or a.AConsigneePhone1!= '') )a where MemberKey = 10 --order by AConsigneePhone2 asc --offset ((${page}-1)*${pagesize}) rows fetch NEXT (${pagesize}) rows only --offset 0 rows fetch NEXT 2 rows only 0 N N N

256 112 Y N