large file from hive to rdbms(oracle)

Recently we have a requirement of dumping a sizable file(4+G) to oracle from s3. The file itself is hive-compatiable. so instead of downloading the file and generate sql for it, we decided to transfer the content using hive jdbc and persist in via jpa/hiberante. Hive On the hive side, one important thing is to make…

install ora2pg on mac osx

Recently we need to migrate our on prem oracle database into postgresql on aws RDS. Looks like ora2pg is a nice little tool to mitigate the problem though not perfect. I am running OS X 10.10.5 Yosemite. It already comes with perl v5.18.2 as well as cpan for installing packages. download/install Oracle client The official document…

JPA hibernate @GeneratedValue with sequence on Oracle

When using Oracle, the JPA with hibernate implementation on the @GeneratedValue with sequence would be quite tricky. I am trying to make a summary here. The version used are JPA 2.1 and Hibernate 4.3.11.Final Default generator setting @GeneratedValue with strategy AUTO and default allocationSize(50) This will use the ‘org.hibernate.id.SequenceGenerator’ which will get a SEQ+1 value….

Oracle partition table

We have an oracle table which used to use store proc to populate from other oracle table. Now since our upstream data source migrated to Hive, we need to do it thru jdbc and persist with JPA. And we hit a problem with: ‘inserted partition key does not map to any partition’. Turns out that…

Understanding Oracle Indexes

B-tree Index Standard Oracle index is B-tree based. A key is a column or expression on which you can build an index. Follow these guidelines for choosing keys to index: Consider indexing keys that are used frequently in WHERE clauses. Consider indexing keys that are used frequently to join tables in SQL statements. For more information…

FORWARD SLASH IN SQL*PLUS

Note to the visitor: Thank you for stopping by. I see a lot of people trying to find out about forward slash in SQL*Plus. I want to clarify: This post only applies to Oracle SQL. If you are querying about a “/” usage in any other database, you are in the wrong place. Forward Slash (“/”)…

create table / sequence if not exist.

it’s quite uncommon to check first and then create the table. If you want to have a running code according to your method, this will be: But I’d rather go catch on the Exception, saves you some unnecessary lines of code:

handle 1000 query problem in oracle select where IN clause

The problem is because oracle has a limit on Select A.a, B.b, C.c  FROM A, B, C where A.a in (1,2,3,4,5,6,….). The limit is 1000.  if the parameter in the (…) is over 1000, exception: java.sql.SQLException: ORA-01795 Thoughts We can use “field in (1-1000) or field in (1001-2000)….” to solve this problem. Implementation  

add oracle odbc to maven repository

If you are using Oracle, you must first install your Oracle JDBC driver in your local Maven repository. Here is how to proceed: Step 1- Download the Oracle JDBC driver Please download manually the Oracle JDBC driver from Oracle web site. Step 2- install your Oracle JDBC driver in your local Maven repository We follow the…