Skip to main content

MySQL Report Across Schemas (Tickler)

 

The Problem

We want to gather information about the same data entities, scattered across diverse schemas.
How to apply the same SELECT query in each schema and gather the results into a single output?

A sample Use Case: we want a list of user login names across all schemas, so we can analyze possible conflicts in moving to a centralized identity management system.

MySQL Cross-Schema Reporting

Screenshot of results of this process, from MySQL Workbench:


Our approach is to create a stored procedure that will walk through all schemas on the server, run the same query to gather the data, and put the collected data into a dedicated table in an administrative schema for future reporting and analysis.

First, let's create a new schema for this Reporting exercise, and select it as the schema in use.

CREATE DATABASE CustomReportingSPS;

USE CustomReportingSPS;


Next, create a table that will collect the results of the query as it is run across schemas.

CREATE TABLE rpt_active_users (

  DB_NAME varchar(200),

    ORG_NAME varchar(200),

    USER_LOGIN_NAME varchar(20),

    NAME_FIRST varchar(100),

    NAME_LAST varchar(200),

    ACTIVE varchar(20)  );


Next, build a stored procedure that will iterate over the schemas.

DELIMITER $$

DROP PROCEDURE IF EXISTS `query_all_schemas`$$

CREATE PROCEDURE `query_all_schemas`()

BEGIN

    -- declare variable for database name

    DECLARE dbname VARCHAR(128) DEFAULT '';

    DECLARE done INTEGER DEFAULT 0;

    -- declare NOT FOUND handler

    DECLARE CONTINUE HANDLER 

    FOR NOT FOUND SET done = 1;


The Cursor we define here allows us to omit tables that we want to skip running the query in. These include any system schemas, this reporting schema we just created, and any test schemas.

    DECLARE schema_list CURSOR FOR 

      SELECT DISTINCT SCHEMA_NAME AS `database`

      FROM `information_schema`.SCHEMATA

      WHERE SCHEMA_NAME NOT IN ( 'information_schema', 'performance_schema', 'mysql', 'sys', 'world','CustomReportingSPS' )

      ORDER BY SCHEMA_NAME;


Now let's iterate through the Cursor to get each schema in turn:

    OPEN schema_list;

    query_table: LOOP

        FETCH schema_list INTO dbname;

        IF done = 1 THEN

        LEAVE query_table;

        END IF;


Once we have the schema name in the dbname variable, create the SQL statement with concatenation. We need to select from the schema and write the data into the reporting schema created above.

        SET @s = CONCAT(

'INSERT INTO CustomReportingSPS.rpt_active_users '

'SELECT "', dbname, '", o.ORG_NAME, u.USER_LOGIN_NAME, p.NAME_FIRST, p.NAME_LAST, u.IS_ACTIVE

FROM ', dbname, '.Users u

LEFT OUTER JOIN ', dbname, '.Persons p ON p.ID = u.PERSON_ID

JOIN ', dbname, '.Organizations o'

);


With the SQL query ready let's Prepare and Execute it, then loop back to get the next schema.

        PREPARE stmt FROM @s;

        EXECUTE stmt;

        DEALLOCATE PREPARE stmt; 

    END LOOP    query_table;

    CLOSE schema_list;


And close our Stored Procedure definition

END$$

DELIMITER ;


To run this stored procedure:

CALL query_all_schemas();


These "memory tickler" posts are quick-hits to refresh my mind about little technical details that arise infrequently enough that I forget in between, and wind up doing an internet search every time. By posting them here, they will be easier to find as needed.)

Labels:

Popular posts from this blog

Git Reset in Eclipse

Using Git and the Eclipse IDE, you have a series of commits in your branch history, but need to back up to an earlier version. The Git Reset feature is a powerful tool with just a whiff of danger, and is accessible with just a couple clicks in Eclipse. In Eclipse, switch to the History view. In my example it shows a series of 3 changes, 3 separate committed versions of the Person file. After commit 6d5ef3e, the HEAD (shown), Index, and Working Directory all have the same version, Person 3.0.
Read more

Scala Collections: A Group of groupBy() Examples

Scala provides a rich Collections API. Let's look at the useful groupBy() function. What does groupBy() do? It takes a collection, assesses each item in that collection against a discriminator function, and returns a Map data structure. Each key in the returned map is a distinct result of the discriminator function, and the key's corresponding value is another collection which contains all elements of the original one that evaluate the same way against the discriminator function. So, for example, here is a collection of Strings: val sports = Seq ("baseball", "ice hockey", "football", "basketball", "110m hurdles", "field hockey") Running it through the Scala interpreter produces this output showing our value's definition: sports: Seq[String] = List(baseball, ice hockey, football, basketball, 110m hurdles, field hockey) We can group those sports names by, say, their first letter. To do so, we need a disc
Read more

Java 8: Rewrite For-loops using Stream API

Java 8 Tip: Anytime you write a Java For-loop, ask yourself if you can rewrite it with the Streams API. Now that I have moved to Java 8 in my work and home development, whenever I want to use a For-loop, I write it and then see if I can rewrite it using the Stream API. For example: I have an object called myThing, some Collection-like data structure which contains an arbitrary number of Fields. Something has happened, and I want to set all of the fields to some common state, in my case "Hidden"
Read more

How to do Git Rebase in Eclipse

This is an abbreviated version of a fuller post about Git Rebase in Eclipse. See the longer one here : One side-effect of merging Git branches is that it leaves a Merge commit. This can create a history view something like: The clutter of parallel lines shows the life spans of those local branches, and extra commits (nine in the above screen-shot, marked by the green arrows icon). Check out this extreme-case history:  http://agentdero.cachefly.net/unethicalblogger.com/images/branch_madness.jpeg Merge Commits show all the gory details of how the code base evolved. For some teams, that’s what they want or need, all the time. Others may find it unnecessarily long and cluttered. They prefer the history to tell the bigger story, and not dwell on tiny details like every trivial Merge-commit. Git Rebase offers us 2 benefits over Git Merge: First, Rebase allows us to clean up a set of local commits before pushing them to the shared, central repository. For this
Read more

Code Coverage in C#.NET Unit Tests - Setting up OpenCover

The purpose of this post is to be a brain-dump for how we set up and used OpenCover and ReportGenerator command-line tools for code coverage analysis and reporting in our projects. The documentation made some assumptions that took some digging to fully understand, so to save my (and maybe others') time and effort in the future, here are my notes. Our project, which I will call CEP for short, includes a handful of sub-projects within the same solution. They are a mix of Web APIs, ASP MVC applications and Class libraries. For Unit Tests, we chose to write them using the MSTest framework, along with the Moq mocking framework. As the various sub-projects evolved, we needed to know more about the coverage of our automated tests. What classes, methods and instructions had tests exercising them, and what ones did not? Code Coverage tools are conveniently built-in for Visual Studio 2017 Enterprise Edition, but not for our Professional Edition installations. Much less for any Commun
Read more