How and Why To Bridge between SQL and NoSQL

SQL have for long now been the synonym of "database" for us. For any sort of data management, SQL had been our instinctive choice. However, the past decade saw the emergence of NoSQL which gave rise to a fierce competition of preferences.

  

What haunts the mind of every aspiring database developer today is the question of choice: To SQL or NoSQL. We want to keep in touch with the latest trends in the technology, but don't want the established technologies to slip away either. However, the most basic point that most people seem to miss is this: SQL and NoSQL are not competitors, and most certainly not antonyms of each other.

SQL or Structured Query Language is the most standard concept of database management systems today. SQL considers data to be stored in the form of tables called Relations, that consist of tuples and attributes. While this concept had been a hugely successful improvement over the data-storage systems present at that time, like flat files, things have changed today.

NoSQL came as a breath of fresh air in an industry that was rapidly changing. The world is going digital, and the digital world is messy. We can never predict the volume, variety or velocity of incoming data. The data, apart from being unpredictable, is also unstructured. Since relational databases are not inherently adept to handle them, something else was required. At the same time, distributed computing is all the rage today, because most businesses are moving towards the cloud. The expansion of relational databases cannot keep up with the pace; thus, NoSQL entered into the scene.

Why to migrate from SQL to NoSQL

Strictly speaking, NoSQL aims to do what SQL cannot. It is not based on relations and it may sometimes even fail to follow the ACID properties! But unlike what you have been taught, ACID properties, though really useful, are not the ultimate necessity. The ultimate necessity is fault tolerance, and NoSQL manages to achieve that anyway.

NoSQL cannot be defined in a single line, as there is no single definition. While all SQL-based databases follow strict guidelines that adhere to SQL-standards, NoSQL gives the databases a free rein. With so many lacks of standards, one might wonder: Are the reasons enough to migrate to NoSQL?

Yes, because we have only touched the crux of the importance of NoSQL in modern world. The two biggest reasons why NoSQL trumps over SQL are agility and scalability.

With the rapid changes that occur daily in the industry, being agile is the only way to survive. However, Relational databases couldn't ever hope to achieve that, with their rigid schemas and complex development. The aforementioned rapid changes are also met by growing size, which require rapid scalability. However, scalability was one aspect that was blatantly ignored in SQL (as it was made in a time when web and internet were non-existent). To cope up with these issues, NoSQL seems like our best bet.

Why to Bridge SQL and NoSQL

"Now that we know how NoSQL differs from SQL, the question arises: Why to bridge them? Why not adopt NoSQL altogether?   "

Simply, because NoSQL doesn't have the same penetration as SQL. A huge number of companies have their entire existing architecture based on relational databases, which would be quite a headache to change. But that doesn't mean that one has to remain stuck with SQL forever. The best option in such scenarios is to bridge the existing SQL framework with a NoSQL database. The benefit? To put it simple, it will bring out "the best of both worlds".

As far the "bridging" goes, there is no one, simple way to do that. The easiest way would be to use third-party drivers like easysoft, which provides ODBC-like bridging capabilities. However, as it comes from a third-party vendor, it might have its own security and licensing issues.

An alternative approach would be to develop languages that could extend SQL functionality to NoSQL databases. One example would be the N1QL, introduced by Couchbase Server, which extends SQL to JSON.

The ways to bridge the gap between these two technologies may differ and evolve; but we can all agree that co-existence of the two is best for the progress of industry.

Please share your thoughts on this topic. If you like this posts, please share it on google by clicking on the Google +1 button.

Read more on NO SQL- NOT ONLY SQL here - WhatisNoSQL

Google BigQuery- An externalized version of Dremel

So What is Google Big Query?? Its powerful Big Data analytics platform used by all types of organizations to run SQL-like queries against multiple terabytes of data in a matter of seconds. With this cloud based interactive query service we can handle web-sized amounts of data at blazing fast speed. 


Big Query (released in 2010)is actually the external or public implementation of one of the Google’s core technologies so-called Dremel .Big Query provides the features available in Dermel to third party conserving its unparalleled query performance. Both in fact share the same underlying architecture and performance characteristics. 

Big Query release made it possible to utilize the power of Dremel and to take advantage of Google’s massive computational infrastructure.

Let’s take a deeper look into power of Dremel… It is a query service that allows you to run SQL-like queries against very, very large data sets and get accurate results in mere seconds. You just need a basic knowledge of SQL to query extremely large datasets in an ad hoc manner.

Dermel runs through tens of thousands of servers simultaneously and makes it easy to analyse large amount of data such as a collection of web documents or a library of digital books or even the data describing millions of spam messages.

“According to Google’s paper, this has been used inside Google since 2006, with “thousands” of Googlers using it to analyse everything from the software crash reports for various Google services to the behavior of disks inside the company’s data centers”


The two core technologies that makes Dremel and BigQuery so fast is the Tree Architecture of Dremel And that the Data is stored in a Columnar Storage fashion in so doing, it gives very high compression ratio and scan throughput. 

So how to use data in Big Query or how to import data into Big Query:

• Upload your data to Google Cloud Storage
• Import the files to Big Query. Executed using command-line tool, Web UI or API, which can typically import roughly 100 GB within a half hour.

Other Important Features of Google Big Query:

• BigQuery is designed to handle structured data using SQL. Apart from SQL queries we can easily read and write data in Big Query via Cloud Dataflow, Spark, and Hadoop

• BigQuery provides extremely high cost effectiveness and full-scan performance for ad hoc queries and cost effectiveness compared to traditional data warehouse solutions and appliances.

• BigQuery is the best choice for ad hoc OLAP/BI queries that require results as fast as possible.

• BigQuery requires no capacity planning, provisioning, 24x7 monitoring or operations, nor does it require manual security patch updates. You simply upload datasets to Google Cloud Storage of your account, import them into Big Query, and let Google’s experts manage the rest.

If you like this post, please share it on google by clicking on the Google +1 button.

Please go through our latest post TOP 6 BIG DATA TRENDS IN THE NEAR FUTURE

READ ONLY TABLES In Oracle 11g

Since Oracle 11g we can place a table in read -only mode with the ALTER TABLE …READ ONLY statement. Read-Only tables are like normal tables, the only dissimilarity being it disallows any attempt to modify the data. Any DML (insert/update/delete/truncate) and certain DDL statement that affects the data are not allowed on these tables.

SYNTAX

ALTER TABLE SALES_TEST READ ONLY;

To Alter a table to read-only mode you must have ALTER ANY TABLE privilege on the table.

If you try to insert a record into this table now,

INSERT INTO SALES_TEST VALUES (100);

Error:

Update operation not allowed on the table.

Even though the table is read –only mode, index creation statement is possible.

CREATE INDEX SALES_TEST_IND ON SALES_TEST (ID);

Index Created.

• To Revert back the table to READ/WRITE mode.

SYNTAX

ALTER TABLE SALES_TEST READ WRITE;

Prior 11g Read-only mode could only be associated to database and tablespace level. Previously to make a table read-only we have to grant SELECT privilege to appropriate users on the table. But still the table will be available for read and write to the creator of the table. With the new Oracle 11g enhancement no DML will be allowed on the table even by the owner of the table.

• How To Check A Table Is In Read Only Or Not?

To identify the mode of the table you can use the READ_ONLY column. Its set to YES if table is read only and NO when table is not read-only.

SELECT TABLE_NAME ,READ_ONLY FROM USER_TABLES WHERE TABLE_NAME=’SALES_TEST ‘

The best example for tables which makes sense in read-only mode is the configuration tables that are not to be modified after installation. The same is applicable for tables that store historical or reference data.

Operations like DML, TRUNCATION, DROP, and FLASHBACK are not permitted on READ ONLY TABLE.

Read the most discussed topics of DATAWAREHOUSECONCEPTS- TOP 10 TOPICS OF 2014

     VIRTUALCOLUMN IN ORACLE 11G 

   INVISIBLEINDEXES - ORACLE 11G NEW FEATURE 

  INTRODUCTIONTO NEWSQL 

 HOW TO UNLOCKTHE LOCKED TABLE IN ORACLE 

ORACLEDATABASE 12C NEW FEATURE-FETCH& OFFSET 

  

How To Add Auto Increment In Oracle- IDENTITY column in Oracle 12c

Lets create employee table for this purpose.

SQL> CREATE TABLE EMP01

(

EMPID NUMBER,

NAME VARCHAR2(50),

DEPT VARCHAR2(20)

);

Next steps is to create oracle sequence to generated the id values.

SQL> CREATE SEQUENCE EMPID_SEQUENCE

START WITH 1

INCREMENT BY 1;

Next Step is to create Trigger to assign the values from sequence to EMPID column.

CREATE OR REPLACE TRIGGER EMPID_TRIGGER

BEFORE INSERT ON EMP01

FOR EACH ROW

BEGIN

SELECT EMPID_SEQUENCE.nextval INTO :NEW. EMPID  FROM dual;

END;

/

Now we will try insertng few values:

SQL> INSERT INTO EMP01 (NAME, DEPT) VALUES ('RON',’ABC’);

1 row created.

SQL> INSERT INTO EMP01 (NAME, DEPT) VALUES ('VICTORIA',’XYZ’);

SQL> SELECT * FROM EMP01;

EMPID NAME DEPT

---------- ------------------------------

1 RON ABC

2 VICTORIA XYZ

You can find that the EMPID getting incremented by 1.
Now there is a new feature available in on Oracle 12c version:IDENTITY column using which we can implement the same auto increment feature.

How to Unlock the Locked Table in ORACLE

Oracle puts locks while performing any DDL or DML operation on oracle tables.When table locks is present on any tables in Oracle we cannot run DDL on those tables.

Some of the locks automatically set by oracle are RS and RX Locks.

SELECT … FOR UPDATE execution results in RS (row share) table lock. When you execute an INSERT, UPDATE or DELETE Oracle puts RX (row exclusive) table lock.

We have to kill the session which holds the lock in order to execute further operations. Follow the below steps to kill the session and forcibly unlock the table.

Let’s assume that 'EMP' table is locked,

Oracle Database 12C New Feature-Fetch& Offset

In this post we will discuss about a new feature that was introduced in Oracle 12c to limit the rows returned in an easier and effective manner compared to methods used in previous releases by using ROWNUM or using Top-N rows or using analytic functions like RANK or DENSE_RANK. Using this Feature  Oracle 12c provides improved support for top-n analysis.

This new Row limiting Clause in Oracle allows us to select the Top N like queries without much effort. Let’s take a look into the syntax now.

SELECT * FROM Table_Name

ORDER BY column_name DESC

[ OFFSET offset { ROW | ROWS } ]

[ FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ]

{ ROW | ROWS } { ONLY | WITH TIES } ]

Features of The New Row limiting Clause In Oracle

Fetch Clause is to limit the number of rows returned in the result set. Fetch Keyword is followed by FIRST or NEXT which can be used interchangeably and is for semantic clarity only. Fetch clause can specify the number of rows or a percentage of rows to return which we will see by using some examples.  

Update and Delete in Correlated Sub query

Correlated sub queries as you all know are used for row-by-row processing. Here each sub query is executed once for every row of the outer query. The oracle server performs a correlated subquery when the subquery references a column from a table referred to in the parent statement. 

The general for of a correlated subquery is:

select column1, column2, . . . . . . .

from table1 outer

where [column1] operator (select column1, column2,

from table2 where expr1= outer.expr2);

To read more about Sub queries please refer our previous post

http://dwhlaureate.blogspot.in/2012/08/sub-queries-in-oracle.html

Now will see how to use correlated update and delete statements 

Introduction To NewSQL

NewSQL is a new class of Databases products that offers high performance and scalability like NoSQL for online transaction processing and also incorporates the relational database properties like SQL and ACID (atomicity, consistency, isolation and durability) transactions.

The NewSQL architecture (distributed systems architecture) significantly reduces overhead and is proficient of running on a large number of nodes without suffering bottlenecks. Also they conserve the high-level language query potential of SQL. NewSQL is now considered as an alternative to NoSQL. 

Invisible Indexes - Oracle 11g New Feature

Before 11g, Oracle didn’t have the facility to make an Index Invisible or visible……

Invisible Indexes, as the name imply is invisible to the optimizer except when we explicitly make it visible. When an index is made invisible it remains unavailable for the optimizer while running execution plans for queries. While running execution plans the optimizer decides which indexes to be used for faster execution.

View and Base Tables (Dropping of View)

What is a View?

A view is a logical table based on one or more tables or another view.View can be thought as a virtual table which takes the output of a query and stores it.

Syntax:

create [or replace] [force|noforce] view

<view_name> [column_alias_names_separated_commas]

as <subquery>[with [check option] [constraint constraint_name] [with read only]];

VIRTUAL COLUMN IN ORACLE 11G

Virtual Column is one of the new features introduced in Oracle 11g.The syntax for defining a virtual column is:

Column_Name [Datatype] [GENERATED ALWAYS] AS [Expression] [VIRTUAL]

Here, Data type: Column’s Data Type and Expression: SQL Expression

Though Virtual Columns appear to be like normal table columns they are defined by an expression and they derive their value from the result of the expression. The values of these virtual columns are not stored in database; instead they are calculated at run time dynamically.

GROUPING Function In Oracle

The Grouping function can be used with either the cube or rollup operator.  Using this function we can find the basis on which the groups are formed and it’s subtotal. It accepts a single column as a parameter and returns numeric values to identify the column value in an aggregated row. It returns 1 if the column contains a null value generated as part of a subtotal by a ROLLUP or CUBE operation and zero if it is NOT NULL

Hierarchical Retrieval In ORACLE or Hierarchical Queries

There is provision to display the reporting pattern in an organization.  In the select query we can use the level (pseudocolumn), which returns 1 for a root row and 2 for a child of a root and so on.The start with clause specifies the root of the rows. Connect by specifies relationship between the parent and the child prior clause is required for a hierarchical query.

Example:

AUTOCOMMIT in SQL- Simple and Useful

DML Statements or Data Manipulation Language statements will not be committed unless they are committed explicitly, But if you use any data definition language (DDL) statement before or after , then Oracle Database issues an implicit COMMIT.

There is a provision in Oracle to commit every DML Transaction automatically once its run. This is called AUTOCOMMIT. We can also turn AUTOCOMMIT on and off based on our requirement.

Below are commands to check for AUTOCOMMIT.

Persistent And Transient Objects( SQL/PLSQL )

There are two different types of objects namely

1. Persistent and    2. Transient.

Persistent Objects: are those that are stored in the database [Objects created using abstract data types varrays, nested tables etc.].   These can be used both with SQL commands and also in PL/SQL blocks.  These reside in the data dictionary.  Persistent objects are available to the user until they are deleted explicitly.  They can be implemented as tables, columns or attributes.  

Transient object exists only within the scope of the PL/SQL block. These get automatically de-allocated once they go out of the scope of the PL/SQL block.  Examples of transient objects are PL/SQL variables. 

IN PL/SQL:

create or replace type Type_adds as object

(

Country varchar2(40),

phase_no varchar2(100),

postal varchar2(50),

city varchar2(200),

state varchar2(150),

pin number(10)

);

Type created.

USAGE OF PARAMETERIZED CURSOR (Oracle SQL/PLSQL)

Parameter is passed in cursor in the same way as how a procedure is passed a parameter except that the parameter can only be IN mode

  

The general form of a cursor with parameters is,

cursor cursor_name [(parameter_name datatype, ……..)]

is select_statement;

Pass parameter values to a cursor when the cursor is opened and the query is executed.

Open an explicit cursor several times with a different active set each time.

open cursor_name(parameter_value,……..);

• PASSING PARAMETERS USING DYNAMIC VARIABLES

The following example displays the usage of parameterized cursor, which asks for the value from the user at runtime and passes the parameter value. 

SQL>

declare

cursor c1 (dd number) is select * from emp where deptno =dd;

y number;

begin

    for x in c1(&y)

    loop

    insert into PROD values(x.empno,x.ename);

    end loop;

    end;

/

Enter value for y: 10

old   5: for x in c1(&y)

new   5: for x in c1(10)

PL/SQL procedure successfully completed.

SQL> select * from PROD;

   EMPNO ENAME

--------- ----------

     7782 CLARK

     7839 KING

     7934 MILLER

SQL> truncate table PROD;

Table truncated.

• USE THE CURSOR ALSO FOR A “COMPUTED FIELDS” (ORACLE SQL/PLSQL)

The following example displays the use of computed fields to define a cursor. 

SQL> declare

    y number :=100;

    cursor c1 is select sal+y salary from emp;

    begin

    for x in c1

    loop

    insert into PROD values(x.salary,'in for');

    end loop;

    end;

   /

PL/SQL procedure successfully completed.

SQL> select * from PROD;

   EMPNO ENAME

--------- ----------

      900 in for

     1700 in for

     1350 in for

     3075 in for

     1350 in for

     2950 in for

     2550 in for

REGEXP_COUNT Function

This function is used to find the number of time a pattern had occurred in a string.

It takes input as string (characters) and returns output as integer .If no records found matching then the function returns 0

REGEXP_COUNT('source_char', 'pattern', position, 'match_param')

• Source_char is the input that we give. It can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

• Pattern is a text field which can be CHAR, VARCHAR2, NCHAR, or NVARCHAR2.

• Position: Tells the position from where searching needs to start. It is a positive integer

• Match_param :It can be any of these

'i' specifies case-insensitive matching

'c' specifies case-sensitive matching

'n' allows the period (.)

'm' treats the source string as multiple lines

'x' ignores white space characters.

For example

SELECT REGEXP_COUNT('123441441441', '441', 3, 'i') COUNT FROM DUAL;

     COUNT

----------

         3