Hortonworks:Service 'userhome' check failed: File does not exist: /user/admin

Problem: Service 'userhome' check failed: File does not exist: /user/admin

Solution:

sudo -u hdfs hadoop fs -mkdir /user/admin

sudo -u hdfs hdfs dfs -chown -R admin:hdfs /user/admin

similar kind of issues :

service 'ats' check failed: server error

could not write file /user/admin/hive/jobs/hive-job

service 'userhome' check failed: authentication required

hadoop mkdir permission denied

failed to get cluster information associated with this view instance

java io filenotfoundexception file does not exist hdfs

Read More

Hive Order by Vs Sort by

Today we will discuss about how and where we can use Order by and Sort by clause in Hive

ORDER BY:

è Forces all the data to go into the same reducer node, by doing this, Order by ensure that entire dataset is totally ordered

è Uses a single reducer to guarantee total order in output

Drawbacks:

è Single reducer will take a long time to sort very large outputs

Sort By:

è Sort the rows based on the given columns per reducer. If there are more than one reducer, then the output per reducer will be sorted

Drawbacks:

If we have more than one reducer, then order of total output is not guaranteed to be sorted.

Let’s take one simple example. Currently Dept. table has following data

First will try to run the Order by query by setting reducer count as 2

If you see above screenshot all the data got sorted based on deptno column in Ascending order.

Now will try to run Sort by command.

We can clearly see that individual reducer level results are sorted but not at complete data set level.

However, sometimes we do not require total ordering. For example, suppose you have a table called user_action_table where each row has user_id, action, and time. Your goal is to order them by time per user_id and in this situation, we can use Sort By clause

Read More

How to retrive/get all the numbers from a string using SQL server

Script for getting all the numbers from string

DECLARE @string1 varchar(100)='welcome2sql1239hello23';

DECLARE @string2 varchar(100)='',@i int=0;

WHILE @i<=LEN(@string1)

BEGIN

IF(SUBSTRING(@string1,@i,1)>='0' AND SUBSTRING(@string1,@i,1)<='9')

SET @string2=@string2+SUBSTRING(@string1,@i,1);

SET @i=@i+1;

END

SELECT @string2 AS [Numbers obtained];

Read More

Question: Find position of numbers in the given string

Script for finding the position of numbers in the given string

DECLARE @input VARCHAR(100)='welcome2Sql376hi975';

DECLARE @output VARCHAR(100)=PATINDEX('%[0-9]%',@input);

DECLARE @position VARCHAR(100)=@output;

WHILE @output<LEN(@input)

BEGIN

SET @input=STUFF(@input,PARSE(@output AS BIGINT),1,'*');

SET @output=PATINDEX('%[0-9]%',@input);

SET @position=@position+','+@output;

END

SELECT @position AS [Positions];

Read More

Getting Error while accessing Hive from command line interface

Some times we  see below error while launching Hive from command line.

Error:

Logging initialized using configuration in file:/etc/hive/2.5.0.0-1245/0/hive-log4j.properties

Exception in thread "main" java.lang.RuntimeException: org.apache.hadoop.security.AccessControlException: Permission denied: user=root, access=WRITE, inode="/user/root":hdfs:hdfs:drwxr-xr-x

 

Here issue will be you are trying to launch the Hive with non HDFS users and it might be Root account.

Below are the steps to solve the problem:

1. Create Root or the users which is using to launch the hive

sudo -u hdfs hdfs dfs -mkdir /user/<<root>>

2. Do the HDFS  ownership change from HDFS to the required user

sudo -u hdfs hdfs dfs -chown -R root:hdfs /user/root

Read More

TOP 100 SQL SERVER INTERVIEW QUESTIONS

SQL SERVER INTERVIEW QUESTIONS

1.      What is the Complex task that you handled in your project

2.      What are the differences between Delete and Truncate?

3.      Diff b/w Char and Varchar

4.      Diff b/w Unicode and Non-unicode characters

5.      Different ways to identify duplicate records in table

6.      How will you identify table granularity if we table doesn’t have any primary key/unique key

7.      Diff b/w Stored procedure and Function

8.      How parameter sniffing will happen? Give me one example

9.      Can we embed Dynamic SQL in Functions

10. Diff b/w CROSS Apply and Outer Apply and different from INNER JOIN and OUTER JOINS

11. Brief about Windows Functions which were introduced as part of SQL Server 2012

12. Diff b/w Coalesce() and ISNULL() and which will be best?

13. How many clustered and non clustered indexes can be created in one table?

14. What are the main differences between UNION ,  UNION ALL and EXCEPT

15. How will identify the root causes of slow running procedures/functions

16. How will you identify slow running queries in SQL Server

17. How will identify the matched and non matched records between two tables 

18. What are ACID properties

19. What are the isolation levels and explain each one with example

20. What is the diff b/w LOCK and NOLOCK

21. What is Table variable, Temp  table and CTE and in which scenarios what kind of table will you use

22. Explain the scenario where you have written normal subquery and Correlated Subquery

23. Diff b/w Where and Having Clause

24. Can we execute Stored Procedure inside Function?

25.  Can we you use Case Statement in Order by Clause?

26. Explain about INNER, LEFT, RIGHT, FULL OUTER joins

27. Explain the scenario where you have used Self join

28. What are deleted and inserted tables

29. Explain the scenario where MERGE statement will be use ful

30. How will you reseed the identity column

31. How will you check if column exists for a table or view in database

32. What is trigger and different types of triggers

33. What is view and indexed view/materialised view

34. What is the Query logical execution order

35. Difference between clustered and non clustered index

36. Difference between Stuff and Replace function

37. What is linked Server

38. Difference b/w Varchar and Varchar(max)

39. What are the system databases (master, msdb, model and tempdb) roles and functionalities

40. Difference b/w index seek and index scan

41. Difference b/w RaiseError and throw and which one will be the best to use

42. Pivot and Unpivot

43. Diff b/w IN and EXISTS clause

44. What is cursor and how many different types are there

45. What is exception handling

46. What are dirty reads, phantom reads

47. What is dead lock

48. Diff b/w Local and Global Temporary tables

49. What is synonym

50. What is Sequence? Diff b/w Sequence and Identity

Read More

SQL SERVER : ALTER, DELETE,DROP

SQL SERVER-ALTER, DELETE, DROP

We will use below table for demo purpose

CREATE TABLE Emp_Test

 (EmpID int,

 Empname varchar(10))

ALTER: ALTER command is used to do below things

•    Add column/constrains
•    Modify column
•    Drop column/Constraint

Add Column syntax:

ALTER TABLE <<Table>>

ADD Column_name datatype [size]

Example:

ALTER TABLE Emp_test

ADD Dateofbirth date

We can also add constraints to the existing table. We will discuss about constrains in future sessions. For now we will learn how to add constraints 

Add Constraint Syntax:

ALTER TABLE <<tablename>>

ADD Constraint  Constraint_name ConstraintType (Columns…)

Example:

ALTER TABLE Emp_test

ADD constraint EMP_Unique UNIQUE(EMPID) – Here Unique means EMPID column data should not be repeated

Modify column:

We can also modify the existing column data type and its size if table is empty and it table has data and then we are trying to modify the existing column data type then if the new data type is compatible to the existing data in the table then it will allow other it will through error. E.g. existing column was created using char(10) then if we are trying to modify to Varchar(20)  then it won’t throw any error. Compatibility data types are always we can change. 

Syntax:

ALTER TABLE <<Table_name>> 

ALTER Column Col_name datatype

Example:

ALTER TABLE Emp_test

ALTER Column Empname char(12)

Drop column/Constraint: We can  drop existing column/constraint using ALTER command 

Syntax: 

ALTER TABLE <<table_name>>

DROP <<Column or Constraint>> <<Column_name>>

Example:

Drop Empname column

ALTER TABLE Emp_test

DROP Column Empname  

Drop unique constraint (EMP_Unique)

ALTER TABLE Emp_test

DROP CONSTRAINT EMP_Unique

DELETE:  DELETE Command is used to delete table rows. We can delete all the rows from a table or we can also delete specific rows by using WHERE condition. 

Syntax:

DELETE FROM <<table name>> 

<<WHERE some conditions>>

Example:

DELETE FROM Emp_test--Delete all the rows

DELETE FROM Emp_test WHERE EmpID=10--Delete rows which are having empid=10

DELETE FROM Emp_test WHERE EmpID<10 --Delete rows which are having empid<10

DELETE FROM Emp_test WHERE EmpID<>10 ----Delete rows which are having empid<>10 

In SQL server we can also write DELETE statement like below

DELETE emp_test

TRUNCATE: TRUNCATE Command is also used to delete the data from table

Syntax:

TRUNCATE TABLE <<Tablename>>

Example:

TRUNCATE TABLE emp_test

Here we should get one question like 

What is the difference between DELETE and TRUNCATE?

 Both are deleting rows. Yes both are deleting table data however there are some differences are there while deleting the data.

      1.     For DELETE We can use WHERE condition whereas TRUNCATE We can’t give 

      2.     DELETE Command will maintain log for each deleted row whereas TRUNCATE will not maintain any log

      3.     We can use some joins to delete the data where as in TRUNCATE we can’t use any joins

      4.     DELETE Will not reset IDENTITY [We will discuss in future sessions about IDENTITY] Whereas TRUNCATE Will      reset IDENTITY

      5.     We can Roll back the data when we use DELETE whereas TRUNCATE we can’t rollback

Overall, if we want to delete all the row then it’s always go with TRUNCATE Command

DROP: DROP Command is used to drop the table

Syntax:

DROP TABLE <<tablename>>

Example:

DROP TABLE emp_test

Read More