Friday, January 8, 2016

How to get Partitioning Information of Hive Tables in CDH

Following document has useful information for getting partition information of a hive table.

In order to query whole partitioning information on hive databases, hive metadata database can be used. It is in the MySQL server. Just login to MySQL server, switch to hive schema/database.

Following queries print partitioning summary and details

select,t.owner,t.tbl_name,t.tbl_type, count(tp.part_name)
from TBLS t, DBS db, PARTITIONS tp where t.db_id=db.db_id
and t.tbl_id=tp.tbl_id
group by,t.owner,t.tbl_name,t.tbl_type
order by 1,2,3;

select,t.owner,t.tbl_name,t.tbl_type, tp.part_name
from TBLS t, DBS db, PARTITIONS tp where t.db_id=db.db_id
and t.tbl_id=tp.tbl_id
order by 1,2,3,5;

Following query prints bucketed table information

select,t.owner, t.tbl_name,b.bucket_col_name,s.num_buckets
where b.sd_id=s.sd_id and s.cd_id=c.cd_id and b.bucket_col_name=c.column_name and s.sd_id=t.sd_id
and t.db_id=d.db_id
order by 1,2,3,4;

Monday, October 12, 2015

ODI-10165: The RunAsUser MyUser is not a valid user from external user identity store

This error occured after i switched authentication mode of ODI repository to external (Microsoft Active Directory) authentication. While developers could run scenarios successfully using configured agent, ODI-10165 was occuring for me.

The problem is that while i was switching authentication mode of ODI to external authentication i added my user to supervisor user list. My user was authenticating as superuser bu not in the security/users list. This causes problem. After i added my user to security/users, problem solved.

Thursday, September 17, 2015

Using Debug Mode in Sql Developer with KERBEROS Debug

Sql developer debug mode can be enabled with 2 steps:

1-) Set Log Directory path in Tools->Preferences->Environment

2-) At sqldeveloper\sqldeveloper\bin directory edit sqldeveloper.conf file, comment out "IncludeConfFile  sqldeveloper-nondebug.conf" and "IncludeConfFile  sqldeveloper-debug.conf"

#IncludeConfFile  sqldeveloper-nondebug.conf
IncludeConfFile  sqldeveloper-debug.conf

Restart sql developer. You will see that debug logs will be created under log directory with the name format SQLDeveloper_YYYYMMDDHH24MISS.log

In order to add kerberos debugging you shoud add following java option to sqldeveloper.conf file at sqldeveloper\sqldeveloper\bin directory


However kerberos debug information will not be written to sql developer debug log file. It is send to stdout. In order to get kerberos debug output you should start sql developer from command line. Set screen buffer size of cmd window to some value higher then the system default so some debug output will not be lost because of small buffer size. 

Sql Developer: How to set up Hive connection with Kerberos authentication on Windows client

Windows by default has restriction to retrieve TGT session key. Following registry values should be set in order to allow applications:

Windows 2003, Windows Vista, and later:
• Key:HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Lsa\Kerberos\Parameters
• Value Name: allowtgtsessionkey
• Value Type: REG_DWORD
• Value: 0x01
Windows XP and Windows 2000:
• KeyHKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Lsa\Kerberos
• Value Name: allowtgtsessionkey
• Value Type: REG_DWORD
• Value: 0x01

Sql developer 4.0.3 and later supports hive connection. My version is

Hive JDBC drivers should be downloaded and registered with sql developer as Third Party JDBC drivers. My hadoop distribution is cloudera version is 2.5.4. I downloaded the driver from following URL:
Here is how it looks like:

"Java Cryptography Extension (JCE) Unlimited Strength Jurisdiction Policy Files " should be downloaded. You should extract (overwrite) files local_policy.jar and US_export_policy.jar into sqldeveloper/jdk/jre/lib/security directory. 

krb5.conf is also needed by java for kerberos authentication. if KRB5_CONFIG is not set then it first looks into jre/lib/security then %WINDIR% directory. You should create this file under sqldeveloper/jdk/jre/lib/security (the same directory you put local_policy.jar and US_export_policy.jar files). Parameters in this file depends on your KDC configuration. In general /etc/krb5.conf file on any hadoop cluster node works fine on windows too. My krb5.conf file looks like this:

default_realm = MYDOMAIN.COM
default_tgs_enctypes = aes256-cts aes128-cts arcfour-hmac-md5 des-cbc-md5 des-cbc-crc
default_tkt_enctypes = aes256-cts aes128-cts arcfour-hmac-md5 des-cbc-md5 des-cbc-crc
permitted_enctypes = aes256-cts aes128-cts arcfour-hmac-md5 des-cbc-md5 des-cbc-crc
dns_lookup_realm = true
dns_lookup_kdc = true
passwd_check_s_address = false
noaddresses = true
udp_preference_limit = 1
ccache_type = 3
kdc_timesync = 1

Now, you can create a Hive connection with Kerberos authentication on Windows client:

Thursday, March 5, 2015

Oracle Enterprise Manager HTTPS Access Certificate Problem

In order to avoid certification problem for oracle enterprise manager https access, read following doc

Installing Browser Certificates

Tuesday, February 24, 2015

Moving Datafile from File System to ASM

If datafile is created on file system instead of ASM, following procedure can be applied to move datafile to ASM

SQL > select NAME,STATUS, ENABLED from v$datafile where TS#=nn

NAME                                                                       STATUS         ENABLED
+DATA/DBNAME/datafile/TS_NAME.nnn.nnn         ONLINE         READ WRITE
+DATA/DBNAME/datafile/TS_NAME .nnn.nnn         ONLINE         READ WRITE


NAME                                                                              STATUS         ENABLED
+DATA/DBNAME/datafile/TS_NAME.nnn.nnn               ONLINE         READ WRITE
+DATA/DBNAME/datafile/TS_NAME.nnn.nnn               ONLINE         READ WRITE
/u01/app/.../DBNAME/dbs/DATA                             ONLINE         READ WRITE  <- asm="" at="" created="" file="" font="" instead="" of="" system=""> 

Take the datafile to offline:

SQL> alter database datafile '/u01/app/.../DBNAME/dbs/DATA' offline drop;
Database altered.

SQL > select NAME, STATUS, ENABLED from v$datafile where TS#=nn

NAME                                                                        STATUS            ENABLED
+DATA/DBNAME/datafile/TS_NAME.nnn.nnn          ONLINE         READ WRITE
+DATA/DBNAME/datafile/TS_NAME.nnn.nnn          ONLINE         READ WRITE
/u01/app/.../DBNAME/dbs/DATA                        RECOVER       READ WRITE

Copy datafile to ASM using RMAN. If you use asmcmd for file copy then file will be created as non-oracle managed file (with alias)


Starting backup at ....
allocated channel...
channel ORA_DISK_1: starting datafile copy
input datafile file number=nnnnn name= /u01/app/.../DBNAME/dbs/DATA
output file name=+DATA/DBNAME/datafile/TS_NAME.nnn.nnn  tag=...
channel ORA_DISK_1: datafile copy complete, elapsed time: ...
Finished backup ...

Update file location

SQL> alter tablespace TS_NAME  rename  datafile '/u01/app/.../DBNAME/dbs/DATA' to '+DATA/DBNAME/datafile/TS_NAME.nnn.nnn';
Tablespace altered.

Recover datafile and bring it online

SQL> recover datafile '+DATA/DBNAME/datafile/TS_NAME.nnn.nnn';
Media recovery complete.

SQL > alter database datafile '+DATA/DBNAME/datafile/TS_NAME.nnn.nnn' online;
Database altered.

SQL > select NAME,CREATION_TIME, STATUS, ENABLED from v$datafile where TS#=nn

NAME                                                                        STATUS            ENABLED
+DATA/DBNAME/datafile/TS_NAME.nnn.nnn          ONLINE         READ WRITE
+DATA/DBNAME/datafile/TS_NAME.nnn.nnn          ONLINE         READ WRITE
+DATA/DBNAME/datafile/TS_NAME.nnn.nnn          ONLINE         READ WRITE

Thursday, February 19, 2015

ORA-00942 while creating materialized view

I hit following problem while creating the materialized view with option refresh fast although   SCHEMA2.TABNAME  exists and SCHEMA1 user has select privilege on  it


ORA-12018: following error encountered during code generation for  "SCHEMA1"."MVIEW "
ORA-00942: table or view does not exist

Problem is not the source table itself but the materialized view log table created before. In order to solve the problem you should identify  materialized view log table and give select permission to  materialized view owner

select LOG_TABLE from ALL_MVIEW_LOGS where log_owner=' SCHEMA2' and master='TABNAME'

grant select on  SCHEMA2.LOG_TABLE to  SCHEMA1;