I cannot create and drop databases in Spark2-Sql kernel in Jupyter Hub on IT labs

Dear Sir / Madam,

I cannot create and drop databases in the Spark2-Sql kernel in the Jupyter Hub of the IT labs. It keeps on coming up with permission errors. I can create databases using the Linux Terminal and then create and drop and use tables in the Jupyter Hub, but when I want to create and drop databases in Jupyter Hub it comes up with permission errors.

I have tried all the usual things like using my username as a prefix for my databases. Using the Set command to ensure the right settings, but I still come up with permission errors.

I have given a copy of what I have done below;

import org.apache.spark.sql.SparkSession

val spark = SparkSession.
builder.
config(“spark.ui.port”, “0”).
config(“spark.sql.warehouse.dir”, “/user/itversity/warehouse”).
enableHiveSupport.
appName(“Spark SQL - Managing Tables - Basic DDL and DML”).
master(“yarn”).
getOrCreate
spark = org.apache.spark.sql.SparkSession@55107cfb
org.apache.spark.sql.SparkSession@55107cfb
%%sql

select current_database()
Waiting for a Spark session to start…
±-----------------+
|current_database()|
±-----------------+
| default|
±-----------------+

import sys.process._
val username = System.getProperty(“user.name”)
s"hdfs dfs -ls /user/itv000764/warehouse/${username}_retail.db/order_items"!
Found 2 items
-rwxr-xr-x 3 itv000764 supergroup 5408880 2021-08-08 06:46 /user/itv000764/warehouse/itv000764_retail.db/order_items/part-00000
-rwxr-xr-x 3 itv000764 supergroup 5408880 2021-08-08 06:57 /user/itv000764/warehouse/itv000764_retail.db/order_items/part-00000_copy_1
username = itv000764
warning: there was one feature warning; re-run with -feature for details
0
%%sql

SET hive.metastore.warehouse.dir = /user/itv000764/warehouse
±-------------------±-------------------+
| key| value|
±-------------------±-------------------+
|hive.metastore.wa…|/user/itv000764/w…|
±-------------------±-------------------+

%%sql
USE itv000764_retail
++
||
++
++

%%sql
SHOW tables
±---------------±----------±----------+
| database| tableName|isTemporary|
±---------------±----------±----------+
|itv000764_retail|order_items| false|
|itv000764_retail| orders| false|
±---------------±----------±----------+

%%sql

select current_database()

±-----------------+
|current_database()|
±-----------------+
| itv000764_retail|
±-----------------+

%%sql

select * from order_items limit 10
| 3| 2| 502| 5| 250.0| …
±------------±------------------±--------------------±------------------±------------------±-----------------------+
|order_item_id|order_item_order_id|order_item_product_id|order_item_quantity|order_item_subtotal|order_item_product_price|
±------------±------------------±--------------------±------------------±------------------±-----------------------+
| 1| 1| 957| 1| 299.98| 299.98|
| 2| 2| 1073| 1| 199.99| 199.99|
| 3| 2| 502| 5| 250.0| 50.0|
| 4| 2| 403| 1| 129.99| 129.99|
| 5| 4| 897| 2| 49.98| 24.99|
| 6| 4| 365| 5| 299.95| 59.99|
| 7| 4| 502| 3| 150.0| 50.0|
| 8| 4| 1014| 4| 199.92| 49.98|
| 9| 5| 957| 1| 299.98| 299.98|
| 10| 5| 365| 5| 299.95| 59.99|
±------------±------------------±--------------------±------------------±------------------±-----------------------+

%%sql

select * from orders limit 10
%%sql

select * from orders limit 10
±-------±---------±----------------±-----------+
|order_id|order_date|order_customer_id|order_status|
±-------±---------±----------------±-----------+
±-------±---------±----------------±-----------+

%%sql

USE itv000764_sms
++
||
++
++

%%sql

select current_database()
±-----------------+
|current_database()|
±-----------------+
| itv000764_sms|
±-----------------+

%
%%sql

show tables
±------------±--------±----------+
| database|tableName|isTemporary|
±------------±--------±----------+
|itv000764_sms| students| false|
±------------±--------±----------+

%%sql

select * from students
±---------±-----------------±----------------±----------------…
±---------±-----------------±----------------±--------------------±-------------------+
|student_id|student_first_name|student_last_name|student_phone_numbers| student_address|
±---------±-----------------±----------------±--------------------±-------------------+
| 1| Scott| Tiger| null| null|
| 3| Mickey| Mouse| [1234567890, 2345…|[A Street, One Ci…|
| 2| Donald| Duck| [1234567890, 2345…| null|
| 4| Bubble| Guppy| [5678901234, 6789…|[Bubbly Street, G…|
±---------±-----------------±----------------±--------------------±-------------------+

%%sql

DROP DATABASE IF EXISTS itv000764_sms1 CASCADE
++
||
++
++

%%sql

CREATE DATABASE IF NOT EXISTS itv000764_sms1
at org.apache.hadoop.hdfs.server.namenode.FSPermissionChecker.checkPermission(FSPermissionChecke…
Magic sql failed to execute with error:
org.apache.hadoop.hive.ql.metadata.HiveException: MetaException(message:Got exception: org.apache.hadoop.security.AccessControlException Permission denied: user=itv000764, access=WRITE, inode="/user/itversity/warehouse":itversity:itversity:drwxr-xr-x
at org.apache.hadoop.hdfs.server.namenode.FSPermissionChecker.check(FSPermissionChecker.java:496)
at org.apache.hadoop.hdfs.server.namenode.FSPermissionChecker.checkPermission(FSPermissionChecker.java:336)
at org.apache.hadoop.hdfs.server.namenode.FSPermissionChecker.checkPermissionWithContext(FSPermissionChecker.java:360)
at org.apache.hadoop.hdfs.server.namenode.FSPermissionChecker.checkPermission(FSPermissionChecker.java:239)
at org.apache.hadoop.hdfs.server.namenode.FSDirectory.checkPermission(FSDirectory.java:1909)
at org.apache.hadoop.hdfs.server.namenode.FSDirectory.checkPermission(FSDirectory.java:1893)
at org.apache.hadoop.hdfs.server.namenode.FSDirectory.checkAncestorAccess(FSDirectory.java:1852)
at org.apache.hadoop.hdfs.server.namenode.FSDirMkdirOp.mkdirs(FSDirMkdirOp.java:60)
at org.apache.hadoop.hdfs.server.namenode.FSNamesystem.mkdirs(FSNamesystem.java:3407)
at org.apache.hadoop.hdfs.server.namenode.NameNodeRpcServer.mkdirs(NameNodeRpcServer.java:1161)
at org.apache.hadoop.hdfs.protocolPB.ClientNamenodeProtocolServerSideTranslatorPB.mkdirs(ClientNamenodeProtocolServerSideTranslatorPB.java:739)
at org.apache.hadoop.hdfs.protocol.proto.ClientNamenodeProtocolProtos$ClientNamenodeProtocol$2.callBlockingMethod(ClientNamenodeProtocolProtos.java)
at org.apache.hadoop.ipc.ProtobufRpcEngine2$Server$ProtoBufRpcInvoker.call(ProtobufRpcEngine2.java:532)
at org.apache.hadoop.ipc.RPC$Server.call(RPC.java:1070)
at org.apache.hadoop.ipc.Server$RpcCall.run(Server.java:1020)
at org.apache.hadoop.ipc.Server$RpcCall.run(Server.java:948)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:422)
at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1845)
at org.apache.hadoop.ipc.Server$Handler.run(Server.java:2952)

Thanks

Sasha Ajay Malkani

@SAMalkani1 Please refer to the article

https://medium.com/@itversity/itversity-big-data-labs-hive-starter-kits-8b7b5c2a2721