Loading Multiple Tables from Multiple Schemas from MySQL to R, and Merge all the Tables

Question

Before I begin I know that these questions exist: Get an R dataframe with merged values from multiple MySQL tables and Best approach to combine multiple MySQL tables in R. But I can't use them for my problem.

I have a list of schemas (let's say 80 schemas) in a MySQL database with a specific ProductID such as shj637, jsfh748, hasd548 etc. I've added a specific table with a specific name in all those schemas as ProductINFO (so all schemas have this table). In which they all have the same column names (like ID, name, product group, etc) but different values in rows.

What I want to do is to make a loop or a function, that would load and union all ProductINFO tables from different ProductID schemas and make one data frame with all those information that contain their specific ProductIDs (as a column).

So for example if: schema A with ProductID shj637 and schema B with ProductID kly457 and 80 more! they would all have one table called ProductINFO. I want to produce:

ProductID	ID	group	otherstuff
shj637	1	Q2	ghafdj...
shj637	2	Q4	jhsdf...
shj637	3	Q1	jhfdl...
kly457	1	Q2	jshfd...
kly457	2	Q4	uiepc...
kly457	3	Q1	qapfn..
.. for all 80 ProductID schemas

This is the table structure:

CREATE TABLE `productinfo` (
`ID` int(11) DEFAULT NULL, 
`Question` text, 
`Product Answer` double DEFAULT NULL, 
`Product Code/Script (If Required)` test, 
`Product Score/Mark` double DEFAULT NULL, 
`Feedback` text, 
`ProductID` text, 
`database ID` text) 
ENGINE=MyISAM DEFAULT CHARSET=latin1

Thank you in advance!

Answer 1

In SO I've seen and learned at least one feasible method to tackle the issue with dynamic columns or tables or even databases for MySQL. The function is called PREPARED STATEMENT.

Take this query as example:

/*1*/
SET @sql := NULL;

/*2*/
SET @sql := (SELECT GROUP_CONCAT(
           CONCAT('SELECT ID, ProductID, Name, ProductGroup FROM ',table_name) 
           SEPARATOR ' UNION ALL ') FROM information_schema.tables
WHERE table_schema='my_database_name' 
/*[AND table_name ...  another condition to get only the tables you want]*/;

/*For checking purpose only. Not part of the prepared statement. Can be removed.*/
SELECT @sql;

/*3*/
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

I'll explain one by one according to the numbers:

1. The first step is to decide on a variable. In this example I'm using @sql and I set it as NULL just in case that the variable was used before and still hold some value.
2. Here we're going to set the @sql variable with a query that will be used for the execution later. The query is created from a combination of CONCAT and GROUP_CONCAT from a query over information_schema database. This is how the table names are obtained the concatenated with the final query. Note that the part /*[AND table_name .. is where you have to define the table identification. Something like:

• if for example those tables have partial matching names like tablename_shj637, tablename_jsfh748, tablename_hasd548 .. then the condition could be:

AND table_name LIKE 'tablename%'

• if the database (table_schema) holds all the table required including productinfo table, then the condition could be:

AND table_name != 'productinfo'

The final generated @sql would look something like this:

SELECT ID, ProductID, Name, ProductGroup FROM hasd548 
UNION ALL 
SELECT ID, ProductID, Name, ProductGroup FROM jsfh748 
UNION ALL 
SELECT ID, ProductID, Name, ProductGroup FROM shj637

You can check @sql variable value by running SELECT @sql after the second step. This will help with seeing whether the query being constructed correctly or not.

3. The last part is to PREPARE, EXECUTE then DEALLOCATE the prepared statement.

I've made a fiddle here : https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=be294ef5fd46ed6e04f8b03d33f34ca2 . However, the SET @sql query is a bit different because of the fiddle condition (temp db, temp tables).

Answer 2

I think you can do this in R fairly directly pasting the query together with a user-created vector of database/schema names.

A manual selection from two databases "mt1" and "mt2" from a table named "quux" would be:

dbGetQuery(mycon, "select * from mt1.quux")
#       row_names  mpg cyl disp  hp drat    wt  qsec vs am gear carb
# 1     Mazda RX4 21.0   6  160 110 3.90 2.620 16.46  0  1    4    4
# 2 Mazda RX4 Wag 21.0   6  160 110 3.90 2.875 17.02  0  1    4    4
# 3    Datsun 710 22.8   4  108  93 3.85 2.320 18.61  1  1    4    1
dbGetQuery(mycon, "select * from mt2.quux")
#           row_names  mpg cyl disp  hp drat    wt  qsec vs am gear carb
# 1    Hornet 4 Drive 21.4   6  258 110 3.08 3.215 19.44  1  0    3    1
# 2 Hornet Sportabout 18.7   8  360 175 3.15 3.440 17.02  0  0    3    2
# 3           Valiant 18.1   6  225 105 2.76 3.460 20.22  1  0    3    1
dbGetQuery(mycon, "(select * from mt1.quux) union all (select * from mt2.quux)")
#           row_names  mpg cyl disp  hp drat    wt  qsec vs am gear carb
# 1         Mazda RX4 21.0   6  160 110 3.90 2.620 16.46  0  1    4    4
# 2     Mazda RX4 Wag 21.0   6  160 110 3.90 2.875 17.02  0  1    4    4
# 3        Datsun 710 22.8   4  108  93 3.85 2.320 18.61  1  1    4    1
# 4    Hornet 4 Drive 21.4   6  258 110 3.08 3.215 19.44  1  0    3    1
# 5 Hornet Sportabout 18.7   8  360 175 3.15 3.440 17.02  0  0    3    2
# 6           Valiant 18.1   6  225 105 2.76 3.460 20.22  1  0    3    1

To do this programmatically, use

schemas <- c("mt1", "mt2")
qry <- paste(sprintf("(select * from %s.quux)", schemas), collapse = " union all ")
dbGetQuery(mycon, qry)
#           row_names  mpg cyl disp  hp drat    wt  qsec vs am gear carb
# 1         Mazda RX4 21.0   6  160 110 3.90 2.620 16.46  0  1    4    4
# 2     Mazda RX4 Wag 21.0   6  160 110 3.90 2.875 17.02  0  1    4    4
# 3        Datsun 710 22.8   4  108  93 3.85 2.320 18.61  1  1    4    1
# 4    Hornet 4 Drive 21.4   6  258 110 3.08 3.215 19.44  1  0    3    1
# 5 Hornet Sportabout 18.7   8  360 175 3.15 3.440 17.02  0  0    3    2
# 6           Valiant 18.1   6  225 105 2.76 3.460 20.22  1  0    3    1

(PS: this is intended to demonstrate using the schema.table method from one connection using a vector of known schema names. The use of select * ... is viewed by many as a little sloppy, I'm using it here for brevity.)

---

Setup

I'm using docker and mysql:8. From the command line (not R):

$ docker run --name some-mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:8

In R:

mycon <- DBI::dbConnect(odbc::odbc(), driver="MySQL ODBC 8.0 ANSI Driver",
                        host="127.0.0.1", port=3306, user="root", password="my-secret-pw")
# initially, no database
dbGetQuery(mycon, "select database()") # NA?
#   database()
# 1       <NA>

dbExecute(mycon, "create schema mt1")
dbExecute(mycon, "create schema mt2")

dbGetQuery(mycon, "select schema_name from information_schema.schemata")
#          SCHEMA_NAME
# 1              mysql
# 2 information_schema
# 3 performance_schema
# 4                sys
# 5                mt1
# 6                mt2

dbExecute(mycon, "use mt1")
# [1] 0
dbGetQuery(mycon, "select database()")
#   database()
# 1        mt1
dbWriteTable(mycon, "quux", mtcars[1:3,], create = TRUE)

dbExecute(mycon, "use mt2")
# [1] 0
dbGetQuery(mycon, "select database()")
#   database()
# 1        mt2
dbWriteTable(mycon, "quux", mtcars[4:6,], create = TRUE)