Friday, May 26, 2017

SQL Server global temporary table and temporary table differences

create table ##t1 (id int);

declare @str1 nvarchar(100);
set @str1 = 'insert into ##t1 values (1)';
execute sp_executesql @str1

select * from ##t1



(1 row(s) affected)
id
-----------
1

(1 row(s) affected)

create table #t2 (id int);
declare @str2 nvarchar(100);
set @str2 = 'insert into #t2 values (2)';
execute sp_executesql @str2

select * from #t2


(1 row(s) affected)

id
-----------
2

(1 row(s) affected)

==============================



declare @str1 nvarchar(100);
set @str1 = 'create table ##t1 (id int);insert into ##t1 values (1)';
execute sp_executesql @str1

select * from ##t1


(1 row(s) affected)
id
-----------
1

(1 row(s) affected)


create table #t2 (id int);
declare @str2 nvarchar(100);
set @str2 = 'insert into #t2 values (2)';
execute sp_executesql @str2

select * from #t2


(1 row(s) affected)

Msg 208, Level 16, State 0, Line 14
Invalid object name '#t2'.


Saturday, May 6, 2017

Build GPDB with Planner

 Reference: https://github.com/greenplum-db/gpdb/blob/master/README.md


[root@localhost gpdb-5.0.0-alpha.2]# mkdir /usr/local/gpdb/
[root@localhost gpdb-5.0.0-alpha.2]# chown donghua:donghua /usr/local/gpdb/

[donghua@localhost gpdb-5.0.0-alpha.2]$ pwd
/home/donghua/gpdb-5.0.0-alpha.2

[donghua@localhost gpdb-5.0.0-alpha.2]$ # Clean environment
[donghua@localhost gpdb-5.0.0-alpha.2]$ make distclean

[donghua@localhost gpdb-5.0.0-alpha.2]$ # Configure build environment to install at /usr/local/gpdb
[donghua@localhost gpdb-5.0.0-alpha.2]$ ./configure --with-perl --with-python --with-libxml --enable-mapreduce --prefix=/usr/local/gpdb

[donghua@localhost gpdb-5.0.0-alpha.2]$ # Compile and install
[donghua@localhost gpdb-5.0.0-alpha.2]$ make
..
make[1]: Leaving directory `/home/donghua/gpdb-5.0.0-alpha.2/gpAux/gpperfmon'
All of Greenplum Database successfully made. Ready to install.

[donghua@localhost gpdb-5.0.0-alpha.2]$ make install
..
Greenplum Database installation complete.
[donghua@localhost gpdb-5.0.0-alpha.2]$ # Bring in greenplum environment into your running shell
[donghua@localhost gpdb-5.0.0-alpha.2]$ source /usr/local/gpdb/greenplum_path.sh
[donghua@localhost gpdb-5.0.0-alpha.2]$ # Start demo cluster (gpdemo-env.sh is created which contain
[donghua@localhost gpdb-5.0.0-alpha.2]$ # __PGPORT__ and __MASTER_DATA_DIRECTORY__ values)
[donghua@localhost gpdb-5.0.0-alpha.2]$ cd gpAux/gpdemo



# configure ssh passwordless login before make cluster
[donghua@localhost gpdemo]$ make cluster
======================================================================
            ______  _____  ______  _______ _______  _____
           |  ____ |_____] |     \ |______ |  |  | |     |
           |_____| |       |_____/ |______ |  |  | |_____|

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

  This is a demo of the Greenplum Database system.  We will create
  a cluster installation with master and 6 segment instances
  (3 primary & 3 mirror).

    GPHOME ................. : /usr/local/gpdb
    MASTER_DATA_DIRECTORY .. : /home/donghua/gpdb-5.0.0-alpha.2/gpAux/gpdemo/datadirs/qddir/demoDataDir-1

    MASTER PORT (PGPORT) ... : 15432
    SEGMENT PORTS .......... : 25432 25433 25434 25435 25436 25437 25438 25439 25440 25441 25442 25443


  NOTE(s):

    * The DB ports identified above must be available for use.
    * An environment file gpdemo-env.sh has been created for your use.

======================================================================

==========================================================================================
executing:
  /usr/local/gpdb/bin/gpinitsystem -a -c clusterConfigFile -l /home/donghua/gpdb-5.0.0-alpha.2/gpAux/gpdemo/datadirs/gpAdminLogs ""
==========================================================================================

20170505:23:36:44:014693 gpinitsystem:localhost:donghua-[INFO]:-Checking configuration parameters, please wait...
20170505:23:36:44:014693 gpinitsystem:localhost:donghua-[INFO]:-Reading Greenplum configuration file clusterConfigFile
20170505:23:36:44:014693 gpinitsystem:localhost:donghua-[INFO]:-Locale has not been set in clusterConfigFile, will set to default value
20170505:23:36:44:014693 gpinitsystem:localhost:donghua-[INFO]:-Locale set to en_US.utf8
20170505:23:36:44:014693 gpinitsystem:localhost:donghua-[INFO]:-No DATABASE_NAME set, will exit following template1 updates
20170505:23:36:44:014693 gpinitsystem:localhost:donghua-[INFO]:-MASTER_MAX_CONNECT not set, will set to default value 25
20170505:23:36:44:014693 gpinitsystem:localhost:donghua-[INFO]:-Detected a single host GPDB array build, reducing value of BATCH_DEFAULT from 60 to 4
20170505:23:36:44:014693 gpinitsystem:localhost:donghua-[WARN]:-Master open file limit is 1024 should be >= 65535
20170505:23:36:44:014693 gpinitsystem:localhost:donghua-[INFO]:-Checking configuration parameters, Completed
20170505:23:36:44:014693 gpinitsystem:localhost:donghua-[INFO]:-Commencing multi-home checks, please wait...
.
20170505:23:36:45:014693 gpinitsystem:localhost:donghua-[INFO]:-Configuring build for standard array
20170505:23:36:45:014693 gpinitsystem:localhost:donghua-[INFO]:-Commencing multi-home checks, Completed
20170505:23:36:45:014693 gpinitsystem:localhost:donghua-[INFO]:-Building primary segment instance array, please wait...
...
20170505:23:36:45:014693 gpinitsystem:localhost:donghua-[INFO]:-Building group mirror array type , please wait...
...
20170505:23:36:46:014693 gpinitsystem:localhost:donghua-[INFO]:-Checking Master host
20170505:23:36:46:014693 gpinitsystem:localhost:donghua-[INFO]:-Checking new segment hosts, please wait...
20170505:23:36:46:014693 gpinitsystem:localhost:donghua-[WARN]:-Host localhost.localdomain open files limit is 1024 should be >= 65535
.20170505:23:36:46:014693 gpinitsystem:localhost:donghua-[WARN]:-----------------------------------------------------------
20170505:23:36:46:014693 gpinitsystem:localhost:donghua-[WARN]:-Host localhost.localdomain is assigned as localhost in /etc/hosts
20170505:23:36:46:014693 gpinitsystem:localhost:donghua-[WARN]:-This will cause segment->master communication failures
20170505:23:36:46:014693 gpinitsystem:localhost:donghua-[WARN]:-Remove localhost.localdomain from local host line in /etc/hosts
20170505:23:36:46:014693 gpinitsystem:localhost:donghua-[WARN]:-----------------------------------------------------------
.20170505:23:36:46:014693 gpinitsystem:localhost:donghua-[WARN]:-----------------------------------------------------------
20170505:23:36:46:014693 gpinitsystem:localhost:donghua-[WARN]:-Host localhost.localdomain is assigned as localhost in /etc/hosts
20170505:23:36:46:014693 gpinitsystem:localhost:donghua-[WARN]:-This will cause segment->master communication failures
20170505:23:36:46:014693 gpinitsystem:localhost:donghua-[WARN]:-Remove localhost.localdomain from local host line in /etc/hosts
20170505:23:36:46:014693 gpinitsystem:localhost:donghua-[WARN]:-----------------------------------------------------------
.20170505:23:36:47:014693 gpinitsystem:localhost:donghua-[WARN]:-----------------------------------------------------------
20170505:23:36:47:014693 gpinitsystem:localhost:donghua-[WARN]:-Host localhost.localdomain is assigned as localhost in /etc/hosts
20170505:23:36:47:014693 gpinitsystem:localhost:donghua-[WARN]:-This will cause segment->master communication failures
20170505:23:36:47:014693 gpinitsystem:localhost:donghua-[WARN]:-Remove localhost.localdomain from local host line in /etc/hosts
20170505:23:36:47:014693 gpinitsystem:localhost:donghua-[WARN]:-----------------------------------------------------------
...
20170505:23:36:47:014693 gpinitsystem:localhost:donghua-[INFO]:-Checking new segment hosts, Completed
20170505:23:36:47:014693 gpinitsystem:localhost:donghua-[INFO]:-Building the Master instance database, please wait...
20170505:23:36:52:014693 gpinitsystem:localhost:donghua-[INFO]:-Starting the Master in admin mode
20170505:23:36:58:014693 gpinitsystem:localhost:donghua-[INFO]:-Commencing parallel build of primary segment instances
20170505:23:36:58:014693 gpinitsystem:localhost:donghua-[INFO]:-Spawning parallel processes    batch [1], please wait...
...
20170505:23:36:59:014693 gpinitsystem:localhost:donghua-[INFO]:-Waiting for parallel processes batch [1], please wait...
..............
20170505:23:37:14:014693 gpinitsystem:localhost:donghua-[INFO]:------------------------------------------------
20170505:23:37:14:014693 gpinitsystem:localhost:donghua-[INFO]:-Parallel process exit status
20170505:23:37:14:014693 gpinitsystem:localhost:donghua-[INFO]:------------------------------------------------
20170505:23:37:14:014693 gpinitsystem:localhost:donghua-[INFO]:-Total processes marked as completed           = 3
20170505:23:37:14:014693 gpinitsystem:localhost:donghua-[INFO]:-Total processes marked as killed              = 0
20170505:23:37:14:014693 gpinitsystem:localhost:donghua-[INFO]:-Total processes marked as failed              = 0
20170505:23:37:14:014693 gpinitsystem:localhost:donghua-[INFO]:------------------------------------------------
20170505:23:37:14:014693 gpinitsystem:localhost:donghua-[INFO]:-Commencing parallel build of mirror segment instances
20170505:23:37:14:014693 gpinitsystem:localhost:donghua-[INFO]:-Spawning parallel processes    batch [1], please wait...
...
20170505:23:37:14:014693 gpinitsystem:localhost:donghua-[INFO]:-Waiting for parallel processes batch [1], please wait...
...........
20170505:23:37:26:014693 gpinitsystem:localhost:donghua-[INFO]:------------------------------------------------
20170505:23:37:26:014693 gpinitsystem:localhost:donghua-[INFO]:-Parallel process exit status
20170505:23:37:26:014693 gpinitsystem:localhost:donghua-[INFO]:------------------------------------------------
20170505:23:37:26:014693 gpinitsystem:localhost:donghua-[INFO]:-Total processes marked as completed           = 3
20170505:23:37:26:014693 gpinitsystem:localhost:donghua-[INFO]:-Total processes marked as killed              = 0
20170505:23:37:26:014693 gpinitsystem:localhost:donghua-[INFO]:-Total processes marked as failed              = 0
20170505:23:37:26:014693 gpinitsystem:localhost:donghua-[INFO]:------------------------------------------------
20170505:23:37:26:014693 gpinitsystem:localhost:donghua-[INFO]:-Deleting distributed backout files
20170505:23:37:26:014693 gpinitsystem:localhost:donghua-[INFO]:-Removing back out file
20170505:23:37:26:014693 gpinitsystem:localhost:donghua-[INFO]:-No errors generated from parallel processes
20170505:23:37:26:014693 gpinitsystem:localhost:donghua-[INFO]:-Restarting the Greenplum instance in production mode
20170505:23:37:27:000766 gpstop:localhost:donghua-[INFO]:-Starting gpstop with args: -a -l /home/donghua/gpdb-5.0.0-alpha.2/gpAux/gpdemo/datadirs/gpAdminLogs -i -m -d /home/donghua/gpdb-5.0.0-alpha.2/gpAux/gpdemo/datadirs/qddir/demoDataDir-1
20170505:23:37:27:000766 gpstop:localhost:donghua-[INFO]:-Gathering information and validating the environment...
20170505:23:37:27:000766 gpstop:localhost:donghua-[INFO]:-Obtaining Greenplum Master catalog information
20170505:23:37:27:000766 gpstop:localhost:donghua-[INFO]:-Obtaining Segment details from master...
20170505:23:37:27:000766 gpstop:localhost:donghua-[INFO]:-Greenplum Version: 'postgres (Greenplum Database) 5.0.0-alpha build dev'
20170505:23:37:27:000766 gpstop:localhost:donghua-[INFO]:-There are 0 connections to the database
20170505:23:37:27:000766 gpstop:localhost:donghua-[INFO]:-Commencing Master instance shutdown with mode='immediate'
20170505:23:37:27:000766 gpstop:localhost:donghua-[INFO]:-Master host=localhost.localdomain
20170505:23:37:27:000766 gpstop:localhost:donghua-[INFO]:-Commencing Master instance shutdown with mode=immediate
20170505:23:37:27:000766 gpstop:localhost:donghua-[INFO]:-Master segment instance directory=/home/donghua/gpdb-5.0.0-alpha.2/gpAux/gpdemo/datadirs/qddir/demoDataDir-1
20170505:23:37:28:000766 gpstop:localhost:donghua-[INFO]:-Attempting forceful termination of any leftover master process
20170505:23:37:28:000766 gpstop:localhost:donghua-[INFO]:-Terminating processes for segment /home/donghua/gpdb-5.0.0-alpha.2/gpAux/gpdemo/datadirs/qddir/demoDataDir-1
20170505:23:37:28:000856 gpstart:localhost:donghua-[INFO]:-Starting gpstart with args: -a -l /home/donghua/gpdb-5.0.0-alpha.2/gpAux/gpdemo/datadirs/gpAdminLogs -d /home/donghua/gpdb-5.0.0-alpha.2/gpAux/gpdemo/datadirs/qddir/demoDataDir-1
20170505:23:37:28:000856 gpstart:localhost:donghua-[INFO]:-Gathering information and validating the environment...
20170505:23:37:28:000856 gpstart:localhost:donghua-[INFO]:-Greenplum Binary Version: 'postgres (Greenplum Database) 5.0.0-alpha build dev'
20170505:23:37:28:000856 gpstart:localhost:donghua-[INFO]:-Greenplum Catalog Version: '301703191'
20170505:23:37:28:000856 gpstart:localhost:donghua-[INFO]:-Starting Master instance in admin mode
20170505:23:37:29:000856 gpstart:localhost:donghua-[INFO]:-Obtaining Greenplum Master catalog information
20170505:23:37:29:000856 gpstart:localhost:donghua-[INFO]:-Obtaining Segment details from master...
20170505:23:37:29:000856 gpstart:localhost:donghua-[INFO]:-Setting new master era
20170505:23:37:29:000856 gpstart:localhost:donghua-[INFO]:-Master Started...
20170505:23:37:29:000856 gpstart:localhost:donghua-[INFO]:-Shutting down master
20170505:23:37:30:000856 gpstart:localhost:donghua-[INFO]:-Commencing parallel primary and mirror segment instance startup, please wait...
..
20170505:23:37:32:000856 gpstart:localhost:donghua-[INFO]:-Process results...
20170505:23:37:32:000856 gpstart:localhost:donghua-[INFO]:-----------------------------------------------------
20170505:23:37:32:000856 gpstart:localhost:donghua-[INFO]:-   Successful segment starts                                            = 6
20170505:23:37:32:000856 gpstart:localhost:donghua-[INFO]:-   Failed segment starts                                                = 0
20170505:23:37:32:000856 gpstart:localhost:donghua-[INFO]:-   Skipped segment starts (segments are marked down in configuration)   = 0
20170505:23:37:32:000856 gpstart:localhost:donghua-[INFO]:-----------------------------------------------------
20170505:23:37:32:000856 gpstart:localhost:donghua-[INFO]:-
20170505:23:37:32:000856 gpstart:localhost:donghua-[INFO]:-Successfully started 6 of 6 segment instances
20170505:23:37:32:000856 gpstart:localhost:donghua-[INFO]:-----------------------------------------------------
20170505:23:37:32:000856 gpstart:localhost:donghua-[INFO]:-Starting Master instance localhost.localdomain directory /home/donghua/gpdb-5.0.0-alpha.2/gpAux/gpdemo/datadirs/qddir/demoDataDir-1
20170505:23:37:33:000856 gpstart:localhost:donghua-[INFO]:-Command pg_ctl reports Master localhost.localdomain instance active
20170505:23:37:33:000856 gpstart:localhost:donghua-[INFO]:-No standby master configured.  skipping...
20170505:23:37:33:000856 gpstart:localhost:donghua-[INFO]:-Database successfully started
20170505:23:37:34:014693 gpinitsystem:localhost:donghua-[INFO]:-Completed restart of Greenplum instance in production mode
20170505:23:37:34:014693 gpinitsystem:localhost:donghua-[INFO]:-Scanning utility log file for any warning messages
20170505:23:37:34:014693 gpinitsystem:localhost:donghua-[WARN]:-*******************************************************
20170505:23:37:34:014693 gpinitsystem:localhost:donghua-[WARN]:-Scan of log file indicates that some warnings or errors
20170505:23:37:34:014693 gpinitsystem:localhost:donghua-[WARN]:-were generated during the array creation
20170505:23:37:34:014693 gpinitsystem:localhost:donghua-[INFO]:-Please review contents of log file
20170505:23:37:34:014693 gpinitsystem:localhost:donghua-[INFO]:-/home/donghua/gpdb-5.0.0-alpha.2/gpAux/gpdemo/datadirs/gpAdminLogs/gpinitsystem_20170505.log
20170505:23:37:34:014693 gpinitsystem:localhost:donghua-[INFO]:-To determine level of criticality
20170505:23:37:34:014693 gpinitsystem:localhost:donghua-[WARN]:-*******************************************************
20170505:23:37:34:014693 gpinitsystem:localhost:donghua-[INFO]:-Greenplum Database instance successfully created
20170505:23:37:34:014693 gpinitsystem:localhost:donghua-[INFO]:-------------------------------------------------------
20170505:23:37:34:014693 gpinitsystem:localhost:donghua-[INFO]:-To complete the environment configuration, please
20170505:23:37:34:014693 gpinitsystem:localhost:donghua-[INFO]:-update donghua .bashrc file with the following
20170505:23:37:34:014693 gpinitsystem:localhost:donghua-[INFO]:-1. Ensure that the greenplum_path.sh file is sourced
20170505:23:37:34:014693 gpinitsystem:localhost:donghua-[INFO]:-2. Add "export MASTER_DATA_DIRECTORY=/home/donghua/gpdb-5.0.0-alpha.2/gpAux/gpdemo/datadirs/qddir/demoDataDir-1"
20170505:23:37:34:014693 gpinitsystem:localhost:donghua-[INFO]:-   to access the Greenplum scripts for this instance:
20170505:23:37:34:014693 gpinitsystem:localhost:donghua-[INFO]:-   or, use -d /home/donghua/gpdb-5.0.0-alpha.2/gpAux/gpdemo/datadirs/qddir/demoDataDir-1 option for the Greenplum scripts
20170505:23:37:34:014693 gpinitsystem:localhost:donghua-[INFO]:-   Example gpstate -d /home/donghua/gpdb-5.0.0-alpha.2/gpAux/gpdemo/datadirs/qddir/demoDataDir-1
20170505:23:37:34:014693 gpinitsystem:localhost:donghua-[INFO]:-Script log file = /home/donghua/gpdb-5.0.0-alpha.2/gpAux/gpdemo/datadirs/gpAdminLogs/gpinitsystem_20170505.log
20170505:23:37:34:014693 gpinitsystem:localhost:donghua-[INFO]:-To remove instance, run gpdeletesystem utility
20170505:23:37:34:014693 gpinitsystem:localhost:donghua-[INFO]:-To initialize a Standby Master Segment for this Greenplum instance
20170505:23:37:34:014693 gpinitsystem:localhost:donghua-[INFO]:-Review options for gpinitstandby
20170505:23:37:34:014693 gpinitsystem:localhost:donghua-[INFO]:-------------------------------------------------------
20170505:23:37:34:014693 gpinitsystem:localhost:donghua-[INFO]:-The Master /home/donghua/gpdb-5.0.0-alpha.2/gpAux/gpdemo/datadirs/qddir/demoDataDir-1/pg_hba.conf post gpinitsystem
20170505:23:37:34:014693 gpinitsystem:localhost:donghua-[INFO]:-has been configured to allow all hosts within this new
20170505:23:37:34:014693 gpinitsystem:localhost:donghua-[INFO]:-array to intercommunicate. Any hosts external to this
20170505:23:37:34:014693 gpinitsystem:localhost:donghua-[INFO]:-new array must be explicitly added to this file
20170505:23:37:34:014693 gpinitsystem:localhost:donghua-[INFO]:-Refer to the Greenplum Admin support guide which is
20170505:23:37:34:014693 gpinitsystem:localhost:donghua-[INFO]:-located in the /usr/local/gpdb/docs directory
20170505:23:37:35:014693 gpinitsystem:localhost:donghua-[INFO]:-------------------------------------------------------
========================================
gpinitsystem returned: 1
========================================

======================================================================
                           OPTIMIZER STATE
----------------------------------------------------------------------
  Optimizer state .. :  off
======================================================================

                                                                                   version
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL  (Greenplum Database 5.0.0-alpha build dev) on x86_64-pc-linux-gnu, compiled by GCC gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11) compiled on May  5 2017 23:21:02
(1 row)

 optimizer
-----------
 off
(1 row)

            gp_opt_version
---------------------------------------
 Server has been compiled without ORCA
(1 row)

======================================================================


[donghua@localhost gpdemo]$ ps -ef|grep gp
donghua    960     1  0 23:37 ?        00:00:00 /usr/local/gpdb/bin/postgres -D /home/donghua/gpdb-5.0.0-alpha.2/gpAux/gpdemo/datadirs/dbfast_mirror2/demoDataDir1 -p 25436 --gp_dbid=6 --gp_num_contents_in_cluster=3 --silent-mode=true -i -M quiescent --gp_contentid=1
donghua    966     1  0 23:37 ?        00:00:00 /usr/local/gpdb/bin/postgres -D /home/donghua/gpdb-5.0.0-alpha.2/gpAux/gpdemo/datadirs/dbfast_mirror3/demoDataDir2 -p 25437 --gp_dbid=7 --gp_num_contents_in_cluster=3 --silent-mode=true -i -M quiescent --gp_contentid=2
donghua    974     1  0 23:37 ?        00:00:00 /usr/local/gpdb/bin/postgres -D /home/donghua/gpdb-5.0.0-alpha.2/gpAux/gpdemo/datadirs/dbfast1/demoDataDir0 -p 25432 --gp_dbid=2 --gp_num_contents_in_cluster=3 --silent-mode=true -i -M quiescent --gp_contentid=0
donghua    977     1  0 23:37 ?        00:00:00 /usr/local/gpdb/bin/postgres -D /home/donghua/gpdb-5.0.0-alpha.2/gpAux/gpdemo/datadirs/dbfast_mirror1/demoDataDir0 -p 25435 --gp_dbid=5 --gp_num_contents_in_cluster=3 --silent-mode=true -i -M quiescent --gp_contentid=0
donghua    978     1  0 23:37 ?        00:00:00 /usr/local/gpdb/bin/postgres -D /home/donghua/gpdb-5.0.0-alpha.2/gpAux/gpdemo/datadirs/dbfast3/demoDataDir2 -p 25434 --gp_dbid=4 --gp_num_contents_in_cluster=3 --silent-mode=true -i -M quiescent --gp_contentid=2
donghua    980     1  0 23:37 ?        00:00:00 /usr/local/gpdb/bin/postgres -D /home/donghua/gpdb-5.0.0-alpha.2/gpAux/gpdemo/datadirs/dbfast2/demoDataDir1 -p 25433 --gp_dbid=3 --gp_num_contents_in_cluster=3 --silent-mode=true -i -M quiescent --gp_contentid=1
donghua   1069     1  0 23:37 ?        00:00:00 /usr/local/gpdb/bin/postgres -D /home/donghua/gpdb-5.0.0-alpha.2/gpAux/gpdemo/datadirs/qddir/demoDataDir-1 -p 15432 --gp_dbid=1 --gp_num_contents_in_cluster=3 --silent-mode=true -i -M master --gp_contentid=-1 -x 0 -E
donghua   2129  9929  0 23:38 pts/0    00:00:00 grep --color=auto gp
root      4703     1  0 21:38 ?        00:00:00 /usr/bin/gnome-keyring-daemon --start --components=gpg
root      4844     1  0 21:38 ?        00:00:00 /usr/libexec/gvfs-gphoto2-volume-monitor


[donghua@localhost gpdemo]$ psql -p 15432 -d template1 -c "select version()"
                                                                                   version
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL  (Greenplum Database 5.0.0-alpha build dev) on x86_64-pc-linux-gnu, compiled by GCC gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11) compiled on May  5 2017 23:21:02
(1 row)


[donghua@localhost gpdemo]$ gpstate -d /home/donghua/gpdb-5.0.0-alpha.2/gpAux/gpdemo/datadirs/qddir/demoDataDir-1 -s
20170505:23:48:22:002544 gpstate:localhost:donghua-[INFO]:-Starting gpstate with args: -d /home/donghua/gpdb-5.0.0-alpha.2/gpAux/gpdemo/datadirs/qddir/demoDataDir-1 -s
20170505:23:48:22:002544 gpstate:localhost:donghua-[INFO]:-local Greenplum Version: 'postgres (Greenplum Database) 5.0.0-alpha build dev'
20170505:23:48:22:002544 gpstate:localhost:donghua-[INFO]:-master Greenplum Version: 'PostgreSQL  (Greenplum Database 5.0.0-alpha build dev) on x86_64-pc-linux-gnu, compiled by GCC gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11) compiled on May  5 2017 23:21:02'
20170505:23:48:22:002544 gpstate:localhost:donghua-[INFO]:-Obtaining Segment details from master...
20170505:23:48:22:002544 gpstate:localhost:donghua-[INFO]:-Gathering data from segments...
.
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-----------------------------------------------------
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:--Master Configuration & Status
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-----------------------------------------------------
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-   Master host                    = localhost.localdomain
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-   Master postgres process ID     = 1069
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-   Master data directory          = /home/donghua/gpdb-5.0.0-alpha.2/gpAux/gpdemo/datadirs/qddir/demoDataDir-1
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-   Master port                    = 15432
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-   Master current role            = dispatch
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-   Greenplum initsystem version   = 5.0.0-alpha build dev
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-   Greenplum current version      = PostgreSQL  (Greenplum Database 5.0.0-alpha build dev) on x86_64-pc-linux-gnu, compiled by GCC gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11) compiled on May  5 2017 23:21:02
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-   Postgres version               = 8.3.23
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-   Master standby                 = No master standby configured
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-----------------------------------------------------
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-Segment Instance Status Report
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-----------------------------------------------------
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-   Segment Info
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Hostname                          = localhost.localdomain
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Address                           = localhost.localdomain
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Datadir                           = /home/donghua/gpdb-5.0.0-alpha.2/gpAux/gpdemo/datadirs/dbfast1/demoDataDir0
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Port                              = 25432
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-   Mirroring Info
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Current role                      = Primary
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Preferred role                    = Primary
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Mirror status                     = Synchronized
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-   Status
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      PID                               = 974
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Configuration reports status as   = Up
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Database status                   = Up
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-----------------------------------------------------
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-   Segment Info
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Hostname                          = localhost.localdomain
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Address                           = localhost.localdomain
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Datadir                           = /home/donghua/gpdb-5.0.0-alpha.2/gpAux/gpdemo/datadirs/dbfast_mirror1/demoDataDir0
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Port                              = 25435
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-   Mirroring Info
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Current role                      = Mirror
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Preferred role                    = Mirror
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Mirror status                     = Synchronized
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-   Status
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      PID                               = 977
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Configuration reports status as   = Up
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Segment status                    = Up
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-----------------------------------------------------
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-   Segment Info
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Hostname                          = localhost.localdomain
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Address                           = localhost.localdomain
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Datadir                           = /home/donghua/gpdb-5.0.0-alpha.2/gpAux/gpdemo/datadirs/dbfast2/demoDataDir1
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Port                              = 25433
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-   Mirroring Info
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Current role                      = Primary
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Preferred role                    = Primary
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Mirror status                     = Synchronized
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-   Status
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      PID                               = 980
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Configuration reports status as   = Up
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Database status                   = Up
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-----------------------------------------------------
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-   Segment Info
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Hostname                          = localhost.localdomain
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Address                           = localhost.localdomain
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Datadir                           = /home/donghua/gpdb-5.0.0-alpha.2/gpAux/gpdemo/datadirs/dbfast_mirror2/demoDataDir1
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Port                              = 25436
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-   Mirroring Info
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Current role                      = Mirror
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Preferred role                    = Mirror
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Mirror status                     = Synchronized
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-   Status
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      PID                               = 960
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Configuration reports status as   = Up
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Segment status                    = Up
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-----------------------------------------------------
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-   Segment Info
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Hostname                          = localhost.localdomain
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Address                           = localhost.localdomain
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Datadir                           = /home/donghua/gpdb-5.0.0-alpha.2/gpAux/gpdemo/datadirs/dbfast3/demoDataDir2
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Port                              = 25434
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-   Mirroring Info
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Current role                      = Primary
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Preferred role                    = Primary
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Mirror status                     = Synchronized
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-   Status
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      PID                               = 978
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Configuration reports status as   = Up
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Database status                   = Up
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-----------------------------------------------------
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-   Segment Info
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Hostname                          = localhost.localdomain
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Address                           = localhost.localdomain
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Datadir                           = /home/donghua/gpdb-5.0.0-alpha.2/gpAux/gpdemo/datadirs/dbfast_mirror3/demoDataDir2
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Port                              = 25437
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-   Mirroring Info
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Current role                      = Mirror
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Preferred role                    = Mirror
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Mirror status                     = Synchronized
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-   Status
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      PID                               = 966
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Configuration reports status as   = Up
20170505:23:48:23:002544 gpstate:localhost:donghua-[INFO]:-      Segment status                    = Up


[donghua@localhost gpdemo]$ gpstop -d /home/donghua/gpdb-5.0.0-alpha.2/gpAux/gpdemo/datadirs/qddir/demoDataDir-1
20170505:23:51:07:002766 gpstop:localhost:donghua-[INFO]:-Starting gpstop with args: -d /home/donghua/gpdb-5.0.0-alpha.2/gpAux/gpdemo/datadirs/qddir/demoDataDir-1
20170505:23:51:07:002766 gpstop:localhost:donghua-[INFO]:-Gathering information and validating the environment...
20170505:23:51:07:002766 gpstop:localhost:donghua-[INFO]:-Obtaining Greenplum Master catalog information
20170505:23:51:07:002766 gpstop:localhost:donghua-[INFO]:-Obtaining Segment details from master...
20170505:23:51:07:002766 gpstop:localhost:donghua-[INFO]:-Greenplum Version: 'postgres (Greenplum Database) 5.0.0-alpha build dev'
20170505:23:51:07:002766 gpstop:localhost:donghua-[INFO]:---------------------------------------------
20170505:23:51:07:002766 gpstop:localhost:donghua-[INFO]:-Master instance parameters
20170505:23:51:07:002766 gpstop:localhost:donghua-[INFO]:---------------------------------------------
20170505:23:51:07:002766 gpstop:localhost:donghua-[INFO]:-   Master Greenplum instance process active PID   = 1069
20170505:23:51:07:002766 gpstop:localhost:donghua-[INFO]:-   Database                                       = template1
20170505:23:51:07:002766 gpstop:localhost:donghua-[INFO]:-   Master port                                    = 15432
20170505:23:51:07:002766 gpstop:localhost:donghua-[INFO]:-   Master directory                               = /home/donghua/gpdb-5.0.0-alpha.2/gpAux/gpdemo/datadirs/qddir/demoDataDir-1
20170505:23:51:07:002766 gpstop:localhost:donghua-[INFO]:-   Shutdown mode                                  = smart
20170505:23:51:07:002766 gpstop:localhost:donghua-[INFO]:-   Timeout                                        = 120
20170505:23:51:07:002766 gpstop:localhost:donghua-[INFO]:-   Shutdown Master standby host                   = Off
20170505:23:51:07:002766 gpstop:localhost:donghua-[INFO]:---------------------------------------------
20170505:23:51:07:002766 gpstop:localhost:donghua-[INFO]:-Segment instances that will be shutdown:
20170505:23:51:07:002766 gpstop:localhost:donghua-[INFO]:---------------------------------------------
20170505:23:51:07:002766 gpstop:localhost:donghua-[INFO]:-   Host                    Datadir                                                                              Port    Status
20170505:23:51:07:002766 gpstop:localhost:donghua-[INFO]:-   localhost.localdomain   /home/donghua/gpdb-5.0.0-alpha.2/gpAux/gpdemo/datadirs/dbfast1/demoDataDir0          25432   u
20170505:23:51:07:002766 gpstop:localhost:donghua-[INFO]:-   localhost.localdomain   /home/donghua/gpdb-5.0.0-alpha.2/gpAux/gpdemo/datadirs/dbfast_mirror1/demoDataDir0   25435   u
20170505:23:51:07:002766 gpstop:localhost:donghua-[INFO]:-   localhost.localdomain   /home/donghua/gpdb-5.0.0-alpha.2/gpAux/gpdemo/datadirs/dbfast2/demoDataDir1          25433   u
20170505:23:51:07:002766 gpstop:localhost:donghua-[INFO]:-   localhost.localdomain   /home/donghua/gpdb-5.0.0-alpha.2/gpAux/gpdemo/datadirs/dbfast_mirror2/demoDataDir1   25436   u
20170505:23:51:07:002766 gpstop:localhost:donghua-[INFO]:-   localhost.localdomain   /home/donghua/gpdb-5.0.0-alpha.2/gpAux/gpdemo/datadirs/dbfast3/demoDataDir2          25434   u
20170505:23:51:07:002766 gpstop:localhost:donghua-[INFO]:-   localhost.localdomain   /home/donghua/gpdb-5.0.0-alpha.2/gpAux/gpdemo/datadirs/dbfast_mirror3/demoDataDir2   25437   u

Continue with Greenplum instance shutdown Yy|Nn (default=N):
> y
20170505:23:51:13:002766 gpstop:localhost:donghua-[INFO]:-There are 0 connections to the database
20170505:23:51:13:002766 gpstop:localhost:donghua-[INFO]:-Commencing Master instance shutdown with mode='smart'
20170505:23:51:13:002766 gpstop:localhost:donghua-[INFO]:-Master host=localhost.localdomain
20170505:23:51:13:002766 gpstop:localhost:donghua-[INFO]:-Commencing Master instance shutdown with mode=smart
20170505:23:51:13:002766 gpstop:localhost:donghua-[INFO]:-Master segment instance directory=/home/donghua/gpdb-5.0.0-alpha.2/gpAux/gpdemo/datadirs/qddir/demoDataDir-1
20170505:23:51:14:002766 gpstop:localhost:donghua-[INFO]:-Attempting forceful termination of any leftover master process
20170505:23:51:14:002766 gpstop:localhost:donghua-[INFO]:-Terminating processes for segment /home/donghua/gpdb-5.0.0-alpha.2/gpAux/gpdemo/datadirs/qddir/demoDataDir-1
20170505:23:51:14:002766 gpstop:localhost:donghua-[INFO]:-No standby master host configured
20170505:23:51:14:002766 gpstop:localhost:donghua-[INFO]:-Commencing parallel primary segment instance shutdown, please wait...
20170505:23:51:14:002766 gpstop:localhost:donghua-[INFO]:-0.00% of jobs completed
20170505:23:51:24:002766 gpstop:localhost:donghua-[INFO]:-100.00% of jobs completed
20170505:23:51:24:002766 gpstop:localhost:donghua-[INFO]:-Commencing parallel mirror segment instance shutdown, please wait...
20170505:23:51:24:002766 gpstop:localhost:donghua-[INFO]:-0.00% of jobs completed
20170505:23:51:34:002766 gpstop:localhost:donghua-[INFO]:-100.00% of jobs completed
20170505:23:51:34:002766 gpstop:localhost:donghua-[INFO]:-----------------------------------------------------
20170505:23:51:34:002766 gpstop:localhost:donghua-[INFO]:-   Segments stopped successfully      = 6
20170505:23:51:34:002766 gpstop:localhost:donghua-[INFO]:-   Segments with errors during stop   = 0
20170505:23:51:34:002766 gpstop:localhost:donghua-[INFO]:-----------------------------------------------------
20170505:23:51:34:002766 gpstop:localhost:donghua-[INFO]:-Successfully shutdown 6 of 6 segment instances
20170505:23:51:34:002766 gpstop:localhost:donghua-[INFO]:-Database successfully shutdown with no errors reported
20170505:23:51:34:002766 gpstop:localhost:donghua-[INFO]:-Cleaning up leftover gpmmon process
20170505:23:51:34:002766 gpstop:localhost:donghua-[INFO]:-No leftover gpmmon process found
20170505:23:51:34:002766 gpstop:localhost:donghua-[INFO]:-Cleaning up leftover gpsmon processes
20170505:23:51:34:002766 gpstop:localhost:donghua-[INFO]:-No leftover gpsmon processes on some hosts. not attempting forceful termination on these hosts
20170505:23:51:34:002766 gpstop:localhost:donghua-[INFO]:-Cleaning up leftover shared memory


Thursday, December 29, 2016

Access HBase from BDA Lite using Oracle BigData SQL

SQL> select * from hbase_datademo where rowkey=1000001451580500;
select * from hbase_datademo where rowkey=1000001451580500
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-11504: error from external driver: java.lang.NoClassDefFoundError: org/apache/hadoop/hbase/util/Bytes


[oracle@bigdatalite bigdatasql_config]$ less /tmp/bigdata-log4j.log
16/12/29 11:25:41 INFO xcat.XCat: cluster property com.oracle.bigdata.conf.default.serdepropcacheenabled is true
16/12/29 11:25:41 DEBUG xcat.XCat: access param com.oracle.bigdata.serdepropcache is true
16/12/29 11:25:41 DEBUG hive.XCatHive: XCatHive.open: connecting to the hive metastore...
16/12/29 11:25:41 DEBUG hive.XCatHive: XCatHive.open: connect succeeded
16/12/29 11:25:41 DEBUG hive.XCatHive: XCatHive.open: getTable succeeded
16/12/29 11:25:41 ERROR sql.JXADDescribe: JXADDescribe.getInfo
java.lang.NoClassDefFoundError: org/apache/hadoop/hbase/util/Bytes
        at org.apache.hadoop.hive.hbase.HBaseSerDe.parseColumnsMapping(HBaseSerDe.java:184)
        at org.apache.hadoop.hive.hbase.HBaseSerDeParameters.(HBaseSerDeParameters.java:73)
        at org.apache.hadoop.hive.hbase.HBaseSerDe.initialize(HBaseSerDe.java:117)
        at org.apache.hadoop.hive.serde2.AbstractSerDe.initialize(AbstractSerDe.java:53)
        at org.apache.hadoop.hive.serde2.SerDeUtils.initializeSerDe(SerDeUtils.java:520)
        at org.apache.hadoop.hive.metastore.MetaStoreUtils.getDeserializer(MetaStoreUtils.java:392)
        at org.apache.hadoop.hive.ql.metadata.Table.getDeserializerFromMetaStore(Table.java:273)
        at org.apache.hadoop.hive.ql.metadata.Table.getDeserializer(Table.java:255)
        at org.apache.hadoop.hive.ql.metadata.Table.getCols(Table.java:602)
        at oracle.hadoop.sql.xcat.hive.XCatHiveUtil.extractSchema(XCatHiveUtil.java:205)
        at oracle.hadoop.sql.xcat.hive.XCatHiveUtil.valueOf(XCatHiveUtil.java:276)
        at oracle.hadoop.sql.xcat.hive.XCatHive.open(XCatHive.java:209)
        at oracle.hadoop.sql.JXADDescribe._compile(JXADDescribe.java:491)
        at oracle.hadoop.sql.JXADDescribe.getXaddoc(JXADDescribe.java:621)
        at oracle.hadoop.sql.JXADDescribe._getInfo(JXADDescribe.java:429)
        at oracle.hadoop.sql.JXADDescribe.getInfo(JXADDescribe.java:380)
16/12/29 11:25:41 DEBUG metrics.TaskMetrics: jxad_task: DESCRIBE__GET_XADDOC_REQTYPE, time: 64, bytes: 0
16/12/29 11:25:41 TRACE sql.JXADProvider: Entering kubsagtiJNIConvCB...
16/12/29 11:25:41 TRACE sql.JXADProvider: Leaving kubsagtiJNIConvCB...retSize=0
16/12/29 11:25:41 TRACE sql.JXADProvider: java.lang.NoClassDefFoundError: org/apache/hadoop/hbase/util/Bytes
16/12/29 11:25:41 ERROR sql.JXADProvider: KUP-11504: error from external driver: java.lang.NoClassDefFoundError: org/apache/hadoop/hbase/util/Bytes
16/12/29 11:25:41 TRACE sql.JXADProvider: Leaving kubsjniDescribeGetInfo...status=6
16/12/29 11:25:41 TRACE sql.JXADProvider: Leaving kubsagtiDescribeGetInfo...status=33
16/12/29 11:25:41 TRACE sql.JXADProvider: Leaving kubsagtDescribeGetInfo...status=33
16/12/29 11:25:41 DEBUG sql.JXADProvider: using agent handle=79
16/12/29 11:25:41 DEBUG sql.JXADProvider: using agent context=0xedfe98
16/12/29 11:25:41 DEBUG sql.JXADProvider: using agent jvm pointer=0x7fe020bf1b48
16/12/29 11:25:41 DEBUG sql.JXADProvider: using agent jni handle=0x10009f8

SQL> select * from hbase_datademo where rownum<10 p="">select * from hbase_datademo where rownum<10 p="">              *
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-11504: error from external driver:
oracle.hadoop.sql.xcat.common.XCatException : 1001 : Error getting splits
document. Cause : java.io.IOException:
java.lang.reflect.InvocationTargetException

16/12/29 13:40:15 DEBUG hadoop.XCatInfm: listStatus(final) file =hdfs://bigdatalite.localdomain:8020/user/hive/warehouse/hbase_datademo
16/12/29 13:40:15 ERROR sql.JXADDescribe: JXADDescribe.getInfo
oracle.hadoop.sql.xcat.common.XCatException : 1001 : Error getting splits document. Cause : java.io.IOException: java.lang.reflect.InvocationTargetException
        at oracle.hadoop.sql.xcat.XCatInfo.getSplitsDoc(XCatInfo.java:395)
        at oracle.hadoop.sql.JXADDescribe.getSplitsDoc(JXADDescribe.java:545)
        at oracle.hadoop.sql.JXADDescribe._getInfo(JXADDescribe.java:417)
        at oracle.hadoop.sql.JXADDescribe.getInfo(JXADDescribe.java:380)
Caused by: java.io.IOException: java.lang.reflect.InvocationTargetException
        at org.apache.hadoop.hbase.client.ConnectionFactory.createConnection(ConnectionFactory.java:240)
        at org.apache.hadoop.hbase.client.ConnectionFactory.createConnection(ConnectionFactory.java:218)
        at org.apache.hadoop.hbase.client.ConnectionFactory.createConnection(ConnectionFactory.java:119)
        at org.apache.hadoop.hive.hbase.HiveHBaseTableInputFormat.getSplitsInternal(HiveHBaseTableInputFormat.java:463)
        at org.apache.hadoop.hive.hbase.HiveHBaseTableInputFormat.getSplits(HiveHBaseTableInputFormat.java:450)
        at oracle.hadoop.sql.xcat.hadoop.mapred.XCatMapredInfm.computeBaseSplits(XCatMapredInfm.java:169)
        at oracle.hadoop.sql.xcat.hadoop.XCatGetSplits.computeSplits(XCatGetSplits.java:563)
        at oracle.hadoop.sql.xcat.XCatInfo.getSplits(XCatInfo.java:354)
        at oracle.hadoop.sql.xcat.XCatInfo.getSplitsDoc(XCatInfo.java:384)
        ... 3 more
Caused by: java.lang.reflect.InvocationTargetException
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
        at org.apache.hadoop.hbase.client.ConnectionFactory.createConnection(ConnectionFactory.java:238)
        ... 11 more
Caused by: java.lang.NoClassDefFoundError: org/apache/htrace/Trace
        at org.apache.hadoop.hbase.zookeeper.RecoverableZooKeeper.exists(RecoverableZooKeeper.java:217)
        at org.apache.hadoop.hbase.zookeeper.ZKUtil.checkExists(ZKUtil.java:419)
        at org.apache.hadoop.hbase.zookeeper.ZKClusterId.readClusterIdZNode(ZKClusterId.java:65)
        at org.apache.hadoop.hbase.client.ZooKeeperRegistry.getClusterId(ZooKeeperRegistry.java:105)
        at org.apache.hadoop.hbase.client.ConnectionManager$HConnectionImplementation.retrieveClusterId(ConnectionManager.java:919)
        at org.apache.hadoop.hbase.client.ConnectionManager$HConnectionImplementation.(ConnectionManager.java:657)
        ... 16 more
16/12/29 13:40:15 DEBUG metrics.TaskMetrics: jxad_task: DESCRIBE__GET_SPLITS_DOC_REQTYPE, time: 59, bytes: 0
16/12/29 13:40:15 TRACE sql.JXADProvider: Entering kubsagtiJNIConvCB...

How to Fix: Adding Hbase and Hbase Lib to java class path for hadoop inside bigdata.properties file

[oracle@bigdatalite tmp]$ diff /u01/bigdatasql_config/bigdata.properties /u01/bigdatasql_config/bigdata.properties.orig
3c3
< java.classpath.hadoop=/usr/lib/hadoop/client/*:/usr/lib/hadoop-mapreduce/*:/usr/lib/hadoop-mapreduce/lib/*:/usr/lib/hbase/*:/usr/lib/hbase/lib/*
---
> java.classpath.hadoop=/usr/lib/hadoop/client/*:/usr/lib/hadoop-mapreduce/*:/usr/lib/hadoop-mapreduce/lib/*

--- restart BDA Lite

Thursday, November 24, 2016

MongoDB - Change audit filters to audit specific user and DML auditing



# Setup directory for replication set auditing testing (audit for specific user using filter)
# hostname: database.dbaglobe.com
mkdir -p /home/donghua/LAB7/{r0,r1,r2}

# Prepare configure file: /home/donghua/LAB7/r0/mongod.conf
net:
   port: 22700
storage:
   dbPath: /home/donghua/LAB7/r0
systemLog:
   destination: file
   path: /home/donghua/LAB7/r0/mongo.log
   logAppend: true
processManagement:
   fork: true
replication:
   replSetName: AuditTest
#security:
#   authorization: enabled
auditLog:
   destination: file
   format: JSON
   path: /home/donghua/LAB7/r0/auditLog.json
   filter: '{ users: { user: "superuser", db: "admin" } }'
# enable DML auditing by audit atype:authCheck
setParameter: {auditAuthorizationSuccess: true}

# Prepare configure file: /home/donghua/LAB7/r1/mongod.conf
net:
   port: 22701
storage:
   dbPath: /home/donghua/LAB7/r1
systemLog:
   destination: file
   path: /home/donghua/LAB7/r1/mongo.log
   logAppend: true
processManagement:
   fork: true
replication:
   replSetName: AuditTest
#security:
#   authorization: enabled  
auditLog:
   destination: file
   format: JSON
   path: /home/donghua/LAB7/r1/auditLog.json
   filter: '{ users: { user: "superuser", db: "admin" } }'
# enable DML auditing by audit atype:authCheck
setParameter: {auditAuthorizationSuccess: true}

# Prepare configure file: /home/donghua/LAB7/r2/mongod.conf
net:
   port: 22702
storage:
   dbPath: /home/donghua/LAB7/r2
systemLog:
   destination: file
   path: /home/donghua/LAB7/r2/mongo.log
   logAppend: true
processManagement:
   fork: true
replication:
   replSetName: AuditTest
#security:
#   authorization: enabled  
auditLog:
   destination: file
   format: JSON
   path: /home/donghua/LAB7/r2/auditLog.json
   filter: '{ users: { user: "superuser", db: "admin" } }'
# enable DML auditing by audit atype:authCheck
setParameter: {auditAuthorizationSuccess: true}
  
# Setup replset with 3 replicas with auditing enable to JSON File
mongod --config /home/donghua/LAB7/r0/mongod.conf
mongod --config /home/donghua/LAB7/r1/mongod.conf
mongod --config /home/donghua/LAB7/r2/mongod.conf

mongo --port 22700 --eval "rs.initiate({_id: 'AuditTest',members: [{ _id: 1, host: 'database.dbaglobe.com:22700' },{ _id: 2, host: 'database.dbaglobe.com:22701' },{ _id: 3, host: 'database.dbaglobe.com:22702' }]})"
mongo --port 22700 --eval "rs.status()"

# Create application specific user
MongoDB Enterprise AuditTest:PRIMARY> use admin
MongoDB Enterprise AuditTest:PRIMARY> db.createUser( {user: "superuser", pwd: "secret", roles:[{role:'root',db:'admin'}]});

# Shutdown and enable security.authorization
# And restart Mongodb

# Shutdown and clean up
mongod --dbpath /home/donghua/LAB7/r0 --shutdown
mongod --dbpath /home/donghua/LAB7/r1 --shutdown
mongod --dbpath /home/donghua/LAB7/r2 --shutdown
rm -rf /home/donghua/LAB7