Load Data from Postgres or Mysql 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:
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
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
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:
- Schedule the pipe to run automatically
- Use the JSON with an AJAX call via the API
- Call the pipe manually via the Command Line Interface
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.