Solution: Check ACLs for HDFS folders & set access for user account hive if required.   It seems that Hive 3.1.0 executes HiveSQL commands as user hive even if you launch them from the Linux command-line with Beeline as another user (e.g. maria_dev). Thus, the hive account needs to have access to any HDFS resources required (i.e. folders & files). Also, it relies on access control lists (ACLs) for this (sometime, just setting the right group access via chmod isn't enough).   Use the following to check ACL permissions: hdfs dfs -getfacls <some_hdfs_path>   Use the following to set ACL permissions: hdfs dfs -setfacls -m user:hive:rwx <some_hdfs_path>   However, sometimes this still didn't work as expected. For e.g., after setting the following to ensure any future files get the right ACLs, I'd find they'd randomly fail to be set: hdfs dfs -setfacls -R -m default:user:hive:rwx <some_hdfs_path>   Then I started getting several other issues while trying to learn Hive with DAS & Spark SQL with Zeppelin.   In Data Analytics Studio, I would be able to create my training database and add tables to it, but was never able to view any tables via the DAS UI (unless I ran the show tables command).   Also, YARN resources were hitting 100% while executing simple queries with a small dataframe, just before services seemed to crash (the remote Zeppelin, Ambari & Shell-in-a-Box sessions all disconnected even though the VM still appeared to be running in VirtualBox). Then all services failed to restart this morning.   Thus, I deleted the VM & re-installed HDP Sandbox 3.0.1.   Straight after re-installing and re-creating my training database, I was able to view all tables created in it and Spark SQL is working fine.   While re-creating the VM is fine in this instance, if anyone can direct me to reading material on how folks handle such problems in live production environments, I'd appreciate it. I'm guessing re-installing the VM is a last resort... ... View more

Update:   After further reading, the following are the different ideas I explored - but still no solution.   1) Whether Hive needs to be started as a different end user than hive – I'm guessing this shouldn't be required. See: https://docs.cloudera.com/HDPDocuments/HDP3/HDP-3.0.1/starting-hive/content/hive_start_hive_as_authorized_user.html   2) Checking that the ACL settings for subfolders created within hdfs:/user/maria_dev are appropriate to allow user hive full access. However, I confirmed that: the use of ACLs is enabled in HDFS Advanced Configs via Ambari as described in: https://docs.cloudera.com/HDPDocuments/HDP3/HDP-3.0.1/hdfs-acls/content/configuring_acls_on_hdfs.html user hive should be able to access as it belongs to the same group as maria_dev (i.e. hdfs) , and the home folder's ACL settings using the hdfs dfs -getfacl command grant full group access - see https://docs.cloudera.com/HDPDocuments/HDP3/HDP-3.0.1/hdfs-acls/content/using_cli_commands_to_create_and_list_acls.html)    [root@sandbox-hdp ~]# hdfs dfs -getfacl -R /user/maria_dev # file: /user/maria_dev # owner: maria_dev # group: hdfs user::rwx group::rwx other::r-x   3) A suitable resource-based authorisation policy needs to be configured in Ranger. I'm guessing this would only be relevant after the db is created. Plus, the current settings allow user hive to access any database in Hive.   So, I'm not sure what else needs to be checked.   In the interim, I've just removed the LOCATION clause in my CREATE DATABASE statement to allow the db to be created in the managed area, so I can get on with my learning. However, I'd appreciate any guidance on how folks create dbs in specific, external locations using Hive 3.0.1 or above. I'm using HDP Sandbox to learn about these products, so any pointers to good books/blogs for Hive 3+ config & use would be helpful (I'm using O'Reilly's Programming Hive by Edward Capriolo, Dean Wampler, Jason Rutherglen, which is good but published in 2012).   Cheers! Ed ... View more