A Cure For Merged Results

[LookerExperts]

Knowledge Drop

Last tested: Jan 29, 2021
 

Let's say that we have the salary table stored in our PostgreSQL database (Payroll) and this table has the following columns: emp_no, salary, from_date, to_date. We've created a connection in Looker that uses the Payroll database.

In MySQL, we have the employees_data table that has the following fields: emp_no, birth_date, first_name, last_name, gender, and hire_date.

Now if, for example, we want to create a report with the employee's first_name, last_name, hire_date, and SUM(salary) in Looker, we'll have to create a connection to MySQL so that the employees_data database would be available in Looker then create a Merged Result.

This approach works but it has some limitations, for example:

1. We cannot save merged results as a Look, thus we won't be able to download all the result if needed or update the query in one place if it's saved to many dashboards.
2. This simple report generates two queries (one for PostgreSQL and the other for MySQL) instead of one. This can impact performance if we have a lot of users creating merged results.

SOLUTION

We could use a data wrapper that will allow us to join PostgreSQL and MySQL tables. In this example, we're going to use the mysql_fdw wrapper. Most of the configuration must be done on the database side.

We start by creating a new user in MySQL that will be used to query the tables.

CREATE USER 'pg_fdw_user'@'%' IDENTIFIED BY 'Babu_frik_21';

GRANT SELECT, INSERT, UPDATE, DELETE ON employees_data to pg_fdw_user@'%';

In PostgreSQL:

The MySQL Foreign Data Wrapper (mysql_fdw) must already be installed in the system. This extension allows us to query MySQL databases via PostgreSQL.

1. We load the wrapper in the Salary database using the command:

CREATE EXTENSION mysql_fdw;

2. Create a server object that point to MySQL server

CREATE SERVER mysql_server FOREIGN DATA WRAPPER mysql_fdw

OPTIONS (host '127.0.0.1', port '3306');

3. We need to map the database user in PostgreSQL to the user on the MySQL server.

CREATE USER MAPPING FOR looker SERVER mysql_server

OPTIONS(username 'pg_fdw_user', password 'Babu_frik_21');

4. The final step is to import the foreign schema so that all the table of the employees (MySQL) database will be created in PostgreSQL as foreign tables.

IMPORT FOREIGN SCHEMA employees FROM SERVER mysql_server INTO public;

We can now create view files in Looker based on these foreign tables (MySQL) as we would with regular tables from our salary database (PostgreSQL) then create an explore and join both views.

explore: salary {

persist_for: "24 hours"

join: employees_data {

type: left_outer

relationship: many_to_one

sql_on: ${salary.emp_no} = ${employees_data.emp_no} ;;

}

}

Here's a screenshot of the result in the explore UI:

And some additional screenshots:

This content is subject to limited support.