Picture by Editor# Introduction
Knowledge has change into a neater commodity to retailer within the present digital period. With the benefit of getting considerable knowledge for enterprise, analyzing knowledge to assist firms achieve perception has change into extra essential than ever.
In most companies, knowledge is saved inside a structured database, and SQL is used to amass it. With SQL, we are able to question knowledge within the kind we wish, so long as the script is legitimate.
The issue is that, generally, the question to amass the information we wish is complicated and never dynamic. On this case, we are able to use SQL saved procedures to streamline tedious scripts into easy callables.
This text discusses creating knowledge analytics automation scripts with SQL saved procedures.
Curious? Right here’s how.
# SQL Saved Procedures
SQL saved procedures are a set of SQL queries saved straight inside the database. If you’re adept in Python, you may consider them as capabilities: they encapsulate a sequence of operations right into a single executable unit that we are able to name anytime. It’s helpful as a result of we are able to make it dynamic.
That’s why it’s useful to grasp SQL saved procedures, which allow us to simplify code and automate repetitive duties.
Let’s strive it out with an instance. On this tutorial, I’ll use MySQL for the database and inventory knowledge from Kaggle for the desk instance. Arrange MySQL Workbench in your native machine and create a schema the place we are able to retailer the desk. In my instance, I created a database known as finance_db with a desk known as stock_data.
We will question the information utilizing one thing like the next.
USE finance_db;
SELECT * FROM stock_data;
Typically, a saved process has the next construction.
DELIMITER $$
CREATE PROCEDURE procedure_name(param_1, param_2, . . ., param_n)
BEGIN
instruct_1;
instruct_2;
. . .
instruct_n;
END $$
DELIMITER ;
As you may see, the saved process can obtain parameters which are handed into our question.
Let’s look at an precise implementation. For instance, we are able to create a saved process to mixture inventory metrics for a selected date vary.
USE finance_db;
DELIMITER $$
CREATE PROCEDURE AggregateStockMetrics(
IN p_StartDate DATE,
IN p_EndDate DATE
)
BEGIN
SELECT
COUNT(*) AS TradingDays,
AVG(Shut) AS AvgClose,
MIN(Low) AS MinLow,
MAX(Excessive) AS MaxHigh,
SUM(Quantity) AS TotalVolume
FROM stock_data
WHERE
(p_StartDate IS NULL OR Date >= p_StartDate)
AND (p_EndDate IS NULL OR Date <= p_EndDate);
END $$
DELIMITER ;
Within the question above, we created the saved process named AggregateStockMetrics. This process accepts a begin date and finish date as parameters. The parameters are then used as circumstances to filter the information.
You may name the saved process like this:
CALL AggregateStockMetrics('2015-01-01', '2015-12-31');
The process will execute with the parameters we go. Because the saved process is saved within the database, you need to use it from any script that connects to the database containing the process.
With saved procedures, we are able to simply reuse logic in different environments. For instance, I’ll name the process from Python utilizing the MySQL connector.
To do this, first set up the library:
pip set up mysql-connector-python
Then, create a perform that connects to the database, calls the saved process, retrieves the end result, and closes the connection.
import mysql.connector
def call_aggregate_stock_metrics(start_date, end_date):
cnx = mysql.connector.join(
person="your_username",
password='your_password',
host="localhost",
database="finance_db"
)
cursor = cnx.cursor()
strive:
cursor.callproc('AggregateStockMetrics', [start_date, end_date])
outcomes = []
for end in cursor.stored_results():
outcomes.lengthen(end result.fetchall())
return outcomes
lastly:
cursor.shut()
cnx.shut()
The end result can be much like the output under.
[(39, 2058.875660431691, 1993.260009765625, 2104.27001953125, 140137260000.0)]
That’s all you could find out about SQL saved procedures. You may lengthen this additional for automation utilizing a scheduler in your pipeline.
# Wrapping Up
SQL saved procedures present a technique to encapsulate complicated queries into dynamic, single-unit capabilities that may be reused for repetitive knowledge analytics duties. The procedures are saved inside the database and are straightforward to make use of from totally different scripts or purposes akin to Python.
I hope this has helped!
Cornellius Yudha Wijaya is a knowledge science assistant supervisor and knowledge author. Whereas working full-time at Allianz Indonesia, he likes to share Python and knowledge ideas through social media and writing media. Cornellius writes on a wide range of AI and machine studying subjects.
Picture by Editor# Introduction
Knowledge has change into a neater commodity to retailer within the present digital period. With the benefit of getting considerable knowledge for enterprise, analyzing knowledge to assist firms achieve perception has change into extra essential than ever.
In most companies, knowledge is saved inside a structured database, and SQL is used to amass it. With SQL, we are able to question knowledge within the kind we wish, so long as the script is legitimate.
The issue is that, generally, the question to amass the information we wish is complicated and never dynamic. On this case, we are able to use SQL saved procedures to streamline tedious scripts into easy callables.
This text discusses creating knowledge analytics automation scripts with SQL saved procedures.
Curious? Right here’s how.
# SQL Saved Procedures
SQL saved procedures are a set of SQL queries saved straight inside the database. If you’re adept in Python, you may consider them as capabilities: they encapsulate a sequence of operations right into a single executable unit that we are able to name anytime. It’s helpful as a result of we are able to make it dynamic.
That’s why it’s useful to grasp SQL saved procedures, which allow us to simplify code and automate repetitive duties.
Let’s strive it out with an instance. On this tutorial, I’ll use MySQL for the database and inventory knowledge from Kaggle for the desk instance. Arrange MySQL Workbench in your native machine and create a schema the place we are able to retailer the desk. In my instance, I created a database known as finance_db with a desk known as stock_data.
We will question the information utilizing one thing like the next.
USE finance_db;
SELECT * FROM stock_data;
Typically, a saved process has the next construction.
DELIMITER $$
CREATE PROCEDURE procedure_name(param_1, param_2, . . ., param_n)
BEGIN
instruct_1;
instruct_2;
. . .
instruct_n;
END $$
DELIMITER ;
As you may see, the saved process can obtain parameters which are handed into our question.
Let’s look at an precise implementation. For instance, we are able to create a saved process to mixture inventory metrics for a selected date vary.
USE finance_db;
DELIMITER $$
CREATE PROCEDURE AggregateStockMetrics(
IN p_StartDate DATE,
IN p_EndDate DATE
)
BEGIN
SELECT
COUNT(*) AS TradingDays,
AVG(Shut) AS AvgClose,
MIN(Low) AS MinLow,
MAX(Excessive) AS MaxHigh,
SUM(Quantity) AS TotalVolume
FROM stock_data
WHERE
(p_StartDate IS NULL OR Date >= p_StartDate)
AND (p_EndDate IS NULL OR Date <= p_EndDate);
END $$
DELIMITER ;
Within the question above, we created the saved process named AggregateStockMetrics. This process accepts a begin date and finish date as parameters. The parameters are then used as circumstances to filter the information.
You may name the saved process like this:
CALL AggregateStockMetrics('2015-01-01', '2015-12-31');
The process will execute with the parameters we go. Because the saved process is saved within the database, you need to use it from any script that connects to the database containing the process.
With saved procedures, we are able to simply reuse logic in different environments. For instance, I’ll name the process from Python utilizing the MySQL connector.
To do this, first set up the library:
pip set up mysql-connector-python
Then, create a perform that connects to the database, calls the saved process, retrieves the end result, and closes the connection.
import mysql.connector
def call_aggregate_stock_metrics(start_date, end_date):
cnx = mysql.connector.join(
person="your_username",
password='your_password',
host="localhost",
database="finance_db"
)
cursor = cnx.cursor()
strive:
cursor.callproc('AggregateStockMetrics', [start_date, end_date])
outcomes = []
for end in cursor.stored_results():
outcomes.lengthen(end result.fetchall())
return outcomes
lastly:
cursor.shut()
cnx.shut()
The end result can be much like the output under.
[(39, 2058.875660431691, 1993.260009765625, 2104.27001953125, 140137260000.0)]
That’s all you could find out about SQL saved procedures. You may lengthen this additional for automation utilizing a scheduler in your pipeline.
# Wrapping Up
SQL saved procedures present a technique to encapsulate complicated queries into dynamic, single-unit capabilities that may be reused for repetitive knowledge analytics duties. The procedures are saved inside the database and are straightforward to make use of from totally different scripts or purposes akin to Python.
I hope this has helped!
Cornellius Yudha Wijaya is a knowledge science assistant supervisor and knowledge author. Whereas working full-time at Allianz Indonesia, he likes to share Python and knowledge ideas through social media and writing media. Cornellius writes on a wide range of AI and machine studying subjects.
