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:
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.
Salary
database using the command:CREATE EXTENSION mysql_fdw;
CREATE SERVER mysql_server FOREIGN DATA WRAPPER mysql_fdw
OPTIONS (host '127.0.0.1', port '3306');
CREATE USER MAPPING FOR looker SERVER mysql_server
OPTIONS(username 'pg_fdw_user', password 'Babu_frik_21');
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.