question Re: Hive with Google Cloud Storage in Archives of Support Questions (Read Only)

Hive with Google Cloud Storage

sudarshan_shrid — Fri, 25 May 2018 20:09:02 GMT

I have installed a hadoop 2.6.5 version cluster in GCP using VM's instances. Used GCP connector and pointed by hdfs to use gs bucket. Added the below 2 entries in coresite.xml:

google.cloud.auth.service.account.json.keyfile=<Path-to-the-JSON-file> 
fs.gs.working.dir=/

When using hadoop gs -ls / works fine , but when I am creating a hive tables

CREATE EXTERNAL TABLE test1256(name string,id  int)   LOCATION   'gs://bucket/';

I get the following error:

Error: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:java.security.AccessControlException: Permission denied: user=hdpuser1, path="gs://bucket/":hive:hive:drwx------) (state=08S01,code=1)

Apart form changes to coresite.xml are there any changes to be made at hive.xml also?

https://docs.hortonworks.com/HDPDocuments/HDP2/HDP-2.6.5/bk_cloud-data-access/content/authentication-gcp.html

Re: Hive with Google Cloud Storage

Shelton — Sat, 26 May 2018 14:59:56 GMT

@sudi ts

You need to copy the connector into the hadoop-client and hive-client location otherwise you will hit an error

cp gcs-connector-latest-hadoop2.jar /usr/hdp/current/hadoop-client/lib/ 
cp gcs-connector-latest-hadoop2.jar /usr/hdp/current/hive-client/lib

The below command should run successfully

$ hdfs dfs -ls gs://bucket/

This should run fine, but the issue you are having is with permission for hdpuser1 you will need to correct by running

$ hdfs dfs -chown hdpuser1 gs://bucket/

Now your create table should work, while logged in as hdpuser1

CREATE EXTERNAL TABLE test1256(name string,id int) LOCATION 'gs://bucket/';

Please let me know. If you found this answer addressed your question, please take a moment to log in and click the "Accept" link on the answer.

Re: Hive with Google Cloud Storage

sudarshan_shrid — Mon, 28 May 2018 21:27:22 GMT

Hi,

Thanks a lot for the info, but still facing the same issue.

I did create the user in AD and have a valid ticket , hdfs command does work accessing the GCS but cannot create external hive table.

Re: Hive with Google Cloud Storage

Shelton — Tue, 29 May 2018 04:25:48 GMT

@sudi ts

Can you share the latest error?

Re: Hive with Google Cloud Storage

sudarshan_shrid — Tue, 29 May 2018 20:05:10 GMT

Error: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:java.security.AccessControlException: Permission denied: user=hdpuser1, path="gs://bucket/":hive:hive:drwx------) (state=08S01,code=1)

hdpuser1 is an AD user, using the same user I execute

$ hdfs dfs -ls gs://bucket/

but using beeline when I try to create an external table it fails

Re: Hive with Google Cloud Storage

Shelton — Wed, 30 May 2018 01:43:52 GMT

@sudi ts

This is clearly a permission issue "Permission denied: user=hdpuser1, path="gs://bucket/":hive:hive:drwx------)"

Have you tried using ACL's

gsutil acl ch -u hdpuser1:WRITE gs://bucket/

And retry

Re: Hive with Google Cloud Storage

sudarshan_shrid — Wed, 30 May 2018 03:19:32 GMT

@Geoffrey Shelton Okot

I did try, but still fails.

CommandException: hdpuser1:WRITE is not a valid ACL changehdpuser1 is not a valid scope type

The GCS bucket has storage admin rights given to service account

hadoop fs -ls gs://bucket/ = works fine

Re: Hive with Google Cloud Storage

Shelton — Wed, 30 May 2018 03:33:18 GMT

@sudi ts

To you have access to the GCP IAM console? When treating a service account as a resource, you can grant permission to a user to access that service account. You can grant the Owner, Editor, Viewer, or Service Account User role to a user to access the service account.

Re: Hive with Google Cloud Storage

sudarshan_shrid — Thu, 31 May 2018 20:54:30 GMT

@Geoffrey Shelton Okot

I was able to create hive external table pointing my storage as GCS. But it only works as hive superuser but doesn't work as a normal hive user meaning, hdpuser1 cannot create hive table it fails with above error, but if execute su - hive it works .

I am no sure how to rectify this.

Re: Hive with Google Cloud Storage

sseth — Fri, 08 Jun 2018 09:15:16 GMT

Hi @sudi ts

Can you share some more information about this deployment.

- Is doAs enabled (hive.server2.enable.doAs)

- What is the authorization mechanism? Is the Ranger Authorizer being used.

If you can pull a stack trace from the HiveServer2 logs, that'll be very useful.

HDP-2.6.5 ships with the Google connector, so there's no need to replace any jars. The GS connectivity is working given that you can create this table if logging in as the hive user, and list files via hadoop fs -ls.

Cloud storage Access Control is generally handled via Cloud Provider constructs - such as IAM roles. Hadoop interaction in terms of file owners and permissions doesn't capture this. The user returned by hadoop fs -ls will typically be the logged in user, and the permissions don't indicate much.

Re: Hive with Google Cloud Storage

sudarshan_shrid — Fri, 08 Jun 2018 20:33:24 GMT

Hi @sseth

Issue is resolved after adding following property in core-site.xml

fs.gs.reported.permissions=777

Normal Users can access hive and create external table pointing to GCS location.

Re: Hive with Google Cloud Storage

sudarshan_shrid — Thu, 19 Jul 2018 21:23:20 GMT

@sseth

I have downloaded the latest jar

https://storage.googleapis.com/hadoop-lib/gcs/gcs-connector-latest-hadoop2.jar

Tried creating the external table and its failing with following error:

FAILED: HiveAccessControlException Permission denied: user [abcd] does not have [READ] privilege on [gs://hdp-opt1/forhive/languages] (state=42000,code=40000)

I have enabled Hive plugin and set the permission of 777 in coresite.xml

Where there any changes made to jar?? I also see few properties have changed in this link:

https://docs.hortonworks.com/HDPDocuments/HDP2/HDP-2.6.5/bk_cloud-data-access/content/gcp-cluster-config.html

Is it mandatory to use the json key? If my vm instance has required permission to talk to gcs

Re: Hive with Google Cloud Storage

agrawalreetika — Tue, 19 Mar 2019 16:13:23 GMT

@sudi ts Were you able to resolve this issue?

Re: Hive with Google Cloud Storage

nanu — Sat, 16 Sep 2023 15:51:30 GMT

Hi,

You need to set in hive-site.xml, these three tags to get this working with hive:

<property> <name>google.cloud.auth.service.account.json.keyfile</name> <value>/home/hadoop/keyfile.json</value> </property> <property> <name>fs.gs.reported.permissions</name> <value>777</value> </property> <property> <name>fs.gs.path.encoding</name> <value>/home/hadoop/</value> </property>

Same xml tags we can have it on hadoop in core-site.xml to have it working with hdfs,

On beeline, just execute this and it shall work:

INSERT OVERWRITE DIRECTORY 'gs://bucket/table' ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' SELECT * FROM table;

Please upvote if you found helpful!