How to fix ERROR 1045 : Access denied for user 'root'@'localhost' (using password: YES)

Please follow below steps to fix HDP Sandbox -Mysql root password issue:

Enter

mysql -u root

UPDATE mysql.user SET Password=PASSWORD('hadoop') WHERE User='root';

FLUSH PRIVILEGES;

Read More

How to Prepare and Clear Hortonworks Data Platform Certified Developer Exam (HDPCD -English)

 HORTONWORKS UNIVERSITY is offering HDPCD certificate and its exclusively hands-on, performance-based exam that require we to complete a set of tasks on actual hadoop cluster instead of just guessing multiple choice questions.

Basically HDPCD certificate categorised into 3 categories

1. Data ingestion

2. Data transformation

3. Data analysis

As mentioned in Hortonworks webiste, here I tried to keep all the tasks

Data Ingestion

Import data from a table in a relational database into HDFS

Import the results of a query from a relational database into HDFS

Import a table from a relational database into a new or existing Hive table

Insert or update data from HDFS into a table in a relational database

Given a Flume configuration file, start a Flume agent

Given a configured sink and source, configure a Flume memory channel with a specified capacity

Data Transformation

Write and execute a Pig script

Load data into a Pig relation without a schema

Load data into a Pig relation with a schema

Load data from a Hive table into a Pig relation

Use Pig to transform data into a specified format

Transform data to match a given Hive schema

Group the data of one or more Pig relations

Use Pig to remove records with null values from a relation

Store the data from a Pig relation into a folder in HDFS

Store the data from a Pig relation into a Hive table

Sort the output of a Pig relation

Remove the duplicate tuples of a Pig relation

Specify the number of reduce tasks for a Pig MapReduce job

Join two datasets using Pig

Perform a replicated join using Pig

Run a Pig job using Tez

Within a Pig script, register a JAR file of User Defined Functions

Within a Pig script, define an alias for a User Defined Function

Within a Pig script, invoke a User Defined Function

Data Analysis

Write and execute a Hive query

Define a Hive-managed table

Define a Hive external table

Define a partitioned Hive table

Define a bucketed Hive table

Define a Hive table from a select query

Define a Hive table that uses the ORCFile format

Create a new ORCFile table from the data in an existing non-ORCFile Hive table

Specify the storage format of a Hive table

Specify the delimiter of a Hive table

Load data into a Hive table from a local directory

Load data into a Hive table from an HDFS directory

Load data into a Hive table as the result of a query

Load a compressed data file into a Hive table

Update a row in a Hive table

Delete a row from a Hive table

Insert a new row into a Hive table

Join two Hive tables

Run a Hive query using Tez

Run a Hive query using vectorization

Output the execution plan for a Hive query

Use a subquery within a Hive query

Output data from a Hive query that is totally ordered across multiple reducers

Set a Hadoop or Hive configuration property from within a Hive query

Reference:

https://hortonworks.com/services/training/certification/exam-objectives/#hdpcd

Read More

How to use Password file with Sqoop

How to use local password-file parameter with sqoop

Problem: sometimes we have to read password from file for sqoop command.

Solution: Passing password parameter file to sqoop command is not a big deal. Just follow below steps:

1.    Create a password with Echo -n “<<password>> > <<passwordfilename>>

E.g.

Echo -n “Srinivas” > passwordfile – Here we can give file name with location

Sqoop Command:

sqoop list-tables  --connect "jdbc:mysql://sandbox.hortonworks.com:3306/hdpcdpractise" --username hadoop --password-file file:///usr/Srinivas/ passwordfile

Read More

SQOOP-SqlManager-Error reading from database-java.sql.SQLException-set com.mysql.jdbc.RowDataDynamic-3c2d5cfb

Problem:

sqoop export --connect "jdbc:mysql://sandbox.hortonworks.com:3306/hdpcdpractise" --username hadoop --password hadoop --table weather --export-dir /user/hortonworks/weather/  --fields-terminated-by ',';

Some times when we run sqoop commands like above we will get following error

“ERROR manager.SqlManager: Error reading from database: java.sql.SQLException: Streaming result set com.mysql.jdbc.RowDataDynamic@3c2d5cfb is still active. No statements may be issued when any streaming result sets are open and in use on a given connection. Ensure that you have called .close() on any active streaming result sets before attempting more queries.

java.sql.SQLException: Streaming result set com.mysql.jdbc.RowDataDynamic@3c2d5cfb is still active. No statements may be issued when any streaming result sets are open and in use on a given connection.”

Solution:

Simple solution would be adding driver parameter with value like below

--driver com.mysql.jdbc.Driver

sqoop export --connect "jdbc:mysql://sandbox.hortonworks.com:3306/hdpcdpractise" --username hadoop --password hadoop --table weather --export-dir /user/hortonworks/weather/  --fields-terminated-by ',' --driver com.mysql.jdbc.Driver

Read More

How to create a column in a table and that should throw error when we do SELECT * or SELECT col

Problem:

Create a column in a table and that should give an error if we give SELECT * or SELECT of that column

Solution:

Friends, don’t think toooooo much like how to solve this problem. Here is the simple solution that I got.

Just use any computed column and that should not have any syntactical errors but it should have logical error (E..g 1/0, 1+’srinivas’)

Sample Script:

create table #test

(id1 int,id2 int,Col1 as id1+'srinu')

select *  from #test

Here We will get

Conversion failed when converting the varchar value 'srinu' to data type int.

That’s all friends……!

Thanks for reading this article and please post a comment if you have any questions/thoughts

Read More

Greenplum System-Useful Queries

Useful Queries:

1. Query to verify the list of segments in a Greenplum system

select * from gp_segment_configuration;

Result:

Column Value	Description
Content = -1	Master Node
Content = 0 - n	Slave segments

2. Query to verify Skewing in a database

select * from gp_toolkit.gp_skew_coefficients;

3. Query to verify the number of records in each segment of a table

select gp_segment_id, count(*)

from zc.zctimesheet1

group by gp_segment_id

order by gp_segment_id;

4. Query to verify the size of a database/ relation (table)

select pg_size_pretty(pg_database_size('DistributionTest'));

select pg_size_pretty(pg_relation_size('zc.zctimesheet1'));

Read More

Greenplum Best Practises

Best Practices:

1. A distribution key should not have more than 2 columns, recommended is 1 column.
2. While modeling a database, we need to make sure all the tables that gets involved in longer query execution are distributed using the joining column.
3. Distribution keys should be domain key but not surrogate keys.
4. Explicitly define a column or random distribution for all tables. Do not use the default as it may cause skew on segments.
5. Do not distribute on columns that will be used in the WHERE clause of a query. You should partition the data on the column that is used in WHERE clause
6. The distribution key column data should contain unique values
7. Do not distribute on dates or timestamps. Distributing data on date or timestamp may cause poor system performance
8. There are 2 questions to ask while deciding the distribution key of a table:
1. Is the Distribution Key used in joins?
1. Makes sure, no redistribution is happening during query execution
2. Does the key 'Equi-distributes'  the data across all the segments?
1. Makes sure all the segments get approximately the same amount of data, hence the same amount of work during query execution.
2. We need to make sure data skewing is minimum for every table.

SELECT * FROM gp_toolkit.gp_skew_coefficients

Read More

Greenplum Architecture

Parallel Processing:

 Taking all the rows from table and spreading them among many parallel processing units. In greenplum these parallel  processing units are called segments.

SMP(symmetric multi processing system):

  A smp system has multiple processors but share a single OS,memory pool,and share access to disks.SMP is a single server which is referred as node.A single SMP system is called as a Segment Host ,multiple Segment Hosts are connected together to create a Massively Parallel Processing (MPP) system.the number of segments per each segment host is defined by number of CPU’s the segment Host contains.

Commodity Hardware:

  Greenplum provides incredible speeds with commodity hardware,this allows to create one segment per CPU. If you have two dual-core CPU processors in a server you should build four segments.

Roles of segment Host and Segment:

  The host is the boss and the segments are the workers.Users login to the host and never communicate directly with the segments. The host builds a plan for the segments to follow that is delivered in plan slices. Each slice instructs the segments what to do. When the segments have done their work they return it to the host.

  The Host is the brains behind the entire operation.

Segment Host Roles:

  1.When a user logs into Greenplum, the host will log them in and be responsible for their session.

  2.The host checks the SQL syntax, creates the EXPLAIN plan, checks the security, and builds a plan for the segments to follow.

  3.The host uses system statistics and statistics from the ANALYZE command to build the best plan.

  4.The host doesn't hold user data, but instead holds the Global System Catalog.

  5.The host always delivers the final answer set to the user.

Segment Roles:

  1.Segments are responsible for storing and retrieving rows from their assigned disk (Virtual disk).

  2.Segments lock the tables and rows.

  3.Segments sort rows and do all aggregation.

  4.Segments handle all the join processing.

  5.Segments handle all space management and space accounting.

Master Host will broadcast the plan to each segment simultaneously, but if you use the distribution key in the WHERE clause of your SQL with an equality statement, then only a single segment will be contacted to return the row.

For example: If the system contains 3 parallel processing units and if Employee_table contains 9 rows then each parallel processing unit holds 3 rows

      

select * from employee_table

              where empno=2000;

 If above query is written without using distribution key in where clause then for fetching the result all of the 3 segments are visited,but in above query empno is used in where clause with equality so only the segment which contain that employee number is visited.

Read More

How to find and delete the top 100 most used files in Linux/Unix

Steps to find and delete the top 100 most used files in Linux/Unix

Command to find most disk space used files:

find / -xdev -type f -size +100M -exec du -sh {} ';' | sort -rh | head -n100

Command to delete the content without removing the file

> <<filename>>

E.g.

> sample.txt

Here > is used to empty the file

Thanks for reading this article and please do subscribe to get more updates 

Read More

How to Install MySQL on CentOS 7

Steps to Install MySQL on Linux-CentOS 7

Step 1: Download MYSQL repositories

wget http://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm

Step 2: Prepare repository to install MySQL

sudo rpm -Uvh mysql57-community-release-el7-9.noarch.rpm

Step 3: Install MySQL on your server

sudo yum install mysql-server

During the installation, you will be asked to provide Y or N. Just type Y.  If you see Complete! message, then it means that Install completed successfully.

Step 4: Start the MySQL service

sudo systemctl start mysqld

Step 5: Check the service status

sudo systemctl status mysqld

Thanks for reading the article. If you feel it solves your problem, please do like,comment and share this !!!!!!!!!

Read More

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