Hive on S3 issue . Not able to create table on s3

Hi I am trying to create hive table on s3 bucket , I am facing the following issue .
Can any one guide me if i am missing any configuration to fix the issue ?

hive> CREATE EXTERNAL TABLE mydata (key STRING, value INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘=’ LOCATION ‘s3n://hivemeta/’;

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:Got exception: java.io.IOException No FileSystem for scheme: s3n)

I had added the following properties to hive-site.xml file :

fs.s3n.awsSecretAccessKey xxxxxxxxxxxxxxxxxxxxx AWS SecretAccessKey fs.s3n.awsAccessKeyId xxxxxxxxxxxxxxxxxxxxx AWS AccessKey

Able to fix the issue with below approach ,but looking for a different approach .

create EXTERNAL table employee_ext_parquet(empid Int, name String, dept String, salary Int, nop Int) PARTITIONED BY(dttime String)ROW FORMAT DELIMITED FIELDS TERMINATED BY “,” stored as parquetfile LOCATION ‘s3n://ACCESS_KEY:SECRET_KEY@hivemeta/’;

ALTER TABLE employee_ext_parquet ADD PARTITION(dttime = ‘07-06-2016-06-06-02’) LOCATION ‘s3n://ACCESS_KEY:SECRET_KEY@hivemeta/07-06-2016-06-06-02’;

select empid,name,salary from employee_ext_parquet where dttime=‘07-06-2016-06-06-02’;

or we can set as below:

hive> set fs.s3.awsAccessKeyId=“xxxxxxxxxxxxxxxxxxx”;
hive> set fs.s3.awsSecretAccessKey=“xxxxxxxxxxxxxxxxxxx”;

hive> CREATE EXTERNAL TABLE IF NOT EXISTS employee ( eid int, name String,dept String, yoj String)
> COMMENT ‘External Employee details’
> ROW FORMAT DELIMITED
> FIELDS TERMINATED BY ‘,’
> LINES TERMINATED BY ‘\n’
> STORED AS TEXTFILE LOCATION ‘s3n://hivemeta/’;

1 Like