Setting up Looker Elasticsearch Integration: 2 Easy Methods

Data-driven decision-making is now considered to be the backbone of business operations. Companies currently spend an immense amount of resources to understand their customers, business processes, and employees better. Hence, Business Intelligence tools have become necessary for organizations of all sizes operating across various domains.

The need for a Business Intelligence tool is apparent in any organization that wishes to derive valuable insights from its data. The insights provided by these tools help businesses get an understanding of their current market position and future goals. By leveraging the analysis of this data, businesses can interpret metrics in a much better way and use these findings to make informed strategic decisions.

There are a wide variety of Business Intelligence tools available in the market that can be leveraged by businesses to perform the necessary analyses. One of the most popular Business Intelligence platforms is Looker. This article will provide you with an understanding of how you can set up Looker Elasticsearch Integration to perform an in-depth analysis of your business data.

Introduction to Looker

Looker is a Web-based Data Visualization and Business Intelligence platform capable of transforming Graphical User Interface (GUI) based user input into SQL queries and sending it directly to the database in live mode. It houses numerous built-in visualizations that give users the ability to analyze their data in real-time. Since Looker is always working in live mode with the database, it works in harmony with Cloud-based Data Warehouses like Google BigQuery, Amazon Redshift, or Snowflake, which scale up or down as per requirements to manage the levels of user concurrency and query load.

Looker makes accessing and analyzing data spread across the Cloud easier due to its ability to query data “in-database” i.e. where it’s located and to connect directly to more than one database at a time. 

Looker houses a Data Modeling layer which is separate from the components that help visualize data. The functionalities offered by this layer can be leveraged by developers to transform data, perform numerous join operations across tables, etc., which can then be used by the business teams to perform the necessary analysis. This functionality is considered to be extremely important as it allows multiple developers to work parallelly on a model and merge their work with Github Sync.

A common use case for Looker is to analyze data in a Cloud database (such as Microsoft Azure SQL Database, MongoDB Atlas, Google Cloud Spanner, or IBM DB2) alongside data from On-premises database (such as Oracle or SAP HANA).

More information about Looker can be found here.

Introduction to Elasticsearch

Elasticsearch is one of the most popular NoSQL Database systems. In the context of data analysis, Elasticsearch is used together with the other components of what is today’s most popular log analytics platform — the ELK Stack (Elasticsearch, Logstash, and Kibana), and plays the role of data indexing and storage.

However, unlike most NoSQL databases, Elasticsearch has a strong focus on search capabilities and features. The distributed nature, speed, scalability, and ability to index any document makes Elasticsearch popular for several use cases such as website and application search backend, application performance monitoring, logging, and log analytics, infrastructure metrics and container monitoring, business analytics, etc.

More information about Elasticsearch can be found here.

Methods to Set up Looker Elasticsearch Integration

Users can set up Looker Elasticsearch Integration by implementing one of the following methods:

• Method 1: Manual Looker Elasticsearch Integration
• Method 2: Using Hevo Data to Set up Looker Elasticsearch Integration

Method 1: Manual Looker Elasticsearch Integration

To follow this guide, you will need the following:

• Access to a Linux Server.
• Access to a Looker environment.
• An existing Elasticsearch installation.
• Access to a free version of MySQL. You can download a free version of MySQL from here.

The general steps for setting up Looker Elasticsearch integration are as follows:

• Looker Elasticsearch Integration Step 1: Exporting Data from Elasticsearch Indices in CSV Format
• Looker Elasticsearch Integration Step 2: Creating a Target Database and Table in MySQL
• Looker Elasticsearch Integration Step 3: Loading CSV File into MySQL Table
• Looker Elasticsearch Integration Step 4: Configuring MySQL Database for Looker Integration
• Looker Elasticsearch Integration Step 5: Connecting Looker to MySQL Database

Looker Elasticsearch Integration Step 1: Exporting Data from Elasticsearch Indices in CSV Format

Logstash is an Open-source Server-side Data Processing pipeline for moving data across different data stores. In Logstash, you build pipelines, which define the flow of data from a source (where the data is read) to a stash (where the data is written). Logstash uses data access and delivery plugins to communicate with these resources.

Users can export data using Logstash by implementing the following steps:

• Install Logstash by running the following command:

• Test the installation by running the following command:

cd logstash-7.6 2
bin/logstash -e "input { stdin { } } output { stdout {} }"

• To use Logstash, you need to install 2 Logstash plugins i.e. Elasticsearch Input plugin and the CSV Output plugin. To install the plugins, use the following commands:

bin/logstash-plugin install logstash-input-elasticsearch
bin/logstash-plugin install logstash-output-csv

The Install Successful message should appear in the console.

• Create a configuration file, output-csv.conf in your logstash-7.6.2 folder. The Input will be the Elasticsearch connection and query while the output will contain the CSV config. Paste the following content in the file:

input {
 elasticsearch {
    hosts => "localhost:9200"
    # elastic index name
    index => "your_index_name"
    query => '
    {
	    "query": {
		    "match_all": {}
	    }	
    } 
  '
  }
}
output {
  csv {
    # elastic field name
    fields => ["field1", "field2", "field3", "field4", "field5"]
    # This is the path where you store the output.   
    path => "/temp/csv-export.csv"
  }
}

filter {
  mutate {
    convert => {
		"lat" => "float"
		"lon" => "float"
		"weight" => "float"
		}
  }
}

The input contains the name of the index that you’re querying. You then have to declare all the fields that you need in the output section and the path where you want to store the CSV file inside the CSV section. The Logstash-output-csv plugin returns a scientific value for any float field on Elasticsearch. The filter section converts them again to float values.

• After that, run the following commands:

bin/logstash -f /path/to/output-csv.conf

The above command will generate CSV output matching the query. Depending on the amount of data, your CSV files will be populated with new values in a few minutes. Check that the csv-export.csv file is located in the specified path.

Looker Elasticsearch Integration Step 2: Creating a Target Database and Table in MySQL

Now that you have exported your data from Elasticsearch, the next step is to import the data in MySQL.

• Using the MySQL client, log in to the MySQL Server using the root user.

>mysql -u root -p
Enter password: ********

Type the password for the root user and press Enter.

• Issue the CREATE DATABASE command with the database name, in this case, es-db, and press Enter:

mysql> CREATE DATABASE es-db;
Query OK, 1 row affected (0.12 sec)

• Select the database for use. Creating a database in MySQL does not select it for use; you must explicitly select the newly created database by issuing the USE database command as follows:

mysql> USE es-db;
Database changed

• Create a MySQL table for storing your data. The columns in the table need to match the data from the CSV file you exported from Elasticsearch. In this case, you will create the table by issuing the following command:

CREATE TABLE es_index1 (
            id INT NOT NULL AUTO_INCREMENT,
            field1 VARCHAR(255) NOT NULL,
            field2 VARCHAR(255) NOT NULL,
            field3 VARCHAR(255) NOT NULL,
            field4 VARCHAR(255) NOT NULL,
            field5 VARCHAR(255) NOT NULL,
            PRIMARY KEY (id)
);

Looker Elasticsearch Integration Step 3: Loading CSV File into MySQL Table

Load the data from the CSV file into the table using the LOAD DATA LOCAL command:

LOAD DATA LOCAL '/temp/csv-export.csv'
INTO TABLE es_index1
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '/n'
IGNORE 1 ROWS;

Change the path and file name to match the path and filename of your CSV file.

Looker Elasticsearch Integration Step 4: Configuring MySQL Database for Looker Integration

For your database to work with Looker, you need to create and grant the required access to the Looker user using the following instructions:

• Configure the MySQL database to use the mysql_native_password plugin to authenticate to Looker through the JDBC driver. This can be done by starting the process with the flag:

default-auth=mysql_native_password

• Next, set the property in the my.cnf configuration file in the MySQL Server directory.

[mysqld]
default-authentication-plugin=mysql_native_password

• Issue the following statements, replacing passwd with your desired password:

CREATE USER looker IDENTIFIED WITH mysql_native_password BY 'passwd';
GRANT SELECT ON database_name.* TO 'looker'@'%';

• Enable the creation of Persistent Derived Tables (PDTs) by setting up a temporary schema in MySQL. To create a temporary database and to grant the required privileges to the Looker user, issue the following commands:

CREATE SCHEMA looker_tmp;
GRANT
  SELECT,
  INDEX,
  INSERT,
  UPDATE,
  DELETE,
  CREATE,
  DROP,
  ALTER,
  CREATE TEMPORARY TABLES
ON looker_tmp.* TO 'looker'@'%';

• Set the max_allowed_packet variable to its maximum value i.e.1G to prevent SQLException: Packet for query is too large errors. To do this, open the my.ini file located in the MySQL server install directory and search for the “max_allowed_packet” parameter. Set it to:

max_allowed_packet=1G

• Restart the MySQL Server for the changes to take effect.

Looker Elasticsearch Integration Step 5: Connecting Looker to MySQL Database

• Select Connections from the Database section in the Looker Admin panel.
• On the Connections page, click the Add Connection button.
• Looker will display the Connection Settings page.
• Set the name of the connection as you would like to refer to it.
• Select MySQL as your SQL dialect.
• Enter your Database Hostname and Port Number that Looker will use to connect to your database host.
• Enter es-db as the name of your database.
• Enter root as your username and also enter the password for the root user.
• Check the Persistent Derived Tables box to enable persistent derived tables.
• Leave the rest of the settings to their default values.
• Once you’ve entered the credentials, click on Test These Settings to verify that the information is correct and the database is able to connect.
• Once you have configured and tested your database connection settings, click Add Connection. Your database connection is now added to the list on the Connections page. 

You can click the Test button to test the connection, the Edit button to edit the connection, and the gear icon to perform other actions on the connection

Limitations of Manually Setting up Looker Elasticsearch Integration

The approach that you have just covered is the simplest way to consume data from Elasticsearch into Looker using only open-source tools. However, it has a couple of limitations. The limitations associated with manual Looker Elasticsearch Integration is as follows:

• This approach is ideal for a one-time load job, but in real life, data transfer is a continuous process that needs to be executed frequently. To accommodate such requirements, you will need to develop customized code to achieve this.
• This approach is resource-intensive and can hog the cluster depending on the number of indexes and the volume of data.

Method 2: Using Hevo Data to Set up Looker Elasticsearch Integration

Hevo helps you directly transfer data from Elasticsearch and various other sources to Business Intelligence tools such as Looker, Data Warehouses, or a destination of your choice in a completely hassle-free & automated manner. Hevo is fully managed and completely automates the process of not only loading data from your desired source but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. Its fault-tolerant architecture ensures that the data is handled in a secure, consistent manner with zero data loss.

Hevo takes care of all your data preprocessing needs and lets you focus on key business activities and draw a much powerful insight on how to generate more leads, retain customers, and take your business to new heights of profitability. It provides a consistent & reliable solution to set up Elasticsearch Looker integration in real-time and always have analysis-ready data in your desired destination.

Hevo focuses on three simple steps to get you started:

• Connect: Connect Hevo with Elasticsearch and various other data sources by simply logging in with your credentials.
• Integrate: Consolidate your data from several sources using Hevo’s Managed Data Warehouse Platform and automatically transform it into an analysis-ready form.
• Visualize: Connect Hevo with your desired BI tool such as Looker and easily visualize your data to gain better insights.

Check out what makes Hevo amazing:

• Real-Time Data Transfer: Hevo with its strong integration with 100+ Sources (including 30+ Free Sources), allows you to transfer data quickly & efficiently. This ensures efficient utilization of bandwidth on both ends.
• Data Transformation: It provides a simple interface to perfect, modify, and enrich the data you want to transfer. 
• Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
• Tremendous Connector Availability: Hevo houses a large variety of connectors and lets you bring in data from numerous Marketing & SaaS applications, databases, etc. such as HubSpot, Marketo, MongoDB, Oracle, Salesforce, Redshift, etc. in an integrated and analysis-ready form.
• Simplicity: Using Hevo is easy and intuitive, ensuring that your data is exported in just a few clicks. 
• Completely Managed Platform: Hevo is fully managed. You need not invest time and effort to maintain or monitor the infrastructure involved in executing codes.
• Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.

Conclusion

This article provided you with a step-by-step guide on how you can set up Elasticsearch Looker Integration manually or using Hevo. There are certain limitations associated with the manual method. If those limitations are not a concern to your analysis, then the manual method is the best option but if it is, then you should consider using automated Data Integration platforms like Hevo.

Hevo helps you directly transfer data from a source of your choice to a Data Warehouse, Business Intelligence tools such as Looker, or desired destination in a fully automated and secure manner without having to write the code. It will make your life easier and make data migration hassle-free. It is User-Friendly, Reliable, and Secure.

Details on Hevo’s pricing can be found here. Give Hevo a try by signing up for the 14-day free trial today.