Load Data from Postgres or Mysql into Elasticsearch

Load database tables into Elasticsearch

If you’re a developer working with databases such as Postgres or Mysql, you have a lot tools at your disposal for implementing search. Sometimes, you want to use a tool like Elasticsearch to aggregate information from different databases and other sources and make it available for search from a single location. In this case, you need to import data into Elasticsearch from these databases. Here’s how to use Flex.io to export data from databases into Elasticsearch

Build the Pipe: Load Database Tables into Elasticsearch

For this example, we’ll take a table from Postgres, reduce the number of fields in the table, then export it to Elasticsearch. Here’s how to do it:

  1. Table Input: Postgres

    In order to copy data from databases to Elasticsearch, you’ll need to create a connection to the database first.

    Once you have a connection, you’ll use the connection alias to read data from the Postgres table:

    input from: my-connection-alias file: tbl_contacts
  2. Reduce number of columns

    Here we’ll pull out unnecessary columns for our Elasticsearch needs. To do this, we use the select command:

    select col: surname, emailaddress, streetaddress, city, state, birthday
  3. Output to Elasticsearch

    In order to send data to Elasticsearch, you’ll need to create a connection first.

    In general, the host follows a form similar to https://12345abc.us-east-1.aws.found.io, the port is generally between 9200 and 9300, and the username and password are the HTTP basic authorization credentials used to authorize access. If your Elasticsearch service is hosted on Amazon Web Services (AWS), you may need to use a proxy server in order to expose this connection with HTTP basic authorization credentials.

    Once you have a connection, you’ll use the connection alias to add your output command:

    output to: my-connection-alias

    That’s it. Run the pipe to pull data from the database and bulk load into Elasticsearch.

Deploy the Pipe

Now that you have your pipe, you can deploy and automate it as desired:

Get Started

To use this pipe yourself, an easy way to get going is to use this TODO - Elasticsearch bulk upload template

Click on the button below to copy the pipe and modify it with your own data and commands.

TODO - Elasticsearch Bulk Upload Template