Good 'ole permissions, eh? A shame the error thrown by the datanuculeus code didn't expose this important piece of information.
Instead we get a red herring about "autocreate flag" nonsense and waste hours of time. Egads. +1 for sharing back! On Fri, Jan 17, 2014 at 6:26 PM, Mohammad Islam <[email protected]> wrote: > I never used postgres with Hive. > > These links might be helpful: > 1. > http://docs.hortonworks.com/HDPDocuments/HDP1/HDP-1.3.0/bk_dataintegration/content/ch_using-hive.html > Look for *Using Postgres for the Hive Metastore* > > 2. > http://www.cloudera.com/content/cloudera-content/cloudera-docs/CDH4/4.2.0/CDH4-Installation-Guide/cdh4ig_topic_18_4.html > *Configure the Metastore Service to Communicate with the PostgreSQL > Database* > > Remember these instructions are vendor specific. > > Regards, > Mohammad > > > On Friday, January 17, 2014 6:06 PM, Leena Gupta <[email protected]> > wrote: > After several tries, I finally figured it out. Thought I'll post what I > had to do in case others hit this problem. > From what I observed, it looks like the Datanucleus error is related to > the absence of proper access permissions to hiveuser in postgres. I > uninstalled both hive and postgres, installed hive 0.10 and postgres 9.1. > Then created the hive schema in postgres and granted permissions on all > objects within the public schema to hiveuser. > After this when I ran create table command, it again threw an error > related to the table 'COLUMNS_V2'. So then I manually granted > select,Insert,update delete for this table to hiveuser and now everything > works fine :) > > > On Thu, Jan 16, 2014 at 10:05 PM, Stephen Sprague <[email protected]>wrote: > > okay. so SEQUENCE_TABLE does indeed exist. that's the first thing to get > out of the way then. hmm. > > I suspect these other tables are just artifacts from the differences in > the hive versions. > > yeah, i'm not sure where to go from here and I couldn't find much via > googling either. > > would it be possible to install hive v0.12? I can at least vouch for that > version. (i'm using postgres v9.1 however.) > > Otherwise, we'll have to wait for someone else to chase this bugger down - > which may not be a bad thing. :) > > > On Thu, Jan 16, 2014 at 7:54 PM, Leena Gupta <[email protected]>wrote: > > Thanks for responding Stephen. > I checked the tables in Postgres and the SEQUENCE_TABLE exists. However in > comparison to the list you gave for Hive 0.12, the following tables are > missing, not sure if these could be the cause of the datanucleus error : > DELEGATION_TOKENS > MASTER_KEYS > VERSION > > Thanks, > Leena > > > On Thu, Jan 16, 2014 at 4:42 PM, Stephen Sprague <[email protected]>wrote: > > > On Thu, Jan 16, 2014 at 4:17 PM, Leena Gupta <[email protected]>wrote: > > Could not create "increment"/"table" value-generation container > "SEQUENCE_TABLE" since autoCreate flags do not allow it. > > > > > Interestingly enough this exact same question is posted here: > http://stackoverflow.com/questions/19205318/cannot-create-database-with-hive > and crickets for responses. the twist is is that its for mysql! which > leads us to the fact that its a middleware (datanucleus?) problem - not a > metastore vendor problem. > > for starters first thing i'd do (regardless of the autocreate flags in > hive-site.xml) is to confirm that table (SEQUENCE_TABLE) exists in your > postgres metastore database. "\dt" would list the tables. Is it present? > > Cheers, > Stephen. > > > PS this is what have for hive v0.12 on postgres if its any help. > > {noformat} > dwr_prod=> \dt > List of relations > Schema | Name | Type | Owner > --------+---------------------------+-------+---------- > public | BUCKETING_COLS | table | postgres > public | CDS | table | postgres > public | COLUMNS_OLD | table | postgres > public | COLUMNS_V2 | table | postgres > public | DATABASE_PARAMS | table | postgres > public | DBS | table | postgres > public | DB_PRIVS | table | postgres > public | DELEGATION_TOKENS | table | postgres > public | GLOBAL_PRIVS | table | postgres > public | IDXS | table | postgres > public | INDEX_PARAMS | table | postgres > public | MASTER_KEYS | table | postgres > public | NUCLEUS_TABLES | table | postgres > public | PARTITIONS | table | postgres > public | PARTITION_EVENTS | table | postgres > public | PARTITION_KEYS | table | postgres > public | PARTITION_KEY_VALS | table | postgres > public | PARTITION_PARAMS | table | postgres > public | PART_COL_PRIVS | table | postgres > public | PART_COL_STATS | table | postgres > public | PART_PRIVS | table | postgres > public | ROLES | table | postgres > public | ROLE_MAP | table | postgres > public | SDS | table | postgres > public | SD_PARAMS | table | postgres > public | SEQUENCE_TABLE | table | postgres > public | SERDES | table | postgres > public | SERDE_PARAMS | table | postgres > public | SKEWED_COL_NAMES | table | postgres > public | SKEWED_COL_VALUE_LOC_MAP | table | postgres > public | SKEWED_STRING_LIST | table | postgres > public | SKEWED_STRING_LIST_VALUES | table | postgres > public | SKEWED_VALUES | table | postgres > public | SORT_COLS | table | postgres > public | TABLE_PARAMS | table | postgres > public | TAB_COL_STATS | table | postgres > public | TBLS | table | postgres > public | TBL_COL_PRIVS | table | postgres > public | TBL_PRIVS | table | postgres > public | TYPES | table | postgres > public | TYPE_FIELDS | table | postgres > public | VERSION | table | postgres > (42 rows) > {noformat} > > > > > > >
