Category: DATABASE

EDIT THE DATA IN HIVE TABLES

admin

In Hive, We know that it works on file reading mechanism where hive reads data present in files present in hadoop file system. Here , pre-requisite is you should have basic knowledge of Hive. STEP-1 Copy the Hadoop files of a particular Partition for that particular Hive object to your local server using get command. For example hdfs dfs –get /hadoop-server-details/path-of-the-file/partition-column-name=value  /home/user1/ Here Assumption is file format of files that are mapped to hive object is not normal text file (say avro file) . So, it is recommended to copy…

BEELINE COMMAND LINE IN HIVE

admin

Today, I will discuss about the beeline command line which we use to call the SQL Query through Linux. But what if same thing needs to be called through Shell Scripting. First of all, we need to call Sql Query through Beeline command line inside shell Scripting using below command. beeline -u “jdbc:hive2://localhost:10000/default;principal=hive/localhost“ -n “username” -p “password” –hivevar var1=$col1_hive –hivevar var2=$schema_name –hivevar var3=$table_name –hivevar var4=$col1_value -f sql_script.sql > text.log Here $col1_hive is the column name of a table. $table_name is the table name. $schema_name is the Schema Name where that…

Check SQL History and Monitor Sessions in PL/SQL Developer Tool

admin

Today, i will discuss about how to monitor Sessions and check what all queries executed in pl/sql Sessions. First, In order to Monitor Sessions which are active /inactive in particular Schema in pl/sql developer tool, Go to Tools->Monitor Sessions. See the below Screen Shot for the same. Second, In order to see what all queries executed in particular Session , go to View -> SQL History. See the below image for the same.

GET COLUMN NAMES ON WHICH INDEX IS CREATED IN ORACLE

admin

Today, i will share the query which will give the column names along with table name on which index is created.Below is the Query for the same. select a.index_name,a.table_name,b.column_name from all_indexes a, all_ind_columns b where a.table_name=b.table_name and a.index_name=b.index_name and a.uniqueness=’UNIQUE’ and a.table_name in <LLIST OF TABLE NAMES> Query to give column names which are not null in the table. select table_name,column_name from all_tab_columns where table_name like ‘%ABC%’ and NULLABLE=’N’ and owner=<SCHEMA_NAME> order by table_name

CHECK WHETHER VALUES ARE UPPERCASE OR NOT IN DATABASE

admin

Today, i will discuss about how to check which values in the table for a particular column are uppercase or not. I have designed a Query using dual to achieve the same. Below is the Query. select * from (select ‘A’ as col1, ‘B’ as col2 from dual union select ‘a’ col1,’D’ as col2 from dual) A, (select ‘A’ as col1 ,’B’ as col2 from dual union select ‘a’ col1 ,’D’ as col2 from dual)B where upper(B.col1)=A.col1 and B.col2=A.col2; I created table A and table B using dual command .…