SQL with Groovy .

SQL with Groovy .

Groovy provides a handy way to handle jdbc when compared to java. Playing with database becomes very easy with groovy. To use SQL scripting with groovy we have to use the  groovy.sql.Sql package.

import groovy.sql.Sql

sql = Sql.newInstance( 'jdbc:jtds:sqlserver://serveripName/databaseName-CLASS;domain=yourdomainname', 'dbusername', 'dbpassword', 'driverclass' )

sql.eachRow( 'select * from tableName' ) {
println "$it.slno -- ${it.userName} --"
}

The Sql.newInstance method helps to create database connection. It takes the parameters like database username, database password, driver class and jdbc url.
The sql.eachRow method is used to fire SQL query and iterate the resultset aswell.

Groovy has provision to handle performance with transaction based query. We can use the batch withBatch method to handle the performance issue.

sql.eachRow("SELECT * FROM employee WHERE filter_criteria ", { employee ->
// Process employee and add update statement to batch
def updateStmts = processEmployee(employee)
stmt.addBatch(updateStmts)
})
})

 

For the sake of integrity we can define the withTransaction block along withBatch

sql.withTransaction {
def result = sql.withBatch({ ... })
...
}

 

When we know that the result set is only one row.

row = sql.firstRow('select columnA, columnB from tableName')
firstRow method helps us to retrive the first row.
def ans = sql.firstRow("select * from PERSON where location_id < $location")
println ans.firstname

groovy insert statement with closure

sql.execute("insert into employee (employeName, empAddress) values (${employeeName}, ${empAddress})")

sql.close().

This method closes the sql connection, and takes care of the connection.

These were only few methods. To know more we can explore the groovy.sql package.

Difference between SQL and MDX

Difference between SQL and MDX

The MDX (Multi Dimensional Expressions) syntax appears very similar to the SQL syntax and also functions somewhat similar to the way SQL performs. But there are many differences, basically conceptual differences, between SQL and MDX.

The basic difference between MDX and SQL is the ability of MDX to reference multiple dimensions.

  1. Data Representation:

SQL refers to only two dimensions, rows and columns, when processing queries since SQL was designed to handle tabular data in two dimensional formats. Here rows and columns have some meaning in SQL syntax. However, in MDX Queries, it can refer to multiple dimensions distributed along different axis. Here rows and columns are different to that of SQL. They are depicted as Horizontal (rows) and vertical (column) axis.

  1. Difference in Select and Where clause:

In SQL, the Select clause is used to depict the column information i.e. it defines the column layout for a query, while the where clause is used to define the row layout. In MDX, the select clause is used to define several axis dimensions, while the where clause is used to restrict the data to a specific dimension or a member.

  1. Visualization:

In SQL, the visualization of data is in a two dimensional format consisting of rows and columns. It is more like a tabular form. In MDX, the visualization of data is multi dimensional on horizontal and vertical axis. It is more like in a cross tabular form.

  1. JOINS:

MDX supports all the joins that SQL supports. The join condition between the fact and the dimension will be specified while designing the cube. Following are the examples in which a join is specified,

  1. Inner Join:

In SQL,


SELECT table1.<column_name>,table2.<column_name>FROM table1 inner join table2

ON table1.column1 = table2.column2;

 

 

In MDX,

SELECT NON EMPTY {[DimensionName].[LevelName].Members} ON ROWS,
NON EMPTY {[Measures].[MeasureName]} ON COLUMNS
FROM [CubeName];

 

  1. Left Outer Join:

In SQL,

SELECT table1.<column_name>,table2.<column_name>
FROM table1 left join table2
ON table1.column1 = table2.column2;

 

In MDX,

SELECT {[DimensionName].[LevelName].Members} ON ROWS,
NON EMPTY {[Measures].[MeasureName]} ON COLUMNS
FROM [CubeName];

 

  1. Right Outer Join:

In SQL,

SELECT table1.<column_name>,table2.<column_name>
FROM table1 right join table2
ON table1.column1 = table2.column2;

 

In MDX,

SELECT NON EMPTY {[DimensionName].[LevelName].Members} ON ROWS,
{[Measures].[MeasureName]} ON COLUMNS
FROM [CubeName];

 

  1. Full Outer Join:

In SQL,

SELECT table1.<column_name>,table2.<column_name>
FROM table1 right join table2
ON table1.column1 = table2.column2;

 

In MDX,

SELECT {[DimensionName].[LevelName].Members} ON ROWS,
{[Measures].[MeasureName]} ON COLUMNS
FROM [CubeName];

 

  1. FUNCTIONS:

 

MDX supports a lot of functions which makes its execution much simpler. In SQL, generating the desired result makes query very complex. For example, generating the Sales value for current year and previous year along with the YTD value is very easy in MDX since there are some predefined functions like Parallel period and YTD which you need to define in the WITH clause of the MDX statement. In SQL, it becomes complicated since you need to create aliases for each value (Current Year, Previous Year and YTD) and then you have to join all the aliases to generate the desired result. It makes query very complex.

 

  • Example in MDX:
WITH member [Measures].[Last Period] as ‘([Time].[Years].CurrentMember.PrevMember, [Measures].[Sales])’ member [Measures].[Growth] as ‘[Measures].[Sales] – [Measures].[Last Period]’
SELECT CROSSJOIN({[Time].[Years].Members},{[Measures].[Sales],[Measures].[Last Period],[Measures].[Growth]}) ON COLUMNS FROM [SteelWheelsSales];

 

CONCLUSION:

MDX and SQL performs the same execution but the results are portrayed in different ways. SQL shows data in a tabular two dimensional form whereas MDX shows data in a multidimensional form.

 

Regards,

Nitish Kumar Mishra

Let’s Have Fun with Triggers in SQL

Let’s Have Fun with Triggers in SQL

What are Triggers?

Triggers are a Block of Structure that executes when a DML Statement is performed or Executed. Triggers are fun to use but from a business point of view not advisable but that a different story entirely ie the Pro’s and Con’s. but for gaining an idea about Triggers here is what the Video Covers

  1. Basic Creation of a Trigger
  2. and a simple real-time scenario on how i used triggers

The Video here illustrates or covers the above points.

Batch-Insertion in Hibernate


Batch-Insertion in Hibernate

To enable batch-processing  to upload a large number of records into your database using Hibernate.

If you are undertaking batch processing you will need to enable the use of JDBC batching. This is absolutely essential if you want to achieve optimal performance. Set the JDBC batch size to a reasonable number (10-50, for example):

hibernate.jdbc.batch_size 20

the code snippet look like this ,

Session session = sessionFactory.openSession();
Transaction txInstance = session.beginTransaction();
   
for ( int i=0; i<100000; i++ ) {
    Student student = new Student(.....);
    session.save(student);
    if ( i % 40 == 0 ) { 
        session.flush();
        session.clear();
    }
}
   
txInstance.commit();
session.close();

When making new objects persistent flush() and then clear() the session regularly in order to control the size of the first-level cache because by default, Hibernate will cache all the persisted objects in the session-level cache and ultimately your application would fall over with an OutOfMemoryException.

A JDBC batch can target one table only, so every new DML statement targeting a different table ends up the current batch and initiates a new one. Mixing different table statements is therefore undesirable when using SQL batch processing.

PUSHPRAJ KUMAR (BI Developer)

 

With Clause in SQL

With Clause in SQL

The WITH query_name clause lets you assign a name to a subquery block. You can then reference the subquery block multiple places in the query by specifying the query name.

You can specify this clause in any top-level SELECT statement and in most types of subqueries. The query name is visible to the main query and to all subsequent subqueries except the subquery that defines the query name itself.

A WITH clause is really best used when the result of the WITH query is required more than one time in the body of the query such as where one averaged value needs to be compared against two or three times.

Example 1:


// single subquery example 
WITH regional_sales AS (
        SELECT region, SUM(amount) AS total_sales
        FROM orders
        GROUP BY region
     )
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM regional_sales)
GROUP BY region, product;

Example 2:


// Multiple subquery with first subquery alias (regional_sales) is used in second subquery alias (top_regions)
WITH regional_sales AS (
        SELECT region, SUM(amount) AS total_sales
        FROM orders
        GROUP BY region
     ), top_regions AS (
        SELECT region
        FROM regional_sales
        WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
     )
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

Beginners Guide in Understanding Different Types of Joins

INNER JOINS

When we decide that one table won’t be enough for getting our desired output then we try to attach one more table to the existing table is known as JOINS. Now Inner Joins in other words is doing the same thing as the definition of joins goes but with an additional advantage which is based on a particular key column from table 1 and similar key column in table 2 then if the condition exist then it clubs that table and solely ignores rows that does not satisfy the condition

Now practically [using PGAdmin]

Blog

LEFT JOINS & RIGHT JOINS

As we defined joins this is also similar but with Left Joins it clubs the 1st table and 2nd Table based on the condition and also rows that does not satisfy the condition in the 2nd Table

Similarly it we can say for Right Joins but in vice versa manner

 

Real Time

person_disability table has 25 Rows and a column with personid
and person_employment has 19558 rows and also column with personid

Now lets see when we say

LEFT

It returned 12663 after adding few filters why?
Because Based on the 1st Table(Left Table) the condition will execute.

I.e. the table person_employment total rows will execute
and only does rows in the person_disability table that satisfy the condition will show and also NULL rows will also show (because those rows does not satisfy the condition).

 

Now if we Right Join the Same table let’s see what happens?

RIGHT

Now it returned 25 rows after adding few filters why?

Because based on the 2nd Table (Right Table) the condition will execute.

I.e. the person_disability table total rows will execute
and only does rows in the person_employment table that satisfy the condition will show and also NULL rows will also show (because those rows does not satisfy the condition).

We can also attain a RIGHT JOIN Using a Left join by swapping the names of the table
example

LEFT1

In My next Blog we will discuss on CROSS JOIN

SQL Event Scheduling

SQL Event Scheduling

 

Event is responsible for making sure that a specified routine (section of query code) is executed at regular intervals that is specified by the user.

An example of use of an Event would be as follows :
A retail store owner wants to see his remaining stocks in his store, in the morning before he opens the store. At such a time, an Event can be created to run at 8:00am, everyday, that will update his database based on the available material.
Under this condition, an Event will be specified which will empty (TRUNCATE) the “Final_Stocks” table and refill it with data from all other tables in the database which are holding the information of remaining stock of goods.

Syntax :
CREATE
[DEFINER = { user | CURRENT_USER }]
EVENT [IF NOT EXISTS] event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT ‘comment’]
DO event_body;

  • event_name : This is the name of the Event that is being created.
  • schedule : The schedule section deals with the starting, ending, and time intervals of repeating the Event.

 

The schedule section has the following parts :

AT timestamp [+ INTERVAL interval] …
| EVERY interval [STARTS timestamp [+ INTERVAL interval] …]
[ENDS timestamp [+ INTERVAL interval] …]

  • Interval : The interval section specifies the time interval after the previous execution the event needs to wait before being triggered again. The interval should have the following syntax :

quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
Where quantity specifies the duration of unit time (specified in the next section), eg: 3 HOUR.

  • Timestamp : The timestamp uses the current date – time timestamp to specify one of the parameters while scheduling the Event.
  • event_body : This is where the main body of the Event is specified. This section holds the working of the event, what all it should do.

 

Example:
1. Creating an Event :

1

2. Setting the time :

2

3. Original Table :

3

4. Table after Event :

4