okky_jobs.conf

input {
      jdbc {
              jdbc_driver_library =>"C:\logstash-7.0.0\mysql-connector-java-8.0.17.jar" 
              jdbc_driver_class => "com.mysql.jdbc.Driver"
              jdbc_connection_string => "jdbc:mysql://ls-a213e1c2ec2dcbfb235022834356aaead9f9b9c2.cxhvxaign6ot.ap-northeast-2.rds.amazonaws.com:3306/okky"
              jdbc_user => "<id>"
              jdbc_password => "<password>"
              #schedule => "* * * * *"
              statement => "

                SELECT 
                  
                 r.id as id, 
                 r.city as city , 
                 REPLACE(r.district, ' ', '') as district,
                  
                 CASE 
                      WHEN r.job_type = 'FULLTIME' THEN 'Regular' 
                      WHEN r.job_type = 'CONTRACT' THEN 'Free'                                                                                             
                 END as job_type , 
                
                 CONCAT(SUBSTR(r.start_date,1,4),'-',SUBSTR(r.start_date,6,2),'-','01') as start_date, 

                 CAST(r.working_month AS SIGNED) as working_month, 
                
                 CASE 
                     WHEN r.start_date is null THEN STR_TO_DATE('0000,000,000','%Y,%m,%d') 
                     ELSE
                      DATE_ADD( DATE_FORMAT((STR_TO_DATE(CONCAT(SUBSTR(r.start_date,1,4),'-',SUBSTR(r.start_date,6,2),'-','01'), '%Y-%m-%d')),'%Y-%m-%d') , INTERVAL CAST(r.working_month AS SIGNED)  MONTH)
                 END as end_date, 
                 

                 
                 CASE 
                     WHEN (select name from okky.company where id = r.id) is null THEN '' 
                     ELSE (select name from okky.company where id = r.id) 
                 END as company_name ,
                 
                 a.title as article_title,
                 j.title as deep_title , 
                 /*j.description,*/ 
                 j.tag_string as tag_types,
                 
                 CAST(j.max_career AS SIGNED) as max_career ,
                 CAST(j.min_career AS SIGNED) as min_career,  
                 CAST(j.max_pay    AS SIGNED) as max_pay, 
                 CAST(j.min_pay    AS SIGNED) as min_pay , 
                 CASE
				 WHEN r.job_type = 'FULLTIME'
						 THEN j.max_pay * 1000000
				 WHEN r.job_type = 'CONTRACT'
					 THEN (j.min_pay + 10) * 100000
					 ELSE j.max_pay
				 END as max_pay, 
				 CASE
				 WHEN r.job_type = 'FULLTIME'
					 THEN j.min_pay * 1000000
				 WHEN r.job_type = 'CONTRACT'
					 THEN j.min_pay * 100000
					 ELSE j.min_pay
				 END as min_pay,                                        
                 d.name as name , 
                                                                           
                 c.text as text , 
                 a.view_count as view_count, 
                 a.vote_count as vote_count, 
                 
                 a.date_created as create_date,  
                 a.last_updated as update_date

                  FROM  
                       okky.recruit AS r
                           INNER JOIN
                       job_position AS j ON r.id = j.recruit_id
                           INNER JOIN
                       job_position_duty AS d ON j.duty_id = d.id
                           INNER JOIN
                       okky.article AS a ON r.article_id = a.id
                           INNER JOIN
                       okky.content AS c ON a.content_id = c.id 

                "
            }
}

filter {
      translate {
        field => "district"
        destination => "modified_disrict"
        dictionary => {
          	  "고운동" => "세종시"
          	  "부천시소사구" => "부천시"
          	  "부천시오정구" => "부천시"
          	  "부천시원미구" => "부천시"
		  "금남면" => "세종시"
		  "당진군" => "당진시"
		  "도담동" => "세종시"
		  "마산시" => "창원시마산회원구"
		  "보람동" => "세종시"
		  "소정면" => "세종시"
		  "수원시" => "수원시팔달구"
		  "아름동" => "세종시"
		  "연기면" => "세종시"
		  "연동면" => "세종시"
		  "연서면" => "세종시"
		  "영등포" => "영등포구"
		  "전의면" => "세종시"
		  "조치원읍" => "세종시"
		  "종촌동" => "세종시"
		  "진해시" => "창원시진해구"
		  "창원시" => "창원시의창구"
		  "천안시" => "천안시서북구"
		  "청원군" => "청주시청원구"
		  "한솔동" => "세종시"
		  
        }
		fallback => "%{district}"
      }
      
    mutate {
        split => ["tag_types", ","]
    }
    
    mutate {
        lowercase => [ "tag_types" ]
    }
}



output {
  elasticsearch {
          hosts  => ["http://localhost:9200"]
          index => "okky_job"
  }
  
  stdout {
          codec => "dots"
  }
}