How to access data files stored in AWS S3 buckets from HDP using HDFS / HIVE / PIG

Step by Step Instructions on how to access data files in AWS S3 Bucket from HDP (HDFS, HIVE and PIG)

Article

Step 1 : Log into AWS your credentials

Step 2 : From the AWS console go to the following options and create a user in for the demo in AWS

Security & Identity --> Identity and Access Management --> Users --> Create New Users

Step 3 : Make note of the credentials

awsAccessKeyId = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxx';

awsSecretAccessKey = 'yyyyyyyyyyyyyyyyyyyyyyyyyyy';

Step 4 : Add the User to the Admin Group by clicking the button “User Actions” and select the option Add Users to Group and add select your user (admin)

Step 5 : Assign the Administration Access Policy to the User (admin)

Step 6 : In the AWS Console , Go to S3 and create a bucket “s3hdptest” and pick your region

Step 7 : Upload the file manually by using the upload button. In our example we are uploading the file S3HDPTEST.csv

Step 8 : In the Hadoop Environment create the user with the same name as it is created in the S3 Environment

Step 9 : In Ambari do all the below properties in both hdfs-site.xml and hive-site.xml

1. <property>
2. <name>fs.s3a.access.key</name>
3. <description>AWS access key ID. Omit for Role-based authentication.</description>
4. </property>
5. <property>
6. <name>fs.s3a.secret.key</name>
7. <description>AWS secret key. Omit for Role-based authentication.</description>
8. </property>

Step 10 : Restart the Hadoop Services like HDFS , Hive and any depending services

Step 11 : Ensure the NTP is set to the properly to reflect the AWS timestamp, follow the steps in the below link

http://www.emind.co/how-to/how-to-fix-amazon-s3-requesttimetooskewed

Step 12 : Run the below statement from the command line to test whether we are able to view the file from S3

1. [root@sandbox ~]# su admin
2. bash-4.1$ hdfs dfs -ls s3a://s3hdptest/S3HDPTEST.csv
3. -rw-rw-rw- 1 188 2016-03-29 22:12 s3a://s3hdptest/S3HDPTEST.csv
4. bash-4.1$

Step 13: To verify the data you can use the below command

1. bash-4.1$ hdfs dfs -cat s3a://s3hdptest/S3HDPTEST.csv

Step 14 : Move a file from S3 to HDFS

1. bash-4.1$ hadoop fs -cp s3a://s3hdptest/S3HDPTEST.csv /user/admin/S3HDPTEST.csv

Step 15 : Move a file from HDFS to S3

1. bash-4.1$ hadoop fs -cp /user/admin/S3HDPTEST.csv s3a://s3hdptest/S3HDPTEST_1.csv

Step 15a : Verify whether the file has been stored in the AWS S3 Bucket

Step 16 : To access the data using Hive from S3:

Connect to Hive from Ambari using the Hive Views or Hive CLI

A) Create a table for the datafile in S3

1. hive> CREATE EXTERNAL TABLE mydata
2. (FirstName STRING, LastName STRING, StreetAddress STRING, City STRING, State STRING,ZipCode INT)
3. ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
4. LOCATION 's3a://s3hdptest/';

B) Select the file data from Hive

1. hive> SELECT * FROM mydata;

Step 17 : To Access the data using Pig from S3:

1. [root@sandbox ~]# pig -x tez
2.  
3. grunt> a = load 's3a://s3hdptest/S3HDPTEST.csv' using PigStorage();
4. grunt> dump a;

Step 18 : To Store the data using Pig to S3:

1. grunt> store a into 's3a://s3hdptest/OUTPUT' using PigStorage();

Checking the created data file in AWS S3 bucket

Note: For the article related to accessing AWS S3 Bucket using Spark please refer to the below link:

https://community.hortonworks.com/articles/25523/hdp-240-and-spark-160-connecting-to-aws-s3-buckets.html