Data Science: Exploring Relational Database Objects Visually with Mathematica GUI and SQL

Daniel de Souza Carvalho
International Business Development Executive – Wolfram Research

(RowDefault[, (Spacer1), , (Spacer1), ])  #WolframTechConf

ABSTRACT

Mathematica and Wolfram Language can connect to basically every open or commercial databases out there, relational (SQL) or NoSQL flavors too. From small to big companies, they have relational database management systems to support CRM, ERP and other enterprise systems. Lots of that data is just sitting there waiting to be explored with a multiparadigm data science approach. Interesting patterns can be found in business data and can be combined with Wolfram-curated data and internet-available datasets too. We will show how to build visual GUI tools to connect to relational databases and navigate visually on database objects.

Nov 2, 2023 5:30 pm - 6:00 pm (-03)
https://wtc.wolfram.com/series/wtc-agenda/landing_page

Overview

About databases on business and market

Explore de database SQL + Wolfram Language

Experimenting some possibilities

Creating a GUI to explore the database

Questions

Database popularity

Business are suported by basically relational databases

https://db-engines.com/en/ranking

Relational Databases as a Data Science Tool

Survey

https://datanerd.tech/

Databases on Cloud or on Premises

Example from Google (GCP), this is basically the same for AWS, Azure, Oracle IBM and others

https://cloudsviewer.com/2021/08/25/your-google-cloud-database-options-explained/

Inspiration

Inspiration for a database GUI "Database Assistant": Mathematica Palettes

({{, , }})

https://reference.wolfram.com/language/tutorial/Views.html

My Tools for Data Science with Databases

Wolfram Language + SQL

Mathematica & Wolfram Language

MySQL (or Oracle, MS SQL Server, etc...)

MySQL Workbench, Oracle SQL Developer, TOAD, etc...

Terabytes of data on companies internal systems, and many different systems
	- OLTP (ERP, CRM, E-Commerce, Financial, Custom Systems...)
	- OLAP (Data Warehouse, Data Mart)

https://www.mysql.com/products/workbench/

Explore de database SQL + Wolfram Language

Using DatabaseLink:

Needs["DatabaseLink`"];

JDBCDrivers["MySQL(Connector/J)"]

JDBCDriverNames[] // Column

(HyperlinkURL["http://reference.wolfram.com/language/DatabaseLink/tutorial/DatabaseReference.html", http://reference.wolfram.com/language/DatabaseLink/tutorial/DatabaseReference.html])

Explore de database SQL + Wolfram Language

Using DatabaseLink:

Needs["DatabaseLink`"]

Connection to a local stage MySQL instance for test:

db = OpenSQLConnection[JDBC["MySQL(Connector/J)",
"127.0.0.1:3306/datascience"],
"Username"->"scott","Password"->"@T1ger_["]

Checking the db object:

Head[db]

db

Make a initial query to test the database connection, basic calculation:

ExternalEvaluate[db,"select 355/113 pi_aproximation"]

Another test a like to do, without using any table, just to check if the connection is OK:

ExternalEvaluate[db,
"select now() now, version() db_version, rand()*1000 random"]

ExternalSessions[]

Default schema "datascience" defined at the database connection string:

ExternalEvaluate[db,"select * from contacts"] 
Head[%]
FullForm[%%] //Shallow

We can explore the database data dictionary:

ExternalEvaluate[db,"show tables"]

Gerring details from tables:

ExternalEvaluate[db,"describe contacts"]

There are lots of details in the data dictionary:

ExternalEvaluate[db,
"select * from information_schema.tables where table_name = 'contacts'"]

Building more interesting queries, getting tables with their PKs:

tableInfo="select tab.table_schema as database_name,
       tab.table_name,
       tco.constraint_name as pk_name,
       group_concat(kcu.column_name
            order by kcu.ordinal_position
            separator ', ') as columns
from information_schema.tables tab
left join information_schema.table_constraints tco
          on tab.table_schema = tco.table_schema
          and tab.table_name = tco.table_name
          and tco.constraint_type = 'PRIMARY KEY'
left join information_schema.key_column_usage kcu
          on tco.constraint_schema = kcu.constraint_schema
          and tco.constraint_name = kcu.constraint_name
          and tco.table_name = kcu.table_name
where tab.table_schema not in ('mysql', 'information_schema', 
                                'performance_schema', 'sys')
group by tab.table_schema,
         tab.table_name,
         tco.constraint_name
order by tab.table_schema,
         tab.table_name";

(* Reference: https://dataedo.com/kb/query/mysql/list-tables-with-their-primary-keys *)

tablesPk = ExternalEvaluate[db, tableInfo]

Create a list of string with SCHEMA_NAME.TABLE_NAME.
We can see all the schemas (databases) that the user has access:

#[[1]]<>"."<>#[[2]]&/@Normal[Values[tablesPk[All,
{"database_name","TABLE_NAME"}]]]

Experimenting some possibilities

Create functions to use with a simple Database Assistent GUI

Function: databaseCellPrint

Print output resulting SQL Dataset to the notebook:

databaseCellPrint[dataExpression_]:=CellPrint[
ExpressionCell[dataExpression, "Output",CellTags->{"databaseGUIOutput"},
CellAutoOverwrite->True]];

Function: databasePing

Just to check if the connection is working fine:

databasePing[db_]:=databaseCellPrint[
ExternalEvaluate[db,"select now() now, version() db_version, 
rand()*1000 random"]]

Function: databaseVersion

Get database parameters and versions info

databaseVersion[db_]:=databaseCellPrint[ExternalEvaluate[db,
"SHOW VARIABLES LIKE '%version%'"]]

Function: databaseDescribe

Get table description

databaseDescribe[db_, schemaTable_]:=databaseCellPrint[
ExternalEvaluate[db,"DESCRIBE "<> Keys[schemaTable] <> "." <> 
Values[schemaTable]]]

Function: databaseCount

Get the number of data lines in a table:

databaseCount[db_, schemaTable_]:=databaseCellPrint[
ExternalEvaluate[db,"select count(1) as quantity from " 
<> Keys[schemaTable] <> "." <> Values[schemaTable]]]

Function: databaseExternalSessions

List the current DB sessions opened:

databaseExternalSessions[db_]:=databaseCellPrint[ExternalSessions[]]

GUI and Database

Getting all schemas and tables available to the user on the database:

tablesPk[All,{"database_name","TABLE_NAME"}]

Now organizing it in roles to help build a tree view GUI component:

Normal[tablesPk[GroupBy["database_name"],Catenate,{"TABLE_NAME"}]]

View just the schemas (databases)

DeleteDuplicates[Flatten[First[#]& /@Normal[Values[
tablesPk[All,{"database_name","TABLE_NAME"}]]]]]

Name the list of schemas and tables

dbinfo=tablesPk[All,{"database_name","TABLE_NAME"}]

Organize then, they are in a Dataset:

tablesPk[GroupBy["database_name"],Catenate,{"TABLE_NAME"}]

Now, out of the Dataset to lists of roles

Normal[Normal[tablesPk[
GroupBy["database_name"],Catenate,{"TABLE_NAME"}]]] // Column

Function: getData

Getting the table data, 100 first lines sample, from a given schema and table:

getData[schemaTable_Rule]:=ExternalEvaluate[db,
"select * from " <> Keys[schemaTable] <> "." <> Values[schemaTable] <>
 " limit 100"];

Testing it:

getData["datascience"->"contacts"]

Testing again with F1 data:

getData["f1"->"drivers"]

Head[%]

databaseCellPrint[%%]

Testing with a button:

Button["datascience"->"contacts",Panel[
databaseCellPrint[getData["datascience"->"contacts"]]]]

Function: setButton (GUI)

About the GUI, create a button

setButton[b_]:= Module[{schema = First[b]},
Button[Row[{🗂️,#}],databaseCellPrint[getData[schema-> #]],
Appearance->None]&/@ Last[b]]

Testing the GUI layout

Grid[{{A,Button[1]},
{SpanFromAbove,Button[2]},
{SpanFromAbove,Button[3]},
{SpanFromAbove,Button[4]}},Frame-> All]

Function: CreteOpenerView (GUI)

Create a GUI component to list the schemas and tables, select the data and show the tables:

CreateOpenerView[schemasTables_List]:=Labeled[Panel[Column[(OpenerView[{First[#],Column[setButton[#]]}])&/@schemasTables]],"Schemas and Tables",Top]

CreateOpenerView2[schemasTables_List]:=Labeled[Panel[
Column[(OpenerView[{First[#],Column[setButton[#]]}])&/@schemasTables]],
"Schemas and Tables",Top]

Testing the GUI

Panel[CreateOpenerView2[Normal[Normal[tablesPk[
GroupBy["database_name"],Catenate,{"TABLE_NAME"}]]]]]

Another GUI test

Panel[Grid[{{CreateOpenerView2[Normal[Normal[tablesPk[
GroupBy["database_name"],Catenate,{"TABLE_NAME"}]]]],Button[1]},
{SpanFromAbove,Button[2]},
{SpanFromAbove,Button[3]},
{SpanFromAbove,Button[4]}},
Frame-> All]]

Testing GUI calling the functions created until now:

Module[{
currentTable="Select a table",
databse=db,
dbIcon=},
Panel[Labeled[Grid[{{CreateOpenerView2[Normal[Normal[tablesPk[GroupBy["database_name"],Catenate,{"TABLE_NAME"}]]]],
Column[{Text["Table: " <>currentTable],
Button[Row[{dbIcon,"Ping"}],databasePing[db],Alignment->Left],
Button[Row[{dbIcon,"Version"}],databaseVersion[db],Alignment->Left],
Button[Row[{dbIcon,"Sessions"}],databaseExternalSessions[db],Alignment->Left],
Button[Row[{dbIcon,"Describe"}],databaseDescribe[db, "datascience"-> "product"],Alignment->Left],
Button[Row[{dbIcon,"Count"}],databaseCount[db, "datascience"-> "product"],Alignment->Left]}]}},
Frame-> All,Alignment->Top],"Database Assistent",Top]]]

Audit the GUI code:

FullForm[%]

Put it All Toghether in One Function...

Creating all the functions needed in a DynamicModule:

Needs["DatabaseLink`"];
databaseAssistent[databaseType_String,connectionString_String, dbUser_String, dbPassword_String]:=DynamicModule[{
db,
dbTables,
tablesPk,
dbIcon=,
tbIcon =,
allTablesSQL = "select tab.table_schema as database_name,
       tab.table_name,
       tco.constraint_name as pk_name,
       group_concat(kcu.column_name
            order by kcu.ordinal_position
            separator ', ') as columns
from information_schema.tables tab
left join information_schema.table_constraints tco
          on tab.table_schema = tco.table_schema
          and tab.table_name = tco.table_name
          and tco.constraint_type = 'PRIMARY KEY'
left join information_schema.key_column_usage kcu
          on tco.constraint_schema = kcu.constraint_schema
          and tco.constraint_name = kcu.constraint_name
          and tco.table_name = kcu.table_name
where tab.table_schema not in ('mysql', 'information_schema', 
                                'performance_schema', 'sys')
group by tab.table_schema,
         tab.table_name,
         tco.constraint_name
order by tab.table_schema,
         tab.table_name"},
(* View *)


setButton[b_]:=DynamicModule[{schema = First[b]},
Button[Row[{"🗂️ ",#}],databaseCellPrint[getData[schema-> #]],Appearance->None]&/@ Last[b]];
databaseCellPrint[dataExpression_]:=CellPrint[ExpressionCell[dataExpression, "Output",CellTags->{"databaseGUIOutput"},CellAutoOverwrite->True]];
CreateOpenerView[schemasTables_List]:=Labeled[Panel[Column[(OpenerView[{First[#],Column[setButton[#]]}])&/@schemasTables]],"Schemas and Tables",Top];

(* Control *)
databaseSQLConnection[dbConnection_]:= databaseCellPrint[dbConnection];
databaseExternalSessions[]:=databaseCellPrint[ExternalSessions[]];
getData[schemaTable_]:=ExternalEvaluate[db,"select * from " <> Keys[schemaTable] <> "." <> Values[schemaTable] <> " limit 100"];
databasePing[dbConnection_]:=databaseCellPrint[ExternalEvaluate[dbConnection,"select now() now, version() db_version, rand()*1000 random"]];
databaseVersion[dbConnection_]:=databaseCellPrint[ExternalEvaluate[dbConnection,"show variables like '%version%'"]];
databaseDescribe[dbConnection_]:=databaseCellPrint[Dynamic[ExternalEvaluate[dbConnection,"describe "<> tb]]];
databaseCount[dbConnection_]:=databaseCellPrint[Dynamic[ExternalEvaluate[dbConnection,"select count(1) as quantity from " <> tb]]]; 
databaseTableInfo[dbConnection_]:=databaseCellPrint[Dynamic[ExternalEvaluate[dbConnection,"select * from information_schema.tables where concat(table_schema, '.', table_name) = '" <> tb <> "'"]]];

(* setup: get info abot schemas and tables visibel by the user *)
Needs["DatabaseLink`"];
db = OpenSQLConnection[JDBC[databaseType,connectionString],"Username"->dbUser,"Password"->dbPassword];
tablesPk=ExternalEvaluate[db,allTablesSQL];
dbTables =(ToString[#[[1]]]<>"."<>ToString[#[[2]]]&/@Normal[Values[tablesPk[All,{"database_name","TABLE_NAME"}]]]);

(* GUI *)
Panel[Labeled[Grid[{{CreateOpenerView[Normal[Normal[tablesPk[GroupBy["database_name"],Catenate,{"TABLE_NAME"}]]]],
Column[{"",
Button[Row[{dbIcon,"Ping"}],databasePing[db],Alignment->Left],
Button[Row[{dbIcon,"Connection"}],databaseSQLConnection[db],Alignment->Left],
Button[Row[{dbIcon,"Version"}],databaseVersion[db],Alignment->Left],
Button[Row[{dbIcon,"Sessions"}],databaseExternalSessions[],Alignment->Left]
}],
Column[{"",PopupMenu[Dynamic[tb],dbTables],
Button[Row[{tbIcon,"Describe"}],databaseDescribe[db],Alignment->Left],
Button[Row[{tbIcon,"Count"}],databaseCount[db],Alignment->Left],
Button[Row[{tbIcon,"Info"}],databaseTableInfo[db],Alignment->Left]
}]}},
Frame-> All,Alignment->Top],"Database Assistent",Top]]];

Using a local staging database for data sciences:

databaseAssistent[
"MySQL(Connector/J)",
"127.0.0.1:3306/datascience",
"scott","@T1ger_["]

Next Steps...

Make it run with other databases (the data dictionary structure are different)

MySQL, MariaDB

PostgreSQL

SQLite

Oracle

MS SQL Server

Publis to Wolfram Function Repository

New features

Upload CSV files to table (importa data)

Export Data

Automated table analytics

Automated column analytics

Thank you! Questions?

Daniel Carvalho
dcarvalho@wolfram.com