You don't really want to mess around with the schema. This is what I have in Oracle 12c schema for TBLS. The same as yours
[image: Inline images 1] But this is Oracle, a serious database :) HTH Dr Mich Talebzadeh LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>* http://talebzadehmich.wordpress.com *Disclaimer:* Use it at your own risk. Any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on this email's technical content is explicitly disclaimed. The author will in no case be liable for any monetary damages arising from such loss, damage or destruction. On 26 August 2016 at 20:32, Stephen Sprague <sprag...@gmail.com> wrote: > yeah... so after the hive upgrade scripts ran we have this in pg for table > "TABLS" > > {quote} > dwr_prod_2_1_0=> \d "TBLS" > Table "public.TBLS" > Column | Type | Modifiers > --------------------+------------------------+-------------- > ------------------- > TBL_ID | bigint | not null > CREATE_TIME | bigint | not null > DB_ID | bigint | > LAST_ACCESS_TIME | bigint | not null > OWNER | character varying(767) | default NULL::character > varying > RETENTION | bigint | not null > SD_ID | bigint | > TBL_NAME | character varying(128) | default NULL::character > varying > TBL_TYPE | character varying(128) | default NULL::character > varying > VIEW_EXPANDED_TEXT | text | > VIEW_ORIGINAL_TEXT | text | > > {quote} > > wonder if i can perform some surgery here. :o do i feel lucky? > > On Fri, Aug 26, 2016 at 12:28 PM, Stephen Sprague <sprag...@gmail.com> > wrote: > >> well that doesn't bode well. :( >> >> we definitely need to use a remote metastore given this is a prod env >> with 100's of users. i wasn't able to see anything in the metastore log >> though so i'm wondering what logger to run to get that? don't think its >> hive.root.logger. >> >> thanks, >> Stephen. >> just toggling hive.metastore.try.direct.sql between true or false which >> seemed like it should influence the metastore access behaviour did not >> change anything. I guess this is a postgres incompatiblity with jdbc4 >> (this "character varying" thing.) >> >> On Fri, Aug 26, 2016 at 8:55 AM, Mich Talebzadeh < >> mich.talebza...@gmail.com> wrote: >> >>> Sounds like there are a number of issues with Hive metastore on >>> Postgres. There have been a number of reports on this. >>> >>> HTH >>> >>> Dr Mich Talebzadeh >>> >>> >>> >>> LinkedIn * >>> https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw >>> <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>* >>> >>> >>> >>> http://talebzadehmich.wordpress.com >>> >>> >>> *Disclaimer:* Use it at your own risk. Any and all responsibility for >>> any loss, damage or destruction of data or any other property which may >>> arise from relying on this email's technical content is explicitly >>> disclaimed. The author will in no case be liable for any monetary damages >>> arising from such loss, damage or destruction. >>> >>> >>> >>> On 26 August 2016 at 16:43, Stephen Sprague <sprag...@gmail.com> wrote: >>> >>>> thanks Gopal. you're right our metastore is using Postgres. very >>>> interesting you were able to intuit that! >>>> >>>> lemme give your suggestions a try and i'll post back. >>>> >>>> thanks! >>>> Stephen >>>> >>>> On Fri, Aug 26, 2016 at 8:32 AM, Gopal Vijayaraghavan < >>>> gop...@apache.org> wrote: >>>> >>>>> > NULL::character%20varying) >>>>> ... >>>>> > i want to say this is somehow related to a java version (we're using >>>>> 8) >>>>> >but i'm not sure. >>>>> >>>>> The "character varying" looks like a lot like a Postgres issue to me >>>>> (though character varying could be the real term for varchar in another >>>>> DB). >>>>> >>>>> The hive-metastore.log should have the real backtrace. >>>>> >>>>> You can try doing >>>>> >>>>> set hive.metastore.uris=; >>>>> set hive.metastore.try.direct.sql=false; >>>>> >>>>> >>>>> (i.e switch to embedded metastore + disable direct sql, in Hive CLI - >>>>> provided you have all the password stuff for the metastore in the >>>>> regular >>>>> hive-site.xml) >>>>> >>>>> https://github.com/apache/hive/blob/master/metastore/src/jav >>>>> a/org/apache/ha >>>>> doop/hive/metastore/MetaStoreDirectSql.java#L887 >>>>> <https://github.com/apache/hive/blob/master/metastore/src/java/org/apache/hadoop/hive/metastore/MetaStoreDirectSql.java#L887> >>>>> >>>>> >>>>> Cheers, >>>>> Gopal >>>>> >>>>> >>>>> >>>> >>> >> >