ELK setup

• payroll-db.config

input {
    jdbc {
        jdbc_connection_string => "jdbc:postgresql://db-host:5432/checkbook"
        jdbc_user => "db-user"
        jdbc_password => "db-pass"
        jdbc_validate_connection => true
        jdbc_driver_library => "/path-to/postgresql-42.2.2.jre6.jar"
        jdbc_driver_class => "org.postgresql.Driver"

        statement => "select
            payroll_id,
            amount_basis_id,
            agency_id,
            agency_code,
            agency_name ,
            agency_name || '[' || agency_id || ']' agency_name_id,
            department_id,
            department_code,
            department_name ,
            lower(department_name) || '[' || department_code || ']' department_name_code,
            fiscal_year_id,
            fiscal_year ,
            calendar_fiscal_year_id,
            calendar_fiscal_year ,
            employee_id,
            employee_name ,
            civil_service_title,
            pay_date,
            pay_date_id,
            pay_frequency,
            annual_salary,
            gross_pay,
            base_pay,
            annual_salary,
            overtime_pay,
            pay_date::varchar date_ordering,
            other_payments,
            annual_salary hourly_rate,
            case when amount_basis_id in (1) then 'SALARIED' else 'NON-SALARIED' end as payroll_type
	    from payroll limit 10000"
    }
}

output {
    elasticsearch {
        hosts =>  "localhost"
        index => "checkbook"
        document_type => "payroll"
    }
    stdout {}
}

• Mapping

PUT checkbook
{
  "settings": {
    "analysis": {
      "filter": {
        "english_stop": {
          "type": "stop",
          "stopwords": "_english_"
        },
        "health_synonym": {
          "type": "synonym",
          "synonyms": [
            "heart attack, myocardial infarction, mi, cardiac arrest, heartattack"
          ]
        },
        "autocomplete_filter": {
          "type": "edge_ngram",
          "min_gram": 1,
          "max_gram": 20
        }
      },
      "analyzer": {
        "index_analyzer": {
          "type": "custom",
          "tokenizer": "keyword",
          "filter": [
            "lowercase",
            "trim"
          ]
        },
        "search_string_analyzer": {
          "type": "custom",
          "tokenizer": "keyword",
          "filter": [
            "lowercase"
          ]
        },
        "autocomplete_analyzer": {
          "type": "custom",
          "tokenizer": "whitespace",
          "filter": [
            "lowercase",
            "trim"
          ]
        },
        "autocomplete": {
          "type": "custom",
          "tokenizer": "standard",
          "filter": [
            "lowercase",
            "autocomplete_filter"
          ]
        },
        "search_autocomplete_analyzer": {
          "type": "custom",
          "tokenizer": "whitespace",
          "filter": [
            "lowercase"
          ]
        },
        "text_general_analyzer": {
          "type": "custom",
          "tokenizer": "keyword",
          "filter": [
            "lowercase",
            "english_stop"
          ]
        },
        "text_general_query_analyzer": {
          "type": "custom",
          "tokenizer": "keyword",
          "filter": [
            "lowercase",
            "english_stop",
            "health_synonym"
          ]
        }
      }
    }
  },
  "mappings": {
    "payroll": {
      "properties": {
        "employee_id": {
          "type": "text",
          "analyzer": "index_analyzer",
          "search_analyzer": "search_string_analyzer"
        },
        "employee_name": {
          "type": "keyword"
        },
        "civil_service_title": {
          "type": "text",
          "fields": {
            "keyword": {
              "type": "keyword",
              "ignore_above": 256
            }
          },
          "analyzer": "autocomplete",
          "search_analyzer": "standard"
        },
        "pay_date_id": {
          "type": "text",
          "analyzer": "index_analyzer",
          "search_analyzer": "search_string_analyzer"
        },
        "pay_frequency": {
          "type": "keyword"
        },
        "amount_basis_id": {
          "type": "text",
          "analyzer": "index_analyzer",
          "search_analyzer": "search_string_analyzer"
        },
        "payroll_type": {
          "type": "keyword"
        },
        "annual_salary": {
          "type": "double"
        },
        "total_gross_pay": {
          "type": "double"
        },
        "base_pay": {
          "type": "double"
        },
        "overtime_pay": {
          "type": "double"
        },
        "other_payments": {
          "type": "double"
        },
        "gross_pay": {
          "type": "double"
        },
        "hourly_rate": {
          "type": "double"
        },
        "employee_name_text": {
          "type": "text",
          "analyzer": "text_general_analyzer",
          "search_analyzer": "text_general_query_analyzer"
        },
        "civil_service_title_text": {
          "type": "text",
          "analyzer": "text_general_analyzer",
          "search_analyzer": "text_general_query_analyzer"
        },
        "employee_name_autocomplete": {
          "type": "text",
          "analyzer": "autocomplete_analyzer",
          "search_analyzer": "search_autocomplete_analyzer"
        },
        "civil_service_title_autocomplete": {
          "type": "text",
          "analyzer": "autocomplete_analyzer",
          "search_analyzer": "search_autocomplete_analyzer"
        },
        "id": {
          "type": "keyword"
        },
        "domain": {
          "type": "text",
          "analyzer": "index_analyzer",
          "search_analyzer": "search_string_analyzer"
        },
        "agency_id": {
          "type": "text",
          "fields": {
            "keyword": {
              "type": "keyword",
              "ignore_above": 256
            }
          },
          "analyzer": "index_analyzer",
          "search_analyzer": "search_string_analyzer"
        },
        "agency_name_autocomplete": {
          "type": "text",
          "analyzer": "index_analyzer",
          "search_analyzer": "search_string_analyzer"
        },
        "agency_name": {
          "type": "text",
          "fields": {
            "keyword": {
              "type": "keyword",
              "ignore_above": 256
            }
          }
        },
        "agency_name_export": {
          "type": "text",
          "analyzer": "index_analyzer",
          "search_analyzer": "search_string_analyzer"
        },
        "oge_contracting_agency_name": {
          "type": "text",
          "analyzer": "index_analyzer",
          "search_analyzer": "search_string_analyzer"
        },
        "agency_name_id": {
          "type": "text",
          "analyzer": "index_analyzer",
          "search_analyzer": "search_string_analyzer"
        },
        "agency_code": {
          "type": "text",
          "analyzer": "index_analyzer",
          "search_analyzer": "search_string_analyzer"
        },
        "agency_name_text": {
          "type": "text",
          "analyzer": "text_general_analyzer",
          "search_analyzer": "text_general_query_analyzer"
        },
        "oge_agency_name": {
          "type": "text",
          "analyzer": "index_analyzer",
          "search_analyzer": "search_string_analyzer"
        },
        "oge_agency_id": {
          "type": "text",
          "analyzer": "index_analyzer",
          "search_analyzer": "search_string_analyzer"
        },
        "oge_agency_name_autocomplete": {
          "type": "text",
          "analyzer": "autocomplete_analyzer",
          "search_analyzer": "search_autocomplete_analyzer"
        },
        "agency_type": {
          "type": "text",
          "analyzer": "index_analyzer",
          "search_analyzer": "search_string_analyzer"
        },
        "fiscal_year_id": {
          "type": "text",
          "analyzer": "index_analyzer",
          "search_analyzer": "search_string_analyzer"
        },
        "fiscal_year": {
          "type": "text",
          "analyzer": "index_analyzer",
          "search_analyzer": "search_string_analyzer"
        },
        "registered_fiscal_year_id": {
          "type": "text",
          "analyzer": "index_analyzer",
          "search_analyzer": "search_string_analyzer"
        },
        "registered_fiscal_year": {
          "type": "text",
          "analyzer": "index_analyzer",
          "search_analyzer": "search_string_analyzer"
        },
        "calendar_fiscal_year_id": {
          "type": "text",
          "analyzer": "index_analyzer",
          "search_analyzer": "search_string_analyzer"
        },
        "calendar_fiscal_year": {
          "type": "text",
          "analyzer": "index_analyzer",
          "search_analyzer": "search_string_analyzer"
        },
        "department_id": {
          "type": "text",
          "analyzer": "index_analyzer",
          "search_analyzer": "search_string_analyzer"
        },
        "department_code": {
          "type": "text",
          "analyzer": "index_analyzer",
          "search_analyzer": "search_string_analyzer"
        },
        "department_name": {
          "type": "keyword"
        },
        "department_name_code": {
          "type": "text",
          "analyzer": "index_analyzer",
          "search_analyzer": "search_string_analyzer"
        },
        "department_name_text": {
          "type": "text",
          "analyzer": "text_general_analyzer",
          "search_analyzer": "text_general_query_analyzer"
        },
        "minority_type_name": {
          "type": "text",
          "analyzer": "index_analyzer",
          "search_analyzer": "search_string_analyzer"
        },
        "minority_type_id": {
          "type": "text",
          "analyzer": "index_analyzer",
          "search_analyzer": "search_string_analyzer"
        },
        "is_prime_or_sub": {
          "type": "text",
          "analyzer": "index_analyzer",
          "search_analyzer": "search_string_analyzer"
        },
        "sub_contract_id": {
          "type": "text",
          "analyzer": "index_analyzer",
          "search_analyzer": "search_string_analyzer"
        },
        "prime_vendor_id": {
          "type": "text",
          "analyzer": "index_analyzer",
          "search_analyzer": "search_string_analyzer"
        },
        "is_minority_vendor": {
          "type": "text",
          "analyzer": "index_analyzer",
          "search_analyzer": "search_string_analyzer"
        },
        "vendor_type": {
          "type": "text",
          "analyzer": "index_analyzer",
          "search_analyzer": "search_string_analyzer"
        },
        "prime_vendor_name": {
          "type": "keyword"
        },
        "contract_original_agreement_id": {
          "type": "text",
          "analyzer": "index_analyzer",
          "search_analyzer": "search_string_analyzer"
        },
        "domain_ordering": {
          "type": "text",
          "analyzer": "index_analyzer",
          "search_analyzer": "search_string_analyzer"
        },
        "date_ordering": {
          "type": "text",
          "analyzer": "index_analyzer",
          "search_analyzer": "search_string_analyzer"
        },
        "industry_type_name": {
          "type": "text",
          "analyzer": "index_analyzer",
          "search_analyzer": "search_string_analyzer"
        },
        "industry_type_id": {
          "type": "text",
          "analyzer": "index_analyzer",
          "search_analyzer": "search_string_analyzer"
        }
      }
    }
  }
}