Data Science: Processing Relational Data for Business or Research

Daniel  Carvalho , International Business Developer Executive

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

ABSTRACT

From small to big companies, operations work on top of traditional relational databases (RDBMS) and SQL is a lingua franca in business. To keep track of complex activities with vast amounts of transactions, lots of data is produced with ERPs, CRMs, BI, financial systems, RH, sales and internally built custom enterprise systems too. We can explore this information better to find patterns and insights for companies. The Wolfram Language can be used to import, process and combine all this info and relate it to real-world curated data. This data science hype brings P&D to many companies that can add value to the business and avoid risks.

There is good value in small data

Valuable information for business improvement is just sitting there in companies’ relational databases.

We can explore this information with Wolfram Language and SQL, and additionally combine it with real-world Wolfram curated data.

Small Data can be processed in pretty standard IT infrastructure, or in a notebook (PC)

Big Data requires a huge amount of space and can be explored just in the cloud

"Gartner Says 70% of Organizations Will Shift Their Focus From Big to Small and Wide Data By 2025"

https://www.gartner.com/en/newsroom/press-releases/2021-05-19-gartner-says-70-percent-of-organizations-will-shift-their-focus-from-big-to-small-and-wide-data-by-2025

Data Lake (raw files) & Data Warehouse (OLAP SQL)

https://martinfowler.com/bliki/DataLake.html  - Thoughtworks

Wolfram Data

It is not needed to download entire terabytes of data from Wolfram. Or pay to keep terabyte of big data at the cloud.
With Wolfram data you can use just what you need, and ask for computation from Wolfram servers (Wolfram Cloud).

#-> #["Area"]& /@EntityList[%] // Column

#-> #["GDP"]& /@EntityList[%] // Column

Risks of Spreadsheets

Spreadsheets are everywhere in business!!
Scientific research and business has been suffering from hidden spreadsheets issues!!

Spreadsheets bring risks to the table!!

Spreadsheets are not databases!!!

😱

12 MODELLING HORROR STORIES & SPREADSHEET

https://www.f1f9.com/wp-content/uploads/2019/04/F1F9_Dirty-Dozen_E-Book_01a.pdf

Database Market-share

There are databases appropriate for different use cases.

Wolfram Language can connect basically to all databases out there.

Popular free databases for Data Science to use at the local machine (small data) are SQLite, PostgreSQL and MySQL (or MariaDB). And more enterprise level Oracle, DB2, HANA, Hive and MS SQL Server.
Any database can be used for P&D and Data Science with Wolfram Language, at local machine, servers (On-premise) or at the cloud.

Some types of popular databases

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

Importing Data to Database Tables

I would be very convenient to have a kind of “supper function” to load CSV files to databases, just digest automatically the data to a table, in order to accelerate Data Science pipeline.

Moreover, big CSV data files like > 1TB can be loaded in memory in a Dataset for example. Wolfram Language can be used to load this data to a database more automatically.

We have two examples for:

MySQL

SQLite

Additional tools:

SQLiteStudio - https://sqlitestudio.pl/

MySQL Workbench - https://www.mysql.com/products/workbench/

Digest the CSV data to a SQLite database table

It is convenient to use the local relational database (on-premisses), because they can handle terabytes of data quicky

Load the first 2 lines of the CSV

First line needs to be the header, for the table fields names

Second line will be the reference for database column type, for SQLite (and MySQL): (HyperlinkURL["http://reference.wolfram.com/language/ref/databaseconnection/SQLite.html", http://reference.wolfram.com/language/ref/databaseconnection/SQLite.html])

Real

Integer

Text (String)  >> NOT VARCHAR

This process just check the first line of data (the 2 CSV line) in order to decide the appropriate data type for that column, it can ve risk but it is a lot faster than analyze all the data

Create the table

Load all the data, line by line

Here we go...

Using regexp (Regular Expression) to check the data type, checking just the first line of data:

Integer

StringMatchQ["2300",RegularExpression["^\\d+$"]]

Real

StringMatchQ["233.33",RegularExpression["^-?\\d+\\.+\\d*$"]]

Text (String)

Otherwise is a considered string (text), and so, if it is not a integer or a real number it is going to be set as string

It is important to observe that we do not know the text line sizes upfront, and so it is better to use field type “text” instead of “varchar”

checkDataType[data_String]:= 
Which[
StringMatchQ[data,RegularExpression["^-?\\d+$"]],"integer",
StringMatchQ[data,RegularExpression["^-?\\d*\\.+\\d+$"]],"real",
True,"text"];

Date data type should be analysed case by case, because it is hard to define automatically what is the date and time format.

Teste the data type check:

#-> checkDataType[#] &/@ {"Hello","3.14","2048","Daniel","1974",".23","-6.7","-10","-.33","Wolfram"} // Column

Test the column names extraction for the CSV first line (header)

header=StringReplace[ToLowerCase[StringSplit[ReadLine["wgi.csv"],","]]," "-> "_"]

Fix header, cant start with number...

header=Join[Take[header,{1,4}],"y"<>#&/@Take[header,{5,-1}]]

Test the first line of data, the 2 line at CSV file, splinting by comma

firstDataLine=StringSplit[ReadLine["wgi.csv"],","]

We can use regexp to break down the first line of data, to process correctly comma in strings between double quotes

firstDataLine=StringSplit[ReadLine["wgi.csv"],RegularExpression[",(?=([^\"]*\"[^\"]*\")*[^\"]*$)"]]

Test the data type check for the first line of data

dataType=checkDataType/@ firstDataLine

View the data with an “x-ray”

FullForm[%10]

Close["wgi.csv"]

Check type validation with one line of data...

#-> checkDataType[#] &/@ firstDataLine// Column

Example of a SQL to create table for SQLite:

create table contacts (
	contact_id integer primary key,
	first_name text not null,
	last_name text not null,
	email text not null unique,
	phone text not null,
	value real
);

SQLite online documentation is awesome:
https://www.sqlite.org/docs.html

Build the SQL to create the table:

createTable ="create table " <> "wsi" <> " ( " <>
StringTake[StringJoin[Insert[#," ", 2]& /@ Append[","]/@Partition[Riffle[header,dataType],2]],{1,-2}] <>
")"

Hint: Yes! Code that create code usually is ugly! But very powerful to automate computation!

dsdb= DatabaseReference[<|
"Backend"->"SQLite",
"Name"->File["ds-db.sqlite"]
|>]

Test SQLite connection by getting the current version and database date:

ExternalEvaluate[dsdb, "select date() now, sqlite_version() version"]

Create table at SQLite

ExternalEvaluate[dsdb, createTable]

Of course it is empty:

ExternalEvaluate[dsdb, "select count(1) from wsi"]

(* ExternalEvaluate[dsdb, "delete from wsi"] *)

View table structure (describe)

ExternalEvaluate[dsdb, "pragma table_info('wsi')"]

Load data line by line:

wsiInsertSql = "insert into wsi values ("<>
ReadLine["wgi.csv"] <>")"

ExternalEvaluate[dsdb, wsiInsertSql]

wsiInsertSql

FullForm[wsiInsertSql]

ExternalEvaluate[dsdb, "insert into wsi values (\"Afghanistan\",\"AFG\",\"Control of Corruption: Percentile Rank, Lower Bound of 90% Confidence Interval\",\"CC.PER.RNK.LOWER\",0,0,0,0,0,0,0,0.485436887,0,0,0,0,0,0,0,0.95693779)"]

Some CSV datasets from the hell, uses comma inside simple or double quote text fields!! It can fixed by regexp, breaking down the record by comma but not in cases where the comma is surrounded by double quotes, for example:

Wolfram Language supports regular expressions bountifully. Regexp has a very peculiar syntax, you can master it and a couple of minutes and define the pattern  you need. And faster then you learned, you may forget all the regexp syntaxes, and the next day the regexp will look like a terrible beast! The documentation if there for our help! :-)

Now lets read the CSV file line by line, and break the CSV by comma...
Create the "insert" code with TemplateApply
And run the SQL at SQLite for test...

StringSplit[ReadLine["wgi.csv"],RegularExpression[",(?=([^\"]*\"[^\"]*\")*[^\"]*$)"]]

TemplateApply["insert into wsi values ("<>StringJoin[Riffle[Table[If[type=="text","\"``\"" ,"``"],{type,dataType}],","]]<>")",%]

ExternalEvaluate[dsdb,%]

FullForm[%%]

Combining it in just one line of code, if no output it is ok, no error!

ExternalEvaluate[dsdb,TemplateApply["insert into wsi values ("<>StringJoin[Riffle[Table[If[type=="text","\"``\"" ,"``"],{type,checkDataType/@ firstDataLine}],","]]<>")",StringSplit[ReadLine["wgi.csv"],RegularExpression[",(?=([^\"]*\"[^\"]*\")*[^\"]*$)"]]]]

Now check again the number of lines in the table wsi:

ExternalEvaluate[dsdb, "select count(1) from wsi"]

ExternalEvaluate[dsdb, "select * from wsi"]

Process all the CSV file lines (records) individually in a infinite loop, reading line by line to the end of file (EOF), this data loading can take some time:

!StringStartsQ["\"aaa","\""]

Lest test just the first 100 lines

re=RegularExpression[",(?=([^\"]*\"[^\"]*\")*[^\"]*$)"];

Table[
ExternalEvaluate[dsdb,StringReplace[TemplateApply["insert into wsi values ("<>StringJoin[Riffle[Table[If[type=="text" ,"\"``\"" ,"``"],{type,dataType}],","]]<>")",StringSplit[ReadLine["wgi.csv"],re]],"\"\""-> "\""]]
,100]

Notes that no database index was created, nor any database constraint.

Hints

Import command can handle CSV strings with quotes and comma issue very well, and load to a Dataset.

The Dataset size is limited to the PC memory available of course.

To working with a volume of data bigger then your memory, read the data line by line and write it down to a database is a good option

SQL can help you get smaller Datasets with relevant data

Datasets can be combined with Wolfram curated data for analysis and reporting

Put it all together...

Now we have written a kind of super function that can hide all the complexity of just import a CSV file to a database table automatically!!

Set the working directory:

SetDirectory[NotebookDirectory[]]

wget -O clients.csv https://github.com/danielscarvalho/data/blob/master/Clients.csv?raw=true

wc clients.csv

Close["clients.csv"]

checkDataType[data_String] := 
 Which[
  StringMatchQ[data, RegularExpression["^-?\\d+$"]], "integer",
  StringMatchQ[data, RegularExpression["^-?\\d*\\.+\\d+$"]], "real",
  True, "text"]

Work in progress, will be published at the function repo. Need more testes to get more general...

You can adapt it for your dataset and context with minor adjustments.

This code (notebook) is available at the the conference Pethable (https://wtc2022.pathable.co/)

This example process CSV, and it can be easily adapted to process TSV, TXT, and other tabular (Cartesian) data types

DigestCsv2Db[database_, fileName_String ] := Module[{dataHeader, dataFirstLine, dataType, dataTableName, createTable, record, re, dbsession},
  dataHeader = StringReplace[ToLowerCase[StringSplit[ReadLine[File[fileName]], ","]], " " -> "_"];
  dataFirstLine = StringSplit[ReadLine[File[fileName]], RegularExpression[",(?=([^\"]*\"[^\"]*\")*[^\"]*$)"]];
  dataType = checkDataType /@ dataFirstLine;
  dataTableName = StringReplace[ToLowerCase[First[StringSplit[fileName, "."]]], " " -> "_"];
  createTable = "create table " <> dataTableName <> " ( " <>
    StringTake[StringJoin[Insert[#, " ", 2] & /@ Append[","] /@ Partition[Riffle[dataHeader, dataType], 2]], {1, -2}] <>
    ")";
  dbsession = StartExternalSession[database];
  
  ExternalEvaluate[dbsession, createTable];
  re = RegularExpression[",(?=([^\"]*\"[^\"]*\")*[^\"]*$)"];
  record = "";
  
  (* Load first data line *)
  ExternalEvaluate[dbsession, StringReplace[TemplateApply["insert into " <> dataTableName <> " values (" <> StringJoin[Riffle[Table[If[type == "text", "\"``\"" , "``"], {type, dataType}], ","]] <> ")", dataFirstLine], "\"\"" -> "\""]];
  
  Do[
   ExternalEvaluate[dbsession, StringReplace[TemplateApply["insert into " <> dataTableName <> " values (" <> StringJoin[Riffle[Table[If[type == "text", "\"``\"" , "``"], {type, dataType}], ","]] <> ")", StringSplit[record, re]], "\"\"" -> "\""]];
   record = ReadLine[File[fileName]];
   If[record == EndOfFile, Break[]];
   , ∞];
  
  Close[fileName];
  DeleteObject[dbsession];
  ]

dsdb = DatabaseReference[<|
   "Backend" -> "SQLite",
   "Name" -> File["ds-db.sqlite"]
   |>]

Close["clients.csv"]

DigestCsv2Db[dsdb, "clients.csv" ]

ExternalEvaluate[dsdb,
 "SELECT name FROM sqlite_master
WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%'
UNION ALL
SELECT name FROM sqlite_temp_master
WHERE type IN ('table','view')
ORDER BY 1
"]

ExternalEvaluate[dsdb, "select count(1) COUNT from clients"]

ExternalEvaluate[dsdb, "select * from clients"]

ExternalEvaluate[dsdb, "select * from clients where dec is not null and sub > 0 and type = 'B'"]

Now we can digest a CSV file directly to the database without the boilerplate!

(* ExternalEvaluate[dsdb, "drop table clients"] *)

Store JSON and Wolfram Language expression at MySQL 8.0 relational database

Two interesting data types for MySQL 8 tables:

JSON

LONGTEXT

MySQL is a very popular database (and its fork MariaDB), 70% of WEB sites are made in LAMP stack (Linux, Apache, MySQL and PHP)
MySQL is largely used in Data Science projects too

SetDirectory[NotebookDirectory[]]

SQL Datababse

dsmysql= DatabaseReference[<|
"Backend"->"mysql",
"Name"->"datascience",
"Port"->Automatic,
"Host"->"localhost",
"Username"->"ds1",
"Password"->"Guu9FaYhL8ivfZvigZpR"|>]

ExternalEvaluate[dsmysql,"select now() NOW, version() VERSION;"]

ExternalEvaluate[dsmysql,"create table datascience.t1 (id integer, val real, name text)"]

ExternalEvaluate[dsmysql,"insert into datascience.t1 values (5,0.5,'Chamaing')"]
ExternalEvaluate[dsmysql,"insert into datascience.t1 values (6,2.2,'WTC2O22')"]

ExternalEvaluate[dsmysql,"select * from  datascience.t1 "]

JSON data type

MySQL 8 has a JSON data type, very convenient to store JSON message without break it down to tables...

CREATE TABLE `datascience`.`json_messages` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `moment` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `message` JSON NOT NULL,
  PRIMARY KEY (`id`));

message=Import["https://www.mercadobitcoin.net/api/BTC/orderbook/","Text",CharacterEncoding->"UTF8"];

message  // Head

message // Short

message // StringLength

ExternalEvaluate[dsmysql,"insert into datascience.json_messages (message) values ('"<>message<>"')"]

btc=Import["https://www.mercadobitcoin.net/api/BTC/ticker/","Text",CharacterEncoding->"UTF8"];
btc // Short
ExternalEvaluate[dsmysql,"insert into datascience.json_messages (message) values ('"<>btc<>"')"]

To learn more about JSON at MySQL database:
https://dev.mysql.com/doc/refman/8.0/en/json.html

Wolfram Language expressions at a LONGTEXT field type

ddg=Dataset[Import["https://api.duckduckgo.com/?q=Wolfram+Research&format=json","RawJSON"] ]

FullForm[ddg] // Short

Another interesting field type of MySQL 8 is LONGTEXT, it can be used to store Wolfram Language expressions:

CREATE TABLE `datascience`.`wl_expressions` (
  `id` INT NOT NULL AUTO_INCREMENT,  
  `name` VARCHAR(500) NOT NULL UNIQUE,
  `moment` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `expression` LONGTEXT NOT NULL,
  PRIMARY KEY (`id`));

Two simple functions to store Wolfram Language expressions at MySQL database table, wrapping the SQL code
It can be easily adapted to other relational databases such as Oracle, MS SQL Server, MariaDb, DB2, etc...

DatabasePut[database_,name_, ex_]:=ExternalEvaluate[database,"insert into datascience.wl_expressions (name, expression) values ('"<>name<>"','"<>Compress[ex]<>"')"];

DatabaseGet[database_,name_]:=Uncompress[ExternalEvaluate[database,"select expression from datascience.wl_expressions where name = '"<>name<>"'"][1,1]];

It is possible to store and recover the entire Dataset, and other expressions too:

DatabasePut[dsmysql, "ddginfo",ddg]

And get it back “alive” in the notebook interface:

DatabaseGet[dsmysql,"ddginfo"]

It is important to notes that Wolfram Language Dataset support nested data common in JSON and XML data structures.
On another hand Pandas from Python (Jupyter Notebook) do not support nested data, just tabular data.

As everything in Wolfram Language is an expression, we can store images and so on...

DatabasePut[dsmysql, "dogimage2",]

DatabaseGet[dsmysql, "dogimage2"]

m=Manipulate[EdgeDetect[Blur[,b],e],{b,1,10},{e,1,10}]

DatabasePut[dsmysql, "frogui2",m]

DatabaseGet[dsmysql, "frogui2"]

DatabasePut[dsmysql, "cats",WebImageSearch["cats"]]

DatabaseGet[dsmysql, "cats"]

The inspiration to store Wolfram Language expression on the local network database came from the functions CloudPut and CloudGet
Wolfram Language can store expression at the cloud:

?CloudPut

?CloudGet

WTC 2022