Elasticsearch 6.2导入MySQL数据后自动补全问题解决方案咨询

Hey there! Let’s work through this autocomplete issue you’re hitting with Elasticsearch 6.2 and Logstash. I’ve dealt with similar setup quirks before, so here are two solid solutions to get your search bar suggestions working:

Option 1: Configure Logstash to Use Completion Fields (Recommended) #

The completion suggester is built specifically for fast autocomplete, but it needs a dedicated field type and structured data. Here’s how to set this up end-to-end:

Step 1: Create a Custom Index Mapping in Elasticsearch #

First, you’ll need to manually define your index mapping to include completion fields for extid and name. Run this in Kibana Dev Tools or via curl:

PUT /your_index_name
{
  "mappings": {
    "your_type_name": {
      "properties": {
        "extid": {
          "type": "completion",
          "analyzer": "standard",
          "preserve_separators": true
        },
        "name": {
          "type": "completion",
          "analyzer": "standard"
        },
        // Keep your existing fields here (e.g., any other data from MySQL)
        "email": {
          "type": "text"
        }
      }
    }
  }
}

Step 2: Update Your Logstash Pipeline #

Next, adjust your Logstash config to convert the raw string values from MySQL into the structure the completion field expects. Use the mutate filter to wrap each value in the required input object:

input {
  jdbc {
    # Your existing JDBC setup goes here
    jdbc_connection_string => "jdbc:mysql://your-db-host:3306/your-database"
    jdbc_user => "your-db-user"
    jdbc_password => "your-db-pass"
    statement => "SELECT extid, name, email FROM your_table"
  }
}

filter {
  # Format extid for completion
  mutate {
    add_field => { "[temp_extid][input]" => "%{extid}" }
    remove_field => ["extid"]
    rename => { "[temp_extid]" => "extid" }
  }

  # Format name for completion
  mutate {
    add_field => { "[temp_name][input]" => "%{name}" }
    remove_field => ["name"]
    rename => { "[temp_name]" => "name" }
  }
}

output {
  elasticsearch {
    hosts => ["http://your-es-host:9200"]
    index => "your_index_name"
    document_type => "your_type_name"
    # Critical: Disable Logstash's auto-template to preserve your custom mapping
    manage_template => false
  }
}

Don’t forget the manage_template => false line—this stops Logstash from overwriting your custom mapping with its default auto-generated one.

Step 3: Update Your Node.js Autocomplete Query #

Now you can use the completion suggester properly in your function. I’ve added fuzzy matching too, so it handles typos:

function getSuggestions(text, size){ 
  return elasticClient.search({ 
    index: indexName, 
    type: indexType, 
    body: { 
      suggest: { 
        extidSuggester: { 
          text: text, 
          completion: { 
            field: "extid", 
            size: size,
            fuzzy: {
              fuzziness: 1 // Adjust this based on how lenient you want to be
            }
          } 
        }, 
        nameSuggester: { 
          text: text, 
          completion: { 
            field: "name", 
            size: size,
            fuzzy: {
              fuzziness: 1
            }
          } 
        } 
      } 
    } 
  }); 
}

Option 2: Quick Fix with Term Suggester (No Logstash Changes) #

If you can’t rework your Logstash pipeline right now, you can tweak your existing term suggester to use the keyword sub-field that Elasticsearch automatically creates for text/string fields. This works because term suggesters match exact un-analyzed terms:

Update your Node.js function like this:

function getSuggestions(text, size){ 
  return elasticClient.search({ 
    index: indexName, 
    type: indexType, 
    body: { 
      suggest: { 
        extidSuggester: { 
          text: text+'~', 
          term: { 
            field: "extid.keyword", // Use the un-analyzed keyword sub-field
            size: size,
            fuzziness: 1
          } 
        }, 
        nameSuggester: { 
          text: text+'~', 
          term: { 
            field: "name.keyword", // Same for name
            size: size,
            fuzziness: 1
          } 
        } 
      } 
    } 
  }); 
}

Heads up: This is less performant than the completion suggester for large datasets, but it’s a quick way to get suggestions working without reconfiguring Logstash.

A Quick Note for ES 6.2 #

If you still need to search the full text of extid or name (not just autocomplete), create separate completion fields (e.g., extid_completion) instead of overwriting the original string fields. That way you get the best of both worlds!

内容的提问来源于stack exchange，提问作者phenric