Monday, December 15, 2014

Replace Consecutive Blanks With a Single Blank

We can use regexp_replace() function to replace multiple consecutive blanks with a single blank. For example, we have the following table.

SQL> select * from tbl_text;

        ID VAL
---------- --------------------------------
         1 hello     world !
         1 how   are     you doing

In the following query, regexp_replace() finds patterns that have 2 or more spaces and replace them with single blank.
SQL> select val, regexp_replace(val, '[[:space:]]{2,}',' ' ') val2 from tbl_text;

VAL                              VAL2
-------------------------------- --------------------------------
hello     world !                hello world !
how   are     you doing          how are you doing

Friday, December 12, 2014

Insert a Record into a Table Every Second

We can using Oracle function dbms_lock.sleep(n_seconds) to run queries at certain interval, such as inserting data into a table every second. Function dbms_lock.sleep(n_seconds) will suspend the session for n_seconds. To illustrate this function, we insert the system time into a table every second 20 times.

SQL> create table tbl_x (id number, dt date);

SQL> begin
for i in 1..20 loop
insert into tbl_x values(i, sysdate);
dbms_lock.sleep(1);
end loop;
commit;
end;
SQL> /

SQL> select * from tbl_x;

 ID DT
---------- -------------------
  1 12-12-2014 12:44:41
  2 12-12-2014 12:44:42
  3 12-12-2014 12:44:43
  4 12-12-2014 12:44:44
  5 12-12-2014 12:44:45
  6 12-12-2014 12:44:46
  7 12-12-2014 12:44:47
  8 12-12-2014 12:44:48
  9 12-12-2014 12:44:49
 10 12-12-2014 12:44:50
 11 12-12-2014 12:44:51
 12 12-12-2014 12:44:52
 13 12-12-2014 12:44:53
 14 12-12-2014 12:44:54
 15 12-12-2014 12:44:55
 16 12-12-2014 12:44:56
 17 12-12-2014 12:44:57
 18 12-12-2014 12:44:58
 19 12-12-2014 12:44:59
 20 12-12-2014 12:45:00

20 rows selected.

As it is shown above, records are inserted every 1 second.

Wednesday, December 10, 2014

Oracle User Privileges for Data Analysts

As data analysts, we often perform various data work within databases such as Oracle. I have found the following user privileges are very helpful. If possible, database administrators should grant us these privileges.


PRIVILEGE
----------------------------------------
CREATE TABLE
CREATE SYNONYM
CREATE VIEW
CREATE SEQUENCE
CREATE DATABASE LINK
CREATE PROCEDURE
CREATE MATERIALIZED VIEW
CREATE ANY DIRECTORY
CREATE JOB
CREATE MINING MODEL

Monday, December 08, 2014

Create Views Based on Nonexistent Tables

In Oracle, it is possible to create views first based on nonexistent tables. After views are created, we can create the underlying tables and the view will work.
To create a view based on tables that do not exist, we using "create force view" as shown below.

SQL> create force view v_tbla as select * from tbla;
Warning: View created with compilation errors.
It is OK that we got the compilation errors. The key word "force" is necessary. Without it, the view will not be created.
SQL> create view v_tbla as select * from tbla;
create view v_tbla as select * from tbla
                                    *
ERROR at line 1:
ORA-00942: table or view does not exist
If we query this view, we will get an error message. This is fine. The error message will disappear after we create the underlying table tbla.
SQL> select * from v_tbla;
select * from v_tbla
              *
ERROR at line 1:
ORA-04063: view "DMUSER.V_TBLA" has errors
Now we create table that the view is based on and populate it with data. As we see, we can query the view!
SQL> create table tbla (id number, val varchar2(32));
Table created.

SQL> insert into tbla values (1,'hello');
1 row created.

SQL> select * from tbla;

 ID VAL
---------- --------------------------------
  1 hello

SQL> select * from v_tbla;

 ID VAL
---------- --------------------------------
  1 hello

Display Long CLOB Text in Sqlplus

In the following example, the display of CLOB text is truncated.

SQL> create table tbl_test (id number, val clob);

Table created.

SQL> insert into tbl_test values(1,'this is a test.ddlkjdfklj dflkjdlkj 
 jdfhi  dfdlkjflkdlkoieuiooiop jdofoidu  baskjhfp  dfdk dkfiegps 
  dfdalkfaidel hdlfjdlflkd dafjdflkdi igod kdigl dfodfud');

1 row created.

SQL> select * from tbl_test;

        ID
----------
VAL
--------------------------------------------------------------------------------
         1
this is a test.ddlkjdfklj  dflkjdlkj  jdfhi  dfdlkjflkdlkoieuiooiop 
jdofoidu  ba

We can use "set long" to specify how many bytes of CLOB to display. In the following example, "set long 2000" tells sqlplus to display up to 2000 bytes of CLOB text.
SQL> set long 2000
SQL> select * from tbl_test;

        ID
----------
VAL
--------------------------------------------------------------------------------
         1
this is a test.ddlkjdfklj  dflkjdlkj  jdfhi  dfdlkjflkdlkoieuiooiop 
jdofoidu  baskjhfp  dfdk  dkfiegps   dfdalkfaidel hdlfjdlflkd dafjdflkdi
 igod kdigl dfodfud 

Sunday, December 07, 2014

Dump the Oracle Schema

We can use Oracle data pump utility to dump the whole schema or selected database objects. The following command dump the schema.

$ expdp prod/XXXX schemas=prod directory=ORA_DATA_EBS_BK dumpfile=prod_bk.dmp logfile=prod_bk.log
The above command creates two files under directory ORA_DATA_EBS_BK.
-rw-r--r-- 1 oracle dba       5129 Dec  7 08:07 prod_bk.log
-rw-r----- 1 oracle dba    2650112 Dec  7 08:07 prod_bk.dmp
We can import them back into a database using impdp command.
$ impdp prod/XXXX schemas=prod directory=ORA_DATA_EBS_BK dumpfile=prod_bk.dmp

Thursday, November 20, 2014

SQL Scripts for Oracle Database Link

In posts Get the source code for Oracle database views and materialized view and Get source code for Oracle database objects: user_source view, we show how to get the SQL scripts that create some database objects.

To get the SQL scripts for existing database links, we can take advantage of dbms_metadata.get_ddl().The following query returns the database links created.

SQL> select  owner, db_link from all_db_links order by owner, db_link;

OWNER                          DB_LINK
------------------------------ ----------------
PROD                           DL_DATA
PROD                           DL_EC1
To get the original SQL scripts that create these database links, we use dbms_metadata.get_ddl() as shown below.
SQL> select dbms_metadata.get_ddl('DB_LINK', 'DL_EC1') from dual;

DBMS_METADATA.GET_DDL('DB_LINK','DL_EC1')
--------------------------------------------------------------------------------

  CREATE DATABASE LINK "DL_EC1"    USING '(description=(address=(protocol=TCP)
(host=ec2-xx-xx-xxx-178.compute-1.amazonaws.com)(port=1521))
(connect_data=(sid=XE)))'

Tuesday, November 18, 2014

Drop User in Oracle

Post Drop All Objects In an Oracle Schema uses PL/SQL to iteratively drop database objects. We can simply drop a user and its database objects in Oracle using "drop user.. cascade". This provides an easy way to clean up the database.

We have to drop a user by connecting to another user. If we try to drop a user that we are currently connecting to, we will get error ORA-01940. The Sqlplus session is connected to user1.

SQL> drop user user1 cascade;
drop user user1 cascade
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected
The user that we are connecting to needs to have "drop user" privilege. In the following example, the Sqlplus session is connected to user2.
SQL> drop user user1 cascade;
rop user user1 cascade

RROR at line 1:
RA-01031: insufficient privileges
We connected to sys and grant "drop user" privilege to user2.
SQL> grant drop user to user2;

Grant succeeded.
Now we connect to user2 and are able to drop user1
SQL> drop user user1 cascade;

User dropped.

Monday, October 06, 2014

Five Trends in Big Data Analytics

Big data are described as having big volume, complex structures and being updated frequently. Analytics, the technology that extract meaningful information from the "raw" data to support decision, is the ultimate driver of the value of the big data. After extensive research, deep-data-mining.com has identified the following five trends in big data analytics.

1. SQL Based In-Database Analytics

Data analytics functions are built within relational database engine. Users take advantage of SQL to perform data mining/predictive analytics task. All processes including data extraction, data preparation, predictive model building and validation, and model deployment are done within the database. SQL based in-database analytics will become a trend, particularly in the enterprise environment. This observation is based on the following facts. Most of the core enterprise data are stored in relationship databases. A lot of business logic that support the daily operation of an enterprises are written in SQL. There are huge number of SQL developers around the world and many of who will be able to perform data analytics task without learning other scripting languages.

2. Apache Spark

According to The Apache Software Foundation, Apache is a fast and general engine for large-scale data processing. Apache Spark runs programs much faster. Its machine learning library MLlib include SVM, logistic regression, decision tree and k-means clustering, etc.

3. the Proliferation R Language

R is a statistical analysis language that is much more "natural" to data scientists than other programming languages such as Java, C or SQL. For example, the R scripts to manipulate vector/matrix or build predictive models are very similar to the mathematics equations found in textbooks. For example, the following R script builds a logistic regression model that predicts y based a, b and c.
  glm(y ~ a+ b + c, data = trainset, family = binomial(link = "logit"))
There is a big community of R users who develop R algorithms and share them in the format of R packages. Thus, we can find almost all data analytics algorithms in R packages.

4. Real Time, In Memory Data Analytics

Traditionally, raw data are that are collected in real time in computer memory but are transformed and loaded into disc-based data warehouse periodically. The data are analyzed offline in a delayed fashion. For example, it is not unusual for a large enterprise to take weeks, if not months, to build a predictive model, test and eventually deploy it. Due to the slowness in identifying new useful patterns in the data, opportunities are lost in the case of new sales or risks are realized in the case of fraud prevention. Thus, there will be a trend to shift traditional offline, disk-based data analytics to online, in-mermory, real time environment.

5. Innovative Data Analytics Applications

As we know, the ultimate purpose of big data is to provide data-driven decision support to solve problems. There will be more and more innovative applications of big data analytics. For example, polices will use model to predict potential repeated criminals. Colleges predict in advance if a student will choose to drop from the school based his background and current situation. Human resource department in a large company can design the best career paths for its employees using models. Applications of data analytics is unlimited.

Sunday, September 28, 2014

Update Multiple Columns In a Table

In the post Update a Table Based on Another Table, we showed how to update a column in a table based on the result from a select query. To update multiple columns at the same time, we simply specify multiple columns after "set" and "select".

SQL> select * from tbl_a order by id;

        ID VALUE            VALUE2
---------- ---------------- ----------------
         1 A
         2 B
         3 C
         4 D

SQL> update tbl_a a set (value, value2) 
                        = ( select a.value||'_1', a.value||'_2' 
                            from tbl_a b where a.id=b.id);

4 rows updated.

SQL> select * from tbl_a order by id;

        ID VALUE            VALUE2
---------- ---------------- ----------------
         1 A_1              A_2
         2 B_1              B_2
         3 C_1              C_2
         4 D_1              D_2

Update a Table Based on Another Table

It is a common task to update records in one table based on those in another table. We can use "update" query to do this. Suppose we have two tables as shown below.

SQL> select * from tbl_1 order by id;

        ID VALUE
---------- --------------------------------
         1 A
         2 X
         3 Y
         4 D

SQL> select * from tbl_2 order by id;

        ID VALUE
---------- --------------------------------
         2 B
         3 C
We want to update the val in tbl_1 with the value in tbl_2 based on id. The desirable result for tbl_1 after the update will be:
       ID VALUE
---------- --------------------------------
         1 A
         2 B
         3 C
         4 D
One way to do this is the following query.

SQL> update tbl_1 a 
set value=(select value from tbl_2 b where a.id=b.id) 
where exists ( select 1 from tbl_2 b where a.id=b.id);

2 rows updated.

SQL> select * from tbl_1 order by id;

        ID VAL
---------- --------------------------------
         1 A
         2 B
         3 C
         4 D
Please notice in the update query, only 2 rows are updated due to the "where exists" clause. If we do not include the "where exists" clause, records whose id are not in tbl_2 will have null values as shown below.
SQL> update tbl_1 a set val=(select val from tbl_2 b where a.id=b.id) ;

4 rows updated.

SQL> select * from tbl_1 order by id;

        ID VALUE
---------- --------------------------------
         1
         2 B
         3 C
         4
In the above update query, 4, instead of 2, rows are updates due to the removal of the "where exists" clause. We can change the above update query without using "where exists" to make it work as the following.
SQL> update tbl_1 a set val=nvl((select value from tbl_2 b where a.id=b.id),a.value) ;

4 rows updated.

SQL> select * from tbl_1 order by id;

        ID VALUE
---------- --------------------------------
         1 A
         2 B
         3 C
         4 D
In the above update query, we added function nvl((select ...), a.value) which means if the select query returns no result (NULL), values will be set as the original values. All four rows are updated. The first update query with "where exists" clause is the best because it only update rows whose id in tbl_2.

Saturday, September 13, 2014

Insert Data into an Oracle View

We are able to insert records into a simple view. By doing that, the data are actually inserted into the physical table that the view is based on. The following is an example.


SQL> create table tbl_test (id number, value varchar2(64));

Table created.

SQL> create view v_tbl_test as select * from tbl_test;

View created.

SQL> insert into tbl_test values(1,'Hello');

1 row created.

SQL> insert into v_tbl_test values(2,'World');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from tbl_test order by id;

        ID VALUE
---------- ----------------------------------------------------------------
         1 Hello
         2 World

SQL> select * from v_tbl_test order by id;

        ID VALUE
---------- ----------------------------------------------------------------
         1 Hello
         2 World

Wednesday, August 27, 2014

Oracle Multiple Table Insert

We can insert the selected results into multiple tables using one query "insert all".


SQL> create table t2 (val number);

Table created.

SQL> create table t2 (val number);

Table created.

SQL> insert all into t1 into t2 select 1 from dual;

2 rows created.

SQL> select * from t1;

  VAL
-----
    1

SQL> select * from t2;

  VAL
-----
    1
However, multiple table insert does not work for remote tables. We can only insert data into one remote table at a time.
SQL> insert all into t1 into t3@DL_ANOTHER_DB select 1 from dual;
insert all into t3 into t2@DL_APEX select 1 from dual
                           *
ERROR at line 1:
ORA-02021: DDL operations are not allowed on a remote database

SQL> insert  into t2@DL_APEX select 1 from dual;

1 row created.

Sunday, August 24, 2014

Oracle Character Return in a String

If a string is too long to display, we can put character returns using "chr(10)" as shown below. As you can see, we can format the long string better. When we generate a long message such as an email from PL/SQL, chr(0) is very helpful.

SQL> select 'hello world!' as message from dual;

MESSAGE
------------
hello world!

SQL> select 'hello'||chr(10)||'world!' as message from dual;

MESSAGE
------------
hello
world!

Schedule Oracle Procedure

We may want to run our Oracle procedures on a regular basis. For example, every day at 10PM, I want to get new transactions from a database schema, calculate the risk score and insert the result into a table. I first created a procedure, proc_all, and then I use DBMS_SCHEDULER.create_job() to schedule the job. This procedure will run automatically every day at 10pm.

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'my_proc',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN proc_all; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=daily; byhour=22;',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Job defined entirely by the CREATE JOB procedure.');
END;
/
If we want to stop the job, we can drop it using DBMS_SCHEDULER.drop_job()
begin DBMS_SCHEDULER.drop_job(job_name => 'my_proc'); end;
We can query views DBA_SCHEDULER_JOBS or USER_SCHEDULER_JOBS to get the status of scheduled jobs.
SQL> select JOB_NAME,JOB_ACTION from USER_SCHEDULER_JOBS;

JOB_NAME                         JOB_ACTION
-------------------------- ------------------------------------------------------------------
MY_PROC                   BEGIN proc_all; END;
PROC_CLEAR_TBL            BEGIN execute immediate 'truncate table tbl_tobe_alerted'; END;

Friday, August 22, 2014

Refresh an Oracle Materialized View

An Oracle materialized view is a very useful feature in situations including data refresh on regular basis. When we create a materialized view, it takes a snapshot of query result and store it physically. This is different from a view where only query logic is stored with the view. Once the materialized view is created, its content does not change until it is refreshed. When we need to refresh the materialized view, one way to do it is to use dbms_mview.refresh().

SQL> create materialized view mv_test_refresh as select sysdate dt 
     from dual;

Materialized view created.
SQL> select to_char(dt, 'YYYYMMDD:HH24:Mi:SS') from mv_test_refresh;

TO_CHAR(DT,'YYYYM
-----------------
20140801:10:42:47

SQL> exec dbms_mview.refresh('mv_test_refresh');

PL/SQL procedure successfully completed.

SQL> select to_char(dt, 'YYYYMMDD:HH24:Mi:SS') from mv_test_refresh;

TO_CHAR(DT,'YYYYM
-----------------
20140801:10:50:31
Alternatively, we can create a refresh group to include materialized views that we want to refresh. Then we can run dbms_refresh.refresh() to refresh all materialized views included in the group.
SQL> exec dbms_refresh.make(name=>'test_grp', list=>'mv_test_refresh', 
next_date=>sysdate, interval=>'null');

SQL> exec dbms_refresh.refresh('test_grp');

PL/SQL procedure successfully completed.

SQL> select to_char(dt, 'YYYYMMDD:HH24:Mi:SS') from mv_test_refresh;

TO_CHAR(DT,'YYYYM
-----------------
20140801:10:54:35
We can also schedule when a materialized view will be refreshed automatically. That will be the topic of another post.

Tuesday, July 22, 2014

Get Current Date In Oracle and Server SQL

To get the current system date, we use sysdate in Oracle and getdate() in SQL Server, respectively.

Using Oracle sqlplus.

SQL> select sysdate from dual;

SYSDATE
-------------------
07-22-2014 07:21:21
Using SQL Sever Management Studio.
select getdate()
2014-07-22 11:23:23.097

Wednesday, July 16, 2014

More on Business Rules vs Statistical Models

In a post Predictive Modeling vs Intuitive Business Rules, we described advantages of data-driven models over intuitive rules. The limitations of our thinking are nicely described in a free book written by a former CIA veteran, Richards J. Heuer Psychology of Intelligence Analysis. It is one of my favorite books and I highly recommend it.I have found often we are too confident about our perception and intuition and not aware that we are wrong. (For example, some fraud detection analysts use her intuitions/experience to reject statistically derived optimal fraud scores). Data-driven statistical models are powerful tools to resolve the limitations in our mental processes.

Sunday, June 08, 2014

a Summary of Blog Posts by Topics

The following is a summary of the blog posts grouped by topics.

Build Predictive Models

Predictive Models vs Business Rules

Predictive Model Variables

Import Text File Into Oracle

Unique Identifiers in Oracle

Recency Frequency Monetary

Oracle Materialized View

Database Link to Oracle on Amazon

Calculate Percent Using SQL

Calculate Cumulative Percent Using SQL

Find Score Cutoff Value Using SQL

Remove Duplicates Using SQL

Calculate Correlation Coefficients Using SQL

Oracle vs SQL Server

NULL Value

Logistic Regression

Random Sampling

Table Insert

Read Only Table

Clustering

Ranking

Find Most Frequent

Median Value

Oracle Source Code

Debug PL/SQL

Hide PL/SQL Scripts

Repair Views

Dump Schema

Move Big Files to Amazon

Sunday, June 01, 2014

MS SQL Server iff function vs "case when"

In SQL server, people sometimes use iff function, which is also an Excel function, to assign different values based on a condition. For example, the following query returns 1 if the amt is less than 50, and 0 otherwise.

select id, iif(amt <50, 1,0) as small_amt from tbl_transaction
However, using iff function is not the best practice as iff is not a standard SQL function. The above query only runs successfully on SQL Server. To make the query work for any relational database, such as Oracle, it is better to use SQL standard "case when" statement as shown below.
select id, case when amt<50 then 1 else 0 end small_amt from tbl_transacton
The above query will run successfully on both Oracle and SQL Server databases as they support SQL standard.