Oracle SQL Scheduled Jobs - An Interesting Approach

 

Oracle SQL Scheduled Jobs

A DB Scheduler is the best way to automate any backend database job.

For instance, if you want to process the persisted data using and stored procedure, in the background mode and do want to bother about the end to end start and stop of the process, a database job can be scheduled which would invoke the stored procedure, which further would invoke the tables' data, process the data and persist the data in the formatted/ processed conditioned state in the target tables.

Some common usage of such pipelines is to process the dashboard numbers per the populated data.

This blog-post would now walk you through the simple process involved in implementing a basic scheduled database pipeline, and would be using Oracle SQL as the technology choice.

1. Check if you have create table access, per result of the following sql:

CREATE TABLE abc_test  
( id number(10),  
  name varchar2(50)
);  
commit;

2. Check if the table is created:

    select * from abc_test;

-- should return empty resultset if table exists

3. Create a stored procedure to do the activity required (like populate data in the table):

    create or replace procedure abc_test_proc as
        begin 
            insert into abc_test(id, name) values (1,'first_test');
            commit;
        end;
    commit;

4. Create a scheduled job, that will invoke the above created stored proc. at a specified timestamp:

5. Validate the data in the table:

Hands-on attempt to visualise Linear Regression and R-Square, using Python and Jupyter

Hands-on attempt to visualise Linear Regression and R-Square, using Python and Jupyter

Visit: https://github.com/namitsharma99/basicLinearRegression

Details: https://youtu.be/bQs8nccZw2s

import numpy as np
import pandas as pd
import csv
import matplotlib.pyplot as plt

plt.rcParams['figure.figsize'] = (40.0, 20.0)

data = pd.read_csv("dummy_data.csv")
print(data.shape)
print(data)

(10, 2)
   X-axis  Y-axis
0       1       2
1       2       2
2       3       5
3       4       4
4       5       3
5       6       6
6       7       6
7       8       9
8       9       8
9      10      11

In [15]:

x = data['X-axis'].values
y = data['Y-axis'].values

x_bar = np.mean(x)
y_bar = np.mean(y)

print(x_bar)
print(y_bar)

5.5
5.6

In [16]:

n = len(x)
print(n)

10

In [17]:

# finding equation y = mx + c

num = 0
den = 0

for i in range(n) :
    num += (x[i] - x_bar) * (y[i] - y_bar)
    den += (x[i] - x_bar) ** 2
    
m = num / den
c = y_bar - m * x_bar

print(m)
print(c)

0.9212121212121213
0.5333333333333323

In [39]:

# trying linear regression model

plt.rcParams.update({'font.size': 50})

#actual points
plt.scatter(x, y, color='red', label='actual scatter plot', s=150)

# for line y = mx + c
# translating variables x and y as u and v, as v = mu + c

u = x
v = m * u + c
plt.plot(u, v, color='green', label='predicted line')

plt.xlabel('X-axis')
plt.ylabel('Y-axis')
plt.legend()
plt.show()

[ 1  2  3  4  5  6  7  8  9 10]
[1.45454545 2.37575758 3.2969697  4.21818182 5.13939394 6.06060606
 6.98181818 7.9030303  8.82424242 9.74545455]

In [42]:

# checking the accuracy of this model using R^2 technique
# using line v = m * u + c

num = 0
den = 0

for i in range(n):
    # y axis predicted :
    v_p = m * u[i] + c
    num += (v_p - y_bar) ** 2
    den += (y[i] - y_bar) ** 2
    
print(num/ den)

0.8496616651956459